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日”的行)。
 
范围分区表示例:
 
假设有一个企业的销售数据比较大,可以作一个范围分区表
 
创建四个表空间,为分区表所用,应该把分区放在不同的物理设备上,性能更好,可我的测试环境是同一个物理设备

  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 );
View Code

 #索引组织表可以按范围、列表或散列进行分区。
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;

  

转载于:https://www.cnblogs.com/jiaozi-li/p/5882494.html

  • 0
    点赞
  • 0
    收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值