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.
Oracle RAC Project Starts Tomorrow
A I previously described I will started a new Oracle RAC project. If everything goes as planned I will get my hands on the 2 node setup tomorrow.
I have been busy researching 3PAR and enhancements to 11G. I will be installing 11G and implementing all new data warehouse ETL code.
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 buys Silver Creek Systems
Oracle said Monday it acquired Silver Creek Systems Inc., which focuses on product data quality. Silver Creek Systems is based in Westminster, Colo.
“Lack of standardized product data continues to be a challenge for many enterprises,” said Hasan Rizvi, senior vice president, Oracle Fusion Middleware Product Development. “With the addition of Silver Creek, Oracle is extending its industry leading data integration offering with complementary solutions to enhance product data quality and help customers get more accurate and consistent product data for use across their enterprise.”
Oracle Storage Research
I have been heavily researching Oracle storage best practices since starting my new Oracle RAC project. My new setup will specifically be for replacing a data warehouse that is currently 4.8T. The new data warehouse will start out significantly smaller. An entirely new ETL process will be deployed an then the historical data will be move over to it.
The new setup will be a (2) node RAC installation running Oracle 11G. The storage for this project will be a SAN and utilizing Oracle’s ASM architecture. Specifically we will be running 3Par’s InServ F400. We will be starting out with (40) 450GB 15K drives. We will be running dual 4 GBIT/s fiber channel adapters. Our SAN switches will be running 2 GBIT/s ports with an upgrade path to use 4 GBIT/s switch later.
Oracle RAC Project
I will be starting a new project where I will be using Oracle RAC 11G. The setup will be a two node RAC setup running on 3PAR’s F400
I will try to document as much as I can here on Square DBA, so stay tuned . . . .
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.
SQL Worksheet gone in 11g client
I just reinstalled my PC and installed a clean OS. I do this every once in a while. I find just formatting and starting over is better that trying to clean the machine up.
My last load consisted of the Oracle 10g client which came with SQL Worksheet. I like SQL Worksheet because it’s not a full dumbed down GUI and yet it gives me a few point-n-click features. Well it looks like Oracle decided to dump a standalone version of SQL Worksheet for embedding it in SQL Developer. I am not a fan.
I feel confined with this new version. I can launch multiple sessions but they all appear within the SQL Developer framework. I was also viewing object using the Enterprise Manager client, well that is gone too. I know I can view all of my database object in SQL Developer, but I don’t like the change.
So now I am using Enterprise Manager Grid Control and SQL Developer. I will use these tools instead of installing the 10g client since Oracle is obviously going in that direction, but I am not doing it willingly.
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.
