Square DBA – Oracle DBA Talk

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.

More »

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.

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'; 

Feds OK Sun acquisition

Oracle Corp. said Thursday that the U.S. Department of Justice has approved its proposed acquisition of Sun Microsystems Inc. and terminated the waiting period under the Hart-Scott-Rodino Act.

Santa Clara-based Sun stockholders approved the transaction on July 16. Closing of the transaction is subject to certain conditions, including clearance by the European Commission.

The $7.4 billion acquisition by Redwood City-based Oracle (Nasdaq: ORCL), which has a campus in Rocklin, is being closely watched by the European Union, with questions raised about Sun’s Java software language.

Killing your own session

Ever wanted to kill your own session? You know commit suicide; This works nicely:

alter session set events 'immediate crash';

Nothing like a ORA-03113: end-of-file on communication channel to brighten your day.