DM数据库常用SQL集合

在DM数据库的日常使用中，本人通过各种途径（数据库安装后的doc目录、官方eco社区、互联网，以及同事技术交流）收集了有一些高频实用的SQL命令集，本文将悉数罗列出来，日后将继续补充完善。

表结构查看

如何获取表字段信息和列注释信息?

-- 获取表结构信息（包括注释和是否自增列）
SELECT S.NAME    AS 表名,
       C.NAME    AS 列名,
       C.TYPE$   AS 类型,
       C.LENGTH$ AS 长度,
       C.SCALE   AS 标度,
       C.INFO2&1 AS 自增字段,
    C.NULLABLE$ AS 可为空,
    C.DEFVAL AS 默认值,
    (SELECT COMMENTS
    FROM ALL_COL_COMMENTS CC
    WHERE CC.OWNER = SF_GET_SCHEMA_NAME_BY_ID(S.SCHID)
    AND CC.TABLE_NAME = S.NAME
    AND CC.COLUMN_NAME = C.NAME) AS 列注释
FROM SYSOBJECTS S,
    SYSCOLUMNS C
WHERE S.ID = C.ID
-- 默认查询当前用户下面的用户表，要查看其他模式的表，请替换下面的USER函数为指定的模式名
    -- AND S.SCHID = SF_GET_SCHEMA_ID_BY_NAME(USER)
  AND SUBTYPE$ = 'UTAB'
-- 默认查询指定模式下所有的表，取消下方注释，替换表名DEPT为你需要查看的表名
  AND S.NAME = 'USERINFO'
ORDER BY S.NAME, C.COLID;

如何获取数据库对象（表、索引、函数、过程）的DDL语句？

-- 根据表名返回DDL语句
SELECT TABLEDEF(USERNAME=>'TEST',TABLENAME=>'DICT_AREA');
-- 根据对象类型、对象名、对象所属模式返回DDL语句
SELECT SF_DBMS_METADATA_GET_DDL(OBJECT_TYPE=>'TABLE',OBJECT_NAME=>'DICT_AREA',SCHNAME=>'TEST');

系统函数信息

如何获知某个函数、过程所需的参数有哪些？

-- 模糊搜索函数名
SELECT * FROM V$IFUN WHERE NAME LIKE upper('%table_used_space%');
-- 根据上一步id查询函数参数
SELECT * FROM V$IFUN_ARG WHERE id= V$IFUN.ID  ;

用户与模式对应

如何获取用户与模式的对应关系？如果找到哪些用户拥有多个模式？

-- 获取用户与模式的对应映射关系
SELECT SF_GET_USERNAME_BY_ID(PID)                         AS USERNAME,
       LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY CRTDATE) AS SCHEMA_NAME
FROM SYSOBJECTS
WHERE TYPE$ = 'SCH'
GROUP BY SF_GET_USERNAME_BY_ID(PID)
ORDER BY USERNAME;

空间占用

表空间占用情况

哪些表空间空间所剩不多，需要提前扩充？哪些表空间使用率较低？（在磁盘紧张的时候考虑充分利用起来）

-- 查看表空间占用
SELECT D.TABLESPACE_NAME                                                        "Name",
       --d.contents "Type",
       TO_CHAR(NVL(A.BYTES / 1024 / 1024 / 1024, 0), '99999999.9')              "总空间(GB)",
       TO_CHAR(NVL(A.BYTES2 / 1024 / 1024 / 1024, 0), '99999999.9')             "已使用(GB)",
       TO_CHAR(NVL((A.BYTES2 / A.BYTES * 100), 0), '990.99')                    "使用%",
       TO_CHAR(NVL((A.BYTES2 - NVL(F.BYTES, 0)) / A.BYTES2 * 100, 0), '990.99') "使用率%"
FROM SYS.DBA_TABLESPACES D,
     (
         SELECT TABLESPACE_NAME,
                SUM(GREATEST(BYTES, MAXBYTES)) BYTES,
                SUM(BYTES)                     BYTES2
         FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME
     ) A,
     (
         SELECT TABLESPACE_NAME,
                SUM(BYTES) BYTES
         FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME
     ) F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
  AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY NVL((A.BYTES2 - NVL(F.BYTES, 0)) / A.BYTES2 * 100, 0) DESC;

用户表占用情况

某个用户模式下来的表都占用了多大空间，分别有多少行？大表有哪些？

