达梦数据库-学习-16-常用SQL记录（持续更新）

目录

一、环境信息

二、介绍

三、查询SQL

1、数据库的总使用空间大小

2、各个表空间的总大小

3、使用空间最大的50个对象

4、使用率最高的50个sequence

5、使用空间率最高的50个自增列

6、定位锁

7、支持HINT

8、表数据页使用率

9、备份文件相关信息

10、初始化库参数信息

11、REDO日志信息

12、归档文件信息

13、DBMS_SCHEDULER包创建且正在执行的作业

---

一、环境信息

名称	值
CPU	12th Gen Intel(R) Core(TM) i7-12700H
操作系统	CentOS Linux release 7.9.2009 (Core)
内存	4G
逻辑核数	2
DM版本	1          DM Database Server 64 V8 2          DB Version: 0x7000c 3          03134284194-20240703-234060-20108 4          Msg Version: 12 5          Gsu level(5) cnt: 0

二、介绍

我们在工作中经常要编写一些SQL语句来辅助我们排查定位问题，但每次都要现写，表示很麻烦，这里做个记录积少成多。

三、查询SQL

1、数据库的总使用空间大小

SELECT ROUND((SUM(BYTES)/1024/1024), 2) AS TOTALSIZE
FROM DBA_DATA_FILES;行号     TOTALSIZE
---------- ---------
1          340已用时间: 20.217(毫秒). 执行号:1924.

2、各个表空间的总大小

SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / 1024 / 1024, 2) AS TOTALSIZE,CASE WHEN CONTENTS = 'TEMPORARY' THEN 'TEMPORARY' ELSE 'PERMANENT' END AS TYPE
FROM (SELECT TABLESPACE_NAME, BYTES, CONTENTS FROM DBA_DATA_FILESJOIN DBA_TABLESPACES USING (TABLESPACE_NAME)
)
GROUP BY TABLESPACE_NAME, CONTENTS
ORDER BY TABLESPACE_NAME;行号     TABLESPACE_NAME TOTALSIZE TYPE     
---------- --------------- --------- ---------
1          MAIN            256       PERMANENT
2          ROLL            128       PERMANENT
3          SYSTEM          74        PERMANENT
4          TEMP            10        TEMPORARY已用时间: 27.572(毫秒). 执行号:1926.

3、使用空间最大的50个对象

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,ROUND(SUM(BYTES) / 1024, 2) AS TOTAL_SIZE
FROM DBA_SEGMENTS
WHERE 
OWNER NOT IN ('SYS','SYSAUDITOR','SYSSSO','CTISYS')
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
ORDER BY TOTAL_SIZE DESC
FETCH FIRST 50 ROWS ONLY;行号     OWNER  SEGMENT_NAME  SEGMENT_TYPE TOTAL_SIZE
---------- ------ ------------- ------------ ----------
1          SYSDBA INDEX33555472 INDEX        128
2          SYSDBA SUN           TABLE        128已用时间: 295.663(毫秒). 执行号:1922.

4、使用率最高的50个sequence

SELECT SEQUENCE_OWNER,SEQUENCE_NAME,TO_NUMBER(ROUND(CAST(LAST_NUMBER AS DOUBLE) / MAX_VALUE * 100, 2)) || '%' AS PERCENTAGEFROM DBA_SEQUENCESWHERE SEQUENCE_OWNER != 'SYS'
ORDER BY PERCENTAGE DESC
FETCH FIRST 50 ROWS ONLY;

5、使用空间率最高的50个自增列

SELECT A.owner,A.OBJECT_NAME,ROUND(C.bytes / 1024 / 1024, 2) AS TotalSizeMFROM all_objects a
INNER JOIN SYSOBJECTS BON a.OBJECT_ID = b.ID
INNER JOIN dba_segments CON A.owner = C.owner AND A.OBJECT_NAME = C.segment_namewhere b.SUBTYPE$ = 'UTAB'AND b.INFO6 != '0x0000000000000000000000000000000001000000000000000000000000000000000000000000000000000000000000000100'ORDER BY TotalSizeM DESC
FETCH FIRST 50 ROWS ONLY;

6、定位锁

SELECT
A.*,
B.SESS_ID,B.SQL_TEXT,B.USER_NAME,B.CLNT_IP,
C.SESS_ID,C.SQL_TEXT,C.USER_NAME,C.CLNT_IP
FROM (SELECT TRX_ID,LTYPE,LMODE,TID FROM V$LOCK WHERE BLOCKED = 1) A 
JOIN V$SESSIONS B ON A.TRX_ID = B.TRX_ID
JOIN V$SESSIONS C ON A.TID    = C.TRX_ID;

别名	描述
A	锁信息
B	等待锁的会话信息
C	持有锁的会话信息

7、支持HINT

SELECT * FROM V$HINT_INI_INFO ORDER BY PARA_NAME;

8、表数据页使用率

SELECTOWNER,TABLE_NAME,SF_GET_PAGE_SIZE() PAGE_SIZE,TABLE_USED_SPACE(OWNER,TABLE_NAME) TOTAL_PAGE_NUMS,TABLE_USED_PAGES(OWNER,TABLE_NAME) USE_PAGE_NUMS,CAST(CAST(TABLE_USED_PAGES(OWNER,TABLE_NAME) AS DOUBLE) / TABLE_USED_SPACE(OWNER,TABLE_NAME) AS NUMBER) * 100 PCT
FROM DBA_TABLES
WHEREOWNER NOT IN ('SYS','SYSAUDITOR','SYSSSO','CTISYS')AND TABLE_USED_SPACE(OWNER,TABLE_NAME) != 0行号     OWNER  TABLE_NAME PAGE_SIZE   TOTAL_PAGE_NUMS      USE_PAGE_NUMS        PCT  
---------- ------ ---------- ----------- -------------------- -------------------- -----
1          SYSDBA SUN        8192        16                   15                   93.75已用时间: 34.657(毫秒). 执行号:1928.

