达梦数据库日常使用语句01

查看数据库初始化参数等信息

--DM8较新版本直接查询V$OPTION视图：
SELECT * FROM V$OPTION;

---通用
SELECT '实例名称' AS 数据库参数名称,INSTANCE_NAME  数据库参数值 FROM V$INSTANCE UNION ALL
SELECT '数据库版本',  BANNER||'.'||ID_CODE FROM V$VERSION WHERE ROWNUM=1 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 'VARCHAR类型是否以字符为单位',VALUE FROM V$PARAMETER WHERE NAME='LENGTH_IN_CHAR' UNION ALL
SELECT '空格填充模式',VALUE FROM V$PARAMETER WHERE NAME='BLANK_PAD_MODE' UNION ALL
SELECT '数据库模式',MODE$ FROM V$INSTANCE  UNION ALL
SELECT '数据库端口',VALUE FROM V$PARAMETER WHERE NAME='PORT_NUM' UNION ALL
SELECT '归档模式',ARCH_MODE FROM V$DATABASE UNION ALL
SELECT '唯一魔数',CAST(PERMANENT_MAGIC AS VARCHAR) UNION ALL
SELECT 'LSN',CAST(CUR_LSN AS VARCHAR) FROM V$RLOG ;

查看活动SQL语句执行时间

select datediff(s, last_recv_time, sysdate) exectime,
       dbms_lob.SUBSTR(sf_get_session_sql(sess_id)) sql_txt,
       CUR_SQLSTR,
       sess_id,
       state,
       user_name,
       clnt_ip,
       clnt_ver
  from v$sessions
 where state = 'ACTIVE'
 order by exectime desc

查询历史耗时SQL

---最近1000条执行时间较长的SQL（默认记录SQL执行时间大于1s的SQL语句，记录总条数和记录执行时间阈值可以手动调整）
select * from v$long_exec_sqls order by 4 desc;

---显示服务器启动以来执行时间最长的20条SQL语句（默认记录SQL执行时间大于1s的SQL语句，记录总条数和记录执行时间阈值可以手动调整）
select * from V$SYSTEM_LONG_EXEC_SQLS order by 5 desc,4 desc;

---查询历史执行SQL语句
select * from v$sql_history order by time_used desc;

主外键对应关系

select 
a.owner,
a.table_name PK_TABLE,
a.constraint_name PK_CONSTRAINT,
b.owner,
b.table_name FK_TABLE,
b.CONSTRAINT_NAME FK_CONSTRAINT
 from dba_constraints a,
 dba_constraints b 
 where a.CONSTRAINT_TYPE='P' 
 and b.R_CONSTRAINT_NAME=a.constraint_name 
 and a.owner=b.owner;

外键列创建索引

--查找数据库中外键列未创建索引的表并生成创建索引语句
with cons as
 (select owner, table_name, constraint_name
    from dba_constraints
   where constraint_type = 'R'),
idxs as
 (select a.table_owner, a.table_name, a.column_name
    from dba_ind_columns a, dba_indexes b
   where a.table_owner = b.owner
     and a.index_name = b.index_name
     and b.index_type <> 'VIRTUAL'
  ),
fk_cols as
 (select owner,
         table_name,
         constraint_name,
         listagg('"' || column_name || '"', ',') WITHIN GROUP(ORDER BY TABLE_NAME) AS FK_COLUMNS
    from dba_cons_columns
   where (owner, table_name, constraint_name) in (select * from cons)
     and (owner, table_name, column_name) not in
         (select table_owner, table_name, column_name
            from idxs)
   group by owner, table_name, constraint_name)
select *,
       'CREATE INDEX IDX_' || constraint_name || ' ON "' || owner || '"."' ||
       table_name || '"(' || FK_COLUMNS || ');' as CREATE_FK_INDEX_SQL
  from fk_cols
 where owner = 'AAAA' /*指定模式名*/
 --and table_name='TD_GWCL_RETREAT_ORG_REGISTER' /*指定表名*/
 ;

 --或者
SELECT
        'alter table "'
        ||t1.table_NAME
        ||'" modify constraint "'
        ||t1.CONSTRAINT_NAME
        ||'" to foreign key("'
        ||t3.COLUMN_NAME
        ||'") references "'
        ||t2.table_name
        ||'"("'
        ||t4.column_name
        ||'") with index;'
        /*t1.owner             AS FK_OWNER,
        t1.table_name        AS FK_TABLE,
        t1.constraint_name   AS FK      ,
        t3.column_name       AS FK_COL  ,
        T2.OWNER             AS PK_OWNER,
        t2.table_name        AS PK_TABLE,
        t1.r_constraint_name AS PK      ,
        t4.column_name  AS PK_COL,
        T1.DELETE_RULE*/
from
        dba_constraints t1 ,
        dba_constraints t2 ,
        DBA_CONS_COLUMNS t3,
        DBA_CONS_COLUMNS T4
