表空间与数据文件监控

来源：互联网发布：网站权限数据库设计编辑：程序博客网时间：2024/05/18 02:18

1. 查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size  from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

2. 查看表空间使用率

select a.tablespace_name,       a.bytes / 1024 / 1024 "Sum MB",       (a.bytes - b.bytes) / 1024 / 1024 "used MB",       b.bytes / 1024 / 1024 "free MB",       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"  from (select tablespace_name, sum(bytes) bytes          from dba_data_files         group by tablespace_name) a,       (select tablespace_name, sum(bytes) bytes, max(bytes) largest          from dba_free_space         group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by ((a.bytes - b.bytes) / a.bytes) desc;

3. 查看表空间对应数据文件的名称及大小

select tablespace_name,       file_id,       file_name,       STATUS,       ONLINE_STATUS,       round(bytes / (1024 * 1024), 0) total_space  from dba_data_files order by tablespace_name;

4. 查看回滚段名称及大小

select tablespace_name,       file_id,       file_name,       STATUS,       ONLINE_STATUS,       round(bytes / (1024 * 1024), 0) total_space  from dba_data_files order by tablespace_name;

1 0