ORACLE日常监控操作.docVIP

数据库版本信息 select * from v$version; oracle 10g oracle最多支持65536个表空间 每个表空间最多支持1022个数据文件 数据文件应该是有参数 DB_files 决定吧 最多支持多少个用户是应该是由processer参数 决定吧 查看某个表空间下有多少表 select * from all_tables where tablespace_name=TRAIN; 查看某个表空间下,某个用户有多少表 select * from all_tables where tablespace_name=TRAIN and owner=TRAIN; 数据库已经安装的产品信息 select * from v$option; 查最近一周每天的归档日志生成量 select logtime, count(*), round(sum(blocks * block_size) / 1024 / 1024) mbsize from (select trunc(first_time, dd) as logtime, a.BLOCKS, a.BLOCK_SIZE from v$archived_log a where a.DEST_ID = 1 and a.FIRST_TIME trunc(sysdate - 7)) group by logtime order by logtime desc; 查当天每小时的各个实例的归档日志生成量 select THREAD#, logtime, count(*), round(sum(blocks * block_size) / 1024 / 1024) mbsize from (select a.THREAD#, trunc(first_time, hh) as logtime, a.BLOCKS, a.BLOCK_SIZE from v$archived_log a where a.DEST_ID = 1 and a.FIRST_TIME trunc(sysdate)) group by THREAD#, logtime order by THREAD#, logtime desc; 查最近一周每天的各个实例的归档日志生成量 Sql代码 select THREAD#, logtime, count(*), round(sum(blocks * block_size) / 1024 / 1024) mbsize from (select THREAD#, trunc(first_time, dd) as logtime, a.BLOCKS, a.BLOCK_SIZE from v$archived_log a where a.DEST_ID = 1 and a.FIRST_TIME trunc(sysdate - 7)) group by THREAD#, logtime order by THREAD#, logtime desc; 无效JOB (BroKen/FAILURES)情况统计 SELECT JOB, WHAT, NEXT_DATE, BROKEN, FAILURES FROM DBA_JOBS WHERE BROKEN = Y OR FAILURES 0 无效对象情况统计 SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME FROM DBA_OBJECTS WHERE STATUS = INVALID and LAST_DDL_TIMEto_dateyyyymmdd) 无效索引情况统计 set linesize 300 col index_name format a30 col owner format a10 col table_name format a30 col tablesapce_name for