Archive for the ‘Oracle How-To’ Category:
Removing Carriage Return From A File
Dealing with data between Windows and Linux can be a pain at times. On thing I need to do somewhat often is to take data from a users (normally on Windows) load it into an Oracle database. Then I will need to manipulate the data or add to it and dump it back out for the user.
An issue arises when I deal with numbers, especially if they are the last column. There is a very easy way to clean up the data though. At the end of a line Windows uses a carriage return and a line feed. Linux only uses the line feed to the carriage return becomes an actual character. You cannot see a carriage return so most of the time it is missed.
More »
Restart Dell OpenManage Service
I had some SNMP errors today that required me to restart Dell’s OpenManage Service. It’s actually pretty simple to do, but I wanted to share the commands.
To stop the services:
sh /root/linux/supportscripts/srvadmin-services.sh stop
To start the services again:
sh /root/linux/supportscripts/srvadmin-services.sh start
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.
