oracle user表空间使用率,查询Oracle表空间使用情况

参考文献

dba_free_space : dba_free_space

dba_data_files : dba_data_files

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;

三、 查询表空间真实大小

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;

六、表空间相关操作

增加表空间文件个数

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

删除表空间

drop tablespace tbs_name including contents and datafiles;

七、注意事项

12c的数据库，将dba改成cdb