Oracle19c数据库日常巡检命令
数据库系统检查
数据库ID、名称、打开模式、归档模式、切换状态、角色查看
set lines 300
col DBID for 99999999999999999999
col NAME for a20
col DB_UNIQUE_NAME for a20
col OPEN_MODE for a20
col LOG_MODE for a20
col SWITCHOVER_STATUS for a20
col DATABASE_ROLE for a20
SELECT DBID,NAME,DB_UNIQUE_NAME,OPEN_MODE,LOG_MODE,SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE;
数据库实例名称、状态、启动时间与运行时长查看
set lines 300
col INSTANCE_NAME for a20
col STATUS for a20
col STARTUP_TIME for a20
col RUN_TIME for a20
SELECT
INSTANCE_NAME,
STATUS,
TO_CHAR(STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS') STARTUP_TIME,
TRUNC(SYSDATE -(STARTUP_TIME)) || 'day ' ||
TRUNC(24 *((SYSDATE - STARTUP_TIME) - TRUNC(SYSDATE - STARTUP_TIME))) || 'h ' ||
MOD(TRUNC(1440 *((SYSDATE - STARTUP_TIME) - TRUNC(SYSDATE - STARTUP_TIME))), 60) || 'm ' ||
MOD(TRUNC(86400 *((SYSDATE - STARTUP_TIME) - TRUNC(SYSDATE - STARTUP_TIME))), 60) || 's' RUN_TIME
FROM
GV$iNSTANCE;
闪回区大小检查
set lines 300
col FILE_TYPE for a30
col PERCENT_SPACE_USED for 99.99
col PERCENT_SPACE_RECLAIMABLE for 99.99
col NUMBER_OF_FILES for 99999999999999999999
col CON_ID for 9999999999
SELECT FILE_TYPE,
PERCENT_SPACE_USED,
PERCENT_SPACE_RECLAIMABLE,
NUMBER_OF_FILES,
CON_ID FROM V$RECOVERY_AREA_USAGE;
日志切换频率检查
set lines 300
col day for a6
col h00 for 999
col h01 for 999
col h02 for 999
col h03 for 999
col h04 for 999
col h05 for 999
col h06 for 999
col h07 for 999
col h08 for 999
col h09 for 999
col h10 for 999
col h11 for 999
col h12 for 999
col h13 for 999
col h14 for 999
col h15 for 999
col h16 for 999
col h17 for 999
col h18 for 999
col h19 for 999
col h20 for 999
col h21 for 999
col h22 for 999
col h23 for 999
col total for 99999
SELECT
substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 6, 5) day,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '00', 1, 0)) h00,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '01', 1, 0)) h01,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '02', 1, 0)) h02,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '03', 1, 0)) h03,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '04', 1, 0)) h04,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '05', 1, 0)) h05,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '06', 1, 0)) h06,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '07', 1, 0)) h07,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '08', 1, 0)) h08,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '09', 1, 0)) h09,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '10', 1, 0)) h10,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '11', 1, 0)) h11,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '12', 1, 0)) h12,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '13', 1, 0)) h13,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '14', 1, 0)) h14,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '15', 1, 0)) h15,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '16', 1, 0)) h16,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '17', 1, 0)) h17,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '18', 1, 0)) h18,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '19', 1, 0)) h19,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '20', 1, 0)) h20,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '21', 1, 0)) h21,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '22', 1, 0)) h22,
SUM(decode(substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 12, 2), '23', 1, 0)) h23,
COUNT(*) total
FROM
v$log_history a
WHERE
first_time > trunc(sysdate - 7)
GROUP BY
substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 6, 5)
ORDER BY
substr(to_char(first_time, 'YYYY-MM-DD HH24:MI:SS'), 6, 5);
切换到PDB
show pdbs
alter session set container=PDB01;
表空间大小检查
set lines 300
col TABLESPACE_NAME for a20
col MAXBYTES/GB for 9999999999
col BYTES/GB for 9999999999
col FREE/GB for 9999999999
col USED/GB for 9999999999
col BYTES_USAGE/% for 99.99
col MAXBYTES_USAGE/% for 99.99
SELECT
TABLESPACE_NAME "TABLESPACE_NAME",
ROUND(MAXBYTES / 1024, 2) "MAXBYTES/GB",
ROUND(BYTES / 1024, 2) "BYTES/GB",
ROUND(FREE / 1024, 2) "FREE/GB",
ROUND((BYTES - FREE) / 1024, 2) "USED/GB",
ROUND(10000 * CURRENT_USED / BYTES) / 100 "BYTES_USAGE/%",
ROUND(10000 * USED / MAXBYTES) / 100 "MAXBYTES_USAGE/%"
FROM
(
SELECT
A.TABLESPACE_NAME TABLESPACE_NAME,
FLOOR(A.MAXBYTES /(1024 * 1024)) MAXBYTES,
FLOOR(A.BYTES /(1024 * 1024)) BYTES,
FLOOR(B.FREE /(1024 * 1024)) FREE,
FLOOR((A.BYTES - B.FREE) /(1024 * 1024)) CURRENT_USED,
FLOOR((A.BYTES - B.FREE) /(1024 * 1024)) USED
FROM
(
SELECT
TABLESPACE_NAME TABLESPACE_NAME,
SUM(GREATEST(MAXBYTES,BYTES)) MAXBYTES,
SUM(BYTES) BYTES
FROM
DBA_DATA_FILES
GROUP BY
TABLESPACE_NAME
) A,
(
SELECT
TABLESPACE_NAME TABLESPACE_NAME,
SUM(BYTES) FREE
FROM
DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
) B
WHERE
A.TABLESPACE_NAME = B.TABLESPACE_NAME
)
ORDER BY 1;
数据文件检查
set lines 300
col TABLESPACE_NAME for a20
col FILE_NAME for a80
col BYTES/GB for 9999999999
col MAXBYTES/GB for 9999999999
col AUTOEXTENSIBLE for a15
SELECT
TABLESPACE_NAME,
FILE_NAME,
ROUND(BYTES / 1024 / 1024 /1024, 2) "BYTES/GB",
ROUND(MAXBYTES / 1024 / 1024 /1024, 2) "MAXBYTES/GB",
AUTOEXTENSIBLE
FROM
DBA_DATA_FILES
ORDER BY 1;
非系统用户排查
set lines 300
col USERNAME for a20
col DEFAULT_TABLESPACE for a20
SELECT USERNAME,DEFAULT_TABLESPACE from dba_users where ORACLE_MAINTAINED='N' AND DEFAULT_TABLESPACE!='SYSTEM';
高水位表检查
set lines 300
col OWNER for a20
col TABLE_NAME for a20
col TABLESPACE_NAME for a20
col HIGH_WATER_MARK(MB) for 9999999999
col RESERVED(MB) for 9999999999
col WASTED(MB) for 9999999999
col WASTED(%) for 99.99
SELECT OWNER,
TABLE_NAME,
TABLESPACE_NAME,
ROUND((BLOCKS * 8 / 1024), 2) "HIGH_WATER_MARK(MB)",
ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024), 2) "USERD(MB)",
ROUND((BLOCKS * 10 / 1024 / 100) * 8, 2) "RESERVED(MB)",
ROUND((BLOCKS * 8 / 1024 - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) - BLOCKS * 8 * 10 / 1024 / 100),2) "WASTED(MB)",
ROUND((ROUND((BLOCKS * 8 / 1024 - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) - BLOCKS * 8 * 10 / 1024 / 100),2) / ROUND((BLOCKS * 8 / 1024), 2)) * 100,2) "WASTED(%)"
FROM DBA_TABLES
WHERE
--非临时表
TEMPORARY = 'N'
--表中数据行数大于1
AND NUM_ROWS > 0
--表的高水位空间大小大于50M
AND ROUND((BLOCKS * 8), 2) >= 51200
AND ROUND((ROUND((BLOCKS * 8 / 1024 - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) - BLOCKS * 8 * 10 / 1024 / 100),2) / ROUND((BLOCKS * 8 / 1024), 2)) * 100,2) >=20.0
ORDER BY 1,4 DESC;
最后修改时间：2024-07-24 23:32:52
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
