k3Cloud72 ORACLE 数据库巡检脚本.sql

set verify on;
set timing on;
set echo on
col num for 99999;
col type for 99;
col display_value for a50;
col value for a50;
col name for a35;
break on owner on table_owner on table_name;
define user = "'ZUMINGDATA'";

set echo on;
set trimspool on;

-- 检查数据库的归档情况。
-- 检查数据库的日志情况。
set num 20;
col group# for 99;
col thread# for 99;
col members for 99;
col blocksize for 999;
col blocksize for 999;
col sequence# for 99999;
col bytes for 9999999999;
col arc for a5;
col status for a10;
col first_time  for a20;
col next_time for a20;
col member for a50;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;
archive log list;

set line 999;
set pagesize 203;
col name for a10;
col log_mode for a12;
col open_mode for a10;
col database_role for a10;
col database_role for a10;
col protection_mode for a20;
col switchover_status for a20;
col force_logging for a15;
col flash_on for a8;
col db_unique_name for a10;
col platform_name for a20;
--查看数据库信息
select name,log_mode,open_mode,database_role,flashback_on flash_on,protection_mode,
       switchover_status,force_logging force_logging,platform_name,db_unique_name from v$database;

--系统配置
col stat_name for a30;
col value for 99999999999999999999
col comments for a70;
select stat_name,value,comments,cumulative from v$osstat;

--查看实例信息
col host_name for a10;
col shutdown_pending for a20;
col restricted_mode for a20;
col blocked for a20;
col inst_name for a60;
select * from v$active_instances;
select instance_number,instance_name,host_name,version,startup_time,status,thread#,logins  from gv$instance;
select instance_number,shutdown_pending,database_status,instance_role,active_state restricted_mode,blocked from gv$instance;

--查看非默认参数值
col name for a40;
col value for a60;
col inst_id for 9999999
col display_value for a60;
select inst_id, num,name,type,value,display_value from gv$system_parameter where isdefault='FALSE' order by name,inst_id;


--查看PGA建议
--查看SGA建议
set num 16
col pval2 for a30;
select * from gv$pga_target_advice;
select * from gv$sga_target_advice;
select * from gv$memory_target_advice;
select * from sys.aux_stats$;

-- 检查数据库的控制文件情况。
col name for a60;
select status, name from v$controlfile;
select * from v$log order by group#;
select * from v$logfile order by group#,member;

-- 数据库限制资源使用情况
col inst_id for 99;
col resource_name for a30;
col initial_allocation for a30;
col limit_value for a20;
select * from gv$resource_limit order by 2,1;

--检查ASM的磁盘使用情况
select name, total_mb, free_mb from v$asm_diskgroup_stat order by 1;

--表空间
col tablespace_name for a30;
col block_size for 99999;
col status for a8;
col contents for a10
col extent_management for a20;
col allocation_type for a15;
col plugged_in for a10;
col segment_space_management for a25;
col compress_for for a15;
col retention for a12;
select tablespace_name, block_size, status,contents,--logging,force_logging,
       extent_management,allocation_type,
       segment_space_management,def_tab_compression,retention,compress_for
from dba_tablespaces order by tablespace_name;

--检查表空间的空闲空间
col tablespace_name for a30;
select t.tablespace_name, total, free, round(100*(1-(free/total)),3)||'%' "USED_PERCENT"      
from (select tablespace_name, sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) t,
     (select tablespace_name, sum(bytes)/1024/1024 free from dba_free_space
     group by tablespace_name) f where t.tablespace_name=f.tablespace_name(+) order by round(100*(1-(free/total)),3);

--检查数据磁盘空间的使用情况
select total_gb, free_gb, total_gb - free_gb used_gb from 
(select (select round(sum(t.bytes)/1024/1024/1024,2) from dba_data_files t) total_gb, 
(select round(sum(f.bytes)/1024/1024/1024,2) from dba_free_space f) free_gb from dual);

--占用SYSAUX表空间的对象属性
col space_usage_kbytes for 999999999999;
col occupant_name for a30;
select space_usage_kbytes, occupant_name, occupant_desc from v$sysaux_occupants order by 1 desc;

--SYSAUX表空间上AWR信息的保存时常
col topnsql for 99999;
col retention for a20;
col snap_interval for a20;
select * from dba_hist_wr_control;

--SYSAUX表空间上统计信息的保存时常
select dbms_stats.get_stats_history_retention from dual;

--UNDO表空间的使用情况
col tablespace_name for a20;
col status for a10;
select tablespace_name, status, sum(bytes)/1024/1024 m from dba_undo_extents group by tablespace_name, status order by 3;

