达梦数据库日常管理常用命令汇总

1、查询当前数据库实例中活动会话数量

SELECT count(*) FROM v$sessions WHERE state='ACTIVE';

注意：可以通过v$sessions视图查询会话的详细信息，如指定session执行的 SQL 语句、客户端工具、客户端地址、连接类型、会话状态等。

2、查询当前数据库实例中活动会话执行的完整SQL语句

SELECT sess_id,SF_GET_SESSION_SQL (SESS_ID),sql_text FROM v$sessions WHERE state = 'ACTIVE';

3、查询当前数据库实例是否有阻塞

SELECT o.name, l.*

  FROM v$lock l, sysobjects o

 WHERE l.table_id = o.id AND blocked = 1;

4、查看当前数据库实例锁信息

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, row_idx 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

  FROM res;

5、查看当前数据库正在运行的未提交事物相关的信息

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 ltype = 'OBJECT' AND a.trx_id = c.trx_id;

6、查看当前数据库中长时间运行的事物会话信息

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

7、查询当前数据库中执行超过2秒的会话信息

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;

8、查看数据库授权信息

select * from v$license;

9、查看当前数据库服务器信息

select * from V$SYSTEMINFO;

10、查看当前数据库参数配置值

select * from v$dm_ini where para_name like '%%';

11、查看当前数据库会话信息

--总会话数

select count(*) from v$sessions;

--按状态分组会话数

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

--按客户端分组会话数

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

12、查看指定时间段或者最近执行过的SQL信息（注意前提条件是ENABLE_MONITOR参数配置为1）

select * from v$sql_history where start_time between '2021-08-15 00:00:00' and '2021-08-17 00:00:00';

13、查看当前数据库基础信息（实例名称、数据库版本、字符集、页大小、大小写敏感、数据库模式、唯一魔数、LSN等）

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;