收缩临时表空间收缩方法及ORA-03297错误处理

当我们进行手动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
   
6. 
   
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24486203/viewspace-2147974/