DM-SQL语句

表空间查询

sql

-- 查看所有表空间
SELECT * FROM SYS."V$TABLESPACE";

-- 查看特定表空间的表信息（表空间：DEMO）
SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = 'DEMO';

类型：系统管理语句
功能：查询表空间物理存储信息

模式(Schema)查询

sql

-- 查看当前模式
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

-- 查看所有模式
SELECT DISTINCT OWNER FROM ALL_OBJECTS;

类型：元数据查询语句
功能：获取命名空间逻辑结构信息

数据查询(DQL)

sql

-- 表空间名：DEMO
-- 表名：TEST_CASE

-- DDL-建表格式
SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST_CASE', 'SYSDBA') FROM DUAL;
-- DDL-列结构查询
SELECT 
    COLUMN_NAME, DATA_TYPE, DATA_LENGTH, 
    NULLABLE, DATA_DEFAULT
FROM 
    ALL_TAB_COLUMNS
WHERE 
    TABLE_NAME = 'TEST_CASE'
    AND OWNER = (SELECT OWNER FROM DBA_TABLES 
                WHERE TABLE_NAME='TEST_CASE' 
                AND TABLESPACE_NAME='DEMO')
ORDER BY COLUMN_ID;

-- 查询表数据
-- 服务名：SYSDBA
-- 表名：TEST_CASE
SELECT * FROM SYSDBA.TEST_CASE;

类型：数据查询语言(DQL)
功能：检索表中的实际数据

常用数据库巡检SQL语句

数据库授权信息查询

sql

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;

该项需注意数据库授权的有效日期，到期前需及时进行更换。
授权CPU个数字段值（出现过因限制CPU使用个数导致DB性能下降）

查询数据库的实例信息

sql

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;

该sql查询的是数据库的安装信息参数，当需要进行实例迁移时需保障两端实例必须一致。
系统状态字段查询值如为非OPEN状态，需检查数据库是否正常
日志文件大小即redo日志不建议低于2G大小
数据库中各实例参数的含义以及重要查看如下文章

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

sql

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

这个值是数据库启动以后从0不断递增的值，当数据库重启后清空又从0计数。
该值可以用来做监控项判断某个时间段sql的执行情况

数据库表空间的状态检查

sql

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;

需重点关注表空间类型非联机的情况，需重点监控

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

sql

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;

MAIN|TEMP|ROLL|SYSTEM这几个表空间是由系统自动管理的不需要关注使用率情况
SQL的计算公式比较复杂，原因是因为一个表空间可以有多个数据文件，这些数据文件中如有一个设置的是自动拓展空间且无上限，那么就不应该关注使用率了。

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

sql

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;

MAIN|TEMP|ROLL|SYSTEM这几个表空间是由系统自动管理的不需要关注

查询数据库中的用户信息

sql

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;

需关注用户中是否存在密码设置有效期后到期的用户
需关注查询中的状态字段是正常状态还是非预期下的用户账号已被锁定

查询数据库中用户权限

sql

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;

需关注是否给应用用户授予DBA权限这类操作

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

sql

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';

需关注库中的函数、包函数、存储过程等对象是否报错，是否需要处理

查询数据库中的大表信息

sql

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

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

sql

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
)

长时间空闲会话检查

sql

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;

在达梦数据库中默认情况下不会断开数据库的会话连接
运维中出现过管理工具中执行一条insert语句未进行提交，导致其他事务插入该表时发生事务等待从而影响生产的情况
对于长时间的空闲会话，应该定期进行会话连接清理

查询数据库的redo日志大小

sql

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

单个redo文件不建议低于2g大小，最好保障每个redo大小日志都是一样的

数据字典的淘汰情况

sql

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

根据数据字典的使用/淘汰率，判断是否需要适当放大数据字典缓冲区参数

查询数据库中的无效索引

sql

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;

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

sql

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;

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

sql

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||'.'||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
)

查询监视器信息

sql

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

查询实例运行错误的日志

sql

SELECT * FROM V$INSTANCE_LOG_HISTORY WHERE LEVEL$ NOT IN ('INFO','WARN')

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

sql

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)

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

sql

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

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

sql

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

DM-SQL语句 ​

表空间查询 ​

模式(Schema)查询 ​

数据查询(DQL) ​

常用数据库巡检SQL语句 ​

数据库授权信息查询 ​

查询数据库的实例信息 ​

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

数据库表空间的状态检查 ​

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

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

查询数据库中的用户信息 ​

查询数据库中用户权限 ​

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

查询数据库中的大表信息 ​

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

长时间空闲会话检查 ​

查询数据库的redo日志大小 ​

数据字典的淘汰情况 ​

查询数据库中的无效索引 ​

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

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

查询监视器信息 ​

查询实例运行错误的日志 ​

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

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

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

DM-SQL语句

表空间查询

模式(Schema)查询

数据查询(DQL)

常用数据库巡检SQL语句

数据库授权信息查询

查询数据库的实例信息

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

数据库表空间的状态检查

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

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

查询数据库中的用户信息

查询数据库中用户权限

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

查询数据库中的大表信息

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

长时间空闲会话检查

查询数据库的redo日志大小

数据字典的淘汰情况

查询数据库中的无效索引

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

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

查询监视器信息

查询实例运行错误的日志

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

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

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