主页 / dba / 问题 / 139200
Accepted
r0tt
Asked: 2016-05-23 04:40:46 +0800 CST

我的oracle数据库有多大？

  • 772

我需要确定 oracle 数据库的总大小。关于改进以下查询的想法？

SELECT 'DataFiles: ' AS TYPE, SUM (bytes / 1024 / 1024) AS MB
  FROM dba_data_files
UNION
SELECT 'Temp.: ', SUM (bytes / 1024 / 1024) AS MB FROM dba_temp_files
UNION
SELECT 'Redologs: ', SUM (bytes / 1024 / 1024) AS MB FROM v$log;
oracle database-size

1 个回答

  1. Best Answer

    重做日志可以镜像，你的查询没有考虑到这一点。控制文件也是数据库的一部分。

    select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all
select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all
select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Controlfiles', sum(block_size * file_size_blks)/1024/1024 from v$controlfile;

    还有其他不重要但需要空间的文件，例如备用重做日志和块更改跟踪文件：

    select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all
select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all
select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Controlfiles', sum(block_size * file_size_blks)/1024/1024 from v$controlfile union all
select 'Standby logs', sum(bytes)/1024/1024 from v$standby_log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Change tracking', bytes/1024/1024 from v$block_change_tracking;
    • 1

相关问题