I have a very large table (Partitioned already ) with about 30Gb in size .
Once a row is inserted Normally we do not update or delete any records in it .
The daily inserts will be only 100-200MB per day for this table ...
To back this 200MB i backup a table which is 30Gb and which makes the dumpfile 19GB.
In case of any imports /recovery it is very time consumming
How do i split the table into two separate tables (Say old and new).
I want that to made as a read only tablespace then to be backed only once ...
I will jion the two tables as a single table as view ..
How how do enforce the constarints(Primary and unique etc ..)
It has to check for the values in the old table also ...
Any help will be of much use ...
Why not split the partition, put the "old" data in it's own tablespace, make the tablespace that contains the "old" data as readonly.
This way, you don't have to worry about creating a join, etc.
already The table is partitioned into 40 diffrent partitions..
When we insert according to the range value it gets inserted into any off the 40 partioned tables ..
and all the 40 partioned is in use ...
If I make any of them read only then it will give errors ...
why don't you write atrigger on this table to identify 'today's' data ?
I have partitioned the table on the key column called doc_type
which has ranges from 1,2,3,4,-40 ..
How do I split the partitioned table on the above conditions ...
Hear i cannot split on 2,3,4
Yes I can write a trigger and get the rows which are inserted recently ..
But this not allow me to make the table or tablespace as read only which is what i want ..
The problem with splitting the table into two tables is how do you enforce the PK on both tables.
First, you need to figure out how to identify the "old" records. Usually, this is done with some sort of DATE field. Once this has been determined, I would use this as my primary partition key.
Assuming doc_type is of some importance, I would then create a partitioned global index using doc_type as the partition key along with any other attributes you need.
Lastly, I would create single PK with a regular index. This way, your PK will still be enforced, you can have "old" data in a read-only tablespace, and "active" data in a read-write tablespace. Just remember to keep your PK in a read-write tablespace and rebuild it whenever you split or move partitions.