查看表空间离不开一般需求是查看表空间的名字,大小,使用率和空闲大小
一般最常见的表就是 dba_tablespaces,dba_data_files,dba_free_space,dba_temp_files,dba_temp_free_space;
经总结和借鉴,常用的sql语句如下
经总结和借鉴,常用的sql语句如下
1. 查看表空间总大小(包含temp表空间)
select * from (
Select a.tablespace_name "表空间名",
to_char(a.bytes/1024/1024,'99,999.99') "表空间大小(M)",
to_char(b.bytes/1024/1024,'99,999.99') "剩余空间大小(M)",
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.99') "使用空间大小(M)",
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' "使用比"
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name ,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name "表空间名",
to_char(c.bytes/1024/1024,'99,999.99') "表空间大小(M)",
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.99') "剩余空间大小(M)",
to_char(d.bytes_used/1024/1024,'99,999.99') "使用空间大小(M)",
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' "使用比"
from
(select tablespace_name ,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
);
select * from (
Select a.tablespace_name "表空间名",
to_char(a.bytes/1024/1024,'99,999.99') "表空间大小(M)",
to_char(b.bytes/1024/1024,'99,999.99') "剩余空间大小(M)",
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.99') "使用空间大小(M)",
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' "使用比"
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name ,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name "表空间名",
to_char(c.bytes/1024/1024,'99,999.99') "表空间大小(M)",
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.99') "剩余空间大小(M)",
to_char(d.bytes_used/1024/1024,'99,999.99') "使用空间大小(M)",
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' "使用比"
from
(select tablespace_name ,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
);
2.查看表空间所属的数据文件及是否可自动扩展
select file_name,tablespace_name,autoextensible from dba_data_files;
3.查看各用户拥有的表空间大小
select owner,tablespace_name ,sum(bytes)/1024/1024 from dba_segments where owner='SYS' group by owner,tablespace_name;