A、本文主要介绍金蝶云星空在ORACLE数据库运行时，日常需要对数据库做的检查事项，目的是确保数据库能够正常稳定地运行。

B、数据库版本：11204。

C、涉及到的脚本，是在数据库服务器上，以 sys 身份、使用sqlplus工具登录数据库执行。

D、脚本包含了对数据库层面的检查，同时也包含对数据中心（按输入用户）的检查。

1、查阅数据库告警日志的信息，看看是否存在错误？

host adrci exec="show base; show problem;"

2、检查数据库的表空间使用情况，看看SYSTEM,SYSAUX表空间的消耗是否合理？业务数据

使用的表空间，是否将耗尽？

   col "USED_PERCENT" for a10;

select sysdate, 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);

3、检查数据库的定时作业，看看作业是否有执行出错的情况（如下红框，失败1次）。

4、检查数据文件的状态，看看数据文件已经有无离线？

    select status,online_status from dba_data_files group by status,online_status;

    5、最近10天内 object_id 序列号被使用的情况

col dt for 9999999999;

col max_object_id for 999999999999999;

col max2_object_id for 999999999999999;

select create_date, max_object_id, min_object_id,

     (max_object_id - min_object_id) dt,

     case when (max_object_id = min_object_id) then max2_object_id else 0 end max2_object_id

from (select to_char(created,'yyyy-mm-dd') as create_date,

        greatest(max(object_id), lag(max(object_id),1) over(order by to_char(created,'yyyy-mm-dd'))) max_object_id,

        greatest( lag(max(object_id),1) over(order by to_char(created,'yyyy-mm-dd')),

                 lag(max(object_id),2) over(order by to_char(created,'yyyy-mm-dd'))

                 ) min_object_id,                  

          least(max(object_id), lag(max(object_id),1) over(order by to_char(created,'yyyy-mm-dd'))) max2_object_id

       from dba_objects

 group by to_char(created,'yyyy-mm-dd'),to_char(created-1,'yyyy-mm-dd')

order by 1 desc) do where rownum <= 10;

6、检查数据库的索引状况，看看是否存在UNUSABLE或者INVISIBLE状态。

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, idx_type, count(*) from

(select index_owner,status, 'PART_INDEX' idx_type from dba_ind_partitions

   union all select index_owner,status,'SUBPART_INDEX' idx_type from dba_ind_subpartitions )

where index_owner in (&user) group by index_owner,status,idx_type order by index_owner,status,idx_type;

7、位图索引（正常情况，系统不会使用位图索引）。

select owner,table_name,index_name from dba_indexes where owner in (&user)

and index_type='BITMAP' order by owner,table_name,index_name;

8、查看不可用，无效状态的约束。

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 b.owner in (&user)

and a.owner=b.owner and b.object_name=a.constraint_name order by a.owner, table_name;

9、物化视图的最后刷新时间及状态（有6个物化视图实时刷新）。

col mview_name for a30;

col staleness for a10;

select dm.owner, mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date, staleness, status

from dba_mviews dm, dba_objects do

where dm.owner in (&user) and do.owner in (&user)

 and dm.owner = do.owner and dm.mview_name=do.object_name

and do.object_type='MATERIALIZED VIEW' order by dm.owner,last_refresh_date;

10、TMP表当前的创建、尺寸大小情况

select owner, decode(created_date,null,null,table_name) table_name, created_date,

      tab_totals , size_m from

(select dts.owner, 'TMP' table_name , to_char(do.created,'yyyy/mm/dd') created_date,

       count(*) tab_totals , sum(dts.bytes/1024/1024) size_m

from (select dt.owner, dt.table_name, dt.owner||','||dt.table_name ot, ds.segment_name, ds.bytes from dba_tables dt

    inner join dba_segments ds on (ds.owner=dt.owner and ds.segment_name=dt.table_name)

    where dt.owner in (&user) and ds.owner in (&user)

    and ds.segment_type='TABLE'

    and regexp_like(dt.table_name,'^TMP[[:alnum:]]{27}$') ) dts

inner join (select owner, object_name, owner||','||object_name oo, created from dba_objects

    where owner in (&user) and object_type='TABLE') do

    on (do.oo=dts.ot)

group by rollup(dts.owner , to_char(do.created,'yyyy/mm/dd') ))

