I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.
I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.
I need to Modify the column(MSGID) data type from RAW to BLOB for a Queue Table, I'm getting the following error.
BANNER --------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE11.2.0.2.0Production
YUV >
YUV > DESC PDA_REPORT_MESSAGE_QTAB Name Null?Type ----------------------------------------------------- -------- ------------------------------------ Q_NAME VARCHAR2(30) MSGID NOT NULL RAW(16)
YUV > YUV > YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB); ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB) * ERROR at line 1: ORA-22858: invalid alteration of datatype
YUV > YUV > YUV > YUV > YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB); ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB) * ERROR at line 1: ORA-24005: Inappropriate utilities used to perform DDL on AQ table PDADBA.PDA_REPORT_MESSAGE_QTAB YUV >
We have some tables in our database in which for loading data we have the setup in place to do Exchange partition after data load into staging. Today we did changes to column length to one pair of main and staging table. Post that Exchange partition stop working.
i have a table in my PD database which have more than 30,000 records .some records in a column say p_code is not tagged with code like '9876543'while other records are tagged in this column with code such as '19022345678'.
Now i want to update these records with tagging 1902 with each one .
i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.
I have a scenario where I need to update field in report_input table.It has bind variables in it which will be prompted while running the query.I see that QUERY_VALUE field that need to be updated has length of VARCHAR2(3000).
desc report_input Name Null Type ----------------- -------- -------------- QUERY_NAME NOT NULL VARCHAR2(64) QUERY_VALUE NOT NULL VARCHAR2(3000) [code]....
I want this trigger to automatically update the num_songs_in_album column in the album table whenever rows are inserted or deleted into the Track table.
SQL> CREATE OR REPLACE TRIGGER update_num_songs_in_album 2 BEFORE INSERT OR DELETE ON Track 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN [code]....
ITEMNUM STORELOC lastyear currentyear AM1324 AM1 need sum(quantity) here need sum(quantity) AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.
Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.
Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.
Now we are supposed to apply column level TDE to some of our table in database. Now it will be a 'ALTER' on the columns. it involves 4 big tables out of which 3 tables having size ~30GB(one is partitioned table) and another one ~800GB(Not partitioned) Now the concern is, what will be the efficient/safest way to apply TDE on columns, below are the two options with us. (NOTE - We do have downtime window during DB maintenance but looking at the size of the table, i suspect it might take lot.)
1. Directly apply 'ALTER' on the columns. (Note- i was testing on my local, it took 3hrs for a 30GB table to ALTER the column to TDE)
2. Use Table Redefinition for Altering the column. (Creating interim table with column as TDE and then Redefining whole table).
I am facing some challenge while running update query on newly added column in existing table.
Environment Details Oracle 9i, version 9.2.0.6 Os Unix Aix 6.1
No of records in table : 12572770
Below are the step i followed.
1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8). 2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29. 3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.
SQL> desc testtablename Name Null? Type ----------------------------------------- -------- ---------------------------- COLUMNNAME1 VARCHAR2(8) COLUMNNAME2 CHAR(1) COLUMNNAME3 CHAR(1) COLUMNNAME4 VARCHAR2(8) COLUMNNAME5 VARCHAR2(11)
[Code]...
Table altered.
SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;
1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update) 2. No oracle error thrown in alert log or in session where the query being executed. 3. The event for the query is "db file sequential read". 4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds. 5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database. 6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.
Below are the queries completed successfully
Update Testtablename Set Columnname29 = '1' Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
[Code]...
Below are the queries hanging in database
Update Testtablename
Set Columnname29 = Columnname1 Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Update Testtablename
Set Columnname29 = '1111' Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Below is character set in database
SQL> select * from v$nls_parameters; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA
if a user have alter table gant but could not alter .. what additional grant it need
SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3)); alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3)) * ERROR at line 1: ORA-00942: table or view does not exist
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I altered existing table EVENT_SUB - added 3 columns. After that, I noticed all the procedures which had mention of this table name went in INVALID status, even if its simple SELECT, ALTER OR INSERT as shown below..
SELECT * FROM EVENT_SUB
OR INSERT INTO EVENT_SUB... OR ALTER TABLE EVENT_SUB WHERE....
So I had to recompile all the procedures associated with it. Is there any other ways to achieve this, like a line of code to add in the procedure itself, right after this DDL statements.
Sometimes i use this: select object_name, object_type from all_objects where owner='TOYCOM' and status='INVALID' Then, I would simply recompile the invalid objects.
For indexes, i do...
alter index <name> rebuild;
BTW, I did try to preview message, and then click on Create Topic, it gave me error..again.
how to play around with NDS dynamic sql and I'm trying to add a column on the fly.Basically the procedure is trying to take a table name, column name, and eventually a data type and adds it to a table.
It works fine without the bind variable for the column name, accepting the table name on the fly.As soon as it tries to use the column name I get an ORA-00904 invalid identifier exception.
Here is the procedure I'm using
CODEcreate or replace procedure test(tbl_name varchar2, col_name varchar2) IS qry varchar2(500); begin
