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

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s