--UNDO段的使用情况
col waits for 9999;
col shrinks for 9999;
select d.segment_name, d.tablespace_name, s.waits, s.shrinks, s.wraps, s.status from v$rollstat s, dba_rollback_segs d
where s.usn = d.segment_id order by 1;

--临时表空间的尺寸
select tablespace_name, sum(bytes)/1024/1024 mb from dba_temp_files group by tablespace_name;

--临时表空间的最高水位(=单次使用的最大值)
select tablespace_name, sum(bytes_cached)/1024/1024 mb from v$temp_extent_pool group by tablespace_name;

--临时表空间当前使用情况
select ss.tablespace_name, sum((ss.used_blocks*ts.blocksize))/1024/1024 mb from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name group by ss.tablespace_name;

--数据文件的尺寸
col m for 99999;
col file_id for 9999;
col file_name for a80;
col tablespace_name for a30;
select tablespace_name,file_id, bytes/1024/1024 m, increment_by, file_name from dba_data_files order by tablespace_name,file_id;

--数据文件的扩展情况
select tablespace_name,file_id, file_name, increment_by from dba_data_files where autoextensible='YES' order by file_id;
 
--数据文件的状态
select status,online_status from dba_data_files group by status,online_status;

select tablespace_name,file_id,online_status, file_name from dba_data_files where online_status='OFFLINE' order by tablespace_name,file_id;

--使用SYSTEM表空间的用户
select owner, segment_name, segment_type from dba_segments where tablespace_name = 'SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');

--没被锁的用户信息
col username for a20;
col temp_ts for a20;
select profile,password_versions,authentication_type from dba_users where account_status not like '%LOCKED%'
order by account_status,user_id;

--使用默认密码的账户（没修改过密码）
col username for a30;
col profile for a10;
col account_status for a20;
col default_tablespace for a20;
col temporary_tablespace for a20;
select b.username, created, account_status, lock_date, expiry_date, default_tablespace, temporary_tablespace, profile, password_versions
from dba_users a, dba_users_with_defpwd b
where a.username=b.username --and account_status not like '%LOCKED%'
order by a.created, username;

--使用的区间大于100的段的情况
col owner for a10;
col segment_name for a30;
col extents for 999999;
col "BYTES M" for 999999;
col "TOTAL BLOCKS" for 99999999;
select owner, segment_type, segment_name, tablespace_name,count(blocks) "EXTENTS", sum(bytes/1024/1024) "BYTES M", sum(blocks) "TOTAL BLOCKS"
from dba_extents where owner in (&user) group by owner, segment_type, segment_name, tablespace_name
having count(*) > 100 order by owner,segment_type,5;

--下次分配的区间大于当前最大空闲块的段。
set pagesize 200;
col owner for a10;
col segment_name for a30;
select s.owner, s.segment_name, s.segment_type, t.num_rows, t.last_analyzed, s.tablespace_name, e.curr_extent, s.next_extent from dba_segments s, 
(select owner, table_name, num_rows, last_analyzed from dba_tables 
 union all
 select owner, index_name, num_rows, last_analyzed from dba_indexes) t,
(select * from 
  (select owner, segment_name, bytes curr_extent, row_number() over(partition by owner, segment_name order by extent_id desc) seq 
   from dba_extents ) where seq=1) e
where s.owner in (&user)
  and s.owner=t.owner(+) and s.segment_name=t.table_name(+)
  and s.owner=e.owner(+) and s.segment_name=e.segment_name(+)
  and s.next_extent > (select max(f.bytes) from dba_free_space f where f.tablespace_name = s.tablespace_name)
  and s.owner not in ('SYS','SYSTEM','CTXSYS','MDSYS','OUTLN','ORDSYS','ORDPLUSING','OWAPUB')
order by s.owner,s.segment_type,s.next_extent;

--exec dbms_utility.compile_schema('',FALSE);

--各作业的运行情况
col job for 9999;
col log_user for a10;
col priv_user for a10;
col schema_user for a10;
col last_date for a20;
col last_sec for a20;
col this_date for a20;
col next_date for a20;
col what for a50;
col failures for 999;
select job,log_user,priv_user  schema_user,last_date,this_date,next_date,what,failures
from dba_jobs where log_user in (&user) order by log_user, job;

col owner for a10;
col job_name for a30;
col log_date for a20;
col last_start_date for a20;
col next_run_date for a20;
col last_run_duration for a30;
col state for a15;
col additional_info for a50;
select owner,job_name,failure_count failures,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss') last_start_date, to_char(next_run_date,'yyyy-mm-dd hh24:mi:ss') next_run_date,
       last_run_duration,state,run_count
