ORA-00600: internal error code, arguments: [kkqctdrvCVM(2): More than one CV!]

>> Friday, 18 November 2016


Oracle Database 11g 11.2.0.3.0

ORA-00600: internal error code, arguments: [kkqctdrvCVM(2): More than one CV!], [], [], [], [], [], [], [], [], [], [], []



Sql causing error
/* SQL Analyze(27,1) */ select "foo"."foo" "foo .....


Call Stack
kgeadse kgerinv_internal kgerinv kgeasnmierr kkqctdrvCVM


This error comes out of SQL Tuning Advisor, if you aren't using the tuning advisor disable it:


select client_name, status,attributes,service_name
from dba_autotask_client;

BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

select client_name, status,attributes,service_name
from dba_autotask_client;


Read more...

Using LPAD with dbms_redefinition to add leading zeros

>> Thursday, 17 November 2016

In the previous post I showed how to change a column definition in a table from varchar to num (or vice-versa) using dbms_redefinition


I had reason to need to pad out a converted varchar column with leading zeros.


begin
DBMS_REDEFINITION.START_REDEF_TABLE(
   uname       => 'MYSCHEMA',
   orig_table  => 'MYTABLE',
   int_table   => 'INT_MYTABLE',
   col_mapping => ' ORDERNUM     ORDERNUM  ,
                              LPAD (to_char (COLUMN_TO_BE CHANGED),3,''0'')  COLUMN_TO_BE CHANGED ,
                              LOAD   LOAD'  ,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID  );
end;
/

LPad will left pad a field for you, so here I am saying take my number column, turn it into a varchar (see post ) and stick leading zeros on the data that is already in it to make it 3 characters.

For the process you need to follow to actually make it all happen go to the previous post, but am I writing this here because I just know at some point I'm going to go "I know I've done that with redef before, but how the hell did I do it" .  So am I going to leave this here so I kick myself when my own post comes up in the google search that I do - you know that feeling too I'm sure !!! 


Read more...

Change column from number to varchar2 using DBMS_REDEF

>> Friday, 24 June 2016


This does a to_char but it also works with a to_num if you want to go from a varchar to a number.

STEP 1 :
Add disk space required

This needs to be enough to repeat the size of the table and its indexes, it is temporary storage for the period of the change only.


STEP 2:
Redo

The redefinition is going to churn through redo, make sure you have enough logs and they are large enough or you will get a bottle neck that will slow it up. If your db is in archivelog mode then also make sure you have a cunning plan to deal with them too.


STEP 3:
Think about a backout plan

I took an export of my table at the start because I had the luxury to do so.  I love a belt and braces approach to change.


STEP 4 :
count original table - alway nice to be able to prove you haven't lost rows when you finish!

select count (*) from myschema.mytable;
record result


STEP 5:
Note what you have already

select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed  from dba_tab_columns
where table_name = 'MYTABLE'
order by column_id;

1 ORDERNUM                        NUMBER 22 8 N dd/mm/yyyy hh:mm:ss
2 COLUMN_TO_BE CHANGED    NUMBER 22 3 N dd/mm/yyyy hh:mm:ss
3 LOAD                              TIMESTAMP(6) 11 N dd/mm/yyyy hh:mm:ss


STEP 6 :
create an interim table

create myschema.INT_mytable the same as the existing table but with COLUMN_TO_BE CHANGED  as a  varchar2 instead of a number (There is no need to create the indexes, but I did include the constraints)

CREATE TABLE myschema.INT_mytable
(
 ORDERNUM                                     NUMBER(8)                          NOT NULL,
 COLUMN_TO_BE CHANGED        VARCHAR2(3 CHAR)          NOT NULL,
  LOAD                                                TIMESTAMP(6)                    NOT NULL
)
TABLESPACE mytablespace
other clauses as per your original table definition
;

STEP 7:
check it

select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed
from dba_tab_columns
where table_name = 'MYTABLE'
and column_id =2;
2 COLUMN_TO_BE CHANGED NUMBER 22 3 N dd/mm/yyyy hh:mm:ss

select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed
from dba_tab_columns
where table_name = 'INT_MYTABLE'
and column_id =2;
2 COLUMN_TO_BE CHANGED VARCHAR2 12 N


STEP 8
check redef will work

begin
DBMS_REDEFINITION.can_redef_table('MYSCHEMA', 'MYTABLE');
end;
/

STEP 9
Start the redefinition

I parallelised this based on cat /proc/cpuinfo | grep processor | wc -l x 4

set echo on;
spool redef.txt
ALTER SESSION FORCE PARALLEL DML PARALLEL 32;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 32;
ALTER SESSION ENABLE RESUMABLE;
set timing on;
begin
DBMS_REDEFINITION.START_REDEF_TABLE(
   uname       => 'MYSCHEMA',
   orig_table  => 'MYTABLE',
   int_table   => 'INT_MYTABLE',
   col_mapping => ' ORDERNUM     ORDERNUM  ,
                              to_char (COLUMN_TO_BE CHANGED)  COLUMN_TO_BE CHANGED ,
                              LOAD   LOAD'  ,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID  );
end;
/


STEP 10:
What else do you want to keep as a part of the redefinition, you need to think about this for yourself based on what is relevant.

set serveroutput on;
DECLARE
error_count pls_integer := 0;
begin
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( UNAME => 'MYSCHEMA',
ORIG_TABLE => 'MYTABLE',
INT_TABLE => 'INT_MYTABLE',
COPY_INDEXES => DBMS_REDEFINITION.cons_orig_params,
COPY_TRIGGERS => FALSE,
COPY_CONSTRAINTS => FALSE,
COPY_PRIVILEGES => TRUE,
IGNORE_ERRORS => FALSE,
COPY_STATISTICS => TRUE,
NUM_ERRORS => error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || error_count);
end;
/

errors := 0


STEP 11:
finish the redefinition

begin
  DBMS_REDEFINITION.finish_redef_table(
    uname      => 'MYSCHEMA',      
    orig_table => 'MYTABLE',
    int_table  => 'INT_MYTABLE');  
 end;
/



STEP 12:
check counts and do checks



select count (*) from MYSCHEMA.MYTABLE;
same result as STEP 4


select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed  from dba_tab_columns
where table_name = 'MYTABLE'
order by column_id;


1 ORDERNUM                                    NUMBER 22 8 N dd/mm/yyyy hh:mm:ss
2 COLUMN_TO_BE CHANGED                VARCHAR2 12 N dd/mm/yyyy hh:mm:ss
3 LOAD                                            TIMESTAMP(6) 11 N dd/mm/yyyy hh:mm:ss



select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed
from dba_tab_columns
where table_name = 'MYTABLE'
and column_id =2;
2 COLUMN_TO_BE CHANGED VARCHAR2 12 N 21/05/2016 18:00:32


check parallel on table and indexes - all the same as original
synonyms still there
grants still there
constraints and indexes still there


STEP 13:
drop the interim table

DROP TABLE MYSCHEMA.INT_MYTABLE;


NOTE:

it took a while for me to get my syntax spot on, if you get failure after the redefinition has started you need to abort the process to be able to restart it:


begin
 DBMS_REDEFINITION.abort_redef_table(
    uname      => 'MYSCHEMA',      
    orig_table => 'MYTABLE',
    int_table  => 'INT_MYTABLE');
    end;







Read more...

  © Blogger template Simple n' Sweet by Ourblogtemplates.com 2009

Back to TOP