【数据库】Oracle常用命令，DBA日常巡检维护SQL脚本

-- 设置SQL*Plus环境
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINESIZE 300
-- 检查数据库的健康状况
DECLARE
    v_db_role VARCHAR2(30);
BEGIN
    SELECT DATABASE_ROLE INTO v_db_role FROM V$DATABASE;
    IF v_db_role = 'PRIMARY' THEN
        DBMS_OUTPUT.PUT_LINE('数据库角色: ' || v_db_role);
    ELSE
        DBMS_OUTPUT.PUT_LINE('数据库角色: ' || v_db_role);
    END IF;
END;
/
 
-- 检查数据文件的健康状况
COLUMN file_name FORMAT A50 HEADING '文件名'
COLUMN size_mb FORMAT 99999 HEADING '大小|MB'
COLUMN free_mb FORMAT 99999 HEADING '剩余空间|MB'
COLUMN used_mb FORMAT 99999 HEADING '已使用空间|MB'
COLUMN pct_used FORMAT A5 HEADING '使用百分比'
SELECT
    NAME AS file_name,
    BYTES/1024/1024 AS size_mb,
    (BYTES - FREE_BYTES)/1024/1024 AS free_mb,
    (BYTES - FREE_BYTES - (NVL(HWM,0)-BYTES))/1024/1024 AS used_mb,
    ROUND((1 - NVL(HWM - FREE_BYTES, 0) / BYTES) * 100, 2) || '%' AS pct_used
FROM
    DBA_DATA_FILES
ORDER BY
    (BYTES - FREE_BYTES) DESC;
 
-- 检查临时文件的健康状况
COLUMN file_name FORMAT A50 HEADING '文件名'
COLUMN size_mb FORMAT 99999 HEADING '大小|MB'
COLUMN free_mb FORMAT 99999 HEADING '剩余空间|MB'
COLUMN used_mb FORMAT 99999 HEADING '已使用空间|MB'
COLUMN pct_used FORMAT A5 HEADING '使用百分比'
SELECT
    NAME AS file_name,
    BYTES/1024/1024 AS size_mb,
    FREE_BYTES/1024/1024 AS free_mb,
    (BYTES - FREE_BYTES)/1024/1024 AS used_mb,
    ROUND((1 - NVL(FREE_BYTES, 0) / BYTES) * 100, 2) || '%' AS pct_used
FROM
    V$TEMPFILE
ORDER BY
    (BYTES - FREE_BYTES) DESC;
 
-- 检查表空间的健康状况
COLUMN tablespace_name FORMAT A20 HEADING '表空间名'
COLUMN total_mb FORMAT 99999 HEADING '总大小|MB'
COLUMN free_mb FORMAT 99999 HEADING '剩余空间|MB'
COLUMN used_mb FORMAT 99999 HEADING '已使用空间|MB'
COLUMN pct_used FORMAT A5 HEADING '使用百分比'
SELECT
    TABLESPACE_NAME AS tablespace_name,
    ROUND(SUM(BYTES)/(1024*1024),2) AS total_mb,
    ROUND(SUM(FREE_BYTES)/(1024*1024),2) AS free_mb,
    ROUND((SUM(BYTES) - SUM(FREE_BYTES))/(1024*1024),2) AS used_mb,
    ROUND((1 - SUM(F