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