setverifyon;settimingon;setechooncolnumfor99999;coltypefor99;coldisplay_valuefora50;colvaluefora50;colnamefora35;breakonownerontable_ownerontable_name;defineuser="'ZUMINGDATA'";setechoon;settrimspoolon;--检查数据库的归档情况。--检查数据库的日志情况。setnum20;colgroup#for99;colthread#for99;colmembersfor99;colblocksizefor999;colblocksizefor999;colsequence#for99999;colbytesfor9999999999;colarcfora5;colstatusfora10;colfirst_timefora20;colnext_timefora20;colmemberfora50;altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';altersessionsetnls_timestamp_tz_format='yyyy-mm-ddhh24:mi:ss';selectsysdatefromdual;archiveloglist;setline999;setpagesize203;colnamefora10;collog_modefora12;colopen_modefora10;coldatabase_rolefora10;coldatabase_rolefora10;colprotection_modefora20;colswitchover_statusfora20;colforce_loggingfora15;colflash_onfora8;coldb_unique_namefora10;colplatform_namefora20;--查看数据库信息selectname,log_mode,open_mode,database_role,flashback_onflash_on,protection_mode,switchover_status,force_loggingforce_logging,platform_name,db_unique_namefromv$database;--系统配置colstat_namefora30;colvaluefor99999999999999999999colcommentsfora70;selectstat_name,value,comments,cumulativefromv$osstat;--查看实例信息colhost_namefora10;colshutdown_pendingfora20;colrestricted_modefora20;colblockedfora20;colinst_namefora60;select*fromv$active_instances;selectinstance_number,instance_name,host_name,version,startup_time,status,thread#,loginsfromgv$instance;selectinstance_number,shutdown_pending,database_status,instance_role,active_staterestricted_mode,blockedfromgv$instance;--查看非默认参数值colnamefora40;colvaluefora60;colinst_idfor9999999coldisplay_valuefora60;selectinst_id,num,name,type,value,display_valuefromgv$system_parameterwhereisdefault='FALSE'orderbyname,inst_id;--查看PGA建议--查看SGA建议setnum16colpval2fora30;select*fromgv$pga_target_advice;select*fromgv$sga_target_advice;select*fromgv$memory_target_advice;select*fromsys.aux_stats$;--检查数据库的控制文件情况。colnamefora60;selectstatus,namefromv$controlfile;select*fromv$logorderbygroup#;select*fromv$logfileorderbygroup#,member;--数据库限制资源使用情况colinst_idfor99;colresource_namefora30;colinitial_allocationfora30;collimit_valuefora20;select*fromgv$resource_limitorderby2,1;--检查ASM的磁盘使用情况selectname,total_mb,free_mbfromv$asm_diskgroup_statorderby1;--表空间coltablespace_namefora30;colblock_sizefor99999;colstatusfora8;colcontentsfora10colextent_managementfora20;colallocation_typefora15;colplugged_infora10;colsegment_space_managementfora25;colcompress_forfora15;colretentionfora12;selecttablespace_name,block_size,status,contents,--logging,force_logging,extent_management,allocation_type,segment_space_management,def_tab_compression,retention,compress_forfromdba_tablespacesorderbytablespace_name;--检查表空间的空闲空间coltablespace_namefora30;selectt.tablespace_name,total,free,round(100*(1-(free/total)),3)||'%'"USED_PERCENT"from(selecttablespace_name,sum(bytes)/1024/1024totalfromdba_data_filesgroupbytablespace_name)t,(selecttablespace_name,sum(bytes)/1024/1024freefromdba_free_spacegroupbytablespace_name)fwheret.tablespace_name=f.tablespace_name(+)orderbyround(100*(1-(free/total)),3);--检查数据磁盘空间的使用情况selecttotal_gb,free_gb,total_gb-free_gbused_gbfrom(select(selectround(sum(t.bytes)/1024/1024/1024,2)fromdba_data_filest)total_gb,(selectround(sum(f.bytes)/1024/1024/1024,2)fromdba_free_spacef)free_gbfromdual);--占用SYSAUX表空间的对象属性colspace_usage_kbytesfor999999999999;coloccupant_namefora30;selectspace_usage_kbytes,occupant_name,occupant_descfromv$sysaux_occupantsorderby1desc;--SYSAUX表空间上AWR信息的保存时常coltopnsqlfor99999;colretentionfora20;colsnap_intervalfora20;select*fromdba_hist_wr_control;--SYSAUX表空间上统计信息的保存时常selectdbms_stats.get_stats_history_retentionfromdual;--UNDO表空间的使用情况coltablespace_namefora20;colstatusfora10;selecttablespace_name,status,sum(bytes)/1024/1024mfromdba_undo_extentsgroupbytablespace_name,statusorderby3;--UNDO段的使用情况colwaitsfor9999;colshrinksfor9999;selectd.segment_name,d.tablespace_name,s.waits,s.shrinks,s.wraps,s.statusfromv$rollstats,dba_rollback_segsdwheres.usn=d.segment_idorderby1;--临时表空间的尺寸selecttablespace_name,sum(bytes)/1024/1024mbfromdba_temp_filesgroupbytablespace_name;--临时表空间的最高水位(=单次使用的最大值)selecttablespace_name,sum(bytes_cached)/1024/1024mbfromv$temp_extent_poolgroupbytablespace_name;--临时表空间当前使用情况selectss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024mbfromgv$sort_segmentss,sys.ts$tswheress.tablespace_name=ts.namegroupbyss.tablespace_name;--数据文件的尺寸colmfor99999;colfile_idfor9999;colfile_namefora80;coltablespace_namefora30;selecttablespace_name,file_id,bytes/1024/1024m,increment_by,file_namefromdba_data_filesorderbytablespace_name,file_id;--数据文件的扩展情况selecttablespace_name,file_id,file_name,increment_byfromdba_data_fileswhereautoextensible='YES'orderbyfile_id;--数据文件的状态selectstatus,online_statusfromdba_data_filesgroupbystatus,online_status;selecttablespace_name,file_id,online_status,file_namefromdba_data_fileswhereonline_status='OFFLINE'orderbytablespace_name,file_id;--使用SYSTEM表空间的用户selectowner,segment_name,segment_typefromdba_segmentswheretablespace_name='SYSTEM'andownernotin('SYS','SYSTEM','OUTLN');--没被锁的用户信息colusernamefora20;coltemp_tsfora20;selectprofile,password_versions,authentication_typefromdba_userswhereaccount_statusnotlike'%LOCKED%'orderbyaccount_status,user_id;--使用默认密码的账户（没修改过密码）colusernamefora30;colprofilefora10;colaccount_statusfora20;coldefault_tablespacefora20;coltemporary_tablespacefora20;selectb.username,created,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace,profile,password_versionsfromdba_usersa,dba_users_with_defpwdbwherea.username=b.username--andaccount_statusnotlike'%LOCKED%'orderbya.created,username;--使用的区间大于100的段的情况colownerfora10;colsegment_namefora30;colextentsfor999999;col"BYTESM"for999999;col"TOTALBLOCKS"for99999999;selectowner,segment_type,segment_name,tablespace_name,count(blocks)"EXTENTS",sum(bytes/1024/1024)"BYTESM",sum(blocks)"TOTALBLOCKS"fromdba_extentswhereownerin(&user)groupbyowner,segment_type,segment_name,tablespace_namehavingcount(*)>100orderbyowner,segment_type,5;--下次分配的区间大于当前最大空闲块的段。setpagesize200;colownerfora10;colsegment_namefora30;selects.owner,s.segment_name,s.segment_type,t.num_rows,t.last_analyzed,s.tablespace_name,e.curr_extent,s.next_extentfromdba_segmentss,(selectowner,table_name,num_rows,last_analyzedfromdba_tablesunionallselectowner,index_name,num_rows,last_analyzedfromdba_indexes)t,(select*from(selectowner,segment_name,bytescurr_extent,row_number()over(partitionbyowner,segment_nameorderbyextent_iddesc)seqfromdba_extents)whereseq=1)ewheres.ownerin(&user)ands.owner=t.owner(+)ands.segment_name=t.table_name(+)ands.owner=e.owner(+)ands.segment_name=e.segment_name(+)ands.next_extent>(selectmax(f.bytes)fromdba_free_spacefwheref.tablespace_name=s.tablespace_name)ands.ownernotin('SYS','SYSTEM','CTXSYS','MDSYS','OUTLN','ORDSYS','ORDPLUSING','OWAPUB')orderbys.owner,s.segment_type,s.next_extent;--execdbms_utility.compile_schema('',FALSE);--各作业的运行情况coljobfor9999;collog_userfora10;colpriv_userfora10;colschema_userfora10;collast_datefora20;collast_secfora20;colthis_datefora20;colnext_datefora20;colwhatfora50;colfailuresfor999;selectjob,log_user,priv_userschema_user,last_date,this_date,next_date,what,failuresfromdba_jobswherelog_userin(&user)orderbylog_user,job;colownerfora10;coljob_namefora30;collog_datefora20;collast_start_datefora20;colnext_run_datefora20;collast_run_durationfora30;colstatefora15;coladditional_infofora50;selectowner,job_name,failure_countfailures,to_char(last_start_date,'yyyy-mm-ddhh24:mi:ss')last_start_date,to_char(next_run_date,'yyyy-mm-ddhh24:mi:ss')next_run_date,last_run_duration,state,run_countfromdba_scheduler_jobswherestate<>'DISABLED'andstate<>'COMPLETED'andownernotin('EXFSYS')orderbyowner,job_name;/*--指定作业的历史运行情况collog_datefora20;colreq_start_datefora20;colactual_start_datefora20;colrun_durationfora15;colstatusfora10;coladditional_infofora50;selectlog_date,req_start_date,actual_start_date,run_duration,status,additional_infofromdba_scheduler_job_run_detailswherejob_name=upper(trim('$job_name'))orderby1,2;*/--库的各对象的状态colobject_typefora20;selectowner,object_type,status,count(*)fromdba_objectswhereownerin(&user)groupbyowner,object_type,statusorderby1,3desc,2;--最近10天内object_id序列号被使用的情况select*from(selecttrunc(created),max(object_id),min(object_id),(max(object_id)-min(object_id))dtfromuser_objectsgroupbytrunc(created)orderby1desc)whererownum<=10;--对象不存在的同义词coltable_namefora30select*fromdba_synonymsawhereownerin(&user)anddb_linkisnullandnotexists(select'x'fromdba_objectswhereowner=nvl(a.table_owner,a.owner)andobject_name=a.table_name)orderbyowner,synonym_name;--各索引的状态colownerfora10colindex_ownerfora10coltable_namefora20selectowner,status,count(*)fromdba_indexeswhereownerin(&user)groupbyowner,statusorderbyowner,status;selectindex_owner,status,count(*)fromdba_ind_partitionswhereindex_ownerin(&user)groupbyindex_owner,statusorderbyindex_owner,status;selectindex_owner,status,count(*)fromdba_ind_subpartitionswhereindex_ownerin(&user)groupbyindex_owner,statusorderbyindex_owner,status;--主键，唯一性约束使用非唯一性索引selecta.owner,a.index_name,a.uniqueness,b.constraint_typefromdba_indexesa,dba_constraintsbwhereb.constraint_typein('P','U')anda.owner=b.owneranda.index_name=b.index_nameanda.uniqueness='NONUNIQUE'anda.ownerin(&user)orderbya.owner,a.index_name;--不可用的索引selecta.owner,a.table_name,a.index_name,a.pname,b.last_ddl_timefrom(selectowner,table_name,index_name,'PART_NAME'pname,statusfromdba_indexesunionallselectindex_ownerowner,'TNAME',index_name,partition_namepname,statusfromdba_ind_partitionsunionallselectindex_ownerowner,'TNAME',index_name,subpartition_namepname,statusfromdba_ind_subpartitions)ajoindba_objectsbona.index_name=b.object_nameanda.owner=b.ownerwherea.ownerin(&user)anda.status='UNUSABLE'andb.object_type='INDEX'orderbya.owner,a.table_name,a.index_name;--位图索引selectowner,table_name,index_namefromdba_indexeswhereownerin(&user)andindex_type='BITMAP'orderbyowner,table_name,index_name;--被屏蔽的索引selecta.owner,a.table_name,a.index_name,a.partitioned,b.last_ddl_timefromdba_indexesajoindba_objectsbona.index_name=b.object_nameanda.owner=b.ownerwherea.ownerin(&user)anda.visibility='INVISIBLE'andb.object_type='INDEX'orderbya.owner,a.table_name,a.index_name;--没索引的外键colr_ownerfora10coltable_namefora30colconstraint_namefora30colr_constraint_namefora30colindex_namefora30selectowner,table_name,constraint_name,status,r_owner,r_constraint_name,index_owner,index_namefromdba_constraintswhereownerin(&user)andconstraint_type='R'andSTATUS='ENABLED'orderbyowner,table_name;--5个以上索引的表breakont_rowsonowner;selectowner,table_name,t_rows,index_name,num_rowsi_rows,partitioned,status,row_number()over(partitionbytable_nameorderbypartitioned,index_name)seqfromdba_indexesa,(selectownerb_owner,table_nameb_tname,num_rowst_rowsfromdba_tables)bwherea.ownerin(&user)anda.owner=b.b_owneranda.table_name=b.b_tnameand(a.owner,a.table_name)in(selectowner,table_namefromdba_indexeshavingcount(*)>5groupbyowner,table_name)andto_char(sysdate,'d')=1orderbyowner,table_name,seq;--4个或以上字段的索引colindex_ownerfora5;coltable_namefora30;colindex_namefora30;colcolumn_namefora30;breakonownerontable_nameonindex_nameont_rowsoni_rows;selecta.index_ownerowner,a.table_name,c.t_rows,a.index_name,c.i_rows,a.column_name,a.column_positionfromdba_ind_columnsa,(selectindex_owner,table_name,index_namefromdba_ind_columnsgroupbyindex_owner,table_name,index_namehavingcount(*)>=4)b,(selecta.owner,a.table_name,a.num_rowst_rows,b.index_name,b.num_rowsi_rowsfromdba_tablesa,dba_indexesbwherea.owner=b.owneranda.table_name=b.table_namegroupbya.owner,a.table_name,a.num_rows,b.index_name,b.num_rows)cwherea.index_ownerin(&user)anda.index_owner=b.index_owneranda.index_owner=c.owneranda.table_name=b.table_nameanda.index_name=b.index_nameanda.index_name=c.index_nameanda.table_name=c.table_nameandto_char(sysdate,'d')=1orderbya.index_owner,a.table_name,a.index_name,a.column_position;--所有索引colindex_columnsfora80;breakonownerontable_name;selecta.owner,a.table_name,a.t_rows,a.index_name,a.i_rows,listagg(column_name,',')withingroup(orderbycolumn_position)index_columnsfrom(selecta.owner,a.table_name,a.num_rowst_rows,b.index_name,b.num_rowsi_rowsfromdba_tablesa,dba_indexesbwherea.owner=b.owneranda.table_name=b.table_namegroupbya.owner,a.table_name,a.num_rows,b.index_name,b.num_rows)a,dba_ind_columnsbwherea.ownerin(&user)anda.owner=b.index_owneranda.index_name=b.index_name--andexists(select'x'fromdualwheretrunc(sysdate)=trunc(last_day(add_months(sysdate,-1))+1))andto_char(sysdate,'d')=1groupbya.owner,a.table_name,a.t_rows,a.index_name,a.i_rowsorderbya.owner,a.table_name,a.index_name;--统计约束的状态colsearch_conditionfora30;selectstatus,count(*)fromdba_constraintswhereownerin(&user)groupbystatus;--查看不可用，无效的状态selecttable_name,constraint_name,constraint_type,a.status,search_condition,last_ddl_timefromdba_constraintsa,dba_objectsbwhere(a.status='DISABLED'ora.status='INVALID')anda.ownerin(&user)anda.owner=b.ownerandb.object_name=a.constraint_nameorderbya.owner,table_name;colsqlfora120;select'altertable'||a.owner||'.'||a.table_name||'enablevalidateconstraint'||a.constraint_name||';'sqlfromdba_constraintsa,dba_tablesbwherea.ownerin(&user)anda.table_name=b.table_nameand(a.status='DISABLED'ora.validated='NOTVALIDATED')orderbya.owner,a.table_name;--各表的统计信息的状态colownerfora15;coltable_namefora30;selectowner,stattype_locked,count(*)fromdba_tab_statisticswhereownerin(&user)groupbyowner,stattype_lockedorderbystattype_locked,owner;--统计信息被锁的普通表selecta.owner,a.table_name,b.temporary,a.stattype_locked,c.created,c.last_ddl_timefromdba_tab_statisticsa,(selectowner,table_name,temporaryfromdba_tableswheretable_namenotlike'MLOG$%')b,(selectowner,object_name,created,last_ddl_timefromdba_objects)cwherea.ownerin(&user)anda.owner=b.owneranda.table_name=b.table_nameanda.owner=c.owneranda.table_name=c.object_nameanda.stattype_locked='ALL'orderbyowner,a.table_name;--表的统计分析情况。selectowner,max(last_analyzed)max,min(last_analyzed)minfromdba_tableswhereownerin(&user)andtemporary='N'andtable_namenotlike'MLOG%'groupbyownerorderbyowner;--50个最早统计分析的表。selectowner,table_name,last_analyzed,num_rows,seqfrom(selectowner,table_name,last_analyzed,num_rows,row_number()over(partitionbyownerorderbylast_analyzed)seqfromdba_tableswhereownerin(&user)andtemporary='N'andtable_namenotlike'MLOG%')whereseq<=50orderbyowner,seq;--统计信息陈旧的表。colownerfora10;colpnamefora13;colobject_typefora15;colnum_rowsfor999999999;colblocksfor999999999;colchain_cntfor999999999;colsample_sizefor999999999;selectowner,table_name,partition_namepname,subpartition_namesubpname,num_rows,blocks,chain_cnt,sample_size,last_analyzedfromdba_tab_statisticswhereownerin(&user)andstale_stats='YES'orderbyowner,table_name;--查看当前用户有多少临时表selectowner,count(*)fromdba_tableswhereownerin(&user)andtemporary='Y'groupbyownerorderbyowner;--查看被采集过信息的临时表。selectowner,table_name,last_analyzedfromdba_tableswhereownerin(&user)andtemporary='Y'andlast_analyzedisnotnullorderbyowner,table_name;--未创建存储段的表colpartitionedfora12;colsegment_createdfora16;coltotalfor99999;selectowner,partitioned,segment_created,count(*)totalfromdba_tableswhereownerin(&user)groupbyowner,segment_created,partitionedorderbyowner,partitioned,segment_created;selectowner,table_name,(selectcreatedfromdba_objectswhereobject_name=a.table_nameandowner=a.ownerandobject_type='TABLE')createdfromdba_tablesawhereownerin(&user)andsegment_created='NO'andto_char(sysdate,'d')=1orderbyowner,table_name;--找出所有数据不能移动的分区表select'ALTERTABLE'||owner||'.'||table_name||'enablerowmovement;'fromdba_tableswherepartitioned='YES'androw_movement='DISABLED'andownerin(&user)orderbyowner,1;--查看参照当前库的物化视图的刷新情况colownerfora20;colowner2fora20;colnamefora30;colmview_sitefora30;colmasterfora30;colmview_idfor999999;selecta.*,b.ownerowner2,b.name,b.can_use_log,b.refresh_method,b.mview_sitefromdba_base_table_mviewsafulljoindba_registered_mviewsbona.mview_id=b.mview_idorderbya.owner,b.mview_site,a.mview_last_refresh_timedesc;--物化视图日志最后的刷新时间colmview_namefora30;colcomplete_statefora15colstatenessfora15;selectowner,mview_name,last_refresh_type,last_refresh_date,compile_state,stalenessfromdba_mviewswhereownerin(&user)orderbyowner,last_refresh_datedesc;--查看尺寸大于2M的物化视图日志colsegment_namefora30;selectlog_owner,a.*,(selectlast_ddl_timefromdba_objectswhereowner=log_ownerandobject_name=log_table)last_ddl_timefrom(selectowner,segment_name,bytes/1024/1024mfromdba_segments)a,dba_mview_logsbwherea.segment_name=b.log_tableanda.owner=b.log_ownerandm>2orderbylog_owner,m,last_ddl_time,segment_name;select'altertable'||owner||'.'||log_table||'move;'from(select*fromdba_mview_logs)a,dba_segmentsbwherea.log_table=b.segment_nameandbytes/1024/1024>2orderbyowner,segment_name;--分区表及分区类型colownerfora10coltable_namefora30colcolumn_namefora20colptypefora10;colsptypefora8;colptotalfor9999999colsptotalfor9999999colppositionfor99999colstatusfora8colhigh_valuefora30coldef_tablespace_namefora25;colref_ptn_constraint_namefora30;selecta.owner,a.table_name,a.partitioning_typeptype,listagg(b.column_name,',')withingroup(orderbyb.column_position)column_name,a.subpartitioning_typesptype,listagg(c.column_name,',')withingroup(orderbyc.column_position)column_name,a.partition_countptotal,a.def_subpartition_countsptotal,a.status,a.def_tablespace_name,a.ref_ptn_constraint_namefromdba_part_tablesa,dba_part_key_columnsb,dba_subpart_key_columnscwherea.ownerin(&user)anda.table_name=b.nameanda.table_name=c.name(+)groupbya.owner,a.table_name,a.partitioning_type,a.subpartitioning_type,a.partition_count,a.def_subpartition_count,a.status,a.def_tablespace_name,a.ref_ptn_constraint_nameorderby1,2;--不规范的分区类型(range(有MAX分区),hash(2的N次幂),list(有DEFAULT分区))colownerfora10;coltable_namefora30;colbptypefora10;colsubptypefora10;colpcountfor9999999;colsubpcountfor9999999;colpnamefora13;colsubpnamefora30;colphigh_valuefora35;colsubphigh_valuefora20;selecta.owner,a.table_name,a.partitioning_typeptype,a.partition_countpcount,a.subpartitioning_typesubptype,a.def_subpartition_countsubpcount,b.pname,b.phigh_value,b.subpname,b.subphigh_valuefromdba_part_tablesa,(selectb.table_owner,b.table_name,b.partition_namepname,b.high_valuephigh_value,c.subpartition_namesubpname,c.high_valuesubphigh_valuefrom(select*from(selecttable_owner,table_name,partition_name,high_value,row_number()over(partitionbytable_nameorderbypartition_positiondesc)max_positionfromdba_tab_partitions))b,(select*from(selecttable_owner,table_name,partition_name,subpartition_name,high_value,subpartition_position,row_number()over(partitionbytable_name,partition_nameorderbysubpartition_positiondesc)max_positionfromdba_tab_subpartitions)wheremax_position=1)cwhereb.table_owner=c.table_ownerandb.table_name=c.table_nameandb.partition_name=c.partition_nameunionallselectb.table_owner,b.table_name,b.partition_name,b.high_value,c.subpartition_name,c.high_valuefrom(select*from(selecttable_owner,table_name,partition_name,high_value,row_number()over(partitionbytable_nameorderbypartition_positiondesc)max_positionfromdba_tab_partitions)wheremax_position=1)b,(select*from(selecttable_owner,table_name,partition_name,subpartition_name,high_value,subpartition_position,row_number()over(partitionbytable_name,partition_nameorderbysubpartition_positiondesc)max_positionfromdba_tab_subpartitions)wheremax_position=1)cwhereb.table_owner=c.table_owner(+)andb.table_name=c.table_name(+)andc.table_nameisnull)bwherea.ownerin(&user)anda.owner=b.table_owneranda.table_name=b.table_nameorderbyowner,decode(subptype,'NONE',1,2),table_name,pname;--各分区表的最大分区数（不包括MAX,DEFAULT类型）coltable_ownerfora10;select*from(selecttable_owner,table_name,partition_name,partition_positionfrom(selecta.*,row_number()over(partitionbytable_nameorderbypartition_positiondesc)pseqfromdba_tab_partitionsa)wheretable_ownerin(&user)andpseq<=2)orderbytable_owner,table_name,partition_position;--查看没主键的普通表selecta.owner,table_name,last_analyzed,(selectcreatedfromdba_objectswhereobject_type='TABLE'andowner=a.ownerandobject_name=a.table_name)createdfrom(selecta.owner,a.table_name,a.last_analyzedfrom(selectowner,table_name,last_analyzedfromdba_tablesawheretemporary='N'andnotexists(select'x'fromdba_mview_logswherelog_table=a.table_nameanda.owner=log_owner))a,(selectowner,table_namefromdba_constraintswhereconstraint_type='P')bwherea.owner=b.owner(+)anda.table_name=b.table_name(+)andb.table_nameisnull)awherea.ownerin(&user)orderbyowner,createddesc;--没有索引的表colpartfora4coltmpfora4selecta.owner,a.table_name,a.partitionedpart,a.temporarytmp,a.num_rows,a.last_analyzed,c.createdfrom(select*fromdba_tablesawheretemporary='N'andownerin(&user)andnum_rowsisnotnullandnotexists(select'x'fromdba_mview_logswheretable_name=log_table))a,dba_indexesb,(select*fromdba_objectswhereobject_type='TABLE')cwhere(a.table_name=b.table_name(+)andb.table_nameisnull)and(a.owner=c.owneranda.table_name=c.object_name)orderbya.owner,a.num_rowsdesc,a.table_name;--没有索引并且不被数据库对象引用的表selecta.owner,a.table_name,a.partitionedpart,a.temporarytmp,a.num_rows,a.last_analyzed,c.createdfrom(select*fromdba_tableswheretemporary='N'andownerin(&user)andnum_rowsisnotnullandnotexists(select'x'fromdba_mview_logswheretable_name=log_table))a,dba_indexesb,(select*fromdba_objectswhereobject_type='TABLE')c,dba_dependenciesdwhere(a.table_name=b.table_name(+)andb.table_nameisnull)and(a.owner=c.owneranda.table_name=c.object_name)and(a.table_name=d.referenced_name(+)andd.referenced_nameisnull)orderbya.owner,a.num_rowsdesc,a.table_name;--回收站中的对象select*fromdba_recyclebinwhereownerin(&user)orderbyowner,droptime;--查看审计策略colobject_schemafora10;colobject_ownerfora10;colobject_textfora10;colobject_columnfora10;colpolicy_textfora10;colpf_schemafora10;colpf_packagefora10;colpf_functionfora10;select*fromdba_audit_policiesorderbyobject_schema,object_name,policy_owner,policy_name;--记录数最多的50个表colseqfor99999;colpartfora8;coltable_namefora30;select*from(selectowner,table_name,last_analyzed,num_rows,lpad(partitioned,8,'')part,row_number()over(partitionbyownerorderbynum_rowsdescnullslast)seqfromdba_tableswherenum_rows>0)whereownerin(&user)andseq<=50orderbyowner,seq;--遗留的EXPORT进程colobject_namefora30selectowner,created,object_namefromdba_objectswhereobject_namelike'SYS_EXPORT%'orderbyowner,created;--表的记录数与尺寸对应情况colsize_mfor999999.99colinit_tsfor99999.99colinit_tabfor99999.99coltable_namefora30;selecta.owner,b.segment_nametable_name,a.tablespace_name,c.init_ts,a.initial_extent/1024/1024init_tab,a.num_rows,b.size_mfromdba_tablesa,(selecttablespace_name,initial_extent/1024/1024init_tsfromdba_tablespaces)c,(selectowner,segment_name,sum(bytes)/1024/1024size_mfromdba_segmentsgroupbyowner,segment_name)bwherea.ownerin(&user)anda.owner=b.owneranda.table_name=b.segment_nameandb.size_m>2anda.num_rowsisnotnullanda.partitioned='NO'anda.tablespace_name=c.tablespace_nameandb.size_m>c.init_tsandb.size_m>a.initial_extent/1024/1024orderbya.owner,a.num_rows,b.size_m;--Recoveryinstancesetnum12;selectrecovery_estimated_ios,actual_redo_blks,target_redo_blks,log_file_size_redo_blks,log_chkpt_timeout_redo_blksfromv$instance_recovery;selectlog_chkpt_interval_redo_blks,fast_start_io_target_redo_blks,target_mttr,estimated_mttr,ckpt_block_writesfromv$instance_recovery;selectoptimal_logfile_size,estd_cluster_available_time,writes_mttr,writes_logfile_size,writes_log_checkpoint_settingsfromv$instance_recovery;selectwrites_other_settings,writes_autotune,writes_full_thread_ckptfromv$instance_recovery;--v$mttr_target_adviceselectmttr_target_for_estimatemttr_target_estimate,dirty_limit,estd_cache_writes,estd_cache_write_factor,estd_total_writes,estd_total_write_factor,estd_total_ios,estd_total_io_factorfromv$mttr_target_advicewhereadvice_status='ON'orderby1;--检查备份情况，备份速度．colstatusfora25colinput_typefora13colinput_bytes_displayfora10coloutput_bytes_displayfora10colinput_bytes_per_sec_displayfora10coloutput_bytes_per_sec_displayfora10coltime_taken_displayfora10selectstart_time,end_time,status,input_type,input_bytes_display,output_bytes_display,input_bytes_per_sec_display,output_bytes_per_sec_display,time_taken_displayfromv$rman_backup_job_detailswherestart_time>=trunc(sysdate)-90orderbystart_time;--备份效率2011-06-1415:54setnum12;selecttype,total_bytes,open_timeopen,close_timeclose,elapsed_timeelapsed,io_count,ready,long_waitswaits,long_wait_time_totaltotal,long_wait_time_maxmaxfromv$backup_async_iowhereopen_time>=trunc(sysdate)+1/4orderbyopen_time;--查看库的归档日志情况coltotalfora20;colh00for999;colh01for999;colh02for999;colh03for999;colh04for999;colh05for999;colh06for999;colh07for999;colh08for999;colh09for999;colh10for999;colh11for999;colh12for999;colh13for999;colh14for999;colh15for999;colh16for999;colh17for999;colh18for999;colh19for999;colh20for999;colh21for999;colh22for999;colh23for999;selectto_char(first_time,'mm/dd')day,sum(decode(to_char(first_time,'hh24'),'00',1,0))h00,sum(decode(to_char(first_time,'hh24'),'01',1,0))h01,sum(decode(to_char(first_time,'hh24'),'02',1,0))h02,sum(decode(to_char(first_time,'hh24'),'03',1,0))h03,sum(decode(to_char(first_time,'hh24'),'04',1,0))h04,sum(decode(to_char(first_time,'hh24'),'05',1,0))h05,sum(decode(to_char(first_time,'hh24'),'06',1,0))h06,sum(decode(to_char(first_time,'hh24'),'07',1,0))h07,sum(decode(to_char(first_time,'hh24'),'08',1,0))h08,sum(decode(to_char(first_time,'hh24'),'09',1,0))h09,sum(decode(to_char(first_time,'hh24'),'10',1,0))h10,sum(decode(to_char(first_time,'hh24'),'11',1,0))h11,sum(decode(to_char(first_time,'hh24'),'12',1,0))h12,sum(decode(to_char(first_time,'hh24'),'13',1,0))h13,sum(decode(to_char(first_time,'hh24'),'14',1,0))h14,sum(decode(to_char(first_time,'hh24'),'15',1,0))h15,sum(decode(to_char(first_time,'hh24'),'16',1,0))h16,sum(decode(to_char(first_time,'hh24'),'17',1,0))h17,sum(decode(to_char(first_time,'hh24'),'18',1,0))h18,sum(decode(to_char(first_time,'hh24'),'19',1,0))h19,sum(decode(to_char(first_time,'hh24'),'20',1,0))h20,sum(decode(to_char(first_time,'hh24'),'21',1,0))h21,sum(decode(to_char(first_time,'hh24'),'22',1,0))h22,sum(decode(to_char(first_time,'hh24'),'23',1,0))h23,count(*)||'('||trim(to_char(sum(blocks*block_size)/1024/1024,'99999,999999.999'))||'M)'TOTALfrom(selectmax(blocks)blocks,max(block_size)block_size,max(first_time)first_timefromv$archived_logawherecompletion_time>trunc(sysdate-90)groupbysequence#)groupbyto_char(first_time,'mm/dd'),to_char(first_time,'yyyy/mm/dd')orderbyto_char(first_time,'yyyy/mm/dd');coltotalclear;--最近2天的归档日志间隔时长colinst_idfor9999;colthread#for9999;colgapfor99999coldeletedfora7colfirst_timefora20colcompletion_timefora20colnamefora75;colsize_mfor999breakonfirst_dayonnext_dayoncompletion_dayselectinst_id,thread#,sequence#,first_time,dest_id,round(to_number(next_time-first_time)*86400)gap,round((blocks*block_size)/1024/1024)size_m,completion_time,deleted,namefromgv$archived_logwhereinst_id=thread#andcompletion_time>trunc(sysdate)-1orderbyfirst_time,sequence#,dest_id;--库归档日志路径的状况coldest_idfor9999;coldest_namefora20;colstatusfora8;coldestinationfora20;colerrorfora10;coldestinationfora30;collog_sequencefor9999999;colreopen_secsfor9999999;coldelay_minsfor9999999;colfailure_countfor9999999;coldb_unique_namefora15;coltypefora10;coldatabase_modefora15;colrecovery_modefora25;colschedulefora10;colstandby_logfile_countfora25;selectinst_id,dest_name,status,target,schedule,destination,log_sequence,reopen_secsfromgv$archive_destwherestatus='VALID'orderbyinst_id,dest_id;selectinst_id,dest_name,delay_mins,failure_count,error,db_unique_name,compression,applied_scnfromgv$archive_destwherestatus='VALID'orderbyinst_id,dest_id;selectinst_id,dest_id,dest_name,type,database_mode,recovery_mode,protection_modestandby_logfile_count,standby_logfile_activefromgv$archive_dest_statuswherestatus='VALID'orderbyinst_id,dest_id;selectinst_id,dest_id,dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,error,srl,synchronized,gap_statusfromgv$archive_dest_statuswherestatus='VALID'orderbyinst_id,dest_id;--查看未备份就被删除的归档日志colseqfor999;coltotalfor9999;selectthread#,row_number()over(partitionbythread#orderbymin(sequence#))seq,min(sequence#)init_log,max(sequence#)max_log,min(first_time)first_time,max(first_time)last_time,max(sequence#)-min(sequence#)+1totalfrom(selectthread#,sequence#,first_time,sequence#-seqgapfrom(selecta.thread#,a.sequence#,a.first_time,row_number()over(partitionbya.thread#orderbya.sequence#)seqfromv$archived_loga,v$backup_archivelog_detailsbwherea.completion_time>=(selectmin(start_time)fromv$backup_set_detailswhereincremental_level=0)anddest_id=1anda.deleted='YES'anda.thread#=b.thread#(+)anda.sequence#=b.sequence#(+)andb.sequence#isnull))groupbythread#,gaporderby2;--OPEN_MODEcoldb_unique_namefora20;colopen_modefora20;coldatabase_rolefora20;colswitchover_statusfora20;selectdb_unique_name,open_mode,database_role,switchover_statusfromv$database;-ORCL库的FRA使用情况setnum10;colnamefora30;selectname,space_limit/1073741824max_g,space_used/1073741824used_g,space_reclaimable/1073741824rec_g,number_of_filesfromv$recovery_file_dest;select*fromv$recovery_area_usage;--STANDBY当前应用日志的状况colarchivedfora10;colappliedfora10;select*from(selectthread#,dest_id,first_time,next_time,sequence#,completion_time,archived,applied,row_number()over(orderbynext_timedesc)seqfromv$archived_logwheredest_idin(2)/*andapplied='YES'*/)whereseq<=20orderbynext_time,completion_time;--当前连接到库的各中间键的情况colusernamefora10colmachinefora25selectusername,machine,status,count(*)fromv$sessionwhereusernameisnotnullgroupbyusername,machine,statusorderby3,1,2;selectinst_id,sessions_max,sessions_warning,sessions_current,sessions_highwater,users_max,cpu_count_currentfromgv$licenseorderbyinst_id;selectinst_id,cpu_core_count_current,cpu_socket_count_current,cpu_count_highwater,cpu_core_count_highwater,cpu_socket_count_highwaterfromgv$licenseorderbyinst_id;selectmax(count(*))fromv$open_cursorgroupbysid;--查看当前非空闲事件的会话colsidfor9999;coleventfora30;colmachinefora10;coltaddrfora15;colwait_classfora15;colmodulefora18;colsql_idfora15;colobject_namefora30;colcall_etfor999999;selectinst_id,sid,serial#,seq#,taddr,sql_id,sql_child_numbersql_child,machine,last_call_etcall_et,module,(selectobject_namefromdba_objectswherea.row_wait_obj#=object_id)object_name,event,wait_class,row_wait_file#r_w_f#,row_wait_block#r_w_b#,row_wait_row#r_w_r#,p1,p2,p3fromgv$sessionawherea.status='ACTIVE'anda.usernameisnotnullorderbylast_call_et;selectsysdatefromdual;--当前正在回滚的事务及进展情况selectusn,state,undoblockstotal,undoblocksdone,cputimefromgv$fast_start_transactions;select*fromgv$fast_start_servers;selectsysdatefromdual;--当前正在运行的定时作业的情况setline190;coljob_namefora30;coleventfora30;colcall_etfor9999999;colsession_stat_cpufora25;selectsession_id,actionjob_name,b.event,session_stat_cpu,last_call_etcall_et,(sysdate-last_call_et/86400)start_timefromv$scheduler_running_jobsa,v$sessionbwherea.session_id=b.sidorderbycall_et;--进程事件与回滚段selectse.sid,se.event,se.last_call_et,rn.usn,rn.name,rs.xacts,rs.extents,rs.hwmsizefromv$sessionse,v$transactionts,v$rollnamern,v$rollstatrswherese.status='ACTIVE'andse.taddr=ts.addrandts.xidusn=rs.usnandrn.usn=rs.usn;--查看当前空闲1小时以上的会话colterminalfora13;colmachinefora10;colmodulefora35;selectsid,serial#,terminal,machine,last_call_et,module,event,wait_class,p1,p2,p3fromgv$sessionawherea.status='INACTIVE'anda.usernameisnotnullandlast_call_et>3600orderbylast_call_et;--查看当前发送DML操作的表colnamefora30;colblocking_othersfora30;select*fromdba_dml_locks;--锁堵塞查询setnum38coltyfora5colid1for999999colid2for999999collmodefor999colrequestfor999colctimefor999999colblockfor9selectb.*,a.ctimefrom(selectid1,id2,ctimefromv$lockwhereblock=1)a,v$lockbwherea.id1=b.id1anda.id2=b.id2orderbya.ctime,b.block,b.id1,b.ctime;--持锁者查询colusernamefora10;colmachinefora10;colterminalfora10;selecta.sid,serial#,seq#,username,paddr,machine,terminal,status,last_call_et,sql_id,prev_sql_id,eventfromv$sessiona,(select/*+rule*/sidfromv$lockwhereblock=1)bwherea.sid=b.sidorderbylast_call_et;selectsysdatefromdual;exit;
