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