ORACLE檢查并創建表空間和表分區
為確保系統在高并發、大數據量環境下的穩定高效運行，要求建立完善的表空間與表分區管理機制，具體包括：定期檢查表空間使用率，及時發現并處理空間不足風險；建立分區自動創建與維護流程，防止因分區缺失導致的數據插入失敗；制定緊急情況下的空間清理與擴展預案，確保在磁盤空間耗盡或表空間無法擴展時能夠快速響應并恢復系統正常運行。
- 物理磁盤空間不足
現象：df -h 顯示使用率超過90%
緊急清理
使用oracle用戶登錄linux系統
su – oracle
輸入相關密碼
# 清理歸檔日志
rman target /
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
RMAN> exit
# 清理回收站
sqlplus / as sysdba
PURGE DBA_RECYCLEBIN;
exit
# 查找并清理大文件
find /u01/app/oracle -type f -size +1G -exec ls -lh {} \;
表空間使用率過高（例如 > 90%）
-- 增加數據文件 ALTER TABLESPACE <tablespace_name> ADD DATAFILE '/data/oracle/database/orcl/表空間文件名稱.dbf' SIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; -- 或擴展現有數據文件,該操作需確認是否要使用 ALTER DATABASE DATAFILE '/data/oracle/database/orcl/表空間文件名稱.dbf ' RESIZE 20G;
表分區日期耗盡導致數據插入異常
現象：ORA-14400 或 ORA-14401 錯誤
-- 創建根據前文查詢缺失的分區
ALTER TABLE 表名稱
ADD PARTITION 分區名稱VALUES LESS THAN ('截止日期，例如20250505')
TABLESPACE 對應表空間名稱
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE(
initial 8M
next 1M
minextents 1
maxextents unlimited
);
- 表空間不足，且磁盤空間已滿
表空間無法擴展
現象：表空間無法擴展，且 df -h 顯示磁盤已滿，清理表空間, 收縮段：查找并收縮可以回收空間的表或索引。
-- 查找高水位線（HWM）較高的表
SELECT table_name, ROUND((blocks * 8) / 1024, 2) "高水位線(MB)",
ROUND((num_rows * avg_row_len / 1024 / 1024), 2) "實際數據大小(MB)",
ROUND((blocks * 8 ) / 1024, 2) - ROUND((num_rows * avg_row_len / 1024 / 1024), 2) "可回收空間(MB)"
FROM dba_tables
WHERE owner = 'YOUR_OWNER'
AND ROUND((blocks * 8) / 1024, 2) > ROUND((num_rows * avg_row_len / 1024 / 1024), 2)
ORDER BY "可回收空間(MB)" DESC;
-- 當表經過大量DELETE操作后，有很多碎片空間時，對表進行移動和收縮（例如對表MY_TABLE）, 操作期間會鎖定表，建議在業務低峰期執行
ALTER TABLE YOUR_OWNER.MY_TABLE ENABLE ROW MOVEMENT;
ALTER TABLE YOUR_OWNER.MY_TABLE SHRINK SPACE CASCADE;
清理回收站
PURGE RECYCLEBIN; -- 清除當前用戶的回收站 PURGE DBA_RECYCLEBIN; -- 需要DBA權限，清除整個數據庫的回收站
歸檔并清理歷史數據
歸檔并清理歷史數據：對于分區表，可以刪除最老的不再需要的歷史分區，這是最快最有效的方法，執行清理前，需查詢并確認分區名稱
ALTER TABLE YOUR_OWNER.YOUR_PARTITIONED_TABLE DROP PARTITION <partition_name>;
- 自動創建表空間和表分區
自動創建表空間和表分區，該存儲過程會創建三年（包含當年）的表空間和表分區，根據“檢查清單”操作，查詢所屬用戶的所有表分區，根據查詢出來的表空間和表分區的命名方式，對以下存儲過程進行修改。若表空間或表分區名稱已存在，則會跳過繼續執行下一個日期的邏輯。
CREATE PROCEDURE SYS_CREATE_TABLESPACE
/**************************************************************
* 存儲過程名稱: SYS_CREATE_TABLESPACE
* 建立日期 : 2025/10/16
* 作者 : 宋
* 作用 ：自動創建表空間和表分區
* 輸出 : 無返回值
*-------------------------------------------------------------
* 修改歷史
* 序號 日期 修改人 修改原因
* 1 2025/10/16 宋 新建
*
**************************************************************/
IS
-- 聲明游標：獲取未來3年（含當前年份）的每個季度的名稱，例如2025_Q1 2025_Q2
CURSOR cur_date IS
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), (LEVEL - 1) * 3), 'YYYY') AS QYEAR,
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), (LEVEL - 1) * 3), 'YYYYMM') AS QMONTH,
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), (LEVEL - 1) * 3), 'YYYY') || '_Q' ||
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), (LEVEL - 1) * 3), 'Q') AS QNAME,
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), (LEVEL - 1) * 3), 'YYYYMMDD') AS QDATE
FROM DUAL
CONNECT BY LEVEL <= 12;
-- 變量聲明
maxrows NUMBER DEFAULT 100000;
q_year DBMS_SQL.VARCHAR2_TABLE; -- 年份
q_month DBMS_SQL.VARCHAR2_TABLE; -- 月份
q_name DBMS_SQL.VARCHAR2_TABLE; -- 表空間和表分區的起名規則
q_date DBMS_SQL.VARCHAR2_TABLE; -- 表分區截止時間
v_proc_name VARCHAR2(50);
v_err_msg VARCHAR2(1024); -- 錯誤描述
i_code NUMBER;
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(4000);
v_sql VARCHAR2(4000);
BEGIN
v_proc_name := 'SYS_CREATE_TABLESPACE';
OPEN cur_date;
LOOP
-- 批量獲取季度數據
FETCH cur_date BULK COLLECT INTO q_year, q_month, q_name, q_date LIMIT maxrows;
-- 退出條件：當沒有數據時退出循環
EXIT WHEN q_name.COUNT = 0;
-- 遍歷每個季度
FOR i IN 1 .. q_name.COUNT LOOP
-- 獲取所有需要創建表空間和表分區的表信息
FOR CUR_TABLE IN (
SELECT owner,
table_name,
table_name || '_' || q_name(i) AS table_name_alias
FROM all_part_tables
WHERE owner IN ('AAAA', 'BBBB')
) LOOP
-- 跳過不需要創建表空間的表
IF CUR_TABLE.TABLE_NAME = 'XXXXXX' THEN
CONTINUE;
END IF;
-- 只為XXXXXX表創建表空間和分區
IF CUR_TABLE.TABLE_NAME = 'XXXXXX' THEN
-- 創建表空間（如果不存在）
BEGIN
-- XXXXXX只創建年份的（只在第一季度創建）
IF q_name(i) NOT LIKE '%_Q1' THEN
CONTINUE;
END IF;
v_sql := 'CREATE TABLESPACE ' || CUR_TABLE.TABLE_NAME || '_' || q_year(i) ||
' DATAFILE ''/data/oracle/database/orcl/' || CUR_TABLE.TABLE_NAME || '_' || q_year(i) || '.dbf'' ' ||
'SIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL';
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
SP_PASSYS_ERRHANDLE(v_proc_name, v_sqlcode, v_sqlerrm);
END;
-- 創建分區
BEGIN
v_sql := 'ALTER TABLE ' || CUR_TABLE.OWNER || '.' || CUR_TABLE.TABLE_NAME ||
' ADD PARTITION CP' || q_year(i) ||
' VALUES LESS THAN (''' || q_month(i) || ''') ' ||
' TABLESPACE ' || CUR_TABLE.TABLE_NAME || '_' || q_year(i);
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
SP_PASSYS_ERRHANDLE(v_proc_name, v_sqlcode, v_sqlerrm);
END;
END IF;
END LOOP; -- 結束表循環
END LOOP; -- 結束季度循環
END LOOP; -- 結束主循環
CLOSE cur_date;
EXCEPTION
WHEN OTHERS THEN
-- 異常處理：確保游標關閉
IF cur_date%ISOPEN THEN
CLOSE cur_date;
END IF;
RAISE;
END SYS_CREATE_TABLESPACE;
/
浙公網安備 33010602011771號