资源描述：
《oracle日常监控操作》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、数据库版本信息select*fromv$version;oracle10goracle最多支持65536个表空间每个表空间最多支持1022个数据文件数据文件应该是有参数DB_files决定吧最多支持多少个用户是应该是由processer参数决定吧查看某个表空间下有多少表select*fromall_tableswheretablespace_name='TRAIN';查看某个表空间下,某个用户有多少表select*fromall_tableswheretablespace_name='TRAIN'andowner
2、='TRAIN';数据库已经安装的产品信息select*fromv$option;查最近一周每天的归档日志生成量selectlogtime,count(*),round(sum(blocks*block_size)/1024/1024)mbsizefrom(selecttrunc(first_time,'dd')aslogtime,a.BLOCKS,a.BLOCK_SIZEfromv$archived_logawherea.DEST_ID=1anda.FIRST_TIME>trunc(sysdate-7))gro
3、upbylogtimeorderbylogtimedesc;查当天每小时的各个实例的归档日志生成量selectTHREAD#,logtime,count(*),round(sum(blocks*block_size)/1024/1024)mbsizefrom(selecta.THREAD#,trunc(first_time,'hh')aslogtime,a.BLOCKS,a.BLOCK_SIZEfromv$archived_logawherea.DEST_ID=1anda.FIRST_TIME>trunc(sys
4、date))groupbyTHREAD#,logtimeorderbyTHREAD#,logtimedesc;查最近一周每天的各个实例的归档日志生成量Sql代码selectTHREAD#,logtime,count(*),round(sum(blocks*block_size)/1024/1024)mbsizefrom(selectTHREAD#,trunc(first_time,'dd')aslogtime,a.BLOCKS,a.BLOCK_SIZEfromv$archived_logawherea.DEST_
5、ID=1anda.FIRST_TIME>trunc(sysdate-7))groupbyTHREAD#,logtimeorderbyTHREAD#,logtimedesc;无效JOB(BroKen/FAILURES)情况统计SELECTJOB,WHAT,NEXT_DATE,BROKEN,FAILURESFROMDBA_JOBSWHEREBROKEN='Y'ORFAILURES>0无效对象情况统计SELECTOWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIMEFROMDBA_OBJ
6、ECTSWHERESTATUS='INVALID'andLAST_DDL_TIME>to_date('20100101','yyyymmdd')无效索引情况统计setlinesize300colindex_nameformata30colownerformata10coltable_nameformata30coltablesapce_nameformata20selectindex_name,owner,table_name,tablespace_namefromdba_indexeswhereownernot
7、in('SYS','SYSTEM')andstatus!='VALID';无效约束情况统计SELECTowner,constraint_name,table_name,constraint_type,statusFROMdba_constraintsWHEREstatus='DISABLED'所有在线实例区情况统计SELECTinst_id,instance_numberinst_no,instance_nameinst_name,parallel,status,database_statusdb_status,
8、active_statestate,host_namehostFROMgv$instanceORDERBYinst_id数据库SID，创建时间，日志归档模式selectname,created,log_modefromv$database;数据库总数据量情况统计selectround(sum(space))all_space_Mfrom(selectsum(bytes)/