from dba_scheduler_jobs where state<>'DISABLED' and state<>'COMPLETED' and owner not in ('EXFSYS') order by owner,job_name;


/*--指定作业的历史运行情况
col log_date for a20;
col req_start_date for a20;
col actual_start_date for a20;
col run_duration for a15;
col status for a10;
col additional_info for a50;
select log_date, req_start_date, actual_start_date, run_duration, status, additional_info
from dba_scheduler_job_run_details where job_name=upper(trim('$job_name')) order by 1,2;
*/

--库的各对象的状态
col object_type for a20;
select owner, object_type, status, count(*) from dba_objects where owner in (&user) group by owner, object_type, status order by 1,3 desc,2;

--最近10天内 object_id 序列号被使用的情况
select * from
(select trunc(created), max(object_id), min(object_id), (max(object_id) - min(object_id)) dt from user_objects
 group by trunc(created) order by 1 desc) where rownum <= 10;

--对象不存在的同义词
col table_name for a30
select * from dba_synonyms a where owner in (&user) and db_link is null and not exists 
(select 'x' from dba_objects where owner=nvl(a.table_owner,a.owner) and object_name=a.table_name) order by owner, synonym_name;

--各索引的状态
col owner for a10
col index_owner for a10
col table_name for a20
select owner,status,count(*) from dba_indexes where owner in (&user) group by owner,status order by owner, status;
select index_owner,status,count(*) from dba_ind_partitions where index_owner in (&user) group by index_owner,status order by index_owner, status;
select index_owner,status,count(*) from dba_ind_subpartitions where index_owner in (&user) group by index_owner,status order by index_owner, status;

--主键，唯一性约束使用非唯一性索引
select a.owner, a.index_name, a.uniqueness, b.constraint_type from dba_indexes a, dba_constraints b 
where b.constraint_type in ('P','U') and a.owner=b.owner and a.index_name=b.index_name and a.uniqueness = 'NONUNIQUE'
  and a.owner in (&user) order by a.owner, a.index_name;

--不可用的索引
select a.owner,a.table_name,a.index_name,a.pname,b.last_ddl_time
from (select owner,table_name,index_name,'PART_NAME' pname,status from dba_indexes
      union all
      select index_owner owner,'TNAME',index_name,partition_name pname,status from dba_ind_partitions
      union all
      select index_owner owner,'TNAME',index_name,subpartition_name pname,status from dba_ind_subpartitions) a
      join dba_objects b on a.index_name=b.object_name and a.owner=b.owner
where a.owner in (&user) and a.status='UNUSABLE' and b.object_type='INDEX' order by a.owner, a.table_name, a.index_name;

--位图索引
select owner,table_name,index_name from dba_indexes where owner in (&user) and index_type='BITMAP' order by owner,table_name,index_name;

--被屏蔽的索引
select a.owner,a.table_name,a.index_name,a.partitioned,b.last_ddl_time
from dba_indexes a join dba_objects b on a.index_name=b.object_name and a.owner=b.owner 
where a.owner in (&user) and a.visibility='INVISIBLE' and b.object_type='INDEX' order by a.owner, a.table_name, a.index_name;

--没索引的外键
col r_owner for a10
col table_name for a30
col constraint_name for a30
col r_constraint_name for a30
col index_name for a30
select owner,table_name,constraint_name,status, r_owner,r_constraint_name,index_owner,index_name from dba_constraints 
where owner in (&user) and constraint_type='R' and STATUS='ENABLED' order by owner,table_name;

--5个以上索引的表
break on t_rows on owner;
select owner,table_name, t_rows, index_name, num_rows i_rows, partitioned, status, row_number() over(partition by table_name order by partitioned, index_name) seq 
from dba_indexes a, (select owner b_owner, table_name b_tname, num_rows t_rows from dba_tables) b
where a.owner in (&user) and a.owner = b.b_owner and a.table_name = b.b_tname
  and (a.owner, a.table_name) in (select owner,table_name from dba_indexes having count(*) >5 group by owner,table_name) 
  and to_char(sysdate,'d')=1
order by owner,table_name,seq;

--4个或以上字段的索引
col index_owner for a5;
col table_name for a30;
col index_name for a30;
col column_name for a30;
break on owner on table_name on index_name on t_rows on i_rows;
select a.index_owner owner,a.table_name,c.t_rows,a.index_name,c.i_rows,a.column_name,a.column_position from dba_ind_columns a,
(select index_owner,table_name, index_name from dba_ind_columns group by index_owner,table_name, index_name having count(*)>=4) b,
(select a.owner, a.table_name, a.num_rows t_rows, b.index_name, b.num_rows i_rows from dba_tables a,dba_indexes b 
 where a.owner=b.owner and a.table_name=b.table_name group by a.owner, a.table_name, a.num_rows, b.index_name, b.num_rows) c
