Solved

Archiving tables

Posted on 2013-01-15
3
285 Views
Last Modified: 2013-02-15
I have been simply asked to archive two tables. This has been asked to me due to the change in roles and I haven't done archiving before.. There is not much information given as to archiving policy, retention peroid, whether the data needs to be available online or not etc

What are some of questions you would ask if you are assigned with this task..please provide me what you think is the good set of questions to ask..

Thanks
0
Comment
Question by:gs79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
ID: 38780678
>>what you think is the good set of questions to ask..

I think you covered them:  archiving policy, retention peroid, whether the data needs to be available online or not etc

I would also ask what is meant by 'archive'.  This could mean pull specific records from the tables or it could mean remove the tables all together.  I suspect it is archiving specific data from the tables.

The answer to that question depends on what specific steps you will need to take.

Assuming archiving data, I suggest you get familiar with the tables and any associated constraints like foreign keys and if these keys cascade deletes.

This will tell you what additional data you need to preserve when you delete from the main tables.

For example: an employee table might have foreign keys to a job_history table.  If you delete an employee, it might automatically delete job_history records.  If you only save off employee records, then delete the employees, when the time comes to recover an employee, you will be missing all the history records if you did not also preserve the history records.
0
 
LVL 4

Assisted Solution

by:tvedtem
tvedtem earned 167 total points
ID: 38781133
The above answer is 100% correct & acceptable.

I'd get something heavy and clonk whoever asked you on the head.
Being left to do that if you're not a DBA (and, more importantly, if you're not familiar with the schema) is not really fair or wise.

I would suggest that you get familiar with the schema before you dare even touch it.
Find the person who used to do this before you and ask them to explain the DB design to you, then ask them whether it's safe to do what's been requested + how they would do it.

If that person is not available, have a stuff drink.  Then make sure the database has been backed up recently.  If it hasn't, do not touch it, and resolve that first.

Cascading deletes, in particular, have the potential to ruin your day...

Finally, if for whatever reason you have no luck with any of the above, I would archive just one row of each type first, then test what happens.  Only when you are very sure should you go removing large volumes of data.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 166 total points
ID: 38794955
DBAs maintains Data but not Business Rules.
Archive policies should come from management and before doing this you should have clear picture what is needed. Sometime archiving means = Purging.

If this is the first time on going process, I would suggest you to create stored/some scripts. i.e. lets say you have two tables as you mentioned. Retention period of 1 is one year and another table hold data of first table for another year. Which means you have 1 year in first table and 2 year in another table.
In this case, I would preferred to make monthly partition of each tables. Convert all Global indexes to local IF NEEDED and do purging/archiving as per your policy.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle encryption 12 74
Need help on decision table structure 7 58
return value based on substr 10 75
oracle sql developer + 45 days from last day of month 2 22
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question