Oracle

Silly and Interesting – I

Experience always counts !!!!and i am a beginner 🙂
Today again had a silly but interesting event.I was told to import data of a table in a 9.2.0.6 database  which i found out was a range based partitioned table.The range was from 01-10th,11-20st and 21-30/31st of every month.I was supposed to import the data of AUGUST and SEPTEMBER month, so i just checked if any partition existed from before, and found that except for TAB_DATA_01TO10AUG2009 all other partitions already existed.
So, i uncompressed the dumpfile for TAB_DATA_01TO10AUG2009 and imported using

"imp file= TAB_DATA_01TO10AUG2009.dmp log=TAB_DATA_01TO10AUG2009_imp.log fromuser=abc
touser=abc commit=n feedback=100000" and saw 7394200 imported successfully.

And after it when i checked for the partition name it didn’t existed…and i was almost in shock mode for few seconds that how can that be!!!!
Suddenly, i realized that the data would have got imported to TAB_DATA_11TO20AUG2009 partition as its range was “VALUES LESS THAN (TO_DATE(‘ 2009-08-21 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))” .Immediately, without wasting a second i went for SPLIT PARTITION,

sql>ALTER TABLE ABC.TAB_DATA
 SPLIT PARTITION TAB_DATA_11TO20AUG2009  AT
 (TO_DATE('2009-08-11 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
 INTO (PARTITION TAB_DATA_01TO10AUG2009
 PARTITION TAB_DATA_11TO20AUG2009);

And then partition TAB_DATA_01TO10AUG2009 got created with 7394200 rows and i was 🙂
One thing to note about  Split Partition is – “It’ll create two temporary segments – one for each of the new partitions, load them and when that is complete, it will convert the temporary segments into permanent ones and drop the old segment.” So, you need to keep checking the tablespace and might need to increase the space if the segment is big.

Leave a Reply