Square DBA – Oracle DBA Talk

Move and partitioned index to a new tablespace

To dynamically move a partitioned index to a new tablespace run the following SQL

select ‘Alter index ‘||INDEX_OWNER||’.'||INDEX_NAME||’
rebuild partition ‘||PARTITION_NAME||’ tablespace CDR_DETAIL_INDEX1;’
from dba_ind_partitions where TABLESPACE_NAME = ‘DW_CDR_DETAIL’;

Change a table’s default tablespace

Do you need to change a table’s default tablespace? It’s pretty simple:

ALTER TABLE {OWNER}.{TABLE NAME}
MODIFY DEFAULT ATTRIBUTES TABLESPACE {NEW TABLESPACE NAME};

This does not move the table it only changes the attribute for the default tablespace. This is helpful if you are adding partitions, but not specifying the tablespace.

Purge The DBA Recycle Bin

Need to dump the DBA recycle bin? It’s pretty easy. As a DBA user run:

purge dba_recyclebin;

Remove non-ASCII characters from a column

Do you need to remove special (non-ASCII) characters from a VarChar2 column in Oracle? Create this function:

CREATE OR REPLACE FUNCTION O1DW.RECTIFY_NON_ASCII(INPUT_STR IN VARCHAR2)
RETURN VARCHAR2
IS
str VARCHAR2(2000);
act number :=0;
cnt number :=0;
askey number :=0;
OUTPUT_STR VARCHAR2(2000);
begin
str:=’^'||TO_CHAR(INPUT_STR)||’^';
cnt:=length(str);

for i in 1 .. cnt loop
askey :=0;
select ascii(substr(str,i,1)) into askey
from dual;
if askey < 32 or askey >=127 then
str :=’^'||REPLACE(str, CHR(askey),”);
end if;
end loop;
OUTPUT_STR := trim(ltrim(rtrim(trim(str),’^'),’^'));
RETURN (OUTPUT_STR);
end;
/

More »

Dynamically Move Partitions To A New Tablespace

Do you want to move partitions to a new tablespace in Oracle? Here is some helpful SQL:

Find a partitioned table’s default table space:

SELECT def_tablespace_name FROM dba_part_tables
where owner = ‘O1DW’
and table_name = ‘ACME_FAILED_DETAIL’;

Change a partitioned table’s tablespace:

ALTER TABLE O1DW.ACME_FAILED_DETAIL
MODIFY DEFAULT ATTRIBUTES TABLESPACE ACME_FAILED_BIG1;

Move existing partitions to a new tablespace and compress the data:

select ‘ALTER TABLE ‘||table_owner||’.'||table_name
||’ move partition ‘||partition_name
||’ TABLESPACE ACME_FAILED_BIG1 COMPRESS FOR ALL OPERATIONS update indexes NOLOGGING;’
from dba_tab_partitions
where table_owner = ‘O1DW’
and table_name = ‘ACME_FAILED_DETAIL’
and TABLESPACE_NAME <> ‘ACME_FAILED_BIG1′
order by PARTITION_POSITION;

Dynamically Delete Old Partitions

Are you looking to delete older partitions in an Oracle database? Here is a basic SQL query that will give you what you need.

select ‘ALTER TABLE ‘||table_owner||’.'||table_name
||’ DROP PARTITION ‘
||partition_name||’ UPDATE INDEXES;’
from dba_tab_partitions
where table_owner = ‘O1DW’
and table_name = ‘ACME_FAILED_DETAIL’
order by PARTITION_POSITION;

Modify to your specific needs.

Finding Large Directories

Recently I was looking to clear some space on one of my Oracle servers. I looked for large files with this command:

find . -type f -size +100000k -exec ls -lh {} \; | awk ‘{ print $9 “: ” $5 }’

This looks for files over 100MB, but other than datafiles there were very few files found. I knew I needed to look for large directories. Directories that had a large amount of small files. This command worked nicely:

du -h / | grep ^[1-9][0-9][0-9.]*G | sort -rn

From there I was able to find the directories that I needed to clear out.

What are cvutrace.log.* files

I was looking to clear some space recently and ran across a 14G directory (/u01/app/grid/product/11.2.0/grid/cv/log). Inside there were a lot of files like “cvutrace.log.0_20100720015347″. These files are log files related to a RAC installaation. They can safely be deleted.

To stop the files from being generated is add the following at the top of the cluvfy script in the /bin directory.

SRVM_TRACE=”false”

Backing up your CRONTAB

If you are running a lot of scripts through CRON you may want to have a current backup.  Since your CRON is simply a text schedule I prefer a daily email backup.  It’s simple and does what I need.  I add this line to my CRON:

00 0 * * * crontab -l > crontablist.txt;
mail -s "DW CRON - Quality Control" email@domain.com < crontablist.txt

Everyday at midnight I get an email with the contents of my CRON.

Finding the 10 largest objects in an Oracle database

Need to find the largest objects in your database?  It’s pretty simple, here is the code:

col	owner format a15
col	segment_name format a30
col	segment_type format a15
col	mb format 999,999,999
select  owner
,	segment_name
,	segment_type
,	mb
from	(
	select	owner
	,	segment_name
	,	segment_type
	,	bytes / 1024 / 1024 "MB"
	from	dba_segments
	order	by bytes desc
	)
where	rownum < 11
/