Archive for the ‘Oracle How-To’ Category:
Compressing Partitioned Tables
I recently wrote a script to compress certain partitions in a table. Basically any partition that was loaded and analyzed I wanted to move to a new tablespace and compress the data.
Resetting an Enterprise Manager Agent
There are times when your Enterprise Manager Agent needs to be cleared out and rest. Here are some very simple steps.
Script to shrink a tablespace
To shrink a tablespace in Oracle you actually need to shrink the datafiles that make up the tablespace. You can only shrink a datafile down to the high water mark (HWM). There are several methods to reduce the HWM, but I am not going to address those in this post. If you want to return some space back to the OS or ASM disk you may be able to run a few commands and not need to worry about trying to adjust the HWM.
Purge the Recyclebin in Oracle
Did you know Oracle has a recycle bin? When you drop a table it goes into the recycle bin assuming you have the feature turned on. If you are trying to reclaim space simply dropping a table will not do it. You need to also purge the table from the recycle bin.
How to export with data pump
I have always used Oracle good old export utility to backup the logical definition of all my databases. In addition to using RMAN backup I feel a logical dump of the database is a good idea. I have now started using Oracle’s data pump utility. I work quite well, but does operate a bit differently that the older export utility.
Strip carriage return from varchar2 column
Have you ever needed to strip a carriage return from varchar2 column? Sometime when loading data from different sources I run into these non-printable characters. SQLLDR loaded them just fine, but I try to move the data into another table with specific datatypes it fails. Why? Because there are character that I cannot see and will not allow the varchar2 data to insert into a number column.
Oracle’s Analytic Functions to Process CDRs
I love Oracle’s analytic functions. Today I needed to process call records (CDRs) from a phone switch. I needed to find telephone numbers that had 5 or more 20 minute calls within any 60 minute period of time. I needed to scan a month’s worth of calls, so about 200 million records.
My first inclination was to write a PL/SQL procedure to calculate this for me as I did not think straight SQL would get the job done. I was wrong Oracle’s analytical functions worked nicely and very fast. Here is the code I used.
select count(*) OVER (PARTITION BY called_station_id ORDER BY
call_begin_tmst RANGE 1/24 PRECEDING) as cnt
, called_station_id
from o1dw.cdr_detail
where summary_date between to_date('20090701','YYYYMMDD')
and to_date('20090731','YYYYMMDD')
and billing_duration_sum > 1200;
This only looks at calls over 20 minutes (billing_duration_sum > 1200) and then counts how many times a phone number (called_station_id ) was seen in any 60 minute period of time (RANGE 1/24 PRECEDING). I was able to scan 200 million records in about 20 minutes. The process worked perfectly.
Truncate date range partitions
Have you ever needed to truncate old range partitions that are broken up by day? I do all the time. Much of my data warehouse data is located in range partitioned tables. These are generally partitioned by day. I put together the following script to take care of the job for me.
CREATE OR REPLACE PROCEDURE "LCR"."TRUNCATEOLDPART" (
i_table_name IN varchar2,
i_table_owner IN varchar2,
i_start_day IN varchar2,
i_end_day IN varchar2
)
as
v_value varchar2(255);
v_name varchar2(30);
p_s_date date := trunc(sysdate-i_start_day);
p_e_date date := trunc(sysdate-i_end_day);
begin
FOR c1 IN (select high_value, partition_name
from all_tab_partitions
where table_name = i_table_name and table_owner = i_table_owner)
loop
v_value := c1.high_value;
v_name := c1.partition_name;
if to_date(substr(v_value,11,10),'YYYY-MM-DD') between p_s_date
and p_e_date then dbms_output.put_line('Start Truncating
Partition '||substr(v_name,1,100)||' at '||
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
EXECUTE IMMEDIATE 'ALTER TABLE '||i_table_owner||'.'||
i_table_name||' truncate partition '||v_name;
dbms_output.put_line('Completed Truncating Partition '||
v_name||' at '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
dbms_output.put_line('------------------------------------');
end if;
end loop;
end;
The usage of this script is as follows.
execute dbms_output.enable(null); execute lcr.truncateoldpart(table_name,table_owner, oldest_days_to_truncate, newest_days_to_truncate);
So to truncate all partitions between 60 and 30 days old on the LCR.ENUM_CDR table just run:
execute dbms_output.enable(null);
execute lcr.truncateoldpart('ENUM_CDR','LCR',60,30);
Output Will look like this:
Start Truncating Partition SYS_P2505671 at 2009-09-22 13:59:30 Completed Truncating Partition SYS_P2505671 at 2009-09-22 13:59:30 ------------------------------------ Start Truncating Partition SYS_P2505651 at 2009-09-22 13:59:30 Completed Truncating Partition SYS_P2505651 at 2009-09-22 13:59:31
One caveat with a script like this is permissions. This works fine for running again any table in the schema owned by the package owner. If you try to run this against another schema you will get permission errors. To solve this you need to grant “drop any table” to the package owner. Obviously this is potential security risk. I prefer to just re-create the package in every schema that needs to perform this function. That way I don’t need to give any one user the ability to drop any table in my database.
Finding large files in Linux
It’s easy to track down your Oracle datafiles.
select BYTES, NAME from v$datafile;
Will tell the size of each datafile and it’s location on the server. If your server is like mine you will large non-Oracle files on the server as well. From time to time I need to find these large files to see if they can either be compressed, backed up and removed, or deleted all together. Here is a handy little statement I use on my Redhat servers.
find {/path/to/directory/} -type f -size +{size-in-kb}k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }'
so when I am looking for 200+ MB files on a specific mount I use
find /old_array -type f -size +200000k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }'
or if I want to find files over 500MB in the current directory I will use
find . -type f -size +500000k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }'
ORA-01502: index or partition of such index is in unusable state
Do you ever get and ORA-01502? This error code says your index is in an unusable state. It’s easy to find these indexes.
select index_name, status, owner from dba_indexes where STATUS = 'UNUSABLE';
If you want to build the rebuild SQL that is easy too.
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where STATUS = 'UNUSABLE';
