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 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,

 (TO_DATE('2009-08-11 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))

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