where
        t1.constraint_type='R'
    and t2.constraint_type='P'
    and t2.constraint_name=t1.r_constraint_name
    and t3.constraint_name=t1.constraint_name
    AND T2.constraint_name=T4.constraint_name
    and t2.table_name     ='表名'

查看内存中的执行计划，输出到文本

---查询语句执行计划缓存地址
select cache_item from v$cachepln where sqlstr like 'select * from t1 where id%';
---打印内存中对应的执行计划
alter session set events 'immediate trace name plndump level 131111152555,dump_file ''/home/dmdba/sql111.log''';

清理内存中执行计划缓存

---清理指定的执行计划
CALL SP_CLEAR_PLAN_CACHE(473546872);
---清理内存中所有执行计划缓存
CALL SP_CLEAR_PLAN_CACHE();

查询会话阻塞

---DM8 DSC集群
select * from V$DSC_TRXWAIT;
select s1.instance_name,s1.user_name,s1.curr_sch,s1.sess_id "被阻塞sess_id",s1.sql_text,
s2.instance_name,s2.user_name,s2.curr_sch,s2.sess_id "产生阻塞sess_id",s2.sql_text,w1.wait_time 
from gv$sessions s1,gv$sessions s2,v$dsc_trxwait w1 
where 
s1.trx_id=w1.trx_id 
and s2.trx_id=w1.WAIT_TRX_ID 
;

---DM8单机锁阻塞查询：
select *from v$trxwait;
select s1.user_name,s1.curr_sch,s1.sess_id "被阻塞sess_id",s1.sql_text,
s2.user_name,s2.curr_sch,s2.sess_id "产生阻塞sess_id",s2.sql_text,w1.wait_time 
from v$sessions s1,v$sessions s2,v$trxwait w1 
where 
s1.trx_id=w1.id
and s2.trx_id=w1.WAIT_FOR_ID ;
WITH LOCK_TAB as(SELECT L1.TRX_ID,L1.TID,L1.LMODE, O1.NAME,L1.BLOCKED from V$LOCK L1,SYSOBJECTS O1 where
O1.ID=L1.TABLE_ID AND L1.BLOCKED<>0)
SELECT
L2.NAME WT_TABLE ,
L2.TRX_ID WT_TRXID ,
L2.TID BLK_TRXID ,
S1.SESS_ID WT_SESS ,
S2.SESS_ID BLK_SESS ,
S1.USER_NAME WT_USER_NAME ,
S2.USER_NAME BLK_USER_NAME,
L2.BLOCKED ,
L2.LMODE,
S1.SQL_TEXT ,
S1.CLNT_IP ,
DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) WAIT_TIME
FROM
V$SESSIONS S1,
V$SESSIONS S2,
LOCK_TAB L2
WHERE
L2.TRX_ID=S1.TRX_ID
AND L2.TID =S2.TRX_ID ;

---DM7锁阻塞查询：
WITH LOCK_INFO as 
(SELECT L1.TRX_ID,L1.ROW_IDX,L1.LMODE,L2.TABLE_ID,L1.BLOCKED
FROM V$LOCK L1,V$LOCK L2 
WHERE L1.TRX_ID=L2.TRX_ID AND L1.BLOCKED<>0 AND L2.TABLE_ID<>0)
SELECT
O.NAME WT_TABLE ,
L.TRX_ID WT_TRXID ,
L.ROW_IDX BLK_TRXID ,
S1.SESS_ID WT_SESS ,
S1.SESS_ID BLK_SESS ,
S1.USER_NAME WT_USER_NAME ,
S1.USER_NAME BLK_USER_NAME,
L.BLOCKED ,
L.LMODE,
S1.SQL_TEXT ,
S1.CLNT_IP ,
DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) WAIT_TIME
FROM
V$SESSIONS S1,
V$SESSIONS S2,
LOCK_INFO L,
SYSOBJECTS O
WHERE
L.TRX_ID=S1.TRX_ID
AND L.ROW_IDX =S2.TRX_ID
AND L.TABLE_ID=O.ID ;

查询用户拥有的模式:

SELECT B.USERNAME,A.NAME SCHEMA_NAME 
FROM SYSOBJECTS A,DBA_USERS B 
WHERE A.PID=B.USER_ID 
AND A.TYPE$='SCH'
ORDER BY B.USERNAME;
---或者
SELECT B.USERNAME,listagg(A.NAME,',') within group (order by B.USERNAME) as SCHEMAS 
FROM SYSOBJECTS A,DBA_USERS B 
WHERE A.PID=B.USER_ID 
AND A.TYPE$='SCH'
GROUP BY B.USERNAME;

DM 武汉达梦数据库股份有限公司
24小时免费服务热线：400 991 6599
达梦在线服务平台：https://eco.dameng.com