目录
一、环境信息
二、介绍
三、查询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;