ORACLE检查并创建表空间和表分区

技术分享 5个月前 (10-24) 0 999+

为确保系统在高并发、大数据量环境下的稳定高效运行，要求建立完善的表空间与表分区管理机制，具体包括：定期检查表空间使用率，及时发现并处理空间不足风险；建立分区自动创建与维护流程，防止因分区缺失导致的数据插入失败；制定紧急情况下的空间清理与扩展预案，确保在磁盘空间耗尽或表空间无法扩展时能够快速响应并恢复系统正常运行。

物理磁盘空间不足

现象：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; /

发表评论