8.达梦数据库常用SQL

前言

本篇博客主要讲一些 DM 数据库常用 SQL

1. 服务器资源

1.1 CPU使用率

SELECT(STAT_VAL/100.0) AS RATIO
FROMSYS.V$SYSSTAT
WHEREUPPER(NAME) ='OS DM DATABASE CPU RATE';

1.2 内存使用率

SELECTA.TOTAL_SIZE_BYTES/1024/1024 "总计分配内存大小/GB",B.USED_SIZE_BYTES/1024/1024 "当前使用的内存大小/GB",(B.USED_SIZE_BYTES/CAST(decode(A.TOTAL_SIZE_BYTES, 0, 1, A.TOTAL_SIZE_BYTES) AS DOUBLE))*100 "使用率（%）"
FROM(SELECTSTAT_VAL TOTAL_SIZE_BYTESFROMSYS.V$SYSSTATWHEREUPPER(NAME)='MEMORY POOL SIZE IN BYTES')A,(SELECTSTAT_VAL USED_SIZE_BYTESFROMSYS.V$SYSSTATWHEREUPPER(NAME)='MEMORY USED BYTES')B;

2 数据库实例管理

2.1 查询版本号

select id_code from dual;

2.2 查询ini配置

SELECTNAME "参数名"     ,(CASE WHEN TYPE IN ('SYS','SESSION') THEN '动态参数' ELSE '静态参数' END CASE) "类型"      ,VALUE "当前会话值"  ,SYS_VALUE "系统值",FILE_VALUE "INI文件值",DESCRIPTION
FROMV$PARAMETER;

常用的参数 name 为：

• SYSTEM_PATH-------实例数据存放目录
• MAX_SESSIONS------会话最大连接数
• PORT_NUM----------数据库端口号
• BUFFER------------系统缓冲区大小
• ARCH_INI----------是否开启归档

2.3 查询归档配置

SELECTARCH_TYPE "归档类型"       ,ARCH_DEST "归档保存路径"     ,ARCH_FILE_SIZE "归档文件大小",ARCH_SPACE_LIMIT "归档上限",ARCH_INCOMING_PATH "远程归档保存路径",ARCH_LOCAL_SHARE "共享标记"
FROMV$DM_ARCH_INI

2.4 数据库实例初始化参数

SELECT *FROM V$OPTION

2.5 查看数据库信息

SELECTNAME "数据库名"                               ,CREATE_TIME "创建时间"                            ,(CASE ARCH_MODE WHEN 'N' THEN '未开归档' ELSE THEN '已开归档'  END CASE) "归档模式",LAST_CKPT_TIME "最后一次检查点时间"                        , (CASE ROLE$ WHEN 0 THEN '普通' WHEN 1 THEN '主库' WHEN 2 THEN '备库' END CASE) "数据库模式",TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024||'MB' "总大小 "                              ,(CASE DSC_NODES WHEN 1 THEN '非DSC' END CASE) "DSC节点数"
FROMV$DATABASE

2.6 查看数据库实例信息

SELECTINSTANCE_NAME "实例名称",STATUS$ "系统状态"      ,MODE$ "模式"      ,HOST_NAME "服务器名称"   ,DAYS_BETWEEN(SYSDATE,START_TIME) "持续运行时间(天)"   ,OGUID           ,(SELECT DECODE(UNICODE,0,'GB18030',1,'UNICODE')) "字符集",(SELECT PARA_VALUE FROM V$DM_INI WHERE PARA_NAME = 'PORT_NUM') "访问端口",(SELECT (CASE ARCH_MODE WHEN 'N' THEN '未开归档' ELSE THEN '已开归档'  END CASE) FROM V$DATABASE) "归档模式",(CASE DSC_ROLE WHEN 'NULL' THEN '非DSC' END CASE) "是否DSC"
FROMV$INSTANCE

2.7 查看数据库实例信息

select '版本',SVR_VERSION from v$instance union all
select '数据库名', NAME from v$database union all
select '实例名',INSTANCE_NAME from v$instance union all
select '系统状态',DECODE(STATUS$,'OPEN','打开','MOUNT','配置','SUSPEND','挂起') from v$instance union all
select '实例模式',DECODE(MODE$,'NORMAL','普通模式','PRIMARY','主机模式','STANDBY','备机模式') from v$instance union all
select '是否启用归档',DECODE(ARCH_MODE,'Y','是','N','否') from v$database union all
select '数据文件总大小', TOTAL_SIZE*PAGE/1024/1024||'MB' from v$database union all
select '数据库打开次数', OPEN_COUNT||'' from v$database union all
select '创建时间', cast(CREATE_TIME as varchar(50)) from v$database union all
select '启动时间',START_TIME||'' from v$instance union all
select '最后检查点时间', cast(LAST_CKPT_TIME as varchar(50)) from v$database;

2.8 查看授权信息

