达梦数据库常用的61个脚本，速来下载！

青年数据库学习互助会 2025-04-16

535

1.查看版本

版本:

Select * from v$version;

小版本号:

select BUILD_VERSION from v$instance;

补丁号:

select id_code();

2.数据库基本信息

数据库名、归档模式、创建时间、最近一次启动时间

select NAME,ARCH_MODE,CREATE_TIME,LAST_STARTUP_TIME FROM V$DATABASE;

初始化参数:

select '实例名称' 数据库选项,INSTANCE_NAME 数据库集群相关参数值 FROM v$instance union all
select '数据库版本',substr(svr_version,instr(svr_version,'(')) FROM v$instance 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(PAGE()/1024 as varchar) union all   SELECT '簇大小',cast(SF_GET_EXTENT_SIZE() as varchar) union all
SELECT '大小写敏感',cast(SF_GET_CASE_SENSITIVE_FLAG() as varchar) union all
select '数据库模式',MODE$ from v$instance union all
select '唯一魔数',cast(permanent_magic as varchar) union all
select 'LSN',cast(cur_lsn as varchar) from v$rlog;

select '实例名称' 数据库选项,INSTANCE_NAME  数据库集群相关参数值 FROM v$instance union all
select top 1 '数据库版本', banner ||'.'||id_code from v$version
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(PAGE()/1024 as varchar) union all
SELECT '簇大小',cast(SF_GET_EXTENT_SIZE() as varchar) union all
SELECT '大小写敏感',cast(SF_GET_CASE_SENSITIVE_FLAG() as varchar) union all
select '数据库模式',MODE$ from v$instance;

3.服务器资源信息

CPU、内存、磁盘等

SELECT N_CPU,TOTAL_PHY_SIZE,FREE_PHY_SIZE,TOTAL_DISK_SIZE,FREE_DISK_SIZE,DRIVER_NAME FROM V$SYSTEMINFO;

4.字符集

