【Oracle ERP Note 】Tablespace [SYSTEM] is [91.45 percent] full

Problem

The tablespace, SYSTEM, at the production node is almost 91.45 percent full. How could I clean up the redundant data?

Fig. 1:



Fig. 2:



Study

此類表 SYSTEM 與 SYSAUX [Oracle 10.g 版本後支援] 作為 DBMS 管理使用，勿手動刪除其表單中任一 Record。但，對應此 Tablespace 的實體檔案儲存容量快不足夠時，又該如何處理?

依據 Hussein Sawwan 的說法，他提到:

1. If there is no more free space available in this specific datafile and you want it to stop growing. Then you have to turn autoextend off.
2. If autoextend is off on one or more tablespace(s) and it is on for one datafiel that belongs to that specific tablespace, the database will write data to that datafile.

Solution

可選擇下列其一來解決，選擇任一並無 Performance Issue。


Option1: ALTER DATABASE DATAFILE '' RESIZE [K|M|G];

Option2: ALTER TABLESPACE ... ADD DATAFILE;


Reference

1. ALTER TABLESPACE, http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3002.htm#SQLRF01002

2. Tablespace check script for Oracle E-Business Suite R11&R12, http://heliosguneserol.com/2010/06/11/tablespace-check-script-for-oracle-e-business-suite-r11r12/

3. OEM Tablespace Space Used % Alerts and the AUTOEXTEND effect, http://gavinsoorma.com/2011/11/oem-tablespace-space-used-alerts-and-the-autoextend-effect/