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

Thread: Best practices for Housekeeping.

  1. #1
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Best practices for Housekeeping.

    Hi All!

    What exactly is housekeeping ? Is is something like cleaning up of unwanted data and archiving them or simply deleting unwanted data depending upon the requirement.

    To be honest, never done this thing before so wanted to know what you guys do for housekeeping ? The Do's and Don'ts ? Any link or document would be helpful.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    Hi Amar,

    We have done the housekeeping before on the Ingres databases. It was the process of archiving the old/historic data (eg: retaining only current and previous years data). We had to create temporary tables based on the tables (eg: inventory_master_1998 for inventory_master) and backup the table and delete the data from the regular tables.

    This was a massive exercise and we used to a good 2 days to complete it.

    We had to restore (at times) to different machines for the reporting purposes- Your understanding is correct, however I have not seen any documents, but it's fairly a common practice in some of the companies. Hopefully, others would share their practices.

    Badrinath
    There is always a better way to do the things.

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Amar, Selective export has made it pretty easy. You can export the historical data to dump files using QUERY option and delete them from tables. You can archive the dump fils to tape that can be restored if required.
    If you are using partitioning then its even simpler just export the partition archive them and drop them.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Thanks Bardri, Thanks Sanjay.

    Its more clear now, but then again the same problems will arise, ie deleting of millions of historic data. Now i can see the problems comming.

    I think i should be looking for best practices for deleting huge ammount of data rather than best practices for Housekeeping

    Hmmm i think now i need to know more about datawarehousing. Slimedave, any suggestions on housekeeping and datawarehousing .

    Regards
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    Wait till you see Sanjay in his French maids outfit, woo hoo!

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by adewri
    I think i should be looking for best practices for deleting huge ammount of data rather than best practices for Housekeeping
    Hmmm... i was thinking rather than deleting millions of rows, why not create a new table and insert rows that i want to keep and drop the old table itself. After all insert is faster than delete.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Originally posted by adewri
    Hmmm... i was thinking rather than deleting millions of rows, why not create a new table and insert rows that i want to keep and drop the old table itself. After all insert is faster than delete.
    What happens when by any chance you need your old data...or....Just take a full export dump before the thought mentioned by you...being at the safer side...you know...
    "Greatest Rewards come only with Greatest Commitments!"

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by sandycrab
    What happens when by any chance you need your old data...or....Just take a full export dump before the thought mentioned by you...being at the safer side...you know...
    Yes of course that would include two inserts then, one for the data that i want to keep on the current DB and the other one for data that i don't want to keep and copy them to remote archival DB, then drop the old one and rename the new one to the old one.

    Thanks for reminding this point.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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