常用Oracle 表空间管理 SQL

一、查看表空间大小及使用

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select     tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,    decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,    decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
    from     dba_data_files group by tablespace_name union all
    select     tablespace_name || '  **TEMP**'
    ,    sum(bytes)/1024/1024 used_mb
    from     dba_temp_files group by tablespace_name) tsu
,    (select tablespace_name, sum(bytes)/1024/1024 free_mb
    from     dba_free_space group by tablespace_name) tsf
where    tsu.tablespace_name = tsf.tablespace_name (+)
order    by 4
/

SQL> @ll.sql

TABLESPACE_NAME      size MB  free MB % used

---------------------------------------- ------------ ----------- ------

APP2   20       20      0

INDX   25       25      0

TOOLS   10       10      0

USERS   25       25      0

APP1  100       98      2

UNDOTBS1  200      186      7

DRSYS   20       11     48

ODM   20       11     48

CWMLITE   20 6     74

SYSTEM  380 9     97

EXAMPLE  150 1     99

XDB   45 1     99

TEMP  **TEMP**   50 0    100

13 rows selected.

SQL>