參考文獻(xiàn)

1. dba_free_space : dba_free_space
2. dba_data_files : dba_data_files
3. v$temp_space_header : v$temp_space_header

一皮壁、 查詢表空間剩余容量

SET lines 2000
SET pagesize 2000

SELECT tablespace_name,
       Count(*)                           AS extends,
       Round(Sum(bytes) / 1024 / 1024, 2) AS mb,
       Sum(blocks)                        AS blocks
FROM   dba_free_space
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

二、 查詢表空間理論大小

SET linesize 200

SELECT tablespace_name,
       Sum(bytes) / 2014 / 1024 AS mb
FROM   v$datafile_header
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

或者

SET linesize 200

SELECT tablespace_name,
       Sum(bytes) / 2014 / 1024 AS mb
FROM   dba_data_files
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

三虑瀑、 查詢表空間真實(shí)大小

SET lines 2000
SET pagesize 2000

SELECT tablespace_name,
       Round(Sum(user_bytes) / 1024 / 1024, 2) AS mb
FROM   dba_data_files
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

四滴须、 查詢表空間已用大小

SET lines 200

SELECT tablespace_name,
       Sum(bytes) / 1024 / 1024 AS mb
FROM   dba_segments
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

或者

SET lines 200

SELECT tablespace_name,
       Sum(bytes) / 1024 / 1024 AS mb
FROM   dba_extents
GROUP  BY tablespace_name
ORDER  BY tablespace_name;

五、 查詢表空間使用率

MB版

SET lines 2000
SET pagesize 2000

SELECT total.tablespace_name,
       Round(total.mb, 2)           AS total_mb,
       Round(total.mb - free.mb, 2) AS used_mb,
       Round(free.mb, 2)            AS free_mb,
       Round(( 1 - free.mb / total.mb ) * 100, 2)
       || '%'                       AS used_pct
FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS mb
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(user_bytes) / 1024 / 1024 AS mb
        FROM   dba_data_files
        GROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name
ORDER  BY tablespace_name;

GB版

SET lines 2000
SET pagesize 2000

SELECT total.tablespace_name,
       Round(total.gb, 2)           AS total_gb,
       Round(total.gb - free.gb, 2) AS used_gb,
       Round(free.gb, 2)            AS free_gb,
       Round(( 1 - free.gb / total.gb ) * 100, 2)
       || '%'                       AS used_pct
FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 / 1024 AS gb
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(user_bytes) / 1024 / 1024 / 1024 AS gb
        FROM   dba_data_files
        GROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name
ORDER  BY tablespace_name;

六、表空間相關(guān)操作

增加表空間文件個(gè)數(shù)

alter tablespace tbs_name add datafile '+data01' size 16g;

刪除表空間

drop tablespace tbs_name including contents and datafiles;

七铭污、注意事項(xiàng)

12c的數(shù)據(jù)庫，將dba改成cdb