当我们进行手动resize的时候报如下错误
-
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 16G;
-
alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 16G
-
*
-
ERROR at line 1:
-
ORA-03297: file contains used data beyond requested RESIZE value
-
-
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 20G;
-
alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 20G
-
*
-
ERROR at line 1:
-
ORA-03297: file contains used data beyond requested RESIZE value
我们采用交换临时表空间方式，收缩临时表空间
新建临时表空间
-
SQL> CREATE TEMPORARY TABLESPACE temp2 tempfile '/u01/app/oracle/oradata/orcl/temp201.dbf' size 4G autoextend on next 20M maxsize 8G;
-
Tablespace created.
-
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
-
Database altered.
-
SQL> select * from (select username,temporary_tablespace from dba_users) where rownum<10;
-
USERNAME TEMPORARY_TABLESPACE
-
------------------------------ ------------------------------
-
SYS TEMP2
-
SYSTEM TEMP2
-
OUTLN TEMP2
-
DIP TEMP2
-
ORACLE_OCM TEMP2
-
APPQOSSYS TEMP2
-
WMSYS TEMP2
-
XS$NULL TEMP2
-
EXFSYS TEMP2
此时删除原临时表空间，发现一直hang在那里
-
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
查询正在使用临时表空间事务我们是测试环境直接kill(
正式环境慎用，等一段时间再drop)
-
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');
-
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
-
Tablespace dropped.
删除临时表空间完成
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24486203/viewspace-2147974/，如需转载，请注明出处，否则将追究法律责任。
转播