where a.index_owner in (&user) and a.index_owner=b.index_owner and a.index_owner=c.owner
  and a.table_name=b.table_name and a.index_name=b.index_name and a.index_name=c.index_name and a.table_name=c.table_name
  and to_char(sysdate,'d')=1
order by a.index_owner,a.table_name, a.index_name, a.column_position;

--所有索引
col index_columns for a80;
break on owner on table_name;
select a.owner, a.table_name, a.t_rows, a.index_name, a.i_rows,
listagg(column_name,',') within group (order by column_position) index_columns from 
(select a.owner, a.table_name, a.num_rows t_rows, b.index_name, b.num_rows i_rows from dba_tables a,dba_indexes b 
 where a.owner=b.owner and a.table_name=b.table_name group by a.owner, a.table_name, a.num_rows, b.index_name, b.num_rows) a, 
 dba_ind_columns b
where a.owner in (&user) and a.owner=b.index_owner and a.index_name=b.index_name
--and exists (select 'x' from dual where trunc(sysdate)=trunc(last_day(add_months(sysdate,-1))+1))
  and to_char(sysdate,'d')=1
group by a.owner, a.table_name, a.t_rows, a.index_name, a.i_rows order by a.owner,a.table_name,a.index_name;

--统计约束的状态
col search_condition for a30;
select status,count(*) from dba_constraints where owner in (&user) group by status;

--查看不可用，无效的状态
select table_name,constraint_name,constraint_type,a.status,search_condition,last_ddl_time from 
dba_constraints a, dba_objects b where (a.status='DISABLED' or a.status='INVALID') 
and a.owner in (&user) and a.owner=b.owner and b.object_name=a.constraint_name order by a.owner, table_name;

col sql for a120;
select 'alter table '||a.owner||'.'||a.table_name||' enable validate constraint '||a.constraint_name||';' sql from dba_constraints a, dba_tables b
where a.owner in (&user) and a.table_name=b.table_name and (a.status = 'DISABLED' or a.validated = 'NOT VALIDATED') order by a.owner, a.table_name;

--各表的统计信息的状态
col owner for a15;
col table_name for a30;
select owner,stattype_locked, count(*) from dba_tab_statistics where owner in (&user) group by owner,stattype_locked order by stattype_locked,owner;

--统计信息被锁的普通表
select a.owner,a.table_name,b.temporary,a.stattype_locked,c.created,c.last_ddl_time from dba_tab_statistics a,
(select owner,table_name,temporary from dba_tables where table_name not like 'MLOG$%') b,
(select owner,object_name,created,last_ddl_time from dba_objects) c
where a.owner in (&user) and a.owner=b.owner and a.table_name=b.table_name and a.owner=c.owner and a.table_name=c.object_name
and a.stattype_locked='ALL' order by owner,a.table_name;

--表的统计分析情况。
select owner,max(last_analyzed) max, min(last_analyzed) min from dba_tables where owner in (&user) and temporary = 'N'
and table_name not like 'MLOG%' group by owner order by owner;

--50个最早统计分析的表。
select owner,table_name,last_analyzed,num_rows,seq from 
(select owner, table_name, last_analyzed, num_rows, row_number() over(partition by owner order by last_analyzed) seq
 from dba_tables where owner in (&user) and temporary = 'N' and table_name not like 'MLOG%')
where seq <= 50 order by owner,seq;

--统计信息陈旧的表。
col owner for a10;
col pname for a13;
col object_type for a15;
col num_rows for 999999999;
col blocks for 999999999;
col chain_cnt for 999999999;
col sample_size for 999999999;
select owner,table_name,partition_name pname,subpartition_name subpname,num_rows,blocks,chain_cnt,sample_size,last_analyzed
from dba_tab_statistics where owner in (&user) and stale_stats='YES' order by owner,table_name;

--查看当前用户有多少临时表
select owner,count(*) from dba_tables where owner in (&user) and temporary = 'Y' group by owner order by owner;

--查看被采集过信息的临时表。
select owner,table_name,last_analyzed from dba_tables where owner in (&user) and temporary = 'Y' and last_analyzed is not null
order by owner,table_name;

--未创建存储段的表
col partitioned for a12;
col segment_created for a16;
col total for 99999;
select owner,partitioned,segment_created, count(*) total from dba_tables where owner in (&user) 
group by owner,segment_created,partitioned order by owner,partitioned,segment_created;