9、备份文件相关信息

SQL> SELECT DEVICE_TYPE,BACKUP_PATH,TYPE,LEVEL,OBJECT_NAME,BACKUP_TIME,ENCRYPT_TYPE,COMPRESS_LEVEL,BEGIN_LSN,END_LSN,CUMULATIVE,VERSION FROM V$BACKUPSET;行号     DEVICE_TYPE BACKUP_PATH                             TYPE        LEVEL       OBJECT_NAME BACKUP_TIME                ENCRYPT_TYPE
---------- ----------- --------------------------------------- ----------- ----------- ----------- -------------------------- ------------COMPRESS_LEVEL BEGIN_LSN            END_LSN              CUMULATIVE  VERSION    -------------- -------------------- -------------------- ----------- -----------
1          DISK        /opt/Dm8/Data/DAMENG/bak/BACKUP_FILE_01 0           0           DAMENG      2025-04-11 14:27:38.266941 00              27606874             27606954             0           16394已用时间: 21.187(毫秒). 执行号:628.

10、初始化库参数信息

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'行号     '字符集'                 CASESF_GET_UNICODE_FLAG()WHEN'0'THEN'GBK18030'WHEN'1'THEN'UTF-8'WHEN'2'THEN'EUC-KR'END
---------- --------------------------- --------------------------------------------------------------------------------------
1          字符集                             GBK18030
2          页大小                             8
3          簇大小                             16
4          字符串比较大小写敏感        1
5          VARCHAR类型是否以字符为单位 0已用时间: 14.854(毫秒). 执行号:629.

11、REDO日志信息

SQL> select * from v$rlogfile;行号     GROUP_ID    FILE_ID     PATH                              CLIENT_PATH  CREATE_TIME                RLOG_SIZE            MIN_EXEC_VER
---------- ----------- ----------- --------------------------------- ------------ -------------------------- -------------------- ------------MIN_DCT_VER-----------
1          2           0           /opt/Dm8/Data/DAMENG/DAMENG01.log DAMENG01.log 2025-02-27 15:47:32.000000 2147483648           V8.1.1.142          2           1           /opt/Dm8/Data/DAMENG/DAMENG02.log DAMENG02.log 2025-02-27 15:47:32.000000 2147483648           V8.1.1.14已用时间: 1.905(毫秒). 执行号:630.

12、归档文件信息

SQL> SELECT * FROM V$ARCHIVED_LOG limit 2;行号     RECID       STAMP       NAME                                                                    DEST_ID     THREAD#             
---------- ----------- ----------- ----------------------------------------------------------------------- ----------- --------------------SEQUENCE#   RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE#        FIRST_TIME                 NEXT_CHANGE#        ----------- ----------------- -------------- ------------ -------------------- -------------------------- --------------------NEXT_TIME                  BLOCKS      BLOCK_SIZE  CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME-------------------------- ----------- ----------- ------- --------- ------------ -------- ------- ------- ------ ---------------DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE COMPRESSED FAL ---------------- -------------- ----------- ------------ ---------------- ----------- --------------------- ---------- ----END_OF_REDO_TYPE BACKED_BY_VSS ARCH_TYPE EP_SEQNO    ARCH_SEQ             NEXT_SEQ             VERSION             ---------------- ------------- --------- ----------- -------------------- -------------------- --------------------
1          NULL        NULL        /opt/Dm8/Data/DAMENG/Arch/MyArch_0x35BA095E_EP0_2025-02-28_15-12-47.log NULL        01           NULL              NULL           NULL         13461775             2025-02-28 15:12:47.615651 134619232025-02-28 17:55:14.413378 NULL        NULL        NULL    NULL      NULL         YES      NULL    NO      A      NULLNULL             NULL           NULL        NULL         NULL             NULL        NO                    NULL       NULLNULL             NULL          LOCAL     0           7718                 7751                 458764行号     RECID       STAMP       NAME                                                                    DEST_ID     THREAD#             
---------- ----------- ----------- ----------------------------------------------------------------------- ----------- --------------------SEQUENCE#   RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE#        FIRST_TIME                 NEXT_CHANGE#        ----------- ----------------- -------------- ------------ -------------------- -------------------------- --------------------NEXT_TIME                  BLOCKS      BLOCK_SIZE  CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME-------------------------- ----------- ----------- ------- --------- ------------ -------- ------- ------- ------ ---------------DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE COMPRESSED FAL ---------------- -------------- ----------- ------------ ---------------- ----------- --------------------- ---------- ----END_OF_REDO_TYPE BACKED_BY_VSS ARCH_TYPE EP_SEQNO    ARCH_SEQ             NEXT_SEQ             VERSION             ---------------- ------------- --------- ----------- -------------------- -------------------- --------------------
2          NULL        NULL        /opt/Dm8/Data/DAMENG/Arch/MyArch_0x35BA095E_EP0_2025-03-03_12-55-50.log NULL        02           NULL              NULL           NULL         13461924             2025-03-03 12:55:50.158300 134621042025-03-03 12:58:56.145858 NULL        NULL        NULL    NULL      NULL         YES      NULL    NO      A      NULLNULL             NULL           NULL        NULL         NULL             NULL        NO                    NULL       NULLNULL             NULL          LOCAL     0           7752                 7763                 458764已用时间: 1.710(毫秒). 执行号:633.

13、DBMS_SCHEDULER包创建且正在执行的作业

SELECT * FROM V$SCHEDULER_JOBS_RUNNING;