Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【Oracle】Oracle的一些监控语句

29 Mar 2019 » Oracle

Oracle的一些监控语句

0.查看Oracle数据的大小

select b.name,
( select sum(bytes)/1024/1024/1024 DF_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 TF_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 RLF_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CF_size from v$controlfile) "Size in GB"
from  dual,  (select name from v$database ) b;

上述命令,查看日志如下:

NAME      Size in GB
--------- ----------
ORADB     47.0447388

1.Oracle数据库实际占有的大小

SQL> SELECT name,sum(bytes)/1024/1024/1024 AS "Size in GB" FROM dba_segments,
  2  (select name from v$database) group by name;

NAME      Size in GB
--------- ----------
ORADB     18.5640259

2.查看表空间的大小

#!/bin/sh
ORACLE_BASE=/u01/app 
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
ORACLE_SID=oradb
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export NLS_LANG=american_america.ZHS16GBK
export LOGNAME=./check_tablespace_`date '+%Y%m%d_%H%M%S'`.log


$ORACLE_HOME/bin/sqlplus -S /nolog << EOF
connect / as sysdba;

set line 1000
set pages 5000
col tablespace_name for a30
col file_name for a80
col free_space for 9999999
compute sum of total_space on report
compute sum of free_space on report
compute sum of MAX_SPACE on report
break on tablespace_name on report nodup

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool $LOGNAME
select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space, round(b.free_space/a.total_space *100,2) "Free%",a.max_space from (select file_id,file_name,sum(bytes)/1024/1024 total_space,sum(MAXBYTES)/1024/1024/1024 
max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a,(select file_id,nvl(sum(bytes)/1024/1024,0) free_space from dba_free_space group by file_id) b, (select tablespace_name,file_id from dba_data_file
s) c where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name;
spool off

exit;
EOF