-- 通过页数和显示单位(可选参数MB/GB，默认KB为单位显示)
WITH FUNCTION GET_SIZE_BY_PAGES(NUM_OF_PAGE INT, KB_MB_GB VARCHAR(2) DEFAULT 'KB') RETURN NUMBER(15, 2) AS
    V_SIZE_KB NUMBER(15, 2) := ROUND(PAGE() * NUM_OF_PAGE / 1024.00, 2);
BEGIN
    IF KB_MB_GB = 'MB' THEN
        RETURN V_SIZE_KB / 1024.00;
    ELSIF KB_MB_GB = 'GB' THEN
        RETURN V_SIZE_KB / 1024.00 / 1024.00;
    END IF;
    RETURN V_SIZE_KB;
END;
-- 查看每个表空间占用情况和行数
SELECT T.TABLESPACE_NAME,
       T.OWNER,
       T.TABLE_NAME,
       GET_SIZE_BY_PAGES(TABLE_USED_PAGES(T.OWNER, T.TABLE_NAME), 'MB') AS TABLE_USED_SPACE_MB,
       GET_SIZE_BY_PAGES(TABLE_USED_PAGES(T.OWNER, T.TABLE_NAME), 'GB') AS TABLE_USED_SPACE_GB,
       TABLE_ROWCOUNT(T.OWNER, T.TABLE_NAME)                            AS TABLE_ROWCOUNT
FROM DBA_TABLES T
WHERE T.OWNER IN ('TEST')
ORDER BY TABLE_USED_SPACE_MB DESC, TABLE_ROWCOUNT DESC;

性能诊断

阻塞会话SQL

当前哪个会话正在执行的哪条SQL（持有的锁）阻塞了另外哪个会话的哪一个SQL正在等待执行？

-- 查询持有锁的会话和等待锁的会话V$TRXWAIT版本
SELECT H.SESS_ID                                    AS HOLD_LOCK_SESSION_ID,
       CONCAT('SP_CLOSE_SESSION(', H.SESS_ID, ');') AS HOLD_LOCK_SESSION_KILL,
       H.SQL_TEXT                                   AS HOLD_LOCK_SESSION_SQL,
       H.STATE                                      AS HOLD_LOCK_SESSION_STATE,
       H.CLNT_HOST                                  AS HOLD_LOCK_CLIENT_HOST,
       H.CLNT_IP                                    AS HOLD_LOCK_CLIENT_IP,
       H.THRD_ID                                    AS HOLD_LOCK_THRD,
       H.TRX_ID                                     AS HOLD_LOCK_TRX_ID,
       T.WAIT_TIME / 1000.00                        AS WAIT_SECOND,
       W.*
FROM SYS."V$TRXWAIT" T
         JOIN SYS."V$SESSIONS" H ON T.WAIT_FOR_ID = H.TRX_ID
         JOIN SYS."V$SESSIONS" W ON T.ID = W.TRX_ID
WHERE H.STATE != 'ACTIVE'
ORDER BY WAIT_SECOND DESC;
-- 或者使用以下查询方式
-- 查询持有锁的会话和等待锁的会话
SELECT H.SESS_ID                                    AS HOLD_LOCK_SESSION_ID,
       CONCAT('SP_CLOSE_SESSION(', H.SESS_ID, ');') AS HOLD_LOCK_SESSION_KILL,
       TO_CHAR(H.SQL_TEXT)                          AS HOLD_LOCK_SESSION_SQL,
       H.STATE                                      AS HOLD_LOCK_SESSION_STATE,
       H.CLNT_HOST                                  AS HOLD_LOCK_CLIENT_HOST,
       H.CLNT_IP                                    AS HOLD_LOCK_CLIENT_IP,
       H.THRD_ID                                    AS HOLD_LOCK_THRD,
       H.TRX_ID                                     AS HOLD_LOCK_TRX_ID,
       SF_GET_TABLENAME_BY_ID(L.TABLE_ID)           AS LOCKED_TABLE,
       CONCAT('SP_CLOSE_SESSION(', W.SESS_ID, ');') AS WAITING_SESSION_KILL,
       W.*
FROM SYS."V$LOCK" L
         JOIN SYS."V$SESSIONS" W ON L.TRX_ID = W.TRX_ID AND L.BLOCKED = 1
         JOIN SYS."V$SESSIONS" H ON L.TID = H.TRX_ID;