SELECT '许可证版本' AS 名称, 'V'||LIC_VERSION AS 信息 FROM V$LICENSE
UNION ALL
SELECT '序列号', SERIES_NO FROM V$LICENSE
UNION ALL
SELECT '校验码', CHECK_CODE FROM V$LICENSE
UNION ALL
SELECT '制作日期', CAST(DATE_GEN AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT '最终用户', AUTHORIZED_CUSTOMER FROM V$LICENSE
UNION ALL
SELECT '项目名称', PROJECT_NAME FROM V$LICENSE
UNION ALL
SELECT'产品名称',PRODUCT_TYPE||'('||DECODE(SERVER_SERIES, 'P', '个人版', 'S', '标准版', 'E', '企业版', 'A', '安全版', 'D', '开发版')||')'
FROMV$LICENSE
UNION ALL
SELECT'产品类型',DECODE(SERVER_TYPE, '1', '正式版', '2', '测试版', '3', '试用版')
FROMV$LICENSE
UNION ALL
SELECT '有效日期', CAST(EXPIRED_DATE AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT'授权系统',OS_TYPE||' '||DECODE(CPU_TYPE, 'X86', 'X86', 'X64', 'X64')
FROMV$LICENSE
UNION ALL
SELECT '授权用户数', CAST(AUTHORIZED_USER_NUMBER AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT '授权并发数', CAST(CONCURRENCY_USER_NUMBER AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT '授权CPU个数', CAST(MAX_CPU_NUM AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT '授权CPU核数', CAST(MAX_CORE_NUM AS VARCHAR(30)) FROM V$LICENSE
UNION ALL
SELECT'授权集群',DECODE(CLUSTER_TYPE, '0000', '无', '0001', 'RAC', '0010', '读写分离', '0011', '读写分离、RAC', '0100', 'MPP', '0101', 'MPP、RAC', '0110', 'MPP、读写分离', '0111', 'MPP、读写分离、RAC', '1000', '主备', '1001', '主备、RAC', '1010', '主备、读写分离', '1011', '主备、读写分离、RAC', '1100', '主备、MPP', '1101', '主备、MPP、RAC', '1110', '主备、MPP、读写分离', '1111', '主备、MPP、读写分离、RAC')
FROMV$LICENSE
UNION ALL
SELECT '未激活状态截止日期', CAST(NOACTIVE_DEADLINE AS VARCHAR(30)) FROM V$LICENSE;

2.9 查询页大小，字符集大小

SELECTSF_GET_PAGE_SIZE()/1024 "页大小(KB)"                            ,(CASE SF_GET_UNICODE_FLAG() WHEN 0 THEN 'GB18030' WHEN 1 THEN 'UFT-8' END CASE) "字符集", (CASE SF_GET_CASE_SENSITIVE_FLAG() WHEN 1 THEN '敏感' WHEN 0 THEN '不敏感' END CASE) "大小写是否敏感",SF_GET_SYSTEM_PATH() "数据库路径"
FROM DUAL

2.10 检查点配置信息

SELECTCKPT_RLOG_SIZE "检查点日志大小",CKPT_DIRTY_PAGES "产生多少脏页刷检查点",CKPT_FLUSH_RATE "检查点刷盘比例",CKPT_INTERVAL "检查点间隔时间" ,CKPT_FLUSH_PAGES "每次检查点至少刷脏页数",LAST_BEGIN_TIME "最近一次检查点开始时间",LAST_END_TIME "最近一次检查点结束时间",CKPT_TIME_USED "检查点使用时间"
FROMV$CKPT;

3 数据对象管理

3.1 查看系统所有的动态视图名称

SELECT * FROM V$DYNAMIC_TABLES

3.2 查看系统提供的所有函数名称

SELECT NAME "名称",CLASS$ "类别" FROM V$IFUN

3.3 查看表中数据的变化信息

-要求AUTO_STAT_OBJ =1
SELECTB.NAME "表名",TOTAL_ROWS "总行数",INSERT_ROWS "已插入行数",DELETE_ROWS "已删除行数",UPDATE_ROWS "已更新行数"
FROMV$AUTO_STAT_TABLE_IDU A
LEFT JOIN SYSOBJECTS B
ONA.ID = B.ID

3.4 查看单个表占用空间大小

SELECTTABLE_USED_PAGES('USER_NAME', 'TB_CNB_PAY_TEMP_EMP')*PAGE/1024 "使用空间",TABLE_USED_SPACE('USER_NAME', 'TB_CNB_PAY_TEMP_EMP')*PAGE/1024 "占用空间"
FROMDUAL;

3.5 查看数据对象的定义语句

SELECTDBMS_METADATA.GET_DDL('INDEX', 'IDX_T2_ID', 'SYSDBA'),--索引DBMS_METADATA.GET_DDL('TABLE', 'T1', 'SYSDBA') --表DBMS_METADATA.GET_DDL('FUNCTION', 'FUNC_NAME', 'SYSDBA') --函数DBMS_METADATA.GET_DDL('PROCEDURE', 'PROC_NAME', 'SYSDBA') --存储过程DBMS_METADATA.GET_DDL('PACKAGE', 'PGK_NAME', 'SYSDBA') --存储过程
FROMDUAL

3.6 查看表的主键列

selectCOLS.NAME
fromSYS.SYSCOLUMNS COLS,(select * from SYS.SYSCONS WHERE TYPE$ = 'P')CONS           ,SYS.SYSINDEXES INDS,(select ID, PID from SYS.SYSOBJECTS where SUBTYPE$='INDEX' AND VALID = 'Y')IND_OBJ
whereCONS.TABLEID =(selectIDfromSYS.SYSOBJECTSwhereSUBTYPE$ like '_TAB'and SCHID =(selectidfromsys.sysobjectswheretype$ = 'SCH'and name  = 'SYSDBA' --替换为模式名)and NAME = 'T1' --替换为表名)and INDS.ID                          = IND_OBJ.IDand COLS.ID                          = CONS.TABLEIDand CONS.INDEXID                         = INDS.IDand SF_COL_IS_IDX_KEY(INDS.KEYNUM, INDS.KEYINFO, COLS.COLID) = 1;

3.7 查看数据库中所有的索引信息

SELECTI.TABLE_OWNER,I.TABLE_NAME ,O.OBJECT_NAME,O.STATUS
FROMUSER_INDEXES I,USER_OBJECTS O
WHEREO.OBJECT_NAME=I.INDEX_NAMEAND O.STATUS     ='VALID' ;

3.8 统计数据库中的所有的表数据量和DDL语句

DECLARETYPE TYPE_TAB_RECORD IS RECORD(ID SYSOBJECTS.ID%TYPE,USER_NAME SYSOBJECTS.NAME%TYPE,SCH_NAME SYSOBJECTS.NAME%TYPE,TAB_NAME SYSOBJECTS.NAME%TYPE,TAB_ROWNUM BIGINT,USED_PAGES INT,USED_SPACE INT,DDL_STR CLOB,DIS_TYPE ALL_TABLES_DIS_INFO.DIS_TYPE%TYPE,DIS_COLS ALL_TABLES_DIS_INFO.DIS_COLS%TYPE);TYPE TAB_REC_ARR IS TABLE OF TYPE_TAB_RECORD;V_TAB_ARR TAB_REC_ARR;CURSOR TB_CUR ISSELECTTAB_OBJ.ID         ,USER_OBJ.NAME USER_NAME,SCH_OBJ.NAME SCH_NAME  ,TAB_OBJ.NAME TAB_NAME  ,0 TAB_ROWNUM       ,0 USED_PAGES       ,0 USED_SPACE       ,'' DDL_STR         ,DIS_INFO.DIS_TYPE      ,DIS_INFO.DIS_COLSFROMSYSOBJECTS USER_OBJINNER JOIN SYSOBJECTS SCH_OBJONSCH_OBJ.PID = USER_OBJ.IDINNER JOIN SYSOBJECTS TAB_OBJONSCH_OBJ.ID = TAB_OBJ.SCHIDLEFT JOIN ALL_TABLES_DIS_INFO DIS_INFOONDIS_INFO.SCHEMA_NAME = SCH_OBJ.NAMEAND DIS_INFO.TABLE_NAME  = TAB_OBJ.NAMEWHEREUSER_OBJ.TYPE$    = 'UR'AND USER_OBJ.SUBTYPE$ ='USER'AND SCH_OBJ.TYPE$     = 'SCH'AND SCH_OBJ.SUBTYPE$ IS NULLAND TAB_OBJ.TYPE$     = 'SCHOBJ'AND TAB_OBJ.SUBTYPE$  = 'UTAB'AND TAB_OBJ.PID       = -1AND SCH_OBJ.NAME NOT   IN ('DMHS', 'SYSDBA', 'SYS');
BEGINOPEN TB_CUR;FETCH TB_CUR BULK COLLECT INTO V_TAB_ARR;FOR I IN V_TAB_ARR.FIRST..V_TAB_ARR.COUNTLOOPEXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||V_TAB_ARR(I).SCH_NAME||'.'||V_TAB_ARR(I).TAB_NAME INTO V_TAB_ARR(I).TAB_ROWNUM;EXECUTE IMMEDIATE 'SELECT TABLEDEF('''||V_TAB_ARR(I).SCH_NAME||''','''||V_TAB_ARR(I).TAB_NAME||''')' INTO V_TAB_ARR(I).DDL_STR;EXECUTE IMMEDIATE 'SELECT TABLE_USED_PAGES('''||V_TAB_ARR(I).SCH_NAME||''','''||V_TAB_ARR(I).TAB_NAME||''')*'||'(PAGE/1024)' INTO V_TAB_ARR(I).USED_PAGES;EXECUTE IMMEDIATE 'SELECT TABLE_USED_SPACE('''||V_TAB_ARR(I).SCH_NAME||''','''||V_TAB_ARR(I).TAB_NAME||''')*'||'(PAGE/1024)' INTO V_TAB_ARR(I).USED_SPACE;END LOOP;SELECT ID,USER_NAME "用户名",SCH_NAME "模式名",TAB_NAME "表名",TAB_ROWNUM "表行数",USED_PAGES||'KB/'||USED_SPACE||'KB' "空间使用", DDL_STR "DDL语句",DIS_TYPE "分布类型",TO_CHAR(DIS_COLS) "分布列" FROM TABLE(V_TAB_ARR);
END;

3.9 统计数据库中所有的索引信息和DDL语句

DECLARETYPE TYPE_IDX_RECORD IS RECORD(ID SYSOBJECTS.ID%TYPE,SCH_NAME SYSOBJECTS.NAME%TYPE,TAB_NAME SYSOBJECTS.NAME%TYPE,IDX_NAME SYSOBJECTS.NAME%TYPE,CREATE_TIME TIMESTAMP(6),IS_VALID CHAR(1),USED_PAGES VARCHAR2(22),USED_SPACE VARCHAR2(22),IDX_TYPE$ VARCHAR2(20),CONS_TYPE$ VARCHAR2(20),DDL_STR CLOB);TYPE IDX_REC_ARR IS TABLE OF TYPE_IDX_RECORD;V_IDX_ARR IDX_REC_ARR;CURSOR IDX_CUR ISSELECTI.ID,S.NAME,T.NAME,I.NAME,I.CRTDATE,I.VALID,INDEX_USED_PAGES(SI.ID)*(PAGE/1024)||'KB',INDEX_USED_SPACE(SI.ID)*(PAGE/1024)||'KB',SI.XTYPE,SC.TYPE$,'' DDL_STRFROMSYS.SYSOBJECTS ILEFT JOIN SYS.SYSOBJECTS TONI.PID = T.IDLEFT JOIN SYS.SYSOBJECTS SONT.SCHID = S.IDLEFT JOIN SYS.SYSINDEXES SIONI.ID = SI.IDLEFT JOIN SYS.SYSCONS SCONSC.TABLEID = T.IDAND SC.INDEXID = I.IDWHERET.TYPE$    ='SCHOBJ'AND T.SUBTYPE$ = 'UTAB'AND I.SUBTYPE$ = 'INDEX';BEGINOPEN IDX_CUR;FETCH IDX_CUR BULK COLLECT INTO V_IDX_ARR;FOR I IN V_IDX_ARR.FIRST..V_IDX_ARR.COUNTLOOPEXECUTE IMMEDIATE 'SELECT INDEXDEF('||V_IDX_ARR(I).ID||','||'1)' INTO V_IDX_ARR(I).DDL_STR;END LOOP;SELECT ID,SCH_NAME "模式名",TAB_NAME "表名",IDX_NAME "索引名",CREATE_TIME "创建时间",IS_VALID "是否有效",USED_PAGES||'/'||USED_SPACE "空间使用",(CASE IDX_TYPE$ WHEN 0 THEN '聚集索引' WHEN 1 THEN '二级索引' ELSE (CASE CONS_TYPE$ WHEN 'P' THEN '主键索引' WHEN 'U' THEN '唯一索引' WHEN 'F' THEN '外键约束' ELSE '无' END CASE) END CASE) "索引类型",(CASE CONS_TYPE$ WHEN 'P' THEN '主键约束' WHEN 'U' THEN '唯一约束' WHEN 'F' THEN '外键约束' ELSE '无' END CASE) "约束类型",DDL_STR "DDL定义"FROM TABLE(V_IDX_ARR);
END;

3.10 统计数据库中所有的存储过程和函数及DDL语句

DECLARETYPE TYPE_PROC_RECORD IS RECORD(ID SYSOBJECTS.ID%TYPE,USER_NAME SYSOBJECTS.NAME%TYPE,SCH_NAME SYSOBJECTS.NAME%TYPE,PROC_NAME SYSOBJECTS.NAME%TYPE,TYPE$ VARCHAR2(20),CREATE_TIME TIMESTAMP(6),IS_VALID CHAR(1),DDL_STR CLOB);TYPE PROC_REC_ARR IS TABLE OF TYPE_PROC_RECORD;V_PROC_ARR PROC_REC_ARR;CURSOR PROC_CUR ISSELECT DISTINCTPROC_OBJ.ID,USER_OBJ.NAME,SCH_OBJ.NAME,PROC_OBJ.NAME,(CASE PROC_OBJ.INFO1 WHEN 0 THEN '函数' WHEN 1 THEN '存储过程' END CASE) ,PROC_OBJ.CRTDATE,PROC_OBJ.VALID,'' DDL_STRFROMSYS.SYSOBJECTS PROC_OBJ,SYS.SYSOBJECTS SCH_OBJ,SYS.SYSOBJECTS USER_OBJWHEREPROC_OBJ.SUBTYPE$ ='PROC' AND PROC_OBJ.INFO1 IN (0,1) AND USER_OBJ.SUBTYPE$ = 'USER' AND SCH_OBJ.ID = PROC_OBJ.SCHID AND SCH_OBJ.PID = USER_OBJ.ID;
BEGINOPEN PROC_CUR;FETCH PROC_CUR BULK COLLECT INTO V_PROC_ARR;FOR I IN V_PROC_ARR.FIRST..V_PROC_ARR.COUNTLOOPEXECUTE IMMEDIATE 'SELECT TXT FROM SYS.SYSTEXTS WHERE ID = '||V_PROC_ARR(I).ID INTO V_PROC_ARR(I).DDL_STR;END LOOP;SELECT ID,USER_NAME "用户名",SCH_NAME "模式名",PROC_NAME "过程名",TYPE$ "类型",CREATE_TIME "创建时间",IS_VALID "是否有效",DDL_STR "DDL语句" FROM TABLE(V_PROC_ARR);
END;

3.11 统计超过15%变化数据的表

-- 要求AUTO_STAT_OBJ =1
DECLARETYPE T_NAME IS TABLE OF VARCHAR2(10); CURSOR CUR_TABISSELECTO2.NAME SCH_NAME,O1.NAME TAB_NAME,CONVERT(NUMBER(5, 2), INSERT_ROWS+DELETE_ROWS+UPDATE_ROWS)/CONVERT(NUMBER(5, 2), TOTAL_ROWS) PERCFROMV$AUTO_STAT_TABLE_IDU ILEFT JOIN SYSOBJECTS O1ONO1.TYPE$   ='SCHOBJ'AND O1.SUBTYPE$='UTAB'AND I.ID       = O1.IDLEFT JOIN SYSOBJECTS O2ONO2.ID = O1.SCHID;TAB_RECORD CUR_TAB%ROWTYPE;
BEGINOPEN CUR_TAB;LOOPFETCH CUR_TAB INTO TAB_RECORD;EXIT WHEN CUR_TAB%NOTFOUND;IF TAB_RECORD.PERC >= 0.15 THENPRINT(TAB_RECORD.SCH_NAME||'--'||TAB_RECORD.TAB_NAME);END IF;END LOOP;
END;

3.12 统计数据库中所有的触发器

3.12.1 库级触发器

select*
from((selectTRIG_OBJ.ID          ,TRIG_OBJ.NAME TRIG_NAME      ,TRIG_OBJ.CRTDATE TRIG_CRTDATE,TRIG_OBJ.INFO1           ,TRIG_OBJ.VALID TRIG_VALID    ,SCH_OBJ.ID           ,SCH_OBJ.NAME SCH_OBJ_NAME    ,'D' TRIG_TYPE        ,NULL             ,NULL OBJ_SCH_OBJ_NAME    ,NULL             ,NULL OBJ_TV_NAME         ,TRIG_OBJ.TRIG_EVENTfrom(selectDBTRIG_OBJ_INNER.ID     ,DBTRIG_OBJ_INNER.NAME   ,DBTRIG_OBJ_INNER.CRTDATE,DBTRIG_OBJ_INNER.INFO1  ,DBTRIG_OBJ_INNER.VALID  ,DBTRIG_OBJ_INNER.SCHID  ,SF_GET_TRI_EVENT_FROM_INFO3(DBTRIG_OBJ_INNER.INFO3, DBTRIG_OBJ_INNER.INFO1) TRIG_EVENTfromSYS.SYSOBJECTS DBTRIG_OBJ_INNER,SYS.SYSOBJECTS SCH_OBJ_INNER   ,SYS.SYSOBJECTS USER_OBJ_INNERwhereDBTRIG_OBJ_INNER.SUBTYPE$                                                    ='TRIG'and DBTRIG_OBJ_INNER.INFO1 & 0x00000002                                                  = 0and DBTRIG_OBJ_INNER.INFO1 & 0x000000C0                                                  = 64and USER_OBJ_INNER.SUBTYPE$                                                      = 'USER'and SCH_OBJ_INNER.ID                                                         = DBTRIG_OBJ_INNER.SCHIDand SCH_OBJ_INNER.PID                                                        = USER_OBJ_INNER.IDand SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), DBTRIG_OBJ_INNER.PID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, DBTRIG_OBJ_INNER.ID) = 1)TRIG_OBJ,(select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH' and ID = 150994945)SCH_OBJwhereTRIG_OBJ.SCHID = SCH_OBJ.ID))
order byTRIG_TYPE,TRIG_NAME

3.12.2 模式级触发器

select*
from((selectTRIG_OBJ.ID              ,TRIG_OBJ.NAME TRIG_NAME      ,TRIG_OBJ.CRTDATE TRIG_CRTDATE    ,TRIG_OBJ.INFO1           ,TRIG_OBJ.VALID TRIG_VALID    ,SCH_OBJ.ID               ,SCH_OBJ.NAME SCH_OBJ_NAME    ,'S' TRIG_TYPE            ,OBJ_SCH_OBJ.ID           ,OBJ_SCH_OBJ.NAME OBJ_SCH_OBJ_NAME,NULL                 ,NULL OBJ_TV_NAME         ,TRIG_OBJ.TRIG_EVENTfrom(selectSCHTRIG_OBJ_INNER.ID     ,SCHTRIG_OBJ_INNER.NAME   ,SCHTRIG_OBJ_INNER.CRTDATE,SCHTRIG_OBJ_INNER.INFO1  ,SCHTRIG_OBJ_INNER.VALID  ,SCHTRIG_OBJ_INNER.SCHID  ,SCHTRIG_OBJ_INNER.PID    ,SF_GET_TRI_EVENT_FROM_INFO3(SCHTRIG_OBJ_INNER.INFO3, SCHTRIG_OBJ_INNER.INFO1) TRIG_EVENTfromSYS.SYSOBJECTS SCHTRIG_OBJ_INNER,SYS.SYSOBJECTS SCH_OBJ_INNER    ,SYS.SYSOBJECTS USER_OBJ_INNERwhereSCHTRIG_OBJ_INNER.SUBTYPE$                                                     ='TRIG'and SCHTRIG_OBJ_INNER.INFO1 & 0x000000C0                                                   = 0and USER_OBJ_INNER.SUBTYPE$                                                        = 'USER'and SCH_OBJ_INNER.ID                                                           = SCHTRIG_OBJ_INNER.SCHIDand SCH_OBJ_INNER.PID                                                          = USER_OBJ_INNER.IDand SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), SCHTRIG_OBJ_INNER.PID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, SCHTRIG_OBJ_INNER.ID) = 1)TRIG_OBJ,(select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH' and ID = 150994945)SCH_OBJ,(select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH')OBJ_SCH_OBJwhereTRIG_OBJ.SCHID = SCH_OBJ.IDand TRIG_OBJ.PID   = OBJ_SCH_OBJ.ID))
order byTRIG_TYPE,TRIG_NAME

3.12.3 视图级触发器

select*
from((selectTRIG_OBJ.ID              ,TRIG_OBJ.NAME TRIG_NAME      ,TRIG_OBJ.CRTDATE TRIG_CRTDATE    ,TRIG_OBJ.INFO1           ,TRIG_OBJ.VALID TRIG_VALID    ,SCH_OBJ.ID               ,SCH_OBJ.NAME SCH_OBJ_NAME    ,'V' TRIG_TYPE            ,OBJ_SCH_OBJ.ID           ,OBJ_SCH_OBJ.NAME OBJ_SCH_OBJ_NAME,OBJ_VIEW_OBJ.ID          ,OBJ_VIEW_OBJ.NAME OBJ_TV_NAME    ,TRIG_OBJ.TRIG_EVENTfrom(selectVIEWTRIG_OBJ_INNER.ID     ,VIEWTRIG_OBJ_INNER.NAME   ,VIEWTRIG_OBJ_INNER.CRTDATE,VIEWTRIG_OBJ_INNER.INFO1  ,VIEWTRIG_OBJ_INNER.VALID  ,VIEWTRIG_OBJ_INNER.SCHID  ,VIEWTRIG_OBJ_INNER.PID    ,SF_GET_TRI_EVENT_FROM_INFO3(VIEWTRIG_OBJ_INNER.INFO3, VIEWTRIG_OBJ_INNER.INFO1) TRIG_EVENTfromSYS.SYSOBJECTS VIEWTRIG_OBJ_INNER,SYS.SYSOBJECTS SCH_OBJ_INNER     ,SYS.SYSOBJECTS USER_OBJ_INNERwhereVIEWTRIG_OBJ_INNER.SUBTYPE$                                                      ='TRIG'and VIEWTRIG_OBJ_INNER.INFO1 & 0x00000002                                                = 2and USER_OBJ_INNER.SUBTYPE$                                                          = 'USER'and SCH_OBJ_INNER.ID                                                             = VIEWTRIG_OBJ_INNER.SCHIDand SCH_OBJ_INNER.PID                                                            = USER_OBJ_INNER.IDand SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), VIEWTRIG_OBJ_INNER.PID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, VIEWTRIG_OBJ_INNER.ID) = 1)TRIG_OBJ,(select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH' and ID = 150994945)SCH_OBJ,(select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH')OBJ_SCH_OBJ,(selectID  ,NAME,SCHIDfromSYS.SYSOBJECTSwhereTYPE$ = 'SCHOBJ'and SUBTYPE$ like 'VIEW')OBJ_VIEW_OBJwhereTRIG_OBJ.SCHID     = SCH_OBJ.IDand TRIG_OBJ.PID       = OBJ_VIEW_OBJ.IDand OBJ_VIEW_OBJ.SCHID = OBJ_SCH_OBJ.ID))
order byTRIG_TYPE,TRIG_NAME

3.12.4 表级触发器

select*
from((selectTRIG_OBJ.ID              ,TRIG_OBJ.NAME TRIG_NAME      ,TRIG_OBJ.CRTDATE TRIG_CRTDATE    ,TRIG_OBJ.INFO1           ,TRIG_OBJ.VALID TRIG_VALID    ,SCH_OBJ.ID               ,SCH_OBJ.NAME SCH_OBJ_NAME    ,'T' TRIG_TYPE            ,OBJ_SCH_OBJ.ID           ,OBJ_SCH_OBJ.NAME OBJ_SCH_OBJ_NAME,OBJ_TAB_OBJ.ID           ,OBJ_TAB_OBJ.NAME OBJ_TV_NAME     ,TRIG_OBJ.TRIG_EVENTfrom(selectTABTRIG_OBJ_INNER.ID     ,TABTRIG_OBJ_INNER.NAME   ,TABTRIG_OBJ_INNER.CRTDATE,TABTRIG_OBJ_INNER.INFO1  ,TABTRIG_OBJ_INNER.VALID  ,TABTRIG_OBJ_INNER.SCHID  ,TABTRIG_OBJ_INNER.PID    ,SF_GET_TRI_EVENT_FROM_INFO3(TABTRIG_OBJ_INNER.INFO3, TABTRIG_OBJ_INNER.INFO1) TRIG_EVENTfromSYS.SYSOBJECTS TABTRIG_OBJ_INNER,SYS.SYSOBJECTS SCH_OBJ_INNER    ,SYS.SYSOBJECTS USER_OBJ_INNERwhereTABTRIG_OBJ_INNER.SUBTYPE$                                                     ='TRIG'and TABTRIG_OBJ_INNER.INFO1 & 0x00000002                                                   = 2and USER_OBJ_INNER.SUBTYPE$                                                        = 'USER'and SCH_OBJ_INNER.ID                                                           = TABTRIG_OBJ_INNER.SCHIDand SCH_OBJ_INNER.PID                                                          = USER_OBJ_INNER.IDand SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), TABTRIG_OBJ_INNER.PID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, TABTRIG_OBJ_INNER.ID) = 1)TRIG_OBJ,(select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH' and ID = 150994945)SCH_OBJ,(select ID, NAME from SYS.SYSOBJECTS where TYPE$='SCH')OBJ_SCH_OBJ,(selectID  ,NAME,SCHIDfromSYS.SYSOBJECTSwhereTYPE$ = 'SCHOBJ'and SUBTYPE$ like '_TAB')OBJ_TAB_OBJwhereTRIG_OBJ.SCHID    = SCH_OBJ.IDand TRIG_OBJ.PID      = OBJ_TAB_OBJ.IDand OBJ_TAB_OBJ.SCHID = OBJ_SCH_OBJ.ID))
order byTRIG_TYPE,TRIG_NAME

4 用户管理

4.1 实例中用户信息

SELECTD.USERNAME "用户名",D.ACCOUNT_STATUS "用户状态",D.CREATED "创建时间",S.CONN_IDLE_TIME "最大空闲时间",S.FAILED_NUM "登录失败次数限制"  ,D.EXPIRY_DATE "过期时间",S.LIFE_TIME "口令剩余有效时间"   ,D.DEFAULT_TABLESPACE "默认表空间",(CASE S.LOCKED_STATUS WHEN 2 THEN 'UNLOCKED' WHEN 1 THEN 'LOCKED' END) "用户锁定状态"
FROMSYSUSERS S,DBA_USERS D
WHERE S.ID = D.USER_ID

5 表空间与数据文件管理

5.1 回滚表空间

5.1.1 显示当前 PURGE 回滚段信息

SELECT OBJ_NUM "待PURGE个数", IS_RUNNING "是否正在PURGE" FROM V$PURGE

5.1.2 查看回滚段信息

SELECTN_ITEM "回滚项目个数",ALLOC_PAGES*SF_GET_PAGE_SIZE()/1024/1024||'MB' "分配空间"  ,TAB_ITEMS "表个数",OBJ_COUNT "对象个数"
FROMV$PSEG_SYS

5.1.3 查看每个回滚项信息

SELECTN_PAGES*SF_GET_PAGE_SIZE()/1024/1024||'MB' "回滚页数",N_PURGED_PAGES*SF_GET_PAGE_SIZE()/1024/1024||'MB' "已经PURGE的页数",N_COMMIT_TRX*SF_GET_PAGE_SIZE()/1024/1024||'MB' "已提交但未PURGE的页数"    ,RESERVE_TIME "事务提交后最长保留时间",N_PURGING_TRX "正在PURGE的事务数"
FROMV$PSEG_ITEMS

5.1.4 查看回滚项中已提交但未 PURGE 的事务信息

SELECT TRX_ID "事务ID",CMT_TIME "事务提交时间" FROM V$PSEG_COMMIT_TRX

5.2 普通表空间

5.2.1 查看所有的数据文件使用情况

SELECTPATH "路径"     ,CREATE_TIME "创建时间",TOTAL_SIZE*PAGE_SIZE/1024/1024||'MB' "总大小",(TOTAL_SIZE-FREE_SIZE)*PAGE_SIZE/1024/1024||'MB' "已使用大小",(CASE AUTO_EXTEND WHEN 1 THEN '自动扩展' WHEN 0 THEN '不自动扩展' END CASE)
FROMV$DATAFILE

5.2.2 查看表空间信息

SELECTNAME "名称"    ,FILE_NUM "数据文件个数",(CASE TYPE$ WHEN 1 THEN '普通表空间' ELSE THEN '临时表空间' END CASE),TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024||'MB' "大小"
FROMV$TABLESPACE

5.3 查看表空间中数据文件信息

SELECTT.NAME          表空间名称,D.PATH       表空文件路径,T.TYPE$    表空间类型,T.STATUS$   表空间状态,T. FILE_NUM  包含的文件数,D.TOTAL_SIZE*16/1024  总大小,D.FREE_SIZE*16/1024  空闲大小,TRUNC((TRUNC(D.TOTAL_SIZE-D.FREE_SIZE, 4)/D.TOTAL_SIZE)*100, 2) 使用率
FROM    V$TABLESPACE T, V$DATAFILE D  WHERE   "GROUP_ID"=T.ID;

6 日志与备份管理

6.1 做数据库的全量备份

BACKUP DATABASE TO FULL_BAK_HAND BACKUPSET 'd:\dmdbms\data\DAMENG\bak\db_full_bak_hand';

6.2 做数据库的增量备份

BACKUP DATABASE INCREMENT WITH BACKUPDIR 'd:\dmdbms\data\DAMENG\bak\' to incre_bak_hand BACKUPSET 'd:\dmdbms\data\DAMENG\bak\db_incre_bak_hand';

6.3 备份历史信息

SELECTPATH "备份集路径"              ,START_TIME "开始时间"         ,END_TIME "结束时间"           ,DATEDIFF(SS, START_TIME, END_TIME) "备份时间(秒)",READ_SIZE /1024/1024 "读取数据文件大小(MB)"  ,WRITE_SIZE/1024/1024 "备份集大小(MB)"   ,AVG_READ "平均读速度"           ,AVG_WRITE "平均写速度"
FROMV$BACKUP_HISTORY
--备注：只会记录100条记录

6.4 对备份集进行校验

SF_BAKSET_CHECK('DISK','/home/dm_bak/db_bak_for_check')
--备注：返回1为合法

6.5 查看备份集及备份链信息

SELECTLEVEL                                                   ,TT.DEVICE_TYPE "介质类型"                                           ,TT.BACKUP_NAME "备份名称"                                           ,TT.BASE_NAME "基备份名称"                                            ,TT.BACKUP_PATH "备份路径"                                           ,(CASE TT.TYPE WHEN 0 THEN '基备份' WHEN 1 THEN '增量备份' WHEN 2 THEN '表备份' WHEN 3 THEN '归档备份' END CASE) "备份类型", (CASE TT."LEVEL#" WHEN 0 THEN '联机备份' WHEN 1 THEN '脱机备份' END CASE) "联机/脱机备份", (CASE TT."RANGE#" WHEN 1 THEN '库备份' WHEN 2 THEN '表空间备份' WHEN 3 THEN '表备份' WHEN 4 THEN '归档备份' END CASE) "备份内容",TT.OBJECT_NAME "备份对象名称"                                         ,TT.BACKUP_TIME "备份时间"                                           , (CASE WHEN TT.TYPE=1 AND TT.CUMULATIVE =1 THEN '累积增量备份' WHEN TT.TYPE=1 AND TT.CUMULATIVE =0 THEN '差异增量备份' END CASE) "增备类型"
FROM(SELECTDEVICE_TYPE   ,BACKUP_ID     ,BACKUP_NAME   ,BASE_NAME     ,BACKUP_PATH   ,TYPE      ,LEVEL "LEVEL#","RANGE#"      ,OBJECT_NAME   ,BACKUP_TIME   ,CUMULATIVEFROMV$BACKUPSET)TT START
WITH TYPE=0 CONNECT BY PRIOR BACKUP_NAME = BASE_NAME
-- 备注：只会搜索V$BACKUPSET_SEARCH_DIRS显示的路径下的备份；如果有备份集在其他路径下例如：d:\dmdbms\data\，需要执行SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK','d:\dmdbms\data\');将路径纳入进来才能查到对应的备份集信息，返回1为执行成功。
--同样的，移除可以使用SELECT SF_BAKSET_BACKUP_DIR_REMOVE('DISK',' d:\dmdbms\data\');返回1为执行成功。或者执行移除全部目录SELECT SF_BAKSET_BACKUP_DIR_REMOVE_ALL();

6.6 删除备份集

SELECT SF_BAKSET_REMOVE('DISK','d:\dmdbms\data\DAMENG\bak\DB_DAMENG_FULL_2021_12_23_09_46_01');
--返回1表示执行成功
--备注：如果要删除的备份集是基备份，那么需要执行级联删除命令，将依赖该备份集的备份一起删除：
SELECT SF_BAKSET_REMOVE('DISK','d:\dmdbms\data\DAMENG\bak\DB_DAMENG_FULL_2021_12_23_09_46_01',1); 返回1表示执行成功

6.7 批量删除数据库备份集

CALL SP_DB_BAKSET_REMOVE_BATCH('DISK', ADD_DAYS(NOW(), -7));
--备注：删除距今7天之前生成的备份集
--只会搜索V$BACKUPSET_SEARCH_DIRS显示的路径下的备份；如果有备份集在其他路径下例如：d:\dmdbms\data\，需要执行SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK','d:\dmdbms\data\');将路径纳入进来才能查到对应的备份集信息。

6.8 查看归档配置信息

SELECTARCH_NAME "配置名称",ARCH_TYPE  "归档类型",ARCH_DEST  "归档目的地",ARCH_FILE_SIZE  "单个归档文件大小",ARCH_SPACE_LIMIT "归档空间上限",ARCH_TIMER_NAME "定时器名称(ASYNC归档有效)",ARCH_IS_VALID "归档配置是否有效",ARCH_INCOMING_PATH "远程归档保存路径（REMOTE归档有效）"
FROMSYS.V$DM_ARCH_INI;

6.9 查看redo日志文件信息

SELECTFILE_ID "ID编号"    ,PATH "路径"     ,CREATE_TIME "创建时间",RLOG_SIZE/1024/1024 "文件大小（MB）"
FROMV$RLOGFILE

6.10 查看redo日志使用信息

SELECTCKPT_LSN "最近一次检查点LSN" ,FILE_LSN "已经到盘上的最大LSN",FLUSH_LSN "准备刷盘的LSN"  ,CUR_LSN "当前LSN"       ,FLUSH_PAGES*SF_GET_PAGE_SIZE()/1024/1024||'MB' "Flush链中的数据量",FLUSHING_PAGES*SF_GET_PAGE_SIZE()/1024/1024||'MB' "正在刷盘的数据量",FREE_SPACE/1024/1024||'MB' "可用日志空间",TOTAL_SPACE/1024/1024||'MB' "日志总空间"
FROMV$RLOG

6.11 查看归档日志文件的具体信息

SELECTNAME "名称"        ,SEQUENCE# "序号"       ,FIRST_CHANGE# "最小LSN",NEXT_CHANGE# "最大LSN" ,FIRST_TIME "开始时间"  ,NEXT_TIME "结束时间"
FROMV$ARCHIVED_LOG

6.12 查看归档任务队列情况

SELECTARCH_TYPE "归档类型"    ,WAITING "等待处理任务数"       ,CUR_WAIT_TIME/1000||'S' "当前任务等待时间",MAX_WAIT_TIME/1000||'S' "最大任务等待时间",TOTAL_WAIT_TIME/1000||'S' "总等待时间",AVERAGE_WAIT_TIME/1000||'S' "平均等待时间"
FROMV$ARCH_QUEUE

7 会话与事务管理

7.1 查看活动会话信息

SELECTSESS_ID "会话ID"    ,STATE "状态"    ,SQL_TEXT "SQL语句"  ,USER_NAME "用户"    ,TRX_ID "事务ID"     ,CREATE_TIME "创建时间",CLNT_TYPE "客户端类型" ,AUTO_CMT "是否自动提交" ,CLNT_IP "客户端IP"   ,THRD_ID "线程ID"
FROMV$SESSIONS
WHERESTATE='ACTIVE'

7.2 查看会话统计信息

--当前活动会话数：
SELECT COUNT(*) FROM V$SESSIONS WHERE STATE='ACTIVE';
--当前总会话数：
SELECT COUNT(*) FROM V$SESSIONS;
--可用会话数=系统允许最大并发会话数-数据库当前会话数；
SELECTPARA_VALUE-(SELECT COUNT(*) FROM V$SESSIONS)
FROMV$DM_INI
WHEREPARA_NAME='MAX_SESSIONS';

7.3 查看当前活动事务数量

--当前活动事务数量，动态监控：
SELECT COUNT(*) FROM V$TRX WHERE STATUS='ACTIVE';

7.4 查看阻塞与被阻塞信息

SELECTDS.SESS_ID "被阻塞的会话ID",DS.SQL_TEXT "被阻塞的SQL",DS.TRX_ID "被阻塞的事务ID",(CASE L.LTYPE WHEN 'OBJECT' THEN '对象锁' WHEN 'TID' THEN '事务锁' END CASE ) "被阻塞的锁类型",DS.CREATE_TIME "开始阻塞时间",SS.SESS_ID "占用锁的会话ID",SS.SQL_TEXT "占用锁的SQL",SS.CLNT_IP "占用锁的IP",L.TID "占用锁的事务ID"
FROMV$LOCK L
LEFT JOIN V$SESSIONS DS
ONDS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ONSS.TRX_ID = L.TID
WHEREL.BLOCKED = 1
-- 备注：“占用锁的会话ID”表示该会话占用这个对象的锁，且事务一直没有提交。导致“被阻塞的会话ID”无法对该对象上锁。有两种解决方式：
--（1）SP_CLOSE_SESSION(占用锁的会话ID);--杀掉占用锁的会话，让该不提交的事务回滚，释放锁；
--（2）SP_CLOSE_SESSION(被阻塞的会话ID);--杀掉被阻塞的会话；

7.5 查看会话统计信息

SELECTSESSID "会话ID"     ,HARD_PARSE_CNT "硬解析次数",LOGIC_READ_CNT "逻辑读次数",PHY_READ_CNT "物理读次数" ,PHY_MULTI_READ_CNT "物理读多页次数",IO_WAIT_TIME "IO等待时间",TAB_SCAN_CNT "全表扫描次数",HASH_JOIN_CNT "HASH连接次数"
FROMV$SESSION_STAT

7.6 数据库启动时的回滚的事务信息

select TRX_ID "事务号", N_UPAGES "回滚页总数"，N_URECS "回滚记录总数" from V$RECV_ROLLBACK_TRX

7.7 关闭一个活动会话

sp_close_session(session_id); 

7.8 查询已执行完毕但未提交事务

SELECTT1.SQL_TEXT,T1.STATE   ,T1.TRX_ID,T1.SESS_ID
FROMV$SESSIONS T1,V$TRX T2
WHERET1.TRX_ID=T2.IDAND T1.STATE ='IDLE'AND T2.STATUS='ACTIVE';

7.9 脏数据占比统计

SELECT((SELECTISNULL(MAX(PARA_VALUE), '')FROMV$DM_INIWHEREUPPER(PARA_NAME)='INSTANCE_NAME')||'_'||NAME) "内存池名称"                                         ,(CAST(N_PAGES AS NUMERIC(22, 0)) * CAST(PAGE_SIZE AS NUMERIC(22, 0)))/1024/1024 "分配内存大小(MB)",(CAST(N_DIRTY AS NUMERIC(22, 0)) * CAST(PAGE_SIZE AS NUMERIC(22, 0)))/1024/1024 "脏数据大小(MB)" ,(N_DIRTY             /CAST(DECODE(N_PAGES, 0, 1, N_PAGES) AS DOUBLE)) "脏数据比例"
FROMSYS.V$BUFFERPOOL
ORDER BYNAME;

7.10 查看死锁信息

SELECTTRX_ID "事务ID"   ,SESS_ID "会话ID"  ,SQL_TEXT "产生死锁的SQL语句",HAPPEN_TIME "死锁时间"
FROMV$DEADLOCK_HISTORY;

8 调度作业管理

8.1 正在运行的作业信息

SELECTSID "SESSIONID"       ,LAST_DATE "上一次成功运行的时间",THIS_DATE "本次运行开始时间"
FROMV$JOBS_RUNNING

8.2 执行失败的定时任务统计

SELECTNAME       AS JOB_NAME,ERRCODE    AS ERR_CODE,ERRINFO    AS ERR_INFO,START_TIME AS CUR_TIME
FROMSYSJOB.SYSJOBHISTORIES2
WHEREERRCODE < 0
ORDER BYSTART_TIME;

9 性能诊断

9.1 查看字典缓冲区使用情况

SELECTTOTAL_SIZE/1024/1024||'MB' "总大小",USED_SIZE/1024/1024||'MB' "已使用大小"   ,DICT_NUM "缓存字典个数",LRU_DISCARD "被淘汰次数"
FROMV$DB_CACHE

9.2 查看数据缓冲区使用情况

SELECT /*+GROUP_OPT_FLAG(1)*/NAME "缓冲区名称",SUM(N_PAGES)*PAGE_SIZE/1024/1024||'MB' "总大小(不含扩展池)",SUM(N_TOTAL_PAGES)*PAGE_SIZE/1024/1024||'MB' "总大小(含扩展池)",SUM(FREE)*PAGE_SIZE/1024/1024||'MB' "空闲大小",SUM(N_DIRTY)*PAGE_SIZE/1024/1024||'MB' "脏数据大小",SUM(N_CLEAR)*PAGE_SIZE/1024/1024||'MB' "干净数据大小",AVG(RAT_HIT) "命中率"
FROMV$BUFFERPOOL
GROUP BYNAME

9.3 查看SQL缓冲区的使用情况

SELECT TYPE$ "类型",COUNT(*)"申请次数",SUM(ITEM_SIZE)/1024/1024||'MB' "占用缓存大小" FROM V$CACHEITEM GROUP BY TYPE$

9.4 查看内存池的使用情况

SELECTNAME "内存池名称",ORG_SIZE/1024/1024||'MB' "初始大小",TOTAL_SIZE/1024/1024||'MB' "当前总大小",RESERVED_SIZE/1024/1024||'MB' "已分配出去的大小",TARGET_SIZE/1024/1024||'MB' "扩展目标大小"           ,N_EXTEND_NORMAL "TARGET范围内累计扩展次数"      ,N_EXTEND_EXCLUSIVE "超过 TARGET累计扩展次数",N_FREE "释放的次数"
FROMV$MEM_POOL WHERE NAME LIKE '%SHARE%' OR NAME LIKE '%DICT%' OR NAME LIKE '%SQL%';

9.5 查看数据库内存的申请信息

--注：当动态参数MEMORY_LEAK_CHECK 为 1 时有效
SELECTRG.POOL        ,SUM(RG.REFNUM) "申请发起次数",SUM(RG.RESERVED_SIZE)/1024/1024||'MB' "占用的内存",SUM(RG.DATA_SIZE)/1024/1024||'MB' "数据实际大小",RG.FNAME
FROMV$MEM_REGINFO RG
GROUP BYPOOL

9.6 显示支持的hint信息

SELECT *FROM V$HINT_INI_INFO

9.7 查看数据库读写统计数据

SELECTNAME "名称",STAT_VAL "统计值"
FROMV$SYSSTAT
WHERENAME IN('logic read count', 'physical read count', 'physical write count');

9.8 收集统计信息

9.8.1 收集user_name用户下所有表的统计信息

DBMS_STATS.GATHER_SCHEMA_STATS('NW_TEST',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

9.8.2 收集user_name用户下某个表的统计信息

DBMS_STATS.GATHER_TABLE_STATS('USER_NAME','GL_PREBALANCE',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

9.8.3 收集某个表某列的统计信息

STAT 100 ON T1(ID)

9.8.4 收集user_name用户下所有索引统计信息

DBMS_STATS.GATHER_SCHEMA_STATS('用户名',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');

9.8.5 收集数据库中某个索引的统计信息

DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T2_X');

10 SQL执行相关

10.1 SQL执行历史信息

--要求ENABLE_MONITOR=1;保存条数依赖于dm.ini参数SQL_HISTORY_CNT设置
SELECTSESS_ID "会话ID"     ,TOP_SQL_TEXT "SQL语句"   ,START_TIME "SQL开始执行时间" ,TIME_USED "SQL执行时间（毫秒）",N_LOGIC_READ "逻辑读次数"   ,N_PHY_READ "物理读次数"     ,(CASE HARD_PARSE_FLAG WHEN 0 THEN '软解析' WHEN 1 THEN '语义解析' WHEN 2 THEN '硬解析' END CASE) "SQL解析方式"
FROMV$SQL_HISTORY;

10.2 SQL语句执行历史报错信息

--要求ENABLE_MONITOR=1;
SELECT DISTINCTA.SESS_ID  "会话ID",A.SQL_TEXT "SQL语句",A.ECPT_DESC "错误描述",A.ERR_TIME "时间",B.USER_NAME "用户名",B.CLNT_IP  "IP地址",B.APPNAME "客户端"
FROMSYS.V$RUNTIME_ERR_HISTORY A,SYS.V$SESSION_HISTORY B

10.3 DDL SQL执行历史信息

SELECTSESS_ID "会话ID"      ,TRX_ID "事务ID"       ,SQL_TEXT "DDL SQL语句",DDL_TIME "执行时间"
FROMV$PLSQL_DDL_HISTORY

10.4 SQL等待信息

SELECTS1.SQL_TEXT "SQL语句",S2.SQL_TEXT "等待的SQL语句",W.WAIT_TIME "等待时间"
FROMV$TRXWAIT W
LEFT JOIN V$SESSIONS S1
ONW.ID = S1.TRX_ID
LEFT JOIN V$SESSIONS S2
ONW.WAIT_FOR_ID = S2.TRX_ID

10.5 开启/关闭SQL日志

--开启跟踪日志
SP_SET_PARA_VALUE(1,'SVR_LOG',1);
--关闭跟踪日志
SP_SET_PARA_VALUE(1,'SVR_LOG',0);
在DM_HOME/log/下生成dmsql****.log的文本文件，里面记录每条SQL的执行情况

10.6 查询当前数据库中执行时间超过1秒的SQL

SELECTSESS_ID "会话ID",SQL_TEXT "SQL语句",SS "执行时间（秒）",FULLSQL "完整SQL语句"
FROM(SELECTSESS_ID                 ,SQL_TEXT                ,DATEDIFF(SS, LAST_SEND_TIME, SYSDATE) SS,SF_GET_SESSION_SQL(SESS_ID) FULLSQLFROMV$SESSIONSWHERESTATE='ACTIVE')
WHERESS>=1;

11 MPP相关

11.1 检查各节点参数一致性

SELECT*
FROMV$PARAMETER
WHEREID IN(SELECTIDFROM(SELECT DISTINCT * FROM V$PARAMETER)GROUP BYIDHAVINGCOUNT(*)>1)
ORDER BYID;

12 数据库巡检专用

12.1 dm.ini关键参数

SELECTNAME                                        as 名称    ,DECODE(TYPE, 'READ ONLY', '手动', 'IN FILE', '静态', 'SYS', '系统级动态', 'SESSION', '会话级动态')as 类型    ,VALUE                                           as 当前会话值 ,SYS_VALUE                                       as 系统值   ,FILE_VALUE                                      as ini文件值,DESCRIPTION                                     as 参数描述
FROMV$PARAMETER
WHERENAME IN ('INSTANCE_NAME', 'MAX_OS_MEMORY', 'MEMORY_POOL', 'BUFFER', 'BUFFER_POOLS', 'MAX_BUFFER', 'RECYCLE', 'RECYCLE_POOLS', 'HJ_BUF_GLOBAL_SIZE', 'HJ_BUF_SIZE', 'DICT_BUF_SIZE', 'TEMP_SIZE', 'VM_POOL_SIZE', 'SESS_POOL_SIZE', 'CACHE_POOL_SIZE', 'WORKER_THREADS', 'TASK_THREADS', 'MAX_SESSION_STATEMENT', 'OPTIMIZER_MODE', 'VIEW_PULLUP_FLAG', 'COMPATIBLE_MODE', 'SVR_LOG', 'MAX_SESSIONS', 'USE_PLN_POOL', 'ENABLE_MONITOR', 'OLAP_FLAG', 'ARCH_INI');

12.2 授权信息

SELECT '许可证版本' AS 名称,'V'||LIC_VERSION AS 信息 FROM V$LICENSE UNION ALL
SELECT '序列号',SERIES_NO FROM V$LICENSE UNION ALL
SELECT '校验码',CHECK_CODE FROM V$LICENSE UNION ALL
SELECT '制作日期',CAST(DATE_GEN AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '最终用户',AUTHORIZED_CUSTOMER FROM V$LICENSE UNION ALL
SELECT '项目名称',PROJECT_NAME FROM V$LICENSE UNION ALL
SELECT '产品名称',PRODUCT_TYPE||'('||DECODE(SERVER_SERIES,'P','个人版','S','标准版','E','企业版','A','安全版','D','开发版')||')' FROM V$LICENSE UNION ALL
SELECT '产品类型',DECODE(SERVER_TYPE,'1','正式版','2','测试版','3','试用版') FROM V$LICENSE UNION ALL
SELECT '有效日期',CAST(EXPIRED_DATE AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授权系统',OS_TYPE||' '||DECODE(CPU_TYPE,'X86','X86','X64','X64') FROM V$LICENSE UNION ALL
SELECT '授权用户数',CAST(AUTHORIZED_USER_NUMBER AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授权并发数',CAST(CONCURRENCY_USER_NUMBER AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授权CPU个数',CAST(MAX_CPU_NUM AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授权CPU核数',CAST(MAX_CORE_NUM AS VARCHAR(30)) FROM V$LICENSE UNION ALL
SELECT '授权集群',DECODE(CLUSTER_TYPE,'0000','无','0001','RAC','0010','读写分离','0011','读写分离、RAC','0100','MPP','0101','MPP、RAC','0110','MPP、读写分离','0111','MPP、读写分离、RAC','1000','主备','1001','主备、RAC','1010','主备、读写分离','1011','主备、读写分离、RAC','1100','主备、MPP','1101','主备、MPP、RAC','1110','主备、MPP、读写分离','1111','主备、MPP、读写分离、RAC') FROM V$LICENSE UNION ALL
SELECT '未激活状态截止日期',CAST(NOACTIVE_DEADLINE AS VARCHAR(30)) FROM V$LICENSE;

12.3 数据库实例信息

SELECT '版本',SVR_VERSION FROM V$INSTANCE UNION ALL
SELECT '数据库名', NAME FROM V$DATABASE UNION ALL
SELECT '实例名',INSTANCE_NAME FROM V$INSTANCE UNION ALL
SELECT '系统状态',DECODE(STATUS$,'OPEN','打开','MOUNT','配置','SUSPEND','挂起') FROM V$INSTANCE UNION ALL
SELECT '实例模式',DECODE(MODE$,'NORMAL','普通模式','PRIMARY','主机模式','STANDBY','备机模式') FROM V$INSTANCE UNION ALL
SELECT '是否启用归档',DECODE(ARCH_MODE,'Y','是','N','否') FROM V$DATABASE UNION ALL
SELECT '数据文件总大小', TOTAL_SIZE*PAGE/1024/1024||'MB' FROM V$DATABASE UNION ALL
SELECT '数据库打开次数', OPEN_COUNT||'' FROM V$DATABASE UNION ALL
SELECT '创建时间', CAST(CREATE_TIME AS VARCHAR(50)) FROM V$DATABASE UNION ALL
SELECT '启动时间',START_TIME||'' FROM V$INSTANCE UNION ALL
SELECT '最后检查点时间', CAST(LAST_CKPT_TIME AS VARCHAR(50)) FROM V$DATABASE;

12.4 表空间

SELECTNAME                                          AS 名称,DECODE(TYPE$, '1', 'DB类型', '2', '临时表空间')                      AS 类型,DECODE(STATUS$, '0', 'ONLINE', '1', 'OFFLINE', '2', 'RES_OFFLINE', '3', 'CORRUPT')AS 状态,TOTAL_SIZE*PAGE/1024/1024||'MB'   AS 总大小,FILE_NUM AS 包含文件数
FROMV$TABLESPACE;

12.5 数据文件

SELECTPATH AS 路径,(TOTAL_SIZE*PAGE/1024/1024||'MB')AS 总大小,(FREE_SIZE*PAGE/1024/1024||'MB')AS 剩余大小,(CAST((TOTAL_SIZE-FREE_SIZE)*100/TOTAL_SIZE AS NUMERIC(4, 2))||'%')     AS 使用比例     ,MAX_SIZE       AS 扩充上限     ,CREATE_TIME    AS 创建日期     ,MODIFY_TIME    AS 修改时间     ,LAST_CKPT_TIME AS 最后一次检查点时间,STATUS$            ,DECODE(RW_STATUS, '1', '读', '2', '写')AS 读写状态
FROMV$DATAFILE;

12.6 会话统计

SELECTSTATE   as 状态  ,CLNT_IP as 连接IP,COUNT(*)as 数量
FROMV$SESSIONS
GROUP BYSTATE,CLNT_IP
ORDER BYSTATE;

12.7 用户对象信息

SELECTOBJECT_TYPE AS 类型  ,OWNER       AS 所属用户,COUNT(*)    AS 数量
FROMALL_OBJECTS
WHEREOWNER NOT IN ('SYS', 'SYSTEM', 'SYSAUDITOR', 'SYSJOB', 'SYSSSO', 'CTISYS')
GROUP BYOBJECT_TYPE,OWNER;

12.8 最慢20条SQL

SELECTTOP 20 TO_DATE(START_TIME, 'YYYY-MM-DD HH24:MI:SS') AS 执行起始时间,(TIME_USED/1000)||'MS'       AS 执行时间,TOP_SQL_TEXT AS SQL内容
FROMV$SQL_HISTORY
ORDER BYTIME_USED DESC;

参考内容

• 达梦社区