达梦数据库巡检脚本

DBA，数据世界的医生（附达梦巡检代码）

1.数据库授权信息查询

SELECT LIC_VERSION AS "许可证版本"       ,
        SERIES_NO AS "序列号"           ,
        CHECK_CODE AS "校验码"          ,
        AUTHORIZED_CUSTOMER AS "最终用户",
        PROJECT_NAME AS "项目名称"       ,
        PRODUCT_TYPE AS "产品名称"       ,
        CASE SERVER_TYPE WHEN '1' THEN '正式版' WHEN '2' THEN '测试版' WHEN '3' THEN '试用版' WHEN '4' THEN '其他' END AS "产品类型",
        TO_CHAR(EXPIRED_DATE) AS "有效日期",
        OS_TYPE AS "授权系统",
        TO_CHAR(AUTHORIZED_USER_NUMBER) AS "授权用户数",
        NVL(TO_CHAR(CONCURRENCY_USER_NUMBER),'') AS "授权并发数",
        NVL(TO_CHAR(MAX_CPU_NUM),'') AS "授权CPU个数",
        CLUSTER_TYPE AS "授权集群"
        FROM V$LICENSE;

2.查询数据库的实例信息

SELECT '版本号',(SELECT id_code)FROM v$instance
union all
select '数据库名',name from v$database
union all
select '实例名',instance_name from v$instance
union all
select '系统状态',status$ from v$instance
union all
select '实例模式',mode$ from v$instance
union all
select '是否启用归档',case arch_mode when 'Y' then '是' when 'N' then '否' end from v$database 
union all
SELECT  '页大小',cast(PAGE()/1024 as varchar)
union all
SELECT  '大小写敏感',cast(case SF_GET_CASE_SENSITIVE_FLAG()when '1' then '是' when '0' then '否' end as varchar)
union all
SELECT '字符集',CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' then 'UTF-8' when '2' then 'EUC-KR' end
union all
SELECT  '以字符为单位',cast(case SF_GET_LENGTH_IN_CHAR()when '1' then '是' when '0' then '否' end as varchar)
union all
SELECT  '空白字符填充模式',cast(case (select BLANK_PAD_MODE()) when '1' then '是' when '0' then '否' end as varchar)
union all
select '日志文件个数',to_char(count(*)) from v$rlogfile
union all
select '日志文件大小',cast(RLOG_SIZE/1024/1024 as varchar) from v$rlogfile where rowid =1
union all
select '创建时间',to_char(create_time) from v$database
union all
select '启动时间',to_char(last_startup_time) from v$database;

3.查询数据库中语句统计信息

select NAME,
       STAT_VAL
        from v$sysstat
 where name in ('select statements',
                'insert statements',
                'delete statements',
                'update statements',
                'ddl statements',
                'transaction total count');

4.数据库表空间的状态检查

SELECT NAME AS "NAME",
       CASE TYPE$ WHEN '1' THEN 'DB类型' WHEN '2' THEN '临时表空间' END AS "TYPE",
       CASE STATUS$ WHEN '0' THEN '联机' WHEN '1' THEN '脱机' WHEN '2' THEN '脱机' WHEN '3' THEN '损坏'END AS "STATUS",
       TOTAL_SIZE*PAGE/1024/1024 AS "TOTALSIZE",
       FILE_NUM AS "FILENUM"
FROM V$TABLESPACE;

5.查询数据库表空间的使用情况

