在 Oracle 数据库上执行数据归档的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1717932/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the best way to perform data archiving on an Oracle database?
提问by Rulas
I'd like to figure out the best way to archive the data that is no needed anymore, in order to improve the application performance and also to save disk space. In your experience what is the best way to implement this, what kind of tools can I use? It is better to develop an specific in house application for that purpose?
我想找出归档不再需要的数据的最佳方法，以提高应用程序性能并节省磁盘空间。根据您的经验，实现这一点的最佳方法是什么，我可以使用什么样的工具？最好为此目的开发特定的内部应用程序？
回答by Sam
One way to manage archiving:
管理归档的一种方法：
- Partition your tables on date range, e.g. monthly partitions
- As partitions become obsolete, e.g. after 36 months, those partitions can now be moved to your data warehouse, which could be another database of just text files depending upon your access needs.
- After moving, the obsolete partitions can be removed from your primary database, so always maintaining just (e.g.) 36 months of current data.
- All this can be automated using a mix of SQL/Shell scripts.
- 按日期范围对表进行分区，例如每月分区
- 随着分区变得过时，例如在 36 个月后，这些分区现在可以移动到您的数据仓库，根据您的访问需要，它可以是另一个仅包含文本文件的数据库。
- 移动后，可以从主数据库中删除过时的分区，因此始终只保留（例如）36 个月的当前数据。
- 所有这些都可以使用 SQL/Shell 脚本的组合来自动化。
回答by Ravi Ramanathan
The best way to archive old data in ORACLE database is:
在 ORACLE 数据库中归档旧数据的最佳方法是：
- Define an archive and retention policy based on date or size.
- Export archivable data to an external table (tablespace) based on a defined policy.
- Compress the external table and store it in a cheaper storage medium.
- Delete the archived data from your active database using SQL DELETE.
- Then to clean up the space execute the below commands: alter table T_XYZ enable row movement; alter table T_XYZ shrink space;
- If you still want to free up some disk space back to the OS (As Oracle would have now reserved the total space that it was previously using), then you may have to resize the datafile itself: SQL> alter database datafile '/home/oracle/database/../XYZ.dbf' resize 1m;
- 根据日期或大小定义存档和保留策略。
- 根据定义的策略将可归档数据导出到外部表（表空间）。
- 压缩外部表并将其存储在更便宜的存储介质中。
- 使用 SQL DELETE 从活动数据库中删除存档数据。
- 然后清理空间执行以下命令：alter table T_XYZ enable row Movement; 改变表 T_XYZ 收缩空间；
- 如果您仍想将一些磁盘空间释放回操作系统（因为 Oracle 现在会保留它以前使用的总空间），那么您可能需要调整数据文件本身的大小： SQL> alter database datafile '/home/ oracle/database/../XYZ.dbf'resize 1m;
For more details, please refer: http://stage10.oaug.org/file/sroaug080229081203621527.pdf
更多详情请参考：http: //stage10.oaug.org/file/sroaug080229081203621527.pdf
回答by Todd Moses
I would export the data to a comma-delimited file so it can be exported into almost any database. So if you change versions of Oracle or go to something else years later you can restore it without much concern.
我会将数据导出到一个逗号分隔的文件中，这样它就可以导出到几乎任何数据库中。因此，如果您更改 Oracle 的版本或在几年后转到其他地方，您可以毫不费力地恢复它。
Use the spool file feature of SQL*Plus to do this: http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/#savingoutput
使用 SQL*Plus 的假脱机文件功能执行此操作：http: //cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/#savingoutput