select owner, table_name, (select created from dba_objects where object_name=a.table_name and owner=a.owner and object_type='TABLE') created
from dba_tables a where owner in (&user) and segment_created='NO' and to_char(sysdate,'d')=1 order by owner, table_name;

--找出所有数据不能移动的分区表
select 'ALTER TABLE '||owner||'.'||table_name||' enable row movement;' from dba_tables where partitioned = 'YES' 
and row_movement='DISABLED' and owner in (&user) order by owner,1;

--查看参照当前库的物化视图的刷新情况
col owner for a20;
col owner2 for a20;
col name for a30;
col mview_site for a30;
col master for a30;
col mview_id for 999999;
select a.*,b.owner owner2,b.name,b.can_use_log,b.refresh_method,b.mview_site from dba_base_table_mviews a
full join dba_registered_mviews b on a.mview_id=b.mview_id order by a.owner,b.mview_site,a.mview_last_refresh_time desc;

--物化视图日志最后的刷新时间
col mview_name for a30;
col complete_state for a15
col stateness for a15;
select owner,mview_name,last_refresh_type,last_refresh_date,compile_state,staleness from dba_mviews 
where owner in (&user) order by owner,last_refresh_date desc;

--查看尺寸大于2M的物化视图日志
col segment_name for a30;
select log_owner,a.*,(select last_ddl_time from dba_objects where owner=log_owner and object_name=log_table) last_ddl_time
from (select owner, segment_name,bytes/1024/1024 m from dba_segments) a, dba_mview_logs b where a.segment_name=b.log_table
and a.owner=b.log_owner and m > 2 order by log_owner,m,last_ddl_time,segment_name;

select 'alter table '||owner||'.'||log_table||' move;' from (select * from dba_mview_logs) a,
dba_segments b where a.log_table=b.segment_name and bytes/1024/1024> 2 order by owner,segment_name;

--分区表及分区类型
col owner for a10
col table_name for a30
col column_name for a20
col ptype for a10;
col sptype for a8;
col ptotal for 9999999
col sptotal for 9999999
col pposition for 99999
col status for a8
col high_value for a30
col def_tablespace_name for a25;
col ref_ptn_constraint_name for a30;
select a.owner,a.table_name,
       a.partitioning_type ptype, listagg(b.column_name,',') within group (order by b.column_position) column_name, 
       a.subpartitioning_type sptype, listagg(c.column_name,',') within group (order by c.column_position) column_name,
       a.partition_count ptotal,a.def_subpartition_count sptotal,a.status,a.def_tablespace_name,a.ref_ptn_constraint_name
from dba_part_tables a, dba_part_key_columns b, dba_subpart_key_columns c 
where a.owner in (&user) and a.table_name=b.name and a.table_name=c.name(+)
group by a.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_name
order by 1,2;

--不规范的分区类型(range(有MAX分区),hash(2的N次幂),list(有DEFAULT分区))
col owner for a10;
col table_name for a30;
col bptype for a10;
col subptype for a10;
col pcount for 9999999;
col subpcount for 9999999;
col pname for a13;
col subpname for a30;
col phigh_value for a35;
col subphigh_value for a20;
select a.owner,a.table_name,a.partitioning_type ptype,a.partition_count pcount,
       a.subpartitioning_type subptype,a.def_subpartition_count subpcount,
       b.pname,b.phigh_value,b.subpname,b.subphigh_value from
dba_part_tables a,
(select b.table_owner,b.table_name,
        b.partition_name pname,b.high_value phigh_value,
        c.subpartition_name subpname,c.high_value subphigh_value from
(select * from 
  (select table_owner, table_name, partition_name, high_value,
          row_number() over (partition by table_name order by partition_position desc) max_position
   from dba_tab_partitions)) b,
(select * from 
  (select table_owner, table_name, partition_name, subpartition_name, high_value, subpartition_position,
          row_number() over (partition by table_name,partition_name order by subpartition_position desc) max_position
   from dba_tab_subpartitions) where max_position=1) c 
where b.table_owner=c.table_owner
  and b.table_name=c.table_name
  and b.partition_name=c.partition_name
union all
select b.table_owner,b.table_name,b.partition_name,b.high_value,c.subpartition_name,c.high_value from
(select * from 
  (select table_owner, table_name, partition_name, high_value,
          row_number() over (partition by table_name order by partition_position desc) max_position
   from dba_tab_partitions) where max_position=1) b,
(select * from 
  (select table_owner, table_name, partition_name, subpartition_name, high_value, subpartition_position,
          row_number() over (partition by table_name,partition_name order by subpartition_position desc) max_position
   from dba_tab_subpartitions) where max_position=1) c 
where b.table_owner=c.table_owner(+)
  and b.table_name=c.table_name(+)
  and c.table_name is null) b
