oracle 表分区
分区表
通过本章节的学习,您可以学到以下几个问题
1、 了解分区表的概念
2、 清楚分区表的用途
3、 了解分区表的分类
4、 了解分区表的操作
在我们使用普通表的过程中会有数据量特别大的表,当一个表的数据超过过2000万条或占用2G空间时,建议建立分区表
分区表的分类:
Range(范围)分区
Hash(哈希)分区
List(列表)分区
以及组合分区:Range-Hash,Range-List
分区的优点:
增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能;
改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
执行概要
分区功能可以提高许多应用程序的可管理性、性能与可用性。通过分区功能,可以将表、索引和索引组织表进一步细分为段,从而能够更精确地管理和访问这些数据库对象。Orac#提供了种类繁多的分区方案以满足每种业务要求。而且,因为sql语句中分区是完全透明的,所以该功能几乎可应用于任何应用程序。
分区功能的优势
分区功能通过改善可管理性、性能和可用性,从而为各式应用程序带来了极大的好处。通常,分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务。通过分区,数据库设计人员和管理员能够解决前沿应用程序带来的一些难题。分区是构建千兆字节数据系统或超高可用性系统的关键工具。
分区功能的基本知识
分区功能能够将表、索引或索引组织表进一步细分为段。这些数据库对象的段叫做分区。每个分区有自己的名称,还可以选择自己的存储特性。
从数据库管理员的角度来看,一个分区后的对象具有多个段,这些段既可进行集体管理,也可单独管理。这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是,从应用程序的角度来看,分区后的表与非分区表完全相同,使#S#D#命令访问分区后的表时,无需任何修改,表的分区是通过“分区键”来实现的,分区键指的是一些列,这些列决定了某一行所在的分区。Orac#数据#1#提供了六项技术用于对表进行分区:
#范围分区
每个分区都由一个分区键值范围指定(对于一个以日期列作为分区键的表,“2005年1月”分区包含分区键值为从“2005年1月1日”到“2005年1月31日”的行)。
范围分区表示例:
假设有一个企业的销售数据比较大,可以作一个范围分区表
创建四个表空间,为分区表所用,应该把分区放在不同的物理设备上,性能更好,可我的测试环境是同一个物理设备
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select * from USER_TABLESPACES; 2 select * from DBA_TABLESPACE_GROUPS; 3 select * from dba_users; 4 select tablespace_name,file_id,file_name,autoextensible, 5 round(bytes/1024/1024/1024,3) "used(G)",round(maxbytes/1024/1024/1024,3) "size(G)" 6 from dba_data_files order by tablespace_name; 7 8 create tablespace lxl_test1 datafile '/dba/user_oracle/bspdev1/bspdev1/lxl_test1.dbf' size 50M; 9 CREATE TABLESPACE LXL_TEST2 DATAFILE '/dba/user_oracle/bspdev1/bspdev1/lxl_test2.dbf' size 50M; 10 CREATE TABLESPACE LXL_TEST3 DATAFILE '/dba/user_oracle/bspdev1/bspdev1/lxl_test3.dbf' size 50M; 11 CREATE TABLESPACE LXL_TEST4 DATAFILE '/dba/user_oracle/bspdev1/bspdev1/lxl_test4.dbf' size 50M; 12 --范围分区 13 create table tt_lxl_part_test1( 14 id number , 15 name varchar2(20), 16 total_cnt number, 17 price number, 18 create_tm date not null 19 )partition by range(create_tm) 20 (partition lxl_part1 values less than (to_date('2016-09-18','YYYY-MM-DD')) tablespace lxl_test1, 21 partition lxl_part2 values less than (to_date('2016-10-18','YYYY-MM-DD'))tablespace lxl_test2, 22 partition lxl_part3 values less than (to_date('2016-11-18','YYYY-MM-DD'))tablespace lxl_test3, 23 partition lxl_part4 values less than (to_date('2016-12-18','YYYY-MM-DD'))tablespace lxl_test4 24 ); 25 insert into tt_lxl_part_test1 values(1,'蔬菜','10','2.9',to_date('2016-09-10','YYYY-MM-DD')); 26 insert into tt_lxl_part_test1 values(2,'水果','13','7.9',to_date('2016-10-10','YYYY-MM-DD')); 27 insert into tt_lxl_part_test1 values(3,'零食','16','5.9',to_date('2016-11-10','YYYY-MM-DD')); 28 insert into tt_lxl_part_test1 values(4,'大米','17','11',to_date('2016-12-10','YYYY-MM-DD')); 29 30 select * from tt_lxl_part_test1 partition(lxl_part1); 31 select * from tt_lxl_part_test1 partition(lxl_part2); 32 select * from tt_lxl_part_test1 partition(lxl_part3); 33 select * from tt_lxl_part_test1 partition(lxl_part4); 34 --列表分区 35 create table lxl_part_test2( 36 id number(4), 37 name varchar2(30), 38 loca varchar2(30) 39 )partition by list(loca) 40 ( 41 partition p1 values('北京') tablespace lxl_test1, 42 partition p2 values('上海','天津','重庆') tablespace lxl_test2, 43 partition p3 values('广东','福建') tablespace lxl_test3, 44 partition p4 values(default) tablespace lxl_test4 45 ); 46 insert into lxl_part_test2 values(1,'李小龙','佛山'); 47 insert into lxl_part_test2 values(2,'顺丰','北京'); 48 insert into lxl_part_test2 values(3,'速运','上海'); 49 insert into lxl_part_test2 values(4,'系统集成','天津'); 50 insert into lxl_part_test2 values(5,'数据研发中心','重庆'); 51 insert into lxl_part_test2 values(6,'深圳','广东'); 52 insert into lxl_part_test2 values(7,'南山','福建'); 53 select * from lxl_part_test2 partition(p1); 54 select * from lxl_part_test2 partition(p2); 55 select * from lxl_part_test2 partition(p3); 56 select * from lxl_part_test2 partition(p4); 57 --散列分区 58 create table lxl_part_test3( 59 id number primary key, 60 class_id number(8) not null, 61 name varchar2(300), 62 finished_date date 63 ) 64 partition by hash(id) 65 ( 66 partition p01 tablespace lxl_test1, 67 partition p02 tablespace lxl_test2, 68 partition p03 tablespace lxl_test3 69 ); 70 insert into lxl_part_test3 values 71 (3,12, '上等的虎皮',to_date('2009-05-30','yyyy-mm-dd')); 72 insert into lxl_part_test3 values 73 (1,13, '虎皮',to_date('2009-05-30','yyyy-mm-dd')); 74 insert into lxl_part_test3 values 75 (200,15, '中等的虎皮',to_date('2009-05-30','yyyy-mm-dd')); 76 insert into lxl_part_test3 values 77 (230,19, '猫虎皮',to_date('2009-05-30','yyyy-mm-dd')); 78 79 select * from lxl_part_test3 partition(p01); 80 select * from lxl_part_test3 partition(p02); 81 select * from lxl_part_test3 partition(p03); 82 83 --组合分区 散列+范围 84 create table lxl_part_test4( 85 id number, 86 name varchar2(30), 87 finish_date date 88 )partition by range(finish_date) 89 subpartition by hash(id) 90 subpartitions 2 91 ( 92 partition p_group1 values less than(to_date('2016-09-18','YYYY-MM-DD')) TABLESPACE lxl_test1, 93 partition p_group2 values less than (to_date('2016-10-18','YYYY-MM-DD')) tablespace lxl_test2, 94 partition p_group3 values less than(maxvalue) tablespace lxl_test3 95 ); 96 insert into lxl_part_test4 values 97 (1,'所发生的',to_date('20161003','yyyymmdd')); 98 99 insert into lxl_part_test4 values 100 (2,'分区表测试',to_date('20161014','yyyymmdd')); 101 102 insert into lxl_part_test4 values 103 (3,'分区表规划',to_date('20161020','yyyymmdd')); 104 105 insert into lxl_part_test4 values 106 (4,'stream实施',to_date('20161018','yyyymmdd')); 107 108 insert into lxl_part_test4 values 109 (5,'oracle设计',to_date('20161103','yyyymmdd')); 110 111 insert into lxl_part_test4 values 112 (6,'数据库规划',to_date('20161103','yyyymmdd')); 113 114 select * from lxl_part_test4 partition(p_group3); 115 select * from lxl_part_test4 partition(p_group2); 116 select * from lxl_part_test4 partition(p_group1); 117 118 --组合分区 列表+范围 119 create table lxl_part_test5( 120 id NUMBER(6), 121 name VARCHAR2(20), 122 area VARCHAR2(30), 123 create_tm date 124 )partition by range(create_tm) 125 subpartition by list(area) 126 subpartition template( 127 subpartition east values('CHINA', 'JAPAN', 'INDIA', 'THAILAND'), 128 subpartition west values('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'), 129 subpartition other values(default)) 130 ( 131 partition p_group04 values less than(to_date('2016-09-18','YYYY-MM-DD')), 132 partition p_group05 values less than(to_date('2016-10-18','YYYY-MM-DD')), 133 partition p_group06 values less than(MAXVALUE) 134 ); 135 136 137 insert into lxl_part_test5 values (1,'阿斯顿发送到','china',to_date('2016-09-08','YYYY-MM-DD')); 138 139 insert into lxl_part_test5 values (2,'多福多寿','GERMANY',to_date('2016-09-17','YYYY-MM-DD')); 140 insert into lxl_part_test5 values (75,'oracle','JAPAN',to_date('2016-09-18','YYYY-MM-DD')); 141 insert into lxl_part_test5 values (34,'IBM','SWITZERLAND',to_date('2016-09-19','YYYY-MM-DD')); 142 insert into lxl_part_test5 values (43,'SUN','CHINA',to_date('2016-10-18','YYYY-MM-DD')); 143 insert into lxl_part_test5 values (876,'DELL','AMERICA',to_date('2017-09-18','YYYY-MM-DD')); 144 insert into lxl_part_test5 values (866,'SYSBASE','INDIA',to_date('2016-09-28','YYYY-MM-DD')); 145 146 select * from lxl_part_test5 partition(p_group04 );
#索引组织表可以按范围、列表或散列进行分区。
Oracle 数据库10g还提供了三种类型的分区索引:
#本地索引
本地索引是其分区方式与其所在基础表的分区方式一模一样的索引。本地索引的每个分区仅对应于其所在基础表的一个分区。
示例:
SQL> CREATE INDEX idx_dyx_part_test1 ON dyx_part_test1 (xiaos_date) LOCAL
2 (PARTITION idx_dyx_part_test1_01 TABLESPACE ts_xiaos2009q1,
3 PARTITION idx_dyx_part_test1_02 TABLESPACE ts_xiaos2009q2,
4 PARTITION idx_dyx_part_test1_03 TABLESPACE ts_xiaos2009q3,
5 PARTITION idx_dyx_part_test1_04 TABLESPACE ts_xiaos2009q4
6 ) TABLESPACE users;
Index created.
#全局分区索引
分区索引是使用不同于其所在表的分区键进行分区的索引,其所在表可以是分区表或非分区表。全局分区的索引可以使用
范围或散列分区进行分区。例如,某个表可以按月份进行范围分区,因此具有十二个分区,而该表上的索引则可以使用不同的分区键
进行范围分区,从而具有不同的分区数量。
分区索引就是在所有每个区上单独创建索引,它能自动维护,在drop或truncate某个分区时不影响该索引的其他分区索引的使用,也就是索
引不会失效,维护起来比较方便,但是在查询性能稍微有点影响
#全局非分区索引
全局非分区索引基本上和非分区表的索引一样。索引结构是不分区的。
Oracle 提供了一系列丰富的技术,可用于对表、索引和索引组织表进行分区,因此可以针对任何业务环境中的任何应用程序进行最佳的分区。
Oracle 还提供一套完整的SQL命令,用于管理分区表。其中包括添加新分区、删除分区、分拆分区以及合并分区的命令。
用分区功能提高可管理性
通过 Oracle 分区功能,可将表和索引分成更多、更小的可管理单元,从而使数据库管理员能以“化整为零,个个击破”的方式管理数据。
使用分区功能,维护操作可集中于表的特定部分。
例如,数据库管理员可以只对表的一部分做备份,而不必对整个表做备份。对整个数据库对象的维护操作,可以在每个分区的基础上进行,
从而将维护工作分解成更容易管理的小块。
利用分区功能提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。可以对
该表进行范围分区,使每个分区包含一周的数据。这样加载进程只是简单地添加新的分区。添加一个分区的操作比修改整个表效率高很多,因
为 DBA 不需要修改任何其他分区。
用分区功能提高性能
由于限制了所检查或操作的数据数量,同时支持并行任务执行,Oracle 分区功能实现了性能上增益。这些特性包括:
#分区修整
分区修整是用分区功能提高性能的最简单最有价值的手段。分区修整常常能够将查询性能提高几个数量级。例如,假设某个应用程序包含一个存
储订单历史记录的 Orders 表,并且此表已按周分区。查询一周的订单只需访问该订单表的一个分区。如果该订单表包含两年的历史记录,这个
查询只需要访问一个分区而不是一百零四个。该查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。
Oracle 能将分区修整功能与任何索引技术、联接技术或并行访问方法结合使用。
分区表的维护:
增加分区:
SQL> ALTER TABLE dyx_part_test1 ADD PARTITION xiaoss2009_q5
2 VALUES LESS THAN (TO_DATE('2010-06-01','YYYY-MM-DD'))
3 TABLESPACE users;
Table altered.
如果已有maxvalue分区,不能增加分区,可以采取分裂分区的办法增加分区!
删除分区:
SQL> ALTER TABLE dyx_part_test1 DROP partition xiaoss2009_q5;
Table altered.
截短分区:
SQL> alter table dyx_part_test1 truncate partition xiaoss2009_q4;
Table truncated.
合并分区:
SQL> alter table dyx_part_test1 merge partitions xiaoss2009_q3,xiaoss2009_q4 into partition xiaoss2009_q4;
Table altered.
SQL> alter index idx_dyx_part_test1 rebuild partition idx_dyx_part_test1_01 parallel 4;
Index altered.
分裂分区:
范围示例:
SQL> ALTER TABLE dyx_part_test1
2 SPLIT PARTITION xiaoss2009_q4
3 AT (TO_DATE('2009-10-01','YYYY-MM-DD'))
4 INTO (partition xiaoss2009_q3,partition xiaoss2009_q4);
Table altered.
列表示例:
SQL> select * from dyx_part_test2 partition(p2);
ID NAME LOCA
---------- ------------------------------ ------------------------------
3 广成子 天津
5 太已真人 上海
7 元始天尊 重庆
SQL> alter table dyx_part_test2 split partition p2 values ('天津','重庆') into (partition p5,partition p6);
Table altered.
SQL> select * from dyx_part_test2 partition(p2);
select * from dyx_part_test2 partition(p2)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL> select * from dyx_part_test2 partition(p5);
ID NAME LOCA
---------- ------------------------------ ------------------------------
3 广成子 天津
7 元始天尊 重庆
SQL> select * from dyx_part_test2 partition(p6);
ID NAME LOCA
---------- ------------------------------ ------------------------------
5 太已真人 上海
注:分开的分区(上例中的p5和p6)不能是原分区表中已经有的不同于被分区的分区
交换分区:
交换分区(Exchange partition)提供了一种方式,让你在表与表或分区与分区之间迁移数据,注意不是将表转换成分区或非分区的形式,而仅只是迁移表中数据
(互相迁移),由于其号称是采用了更改数据字典的方式,因此效率最高(几乎不涉及io操作)。Exchange partition适用于所有分区格式,你可以将数据从分区表迁移
到非分区表,也可以从非分区表迁移至分区表,或者从hash partition到range partition诸如此类吧
SQL> create table dyx_part_test7 (id number,name varchar2(50))
2 partition by range(id)
3 (partition t_range_p1 values less than (10) tablespace ts_xiaos2009q1,
4 partition t_range_p2 values less than (20) tablespace ts_xiaos2009q2,
5 partition t_range_p3 values less than (30) tablespace ts_xiaos2009q3,
6 partition t_range_pmax values less than (maxvalue) tablespace ts_xiaos2009q4
7 );
Table created.
SQL> insert into dyx_part_test7 values (11,'a');
1 row created.
SQL> insert into dyx_part_test7 values (12,'b');
1 row created.
SQL> insert into dyx_part_test7 values (13,'c');
1 row created.
SQL> commit;
Commit complete.
SQL> create table dyx_part_test7_tmp (id number,name varchar2(50));
Table created.
SQL> alter table dyx_part_test7 exchange partition t_range_p2
2 with table dyx_part_test7_tmp;
Table altered.
SQL> select * from dyx_part_test7 partition(t_range_p2);
no rows selected
SQL> select * from dyx_part_test7_tmp;
ID NAME
---------- --------------------------------------------------
11 a
12 b
13 c
记录成功交换到未分区的表中
接着再执行一次交换
SQL> alter table dyx_part_test7 exchange partition t_range_p2
2 with table dyx_part_test7_tmp;
Table altered.
SQL> select * from dyx_part_test7_tmp;
no rows selected
SQL> select * from dyx_part_test7 partition(t_range_p2);
ID NAME
---------- --------------------------------------------------
11 a
12 b
13 c
从上面可以看出又交换回去了
SQL> insert into dyx_part_test7_tmp values (15,'d');
1 row created.
SQL> insert into dyx_part_test7_tmp values (16,'e');
1 row created.
SQL> insert into dyx_part_test7_tmp values (17,'d');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> alter table dyx_part_test7 exchange partition t_range_p2
2 with table dyx_part_test7_tmp;
Table altered.
SQL>
SQL> select *from dyx_part_test7 partition(t_range_p2);
ID NAME
---------- --------------------------------------------------
15 d
16 e
17 d
SQL> select *from dyx_part_test7_tmp;
ID NAME
---------- --------------------------------------------------
11 a
12 b
13 c
从上面的测试可以看出,两个表或分区表是在交换数据!
注意:
参于交换的两表之间表结构必须一致,除非附加with validation子句;
如果是从非分区表向分区表做交换,非分区表中的数据必须符合分区表中指定分区的规则,除非附加without validation子句;
如果从分区表向分区表做交换,被交换的分区的数据必须符合分区规则,除非附加without validation子句;
Global索引或涉及到数据改动了的global索引分区会被置为unusable,除非附加update indexes子句。
提示:
一旦附加了without validation子句,则表示不再验证数据有效性,因此指定该子句时务必慎重。
访问指定分区:
SQL> select * from dyx_part_test2 partition(p1);
ID NAME LOCA
---------- ------------------------------ ------------------------------
2 云中子 北京
8 南级战神 北京
导出导入:
EXPORT指定分区:
[oracle@ora ~]$ exp dyx/dyx tables=dyx_part_test2:p1 file=dyx_part_test2_p1.dmp
Export: Release 10.2.0.1.0 - Production on Sat Apr 25 09:42:47 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table DYX_PART_TEST2
. . exporting partition P1 2 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
IMPORT指定分区:
[oracle@ora ~]$ imp dyx/dyx file=dyx_part_test2_p1.dmp TABLES=(dyx_part_test2:p1) IGNORE=y
Import: Release 10.2.0.1.0 - Production on Sat Apr 25 09:44:15 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing DYX's objects into DYX
. importing DYX's objects into DYX
. . importing partition "DYX_PART_TEST2":"P1" 2 rows imported
Import terminated successfully without warnings.
普通表变为分区表
将已存在数据的普通表转变为分区表,没有办法通过修改属性的方式直接转化为分区表,必须通过重建的方式进行转变,一般可以有三种方法:
方法一:利用原表重建分区表。
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY,TIME DATE);
Table created.
SQL> INSERT INTO T
2 SELECT ROWNUM, SYSDATE - ROWNUM FROM user_OBJECTS WHERE ROWNUM <= 5000;
57 rows created.
SQL> commit;
Commit complete.
SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2009-01-01', 'YYYY-MM-DD')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE('2009-04-01', 'YYYY-MM-DD')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE('2009-10-01', 'YYYY-MM-DD')),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE))
6 AS SELECT ID,TIME FROM T;
Table created.
更换表名
SQL> RENAME T TO T_OLD;
Table renamed.
SQL> SQL> RENAME T_NEW TO T;
Table renamed.
SQL> SELECT COUNT(*) FROM T PARTITION (P2);
COUNT(*)
----------
33
SQL> SELECT COUNT(*) FROM T PARTITION (P3);
COUNT(*)
----------
24
优点:方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建表完成后数据已经在分布到各个分区中了。
不足:对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的
修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。
适用于修改不频繁的表,在闲时进行操作,表的数据量不宜太大。
方法二:使用交换分区的方法。
因前面有闪换分区的示例,这里就不用重新示例了
优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行
完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。
不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行
分区的SPLIT操作,会增加操作的复杂度,效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
方法三:Oracle9i以上版本,利用在线重定义功能
不作示例
优点:保证数据的一致性,在大部分时间内,表T都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需
要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。
相关视图:
显示当前用户可访问的所有分区表信息﹕ALL_PART_TABLES
显示当前用户所有分区表的信息﹕USER_PART_TABLES
显示表分区信息 显示数据库所有分区表的详细分区信息﹕DBA_TAB_PARTITIONS
显示当前用户可访问的所有分区表的详细分区信息﹕ALL_TAB_PARTITIONS
显示当前用户所有分区表的详细分区信息﹕USER_TAB_PARTITIONS
显示子分区信息 显示数据库所有组合分区表的子分区信息﹕DBA_TAB_SUBPARTITIONS
显示当前用户可访问的所有组合分区表的子分区信息﹕ALL_TAB_SUBPARTITIONS
显示当前用户所有组合分区表的子分区信息﹕USER_TAB_SUBPARTITIONS
显示分区列 显示数据库所有分区表的分区列信息﹕DBA_PART_KEY_COLUMNS
显示当前用户可访问的所有分区表的分区列信息﹕ALL_PART_KEY_COLUMNS
显示当前用户所有分区表的分区列信息﹕USER_PART_KEY_COLUMNS
显示子分区列 显示数据库所有分区表的子分区列信息﹕DBA_SUBPART_KEY_COLUMNS
显示当前用户可访问的所有分区表的子分区列信息﹕ALL_SUBPART_KEY_COLUMNS
显示当前用户所有分区表的子分区列信息﹕USER_SUBPART_KEY_COLUMNS
1.创建用户 create user DBAMON identified by "bspdev" default tablespace BSP_DATA temporary tablespace temp profile DEFAULT; 2.授权 grant connect to DBAMON; grant create indextype to DBAMON; grant create job to DBAMON; grant create materialized view to DBAMON; grant create procedure to DBAMON; grant create public synonym to DBAMON; grant create sequence to DBAMON; grant create session to DBAMON; grant create table to DBAMON; grant create trigger to DBAMON; grant create type to DBAMON; grant create view to DBAMON; grant unlimited tablespace to DBAMON; alter user DBAMON quota unlimited on BSP_DATA; grant connect,resource,dba to DBAMON grant sys to DBAMON; grant all on sys to DBAMON; grant select on sys.dba_part_tables to DBAMON; grant select on sys.dba_part_tables to bspdev; grant select on sys.dba_tab_partitions to DBAMON; grant select on sys.dba_tab_partitions to bspdev; grant select on sys.dba_indexes to DBAMON; grant select on sys.dba_indexes to bspdev; grant select on sys.dba_ind_partitions to DBAMON; grant select on sys.dba_ind_partitions to bspdev; grant select on sys.DBA_TAB_COLUMNS to DBAMON; grant select on sys.DBA_TAB_COLUMNS to bspdev; grant select on sys.DBA_PART_KEY_COLUMNS to DBAMON; grant select on sys.DBA_PART_KEY_COLUMNS to bspdev; grant select on sys.v$instance to DBAMON; grant select on v$instance to bspdev; v$instance select instance_name from sys.v_$instance; alter user sys account unlock; select * /*into v_count*/ from v$instance where version like '11%'; DECLARE V_MSG VARCHAR(4000); BEGIN DBAMON.CONFIG_TAB_POLICY( v_MSG => V_MSG, v_table_owner => 'BSPDEV', v_table_name => 'TT_GEN_SPE_ROUTE_RECORD_BAK', v_drop_time_units => 3, v_policy => 4, v_partition_type => 'MONTH' ); DBMS_OUTPUT.put_line(SUBSTR(V_MSG,1,1000)); COMMIT; END; / select * from dba_part_tables t where t.table_name='TT_GEN_SPE_ROUTE_RECORD_BAK'; select * from tbl_part_info t where t.table_name='TT_GEN_SPE_ROUTE_RECORD_BAK'; create table DBAMON.TBL_PART_INFO ( table_owner VARCHAR2(40), table_name VARCHAR2(60), min_partition VARCHAR2(60), max_partition VARCHAR2(60), min_date VARCHAR2(15), max_date VARCHAR2(15), min_value VARCHAR2(2000), max_value VARCHAR2(2000), partition_column_type VARCHAR2(20), sample_time DATE, existmax VARCHAR2(10), format VARCHAR2(15), partition_type VARCHAR2(10), global_indx NUMBER(10), part_column_name VARCHAR2(100), autointerval VARCHAR2(100) ); create table DBAMON.TBL_TAB_PART_CONFIG ( id NUMBER(10) not null, table_owner VARCHAR2(30) not null, table_name VARCHAR2(30) not null, partition_column_type VARCHAR2(26) not null, partition_pre VARCHAR2(26) default 'P' not null, partition_type VARCHAR2(30) not null, drop_time_units NUMBER(10) not null, add_time_units NUMBER(10) not null, tablespace_name VARCHAR2(30), index_tb_name VARCHAR2(30), valid_flg NUMBER(1) not null, config_type VARCHAR2(100), format VARCHAR2(15), shour NUMBER(2), ehour NUMBER(2) ); -- Add comments to the columns comment on column DBAMON.TBL_TAB_PART_CONFIG.id is 'ID , 没有业务含义'; comment on column DBAMON.TBL_TAB_PART_CONFIG.table_owner is '需要维护的表属主'; comment on column DBAMON.TBL_TAB_PART_CONFIG.table_name is '需要维护的表名'; comment on column DBAMON.TBL_TAB_PART_CONFIG.partition_column_type is '分区列类型, 支持VARCHAR2和DATE,TIMESTAMP,CHAR,VARCHAR,类型'; comment on column DBAMON.TBL_TAB_PART_CONFIG.partition_pre is '分区名前缀'; comment on column DBAMON.TBL_TAB_PART_CONFIG.partition_type is '分区类型,月, 1 天,10天,5天,, WEEK'; comment on column DBAMON.TBL_TAB_PART_CONFIG.drop_time_units is '保留分区个数'; comment on column DBAMON.TBL_TAB_PART_CONFIG.add_time_units is '增加分区个数'; comment on column DBAMON.TBL_TAB_PART_CONFIG.tablespace_name is '表空间'; comment on column DBAMON.TBL_TAB_PART_CONFIG.index_tb_name is '所音表空间'; comment on column DBAMON.TBL_TAB_PART_CONFIG.valid_flg is '可用标记 1 加删 2不加不删,3分区只加不删,4 只删不加'; comment on column DBAMON.TBL_TAB_PART_CONFIG.config_type is 'test,and run'; comment on column DBAMON.TBL_TAB_PART_CONFIG.format is '分区类型为字符的格式YYYYMM或者YYYY-MM'; comment on column DBAMON.TBL_TAB_PART_CONFIG.shour is 'start hour 0-23'; comment on column DBAMON.TBL_TAB_PART_CONFIG.ehour is 'end hour 1-24'; -- Create/Recreate primary, unique and foreign key constraints alter table DBAMON.TBL_TAB_PART_CONFIG add constraint PK_TAB_PART_ID primary key (ID) using index; alter table DBAMON.TBL_TAB_PART_CONFIG add constraint C_CHECK_CONFIG check (PARTITION_TYPE IN ('MONTH','DAY','TEN','WEEK','FIVE')); alter table DBAMON.TBL_TAB_PART_CONFIG add constraint C_COLUMN_TYPE check (PARTITION_COLUMN_TYPE IN ('VARCHAR2','DATE')); alter table DBAMON.TBL_TAB_PART_CONFIG add constraint C_EHOUR check (EHOUR IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24) AND EHOUR>SHOUR); alter table DBAMON.TBL_TAB_PART_CONFIG add constraint C_SHOUR check (SHOUR IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)); -- Create table create table DBAMON.TBL_PART_MANAGE_SQL ( gid NUMBER(10), table_owner VARCHAR2(30), table_name VARCHAR2(50), partition_name VARCHAR2(100), max_date VARCHAR2(20), sql_text VARCHAR2(4000), create_time DATE, order_id NUMBER(10) not null, resultmsg VARCHAR2(100) ); -- Create/Recreate indexes create index DBAMON.IDX_SQLCREATETIME on DBAMON.TBL_PART_MANAGE_SQL (CREATE_TIME); -- Create/Recreate primary, unique and foreign key constraints alter table DBAMON.TBL_PART_MANAGE_SQL add primary key (ORDER_ID) using index ; -- Create table create table DBAMON.TBL_PART_ERR ( proc_name VARCHAR2(100), errmsg VARCHAR2(100), sample_time DATE ); -- Create table create table DBAMON.TBL_CONFIG_LOG ( config_time DATE, username VARCHAR2(50), oseruser VARCHAR2(50), proxy_user VARCHAR2(50), ip_addr VARCHAR2(100), machine VARCHAR2(50), table_owner VARCHAR2(50), table_name VARCHAR2(50), drop_time_units NUMBER(3), policy NUMBER(3), v_start_time NUMBER(3), v_end_time NUMBER(3), v_partition_type VARCHAR2(30), v_tab_tbs VARCHAR2(50), v_inx_tbs VARCHAR2(50), config_result VARCHAR2(2000) ); create sequence DBAMON.SEQ_TM_PART_MAINTEN minvalue 1 maxvalue 100000 start with 1 increment by 1 cache 5 cycle; create sequence DBAMON.SEQ_SQL_ORDER minvalue 1 maxvalue 10000000 start with 1 increment by 1 cache 20; CREATE OR REPLACE PACKAGE BODY DBAMON.PKG_MAINTEN_PART as --增加按月分区的SQL PROCEDURE STP_ADD_MON_DT AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_hour number(2); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 分区自动维护加分区会有问题。不再判断, 允许报错。 ---v4 分区指定表空间对于子分区SQL 可能会报错。修改未指定表空间的分区SQL不指定表空间。 --- author: bobo at 2014-09-10 ---v5 varchange BEGIN /*delete tbl_part_manage_sql;*/ commit; v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'MONTH' and p.partition_column_type = 'DATE' and p.valid_flg in (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_maxpart := null; v_tempower := rm.table_owner; --- 获取分区最大值 v_currmax_date := null; v_isdefault := 'FALSE'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; else execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; if v_currmax_date <= v_part_date or v_currmax_date is null then v_currmax_date := v_part_date; end if; end if; end loop; ---如果配置表里表空间为空,不指定表空间参数 if rm.tablespace_name is null then v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 while (add_months(v_currmax_date, -rm.add_time_units) <= trunc(sysdate, 'MM')) loop v_partname := to_char(trunc(v_currmax_date), 'YYYYMM'); v_partdate := to_char(trunc(add_months(v_currmax_date, 1)), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := add_months(v_currmax_date, 1); select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (to_date(''' || v_partdate || ''',''YYYY-MM-DD HH24:MI:SS''))' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (to_date(''' || v_partdate || ''', ''YYYY-MM-DD HH24:MI:SS'')) into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name || ' parallel 4'; begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, cur_part_ind.index_owner, cur_part_ind.index_name, cur_part_ind.partition_name, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempower, v_tempower, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_MON_DT; PROCEDURE STP_ADD_DAY_DT AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_hour number(2); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 分区自动维护加分区会有问题。不再判断, 允许报错。 BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'DAY' and p.partition_column_type = 'DATE' and p.valid_flg IN (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_currmax_date := null; v_tempower := rm.table_owner; --- 获取分区最大值 v_isdefault := 'FALSE'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; else ---获取分区的MAX_VALUE.转化成DATE类型 execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; ----判断是否分区最大值 if v_currmax_date <= v_part_date or v_currmax_date is null then v_currmax_date := v_part_date; end if; end if; end loop; ---获取表空间名字 if rm.tablespace_name is null then v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 while (v_currmax_date - rm.add_time_units <= trunc(sysdate)) loop v_partname := to_char(trunc(v_currmax_date), 'YYYYMMDD'); v_partdate := to_char(v_currmax_date + 1, 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 1; select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt <> 0) then v_partname := v_partname || 's'; end if; if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (to_date(''' || v_partdate || ''',''YYYY-MM-DD HH24:MI:SS''))' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (to_date(''' || v_partdate || ''', ''YYYY-MM-DD HH24:MI:SS'')) into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name /* a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status*/ from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner /*and a.partition_name = rm.partition_pre || v_partname*/ ) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop begin v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, cur_part_ind.index_owner, cur_part_ind.index_name, cur_part_ind.partition_name, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录异常日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempower, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_DAY_DT; PROCEDURE STP_ADD_DAY_VAR AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_hour number(2); v_current_maxval varchar2(3000); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 分区自动维护加分区会有问题。不再判断, 允许报错。 BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'DAY' and p.partition_column_type = 'VARCHAR2' and p.valid_flg IN (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_tempower := rm.table_owner; --- 获取分区最大值 v_isdefault := 'FALSE'; v_current_maxval := null; v_currmax_date := null; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; elsif v_current_maxval < v_high_value or v_current_maxval is null then v_current_maxval := v_high_value; end if; end loop; if rm.format = 'YYYY-MM-DD' then v_part_date := to_date(substr(v_current_maxval, 2, 10), 'YYYY-MM-DD'); elsif rm.format = 'YYYYMMDD' then v_part_date := to_date(substr(v_current_maxval, 2, 8), 'YYYYMMDD'); end if; v_currmax_date := v_part_date; ---如果配置表里表空间为空,找出该表目前所用表空间 if rm.tablespace_name is null then /* select tablespace_name into v_tablespace_name from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and rownum = 1;*/ v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 while (v_currmax_date - rm.add_time_units <= trunc(sysdate)) loop v_partname := to_char(trunc(v_currmax_date), 'YYYYMMDD'); if rm.format = 'YYYY-MM-DD' then v_partdate := to_char(trunc(v_currmax_date) + 1, 'YYYY-MM-DD'); elsif rm.format = 'YYYYMMDD' then v_partdate := to_char(trunc(v_currmax_date) + 1, 'YYYYMMDD'); end if; v_currmax_date := v_currmax_date + 1; select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (''' || v_partdate || ''') ' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (''' || v_partdate || ''' into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name /* a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status*/ from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner /*and a.partition_name = rm.partition_pre || v_partname*/ ) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop begin v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, cur_part_ind.index_owner, cur_part_ind.index_name, cur_part_ind.partition_name, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录异常日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempower, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_DAY_VAR; PROCEDURE STP_ADD_MON_VAR AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempowner varchar2(100); v_tempname varchar2(50); v_hour number(2); v_current_maxval varchar2(3000); ---V1:var类型的分区MAXVALUE 字段有两种,一种是YYYY-MM,另外一种是YYYYMM. 需要分别处理。 --- BEGIN ---删除SQL,测试用 /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; --获取本次处理的ID for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'MONTH' and p.partition_column_type = 'VARCHAR2' and p.valid_flg in (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempowner := rm.table_owner; v_tempname := rm.table_name; v_isdefault := 'FALSE'; --v_currmax_date := null; v_current_maxval := null; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop -- 获取当前分区的最大值, 转化为DATE类型 v_high_value := substr(cur_part.high_value, 1, 2000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; elsif v_current_maxval < v_high_value or v_current_maxval is null then v_current_maxval := v_high_value; end if; end loop; if rm.format = 'YYYY-MM' then v_part_date := to_date(substr(v_current_maxval, 2, 7), 'YYYY-MM'); elsif rm.format = 'YYYYMM' then v_part_date := to_date(substr(v_current_maxval, 2, 6), 'YYYYMM'); end if; v_currmax_date := v_part_date; --如果配置表里表空间为空,找出该表目前所用表空间 if rm.tablespace_name is null then select tablespace_name into v_tablespace_name from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and rownum = 1; else v_tablespace_name := rm.tablespace_name; end if; --增加分区 while (add_months(v_currmax_date, -rm.add_time_units) <= trunc(sysdate, 'MM')) loop v_partname := to_char(trunc(v_currmax_date), 'YYYYMM'); if rm.format = 'YYYY-MM' THEN v_partdate := to_char(trunc(add_months(v_currmax_date, 1)), 'YYYY-MM'); elsif rm.format = 'YYYYMM' THEN v_partdate := to_char(trunc(add_months(v_currmax_date, 1)), 'YYYYMM'); end if; v_currmax_date := add_months(v_currmax_date, 1); select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then -- ADD 分区 if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (''' || v_partdate || ''') tablespace ' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at ''' || v_partdate || ''' into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name /* a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status*/ from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner /*and a.partition_name = rm.partition_pre || v_partname*/ ) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录异常日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_MON_VAR; PROCEDURE STP_DEL_MON AS v_currmax_date date; v_high_value varchar2(4000); v_part_date date; v_drop_date date; v_partname varchar2(50); v_partdate varchar2(10); v_id number; v_sql varchar2(1000); v_errmsg varchar2(1000); v_tempowner varchar2(100); v_tempname varchar2(50); v_hour number(2); v_dropval varchar2(50); v_count NUMBER; BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; --处理DATE类型的分区表 for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type IN ('MONTH') and p.partition_column_type = 'DATE' and p.valid_flg in (1, 4) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_tempowner := rm.table_owner; v_currmax_date := null; --获取删除的最大DATE v_drop_date := add_months(trunc(sysdate, 'MM'), -rm.drop_time_units); for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); if v_high_value = 'MAXVALUE' then null; else execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; if v_drop_date >= v_part_date then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' drop partition ' || cur_part.partition_name; v_partname := cur_part.partition_name; v_partdate := to_char(v_part_date, 'YYYY-MM-DD'); ---if there is any global index. select count(*) into v_count from dba_indexes t where t.table_owner = RM.TABLE_OWNER AND T.table_name = rm.table_name and PARTITIONED = 'NO'; begin if rm.config_Type = 'TEST' and v_count = 0 then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' and v_count = 0 then v_errmsg := 'OK'; execute immediate v_sql; else v_errmsg := 'GLOBAL_INDEXES'; v_sql:=v_sql||' UPDATE GLOBAL INDEXES'; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end if; end if; end loop; end loop; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'MONTH' and p.partition_column_type = 'VARCHAR2' and p.valid_flg in (1, 4)) loop v_tempname := rm.table_name; v_tempowner := rm.table_owner; v_drop_date := add_months(trunc(sysdate, 'MM'), -rm.drop_time_units); if rm.format = 'YYYY-MM' then v_dropval := ''''||TO_CHAR(v_drop_date, 'YYYY-MM')||''''; elsif rm.format = 'YYYYMM' then v_dropval := ''''||TO_CHAR(v_drop_date, 'YYYYMM')||''''; elsif rm.format = 'YYYYMMDD' then v_dropval := ''''||TO_CHAR(v_drop_date, 'YYYYMMDD')||''''; elsif rm.format = 'YYYY-MM-DD' then v_dropval := ''''||TO_CHAR(v_drop_date, 'YYYY-MM-DD')||''''; end if; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 2000); if v_high_value = 'MAXVALUE' then null; else if v_dropval >= v_high_value then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' drop partition ' || cur_part.partition_name; v_partname := cur_part.partition_name; v_partdate := v_high_value; select count(*) into v_count from dba_indexes t where t.table_owner = RM.TABLE_OWNER AND T.table_name = rm.table_name and PARTITIONED = 'NO'; begin if rm.config_Type = 'TEST' and v_count = 0 then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' and v_count = 0 then v_errmsg := 'OK'; execute immediate v_sql; else v_errmsg := 'GLOBAL_INDEXES'; v_sql:=v_sql||' UPDATE GLOBAL INDEXES'; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end if; end if; end loop; for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; end loop; end loop; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录异常日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, 'ALL', v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_DEL_MON; PROCEDURE STP_DEL_DAY AS v_currmax_date date; v_high_value varchar2(4000); v_part_date date; v_drop_date date; v_id number; v_sql varchar2(1000); v_errmsg varchar2(1000); v_tempowner varchar2(100); v_tempname varchar2(50); v_partname varchar2(50); v_partdate varchar2(20); v_hour number(2); v_dropval varchar2(50); v_count NUMBER; BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'DAY' and p.partition_column_type = 'DATE' and p.valid_flg in (1, 4) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_tempowner := rm.table_owner; v_currmax_date := null; v_drop_date := trunc(sysdate, 'DD') - rm.drop_time_units; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); if v_high_value = 'MAXVALUE' then null; else execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; if v_drop_date >= v_part_date then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' drop partition ' || cur_part.partition_name; v_partname := cur_part.partition_name; v_partdate := to_char(v_part_date, 'YYYY-MM-DD'); select count(*) into v_count from dba_indexes t where t.table_owner = RM.TABLE_OWNER AND T.table_name = rm.table_name and PARTITIONED = 'NO'; begin if rm.config_Type = 'TEST' and v_count = 0 then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' and v_count = 0 then v_errmsg := 'OK'; execute immediate v_sql; else v_errmsg := 'GLOBAL_INDEXES'; v_sql:=v_sql||' UPDATE GLOBAL INDEXES'; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end if; end if; end loop; end loop; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'DAY' and p.partition_column_type = 'VARCHAR2' and p.valid_flg in (1, 4)) loop v_tempname := rm.table_name; v_currmax_date := null; v_drop_date := trunc(sysdate) - rm.drop_time_units; if rm.format = 'YYYY-MM-DD' THEN v_dropval := ''''||to_char(v_drop_date, 'YYYY-MM-DD')||''''; elsif rm.format = 'YYYYMMDD' THEN v_dropval := ''''||to_char(v_drop_date, 'YYYYMMDD')||''''; end if; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 2000); if v_high_value = 'MAXVALUE' then null; else if v_dropval >= v_high_value then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' drop partition ' || cur_part.partition_name; v_partname := cur_part.partition_name; v_partdate := v_high_value; select count(*) into v_count from dba_indexes t where t.table_owner = RM.TABLE_OWNER AND T.table_name = rm.table_name and PARTITIONED = 'NO'; begin if rm.config_Type = 'TEST' and v_count = 0 then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' and v_count = 0 then v_errmsg := 'OK'; execute immediate v_sql; else v_errmsg := 'GLOBAL_INDEXES'; v_sql:=v_sql||' UPDATE GLOBAL INDEXES'; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end if; end if; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_DEL_DAY; PROCEDURE STP_ADD_WEEK_DT AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_hour number(2); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 分区自动维护加分区会有问题。不再判断, 允许报错。 ---v4 分区指定表空间对于子分区SQL 可能会报错。修改未指定表空间的分区SQL不指定表空间。 BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'WEEK' and p.partition_column_type = 'DATE' and p.valid_flg in (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_maxpart := null; v_tempower := rm.table_owner; --- 获取分区最大值 v_currmax_date := null; v_isdefault := 'FALSE'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; else execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; if v_currmax_date <= v_part_date or v_currmax_date is null then v_currmax_date := v_part_date; end if; end if; end loop; ---如果配置表里表空间为空,不指定表空间参数 if rm.tablespace_name is null then v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 while (v_currmax_date - rm.add_time_units * 7 <= SYSDATE) loop v_partname := to_char(trunc(v_currmax_date), 'YYYYMMDD'); v_partdate := TO_CHAR(trunc(v_currmax_date + 7), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 7; select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (to_date(''' || v_partdate || ''',''YYYY-MM-DD HH24:MI:SS''))' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (to_date(''' || v_partdate || ''', ''YYYY-MM-DD HH24:MI:SS'')) into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, cur_part_ind.index_owner, cur_part_ind.index_name, cur_part_ind.partition_name, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempower, v_tempower, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_WEEK_DT; PROCEDURE STP_ADD_WEEK_VAR AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_hour number(2); v_current_maxval varchar2(3000); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 分区自动维护加分区会有问题。不再判断, 允许报错。 BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'WEEK' and p.partition_column_type = 'VARCHAR2' and p.valid_flg IN (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_currmax_date := null; v_tempower := rm.table_owner; v_current_maxval := null; --- 获取分区最大值 v_isdefault := 'FALSE'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; elsif v_current_maxval < v_high_value or v_current_maxval is null then v_current_maxval := v_high_value; end if; end loop; ---transfer to date if rm.format = 'YYYY-MM-DD' then v_part_date := to_date(substr(v_current_maxval, 2, 10), 'YYYY-MM-DD'); elsif rm.format = 'YYYYMMDD' then v_part_date := to_date(substr(v_current_maxval, 2, 8), 'YYYYMMDD'); end if; v_currmax_date := v_part_date; ---如果配置表里表空间为空,找出该表目前所用表空间 if rm.tablespace_name is null then /* select tablespace_name into v_tablespace_name from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and rownum = 1;*/ v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 while (v_currmax_date - rm.add_time_units * 7 <= trunc(sysdate)) loop v_partname := to_char(trunc(v_currmax_date), 'YYYYMMDD'); if rm.format = 'YYYY-MM-DD' then v_partdate := to_char(trunc(v_currmax_date) + 7, 'YYYY-MM-DD'); elsif rm.format = 'YYYYMMDD' then v_partdate := to_char(trunc(v_currmax_date) + 7, 'YYYYMMDD'); end if; v_currmax_date := v_currmax_date + 7; select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (''' || v_partdate || ''') ' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (''' || v_partdate || ''' into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name /* a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status*/ from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner /*and a.partition_name = rm.partition_pre || v_partname*/ ) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop begin v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, cur_part_ind.index_owner, cur_part_ind.index_name, cur_part_ind.partition_name, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录异常日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempower, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_WEEK_VAR; PROCEDURE STP_ADD_FIVE_DT AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_units number; v_hour number(2); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 分区自动维护加分区会有问题。不再判断, 允许报错。 ---v4 分区指定表空间对于子分区SQL 可能会报错。修改未指定表空间的分区SQL不指定表空间。 BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'FIVE' and p.partition_column_type = 'DATE' and p.valid_flg in (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_maxpart := null; v_tempower := rm.table_owner; v_units := trunc(TO_NUMBER(TO_CHAR(sysdate, 'DD')) / 5) + 1 + rm.add_time_units; --- 获取分区最大值 v_currmax_date := null; v_isdefault := 'FALSE'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; else execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; if v_currmax_date <= v_part_date or v_currmax_date is null then v_currmax_date := v_part_date; end if; end if; end loop; ---如果配置表里表空间为空,不指定表空间参数 if rm.tablespace_name is null then v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 while (v_currmax_date <= add_months(trunc(sysdate, 'MM'), trunc(v_units / 6)) + mod(v_units, 6) * 5) loop if trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 0 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'A'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 1 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'B'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 2 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'C'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 3 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'D'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 4 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'E'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 5 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'F'; v_partdate := TO_CHAR(ADD_MONTHS(trunc(v_currmax_date, 'MM'), 1), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := ADD_MONTHS(trunc(v_currmax_date, 'MM'), 1); end if; select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (to_date(''' || v_partdate || ''',''YYYY-MM-DD HH24:MI:SS''))' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (to_date(''' || v_partdate || ''', ''YYYY-MM-DD HH24:MI:SS'')) into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); COMMIT; --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; end loop; end STP_ADD_FIVE_DT; PROCEDURE STP_ADD_FIVE_VAR AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_units number; v_hour number(2); v_current_maxval varchar2(3000); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 对于11G 的新建分区不做判断, 直接处理。 BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'FIVE' and p.partition_column_type = 'VARCHAR2' and p.valid_flg IN (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_tempower := rm.table_owner; v_current_maxval := null; --- 获取分区最大值 v_isdefault := 'FALSE'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; elsif v_current_maxval < v_high_value or v_current_maxval is null then v_current_maxval := v_high_value; end if; end loop; ---transfer to date if rm.format = 'YYYY-MM-DD' then v_part_date := to_date(substr(v_current_maxval, 2, 10), 'YYYY-MM-DD'); elsif rm.format = 'YYYYMMDD' then v_part_date := to_date(substr(v_current_maxval, 2, 8), 'YYYYMMDD'); end if; v_currmax_date := v_part_date; ---如果配置表里表空间为空,找出该表目前所用表空间 if rm.tablespace_name is null then /* select tablespace_name into v_tablespace_name from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and rownum = 1;*/ v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 v_units := trunc(TO_NUMBER(TO_CHAR(sysdate, 'DD')) / 5) + 1 + rm.add_time_units; while (v_currmax_date - rm.add_time_units <= add_months(trunc(sysdate, 'MM'), trunc(v_units / 6)) + mod(v_units, 6) * 10) loop if trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 0 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'A'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 1 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'B'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 2 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'C'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 3 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'D'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 4 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'E'; v_partdate := TO_CHAR(trunc(v_currmax_date + 5), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 5; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 5) = 5 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'F'; v_partdate := TO_CHAR(ADD_MONTHS(trunc(v_currmax_date, 'MM'), 1), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := ADD_MONTHS(trunc(v_currmax_date, 'MM'), 1); end if; if rm.format = 'YYYY-MM-DD' then v_partdate := to_char(trunc(v_currmax_date), 'YYYY-MM-DD'); elsif rm.format = 'YYYYMMDD' then v_partdate := to_char(trunc(v_currmax_date), 'YYYYMMDD'); end if; select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (''' || v_partdate || ''') ' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (''' || v_partdate || ''' into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name /* a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status*/ from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner /*and a.partition_name = rm.partition_pre || v_partname*/ ) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop begin v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, cur_part_ind.index_owner, cur_part_ind.index_name, cur_part_ind.partition_name, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录异常日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempower, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_FIVE_VAR; PROCEDURE STP_DEL_WEEK AS v_currmax_date date; v_high_value varchar2(4000); v_part_date date; v_drop_date date; v_id number; v_sql varchar2(1000); v_errmsg varchar2(1000); v_tempowner varchar2(100); v_tempname varchar2(50); v_partname varchar2(50); v_partdate varchar2(20); v_hour number(2); v_dropvalue varchar2(40); v_count NUMBER; BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'WEEK' and p.partition_column_type = 'DATE' and p.valid_flg in (1, 4) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_tempowner := rm.table_owner; v_currmax_date := null; v_drop_date := trunc(sysdate) - rm.drop_time_units * 7; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); if v_high_value = 'MAXVALUE' then null; else execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; if v_drop_date >= v_part_date then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' drop partition ' || cur_part.partition_name; v_partname := cur_part.partition_name; v_partdate := to_char(v_part_date, 'YYYY-MM-DD'); select count(*) into v_count from dba_indexes t where t.table_owner = RM.TABLE_OWNER AND T.table_name = rm.table_name and PARTITIONED = 'NO'; begin if rm.config_Type = 'TEST' and v_count = 0 then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' and v_count = 0 then v_errmsg := 'OK'; execute immediate v_sql; else v_errmsg := 'GLOBAL_INDEXES'; v_sql:=v_sql||' UPDATE GLOBAL INDEXES'; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end if; end if; end loop; end loop; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'WEEK' and p.partition_column_type = 'VARCHAR2' and p.valid_flg in (1, 4)) loop v_tempname := rm.table_name; v_currmax_date := null; v_drop_date := trunc(sysdate) - rm.drop_time_units * 7; if rm.format = 'YYYY-MM-DD' THEN v_dropvalue := ''''||to_char(v_drop_date, 'YYYY-MM-DD')||''''; elsif rm.format = 'YYYYMMDD' THEN v_dropvalue := ''''||to_char(v_drop_date, 'YYYYMMDD')||''''; end if; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 2000); if v_high_value = 'MAXVALUE' then null; else if v_dropvalue > v_high_value then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' drop partition ' || cur_part.partition_name; v_partname := cur_part.partition_name; v_partdate := v_high_value; select count(*) into v_count from dba_indexes t where t.table_owner = RM.TABLE_OWNER AND T.table_name = rm.table_name and PARTITIONED = 'NO'; begin if rm.config_Type = 'TEST' and v_count = 0 then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' and v_count = 0 then v_errmsg := 'OK'; execute immediate v_sql; else v_errmsg := 'GLOBAL_INDEXES'; v_sql:=v_sql||' UPDATE GLOBAL INDEXES'; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end if; end if; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_DEL_WEEK; PROCEDURE STP_ADD_TEN_DT AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_units number; v_hour number(2); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 分区自动维护加分区会有问题。不再判断, 允许报错。 ---v4 分区指定表空间对于子分区SQL 可能会报错。修改未指定表空间的分区SQL不指定表空间。 BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'TEN' and p.partition_column_type = 'DATE' and p.valid_flg in (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_maxpart := null; v_tempower := rm.table_owner; v_units := trunc(TO_NUMBER(TO_CHAR(sysdate, 'DD')) / 10) + 1 + rm.add_time_units; --- 获取分区最大值 v_currmax_date := null; v_isdefault := 'FALSE'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; else execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; if v_currmax_date <= v_part_date or v_currmax_date is null then v_currmax_date := v_part_date; end if; end if; end loop; ---如果配置表里表空间为空,不指定表空间参数 if rm.tablespace_name is null then v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 while (v_currmax_date <= add_months(trunc(sysdate, 'MM'), trunc(v_units / 3)) + mod(v_units, 3) * 10) loop if trunc(to_number(to_char(v_currmax_date, 'DD')) / 10) = 0 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'A'; v_partdate := TO_CHAR(trunc(v_currmax_date + 10), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 10; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 10) = 1 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'B'; v_partdate := TO_CHAR(trunc(v_currmax_date + 10), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 10; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 10) = 2 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'C'; v_partdate := TO_CHAR(ADD_MONTHS(trunc(v_currmax_date, 'MM'), 1), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := ADD_MONTHS(trunc(v_currmax_date, 'MM'), 1); end if; select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (to_date(''' || v_partdate || ''',''YYYY-MM-DD HH24:MI:SS''))' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (to_date(''' || v_partdate || ''', ''YYYY-MM-DD HH24:MI:SS'')) into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, cur_part_ind.index_owner, cur_part_ind.index_name, cur_part_ind.partition_name, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempower, v_tempower, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_TEN_DT; PROCEDURE STP_ADD_TEN_VAR AS v_currmax_date date; v_partdate varchar2(20); v_high_value varchar2(4000); v_partname varchar2(30); v_part_date date; v_tablespace_name varchar2(80); v_partition_cnt number(10); v_tempname varchar2(50); v_id number; v_isdefault varchar2(20); v_sql varchar2(1000); v_errmsg varchar2(1000); v_maxpart varchar2(100); v_tempower varchar2(100); v_units number; v_hour number(2); v_current_maxval varchar2(3000); ---v1 tbl_part_manage_sql 修改为日志表。 记录执行时的日志。增加执行时的判断,记录报错信息。 ---v2 考虑split分区的情况, ---v3 对于11G 的新建分区不做判断, 直接处理。 BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'TEN' and p.partition_column_type = 'VARCHAR2' and p.valid_flg IN (1, 3) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_tempower := rm.table_owner; v_current_maxval := null; --- 获取分区最大值 v_isdefault := 'FALSE'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); --- 判断是否存在MAXVALUE, 如果存在则只能使用SPLIT分区,记录DEFAULT 分区名字 if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; v_maxpart := cur_part.partition_name; elsif v_current_maxval < v_high_value or v_current_maxval is null then v_current_maxval := v_high_value; end if; end loop; ---transfer to date if rm.format = 'YYYY-MM-DD' then v_part_date := to_date(substr(v_current_maxval, 2, 10), 'YYYY-MM-DD'); elsif rm.format = 'YYYYMMDD' then v_part_date := to_date(substr(v_current_maxval, 2, 8), 'YYYYMMDD'); end if; v_currmax_date := v_part_date; ---如果配置表里表空间为空,找出该表目前所用表空间 if rm.tablespace_name is null then /* select tablespace_name into v_tablespace_name from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and rownum = 1;*/ v_tablespace_name := ''; else v_tablespace_name := ' tablespace ' || rm.tablespace_name; end if; ---生成加分区SQL并执行。 v_units := trunc(TO_NUMBER(TO_CHAR(sysdate, 'DD')) / 10) + 1 + rm.add_time_units; while (v_currmax_date - rm.add_time_units <= add_months(trunc(sysdate, 'MM'), trunc(v_units / 3)) + mod(v_units, 3) * 10) loop if trunc(to_number(to_char(v_currmax_date, 'DD')) / 10) = 0 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'A'; v_partdate := TO_CHAR(trunc(v_currmax_date + 10), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 10; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 10) = 1 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'B'; v_partdate := TO_CHAR(trunc(v_currmax_date + 10), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := v_currmax_date + 10; elsif trunc(to_number(to_char(v_currmax_date, 'DD')) / 10) = 2 then v_partname := to_char(trunc(v_currmax_date), 'YYYYMM') || 'C'; v_partdate := TO_CHAR(ADD_MONTHS(trunc(v_currmax_date, 'MM'), 1), 'YYYY-MM-DD HH24:MI:SS'); v_currmax_date := ADD_MONTHS(trunc(v_currmax_date, 'MM'), 1); end if; if rm.format = 'YYYY-MM-DD' then v_partdate := to_char(trunc(v_currmax_date), 'YYYY-MM-DD'); elsif rm.format = 'YYYYMMDD' then v_partdate := to_char(trunc(v_currmax_date), 'YYYYMMDD'); end if; select count(1) into v_partition_cnt from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name and partition_name = rm.partition_pre || v_partname; if (v_partition_cnt = 0) then if v_isdefault = 'FALSE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' add partition ' || rm.partition_pre || v_partname || ' values less than (''' || v_partdate || ''') ' || v_tablespace_name; elsif v_isdefault = 'TRUE' then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' split partition ' || v_maxpart || ' at (''' || v_partdate || ''' into (partition ' || rm.partition_pre || v_partname || ',partition ' || v_maxpart || ')'; end if; ---执行加分区的SQL begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); --如果配置了索引表空间索引,索引分区需rebuild到指定的索引表空间 if (rm.index_tb_name is not null) then for cur_part_ind in (select distinct a.index_owner, a.index_name /* a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status*/ from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and a.index_owner = rm.table_owner /*and a.partition_name = rm.partition_pre || v_partname*/ ) loop v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || rm.partition_pre || v_partname || ' tablespace ' || rm.index_tb_name; --- 执行SQL 重建索引 begin if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, rm.table_owner, rm.table_name, rm.partition_pre || v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end if; end if; end loop; --重建不可用的局部索引 for cur_part_ind in (select a.index_owner, a.index_name, a.partition_name, a.tablespace_name, a.status from dba_ind_partitions a, dba_indexes b where a.index_owner = b.owner and a.index_name = b.index_name and b.table_name = rm.table_name and b.table_owner = rm.table_owner and a.status = 'UNUSABLE') loop begin v_sql := 'alter index ' || cur_part_ind.index_owner || '.' || cur_part_ind.index_name || ' rebuild partition ' || cur_part_ind.partition_name || ' tablespace ' || cur_part_ind.tablespace_name; if rm.config_Type = 'TEST' then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' then v_errmsg := 'OK'; execute immediate v_sql; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, cur_part_ind.index_owner, cur_part_ind.index_name, cur_part_ind.partition_name, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录异常日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempower, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_ADD_TEN_VAR; PROCEDURE STP_DEL_TEN AS v_currmax_date date; v_high_value varchar2(4000); v_part_date date; v_drop_date date; v_id number; v_sql varchar2(1000); v_errmsg varchar2(1000); v_tempowner varchar2(100); v_tempname varchar2(50); v_partname varchar2(50); v_partdate varchar2(20); v_units number; v_hour number(2); v_dropval varchar2(40); v_count NUMBER; BEGIN /*delete tbl_part_manage_sql;*/ v_hour := to_number(to_char(sysdate, 'HH24')); select SEQ_TM_PART_MAINTEN.nextval into v_id from dual; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'TEN' and p.partition_column_type = 'DATE' and p.valid_flg in (1, 4) and v_hour >= p.shour and v_hour < p.ehour) loop v_tempname := rm.table_name; v_tempowner := rm.table_owner; v_currmax_date := null; ---获取需删除分区的最大日期 v_units := trunc(TO_NUMBER(TO_CHAR(sysdate, 'DD')) / 10) + 1 - rm.drop_time_units; if v_units < 0 then v_drop_date := ADD_MONTHS(trunc(sysdate, 'MM'), trunc(v_units / 3) - 1) + (mod(v_units, 3) + 3) * 10; elsif v_units >= 0 then v_drop_date := trunc(sysdate, 'MM') + v_units * 10; end if; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); if v_high_value = 'MAXVALUE' then null; else execute immediate 'select ' || v_high_value || ' from dual' into v_part_date; if v_drop_date >= v_part_date then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' drop partition ' || cur_part.partition_name; v_partname := cur_part.partition_name; v_partdate := to_char(v_part_date, 'YYYY-MM-DD'); select count(*) into v_count from dba_indexes t where t.table_owner = RM.TABLE_OWNER AND T.table_name = rm.table_name and PARTITIONED = 'NO'; begin if rm.config_Type = 'TEST' and v_count = 0 then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' and v_count = 0 then v_errmsg := 'OK'; execute immediate v_sql; else v_errmsg := 'GLOBAL_INDEXES'; v_sql:=v_sql||' UPDATE GLOBAL INDEXES'; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end if; end if; end loop; end loop; for rm in (select distinct p.* from tbl_tab_part_config p, dba_tab_partitions u where p.table_owner = u.table_owner and p.table_name = u.table_name and p.partition_type = 'TEN' and p.partition_column_type = 'VARCHAR2' and p.valid_flg in (1, 4)) loop v_tempname := rm.table_name; v_currmax_date := null; ----获取分区最大日期的类型 v_units := trunc(TO_NUMBER(TO_CHAR(sysdate, 'DD')) / 10) + 1 - rm.drop_time_units; if v_units < 0 then v_drop_date := ADD_MONTHS(trunc(sysdate, 'MM'), trunc(v_units / 3) - 1) + (mod(v_units, 3) + 3) * 10; elsif v_units >= 0 then v_drop_date := trunc(sysdate, 'MM') + v_units * 10; end if; if rm.format = 'YYYY-MM-DD' THEN v_dropval := ''''||to_char(v_drop_date, 'YYYY-MM-DD')||''''; elsif rm.format = 'YYYYMMDD' THEN v_dropval := ''''||to_char(v_drop_date, 'YYYYMMDD')||''''; end if; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = rm.table_owner and table_name = rm.table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 2000); if v_high_value = 'MAXVALUE' then null; else if v_dropval >= v_high_value then v_sql := 'alter table ' || rm.table_owner || '.' || rm.table_name || ' drop partition ' || cur_part.partition_name; v_partname := cur_part.partition_name; v_partdate := v_high_value; select count(*) into v_count from dba_indexes t where t.table_owner = RM.TABLE_OWNER AND T.table_name = rm.table_name and PARTITIONED = 'NO'; begin if rm.config_Type = 'TEST' and v_count = 0 then v_errmsg := 'TEST'; elsif rm.config_type = 'RUN' and v_count = 0 then v_errmsg := 'OK'; execute immediate v_sql; else v_errmsg := 'GLOBAL_INDEXES'; v_sql:=v_sql||' UPDATE GLOBAL INDEXES'; end if; exception when others then v_errmsg := substr(sqlerrm, 1, 60); end; ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; end if; end if; end loop; end loop; commit; exception when others then v_errmsg := substr(sqlerrm, 1, 60); ---纪录操作日志 insert into TBL_PART_MANAGE_SQL (gid, table_owner, table_name, partition_name, max_date, sql_text, create_time, order_id, resultmsg) values (v_id, v_tempowner, v_tempname, v_partname, v_partdate, v_sql || ';', sysdate, seq_sql_order.nextval, v_errmsg); commit; END STP_DEL_TEN; PROCEDURE GATHER_TABLE_INFO(v_table_owner in varchar2, v_table_name in varchar2) AS v_high_value varchar2(4000); v_isdefault varchar2(30); v_maxpart varchar2(60); v_minpart varchar2(60); v_part_time date; v_partmaxdate varchar2(12); v_part2stdate varchar2(12); v_partmindate varchar2(12); v_current_maxval varchar2(3000); v_current_minval varchar2(3000); v_current_2stval varchar2(3000); v_partcol_type varchar2(20); v_part_type varchar2(10); v_format varchar2(12); v_gi_count number; v_partcol_name varchar2(100); v_count number; v_interval varchar2(30); BEGIN delete dbamon.tbl_part_info where table_owner = v_table_owner and table_name = v_table_name; v_isdefault := 'FALSE'; v_current_maxval := null; v_current_minval := null; v_current_2stval := null; select count(*) into v_gi_count from dba_indexes t where t.table_owner = v_table_owner and t.table_name = v_table_name and t.partitioned <> 'YES'; for cur_part in (select partition_name, high_value from dba_tab_partitions where table_owner = v_table_owner and table_name = v_table_name order by partition_name) loop v_high_value := substr(cur_part.high_value, 1, 4000); if v_high_value = 'MAXVALUE' then v_isdefault := 'TRUE'; else if v_current_maxval is null then v_current_maxval := v_high_value; v_maxpart := cur_part.partition_name; elsif v_current_maxval < v_high_value then v_current_2stval := v_current_maxval; v_current_maxval := v_high_value; v_maxpart := cur_part.partition_name; elsif v_current_2stval < v_high_value then v_current_2stval := v_high_value; end if; if v_current_minval is null or v_current_minval >= v_high_value then v_current_minval := v_high_value; v_minpart := cur_part.partition_name; end if; end if; end loop; v_format := null; v_partmaxdate := null; v_partmindate := null; v_part2stdate := null; SELECT DATA_TYPE, t1.COLUMN_NAME INTO v_partcol_type, v_partcol_name fROM DBA_TAB_COLUMNS T1, DBA_PART_KEY_COLUMNS T2 WHERE T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME AND T1.TABLE_NAME = v_table_name AND T1.OWNER = v_table_owner and rownum < 2; /* select count(*) into v_count from v$instance where version like '11%';*/ v_count:=1; if v_count > 0 then execute immediate 'select interval from dba_part_tables where owner=''' || v_table_owner || ''' and table_name=''' || v_table_name || '''' into v_interval; else v_interval := null; end if; if v_partcol_type = 'DATE' or v_partcol_type like 'TIMESTAMP%' then execute immediate 'select ' || v_current_maxval || ' from dual' into v_part_time; v_partmaxdate := to_char(v_part_time, 'YYYY-MM-DD'); execute immediate 'select ' || v_current_minval || ' from dual' into v_part_time; v_partmindate := to_char(v_part_time, 'YYYY-MM-DD'); if v_current_2stval is not null then execute immediate 'select ' || v_current_2stval || ' from dual' into v_part_time; v_part2stdate := to_char(v_part_time, 'YYYY-MM-DD'); end if; elsif v_partcol_type = 'VARCHAR2' OR v_partcol_type = 'VARCHAR' or v_partcol_type = 'CHAR' then if length(v_current_maxval) = 9 and substr(v_current_maxval, 6, 1) = '-' then v_format := 'YYYY-MM'; v_partmaxdate := REPLACE(v_current_maxval, '''', NULL) || '-01'; elsif length(v_current_maxval) = 8 and instr(v_current_maxval, '-') = 0 then v_format := 'YYYYMM'; v_partmaxdate := substr(v_current_maxval, 2, 4) || '-' || substr(v_current_maxval, 6, 2) || '-01'; elsif length(v_current_maxval) = 12 and substr(v_current_maxval, 6, 1) = '-' and substr(v_current_maxval, 9, 1) = '-' then v_format := 'YYYY-MM-DD'; v_partmaxdate := replace(v_current_maxval, '''', ''); elsif length(v_current_maxval) = 10 and instr(v_current_maxval, '-') = 0 then v_format := 'YYYYMMDD'; v_partmaxdate := substr(v_current_maxval, 2, 4) || '-' || substr(v_current_maxval, 6, 2) || '-' || substr(v_current_maxval, 8, 2); end if; if length(v_current_minval) = 9 and substr(v_current_minval, 6, 1) = '-' then v_partmindate := REPLACE(v_current_minval, '''', NULL) || '-01'; elsif length(v_current_minval) = 8 and instr(v_current_minval, '-') = 0 then v_partmindate := substr(v_current_minval, 2, 4) || '-' || substr(v_current_minval, 6, 2) || '-01'; elsif length(v_current_minval) = 12 and substr(v_current_minval, 6, 1) = '-' and substr(v_current_minval, 9, 1) = '-' then v_partmindate := replace(v_current_minval, '''', ''); elsif length(v_current_minval) = 10 and instr(v_current_minval, '-') = 0 then v_partmindate := substr(v_current_minval, 2, 4) || '-' || substr(v_current_minval, 6, 2) || '-' || substr(v_current_minval, 8, 2); end if; if length(v_current_2stval) = 9 and substr(v_current_2stval, 6, 1) = '-' then v_part2stdate := REPLACE(v_current_2stval, '''', NULL) || '-01'; elsif length(v_current_2stval) = 8 and instr(v_current_2stval, '-') = 0 then v_part2stdate := substr(v_current_2stval, 2, 4) || '-' || substr(v_current_2stval, 6, 2) || '-01'; elsif length(v_current_2stval) = 12 and substr(v_current_2stval, 6, 1) = '-' and substr(v_current_2stval, 9, 1) = '-' then v_part2stdate := replace(v_current_2stval, '''', ''); elsif length(v_current_2stval) = 10 and instr(v_current_2stval, '-') = 0 then v_part2stdate := substr(v_current_2stval, 2, 4) || '-' || substr(v_current_2stval, 6, 2) || '-' || substr(v_current_2stval, 8, 2); end if; end if; v_part_type := null; if v_partmaxdate is not null and v_part2stdate is not null then if to_date(v_partmaxdate, 'yyyy-mm-dd') = add_months(TO_DATE(v_part2stdate, 'YYYY-MM-DD'), 1) THEN v_part_type := 'MONTH'; elsif to_date(v_partmaxdate, 'yyyy-mm-dd') = TO_DATE(v_part2stdate, 'YYYY-MM-DD') + 1 then v_part_type := 'DAY'; elsif to_date(v_partmaxdate, 'yyyy-mm-dd') = TO_DATE(v_part2stdate, 'YYYY-MM-DD') + 7 then v_part_type := 'WEEK'; elsif to_date(v_partmaxdate, 'yyyy-mm-dd') - TO_DATE(v_part2stdate, 'YYYY-MM-DD') in (8, 10, 11) then v_part_type := 'TEN'; elsif to_date(v_partmaxdate, 'yyyy-mm-dd') = add_months(TO_DATE(v_part2stdate, 'YYYY-MM-DD'), 1) then v_part_type := 'YEAR'; elsif to_date(v_partmaxdate, 'yyyy-mm-dd') - TO_DATE(v_part2stdate, 'YYYY-MM-DD') in (5,6) then v_part_type := 'FIVE'; else v_part_type := to_date(v_partmaxdate, 'yyyy-mm-dd') - TO_DATE(v_part2stdate, 'YYYY-MM-DD'); end if; end if; insert into dbamon.tBL_part_Info values (v_table_owner, v_table_name, v_minpart, v_maxpart, v_partmindate, v_partmaxdate, v_current_minval, v_current_maxval, v_partcol_type, sysdate, v_isdefault, v_format, v_part_type, v_gi_count, v_partcol_name, v_interval); commit; end GATHER_TABLE_INFO; PROCEDURE GATHER_PARTITION_INFO AS begin delete dbamon.tbl_part_info; commit; for rm in (select * from DBA_PART_TABLES t WHERE T.OWNER NOT IN ('SYS', 'SYSTEM') and t.partitioning_type = 'RANGE' and t.table_name not like 'BIN$%') loop GATHER_table_INFO(rm.owner, rm.table_name); end loop; end GATHER_PARTITION_INFO; PROCEDURE ADD_ALL_PART AS PROC_NAME VARCHAR2(100); ERRMSG VARCHAR2(100); BEGIN PROC_NAME := 'STP_ADD_DAY_DT'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); COMMIT; STP_ADD_DAY_DT; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_ADD_DAY_VAR'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); COMMIT; STP_ADD_DAY_VAR; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); PROC_NAME := 'STP_ADD_FIVE_DT'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); STP_ADD_FIVE_DT; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_ADD_FIVE_VAR'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); STP_ADD_FIVE_VAR; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_ADD_WEEK_DT'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); STP_ADD_WEEK_DT; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_ADD_WEEK_VAR'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); STP_ADD_WEEK_VAR; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_ADD_TEN_DT'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); STP_ADD_TEN_DT; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_ADD_TEN_VAR'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); STP_ADD_TEN_VAR; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_ADD_MON_DT'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); STP_ADD_MON_DT; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_ADD_MON_VAR'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); COMMIT; STP_ADD_MON_VAR; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; EXCEPTION WHEN OTHERS THEN ERRMSG := SUBSTR(SQLERRM, 1, 100); INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, ERRMSG, SYSDATE); END ADD_ALL_PART; PROCEDURE DEL_ALL_PART AS PROC_NAME VARCHAR2(100); ERRMSG VARCHAR2(100); BEGIN PROC_NAME := 'STP_DEL_DAY'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); COMMIT; STP_DEL_DAY; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_DEL_TEN'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); COMMIT; STP_DEL_TEN; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_DEL_WEEK'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); COMMIT; STP_DEL_WEEK; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; PROC_NAME := 'STP_DEL_MON'; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'START', SYSDATE); COMMIT; STP_DEL_MON; INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, 'END', SYSDATE); COMMIT; EXCEPTION WHEN OTHERS THEN ERRMSG := SUBSTR(SQLERRM, 1, 100); INSERT INTO TBL_PART_ERR VALUES (PROC_NAME, ERRMSG, SYSDATE); END DEL_ALL_PART; end PKG_MAINTEN_PART; create or replace procedure dbamon.config_tab_policy( v_MSG OUT VARCHAR2, ---------信息反馈。 v_table_owner in varchar2, ------分区表属主 v_table_name in varchar2, ------分区表名字 v_drop_time_units in number, ----保留分区个数 v_policy in number, ------1 加删,2不加不删,3,只加不删,4 只删不加。 v_start_time in number default 0, -----开始维护时间(24小时制,0-23) v_end_time in number default 24, ----结束维护时间 (24小时制,1-24) v_partition_type in varchar2 default null, ----分区类型,支持WEEK,MONTH,DAY,FIVE,TEN v_tab_tbs IN VARCHAR2 DEFAULT NULL, -----表所在表空间,可以为空 v_INX_TBS IN VARCHAR2 DEFAULT NULL ------索引所在表空间,可以为空, ) as l_count number; l_table_owner varchar2(40); l_table_name varchar2(40); l_partition_column_type varchar2(30); l_partition_type varchar2(100); l_format varchar2(20); l_maxpartition varchar2(100); l_gl_idx number(3); l_auto varchar2(30); l_add_time_units number(5); apperr exception; begin l_table_owner := upper(v_TABLE_OWNER); l_table_name := upper(v_TABLE_NAME); -----判断输入是否符合规范 --判断是否RANGE分区表 select count(*) into l_count from dba_part_tables t where t.owner = l_table_owner and t.table_name = l_table_name and partitioning_type = 'RANGE'; v_msg:='OK'; if l_count = 0 then v_MSG := '配置失败:表不是分区表或者不是RANGE类型分区'; raise apperr; end if; if v_start_time not in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23) or v_end_time not in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24) then v_msg := '配置失败:开始时间应该为0-23之间的整数, 结束时间应该为1-24之间整数'; raise apperr; end if; if v_policy not in (1, 2, 3, 4) then v_msg := '配置失败:policy 参数只能为1,2,3,4, 1 加删,2不加不删,3,只加不删,4 只删不加'; raise apperr; end if; if upper(v_partition_type) not in ('WEEK', 'DAY', 'TEN', 'MONTH', 'FIVE') and v_partition_type is not null then v_msg := '配置失败, 分区类型只可以为WEEK,DAY,TEN,MONTH,FIVE'; raise apperr; end if; ---开始收集信息 dbamon.pkg_mainten_part.gather_table_info(l_table_owner, l_table_name); select count(*) into l_count from dbamon.tbl_part_info where table_owner = v_table_owner and table_name = v_table_name; if l_count = 0 then v_msg := '配置失败:无法获取分区表信息,联系DBA'; raise apperr; end if; select partition_column_type, partition_type, format, autointerval, max_partition, global_indx into l_partition_column_type, l_partition_type, l_format, l_auto, l_maxpartition, l_gl_idx from dbamon.tbl_part_info where table_owner = v_table_owner and table_name = v_table_name; if l_partition_column_type like 'TIMESTAMP%' then l_partition_column_type := 'DATE'; elsif l_partition_column_type in ('CHAR', 'VARCHAR') then l_partition_column_type := 'VARCHAR2'; end if; if l_partition_column_type not in ('DATE', 'TIMESTAMP', 'VARCHAR2') then v_msg := '配置失败: 分区列数据类型只可以为DATE,TIMESTAMP,VARCHAR2,CHAR,VARCHAR 类型'; raise apperr; end if; if upper(v_partition_type) in ('WEEK', 'DAY', 'TEN', 'MONTH', 'FIVE') then l_partition_type := upper(v_partition_type); end if; if l_partition_type = 'WEEK' then l_add_time_units := 12; elsif l_partition_type = 'MONTH' then l_add_time_units := 3; elsif l_partition_type = 'DAY' then l_add_time_units := 16; elsif l_partition_type in ('FIVE', 'TEN') then l_add_time_units := 10; end if; if l_auto is not null and v_policy in (1, 3) then v_msg := '配置失败: 11g 自增长分区,不可以配置成1 加删,3,只加不删'; raise apperr; end if; delete dbamon.tbl_tab_part_config where table_owner = l_table_owner and table_name = l_table_name; insert into dbamon.tbl_tab_part_config (id, table_owner, table_name, partition_column_type, partition_pre, partition_type, drop_time_units, add_time_units, tablespace_name, index_tb_name, valid_flg, config_type, format, shour, ehour) values (dbamon.SEQ_TM_PART_MAINTEN.NEXTVAL, l_table_owner, l_table_name, l_partition_column_type, NVL(SUBSTR(l_maxpartition, 1, INSTR(l_maxpartition, '2') - 1), 'PART'), l_partition_type, v_drop_time_units, l_add_time_units, v_tab_tbs, v_INX_TBS, v_policy, 'RUN', l_format, v_start_time, v_end_time); insert into dbamon.tbl_config_log (config_time, username, oseruser, proxy_user, ip_addr, machine, table_owner, table_name, drop_time_units, policy, v_start_time, v_end_time, v_partition_type, v_tab_tbs, v_inx_tbs, config_result) values (sysdate, SYS_CONTEXT('USERENV', 'CURRENT_USER'), SYS_CONTEXT('USERENV', 'OS_USER'), SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'TERMINAL') , v_table_owner, v_table_name, v_drop_time_units, v_policy, v_start_time, v_end_time, v_partition_type, v_tab_tbs, v_inx_tbs, v_msg); commit; exception when apperr then dbms_output.put_line(v_msg); insert into dbamon.tbl_config_log (config_time, username, oseruser, proxy_user, ip_addr, machine, table_owner, table_name, drop_time_units, policy, v_start_time, v_end_time, v_partition_type, v_tab_tbs, v_inx_tbs, config_result) values (sysdate, SYS_CONTEXT('USERENV', 'CURRENT_USER'), SYS_CONTEXT('USERENV', 'OS_USER'), SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'TERMINAL') , v_table_owner, v_table_name, v_drop_time_units, v_policy, v_start_time, v_end_time, v_partition_type, v_tab_tbs, v_inx_tbs, v_msg); commit; when others then v_msg := substr(sqlerrm, 1, 50); insert into dbamon.tbl_config_log (config_time, username, oseruser, proxy_user, ip_addr, machine, table_owner, table_name, drop_time_units, policy, v_start_time, v_end_time, v_partition_type, v_tab_tbs, v_inx_tbs, config_result) values (sysdate, SYS_CONTEXT('USERENV', 'CURRENT_USER'), SYS_CONTEXT('USERENV', 'OS_USER'), SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'TERMINAL') , v_table_owner, v_table_name, v_drop_time_units, v_policy, v_start_time, v_end_time, v_partition_type, v_tab_tbs, v_inx_tbs, v_msg); commit; end; grant select on DBAMON.TBL_PART_INFO to bspdev; grant select on DBAMON.TBL_PART_INFO to PUBLIC; grant select on DBAMON.TBL_TAB_PART_CONFIG to bspdev; grant select on DBAMON.TBL_PART_MANAGE_SQL to PUBLIC; grant select on DBAMON.TBL_PART_MANAGE_SQL to bspdev; grant select on DBAMON.TBL_PART_MANAGE_SQL to PUBLIC; grant select on DBAMON.TBL_PART_ERR to PUBLIC; grant select on DBAMON.TBL_PART_ERR to bspdev; grant select on DBAMON.TBL_CONFIG_LOG to PUBLIC; grant select on DBAMON.TBL_CONFIG_LOG to bspdev; grant execute on DBAMON.config_tab_policy TO PUBLIC; grant select_catalog_role to dbamon; --dbmon declare v_job number; begin sys.dbms_job.submit( job => v_job, what=> 'pkg_mainten_part.ADD_ALL_PART(); pkg_mainten_part.del_ALL_PART();', next_date=>sysdate, interval =>'sysdate+1/24'); commit; end;