I have a situation where I have several tables, each table has CREATED_DATE fields and they FK relationships between the tables:
Example: TableA has FK in TableB which has FK in TableC
We need to archive the records, but only once the CREATED_DATE on the youngest child record (TableC) is older than 30 days old.
Any ideas how this could be accomplished via partitions or some other way??
Not enough information.
I hope CREATED_DATE is not your PK/FK column.
How about defining "archiving"? is archived data supposed to stay in the database forever?
How about the cardinality of these FK relationships?
How about volume?
Are you planning to "archive" in a daily basis? weekly? monthly?
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Check out IxUnload, at ixionsoftware.com
Originally Posted by penguin71
create_DATE is not the PK/FK. We have regular sequences for our keys.
Originally Posted by PAVB
By archiving I mean that we will move records from primary schema to a archive schema or db, then delete from primary schema.
The parent to child cardinality is 1 to many in all cases. The hierarchy is about 8 tables deep.
The top parent table gets currently about 5,000 new records a day which will probably rise to about 20,000 per day within the next 3 months. The children multiply from there.
Archive frequency has not been decided, sufficed to say we want to keep our load on the primary db/schema to < 90 days. Parent cannot be archived until all the children are archived.
Partition by whatever frequency, week/day/month etc...
When you choose to archive, simply exchange the partitions for all of the tables involved. A good partition naming convention is very helpful for doing this activity along with some custom pl/sql and dba_jobs for scheduling.
If your moving to a seperate DB, then you'll need to Unload the data and re-load it, exp/import, sqlldr, data pump...
Why not extract the partitions using a utility and then make the "archived" data available via external tables? That way you can move them to tape or some other long term storage media.
Click Here to Expand Forum to Full Width