查询所有表空间的大小

 

--  查询所有标空间的大小 (sys 用户有此权限)
SELECT a.tablespace_name "Tablespace_name",
       total/(1024*1024) as "TotalSize (MB)",
       free/(1024*1024)  as "FreeSize (MB)",
       (total - free)/(1024*1024)  as "UseSize(MB)",
       ROUND((total - free) / total, 4) * 100 "UserPersent %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM DBA_FREE_SPACE
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM DBA_DATA_FILES
         GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
--  and a.tablespace_name = 'XXX'  -- 查询具体哪个表空间的大小

--  查询临时表空间的大小 (sys 用户有此权限)
SELECT a.tablespace_name as "Tablespace_name",
       total/(1024*1024) as "TotalSize (MB)",
       (total-used)/(1024*1024) as "FreeSize (MB)",
       used/(1024*1024) as "UseSize(MB)",
       ROUND(used / total, 4) * 100 "UserPersent (%)"
  FROM (SELECT tablespace_name, SUM(bytes_used) used
          FROM v$temp_space_header
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_temp_files
         GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name