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'
AND EXISTS
(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
FROM
(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;





Read more...

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

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

Back to TOP