This content has been marked as final. Show 10 replies
the easyway i thought will be:
ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
The only way will be data pump to archive that table out of the database, but you will have to import it when you need it.
and is there any possibility to archive the data for 2009 year and wanted to reuse it as when it is required
if at all i archived the data and wanted to reload the again what are the options to reload it successfully without any errors the best possible option expectedI'm not sure about the most suitable solution in your case.
For instance, if you used Partitions and exported the Partitions storing the Year 2009 data before cleaning them, you can reload the Data easily with an Import. The DROP PARTITION and EXCHANGE PARTITION let you Purge and Load historical data:
Else, in *11g* you have a new feature called FLASHBACK ARCHIVE which let you store all the changes on specific Tables of your choice during a retention period:
Hope this help.
For instance, if you have a single Table with historical data with a Column which stores the "date" information, you may proceed as follow:
- 1. Export the Year 2009 data of this Table by using the QUERY Parameter. With this parameter you'll define the WHERE Clause to select the year 2009 data. You'll find here an example of QUERY Parameter usage:
- 2. Check for any error during the Export (define a logfile for the Export with the Parameter LOGFILE).
- 3. Delete the Year 2009 data by using the same WHERE Clause (as for the QUERY parameter).
- 4. Then, you may SHRINK the Table so as to reclaim the space to the Tablespace.
Afterwards, if you have to Reload the Year 2009 data you can Import the Dump previously generated, with the Parameter TABLE_EXISTS_ACTION=APPEND.
If you have a Range Partitioned Table with Partition Key on the Column which stores the "date" information. You may define a Partition for each year.
Then, Exchange the Partition (the one for the year 2009) to a an "ArchiveTable" (which has the same structure as the Partitioned Table). Export the "Archive Table" and drop it. To Reload the Data, you have to Import the "Archive Table" and Exchange the Partition to get back the data into the Partition.
You'll find here useful explanations:
Anyway, test the method you choose to apply on a test Database, so as to be sure that it's safe and you can Reload easily your data. And, never Archive your data on a Production Database without a good BACKUP.
Hope this help
Separate it off to a separate table, then use a view that unions the two tables when you need it. If you don't need to cross years, you might just change queries between tables, have one report for the new data and another report for the archived data. If people are putting together "reports" in Excel or something, let them do the unioning manually. It depends on what you have and what you need.
I have transactional tables that go back a decade, because really, why archive at all? Do you have inappropriate full table scans?