archiving
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: archiving

  1. #1
    Join Date
    May 2007
    Posts
    2

    Question archiving

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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by penguin71
    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??
    Check out IxUnload, at ixionsoftware.com

  4. #4
    Join Date
    May 2007
    Posts
    2
    Quote Originally Posted by PAVB
    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?
    create_DATE is not the PK/FK. We have regular sequences for our keys.

    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.

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    Use partitioning.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width