oracle 表空間

SELECT a.tablespace_name "表空間名",
       total "表空間大小",
       free "表空間剩餘大小",
       (total - free) "表佔用空間大小",
       ROUND((total - free) / total * 100, 2) || '%' "已使用空間百分比"
  FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_data_files
         GROUP BY tablespace_name) 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
 ORDER BY (total - free) DESC;

二、Oracle查詢表佔用的空間

SELECT segment_name "表名",
       segment_type "物件型別",
       sum(bytes) / 1024 / 1024 "佔用空間（MB）"
  FROM dba_extents
 WHERE segment_name = '表名'
 GROUP BY segment_name, segment_type;

三、Oracle查詢表空間使用情況

SELECT tablespace_name,
       file_id,
       bytes,
       blocks,
       maxbytes
  FROM dba_data_files;

四、Oracle查詢每張表佔用空間

SELECT owner,
       segment_name,
       segment_type,
       sum(bytes) / 1024 / 1024 "佔用空間（MB）"
  FROM dba_segments
 WHERE owner = '表所有者'
 GROUP BY owner, segment_name, segment_type;

五、Oracle表空間大小查詢

SELECT tablespace_name,
       SUM(bytes) / 1024 / 1024 "佔用空間（MB）"
  FROM dba_segments
 WHERE segment_type IN ('TABLE', 'INDEX')
 GROUP BY tablespace_name;

六、Oracle查表空間大小

SELECT tablespace_name,
       SUM(bytes) / 1024 / 1024 "佔用空間（MB）"
  FROM dba_segments
 WHERE tablespace_name = '表空間名'
 GROUP BY tablespace_name;

七、Oracle查詢表空間大小

SELECT tablespace_name,
       sum(bytes) / 1024 / 1024 "佔用空間（MB）"
  FROM dba_data_files
 GROUP BY tablespace_name;

八、檢視Oracle各個表佔空間

SELECT owner,
       segment_name,
       segment_type,
       format_number(bytes / (1024 * 1024), 2) "已用空間（MB）",
       format_number(maxbytes / (1024 * 1024), 2) "最大空間（MB）"
  FROM dba_segments
 WHERE owner = '表所有者'
 ORDER BY bytes DESC;

select TABLE_NAME,
    COLUMN_NAME,
    SEGMENT_NAME 
 from dba_lobs 
-- 對應的上面語句查到的segment_name名稱，如SYS_LOB0000152921C00008$$
 where SEGMENT_NAME='segment_name名稱';

九、Oracle檢視錶空間大小

SELECT tablespace_name,
       sum(blocks) * 8192 / 1024 / 1024 "表空間大小（MB）",
       sum(blocks) * 8192 / 1024 / 1024 - sum(bytes) / 1024 / 1024 "剩餘空間（MB）",
       sum(bytes) / 1024 / 1024 "已用空間（MB）",
       sum(MAXBYTES) / 1024 / 1024 "最大空間（MB）"
  FROM (SELECT tablespace_name,
               file_id,
               MAX(blocks) MAXBLOCKS,
               SUM(blocks) blocks
          FROM dba_extents
         GROUP BY tablespace_name, file_id),
       (SELECT tablespace_name, file_id, SUM(bytes) bytes
          FROM dba_free_space
         GROUP BY tablespace_name, file_id)
 WHERE tablespace_name NOT LIKE 'UNDO%'
   AND tablespace_name NOT LIKE 'TEMP%'
   AND tablespace_name NOT LIKE 'SYSAUX%'
   AND tablespace_name NOT LIKE 'SYSTEM%'
   AND tablespace_name NOT LIKE 'RMAN%'
   AND tablespace_name NOT LIKE 'FLASH%'
 GROUP BY tablespace_name;