As a DBA we should be in a situation to understand where to place these tables and how to optimize them with the storage. Some tables in my environment are 50GB and they are backed up every month before running a critical process. Then the backup stays for 1 year.
Some tips to handle such cases
Some backup tables are never used , so I would suggest to export them with EXPDP utility and delete them on the schema.
Some backup tables are used by developers , so to address that there are various options
- Create a New Disk Group with cheaper devices and create a tablespace on it. Use tablespace clause in the create table command to move the tablespace into them.
ex : create table sh.employee_history_bkup_jan2010 tablespace tablebackup as select * from sh.employee_history;
- Use compress option when creating such tables.
ex : create table sh.employee_history_bkup_jan2010 tablespace tablebackup compress as select * from sh.employee_history;
- If you have already backed up the table without compression method , then use alter table move procedure with compress option.
ex : alter table sh.employee_history_bkup_jan2010 move compress;
Note : When creating these backup tables make use of the nologging feature to avoid archivelogs.
I hope this helps people is warehousing environments.
