oracle清理临时表空间

Microsoft Windows [版本 6.1.7601]
版权所有 © 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>sqlplus sys/ORCL2015@orcl as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期三 5月 8 09:08:35 2019

Copyright © 1982, 2013, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database tempfile ‘D:\oracle\oradata\orcl\TEMP01.DBF’ drop;
alter database tempfile ‘D:\oracle\oradata\orcl\TEMP01.DBF’ drop
*
第 1 行出现错误:
ORA-25152: TEMPFILE 目前不能被删除

SELECT s.sid,
s.username,
s.status,
u.tablespace,
u.segfile#,
u.contents,
u.extents,
u.blocks
FROM v$sessions,v$sort_usage u
WHERE s.saddr = u.session_addr
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;

select s.“SID”, s.“SADDR”, s.“SERIAL#”
from v$session s
where s.“SID” = ‘15’;

alter system kill session ‘15,43’;

SQL> alter database tempfile ‘D:\oracle\oradata\orcl\TEMP01.DBF’ drop;

数据库已更改。

SQL> alter tablespace temp add tempfile ‘D:\oracle\oradata\orcl\TEMP01.DBF’ size 1048M reuse autoextend on next 100M;

表空间已更改。

SQL> select d.file_name, d.file_id, d.tablespace_name, d.bytes
2 from dba_temp_files d;

FILE_NAME

FILE_ID TABLESPACE_NAME BYTES

---

D:\ORACLE\ORADATA\ORCL\TEMP01.DBF
1 TEMP 2147483648

D:\ORACLE\ORADATA\ORCL\TEMP02.DBF
2 TEMP1 536870912

D:\ORACLE\ORADATA\ORCL\HUAIREN_TEMP.DBF
4 HUAIREN_TEMP 52428800

FILE_NAME

FILE_ID TABLESPACE_NAME BYTES

---

D:\NUMB_TEMP.DBF
5 NUMB 33554432

SQL>alter database tempfile ‘D:\oracle\oradata\orcl\TEMP01.DBF’ autoextend off;