where a.owner in (&user)
  and a.owner=b.table_owner
  and a.table_name=b.table_name
order by owner,decode(subptype,'NONE',1,2),table_name,pname;

--各分区表的最大分区数（不包括MAX,DEFAULT类型）
col table_owner for a10;
select * from (select table_owner, table_name, partition_name, partition_position from 
                (select a.*, row_number() over(partition by table_name order by partition_position desc) pseq from dba_tab_partitions a)
               where table_owner in (&user) and pseq <=2) order by table_owner, table_name, partition_position;

--查看没主键的普通表
select a.owner, table_name, last_analyzed,
       (select created from dba_objects where object_type='TABLE' and owner = a.owner and object_name=a.table_name) created
from (select a.owner, a.table_name, a.last_analyzed 
      from (select owner, table_name, last_analyzed from dba_tables a where temporary='N' 
            and not exists (select 'x' from dba_mview_logs where log_table=a.table_name and a.owner=log_owner)) a, 
           (select owner, table_name from dba_constraints where constraint_type='P') b 
      where a.owner = b.owner(+) and a.table_name=b.table_name(+) and b.table_name is null) a
where a.owner in (&user) order by owner, created desc;

-- 没有索引的表
col part for a4
col tmp for a4
select a.owner, a.table_name, a.partitioned part,a.temporary tmp, a.num_rows, a.last_analyzed,c.created
from (select * from dba_tables a where temporary='N' and owner in (&user) and num_rows is not null
      and not exists (select 'x' from dba_mview_logs where table_name=log_table)) a,
     dba_indexes b,(select * from dba_objects where object_type='TABLE') c
where (a.table_name=b.table_name(+) and b.table_name is null) 
  and (a.owner=c.owner and a.table_name=c.object_name)
order by a.owner,a.num_rows desc,a.table_name;

-- 没有索引并且不被数据库对象引用的表
select a.owner, a.table_name, a.partitioned part,a.temporary tmp, a.num_rows, a.last_analyzed,c.created from
(select * from dba_tables where temporary='N' and owner in (&user)
 and num_rows is not null and not exists (select 'x' from dba_mview_logs where table_name=log_table)) a,
 dba_indexes b, (select * from dba_objects where object_type='TABLE') c, dba_dependencies d
where (a.table_name=b.table_name(+) and b.table_name is null)
  and (a.owner=c.owner and a.table_name=c.object_name)
  and (a.table_name=d.referenced_name(+) and d.referenced_name is null)
  order by a.owner,a.num_rows desc,a.table_name;

--回收站中的对象
select * from dba_recyclebin where owner in (&user) order by owner,droptime;

--查看审计策略
col object_schema for a10;
col object_owner  for a10;
col object_text   for a10;
col object_column for a10;
col policy_text for a10;
col pf_schema for a10;
col pf_package for a10;
col pf_function for a10;
select * from dba_audit_policies order by object_schema, object_name, policy_owner, policy_name;

--记录数最多的50个表
col seq for 99999;
col part for a8;
col table_name for a30;
select * from (select owner,table_name,last_analyzed,num_rows,lpad(partitioned,8,' ') part,
               row_number() over(partition by owner order by num_rows desc nulls last) seq from dba_tables where num_rows >0)
where owner in (&user) and seq <= 50 order by owner,seq;

-- 遗留的 EXPORT 进程
col object_name for a30
select owner, created, object_name from dba_objects where object_name like 'SYS_EXPORT%' order by owner, created;

-- 表的记录数与尺寸对应情况
col size_m for 999999.99
col init_ts for 99999.99
col init_tab for 99999.99
col table_name for a30;
select a.owner, b.segment_name table_name, a.tablespace_name, c.init_ts, a.initial_extent/1024/1024 init_tab,
       a.num_rows, b.size_m from dba_tables a, (select tablespace_name, initial_extent/1024/1024 init_ts from dba_tablespaces) c,
(select owner, segment_name, sum(bytes)/1024/1024 size_m from dba_segments group by owner,segment_name) b
 where a.owner in (&user) and a.owner=b.owner and a.table_name=b.segment_name and b.size_m > 2
   and a.num_rows is not null and a.partitioned='NO' and a.tablespace_name=c.tablespace_name
   and b.size_m > c.init_ts and b.size_m > a.initial_extent/1024/1024
   order by a.owner, a.num_rows, b.size_m;

