Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Archiving tables

Posted on 2013-01-15
3
Medium Priority
?
288 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 668 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 668 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 664 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

972 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