Finding Oracle tablespaces with mixed autoextensible datafiles

>> Monday, 27 February 2017

Two ways to identify tablespaces where some datafiles are autoextend true and some are false.
(Mixing these can cause issues with monitoring, so try to keep them consistent.)

SELECT DISTINCT a.tablespace_name
FROM dba_data_files a
WHERE a.autoextensible ='YES'
(SELECT DISTINCT b.tablespace_name
FROM dba_data_files b
WHERE b.autoextensible ='NO'
AND b.tablespace_name=a.tablespace_name )
ORDER BY tablespace_name;

SELECT  ddf2.tablespace_name
(SELECT DISTINCT ddf.tablespace_name,ddf.autoextensible
FROM  dba_data_files ddf
GROUP BY ddf.tablespace_name,ddf.autoextensible
) ddf2
GROUP BY ddf2.tablespace_name
HAVING  count(1) = 2
ORDER BY ddf2.tablespace_name;


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

>> Friday, 18 November 2016

Oracle Database 11g

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;

client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);

select client_name, status,attributes,service_name
from dba_autotask_client;


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.

   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'  ,

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 !!! 


  © Blogger template Simple n' Sweet by 2009

Back to TOP