■■ CMP临时文件
CMP - 指 Compression Advisor ,是 Oracle 数据库的压缩建议特性,在生成建议时产生的中间过程表,一般会自行删除。这个特性自 11.2.0.4 引入( Oracle 11.2.0.4 BP1 or higher),在某些情况,该功能执行失败后,会遗留下一个系列的数据库对象。
这些对象,可以在 SYS 下进行删除。
If you see tables with names like CMP3xxxxxx or CMP4xxxxxx (where xxxxxx is a number) left over after running Compression Advisor, it is likely because Compression Advisor failed at some point. These are interim tables created/used by Compression Advisor, which are normally dropped when it completes. You can safely drop those tables.
■■ 实践案例:清理临时文件
巡检发现某几个业务用户有系统遗留的较大临时表,最大的一个表 USER2.CMP3$1255387 多达13亿行记录,需要及时清理。
代码语言:txt复制■ 用户1
TABLE_NAME TABLESPACE_NAME NUM_ROWS LAST_ANALYZED
------------------------------------------------------------------------
CMP3$662133 USER1_IRMS_TBS 29067846 2019/12/19 22:17
■ 用户2
TABLE_NAME TABLESPACE_NAME NUM_ROWS LAST_ANALYZED
------------------------------------------------------------------------
CMP3$22077693 USER2_RMW_TBS 6503320 2016/5/27 6:19
CMP3$22077695 USER2_RMW_TBS 32448070 2016/5/27 6:20
CMP3$1255387 USER2_RMW_TBS 1331586873 2022/1/13 22:58
■ 用 SYS 用户执行删除清理
drop table "USER1".CMP3$662133 purge;
drop table "USER2".CMP3$22077693 purge;
drop table "USER2".CMP3$22077695 purge;
drop table "USER2".CMP3$1255387 purge;