--WHERE H.STATE != 'ACTIVE';

使用大量内存的SQL

哪些SQL占用了大量的内存？

-- 查询最近1000条内存占用量高的SQL
select * from SYS."V$LARGE_MEM_SQLS" ORDER BY FINISH_TIME DESC;
-- 查询系统中内存占用量最高的20条SQL
SELECT *
FROM V$SYSTEM_LARGE_MEM_SQLS
ORDER BY "V$SYSTEM_LARGE_MEM_SQLS".MEM_USED_BY_K DESC;
-- 查询最近内存占用最高的10000条SQL
SELECT SF_GET_SESSION_SQL(SESSID),
       MAX_MEM_USED / 1024.0 MAX_MEM_USED_MB,
       SQL_TXT
FROM V$SQL_STAT_HISTORY
ORDER BY MAX_MEM_USED DESC;

慢查询定位

如何快速的获取慢查询语句？

-- 查询执行时间大于2秒的活动会话
SELECT SESS_ID,
       SQL_TEXT,
       DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) EXETIME,
       TO_CHAR(SF_GET_SESSION_SQL(SESSID))   FULLSQL,
       CLNT_IP
FROM V$SESSIONS
WHERE STATE = 'ACTIVE'
  AND LAST_RECV_TIME <= SYSDATE - 1 / 24 / 60 * 2;

-- 显示系统最近 1000 条执行时间超过预定值(SF_GET_LONG_TIME，该阀值可通过SP_SET_LONG_TIME调整)的 SQL 语句
select * from SYS."V$LONG_EXEC_SQLS";

-- 显示系统自启动以来执行时间最长的 20 条 SQL 语句
select * from SYS."V$SYSTEM_LONG_EXEC_SQLS";

慢查询分析

如何知道SQL执行计划的哪些执行步骤消耗最长，更值得优化？

-- 通过ET查看SQL各个执行阶段耗时
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
-- 下面执行需要测量的查询，获取执行号
select count(*) from AIRPORT.BOOKING;
-- 使用下面的存储过程或者直接点击管理工具消息窗口中的执行号超级链接
et(16974);
-- 取消监控
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',0);

备份集

-- 备份集查看
SELECT BACKUP_TIME,
       BACKUP_NAME,
       BACKUP_PATH,
       CASE TYPE WHEN 1 THEN '增备' ELSE '全备' END AS "type"
FROM V$BACKUPSET
WHERE PARENT_ID = -1
ORDER BY BACKUP_TIME DESC;

系统信息

如何通过数据库快速查看服务器的负载信息？

-- 系统信息
select * from SYS."V$SYSTEMINFO";

license授权信息

如何查看当前数据库的授权信息？授权的的到期时间是什么时候？

-- 查看授权信息
SELECT SERIES_NO,
       EXPIRED_DATE,
       AUTHORIZED_CUSTOMER,
       PROJECT_NAME,
       CLUSTER_TYPE
FROM V$LICENSE;

替换授权的KEY之后，如何刷新授权信息？

-- 重新载入授权
CALL P_LOAD_LIC_INFO();

小技巧

这么多的常用SQL命令，大家都是保存到哪里的呢？保存后又是怎样才能快速地找出来并调用？

好多同学可能会将这些日常使用的SQL用文本文件的方式，或者通过各种笔记软件（有道云笔记、印象笔记等等）进行记录并保存，每次使用的时候再打开这个文本文件进行搜索、复制、粘贴。

这些方法都未尝不可，但对于追求效率的我们来说，稍微有那么一点点的麻烦。我在这里推荐给大家一个我日常使用的小技巧：将日常使用频繁的SQL命令添加的输入法的自定义词条，这样不仅可能快速查找并调用，还能支持云端同步保存。下面，我将以搜狗输入法为例进行演示。

例如，大家很多时候想查看某些（个）表结构信息（字段名、字段类型、默认值、是否自增列、列注释等），可能会通过DM管理工具依次点击【对象导航】→【模式名】→【表名】→【修改】、【属性】、【列】打开对应窗口进行查看。但如果使用输入法的自定义词条功能，那么通常只需要切换到搜索输入法，然后键入desc，然后选择5（假设我们将该SQL查询命令候选项设置为5），即可快速调用查看表结构的SQL命令。如下图所示：

达梦技术社区：https://eco.dameng.com/