where ( (owner is not null and created_date is not null) or (owner is null and created_date is null) ) order by owner, created_date nulls last;

11、数据中心表的统计分析情况。

select owner,max(last_analyzed) max, min(last_analyzed) min from dba_tables

where owner in (&user) and temporary = 'N' and read_only='NO'

and table_name not like 'MLOG%' group by owner order by owner;

12、没有索引并且不被数据库对象引用的表。

select a.owner, a.table_name, a.partitioned parted, a.num_rows, a.last_analyzed,c.created from

(select * from dba_tables where temporary='N' and owner in (&user)

and not regexp_like(table_name,'^TMP[[:alnum:]]{27}$') and read_only='NO'

   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' and owner in (&user)) c,

   dba_dependencies d

where (a.owner=b.table_owner(+) and 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.owner=d.owner(+) and a.table_name=d.referenced_name(+) and d.referenced_name

is null) order by a.owner,a.num_rows desc,a.table_name;

13、查看最大\默认分区是否包含数据

col table_name for a30;

col p_name for a12;

col interval for a27;

col p_pos for 99999;

col p_type for a8;

select table_owner owner, table_name, partition_name p_name, partitioning_type p_type,

      interval, num_rows, last_analyzed, partition_position p_pos from

(select table_owner, table_name, partition_name,

       (select partitioning_type from dba_part_tables where owner=a.table_owner and a.table_name=table_name) partitioning_type,

       (select interval from dba_part_tables where owner=a.table_owner and a.table_name=table_name) interval, num_rows, last_analyzed, partition_position,

      row_number() over(partition by table_name order by partition_position desc) seq

from dba_tab_partitions a where table_owner in (&user)) where seq=1  order by 1,2;

14 --查看被参照/引用最多的对象

col referenced_name for a30;

col referenced_type for a20;

select * from (select owner, referenced_name, referenced_type, count(*) r_count

from dba_dependencies where owner in (&user)

group by owner, referenced_name, referenced_type order by owner, r_count desc)

where rownum <= 10;

15、导出失败，遗留的 EXPORT 进程辅助表（正常情况应该不存在）。

col object_name for a30

select owner, created, object_name from dba_objects where object_name like 'SYS_EXPORT%' order by owner, created;

16、回收站中的对象（正常情况下，回收站为空）。

select * from dba_recyclebin order by owner,droptime;

17、查看未备份就被删除的归档日志（仅对做了RMAN全库＋日志备份的情况有效）。

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;

18、STANDBY 库当前应用日志的状况（仅对配置STANDBY库的主库有效）。

col archived for a10;

col applied for a10;

select v1.thread#, v1.dest_id, first_time, next_time, v1.sequence#, completion_time, archived,

applied, row_number() over(partition by v1.dest_id, v1.thread# order by next_time desc) seq

    from v$archived_log v1,

    (select * from (select thread#, dest_id, sequence#, row_number() over(partition by dest_id,

thread# order by next_time desc) seq

 from v$archived_log where dest_id in (2,3) and applied='YES') where seq = 1) v2

where v1.dest_id=v2.dest_id and v1.thread#=v2.thread# and v1.sequence# between v2.sequence# - 4 and v2.sequence# + 5

order by v1.dest_id, v1.thread#, v1.dest_id, next_time, completion_time;

19、当前连接到库的各中间键的情况。

col username for a15;

col machine for a40;

select inst_id,username,machine,status,count(*) from gv$session where username is not null

group by inst_id, username, machine, status order by 1,4,3,2;