-- Recovery instance
set num 12;
select recovery_estimated_ios,actual_redo_blks,target_redo_blks,log_file_size_redo_blks,log_chkpt_timeout_redo_blks from v$instance_recovery;
select log_chkpt_interval_redo_blks,fast_start_io_target_redo_blks,target_mttr,estimated_mttr,ckpt_block_writes from v$instance_recovery;
select optimal_logfile_size,estd_cluster_available_time,writes_mttr,writes_logfile_size,writes_log_checkpoint_settings from v$instance_recovery;
select writes_other_settings,writes_autotune,writes_full_thread_ckpt from v$instance_recovery;

--v$mttr_target_advice
select mttr_target_for_estimate mttr_target_estimate,dirty_limit,estd_cache_writes,estd_cache_write_factor,estd_total_writes,
       estd_total_write_factor,estd_total_ios,estd_total_io_factor from v$mttr_target_advice where advice_status='ON' order by 1;

--检查备份情况，备份速度．
col status for a25
col input_type for a13
col input_bytes_display for a10
col output_bytes_display for a10
col input_bytes_per_sec_display for a10
col output_bytes_per_sec_display for a10
col time_taken_display for a10
select start_time,end_time,status,input_type,
 input_bytes_display,output_bytes_display,input_bytes_per_sec_display,output_bytes_per_sec_display,time_taken_display
 from v$rman_backup_job_details where start_time >= trunc (sysdate) - 90 order by start_time;

--备份效率 2011-06-14 15:54
set num 12;
select type,total_bytes,open_time open,close_time close,elapsed_time elapsed,io_count,ready,long_waits waits,
long_wait_time_total total,long_wait_time_max max from v$backup_async_io where open_time >=trunc(sysdate)+1/4 order by open_time;


