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”

Oracle GoldenGate version 11.1.1.1 released

After long wait Oracle finaly relesed new Oracle GoldenGate version 11.1.1.1 which supports encrypted tablespaces.

About Replicating TDE-encrypted data

Oracle GoldenGate supports the Transparent Data Encryption (TDE) at the column and tablespace level.

● Column-level encryption is supported for all versions of 10.2.0.5, 11.1, and 11.2.
● Tablespace-level encryption is supported for all versions of 10.2.0.5 and 11.1.0.2.

Required database patches

To support TDE, one of the following Oracle patches must be applied to the database,
depending on the version.

● Patch 10628966 for 10.2.0.5.2PSU
● Patch 10628963 for 11.1.0.7.6PSU
● Patch 10628961 for 11.2.0.2

Site Upgrade and Personal Blog

I upgraded wordpress to 3.1.2. Let me know if you see any issues.

Also I run this blog to document some of my Oracle experiences. It do not update it all that often, but I try to get a couple updates posted per month. I also have several other sites. One for where I live, one for my photography, and a few others. None of the sites are just general blogs. I did not want to subject my family and friends to my rants so I choose not to use my Facebook account for daily updates. So I started a personal blog at mattmcguire.org. Check it out if you are so inclined.