文章目录
前言
本篇博客主要讲一些 DM 数据库常用 SQL
1. 服务器资源
1.1 CPU使用率
SELECT
(STAT_VAL/100.0) AS RATIO
FROM
SYS.V$SYSSTAT
WHERE
UPPER(NAME) ='OS DM DATABASE CPU RATE';
1.2 内存使用率
SELECT
A.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
(
SELECT
STAT_VAL TOTAL_SIZE_BYTES
FROM
SYS.V$SYSSTAT
WHERE
UPPER(NAME)='MEMORY POOL SIZE IN BYTES'
)
A,
(
SELECT
STAT_VAL USED_SIZE_BYTES
FROM
SYS.V$SYSSTAT
WHERE
UPPER(NAME)='MEMORY USED BYTES'
)
B;
2 数据库实例管理
2.1 查询版本号
select id_code from dual;
2.2 查询ini配置
SELECT
NAME "参数名" ,
(CASE WHEN TYPE IN ('SYS','SESSION') THEN '动态参数' ELSE '静态参数' END CASE) "类型" ,
VALUE "当前会话值" ,
SYS_VALUE "系统值",
FILE_VALUE "INI文件值",
DESCRIPTION
FROM
V$PARAMETER;
常用的参数 name 为：
- SYSTEM_PATH-------实例数据存放目录
- MAX_SESSIONS------会话最大连接数
- PORT_NUM----------数据库端口号
- BUFFER------------系统缓冲区大小
- ARCH_INI----------是否开启归档
2.3 查询归档配置
SELECT
ARCH_TYPE "归档类型" ,
ARCH_DEST "归档保存路径" ,
ARCH_FILE_SIZE "归档文件大小",
ARCH_SPACE_LIMIT "归档上限",
ARCH_INCOMING_PATH "远程归档保存路径",
ARCH_LOCAL_SHARE "共享标记"
FROM
V$DM_ARCH_INI
2.4 数据库实例初始化参数
SELECT *FROM V$OPTION
2.5 查看数据库信息
SELECT
NAME "数据库名" ,
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节点数"
FROM
V$DATABASE
2.6 查看数据库实例信息
SELECT
INSTANCE_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"
FROM
V$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', '开发版')
||')'
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;
2.9 查询页大小，字符集大小
SELECT
SF_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 检查点配置信息
SELECT
CKPT_RLOG_SIZE "检查点日志大小",
CKPT_DIRTY_PAGES "产生多少脏页刷检查点",
CKPT_FLUSH_RATE "检查点刷盘比例",
CKPT_INTERVAL "检查点间隔时间" ,
CKPT_FLUSH_PAGES "每次检查点至少刷脏页数",
LAST_BEGIN_TIME "最近一次检查点开始时间",
LAST_END_TIME "最近一次检查点结束时间",
CKPT_TIME_USED "检查点使用时间"
FROM
V$CKPT;
3 数据对象管理
3.1 查看系统所有的动态视图名称
SELECT * FROM V$DYNAMIC_TABLES
3.2 查看系统提供的所有函数名称
SELECT NAME "名称",CLASS$ "类别" FROM V$IFUN
3.3 查看表中数据的变化信息
-要求AUTO_STAT_OBJ =1
SELECT
B.NAME "表名",
TOTAL_ROWS "总行数",
INSERT_ROWS "已插入行数",
DELETE_ROWS "已删除行数",
UPDATE_ROWS "已更新行数"
FROM
V$AUTO_STAT_TABLE_IDU A
LEFT JOIN SYSOBJECTS B
ON
A.ID = B.ID
3.4 查看单个表占用空间大小
SELECT
TABLE_USED_PAGES('USER_NAME', 'TB_CNB_PAY_TEMP_EMP')*PAGE/1024 "使用空间",
TABLE_USED_SPACE('USER_NAME', 'TB_CNB_PAY_TEMP_EMP')*PAGE/1024 "占用空间"
FROM
DUAL;
3.5 查看数据对象的定义语句
SELECT
DBMS_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') --存储过程
FROM
DUAL
3.6 查看表的主键列
select
COLS.NAME
from
SYS.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
where
CONS.TABLEID =
(
select
ID
from
SYS.SYSOBJECTS
where
SUBTYPE$ like '_TAB'
and SCHID =
(
select
id
from
sys.sysobjects
where
type$ = 'SCH'
and name = 'SYSDBA' --替换为模式名
)
and NAME = 'T1' --替换为表名
)
and INDS.ID = IND_OBJ.ID
and COLS.ID = CONS.TABLEID
and CONS.INDEXID = INDS.ID
and SF_COL_IS_IDX_KEY(INDS.KEYNUM, INDS.KEYINFO, COLS.COLID) = 1;
3.7 查看数据库中所有的索引信息
SELECT
I.TABLE_OWNER,
I.TABLE_NAME ,
O.OBJECT_NAME,
O.STATUS
FROM
USER_INDEXES I,
USER_OBJECTS O
WHERE
O.OBJECT_NAME=I.INDEX_NAME
AND O.STATUS ='VALID' ;
3.8 统计数据库中的所有的表数据量和DDL语句
DECLARE
TYPE 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 IS
SELECT
TAB_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_COLS
FROM
SYSOBJECTS USER_OBJ
INNER JOIN SYSOBJECTS SCH_OBJ
ON
SCH_OBJ.PID = USER_OBJ.ID
INNER JOIN SYSOBJECTS TAB_OBJ
ON
SCH_OBJ.ID = TAB_OBJ.SCHID
LEFT JOIN ALL_TABLES_DIS_INFO DIS_INFO
ON
DIS_INFO.SCHEMA_NAME = SCH_OBJ.NAME
AND DIS_INFO.TABLE_NAME = TAB_OBJ.NAME
WHERE
USER_OBJ.TYPE$ = 'UR'
AND USER_OBJ.SUBTYPE$ ='USER'
AND SCH_OBJ.TYPE$ = 'SCH'
AND SCH_OBJ.SUBTYPE$ IS NULL
AND TAB_OBJ.TYPE$ = 'SCHOBJ'
AND TAB_OBJ.SUBTYPE$ = 'UTAB'
AND TAB_OBJ.PID = -1
AND SCH_OBJ.NAME NOT IN ('DMHS', 'SYSDBA', 'SYS');
BEGIN
OPEN TB_CUR;
FETCH TB_CUR BULK COLLECT INTO V_TAB_ARR;
FOR I IN V_TAB_ARR.FIRST..V_TAB_ARR.COUNT
LOOP
EXECUTE 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语句
DECLARE
TYPE 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 IS
SELECT
I.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_STR
FROM
SYS.SYSOBJECTS I
LEFT JOIN SYS.SYSOBJECTS T
ON
I.PID = T.ID
LEFT JOIN SYS.SYSOBJECTS S
ON
T.SCHID = S.ID
LEFT JOIN SYS.SYSINDEXES SI
ON
I.ID = SI.ID
LEFT JOIN SYS.SYSCONS SC
ON
SC.TABLEID = T.ID
AND SC.INDEXID = I.ID
WHERE
T.TYPE$ ='SCHOBJ'
AND T.SUBTYPE$ = 'UTAB'
AND I.SUBTYPE$ = 'INDEX';
BEGIN
OPEN IDX_CUR;
FETCH IDX_CUR BULK COLLECT INTO V_IDX_ARR;
FOR I IN V_IDX_ARR.FIRST..V_IDX_ARR.COUNT
LOOP
EXECUTE 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语句
DECLARE
TYPE 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 IS
SELECT DISTINCT
PROC_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_STR
FROM
SYS.SYSOBJECTS PROC_OBJ,
SYS.SYSOBJECTS SCH_OBJ,
SYS.SYSOBJECTS USER_OBJ
WHERE
PROC_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;
BEGIN
OPEN PROC_CUR;
FETCH PROC_CUR BULK COLLECT INTO V_PROC_ARR;
FOR I IN V_PROC_ARR.FIRST..V_PROC_ARR.COUNT
LOOP
EXECUTE 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
DECLARE
TYPE T_NAME IS TABLE OF VARCHAR2(10);
CURSOR CUR_TAB
IS
SELECT
O2.NAME SCH_NAME,
O1.NAME TAB_NAME,
CONVERT(NUMBER(5, 2), INSERT_ROWS+DELETE_ROWS+UPDATE_ROWS)/CONVERT(NUMBER(5, 2), TOTAL_ROWS) PERC
FROM
V$AUTO_STAT_TABLE_IDU I
LEFT JOIN SYSOBJECTS O1
ON
O1.TYPE$ ='SCHOBJ'
AND O1.SUBTYPE$='UTAB'
AND I.ID = O1.ID
LEFT JOIN SYSOBJECTS O2
ON
O2.ID = O1.SCHID;
TAB_RECORD CUR_TAB%ROWTYPE;
BEGIN
OPEN CUR_TAB;
LOOP
FETCH CUR_TAB INTO TAB_RECORD;
EXIT WHEN CUR_TAB%NOTFOUND;
IF TAB_RECORD.PERC >= 0.15 THEN
PRINT(TAB_RECORD.SCH_NAME||'--'||TAB_RECORD.TAB_NAME);
END IF;
END LOOP;
END;
3.12 统计数据库中所有的触发器
3.12.1 库级触发器
select
*
from
(
(
select
TRIG_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_EVENT
from
(
select
DBTRIG_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_EVENT
from
SYS.SYSOBJECTS DBTRIG_OBJ_INNER,
SYS.SYSOBJECTS SCH_OBJ_INNER ,
SYS.SYSOBJECTS USER_OBJ_INNER
where
DBTRIG_OBJ_INNER.SUBTYPE$ ='TRIG'
and DBTRIG_OBJ_INNER.INFO1 & 0x00000002 = 0
and DBTRIG_OBJ_INNER.INFO1 & 0x000000C0 = 64
and USER_OBJ_INNER.SUBTYPE$ = 'USER'
and SCH_OBJ_INNER.ID = DBTRIG_OBJ_INNER.SCHID
and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID
and 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_OBJ
where
TRIG_OBJ.SCHID = SCH_OBJ.ID
)
)
order by
TRIG_TYPE,
TRIG_NAME
3.12.2 模式级触发器
select
*
from
(
(
select
TRIG_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_EVENT
from
(
select
SCHTRIG_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_EVENT
from
SYS.SYSOBJECTS SCHTRIG_OBJ_INNER,
SYS.SYSOBJECTS SCH_OBJ_INNER ,
SYS.SYSOBJECTS USER_OBJ_INNER
where
SCHTRIG_OBJ_INNER.SUBTYPE$ ='TRIG'
and SCHTRIG_OBJ_INNER.INFO1 & 0x000000C0 = 0
and USER_OBJ_INNER.SUBTYPE$ = 'USER'
and SCH_OBJ_INNER.ID = SCHTRIG_OBJ_INNER.SCHID
and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID
and 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_OBJ
where
TRIG_OBJ.SCHID = SCH_OBJ.ID
and TRIG_OBJ.PID = OBJ_SCH_OBJ.ID
)
)
order by
TRIG_TYPE,
TRIG_NAME
3.12.3 视图级触发器
select
*
from
(
(
select
TRIG_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_EVENT
from
(
select
VIEWTRIG_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_EVENT
from
SYS.SYSOBJECTS VIEWTRIG_OBJ_INNER,
SYS.SYSOBJECTS SCH_OBJ_INNER ,
SYS.SYSOBJECTS USER_OBJ_INNER
where
VIEWTRIG_OBJ_INNER.SUBTYPE$ ='TRIG'
and VIEWTRIG_OBJ_INNER.INFO1 & 0x00000002 = 2
and USER_OBJ_INNER.SUBTYPE$ = 'USER'
and SCH_OBJ_INNER.ID = VIEWTRIG_OBJ_INNER.SCHID
and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID
and 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,
(
select
ID ,
NAME,
SCHID
from
SYS.SYSOBJECTS
where
TYPE$ = 'SCHOBJ'
and SUBTYPE$ like 'VIEW'
)
OBJ_VIEW_OBJ
where
TRIG_OBJ.SCHID = SCH_OBJ.ID
and TRIG_OBJ.PID = OBJ_VIEW_OBJ.ID
and OBJ_VIEW_OBJ.SCHID = OBJ_SCH_OBJ.ID
)
)
order by
TRIG_TYPE,
TRIG_NAME
3.12.4 表级触发器
select
*
from
(
(
select
TRIG_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_EVENT
from
(
select
TABTRIG_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_EVENT
from
SYS.SYSOBJECTS TABTRIG_OBJ_INNER,
SYS.SYSOBJECTS SCH_OBJ_INNER ,
SYS.SYSOBJECTS USER_OBJ_INNER
where
TABTRIG_OBJ_INNER.SUBTYPE$ ='TRIG'
and TABTRIG_OBJ_INNER.INFO1 & 0x00000002 = 2
and USER_OBJ_INNER.SUBTYPE$ = 'USER'
and SCH_OBJ_INNER.ID = TABTRIG_OBJ_INNER.SCHID
and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID
and 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,
(
select
ID ,
NAME,
SCHID
from
SYS.SYSOBJECTS
where
TYPE$ = 'SCHOBJ'
and SUBTYPE$ like '_TAB'
)
OBJ_TAB_OBJ
where
TRIG_OBJ.SCHID = SCH_OBJ.ID
and TRIG_OBJ.PID = OBJ_TAB_OBJ.ID
and OBJ_TAB_OBJ.SCHID = OBJ_SCH_OBJ.ID
)
)
order by
TRIG_TYPE,
TRIG_NAME
4 用户管理
4.1 实例中用户信息
SELECT
D.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) "用户锁定状态"
FROM
SYSUSERS 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 查看回滚段信息
SELECT
N_ITEM "回滚项目个数",
ALLOC_PAGES*SF_GET_PAGE_SIZE()/1024/1024
||'MB' "分配空间" ,
TAB_ITEMS "表个数",
OBJ_COUNT "对象个数"
FROM
V$PSEG_SYS
5.1.3 查看每个回滚项信息
SELECT
N_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的事务数"
FROM
V$PSEG_ITEMS
5.1.4 查看回滚项中已提交但未 PURGE 的事务信息
SELECT TRX_ID "事务ID",CMT_TIME "事务提交时间" FROM V$PSEG_COMMIT_TRX
5.2 普通表空间
5.2.1 查看所有的数据文件使用情况
SELECT
PATH "路径" ,
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)
FROM
V$DATAFILE
5.2.2 查看表空间信息
SELECT
NAME "名称" ,
FILE_NUM "数据文件个数",
(CASE TYPE$ WHEN 1 THEN '普通表空间' ELSE THEN '临时表空间' END CASE),
TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024||'MB' "大小"
FROM
V$TABLESPACE
5.3 查看表空间中数据文件信息
SELECT
T.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 备份历史信息
SELECT
PATH "备份集路径" ,
START_TIME "开始时间" ,
END_TIME "结束时间" ,
DATEDIFF(SS, START_TIME, END_TIME) "备份时间(秒)",
READ_SIZE /1024/1024 "读取数据文件大小(MB)" ,
WRITE_SIZE/1024/1024 "备份集大小(MB)" ,
AVG_READ "平均读速度" ,
AVG_WRITE "平均写速度"
FROM
V$BACKUP_HISTORY
--备注：只会记录100条记录
6.4 对备份集进行校验
SF_BAKSET_CHECK('DISK','/home/dm_bak/db_bak_for_check')
--备注：返回1为合法
6.5 查看备份集及备份链信息
SELECT
LEVEL ,
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
(
SELECT
DEVICE_TYPE ,
BACKUP_ID ,
BACKUP_NAME ,
BASE_NAME ,
BACKUP_PATH ,
TYPE ,
LEVEL "LEVEL#",
"RANGE#" ,
OBJECT_NAME ,
BACKUP_TIME ,
CUMULATIVE
FROM
V$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 查看归档配置信息
SELECT
ARCH_NAME "配置名称",
ARCH_TYPE "归档类型",
ARCH_DEST "归档目的地",
ARCH_FILE_SIZE "单个归档文件大小",
ARCH_SPACE_LIMIT "归档空间上限",
ARCH_TIMER_NAME "定时器名称(ASYNC归档有效)",
ARCH_IS_VALID "归档配置是否有效",
ARCH_INCOMING_PATH "远程归档保存路径（REMOTE归档有效）"
FROM
SYS.V$DM_ARCH_INI;
6.9 查看redo日志文件信息
SELECT
FILE_ID "ID编号" ,
PATH "路径" ,
CREATE_TIME "创建时间",
RLOG_SIZE/1024/1024 "文件大小（MB）"
FROM
V$RLOGFILE
6.10 查看redo日志使用信息
SELECT
CKPT_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' "日志总空间"
FROM
V$RLOG
6.11 查看归档日志文件的具体信息
SELECT
NAME "名称" ,
SEQUENCE# "序号" ,
FIRST_CHANGE# "最小LSN",
NEXT_CHANGE# "最大LSN" ,
FIRST_TIME "开始时间" ,
NEXT_TIME "结束时间"
FROM
V$ARCHIVED_LOG
6.12 查看归档任务队列情况
SELECT
ARCH_TYPE "归档类型" ,
WAITING "等待处理任务数" ,
CUR_WAIT_TIME/1000||'S' "当前任务等待时间",
MAX_WAIT_TIME/1000||'S' "最大任务等待时间",
TOTAL_WAIT_TIME/1000||'S' "总等待时间",
AVERAGE_WAIT_TIME/1000||'S' "平均等待时间"
FROM
V$ARCH_QUEUE
7 会话与事务管理
7.1 查看活动会话信息
SELECT
SESS_ID "会话ID" ,
STATE "状态" ,
SQL_TEXT "SQL语句" ,
USER_NAME "用户" ,
TRX_ID "事务ID" ,
CREATE_TIME "创建时间",
CLNT_TYPE "客户端类型" ,
AUTO_CMT "是否自动提交" ,
CLNT_IP "客户端IP" ,
THRD_ID "线程ID"
FROM
V$SESSIONS
WHERE
STATE='ACTIVE'
7.2 查看会话统计信息
--当前活动会话数：
SELECT COUNT(*) FROM V$SESSIONS WHERE STATE='ACTIVE';
--当前总会话数：
SELECT COUNT(*) FROM V$SESSIONS;
--可用会话数=系统允许最大并发会话数-数据库当前会话数；
SELECT
PARA_VALUE-
(
SELECT COUNT(*) FROM V$SESSIONS
)
FROM
V$DM_INI
WHERE
PARA_NAME='MAX_SESSIONS';
7.3 查看当前活动事务数量
--当前活动事务数量，动态监控：
SELECT COUNT(*) FROM V$TRX WHERE STATUS='ACTIVE';
7.4 查看阻塞与被阻塞信息
SELECT
DS.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"
FROM
V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
SS.TRX_ID = L.TID
WHERE
L.BLOCKED = 1
-- 备注：“占用锁的会话ID”表示该会话占用这个对象的锁，且事务一直没有提交。导致“被阻塞的会话ID”无法对该对象上锁。有两种解决方式：
--（1）SP_CLOSE_SESSION(占用锁的会话ID);--杀掉占用锁的会话，让该不提交的事务回滚，释放锁；
--（2）SP_CLOSE_SESSION(被阻塞的会话ID);--杀掉被阻塞的会话；
7.5 查看会话统计信息
SELECT
SESSID "会话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连接次数"
FROM
V$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 查询已执行完毕但未提交事务
SELECT
T1.SQL_TEXT,
T1.STATE ,
T1.TRX_ID,
T1.SESS_ID
FROM
V$SESSIONS T1,
V$TRX T2
WHERE
T1.TRX_ID=T2.ID
AND T1.STATE ='IDLE'
AND T2.STATUS='ACTIVE';
7.9 脏数据占比统计
SELECT
(
(
SELECT
ISNULL(MAX(PARA_VALUE), '')
FROM
V$DM_INI
WHERE
UPPER(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)) "脏数据比例"
FROM
SYS.V$BUFFERPOOL
ORDER BY
NAME;
7.10 查看死锁信息
SELECT
TRX_ID "事务ID" ,
SESS_ID "会话ID" ,
SQL_TEXT "产生死锁的SQL语句",
HAPPEN_TIME "死锁时间"
FROM
V$DEADLOCK_HISTORY;
8 调度作业管理
8.1 正在运行的作业信息
SELECT
SID "SESSIONID" ,
LAST_DATE "上一次成功运行的时间",
THIS_DATE "本次运行开始时间"
FROM
V$JOBS_RUNNING
8.2 执行失败的定时任务统计
SELECT
NAME AS JOB_NAME,
ERRCODE AS ERR_CODE,
ERRINFO AS ERR_INFO,
START_TIME AS CUR_TIME
FROM
SYSJOB.SYSJOBHISTORIES2
WHERE
ERRCODE < 0
ORDER BY
START_TIME;
9 性能诊断
9.1 查看字典缓冲区使用情况
SELECT
TOTAL_SIZE/1024/1024||'MB' "总大小",
USED_SIZE/1024/1024||'MB' "已使用大小" ,
DICT_NUM "缓存字典个数",
LRU_DISCARD "被淘汰次数"
FROM
V$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) "命中率"
FROM
V$BUFFERPOOL
GROUP BY
NAME
9.3 查看SQL缓冲区的使用情况
SELECT TYPE$ "类型",COUNT(*)"申请次数",SUM(ITEM_SIZE)/1024/1024||'MB' "占用缓存大小" FROM V$CACHEITEM GROUP BY TYPE$
9.4 查看内存池的使用情况
SELECT
NAME "内存池名称",
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 "释放的次数"
FROM
V$MEM_POOL WHERE NAME LIKE '%SHARE%' OR NAME LIKE '%DICT%' OR NAME LIKE '%SQL%';
9.5 查看数据库内存的申请信息
--注：当动态参数MEMORY_LEAK_CHECK 为 1 时有效
SELECT
RG.POOL ,
SUM(RG.REFNUM) "申请发起次数",
SUM(RG.RESERVED_SIZE)/1024/1024||'MB' "占用的内存",
SUM(RG.DATA_SIZE)/1024/1024||'MB' "数据实际大小",
RG.FNAME
FROM
V$MEM_REGINFO RG
GROUP BY
POOL
9.6 显示支持的hint信息
SELECT *FROM V$HINT_INI_INFO
9.7 查看数据库读写统计数据
SELECT
NAME "名称",
STAT_VAL "统计值"
FROM
V$SYSSTAT
WHERE
NAME 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设置
SELECT
SESS_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解析方式"
FROM
V$SQL_HISTORY;
10.2 SQL语句执行历史报错信息
--要求ENABLE_MONITOR=1;
SELECT DISTINCT
A.SESS_ID "会话ID",
A.SQL_TEXT "SQL语句",
A.ECPT_DESC "错误描述",
A.ERR_TIME "时间",
B.USER_NAME "用户名",
B.CLNT_IP "IP地址",
B.APPNAME "客户端"
FROM
SYS.V$RUNTIME_ERR_HISTORY A,
SYS.V$SESSION_HISTORY B
10.3 DDL SQL执行历史信息
SELECT
SESS_ID "会话ID" ,
TRX_ID "事务ID" ,
SQL_TEXT "DDL SQL语句",
DDL_TIME "执行时间"
FROM
V$PLSQL_DDL_HISTORY
10.4 SQL等待信息
SELECT
S1.SQL_TEXT "SQL语句",
S2.SQL_TEXT "等待的SQL语句",
W.WAIT_TIME "等待时间"
FROM
V$TRXWAIT W
LEFT JOIN V$SESSIONS S1
ON
W.ID = S1.TRX_ID
LEFT JOIN V$SESSIONS S2
ON
W.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
SELECT
SESS_ID "会话ID",
SQL_TEXT "SQL语句",
SS "执行时间（秒）",
FULLSQL "完整SQL语句"
FROM
(
SELECT
SESS_ID ,
SQL_TEXT ,
DATEDIFF(SS, LAST_SEND_TIME, SYSDATE) SS,
SF_GET_SESSION_SQL(SESS_ID) FULLSQL
FROM
V$SESSIONS
WHERE
STATE='ACTIVE'
)
WHERE
SS>=1;
11 MPP相关
11.1 检查各节点参数一致性
SELECT
*
FROM
V$PARAMETER
WHERE
ID IN
(
SELECT
ID
FROM
(
SELECT DISTINCT * FROM V$PARAMETER
)
GROUP BY
ID
HAVING
COUNT(*)>1
)
ORDER BY
ID;
12 数据库巡检专用
12.1 dm.ini关键参数
SELECT
NAME as 名称 ,
DECODE(TYPE, 'READ ONLY', '手动', 'IN FILE', '静态', 'SYS', '系统级动态', 'SESSION', '会话级动态')as 类型 ,
VALUE as 当前会话值 ,
SYS_VALUE as 系统值 ,
FILE_VALUE as ini文件值,
DESCRIPTION as 参数描述
FROM
V$PARAMETER
WHERE
NAME 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 表空间
SELECT
NAME 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 包含文件数
FROM
V$TABLESPACE;
12.5 数据文件
SELECT
PATH 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 读写状态
FROM
V$DATAFILE;
12.6 会话统计
SELECT
STATE as 状态 ,
CLNT_IP as 连接IP,
COUNT(*)as 数量
FROM
V$SESSIONS
GROUP BY
STATE,
CLNT_IP
ORDER BY
STATE;
12.7 用户对象信息
SELECT
OBJECT_TYPE AS 类型 ,
OWNER AS 所属用户,
COUNT(*) AS 数量
FROM
ALL_OBJECTS
WHERE
OWNER NOT IN ('SYS', 'SYSTEM', 'SYSAUDITOR', 'SYSJOB', 'SYSSSO', 'CTISYS')
GROUP BY
OBJECT_TYPE,
OWNER;
12.8 最慢20条SQL
SELECT
TOP 20 TO_DATE(START_TIME, 'YYYY-MM-DD HH24:MI:SS') AS 执行起始时间,
(TIME_USED/1000)
||'MS' AS 执行时间,
TOP_SQL_TEXT AS SQL内容
FROM
V$SQL_HISTORY
ORDER BY
TIME_USED DESC;
303
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
