一、檢視臨時表空間

--檢視所有臨時表空間名字及檔名

select f.file#,t.ts#,f.name "File",t.name "Tablespace" from v$tempfile f,V$tablespace t where f.ts# = t.ts#;

--檢視當前使用者預設臨時表空間

select * from database_properties where property_name="DEFAULT_TEMP_TABLESPACE";

--檢視所有使用者的預設表空間和預設臨時表空間

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

--檢視所有臨時表空間大小

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,

USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",

NVL(FREE_SPACE,0) "FREE_SPACE(M)"

FROM

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

二、檢視所有表空間大小、使用

COLUMN dummy NOPRINT

COLUMN pct_used FORMAT 999.9 HEADING "%|Used"

COLUMN name FORMAT a19 HEADING "Tablespace Name"

COLUMN Kbytes FORMAT 999,999,999,999 HEADING "KBytes"

COLUMN used FORMAT 999,999,999,999 HEADING "Used"

COLUMN free FORMAT 999,999,999,999 HEADING "Free"

COLUMN largest FORMAT 999,999,999,999 HEADING "Largest"

BREAK ON report

COMPUTE sum OF kbytes ON REPORT

COMPUTE sum OF free ON REPORT

COMPUTE sum OF used ON REPORT

set pagesize 2000

set line 300

--SPOOL tablespace_size.lst

SELECT

NVL(b.tablespace_name,nvl(a.tablespace_name,"UNKOWN")) name

, kbytes_alloc kbytes

, kbytes_alloc-NVL(kbytes_free,0) used

, NVL(kbytes_free,0) free

, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used

, NVL(largest,0) largest

FROM ( SELECT SUM(bytes)/1024 Kbytes_free

, MAX(bytes)/1024 largest

, tablespace_name

FROM sys.dba_free_space

GROUP BY tablespace_name

) a

, ( SELECT SUM(bytes)/1024 Kbytes_alloc

, tablespace_name

FROM sys.dba_data_files

GROUP BY tablespace_name

) b

WHERE a.tablespace_name (+) = b.tablespace_name

order by pct_used desc

/

三、檢視undo表空間大小、使用

select * from (select

a.tablespace_name,

sum(a.bytes)/(1024*1024) total_space_MB,

round(b.free,2) Free_space_MB,

round(b.free/(sum(a.bytes)/(1024*1024))* 100,2) percent_free

from dba_data_files a,

(select tablespace_name,sum(bytes)/(1024*1024) free from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

group by a.tablespace_name,b.free)

where tablespace_name = "UNDOTBS1";