Solved

Archiving tables

Posted on 2013-01-15
3
280 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
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
Comment Utility
>>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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now