查看数据库及文件大小

查看数据库及文件大小
标签： oracle 数据空间 数据文件 杂谈 分类： oracle实验笔记

–查看数据库大小
select sum(SumMB)/1024,sum(usedMB)/1024,sum(freeMB)/1024 ,(sum(usedMB)/1024+sum(freeMB)/1024)from (
select a.tablespace_name,
a.bytes / 1024 / 1024 SumMB,
(a.bytes - b.bytes) / 1024 / 1024 usedMB,
b.bytes / 1024 / 1024 freeMB,
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 );

–查看表空间使用情况

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 ;

select * from dba_free_space;

select a.tablespace_name ,count(1) 碎片量 from
dba_free_space a, dba_tablespaces b
where a.tablespace_name =b.tablespace_name
group by a.tablespace_name
having count(1) >5
order by 2;

select
tablespace_name,
sum(bytes) 总字节数,
max(bytes),
count(*)
from dba_free_space
group by tablespace_name;

–表空间碎片整理语法：
–alter tablespace exch coalesce;

–回滚段命中率
select
rn.name,
rs.gets 被访问次数,
rs.waits 等待回退段块的次数,
(rs.waits/rs.gets)*100 命中率
from v$rollstat rs,v$rollname rn

–可以查看该表空间总共有几个数
–据文件，每个数据文件是否自动扩展，可以自动扩展的最大值。
select
file_id,
file_name,
tablespace_name,
autoextensible,increment_by,
bytes/1024/1024 “bytes MB”,
maxbytes/1024/1024 “maxbytes MB” ,
bytes/maxbytes
from dba_data_files
order by file_id desc;