--查看库的归档日志情况
col total for a20;
col h00 for 999;
col h01 for 999;
col h02 for 999;
col h03 for 999;
col h04 for 999;
col h05 for 999;
col h06 for 999;
col h07 for 999;
col h08 for 999;
col h09 for 999;
col h10 for 999;
col h11 for 999;
col h12 for 999;
col h13 for 999;
col h14 for 999;
col h15 for 999;
col h16 for 999;
col h17 for 999;
col h18 for 999;
col h19 for 999;
col h20 for 999;
col h21 for 999;
col h22 for 999;
col h23 for 999;
select to_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)' TOTAL
from (select max(blocks) blocks,max(block_size) block_size,max(first_time) first_time from
            v$archived_log a where completion_time > trunc(sysdate - 90)         
            group by sequence#)
group by to_char(first_time,'mm/dd'), to_char(first_time,'yyyy/mm/dd')
order by to_char(first_time,'yyyy/mm/dd');

col total clear;

-- 最近2天的归档日志间隔时长
col inst_id for 9999;
col thread# for 9999;
col gap for 99999
col deleted for a7
col first_time for a20
col completion_time for a20
col name for a75;
col size_m for 999
break on first_day on next_day on completion_day
select inst_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, name
from gv$archived_log where inst_id=thread# and completion_time > trunc(sysdate)-1 order by first_time,sequence#,dest_id;


--库归档日志路径的状况
col dest_id for 9999;
col dest_name for a20;
col status for a8;
col destination for a20;
col error for a10;
col destination for a30;
col log_sequence for 9999999;
col reopen_secs for 9999999;
col delay_mins for 9999999;
col failure_count for 9999999;
col db_unique_name for a15;
col type for a10;
col database_mode for a15;
col recovery_mode for a25;
col schedule for a10;
col standby_logfile_count for a25;
select inst_id, dest_name, status, target, schedule, destination, log_sequence, reopen_secs
       from gv$archive_dest where status='VALID' order by inst_id, dest_id;

select inst_id, dest_name,delay_mins, failure_count, error, db_unique_name, compression, applied_scn 
       from gv$archive_dest where status='VALID' order by inst_id, dest_id;

select inst_id,dest_id,dest_name,type,database_mode,recovery_mode,protection_mode
       standby_logfile_count,standby_logfile_active
from gv$archive_dest_status where status='VALID' order by inst_id, dest_id;

select inst_id, dest_id, dest_name, archived_thread#,archived_seq#,applied_thread#,
       applied_seq#,error,srl,synchronized,gap_status
from gv$archive_dest_status where status='VALID' order by inst_id, dest_id;

-- 查看未备份就被删除的归档日志
col seq for 999;
col total for 9999;
select thread#, row_number() over(partition by thread# order by min(sequence#)) seq,
       min(sequence#) init_log, max(sequence#) max_log, min(first_time) first_time, max(first_time) last_time, 
       max(sequence#) - min(sequence#)+1 total from
(select thread#, sequence#, first_time, sequence# - seq gap
from (
select a.thread#, a.sequence#, a.first_time,
       row_number() over(partition by a.thread# order by a.sequence#) seq
from v$archived_log a, v$backup_archivelog_details b 
where a.completion_time >= (select min(start_time) from v$backup_set_details where incremental_level=0) and dest_id=1 
  and a.deleted = 'YES'
  and a.thread#=b.thread#(+) and a.sequence#=b.sequence#(+)
  and b.sequence# is null 
)) group by thread#, gap order by 2;

-- OPEN_MODE 
col db_unique_name for a20;
col open_mode for a20;
col database_role for a20;
col switchover_status for a20;
select db_unique_name, open_mode, database_role, switchover_status from v$database;
- ORCL 库的FRA使用情况 
set num 10;
col name for a30;
select name, space_limit/1073741824 max_g, space_used/1073741824 used_g, space_reclaimable/1073741824 rec_g, 
       number_of_files from v$recovery_file_dest;
select * from v$recovery_area_usage;

--STANDBY 当前应用日志的状况
col archived for a10;
col applied for a10;
select * from
 (select thread#, dest_id, first_time, next_time, sequence#, completion_time, archived, applied, 
         row_number() over(order by next_time desc) seq
  from v$archived_log where dest_id in (2) /*and applied='YES'*/)
  where seq <= 20 order by next_time, completion_time;

--当前连接到库的各中间键的情况
col username for a10
col machine for a25
select username,machine,status,count(*) from v$session where username is not null group by username,machine,status order by 3,1,2;

select inst_id, sessions_max, sessions_warning, sessions_current, sessions_highwater, users_max, cpu_count_current from gv$license order by inst_id;
select inst_id, cpu_core_count_current, cpu_socket_count_current, cpu_count_highwater, cpu_core_count_highwater, cpu_socket_count_highwater 
from gv$license order by inst_id;

select max(count(*)) from v$open_cursor group by sid;

--查看当前非空闲事件的会话
col sid for 9999;
col event for a30;
col machine for a10;
col taddr for a15;
col wait_class for a15;
col module for a18;
col sql_id for a15;
col object_name for a30;
col call_et for 999999;
select inst_id,sid,serial#,seq#,taddr,sql_id,sql_child_number sql_child,machine,last_call_et call_et,module,
(select object_name from dba_objects where a.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,p3 from gv$session a
where a.status = 'ACTIVE' and a.username is not null order by last_call_et;

select sysdate from dual;

--当前正在回滚的事务及进展情况
select usn, state, undoblockstotal , undoblocksdone ,cputime from gv$fast_start_transactions;
select * from gv$fast_start_servers;
select sysdate from dual;

--当前正在运行的定时作业的情况
set line 190;
col job_name for a30;
col event for a30;
col call_et for 9999999;
col session_stat_cpu for a25;
select session_id, action job_name, b.event, session_stat_cpu, last_call_et call_et, (sysdate - last_call_et/86400) start_time
from v$scheduler_running_jobs a, v$session b where a.session_id=b.sid order by call_et;

--进程事件与回滚段
select se.sid,se.event,se.last_call_et, rn.usn,rn.name,rs.xacts,rs.extents,rs.hwmsize
from v$session se, v$transaction ts, v$rollname rn, v$rollstat rs 
where se.status='ACTIVE' and se.taddr=ts.addr and ts.xidusn=rs.usn and rn.usn = rs.usn;

--查看当前空闲1小时以上的会话
col terminal for a13;
col machine for a10;
col module for a35;
select sid,serial#,terminal,machine,last_call_et,module,event,wait_class,p1,p2,p3 from gv$session a
where a.status = 'INACTIVE' and a.username is not null and last_call_et> 3600 order by last_call_et;

--查看当前发送DML操作的表
col name for a30;
col blocking_others for a30;
select * from dba_dml_locks;

--锁堵塞查询
set num 38
col ty for a5
col id1 for 999999
col id2 for 999999
col lmode for 999
col request for 999
col ctime for 999999
col block for 9
select b.*,a.ctime from (select id1,id2,ctime from v$lock where block=1) a, v$lock b
where a.id1=b.id1 and a.id2=b.id2 order by a.ctime,b.block,b.id1,b.ctime;

--持锁者查询
col username for a10;
col machine for a10;
col terminal for a10;
select a.sid,serial#,seq#,username,paddr,machine,terminal,status,last_call_et,sql_id,prev_sql_id,event from v$session a,
(select /*+ rule */ sid from v$lock where block=1) b where a.sid=b.sid order by last_call_et;

select sysdate from dual;

exit;