SELECT
       F.TABLESPACE_NAME ,
       ROUND((T.TOTAL_SPACE - F.FREE_SPACE) / 1024, 2) "USED" ,
       CASE WHEN H.TOTAL_MAX_SPACE == 0 THEN ROUND(F.FREE_SPACE / 1024, 2) ELSE ROUND((H.TOTAL_MAX_SPACE -(T.TOTAL_SPACE - F.FREE_SPACE)) / 1024, 2) END "FREE_MAX" ,
       CASE WHEN H.TOTAL_MAX_SPACE == 0 THEN ROUND(T.TOTAL_SPACE / 1024, 2) ELSE ROUND(H.TOTAL_MAX_SPACE / 1024, 2) END "TOTAL_MAX" ,
       CASE WHEN H.TOTAL_MAX_SPACE == 0 THEN ROUND((F.FREE_SPACE/1024)/(T.TOTAL_SPACE / 1024), 4)*100||'%' ELSE ROUND(((H.TOTAL_MAX_SPACE-(T.TOTAL_SPACE - F.FREE_SPACE))/1024)/(H.TOTAL_MAX_SPACE / 1024), 4)*100||'%' END PER_FREE_MAX,
       CASE WHEN H.TOTAL_MAX_SPACE == 0 THEN ROUND((((T.TOTAL_SPACE - F.FREE_SPACE))/1024)/(T.TOTAL_SPACE / 1024), 4)*100||'%' ELSE ROUND((((T.TOTAL_SPACE - F.FREE_SPACE))/1024)/(H.TOTAL_MAX_SPACE / 1024), 4)*100||'%' END PER_USED_MAX ,
       ROUND(F.FREE_SPACE / 1024, 2) "FREE" ,
       ROUND(T.TOTAL_SPACE / 1024, 2) "TOTAL",
       CASE WHEN T.TOTAL_SPACE == 0 THEN '' ELSE (ROUND((F.FREE_SPACE / T.TOTAL_SPACE), 4)* 100) || '% ' END PER_FREE,
       CASE WHEN T.TOTAL_SPACE == 0 THEN '' ELSE (ROUND((T.TOTAL_SPACE - F.FREE_SPACE) / T.TOTAL_SPACE, 4) * 100)||'%' END PER_USED
  FROM ( SELECT TABLESPACE_NAME,
                ROUND(SUM(BLOCKS * ( SELECT PARA_VALUE / 1024
                   FROM V$DM_INI
                  WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE' ) / 1024)) FREE_SPACE
           FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME,
                ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
           FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME ) T, ( SELECT TABLESPACE_NAME,
                ROUND(SUM(MAXBYTES / 1048576)) TOTAL_MAX_SPACE
           FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME ) H
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND F.TABLESPACE_NAME =H.TABLESPACE_NAME;

6.查询表空间的数据文件使用情况

SELECT  PATH,
       TO_CHAR(TOTAL_SIZE*PAGE/1024/1024) AS TOTAL_SIZE,
       TO_CHAR(FREE_SIZE*PAGE/1024/1024) AS FREE_SIZE,
       (TO_CHAR(100-FREE_SIZE*100/TOTAL_SIZE)) AS REM_PER,
       CASE AUTO_EXTEND WHEN '0' THEN '未开启' WHEN '1' THEN '已开启' END AS AUTO_EXTEND,
       NEXT_SIZE,
       MAX_SIZE,
       b.TABLESPACE_NAME
  FROM V$DATAFILE a,dba_data_files b where b.file_name = a.PATH  order by GROUP_ID;

7.查询数据库中的用户信息

SELECT A.USERNAME ,
       CASE B.RN_FLAG WHEN '0' THEN '否' WHEN '1' THEN '是' END AS READ_ONLY,
       CASE A.ACCOUNT_STATUS WHEN 'LOCKED' THEN '锁定' WHEN 'OPEN' THEN '正常' END AS "状态",
       TO_CHAR(A.LOCK_DATE,'YYYY-MM-DD HH24:MI:SS') AS "锁定起始时间",
       TO_CHAR(A.EXPIRY_DATE,'YYYY-MM-DD HH24:MI:SS') AS "密码截止使用时间",
       TO_CHAR(round(datediff(DAY,TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(A.EXPIRY_DATE,'YYYY-MM-DD HH24:MI:SS')),2)) AS EXPIRY_DATE_DAY,
       TO_CHAR(round(datediff(DAY,TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(A.LOCK_DATE,'YYYY-MM-DD HH24:MI:SS')),2)) AS LOCK_DATE_DAY,
       A.DEFAULT_TABLESPACE,
       A.PROFILE,
       TO_CHAR(A.CREATED,'YYYY-MM-DD HH24:MI:SS') AS CREATE_TIME
  FROM DBA_USERS A, 
       SYSUSERS B 
 WHERE A.USER_ID=B.ID;

8.查询数据库中用户权限

SELECT USERNAME AS "用户名", 
WM_CONCAT(PRIVILEGE) AS "默认权限"
FROM
(SELECT  A.USERNAME ,     
C.PRIVILEGE
 FROM DBA_USERS A,SYSUSERS B,
 (SELECT A.* FROM (SELECT GRANTEE,GRANTED_ROLE PRIVILEGE,'ROLE_PRIVS' PRIVILEGE_TYPE,
CASE ADMIN_OPTION WHEN 'Y' THEN 'YES' ELSE 'NO' END ADMIN_OPTION FROM DBA_ROLE_PRIVS 
UNION SELECT GRANTEE,PRIVILEGE,'SYS_PRIVS' PRIVILEGE_TYPE,ADMIN_OPTION FROM DBA_SYS_PRIVS
UNION SELECT GRANTEE,PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME PRIVILEGE,'TABLE_PRIVS' PRIVILEGE_TYPE,GRANTABLE FROM DBA_TAB_PRIVS) A
WHERE GRANTEE IN (SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSDBA','SYSSSO','SYSAUDITOR') ) 
) C
 WHERE A.USER_ID=B.ID
 AND A.USERNAME = C.GRANTEE)
GROUP BY USERNAME;

9.查询数据库中的对象是否无效（函数、存储过程、包等对象）

SELECT  OWNER,
       OBJECT_NAME,
       OBJECT_TYPE,
       TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS'),
       TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS')
  FROM DBA_OBJECTS
 WHERE OWNER NOT IN('SYS',
                    'SYSJOB',
                    'SYSAUDITOR',
                    'CTISYS',
                    'SYSSSO')
   and STATUS = 'INVALID';

10.查询数据库中的大表信息

SELECT A.TABLE_NAME,A.TABLESPACE_NAME,B.OWNER ,B.BYTES
                   FROM (SELECT TABLE_NAME,TABLESPACE_NAME FROM ALL_TABLES  GROUP BY TABLE_NAME,TABLESPACE_NAME) AS A
                   LEFT JOIN (SELECT OWNER,SEGMENT_NAME,SUM(BYTES) BYTES FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='TABLE'GROUP BY OWNER,SEGMENT_NAME) AS B
                   ON A.TABLE_NAME=B.SEGMENT_NAME
                   WHERE B.OWNER NOT IN ('SYS','SYSDBA','SYSAUDITOR','SYSSSO','CTISYS') ORDER BY BYTES DESC LIMIT 10;

11.查询数据库中的分区大表信息

SELECT A.OWNER,
       A.TABLE_NAME,
       A.PARTITIONING_TYPE,
       TO_CHAR(ROUND(TABLE_USED_SPACE(A.OWNER, A.TABLE_NAME) * PAGE / 1024.0 / 1024, 2))    SIZEMB,
       A.PARTITION_COUNT                                                                 as partition_num,
       table_rowcount(a.owner, a.table_name)                                             as row_num
  FROM DBA_PART_TABLES a;

12.查询数据库中会话的使用情况

SELECT  *
FROM
        (
                SELECT
                        STATE    ,
                         CASE 
    WHEN  INSTR(CLNT_IP, ':',8)  > 0 
     THEN SUBSTR(CLNT_IP, 1, INSTR(CLNT_IP, ':',8) - 1) 
    ELSE CLNT_IP 
    END AS CLNT_IP  ,
                        CLNT_TYPE,
                        CURR_SCH ,
                        USER_NAME,
                        COUNT(*) COUNTS
                FROM
                        V$SESSIONS
                GROUP BY
                        STATE    ,
                         CASE 
    WHEN  INSTR(CLNT_IP, ':',8)  > 0 
     THEN SUBSTR(CLNT_IP, 1, INSTR(CLNT_IP, ':',8) - 1) 
    ELSE CLNT_IP 
    END   ,
                        CLNT_TYPE,
                        CURR_SCH ,
                        USER_NAME
                ORDER BY
                        STATE
        );

13.长时间空闲会话检查

SELECT SESS_ID,
       SESS_SEQ,
       USER_NAME,
       CREATE_TIME,
       CLNT_TYPE,
       CLNT_HOST,
       CLNT_IP,
       OSNAME,
       CONN_TYPE,
       CLNT_VER
  FROM SYS.V$SESSIONS
 WHERE STATE = 'IDLE'
   AND DATEDIFF(HH, LAST_SEND_TIME, SYSDATE) > 48
   AND DATEDIFF(HH, CREATE_TIME, SYSDATE) > 48;

14.查询数据库的redo日志大小

SELECT FILE_ID,PATH,CLIENT_PATH,RLOG_SIZE FROM V$RLOGFILE;

15.查询数据库的定时任务信息

SELECT  SYSJOB."NAME"           ,
        SCHE."NAME" SCHENAME    ,
        SCHE."JOBID"            ,
        SCHE."TYPE"             ,
        SCHE."FREQ_INTERVAL"    ,
        SCHE."FREQ_SUB_INTERVAL",
        SCHE."STARTTIME"        ,
        STEPS."NAME" STEPSNAME  ,
        STEPS."SEQNO" STEPSSEQNO,
        STEPS."TYPE" STEPSTYPE  ,
        STEPS.COMMAND WHAT      ,
        STEPS.SUCC_ACTION       ,
        STEPS.FAIL_ACTION
FROM
        SYSJOB.SYSJOBSCHEDULES SCHE
LEFT JOIN SYSJOB.SYSJOBSTEPS STEPS
ON
        SCHE.JOBID = STEPS.JOBID
LEFT JOIN SYSJOB.SYSJOBS SYSJOB
ON
        SCHE.JOBID = SYSJOB.ID
WHERE
        SCHE.VALID == 'Y'
ORDER BY
        STEPS.JOBID,
        STEPS.SEQNO ASC;

16.查询定时任务是否有错误

select  
         NAME ,
         '' STEPNAME ,
         MAX(START_TIME) START_TIME,
         ERRINFO
    from ( SELECT NAME ,
                  MAX(START_TIME) START_TIME,
                  ERRINFO
             FROM SYSJOB.SYSSTEPHISTORIES2
            WHERE ERRCODE !=0
         GROUP BY NAME,
                  ERRINFO
         union all
           select NAME ,
                  MAX(START_TIME) START_TIME,
                  ERRINFO
             from SYSJOB.SYSJOBHISTORIES2
            where ERRCODE !=0
         GROUP BY NAME,
                  ERRINFO )
   WHERE TO_CHAR(START_TIME,'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(TRUNC(ADD_DAYS(SYSDATE, -7)),'YYYY-MM-DD HH24:MI:SS')
GROUP BY NAME,
         ERRINFO
ORDER BY START_TIME DESC LIMIT 10;

17.数据字典的淘汰情况

SELECT ROUND(TOTAL_SIZE/1024.0/1024, 2) TOTALSIZE         ,
                          ROUND(USED_SIZE /1024.0/1024, 2) USEDSIZE         ,
                          DICT_NUM DICTNUM                                ,
                          ROUND(SIZE_LRU_DISCARD/1024.0/1024, 2)  SIZELRUDISCARD,
                          LRU_DISCARD LRUDISCARD,ROUND((USED_SIZE/1024.0/1024)/(TOTAL_SIZE/1024.0/1024)*100, 2) CACHE_HIT
                   FROM
                          V$DB_CACHE;

18.查询数据库中的无效索引

select owner, index_name,table_name,index_type,status from dba_indexes
where status != 'VALID' and owner not in ('SYS', 'SYSAUDITOR', 'SYSSSO', 'SYSDBA', 'DEM', 'SYSJOB', 'SYSDBO')
order by 1,2,3;

19.查询数据库分区表中的无效索引

SELECT *
  FROM (SELECT SCH_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME,STATUS
          FROM DBA_IND_SUBPARTITIONS
        UNION
        SELECT SCH_NAME, INDEX_NAME, PARTITION_NAME, NULL,STATUS
          FROM DBA_IND_PARTITIONS
        UNION
        SELECT OWNER, INDEX_NAME, NULL, NULL,STATUS FROM DBA_INDEXES) S
 WHERE S.STATUS = 'UNUSABLE'
   AND S.SCH_NAME NOT IN
       ('SYS', 'SYSAUDITOR', 'SYSSSO', 'SYSDBA', 'DEM', 'SYSJOB', 'SYSDBO')
 ORDER BY 1, 2;

20.查询数据库中的大索引信息

SELECT objname AS "对象名",
objtype as "对象类型",
TABLESPACE_NAME AS "表空间",
to_char(round(TOT_BLOCKS/1024.0/1024.0*page(),2)) AS "大小(MB)"
from 
(
SELECT objname,objtype,TABLESPACE_NAME,SUM(page_used) TOT_BLOCKS 
FROM 
(select * from
/*(select owner||'.'||table_name objname,
 'TABLE/TABLE PART' objtype,
 TABLESPACE_NAME,
 TABLE_USED_PAGES(owner,table_name) page_used
 from dba_tables 
 where tablespace_name not in ('TEMP','ROLL','SYSTEM') 
and owner not in ('SYS','SYSAUDITOR','SYSSSO','SCHEDULER')
and temporary='N'
and TABLE_USED_PAGES(owner,table_name)> (select sum(TOTAL_SIZE)*0.0001 from v$datafile)
order by table_used_space(owner,table_name) desc
limit 10)
union all*/
(select owner||'.'||index_name objname,
 'INDEX/INDEX PART' objtype,
 TABLESPACE_NAME,
 INDEX_USED_PAGES(owner,index_name) page_used
 from dba_indexes 
 where tablespace_name not in ('TEMP','ROLL','SYSTEM') 
and owner not in ('SYS','SYSAUDITOR','SYSSSO','SCHEDULER')
and temporary='N'
and INDEX_TYPE != 'CLUSTER'
and INDEX_USED_PAGES(owner,index_name)> (select sum(TOTAL_SIZE)* 0 from v$datafile)
order by index_used_space(owner,table_name) desc)
order by page_used desc
limit 10
)
GROUP BY objname,objtype,TABLESPACE_NAME
order by TOT_BLOCKS DESC limit 10);

21.查询监视器信息

select
TO_CHAR(DW_CONN_TIME, 'YYYY-MM-DD HH24:MI:SS') CONN_TIME,
MON_CONFIRM,
MON_IP,
MON_ID,
MON_TERM
 from v$dmmonitor;

22.查询实例运行错误的日志

select * from V$INSTANCE_LOG_HISTORY where LEVEL$ not in ('INFO','WARN');

23.查询数据库中是否存在死锁

SELECT TO_CHAR(HAPPEN_TIME,'YYYY-MM-DD HH24:MI:SS') HAPPEN_TIME,SQL_TEXT  FROM V$DEADLOCK_HISTORY WHERE HAPPEN_TIME >DATEADD(DAY,-30,SYSDATE);

24.查询数据库中的已经运行后的慢SQL

SELECT SQL_TEXT,EXEC_TIME,FINISH_TIME FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;

25.查询数据库中运行报错的SQL语句

SELECT SQL_TEXT,ECPT_DESC,max(ERR_TIME)ERR_TIME FROM V$RUNTIME_ERR_HISTORY  group by SQL_TEXT,ECPT_DESC LIMIT 10;

26.查询数据库中正在运行的慢SQL

select *  from ( SELECT DATEDIFF(MS,LAST_RECV_TIME,SYSDATE) EXEC_TIME,
                            DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)) SLOW_SQL,
                            SESS_ID,
                            CURR_SCH,
                            THRD_ID,
                            LAST_RECV_TIME,
                            SUBSTR(CLNT_IP,8,13) CONN_IP
                       FROM V$SESSIONS
                      WHERE  1=1 
                   and STATE='ACTIVE'
                   ORDER BY 1 DESC) 
             where EXEC_TIME >= ? and LAST_RECV_TIME > TO_TIMESTAMP('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') LIMIT ?