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

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

物理磁盘空间不足

现象：df -h 显示使用率超过90%

紧急清理

使用oracle用户登录linux系统

1

su – oracle

输入相关密码

1

2

3

4

5

6

7

8

9

10

# 清理归档日志

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%）

1

2

3

4

5

6

7

-- 增加数据文件

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 错误

1

2

3

4

5

6

7

8

9

10

11

12

13

-- 创建根据前文查询缺失的分区

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 显示磁盘已满，清理表空间, 收缩段：查找并收缩可以回收空间的表或索引。

1

2

3

4

5

6

7

8

9

10

11

-- 查找高水位线（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;

清理回收站

1

2

PURGE RECYCLEBIN; -- 清除当前用户的回收站

PURGE DBA_RECYCLEBIN; -- 需要DBA权限，清除整个数据库的回收站

归档并清理历史数据

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

1

ALTER TABLE YOUR_OWNER.YOUR_PARTITIONED_TABLE DROP PARTITION <partition_name>;

自动创建表空间和表分区

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

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;

/