收縮臨時表空間收縮方法及ORA-03297錯誤處理

shawnloong發表於2017-11-29
當我們進行手動resize的時候報如下錯誤


點選(此處)摺疊或開啟

  1. SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 16G;
  2. alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 16G
  3. *
  4. ERROR at line 1:
  5. ORA-03297: file contains used data beyond requested RESIZE value

  7. SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 20G;
  8. alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 20G
  9. *
  10. ERROR at line 1:
  11. ORA-03297: file contains used data beyond requested RESIZE value




我們採用交換臨時表空間方式，收縮臨時表空間
新建臨時表空間

點選(此處)摺疊或開啟

  1. SQL> CREATE TEMPORARY TABLESPACE temp2 tempfile '/u01/app/oracle/oradata/orcl/temp201.dbf' size 4G autoextend on next 20M maxsize 8G;
  2. Tablespace created.
  3. SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
  4. Database altered.
  5. SQL> select * from (select username,temporary_tablespace from dba_users) where rownum<10;
  6. USERNAME TEMPORARY_TABLESPACE
  7. ------------------------------ ------------------------------
  8. SYS TEMP2
  9. SYSTEM TEMP2
  10. OUTLN TEMP2
  11. DIP TEMP2
  12. ORACLE_OCM TEMP2
  13. APPQOSSYS TEMP2
  14. WMSYS TEMP2
  15. XS$NULL TEMP2
  16. EXFSYS TEMP2
此時刪除原臨時表空間，發現一直hang在那裡

點選(此處)摺疊或開啟

  1. SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

查詢正在使用臨時表空間事務我們是測試環境直接kill(正式環境慎用，等一段時間再drop)

點選(此處)摺疊或開啟

  1. SQL> SELECT 'ALTER SYSTEM KILL SESSION ''' || sid ||','|| serial# || '''immediate;' FROM v$session where saddr in (SELECT session_addr FROM v$sort_usage WHERE tablespace='TEMP2');


點選(此處)摺疊或開啟

  1. SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
  2. Tablespace dropped.



刪除臨時表空間完成

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24486203/viewspace-2147974/，如需轉載，請註明出處，否則將追究法律責任。

相關文章