如何确定temp表空间？

  1确定temp表空间ts#

SQL> select ts#, name from sys temp 。ts$ ;

TS# NAME

0 SYSTEM

1 UNDOTBS1

2 SYSAUX

3 TEMP

4 USERS

5 UNDOTBS2

6 EXAMPLE

7 TSS

8 TEMP2

2执行清理

SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;

Session altered temp 。

temp表空间的TS# 为 3*, So TS#+ 1= 4

SELECT UPPER(F temp 。TABLESPACE_NAME) "tablesacpe name",

D temp 。TOT_GROOTTE_MB "tablespace size(M)",

D temp 。

  TOT_GROOTTE_MB - F temp 。TOTAL_BYTES "have used(M)",

TO_CHAR(ROUND((D temp 。TOT_GROOTTE_MB - F。TOTAL_BYTES) / D。TOT_GROOTTE_MB * 100,2),'990。

  99') "using rate",

F temp 。TOTAL_BYTES "free space(M)",

F temp 。MAX_BYTES "max block(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS temp 。

  DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD temp 。TABLESPACE_NAME,

ROUND(SUM(DD temp 。BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS temp 。

  DBA_DATA_FILES DD

GROUP BY DD temp 。TABLESPACE_NAME) D

WHERE D temp 。TABLESPACE_NAME = F。TABLESPACE_NAME

ORDER BY 4 DESC temp 。