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

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

• 物理磁盘空间不足
  

现象：df -h 显示使用率超过90%
紧急清理
使用oracle用户登录linux系统

1. su – oracle

复制代码

输入相关密码

1. # 清理归档日志
2. rman target /
3. RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
4. RMAN> exit
5. # 清理回收站
6. sqlplus / as sysdba
7. PURGE DBA_RECYCLEBIN;
8. exit
9. # 查找并清理大文件
10. find /u01/app/oracle -type f -size +1G -exec ls -lh {} \;

复制代码

表空间使用率过高（例如 > 90%）

1. -- 增加数据文件
2. ALTER TABLESPACE <tablespace_name>
3. ADD DATAFILE '/data/oracle/database/orcl/表空间文件名称.dbf'
4. SIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
6. -- 或扩展现有数据文件,该操作需确认是否要使用
7. ALTER DATABASE DATAFILE '/data/oracle/database/orcl/表空间文件名称.dbf ' RESIZE 20G;

复制代码

表分区日期耗尽导致数据插入异常
现象：ORA-14400 或 ORA-14401 错误

1. -- 创建根据前文查询缺失的分区
2. ALTER TABLE 表名称
3. ADD PARTITION 分区名称VALUES LESS THAN ('截止日期，例如20250505')
4. TABLESPACE 对应表空间名称
5. PCTFREE 10
6. INITRANS 1
7. MAXTRANS 255
8. STORAGE(
9.         initial 8M
10.         next 1M
11.         minextents 1
12.         maxextents unlimited
13. );

复制代码

• 表空间不足，且磁盘空间已满
  

表空间无法扩展
现象：表空间无法扩展，且 df -h 显示磁盘已满，清理表空间, 收缩段：查找并收缩可以回收空间的表或索引。

1. -- 查找高水位线（HWM）较高的表
2. SELECT table_name, ROUND((blocks * 8) / 1024, 2) "高水位线(MB)",
3.        ROUND((num_rows * avg_row_len / 1024 / 1024), 2) "实际数据大小(MB)",
4.        ROUND((blocks * 8 ) / 1024, 2) - ROUND((num_rows * avg_row_len / 1024 / 1024), 2) "可回收空间(MB)"
5. FROM dba_tables
6. WHERE owner = 'YOUR_OWNER'
7.   AND ROUND((blocks * 8) / 1024, 2) > ROUND((num_rows * avg_row_len / 1024 / 1024), 2)
8. ORDER BY "可回收空间(MB)" DESC;
9. -- 当表经过大量DELETE操作后，有很多碎片空间时，对表进行移动和收缩（例如对表MY_TABLE）, 操作期间会锁定表，建议在业务低峰期执行
10. ALTER TABLE YOUR_OWNER.MY_TABLE ENABLE ROW MOVEMENT;
11. ALTER TABLE YOUR_OWNER.MY_TABLE SHRINK SPACE CASCADE;

复制代码

　　清理回收站

1. PURGE RECYCLEBIN; -- 清除当前用户的回收站
2. PURGE DBA_RECYCLEBIN; -- 需要DBA权限，清除整个数据库的回收站

复制代码

　　归档并清理历史数据
归档并清理历史数据：对于分区表，可以删除最老的不再需要的历史分区，这是最快最有效的方法，执行清理前，需查询并确认分区名称

1. ALTER TABLE YOUR_OWNER.YOUR_PARTITIONED_TABLE DROP PARTITION <partition_name>;

复制代码

• 自动创建表空间和表分区
  

自动创建表空间和表分区，该存储过程会创建三年（包含当年）的表空间和表分区，根据“检查清单”操作，查询所属用户的所有表分区，根据查询出来的表空间和表分区的命名方式，对以下存储过程进行修改。若表空间或表分区名称已存在，则会跳过继续执行下一个日期的逻辑。
[code]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