ORACLE数据库、表空间、表的容量相关查询--1(二)

----------------------------------- ---------- --------------- ----------

/u01/oradata/bys1/users01.dbf 4 USERS 1703.75

/u01/oradata/bys1/undotbs01.dbf 3 UNDOTBS1 125

/u01/oradata/bys1/sysaux01.dbf 2 SYSAUX 670

/u01/oradata/bys1/system01.dbf 1 SYSTEM 700

/u01/oradata/bys1/example01.dbf 5 EXAMPLE 100

/u01/oradata/bys1/rmantest.dbf 6 RMANTEST 10

4.查询整个数据库的容量

数据文件大小

select sum(m) as sum_d from (select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files);

重做日志文件大小

select sum(a.members*a.m) as sum_r from (select group#,members,bytes/1024/1024 m from v$log) a;

控制文件大小

SQL> select sum(bytes_m) as sum_c from (select name,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile);

数据库总容量：

SQL> select sum_d+sum_r+sum_c as sum_database_M,sum_d as sum_datafile,sum_r as sum_redo,sum_c as sum_ctl from (select sum(m) as sum_d from (select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files)) a,(select sum(members*m) as sum_r from (select group#,members,bytes/1024/1024 m from v$log)) b,(select sum(bytes_m) as sum_c from (select name,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile)) c;

SUM_DATABASE_M SUM_DATAFILE SUM_REDO SUM_CTL

-------------- ------------ ---------- ----------

2733.75 2615.25 90 28.5