select unicode();
Select sf_get_unicode_flag();
SELECT (CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' THEN 'UTF-8' WHEN '2' THEN 'EUC-KR' END);

其中：0表示GB18030；1表示UTF-8; 2表示EUC-KR

5.大小写敏感

查询大小写是否敏感:

SELECT SF_GET_CASE_SENSITIVE_FLAG();
SELECT CASE_SENSITIVE();

6.页大小

SELECT SF_GET_PAGE_SIZE();
SELECT CAST(PAGE()/1024 AS VARCHAR);

7.簇大小

SELECT SF_GET_EXTENT_SIZE();

8.数据文件信息

表空间、数据文件路径名称、自动扩展、最大扩展

SET PAGESIZE 100;
SELECT TABLESPACE_NAME,FILE_NAME,BYTES,STATUS,AUTOEXTENSIBLE,MAXBYTES FROM DBA_DATA_FILES;

9.表空间

表空间使用率:

select
a.tablespace_name space_name          ,
total        /1024/1024/1024 "总大小(G)"       ,
free         /1024/1024/1024 "可使用(G)"       ,
(total       -free) /1024/1024/1024 "已使用（G）",
round((total -free)/total, 4)*100 "使用率"
from
(
select
tablespace_name,
sum(bytes) free
from
dba_free_space
group by
tablespace_name
)
a,
(
select
tablespace_name,
sum(bytes) total
from
dba_data_files
group by
tablespace_name
)
b
where
a.tablespace_name = b.tablespace_name
ORDER BY
"使用率" DESC;

SELECT a.tablespace_name "表空间名称",
total / (1024 * 1024) "表空间大小(M)",
free / (1024 * 1024) "表空间剩余大小(M)",
(total - free) / (1024 * 1024 ) "表空间使用大小(M)",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

10.Temp表空间

TEMP 表空间完全由 DM 数据库自动维护。当用户的 SQL 语句需要磁盘空间来完成某个操作时，DM 数据库会从 TEMP 表空间分配临时段。如创建索引、无法在内存中完成的排序操作、SQL 语句中间结果集以及用户创建的临时表等都会使用到 TEMP 表空间。

查看方法如下：

SELECT a.tablespace_name "表空间名称",
total / (1024 * 1024) "表空间大小(M)",
free / (1024 * 1024) "表空间剩余大小(M)",
(total - free) / (1024 * 1024 ) "表空间使用大小(M)",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

11.控制文件

控制文件信息

show parameter ctl;
Select para_name,para_value from v$dm_ini where para_name='CTL_PATH';
select name,type,value,default_value,isdefault from v$parameter where name like '%CTL%';

转储控制文件

dmctlcvt TYPE=1 SRC=/dm8/ctl/dm01.ctl DEST=/dm8/tmp/20250306_dmctl.txt

转储控制文本文件为二进制

dmctlcvt TYPE=2 SRC=/dm8/tmp/20250306_dmctl.txt DEST=/dm8/ctl02/dm02.ctl

12.REDO日志文件

查看:

select group_id,file_id,path,rlog_size,create_time from v$rlogfile;

SELECT
A.FILE_ID    ,
A.PATH       ,
A.CLIENT_PATH,
A.RLOG_SIZE  ,
B.FREE_SPACE ,
B.TOTAL_SPACE,
B.CUR_FILE
from
(
select * from V$RLOGFILE where true
)  A,
(
select * from V$RLOG where true
)  B;

添加REDO日志文件:

ALTER DATABASE ADD LOGFILE '/dm8/redo/dm_redo03.log' size 1024;

扩展REDO日志文件

alter database resize logfile '/dm8/redo/dm_redo03.log' to 2048;

13.实例初始化

[dmdba@cjc-db-01 dmdbms]$ dminit help
...
CHARSET/UNICODE_FLAG        CHARSET(0), optional value:0[GB18030],1[UTF-8],2[EUC-KR]

可以看到字符集默认GB18030，本次选择字符集为UTF-8。

开始初始化

注意：

部分参数只有初始化时可以指定，一旦初始化完成，不再支持参数的修改，例如CHARSET等参数。

dminit path=/dm8/data CTL_PATH=/dm8/ctl/dm01.ctl LOG_PATH=/dm8/redo/dm_redo01.log LOG_PATH=/dm8/redo/dm_redo02.log LOG_SIZE=2048 CASE_SENSITIVE=N CHARSET=1 DB_NAME=cjc INSTANCE_NAME=cjc EXTENT_SIZE=32 PAGE_SIZE=32 SYSDBA_PWD=************ SYSAUDITOR_PWD=************ BUFFER=2048 PORT_NUM=5238 AUTO_OVERWRITE=0

参数说明

Path: 初始数据库存放路径
CTL_PATH：控制文件路径
LOG_PATH：日志文件路径
CASE_SENSENSITIVE：大小写敏感，可选择值，Y/N 1/0
CHARSET：字符集：0（GB18030）、1（UTF-8）、2（EUC-KR）
DB_NAME：数据库名
INSTANCE_NAME：实例名
SYSDBA_PWD：设置SYSDBA密码
SYSAUDITOR_PWD：设置SYSAUDITOR 密码
BUFFER：系统缓存大小
PORT_NUM：数据库服务器监听端口号

14.注册和取消服务

注册服务

root用户执行

cd /dm8/dbms/script/root
./dm_service_installer.sh -t dmserver -dm_ini dm8/data/cjc/dm.ini -p cjc

取消注册服务:

./dm_service_uninstaller.sh -n 服务名称

15.达梦数据库启动和关闭

启动(三种方式):

systemctl start DmServiceCJC.service
DmServiceCJC start
dmserver dm8/data/CJC/dm.ini

关闭(三种方式):

systemctl stop DmServiceCJC.service
shutdown immediate;
DmServiceCJC stop

守护进程的启动和关闭

systemctl start DmWatcherServiceWATCHER.service
systemctl stop DmWatcherServiceWATCHER.service

监控器的启动和关闭:

systemctl start DmMonitorServiceMONITOR.service
systemctl stop DmMonitorServiceMONITOR.service

16.达梦数据库连接

(1):本地连接
su - dmdba
语法：
disql 用户名:端口号
disql 用户名/密码:端口号
示例：
disql SYSDBA:5238
disql SYSDBA/******:5238

(2):远程连接
语法：
disql 用户名@IP:端口号
disql 用户名/密码@IP:端口号
示例：
disql SYSDBA@IP:5238
disql SYSDBA/******@IP:5238

(3):服务名连接
使用服务名连接，需要提前在客户端所在服务器配置/etc/dm_svc.conf文件
守护集群
配置示例:
vi etc/dm_svc.conf
##全局配置区
TIME_ZONE=(480)
LANGUAGE=(cn)
DMHACJC=(IP1:5238,IP2:5238)
##服务配置
[DMHACJC]
SWITCH_TIMES=(3)
SWITCH_INTERVAL=(100)
LOGIN_MODE=(1)


其中：
LOGIN_MODE=(1)表示只连接主库;
IP1和IP2是主、备库的物理IP。


连接方式:
disql 用户名@服务名
disql 用户名/密码@服务名
例如:
disql sysdba/******@DMHACJC

17.查询参数

查询参数：

查询方法:

1.v$parameter
2.v$dm_ini
3.dm.ini文件
4.show parameter命令
5.通过函数查看

方法1:v$parameter

select name,type,value,default_value,isdefault from v$parameter where name='BUFFER';

方法2:v$dm_ini

SELECT PARA_NAME,PARA_VALUE,MIN_VALUE,MAX_VALUE,DEFAULT_VALUE FROM V$DM_INI WHERE PARA_NAME='BUFFER';

方法3:dm.ini文件

dmdba@CJC-DB-01:/db/dm8/data/cjc$cat dm.ini|grep -w BUFFER

方法4:show parameter命令

SQL> show parameter buffer

方法5:函数方式

查看BUFFER参数内存中的值

SELECT SF_GET_PARA_VALUE(2,'BUFFER');

查看常见参数:

set pagesize 100;
select name,type,value,default_value,isdefault from v$parameter
where name in
(
'MEMORY_POOL',
'BUFFER',
'PWD_POLICY',
'SORT_BUF_SIZE',
'MAX_SESSIONS',
'TEMP_SPACE_LIMIT',
'CALC_AS_DECIMAL',
'RLOG_BUF_SIZE',
'DICT_BUF_SIZE',
'CACHE_POOL_SIZE',
'GLOBAL_STR_CASE_SENSITIVE',
'GLOBAL_CHARSET'
) order by type,name;

参数类型

select distinct type,count(*) from v$parameter group by type order by 2 desc;

18.权限

1.查询CJC用户有哪些角色

select grantee,granted_role from dba_role_privs where grantee in ('CJC') order by 1;

一共有19种role角色:

select ROLE FROM DBA_ROLES order by 1;
或
select distinct granted_role from dba_role_privs order by 1;

2.查询CJC用户有哪些系统权限

Select grantee,privilege from dba_sys_privs where grantee in ('CJC') order by 1;

3.查询CJC用户有哪些对象权限

Select grantee,privilege from dba_tab_privs where grantee in ('CJC') order by 1;

4.查询CJC用户有哪些列权限

Select grantee,privilege from dba_col_privs where grantee in ('CJC') order by 1;

6.查看RESOURCE 角色对应的权限

系统权限

SET PAGESIZE 100;
Select privilege from dba_sys_privs where grantee in ('RESOURCE') order by 1;

7.拥有DBA角色的用户

select grantee,granted_role from dba_role_privs where GRANTED_ROLE='DBA';

8.用户权限信息

通过以下 SQL 命令可以对用户的权限信息进行检查，包括用户名、权限、类型等信息。

SELECT * 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
)
WHERE GRANTEE IN (SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSDBA','SYSSSO','SYSAUDITOR') )
ORDER BY GRANTEE,PRIVILEGE_TYPE,PRIVILEGE;

19.查询表定义

CALL SP_TABLEDEF('CJC','EMP');
select dbms_metadata.get_ddl('TABLE','EMP','CJC') from dual;

20.表使用情况

已分配给表的页面数

SELECT table_used_space('CJC','EMP');

表已使用的页面数

SELECT table_used_pages('CJC','EMP');

查询某个表的大字段空间占用大小

select obj.name,
seg.n_extent*sf_get_page_size()*sf_get_extent_size()/1024/1024 MB
from sysobjects obj
left join v$lob_seg seg
on obj.id=seg.tab_id
where obj.name ='表名';

查询分区子表的对象大小和占用空间

询分区表的每个分区的对象大小和占用空间，便于评估分区表数据量。

select schname,ptab,subtabname,
TABLE_USED_PAGES(schname,subtabname)*page()/1024/1024.0 as "子表对象大小_MB",
TABLE_USED_SPACE(schname,subtabname)*page()/1024/1024.0 as "子表占用空间_MB"
from
(select SF_GET_SCHEMA_NAME_BY_ID(b.schid) as schname,b.name as subtabname,a.name as ptab
from sysobjects a,sysobjects b
where a.id=b.pid
and b.subtype$='UTAB' and a.name='<分区表名>')
order by 3;

21.查询列信息

查询用户中所有表所有列的注释、列类型、列长度、是否允许为空、缺省值

SELECT
TAB.NAME    AS "表名",
SYSCOL.NAME AS "列名",
SYSCOL.TYPE$
||'('
||SYSCOL.LENGTH$
||')'            AS "列类型and列长度",
SYSCOL.NULLABLE$ as "是否为空"     ,
SYSCOL.DEFVAL    AS "缺省值",
COMM.COMMENT$
FROM
SYSOBJECTS AS TAB,
SYSOBJECTS AS SCH,
SYSCOLUMNS AS SYSCOL,
SYSCOLUMNCOMMENTS as COMM
WHERE
SCH.ID   =TAB.SCHID
AND TAB.ID   =SYSCOL.ID
AND SCH.NAME = 'SYSDBA'
AND SYSCOL.NAME = COMM.COLNAME
and TAB.NAME IN
(
select TABLE_NAME from ALL_TABLES WHERE OWNER = 'SYSDBA'--用户名
);

22.查看索引信息

索引信息

SELECT TABLE_NAME,INDEX_NAME,INDEX_TYPE,STATUS FROM USER_INDEXES WHERE TABLE_NAME='EMP';

索引和列的信息

set line 300
set pagesize 100
col TABLE_OWNER for a15
col TABLE_NAME for a30
col COLUMN_NAME for a15
col INDEX_NAME for a35
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where TABLE_NAME in ('T1') order by 2,4,3;

SELECT TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME='EMP';

索引定义语句

select dbms_metadata.get_ddl('INDEX','IDX_EMP_NAME','CJC') from dual;

select
indexdef(id, 1)
from
sysobjects
where
pid =
(
SELECT
ID
FROM
sysobjects
WHERE
NAME='T1'
AND SCHID =
(
SELECT ID FROM SYSOBJECTS WHERE NAME='CJC' AND TYPE$='SCH'
)
)
and subtype$ = 'INDEX'
and name != 'INDEX' || id ;

重组表的索引

SP_REORGANIZE_INDEX('用户名','索引名');

23.查看约束信息

查询约束:

SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,DEFERRABLE FROM DBA_CONSTRAINTS WHERE OWNER='CJC';

查询统计表的各种约束

查询主键
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='P';


查询外键
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='R';


查询唯一约束
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='U';


查询check约束
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名",SEARCH_CONDITION as "CHECK约束的条件"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='C';


查询默认列
SELECT OWNER as "模式名",TABLE_NAME as "表名",COLUMN_NAME as "列名",DATA_TYPE as "列类型",DATA_DEFAULT as "默认值" FROM DBA_TAB_COLUMNS WHERE DATA_DEFAULT IS NOT NULL;


查询非空列
SELECT OWNER as "模式名",TABLE_NAME as "表名",COLUMN_NAME as "列名",DATA_TYPE as "列类型" FROM DBA_TAB_COLUMNS WHERE NULLABLE ='Y';

约束定义语句:

select dbms_metadata.get_ddl('CONSTRAINT','CK_DEPT_ID','CJC') from dual;
select dbms_metadata.get_ddl('CONSTRAINT','CONS134220458','CJC') from dual;
select dbms_metadata.get_ddl('CONSTRAINT','fk_emp_deptno','CJC') from dual;

24.视图依赖

查看某个表被哪些视图引用

select * from DBA_dependencies T WHERE  t.REFERENCED_NAME = '表名'；
select * from DBA_dependencies T WHERE  t.REFERENCED_NAME = 'DEPARTMENT';

25.查询外键关联表

select
t1.owner 原始表用户名,
t1.table_name 原始表,
t2.owner as 被依赖表用户名,
t2.table_name as  被依赖的表,
t1.constraint_name 外键名称,
a1.column_name 列,
a2.column_name as  被依赖的列
from dba_constraints t1, dba_constraints t2, dba_cons_columns a1, dba_cons_columns a2
where
t1.r_constraint_name = t2.constraint_name and
t1.constraint_name = a1.constraint_name and
t1.r_constraint_name = a2.constraint_name
order by t1.owner;

26.查询使用自增列的表

select b.owner,
b.table_name,
a.name COL_NAME
from
SYS.SYSCOLUMNS a,
all_tables b    ,
sys.sysobjects c
where
a.INFO2=1
and a.id =c.id
and c.name = b.table_name order by 1,2,3;

28.查询模式

用户和模式:

select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null  order by username,schenma;

select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null and a.name='CJC' order by username,schenma;

当前模式：

SELECT SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID());

29.查看对象大小

SELECT TABLE_USED_SPACE('CJC','T1')*page()/1024/1024.0  FROM dual;

SELECT segment_name AS TABLENAME,
BYTES B,
BYTES / 1024 KB,
BYTES / 1024 / 1024 MB
FROM user_segments
WHERE segment_name = upper('CJC');

30.查询系统函数以及参数定义

select i.id,
i.name as f_name,
i.arg_num,
ig.name as arg_name,
ig.seq,
ig.data_type,
ig.len,
ig.prec,
ig.io_type,
i.IS_READONLY,
i.IS_MOUNT_ONLY,
i.IS_INCLUDE_SQL,
i.IS_LPQ_FORBIDDEN,
i.CLASS$
from v$ifun i,
v$ifun_arg ig
where i.id=ig.id
and i.name = 'SP_CLOSE_SESSION'
order by 1,
2,
3,
5;

31.查看作业信息

查看当前库中的作业状态是否启用

select name,
CASE ENABLE WHEN '1'THEN '已启用' when '0' THEN '已禁用' end as enable
from USER_JOBS_VIEW;

作业信息

set pagesize 300;
SELECT EXEC_ID,NAME,START_TIME,END_TIME,ERRCODE,JOBID FROM SYSJOB.SYSJOBHISTORIES2 ORDER BY START_TIME;

查看所有作业信息

SELECT A.ID,A.NAME,A."ENABLE",A.USERNAME,A.CREATETIME,A.MODIFYTIME,A.DESCRIBE,
B.LAST_DATE||' '||B.LAST_SEC LAST_TIME,B.NEXT_DATE||' '||B.NEXT_SEC NEXT_TIME,B.WHAT
FROM SYSJOB.SYSJOBS A,SYSJOB.USER_JOBS B
WHERE A.ID=B.JOB;

查看所有作业调度信息

SELECT * FROM SYSJOB.SYSJOBSCHEDULES;

通过 SQL 命令检查。通过执行以下命令查看作业运行历史信息。

SELECT *
FROM SYSJOB.SYSSTEPHISTORIES2 A
WHERE (SELECT COUNT(*)
FROM SYSJOB.SYSSTEPHISTORIES2 B
WHERE B.NAME = A.NAME
AND B.EXEC_ID >= A.EXEC_ID) <= 10
ORDER BY A.START_TIME DESC,A.NAME;

32.查询数据字典和视图

动态性能视图
v$dynamic_tables


数据字典
SYSOBJECTS

例如:查看SQL相关的动态性能视图和数据字典

set pagesize 500;
select NAME from v$dynamic_tables where NAME like '%SQL%';
select NAME from SYSOBJECTS where NAME like '%SQL%';

33.统计信息

查询统计信息:

select a.object_name,a.owner,a.object_type,b.last_gathered
from dba_objects a,SYSSTATS b
where a.object_id = b.id --and a.OWNER = '用户名'
order by b.last_gathered limit 10;

查询CJC用户下T1表上字段的具体统计信息:

select * from (
select o.owner,o.object_name,b.column_name,b.column_id,b.data_type,s.t_total,s.n_smaple,s.n_distinct,s.n_null,s.n_buckets,s.last_gathered
from sysstats s
inner join dba_objects o on s.id=o.object_id
inner join dba_tab_columns b on o.owner=b.owner and o.object_name=b.table_name and s.colid+1=b.column_id
where o.owner='CJC'
and o.object_name='T1'
and o.object_type='TABLE')
order by b.column_id;

## CALL SP_DB_STAT_INIT ();

收集表统计信息:
示例如下:
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'CJC',TABNAME=>'T1',ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);


收集索引统计信息:
示例如下:
DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>'CJC',INDNAME=>'IDX001',ESTIMATE_PERCENT=>100);


收集模式下对象的统计信息
示例如下:
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'CJC',ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);


收集某表某列的统计信息：
STAT 100 ON table_name(column_name);


删除表统计信息
根据设定参数，删除与表相关对象的统计信息。
示例如下:
DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'CJC',TABNAME=>'T1',CASCADE_INDEXES='TRUE');
删除模式统计信息
根据设定参数，删除模式下对象的统计信息。
示例如下:
DBMS_STATS.DELETE_SCHEMA_STATS(OWNNAME=>'CJC');


删除索引统计信息
示例如下:
DBMS_STATS.DELETE_INDEX_STATS(OWNNAME=>'CJC',INDNAME=>'IDX001');


删除列统计信息
示例如下:
DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'CJC',TABNAME=>'T1',COLNAME=>'COL1');

查看统计信息收集进度:

SELECT wm_concat(progess) from (
select COUNT(*)  progess from dba_tables where
OWNER='SYSDBA' AND to_char(last_analyzed,'yyyymmdd') =to_char(sysdate,'yyyymmdd') --目前收集的个数
UNION
SELECT COUNT(*) progess  from dba_tables where OWNER='SYSDBA' --总的表个数
);

34.SQL跟踪日志

show parameter SVR_LOG
开启跟踪日志
SP_SET_PARA_VALUE(1,'SVR_LOG',1);
关闭SVR_LOG
SP_SET_PARA_VALUE(0,'SVR_LOG',1);

35.视图查看慢SQL

1. SQL 记录配置

当 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 打开时，显示系统最近 1000 条执行时间超过预定值的 SQL 语句，默认预定值为 1000 毫秒。

以上两个参数可通过 SP_SET_PARA_VALUE 系统函数修改，通过 SF_GET_PARA_VALUE 系统函数查看当前值。

查看参数值

select SF_GET_PARA_VALUE(1,'ENABLE_MONITOR');
select SF_GET_PARA_VALUE(1,'MONITOR_TIME');

修改参数值

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

查询

SELECT * FROM V$LONG_EXEC_SQLS;

查询当前正在执行的会话信息。

SELECT * FROM (
SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' AS CLOSE_SESSION,
DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) sql_exectime,
TRX_ID,
CLNT_IP,
B.IO_WAIT_TIME AS IO_WAIT_TIME,
SF_GET_SESSION_SQL(SESS_ID) FULLSQL,
A.SQL_TEXT
FROM V$SESSIONS a,V$SQL_STAT B WHERE STATE IN ('ACTIVE','WAIT')
AND A.SESS_ID = B.SESSID
);

36.生成执行计划

方法一:EXPLAIN

explain select id,name,time from cjc.t1 where id=100;

方法二:AUTOTRACE

SQL> set autotrace on
SQL> select * from cjc.t1;


SQL> set autotrace traceonly
SQL> select * from cjc.t1;

37:ET工具

ET 工具是 DM 数据库自带的 SQL 性能分析工具，能够统计 SQL 语句执行过程中每个操作符的实际开销，为 SQL 优化提供依据以及指导。

1. 功能的开启/关闭

ET 功能默认关闭，可通过配置 INI 参数中的 ENABLE_MONITOR=1、MONITOR_SQL_EXEC=1 开启该功能。

两个参数均为动态参数，可直接调用系统函数进行修改

查询

select SF_GET_PARA_VALUE(1,'ENABLE_MONITOR');
select SF_GET_PARA_VALUE(1,'MONITOR_SQL_EXEC');

启动 ET

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);

查看方式

执行 SQL 语句后，客户端会返回 SQL 语句的执行号。单击执行号即可查看 SQL 语句对应的 ET 结果。

如果没有图形界面，调用存储过程可返回相同结果。

select * from cjc.t1;

SET PAGESIZE 100;
CALL ET(9713);

关闭 ET

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);

38.10053事件

开启10053事件:
alter session set events '10053 trace name context forever,level 1';


...执行SQL...


关闭10053事件:
alter session set events '10053 trace name context off';


查看trace日志:
select para_name,para_value from v$dm_ini where para_name like '%TRACE_PATH%';

调整级别:

其中 session 0 表示所有会话，省略表示为当前会话。
level 1:输出查询语句计划中单表过滤或多表连接的优化过程。
level 2:输出查询语句计划中单表过滤以及多表连接的优化过程。


alter session 0 set events '10053 trace name context forever,level 1';

39.使用 DBMS_SQLTUNE 包获取 SQL 执行信息

DBMS_SQLTUNE 包提供一系列对实时 SQL 监控的方法。

当 SQL 监控功能开启后，DBMS_SQLTUNE 包可以实时监控 SQL 执行过程中的信息，包括：执行时间、执行代价、执行用户、统计信息等情况。SQL 监控功能开启的方法是将 DM.INI 参数 ENABLE_MONITOR 和 MONITOR_SQL_EXEC 均设置为 1。

具体操作步骤：

设置 disql 界面显示长度。
SET LONG 9999999


打开 MONITOR_SQL_EXEC
sf_set_session_para_value('MONITOR_SQL_EXEC',1);


执行需要调优的 SQL，例如如下 SQL：
select * from cjc.t1;


调用 DBMS_SQLTUNE.REPORT_SQL_MONITOR，例如传入执行 ID 17909。
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID=>17909) from dual;

40.查询内存中执行计划

获取内存的执行计划，计划生成时间，相关表和模式

select
s.phd_time         ,
s1.name as sch_name,
c.cache_item       ,
'sp_clear_plan_cache('
|| c.cache_item
|| ');',
'alter session set events ''immediate trace name plndump level '
|| C.CACHE_ITEM
|| ',dump_file ''''/tmp/'
|| C.CACHE_ITEM
||'.log'''''';',
c.HASH_VALUE   ,
c.sqlstr
from
v$cachepln c
join V$SQL_PLAN s
on
c.cache_item=s.PLN_ADDR
and c.HASH_VALUE=s.HASH_VALUE
join sysobjects s1
on
s.SCHID=s1.id
where
c.sqlstr like '获取计划的sql%' ;

41.绑定执行计划

设置参数 LOAD_BINDED_PLN=1
sp_set_para_value(1,'LOAD_BINDED_PLN',1);


通过查询执行计划缓存视图，找到 SQL 需要绑定的正确执行计划缓存的 hash 值。
select * from v$cachepln where sqlstr like '%XXX%'


绑定执行计划，通过语句 hash 值绑定执行计划。
--内存中绑定
SP_SET_PLN_BINDED(-522750294, 'SYSDBA', 'SQL', 1);
--持久化绑定
SP_SET_PLN_BINDED(-522750294, 'SYSDBA', 'SQL', 2);
如果想取消绑定，清理计划缓存即可。
sp_clear_plan_cache(-522750294);

补充查询视图信息

--查询系统中绑定执行计划持久化的信息。

select * from SYSPLNINFO;

--查询系统中绑定执行计划对应字典对象的信息。

select * from SYSPLNOBJID;

42.会话信息

查询当前所有会话数

select count(*) from v$sessions;

查看当前数据库中活动会话

select count(*) from v$sessions where state='ACTIVE';

查看当前空闲会话

select count(*) from v$sessions where state='IDLE';

结束会话

sp_close_session(sess_id);

操作系统命令查询会话数

假设端口为 5236:

lsof -i:5236|grep dmserver|wc -l
netstat -nat|awk '{print $4}'|grep 5236|wc -l

会话汇总

select  count(*),state from v$sessions group by state;
select  count(*),clnt_ip from v$sessions group by clnt_ip;

用户的主机名和用户的 IP 等信息:

SELECT CLNT_HOST,CLNT_IP,OSNAME FROM V$SESSIONS;

活跃的会话

select
--sysdate                    ,
		datediff (ss, last_recv_time, SYSDATE) Y_EXETIME,
---SF_GET_SESSION_SQL(SESS_ID), --获取完整 SQL
sess_id                    ,
sess_seq                   ,
---sql_text                   ,
state                      ,
seq_no                     ,
user_name                  ,
trx_id
--create_time
from
v$sessions
where
state='ACTIVE';

当前正在执行的会话

SELECT * FROM (
SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' AS CLOSE_SESSION,
DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) sql_exectime,
TRX_ID,
CLNT_IP,
B.IO_WAIT_TIME AS IO_WAIT_TIME,
SF_GET_SESSION_SQL(SESS_ID) FULLSQL,
A.SQL_TEXT
FROM V$SESSIONS a,V$SQL_STAT B WHERE STATE IN ('ACTIVE','WAIT')
AND A.SESS_ID = B.SESSID);

通过 sess_id 获取SQL语句

select SF_GET_SESSION_SQL(281422297507000)

活跃会话数和总会话数

select
(select count(*)
from v$sessions
where state='ACTIVE' and sess_id != sessid()
) act_ses,
(select count(*) from v$sessions) tot_ses;

查询已执行超过 2 秒的活动 SQL

select  *
from ( SELECT sess_id,
sql_text,
state,
datediff(ss,last_recv_time,sysdate) Y_EXETIME,
to_char(SF_GET_SESSION_SQL(SESS_ID)) fullsql,
clnt_ip
FROM V$SESSIONS
WHERE STATE='ACTIVE')
where Y_EXETIME>=2;

数据库连接资源释放:

命令行设置：比如设置会话空闲时间为 10 分钟，采用如下 SQL 语法：

alter user 用户名 limit CONNECT_IDLE_TIME 10;

通过 V$SESSIONS 查出空闲会话，然后使用 sp_close_session(会话 ID) 关闭会话。

select * from V$SESSIONS where  STATE='IDLE' ;
sp_close_session(会话ID);

43.线程信息

线程监控主要监控数据库中活动线程信息和等待线程信息。

监控当前系统中活动线程的信息

SELECT * FROM V$THREADS;

监控当前正在等待的线程信息。

SELECT * FROM V$LATCHES;

44.杀会话

select user_name,count(*) from v$sessions group by user_name;

活跃的事务

set pagesize 1000
select SESS_ID,STATE,USER_NAME,TRX_ID,CREATE_TIME,RUN_STATUS from v$sessions;
select ADDR,TRX_ID,LTYPE,LMODE,BLOCKED,TABLE_ID from v$lock;
select ADDR,TRX_ID,LTYPE,LMODE,BLOCKED,TABLE_ID from v$lock where TRX_ID !=0 order by table_id;
select SQL_TEXT from v$sessions;

查看事务

SELECT * FROM V$TRXWAIT;
select ID,STATUS,SESS_ID from V$TRX;

查看 39948，39943，39944 对应SQL语句

select SESS_ID,STATE,USER_NAME,TRX_ID,CREATE_TIME,RUN_STATUS,SQL_TEXT from v$sessions where TRX_ID IN (39943,39944,39948);

杀会话

select SESS_ID,STATE,USER_NAME,TRX_ID,CREATE_TIME,RUN_STATUS from v$sessions where SQL_TEXT like 'delete from HRMDEPARTMENT';
call sp_close_session('140130307046***');

结束 60 秒以上的活动会话:

首先查看一下符合条件的相关会话：

select * from
(SELECT
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) SS    ,
DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)),
*
FROM
V$SESSIONS
WHERE
STATE = 'ACTIVE'
order by
ss desc) where SS >60;

使用游标去执行杀会话

DECLARE
CURSOR c_sessions
is
select * from
(SELECT
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) SS    ,
DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)),
*
FROM
V$SESSIONS
WHERE
STATE = 'ACTIVE'
order by
ss desc) where SS >60;
c_raw c_sessions%rowtype;
begin
for c_row in c_sessions
loop
execute immediate 'sp_close_session('''||c_row.sess_id||''')';
end loop;
end;
/

45.查询当前会话事物ID

通过包 DBMS_TRANSACTION:

select DBMS_TRANSACTION.LOCAL_TRANSACTION_ID();

通过函数 tid:

select tid ;

通过 v$sessions 视图:

select trx_id from v$sessions where sess_id=sessid;

46.句柄数满

达梦数据库通过配置系统参数 MAX_SESSION_STATEMENT 来管控数据库单个会话上允许同时打开的语句句柄最大数。

一般情况下，当单个会话上句柄数达到 MAX_SESSION_STATEMENT 设置后，会出现【语句句柄个数超上限或系统内存不足】报错。

在遇到该问题时，为了快速恢复系统，可以参考如下思路进行恢复：

可以通过调大 dm.ini 文件中的 MAX_SESSION_STATEMENT 参数暂时解决问题。

可以通过语句查询各个会话的句柄数量。

select
sql_text               ,
state                  ,
n_stmt "句柄的容量"         ,
n_used_stmt as "使用的句柄数",
curr_sch               ,
user_name              ,
trx_id                 ,
create_time            ,
clnt_type              ,
clnt_ip                ,
run_status
from
v$sessions;

47.查询最大空闲时间

检查是否用户设置了最大空闲时间参数，导致超时断开

select name,value from v$parameter where name='MAX_SESSIONS';
select b.username 账号,b.password_versions 密码策略,a.sess_per_user 同时拥有的会话数,a.conn_idle_time 会话访问服务器的时间上限 from sysusers a,dba_users b where a.id=b.user_id;

48.执行时间超 2s 的SQL

可以自定义该时间

SELECT
*
FROM
(
SELECT
sess_id                                         ,
sql_text                                        ,
datediff (ss, last_recv_time, SYSDATE) Y_EXETIME,
SF_GET_SESSION_SQL (SESS_ID) fullsql            ,
clnt_ip
FROM
V$SESSIONS
WHERE
STATE = 'ACTIVE'
)
WHERE
Y_EXETIME >= 2;

查询已执行超过 2 秒的活动 SQL

select  *  from
(
SELECT
user_name,
clnt_ip,
sess_id,
sql_text,
datediff(ss, last_send_time, sysdate) ss,
SF_GET_SESSION_SQL(SESS_ID) fullsql
FROM
V$SESSIONS
WHERE
STATE='ACTIVE' and user_name!='SYSDBA'
)
where ss>=2 order by 5 desc;

查找出活动会话中执行时间大于 1S 的 SQL

select * from (
select timestampdiff(second,s.last_recv_time,sysdate) t ,s.*
from v$sessions s  where state='ACTIVE')
where t > 1;

49.清理内存中执行计划缓存

查找出活动会话中执行时间大于 1S 的 SQL

select * from (
select timestampdiff(second,s.last_recv_time,sysdate) t ,s.*
from v$sessions s  where state='ACTIVE')
where t > 1

到对应慢 SQL 对应的 cache_item 值

select * from v$cachepln where upper(sqlstr) like '%SQL%'

在 trace 目录中生成对应 trc 文件

alter session set events 'immediate trace name plndump ,level cache_item'

对比管理工具的执行计划和 .trc 文件中的执行计划。

清理内存中执行计划缓存

call sp_clear_plan_cache();
call sp_clear_plan_cache(pln号)；---不加 pln 就是清理所有 sql 缓存。

50.查询TOP SQL

查询最近的 sql 执行记录

select * from  v$sql_history limit 10;

查看TOP SQL

set pagesize 100
Select
top 60 datediff(ss,last_send_time,sysdate) TIME,
sess_id,
run_status,
---sql_text,
AUTO_CMT,
user_name,
appname,
CLNT_IP,trx_id
from  v$sessions
order by TIME desc;

查询SQL

select SF_GET_SESSION_SQL(281422364616xxx);

按执行时间排序

set pagesize 100;
select SESS_ID,SQL_ID,EXEC_TIME,N_RUNS from V$SYSTEM_LONG_EXEC_SQLS order by 3;

按执行次数排序

set pagesize 100;
select SESS_ID,SQL_ID,EXEC_TIME,N_RUNS from V$SYSTEM_LONG_EXEC_SQLS order by 4 desc;

最慢的 20 条 SQL 统计

通过以下 SQL 命令可以查询到最慢的 20 条 SQL 信息。

SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;
SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;

高内存的 20 条 SQL 统计

通过以下 SQL 命令可以查询到高内存的 20 条 SQL 信息，包括会话 id、SQL ID、SQL 文本、使用内存数、执行结束时间、执行次数等。

select * from  V$SYSTEM_LARGE_MEM_SQLS order by mem_used_by_k desc;

前 20 条长耗时等待事件统计

动态性能视图 V$SYSTEM_EVENT 可以查看到包括自系统启动以来所有等待事件的详细信息。通过以下 SQL 命令可以查询到前 20 条长耗时等待事件信息。

SELECT top 20 * FROM V$SYSTEM_EVENT ORDER BY TOTAL_WAITS DESC;

查询占用内存最多的 sql

查询执行在 1 秒以上的SQL语句使用的内存

SELECT "SESSID", MAX_MEM_USED||'KB',SQL_TXT FROM V$SQL_STAT order by MAX_MEM_USED DESC;

查询占用 io 较大的 sql

查询物理读次数较大的 sql

select
s.sess_id,
s.sql_text,
s.user_name,
s.state,
st.PHY_READ_CNT,
st.iO_WAIT_TIME
from
v$sessions s, v$session_stat st
where
s.sess_id=st.sessid and s.state  ='ACTIVE'
ORDER BY  5 DESC;

查找性能相对较差的 sql

即磁盘读取次数较多，按照硬解析读取次数倒叙排序。

select
SQL_TXT,
	EXEC_TIME,
	PARSE_CNT,
PARSE_TIME,
	HARD_PARSE_CNT,
	HARD_PARSE_TIME
FROM
v$sql_stat
ORDER BY
HARD_PARSE_CNT DESC,
EXEC_TIME DESC;

51.查看当前 DML 事务执行进度

当存在活动事务时，可以查询事务当前进度（执行时间、插入行数、删除行数、更新行数、是否正在回滚）便于判断事务当前状态。

可以参考如下 SQL 进行查询:

SELECT DATEDIFF(SS,LAST_RECV_TIME,SYSDATE) SS,
S.LAST_RECV_TIME,
S.SESS_ID,
T.THRD_ID,
T.ID,
S.SQL_TEXT,
SF_GET_SESSION_SQL(S.SESS_ID) AS SQL_FULL_TEXT,
T.STATUS,
T.INS_CNT,
T.DEL_CNT,
T.UPD_CNT,
T.UPD_INS_CNT,
T.WAITING,
T.START_LSN,
T.ROLLBACK_FLAG,
T.LOCK_CNT,
S.AUTO_CMT,
S.CONNECTED,
S.CLNT_IP,
S.USER_NAME
FROM V$SESSIONS S,V$TRX T
WHERE S.TRX_ID=T.ID
AND S.STATE='ACTIVE'
AND S.SESS_ID<>SESSID;

52.死锁

select * from V$DEADLOCK_HISTORY;

查询死锁历史事务信息

select
dh.trx_id ,
sh.sess_id,
wm_concat(top_sql_text)
from
V$DEADLOCK_HISTORY dh,
V$SQL_HISTORY sh
where
dh.trx_id =sh.trx_id
and dh.sess_id=sh.sess_id
group by
dh.trx_id, sh.sess_id;

53.阻塞

通过 v$trxwait 视图查询阻塞者事务 ID:

select id,wait_for_id,wait_time,thrd_id from v$trxwait;

将得到的造成阻塞的事务 ID wait_for_id 代入 v$sessions 进行查询；

select sess_id,sql_text,state,trx_id from v$sessions where trx_id='wait_for_id';

通过得到的会话信息确认是否可以关闭阻塞者会话，如果可以关闭可通过执行下面语句关闭该会话。

sp_close_session(sess_id);

WITH TRX_TAB AS
(SELECT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND O1.ID<>0),
TRX_SESS AS (
SELECT L.TRX_ID WT_TRXID, L.ROW_IDX BLK_TRXID,L.BLOCKED,(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID=L.TRX_ID) WT_TABLE,
S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS,
S1.USER_NAME WT_USER_NAME,S2.USER_NAME BLK_USER_NAME,S1.SQL_TEXT,S1.CLNT_IP,DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS
FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2
WHERE L.TRX_ID=S1.TRX_ID AND L.ROW_IDX=S2.TRX_ID)
SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1;

查询锁

SELECT O.NAME,L.* FROM V$LOCK L,SYSOBJECTS O WHERE L.TABLE_ID=O.ID AND BLOCKED=1;

查询阻塞

WITH LOCKS
AS (SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIME
FROM V$LOCK L, SYSOBJECTS O, V$SESSIONS S
WHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID),
LOCK_TR
AS (SELECT TRX_ID WT_TRXID, TID BLK_TRXID
FROM LOCKS
WHERE BLOCKED = 1),
RES
AS (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,
T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,
SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL,
DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS,
T1.SQL_TEXT WT_SQL
FROM LOCK_TR S, LOCKS T1, LOCKS T2
WHERE     T1.LTYPE = 'OBJECT'
AND T1.TABLE_ID <> 0
AND T2.LTYPE = 'OBJECT'
AND T2.TABLE_ID <> 0
AND S.WT_TRXID = T1.TRX_ID
AND S.BLK_TRXID = T2.TRX_ID)
SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXID
FROM RES;

检查当前数据库中包含的慢 SQL 及阻塞语句:

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;

对象或资源发生等待问题

如果存在长时间没有返回结果的语句，首先通过 V$SESSIONS 确认语句处于活动状态，语句如下：

SELECT * FROM v$sessions WHERE state='ACTIVE' AND dbms_lob.substr(sf_get_session_sql(sess_id)) LIKE '%语句片段%';

明确事务等待导致语句没有正常执行结束，语句如下：

SELECT * FROM v$trxwait WHERE id = 上述语句得到结果的 TRX_ID

54.查询有事务未提交的表

SELECT b.object_name, c.sess_id, a.*
FROM v$lock a, dba_objects b, v$sessions c
WHERE a.table_id = b.object_id AND a.ltype = 'OBJECT' AND a.trx_id = c.trx_id;

已执行未提交的 SQL:

SELECT t1.sql_text, t1.state, t1.trx_id
FROM v$sessions t1, v$trx t2
WHERE t1.trx_id = t2.id AND t1.state = 'IDLE' AND t2.status = 'ACTIVE';

55.数据库统计信息检查

系统统计信息

通过 SQL 命令查询动态性能视图 V$SYSSTAT 统计系统对象的信息，包括对象的 id、对象类型、统计对象名、统计值等。

select * from V$SYSSTAT where classid in (11,5) order by classid desc;

会话统计

通过以下 SQL 命令可以查询到会话信息，包括会话活动状态、客户端 IP、客户端类型、当前模式、当前用户、会话数量等。

SELECT STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME,COUNT(*)
COUNTS FROM V$SESSIONS GROUP BY STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME
ORDER BY STATE;

对象统计

通过以下 SQL 命令可以查询到对象信息，包括表空间名、类型和数量等。

select tablespace_name,'TABLE_OF_TS' OBJTYPE,count(*) COUNTS from all_tables group by tablespace_name union all
select * from ( select owner,object_type,count(*) from all_objects
where owner not in ('SYS','SYSTEM','SYSAUDITOR','SYSSSO','CTISYS') group by object_type,owner
order by 1,2);

表行数统计

通过以下 SQL 命令可以查询表行数统计信息。

收集统计信息:

begin
for rec in (select owner,table_name from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')) loop
call SP_TAB_STAT_INIT(rec.owner,rec.table_name);
end loop;
end;
/

查看统计信息

select owner,table_name,tablespace_name,status,num_rows from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')
order by owner,num_rows desc;

56.密码策略

SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME='PWD_POLICY';

其中VALUE数字代表不同密码策略，默认2。

0 表示无策略；1表示 禁止与用户名相同；2表示口令长度不小于9；4表示至少包含一个大写字母(A-Z)；8表示至少包含一个数字(0-0)；16表示至少包含一个标点符号(英文状态下，除了”和空格外的符号，口令策略可以单独使用，也可以组合使用)

57.内存使用

字典缓存

通过 SQL 命令查询动态性能视图 V$DB_CACHE，可以查看当前字典缓存信息、包括数据字典地址、缓存池总空间、实际使用空间等字典缓存信息。

select * from V$DB_CACHE;

数据缓冲池

通过 SQL 命令查询页面缓冲区动态性能表 V$BUFFERPOOL，可以查看缓冲区页数、页大小、空闲页、淘汰的页数、数据页被引用的次数等页面缓冲区信息。

select * from V$BUFFERPOOL;

select
name,             --缓冲区名称
n_pages,           --页数
free,              --空闲页数目
N_DISCARD64       --淘汰的页数
from  v$bufferpool;

查询结果可以参考如下：

（1）如果 free 较大，说明该缓冲区空闲，可以适当的调整降低 buffer 缓冲区参数值。
（2）如果 free 项为 0，或者 N_DISCARD64 非零，表示该缓冲区经常淘汰，说明对应的缓冲区参数太小，导致频繁淘汰，需要调整对应的缓冲区的参数。

查询缓冲区命中率

数据缓冲区是 DMSERVER 在将数据页写入磁盘之前以及从磁盘上读取数据页之后，数据页所存储的地方。

数据缓冲区设定得太小，会导致缓冲页命中率低，磁盘 IO 频繁；将其设定得太大，又会导致操作系统内存本身不够用。

select
name 缓冲池名称,
sum(page_size)*sf_get_page_size 缓冲池大小_G,
sum(rat_hit) /count(*) 命中率
from
v$bufferpool
group by name;

内存池

通过 SQL 命令查询动态性能视图 V$MEM_POOL 可以查看内存结构地址、是否是共享内存池、初始大小、当前总大小、数据占用大小等内存池信息。

select * from V$MEM_POOL;

查询内存总量

select
(select sum(n_pages) * page()/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE,
(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as mem_pool,
(select sum(n_pages) * page()/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as TOTAL_SIZE
from  dual;

数据库的内存使用情况

数据库级别的判断：数据库使用的内存大致等于 BUFFER_SIZE + POOL_SIZE，对应的 SQL 语句如下:

select (select sum(n_pages * page_size) from v$bufferpool) + (select sum(total_size) from v$mem_pool) from dual;

一般来说，发生内存泄漏的都在 mem_pool 上，比如操作系统上看到内存占用 60G，但 BUFFER 已经占用到 50 GB，实际上发生泄漏的概率较小，应该适当调小 BUF，空出内存给内存池使用。

是否存在使用内存过多的 SQL:

较新版本的 DM7 服务器提供会话及语句资源监控视图（ENABLE_MONITOR=1) 生效 v$SESSION_STAT，V$SQL_STAT 视图上字段比较丰富，我们这里主要关注 MAX_MEM_USED 字段。一般来讲，如果是某条或者特定的几条 SQL 导致内存增长过多，可以通过这两个视图查询出来。

通过执行以下语句：

SELECT MAX_MEM_USED,SQL_TXT FROM V$SQL_STAT order by MAX_MEM_USED DESC;

可以确定使用内存较大的 SQL，可以针对行的优化（消除 HASH,SORT,DISTINCT 等) 注意：该查询只能查询当前活动 STMT 上的语句消耗情况，历史情况需要查询 V$SQL_STAT_HISTORY，该视图上保留 1 万行 SQLSTAT 历史信息。

以上 MAX_MEM_USED 列的单位均为 KB。

是否存在扩展过大的内存池:

前面介绍过，正常使用过程中，内存池的大小一般会维持在 TARGET_SIZE 左右，不会发生太大的差距，如果发生非常多次计划外的扩展，需要考虑该池是否发生内存泄漏，与之相关的系统视图和系统视图字段为：v$mem_pool 上的 N_EXTEND_EXCLUSIVE 字段。

相应的查询语句为如下：

Select * from v$mem_pool where n_extend_exclusive = 0;

确认内存的详细使用情况:

详细的内存使用情况需要视图 v$mem_reginfo，该视图在 INI 参数 MEM_LEAK_CHECK 为 1 时有效，可动态开启，开启后登记数据库中所有内存的申请释放信息。

相应的查询语句如下：

Select sum(reserved_size),fname,lineno from v$mem_reginfo group by fname,lineno order by sum(reserved_size) desc;

按照大小降序，或者根据文件名、行号分组，查看是否存在大量某文件某行使用内存未释放的情况，转交研发处理。

内存池的使用:

select
name,                      --内存池名称
is_shared,                   --是否是共享的
is_overflow,                  --是否用到了备份池
org_size/1024.0/1024.0,        --内存池初始大小
TOTAL_size/1024.0/1024.0,     --内存池总大小(包括扩展的)
RESERVED_SIZE/1024.0/1024.0,  --当前已分配大小(包括扩展的)
DATA_SIZE/1024.0/1024.0,      --实际有效字节
EXTEND_SIZE,                --每次扩展多少
TARGET_SIZE,                --目标大小
N_EXTEND_NORMAL ,         --TARGET范围内累计扩展次数
N_EXTEND_EXCLUSIVE         --超过TARGET累计扩展次数
from  v$mem_pool
order by  TOTAL_size desc;

查询结果可以参考如下：

（1）N_EXTEND_EXCLUSIVE 如果长期大于 0，说明长期从池外扩展，可能存在内存泄露，需要重点关注。
（2）若使用到备份池，则需要保持高度关注。
（3）内存池创建的线程号 creator 可以与 session 的 thrd_id 关联，查看对应的某个会话的内存使用情况，查看方法可以参考 单个会话内存使用情况。
（4）若 RESERVED_SIZE 比 org_size 小，说明内存池非常空闲，可以减小对应的初始内存，避免浪费。
（5）若 TOTAL_size 比 TARGET_SIZE 大，说明内存池不够，经常向池外申请，需要把对应参数调大。

可以通过 v$sysstat 视图监控内存的使用情况:

select  name ,stat_val/1024.0/1024.0 from  v$sysstat where CLASSID=11;

以上查询结果中，字段含义如下：

memory pool size in bytes：内存池总的大小。
memory used bytes：内存池使用的内存大小。
memory used bytes from os：内存池从操作系统分配的大小。

单个会话内存使用情况:

SELECT
A.CREATOR                          ,
B.SQL_TEXT                         ,
SUM(A.TOTAL_SIZE)/1024.0/1024.0 TOTAL_M,    --当前总量(包括扩展)
SUM(A.DATA_SIZE) /1024.0/1024.0 DATA_SIZE_M  --实际使用量
FROM
V$MEM_POOL A,
V$SESSIONS B
WHERE
A.CREATOR = B.THRD_ID
GROUP BY
A.CREATOR,
B.SQL_TEXT
ORDER BY
TOTAL_M DESC;

58.查询错误码

select * from v$err_info where ERRINFO like '%主键%';

59.兼容 MySQL 的 \G 列式

兼容 MySQL 的 \G 列式显示 SQL 结果
disql 登录设置 ISQL_MODE=3。
SQL> set isql_mode 3


设置后即可支持 \G 列式显示(结尾没分号)。
SQL> select * from cjc.t\G

60.设置命令行的前缀标识

如何设置命令行的前缀标识为用户名，IP，端口以及实例名

Disql 登录时如何设置命令行的前缀标识为用户名，IP，端口以及实例名

在 dmdbms/bin/disql_conf 目录下配置 glogin.sql 文件(默认是空文件)，配置内容参考如下：

vi dmdbms/bin/disql_conf
添加如下内容:
column instance_name new_value instance_name
select instance_name from v$instance;
set sqlprompt "_user'@'_connect_identifier(&instance_name)>"

61.日志检查

日志中信息记录分类

达梦数据库日志主要有 INFO、WARNING、ERROR 和 FATAL 四类信息。

INFO（正常）：用于打印程序应该出现的正常状态信息， 便于追踪定位；
WARNING（告警）：表明系统出现轻微的不合理，一般不影响运行和使用；
ERROR（错误）：表明出现了系统错误和异常，无法正常完成目标操作；
FATAL（致命）：表明发生了严重的错误，会导致数据库宕机，服务停止。

一般在进行数据库日志监控时，主要关注以下内容：

WARNING、ERROR 和 FATAL 类信息监控。

INFO 类信息中涉及实例重启的信息

cd $DM_HOME
cd log
ls -ltr *.log

检查日志中是否有错误信息
cat dm_CJC_202504.log |grep '\[ERROR'


检查日志中是否有警告信息
cat dm_CJC_202504.log |grep '\[WARNING'


检查数据库是否重启过
cat dm_CJC_202504.log |grep 'SYSTEM IS READY'

参考:

达梦技术文档/运维常见问题
https://eco.dameng.com/document/dm/zh-cn/faq/faq-db-check.html
达梦技术文档/运维指南/数据库运维监控
https://eco.dameng.com/document/dm/zh-cn/ops/
达梦技术文档/运维指南/数据库监控检查
https://eco.dameng.com/document/dm/zh-cn/ops/check-system.html

重要说明:

本人推出达梦数据库实战课程:

《达梦数据库实战，从入门到精通！》

https://mp.weixin.qq.com/mp/appmsgalbum?__biz=MzI5OTY2NzQ5MA==&action=getalbum&album_id=3846239617787428865&scene=21

当前已更新18课时，计划约25课时，包括安装、守护集群、备份恢复、迁移、升级、故障处理、性能优化等。

限时优惠，仅需30元(300微信豆)(安卓)

专属答疑微信群:CJC_达梦数据库实战

目前已有39人付费学习:

欢迎关注我的公众号《IT小Chen》

###chenjuchao 20250408###

select 达梦数据库数据库脚本 sql优化表空间

文章转载自青年数据库学习互助会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

达梦数据库常用的61个脚本，速来下载！

1.查看版本

2.数据库基本信息

3.服务器资源信息

4.字符集

5.大小写敏感

6.页大小

7.簇大小

8.数据文件信息

9.表空间

10.Temp表空间

11.控制文件

12.REDO日志文件

13.实例初始化

14.注册和取消服务

15.达梦数据库启动和关闭

16.达梦数据库连接

17.查询参数

18.权限

19.查询表定义

20.表使用情况

21.查询列信息

22.查看索引信息

23.查看约束信息

24.视图依赖

25.查询外键关联表

26.查询使用自增列的表

28.查询模式

29.查看对象大小

30.查询系统函数以及参数定义

31.查看作业信息

32.查询数据字典和视图

33.统计信息

34.SQL跟踪日志

35.视图查看慢SQL

36.生成执行计划

37:ET工具

38.10053事件

39.使用 DBMS_SQLTUNE 包获取 SQL 执行信息

40.查询内存中执行计划

41.绑定执行计划

42.会话信息

43.线程信息

44.杀会话

45.查询当前会话事物ID

46.句柄数满

47.查询最大空闲时间

48.执行时间超 2s 的SQL

49.清理内存中执行计划缓存

50.查询TOP SQL

51.查看当前 DML 事务执行进度

52.死锁

53.阻塞

54.查询有事务未提交的表

55.数据库统计信息检查

56.密码策略

57.内存使用

58.查询错误码

59.兼容 MySQL 的 \G 列式

60.设置命令行的前缀标识

61.日志检查

参考:

重要说明:

评论