最近在几个Oracle数据库维护中使用到了分区表，为了特意整理下如下内容，借鉴了其他兄弟的内容，看到后有异议可以联系我。
1.分区表概述
1.1.分区表概念
分区表就是将表在物理存储层面分成多个小的片段，这些片段即称为分区，每个分区保存表的一部分数据，表的分区对上层应用是完全透明的，从应用的角度来看，表在逻辑上依然是一个整体。
每个分区都有自己的名字并可以拥有不同的存储特性，例如可以将分区保存在不同的磁盘以上分散I/O，或者分散在不同的表空间（表空间需要有相同的block size）。
向分区表插入数据时，为了判断每条数据应该被分配至哪个分区，我们通常需要选择定义一个分区键（Partition Key）。根据每条数据分区键的值或者对其运算的结果来决定数据的分区归属，分区键可以由1或多个列组成（最多16个列）.
1.2.何时使用分区表?
什么情况下应该使用分区表呢？如果遇到如下几个场景你可以考虑使用分区表：
- 表的大小超过2G
- 表中有大量的历史数据，数据存在明显的时间顺序
- 表的存储必须分散在不同的存储设备上
1.3.分区表的优点
分区表在结构和管理上比普通表更复杂，但它也有一定的优点，主要优点有以下3类：
1.3.1.提升SQL查询性能
对于SQL查询，当where条件涉及分区键时，可以快速定位需要扫描的分区，这样可以将数据的扫描范围限制在很小的范围，极大的提升查询性能。这个特性叫做分区裁剪（Partition Pruning）。
另外，在多表连接（join）时，如果在每个表在连接的键上都进行了分区，那么Oracle可以将两个大表之间的连接转换成更小的分区级连接，极大提升连接速度，这个特性叫做分区连接（Partition-wise Join）。
1.3.2.提升表可管理性
使用分区表之后，原来表级别的管理操作也被分散为至“分区级”，各个分区上独立的进行运维任务，原先一个大表上的运维任务，现在可以拆开成一系列小任务分散在不同的时间窗口执行。例如，平时备份表的操作，现在可以备份单个分区。
1.3.3.提升数据可用性
当表分区后，每个分区都具有独立性。在你操作某个分区时，不会影响其他分区数据的使用，即使某个分区因为故障不可用，也完全不会影响其他分区上运行的事务。同时分区可以存储在不同的表空间/物理介质上，分散I/O压力。
2.分区策略
Oracle 8i 包括了2种复合分区：RANGE-HASH、RANGE-LIST
Oracle 11g 提供了4种复合分区：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST
根据不同的应用场景，你可以为表选择不同的分区策略，Oracle提供的基础分区策略有：
-
范围分区（Range Partitioning）8i，
以某一个范围进行分区，支持多个字段分区。eg:时间、数值划分。 -
哈希分区（Hash Partitioning）8i，
当表中没有合适的列进行某种分区并且表数据量大，可以根据字段的hash值进行hash算法进行的散列分区，可以有效的消除io的竞争，使得每个分区数据量大致一致（数据的均匀存储）。每个分区的数据感觉上是没有规律的，是根据hash算法自动分配 -
列表分区（List Partitioning）9i，
以某列只有几个值进行分区，只支持单个字段分区。eg：地区分区，省份进行划分。
除了前面介绍的3种基础分区策略，Oracle还提供一些其他的分区策略，它们都是在基础分区策略上进行某种功能的扩充。
4. 复合(组合)分区（Composite Partitioning），9i
某表在分区之后仍然较大，可以通过分区内再建子分区的方式将分区再分区，即复合分区的方式。
- 隔分区（Interval Extension to Range Partitioning）11g，它就是以一个区间分区表为“起点”，并在定义中增加了一个规则（即间隔），使数据库知道将来如何增加分区。比如每个月增加一个分区，比如每隔一天/月要生成新的24个分区，用以存储第二天/月的数据。而在11g中这项工作可以交由Oracle自动完成了，基于Range、List、hash的Interval Partitioning分区登场，从而省去了你不断的ADD或者SPLIT新的分区。可以按天，月，季度，年：1day，1month，3month，12month
- 年：interval (numtoyminterval(1,‘year’))
- 月：interval (numtoyminterval(1,‘month’) )
- 天：interval (numtodsinterval(1,‘day’)) #注意天的间隔和年、月的值不一样
-
虚拟字段分区（Virtual Column Partitioning）11g，虚拟列功能，虚拟列的值从其他的列推导而来，Oracle只保存源数据，这个列不占存储空间。虚拟列其中一个引申功能就是虚拟列分区功能。在运行时计算，不存储在数据库中，不能更新虚拟列的值。11g增加对虚拟列的支持，这使得分区功能更加灵活。
定义一个虚拟列的语法：column name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]。
以下是虚拟字段分区的限制：
（1）只能在堆组织表（普递表）上创建虚拟列，不能在素引组织表、外部表、临时表上创建虚拟列
（2）虚拟列不能是LOB或者RAW类型
（3）虚拟列的值并是不真实存在的，只有用到时，才根据表达式计算出虚拟列的值，磁盘上并不存放
（4）可把虚拟列当做分区关键字建立分区表，这是11g的另一新特性–虚拟列分区
（5）可在虚拟列上创建索引
（6）如果在已经创建的表中增加虚拟列时，若没有指定虚拟列的字段类型，ORACLE会根据generated always as后面的表达式计算的结果自动设置该字段的类型
（7）也视列的售由ORACLE根据表达式自动计算得出，不可以做UPDATE和INSERT操作，可以对虚拟列做DELETE操作
（8）表达式中的所有列必须在同一张表；表达式不能使用其他虚拟列 -
系统分区
11g 以前的分区表，需要指定一个或多个分区字段，并根据这个分区字段的值，按照一定的算法（RANGE、HASH和LIST）来决定一条记录属于那个分区。从11g开始，Oracle允许用户不指定分区列，完全根据程序来控制数据存储在那个分区中。
在以前确定了分区列和分区方式，那么一条数据属于哪个分区也就被确定下来。 而对于系统分区而言，分区是分区，数据是数据，二者没有对应的关系，数据可以被放在任意一个分区中，这不是由数据本身决定的，而是应用程序在插入时确定的。
2.1.范围分区（Range Partition）
范围分区根据预先定义的范围来划分分区，范围分区最适合管理类似且有明显顺序的数据，根据数据的顺序可以很容易划定分区范围。范围分区最典型的应用场景就是按时间对数据进行分区，所以其经常使用时间类型的分区键。
范围分区表是通过 create table 语句的 partition by range 子句来创建的，分区的范围通过 values less than 子句指定，其指定的是分区的上限（不包含），所有大于等于指定值的数据被分配至下一个分区，除了第一个分区，每个分区的下限即前一个分区的上限：
create table members (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
(
partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2023-03-01', 'yyyy-mm-dd')),
partition pmax values less than (maxvalue)
);
上面的例子中定义了3个分区：
- 所有create_time小于’2023-02-01’的数据（不包含）被分配在分区p1中。
- 所有create_time小于’2023-03-01’的数据（不包含）被分配在p2中。
- 所有create_time大于等于’2023-03-01’的数据被分配在pmax中，如果没有这个分区，那么插入大于等于’2023-03-01’的数据时，会因为没有合适的存储分区而报错。
你也可以在定义分区时指定存储特性，例如将分区分散到不同的表空间（表空间可以放到不同的物理磁盘上）：
create tablespace tbs1 datafile '/u01/app/oracle/oradata/seven/tbs1.dbf' size 100m autoextend on next 20m maxsize 30g;
create tablespace tbs2 datafile '/u01/app/oracle/oradata/seven/tbs2.dbf' size 100m autoextend on next 20m maxsize 30g;
create tablespace tbs3 datafile '/u01/app/oracle/oradata/seven/tbs3.dbf' size 100m autoextend on next 20m maxsize 30g;
create table members (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
(
partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')) tablespace tbs1,
partition p2 values less than (to_date('2023-03-01', 'yyyy-mm-dd')) tablespace tbs2,
partition pmax values less than (maxvalue) tablespace tbs3
);
2.1.1.间隔分区（Interval partition）
间隔分区是范围分区的一个扩展，它也是通过范围来划分分区，唯一的区别是：间隔分区可以在相应分区数据插入时自动创建分区，省去了普通范围分区手动创建分区的操作。
如果不是需要创建不规则的范围分区，那么更推荐使用间隔分区来替代范围分区，你只需要指定一个分区间隔及初始分区，后续的分区创建将由Oracle自动完成。
间隔分区表的创建由在普通范围分区定义上新增一个interval子句创建：
create table inv_part (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH')) -- 指定分区间隔
(
partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd'))
);
上面的例子指定在初始分区p1的基础上，每隔1个月创建一个分区。
通过视图user_tab_partitions可以看到目前只有1个分区p1:
SQL> select table_name, partition_name from user_tab_partitions where table_name='INV_PART';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INV_PART P1
SQL>
我们在初始分区的上限之上插入一条数据：
SQL> insert into inv_part values(1, 'Vincent', date '2023-02-02');
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name, partition_name from user_tab_partitions where table_name='INV_PART';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INV_PART P1
INV_PART SYS_P41
SQL>
在现有分区之上插入数据时，Oracle自动为我们创建了1个对应的分区SYS_P327。
对于间隔分区，你也可以通过 store in 子句指定多个表空间，Oracle将以循环的方式在各个表空间中创建分区。
create table multi_tbs (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH')) store in (tbs1, tbs2, tbs3)
(
partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')) tablespace tbs1
);
查询初始分区的所属表空间：
SQL> select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='MULTI_TBS';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
MULTI_TBS P1 TBS1
SQL>
插入两条数据，触发自动创建新的分区：
SQL> insert into multi_tbs values(1, 'Vincent', date '2023-02-02');
1 row created.
SQL> insert into multi_tbs values(2, 'Victor', date '2023-03-02');
1 row created.
SQL> commit;
Commit complete.
SQL> SQL> select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='MULTI_TBS';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
MULTI_TBS P1 TBS1
MULTI_TBS SYS_P42 TBS2
MULTI_TBS SYS_P43 TBS3
SQL>
可以看到Oracle自动以循环的方式在3个表空间中创建了分区。
2.2.哈希分区（Hash Partition）
哈希分区是对指定的分区键（Partition Key）运行哈希算法来决定数据存储在哪个分区。哈希分区会随机的将数据分配到各个分区中，并尽量平均，保证各个分区的大小差不多一致。
由于数据是随机分布，所以哈希分区并不适合管理有明显时间顺序的历史数据。它更适合需要将数据平均的分布到各个不同存储设备上的场景。同时在选用哈希分区时建议满足下列条件：
- 选取分区键时尽量选取唯一列（Unique）或列中有大量唯一值（Almost Unique）的列。
- 创建哈希分区时，分区的数量尽量是2的幂，例如2,4,8,16等。
哈希分区表是通过 create table 语句的 partition by hash 子句来创建的，创建时你可以显式的指定每个分区名称，所属表空间。
create table hash_part1 (
id number,
name varchar2(32))
partition by hash(id)
(
partition p1 tablespace tbs1,
partition p2 tablespace tbs2
);
也可以仅指定哈希分区的数量，此时Oracle会自动为每个分区生成名字：
create table hash_part2 (
id number,
name varchar2(32))
partition by hash(id)
partitions 2; -- 指定哈希分区数量，不用指定分区名
你也可以用 store in 子句让分区以循环的方式建立在各个表空间中：
create table hash_part3 (
id number,
name varchar2(32))
partition by hash(id)
partitions 4
store in (tbs1, tbs2, tbs3);
2.3.列表分区（List partition）
列表分区是由你为每个分区指定一系列的离散值（列表），当分区键等于特定的离散值时，数据会被放到相应的分区。列表分区可以让你自定义数据的组织方式，例如按照地域来分类数据。
列表分区表是通过 create table 语句的 partition by list 子句来创建的，创建时你需要为每个分区指定一个列表（离散值）。
create table list_part1 (
id number,
name varchar2(32),
city varchar2(32))
partition by list(city)
(
partition p_jiangsu values ('NanJing', 'SuZhou'),
partition p_zhejiang values('HangZhou', 'JiaXing')
);
你可以选择性的增加一个包含 default 值的分区，这样所有没有预先定义的分区键值都会放入该分区，否则会报错：
create table list_part2 (
id number,
name varchar2(32),
city varchar2(32))
partition by list(city)
(
partition p_jiangsu values ('NanJing', 'SuZhou'),
partition p_zhejiang values('HangZhou', 'JiaXing'),
partition p_def values (default)
);
列表分区建立完成后，你可以很方便的利用 alter table … modify partition … add/drop values ( … ) 来修改列表分区的枚举值：
alter table list_part2 modify partition p_jiangsu add values('YangZhou');
alter table list_part2 modify partition p_jiangsu drop values('YangZhou');
如果列表分区是子分区，只需要将 modify partition 替换为 modify subpartition 即可。
2.4.复合分区（Composite Partition）
复合分局，顾名思义，就是将多种分区策略结合起来使用，在基础分区的策略上，对每个分区再一次应用分区策略。例如，在基础的范围分区基础上，还可以对每个分区再次应用范围分区，即每个分区又被划分为若干个子分区。类似于中国可以划分为很多省（分区），每个省又可以划分为很多市（子分区）。
在使用复合分区时，3种基础分区策略可以随意组合，例如，使用范围分区作为基础分区，其子分区可以使用范围、哈希、列表分区策略，即：
- 范围-范围分区
- 范围-哈希分区
- 范围-列表分区
其他两种分区类型同理，因此复合分区共有3*3=9种方案。
子分区是通过原来分区策略上通过新增 subpartition 子句来定义的，下面我们以范围分区（间隔分区）为基础分区，演示三种子分区的创建方式
comp_part1的采用范围-哈希分区策略：
create table comp_part1 (
id number,
name varchar2(32),
create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH')) -- 范围分区（间隔分区）
subpartition by hash(id) subpartitions 4 -- 子分区采用哈希分区，每个范围分区再分为4个哈希分区
(
partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd'))
);
comp_part2的采用范围-范围分区策略：
create table comp_part2 (
id number,
name varchar2(32),
age number,
create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH')) -- 范围分区（间隔分区）
subpartition by range(age) -- 子分区通过年龄进行划分
subpartition template -- 定义子分区模板
(
subpartition p_children values less than (12),
subpartition p_adolescent values less than (30),
subpartition p_adult values less than (60),
subpartition p_elder values less than (100)
)
(
partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd'))
);
comp_part3的采用范围-列表分区策略：
create table comp_part3 (
id number,
name varchar2(32),
sex varchar2 (32),
create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH')) -- 范围分区（间隔分区）
subpartition by list(sex) -- 子分区通过性别进行划分
subpartition template
(
subpartition p_man values ('male'),
subpartition p_women values ('female')
)
(
partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd'))
);
2.5.引用分区（Reference Partition）
引用分区是一种基于主-外键引用关系的分区策略，如果两张表上定义了外键引用，即两张表存在父-子关系（Parent-Child Realtionship），那么基于这种主键-外键引用关系，可以使子表继承主表的分区策略。
引用分区特别适合在需要自动维护子表，或者两表频繁连接查询的场景，因为他们的分区策略是相同的，两表连接通常会被转换为分区连接（partition-wise join），大大缩小连接的结果集。
引用分区是通过partition by reference创建的。例如，下面两张表parent_table和child_table 定义了引用分区：
create table parent_table (
id number primary key,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH'))
(
partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd'))
);
创建子表时，如果要采用引用分区，则定义外键的列要非空，子表会通过外键继承主表的分区方案。
create table child_table (
id number primary key,
parent_id number not null, -- 定义外键的列要非空
sex varchar2(32),
constraint parent_id_fk foreign key (parent_id) references parent_table(id)) -- 定义外键约束
partition by reference (parent_id_fk);
验证引用分区的继承，通过视图 user_tab_partitions 可以看到，初始child_table也继承了主表初始分区
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='PARENT_TABLE';
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='CHILD_TABLE';
我们往 parent_table 中插入一条数据，触发间隔分区的自动新建分区特性：
insert into parent_table values(1, 'Vincent', date '2023-02-02');
commit;
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='PARENT_TABLE';
下面我们往child_table中插入一条数据：
insert into child_table values(1, 1,'male');
commit;
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='CHILD_TABLE';
可以看到，在子表插入数据的时候，对应的分区也自动创建了出来（且分区编号都相同）。
当我们在主表上删除分区时，对应的子表上的分区也被自动删除了：
alter table parent_table drop partition SYS_P391;
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='CHILD_TABLE';
2.6.虚拟列分区（Virtual Column-based Partition）
虚拟列分区即分区键可以定义在虚拟列上，虚拟列分区使分区键可以定义在一个表达式上，这个表达式会被保存为元数据，而列并不实际存在于数据库中。虚拟列分区可以与任何分区策略结合使用。
下面示例中，表 virtual_part 上通过salary和bonus定义了一个虚拟列income，然后将income作为分区键：
create table virtual_part(
id number primary key,
name varchar2(32),
salary number,
bonus number,
income as (salary + bonus))
partition by range(income)
(
partition p1 values less than (1000),
partition p2 values less than (5000)
);
2.7.系统分区（System Partition）
前介绍的分区策略都是由数据库来决定数据放在哪个分区，分区对应用都是透明的。
系统分区可以仅建立一个分区表，但不指定分区策略，因此它没有分区键和分区规则。
系统分区对上层应用不是透明的，应用往系统分区插入数据时，SQL必须显式的指定分区名，否则会报错。
系统分区通过 create table 的 partition by system 子句创建，后续只需要定义分区，不需要分区键：
create table system_part (
id number primary key,
name varchar2(32))
partition by system(
partition p1,
partition p2
);
系统分区的数据存储完全由应用决定，因此在插入数据时，必须显示指定数据保存的分区：
insert into system_part values (1, 'Vincent');
仅通过表名插入数据时报错：系统分区还需要提供分区扩展名
insert into system_part partition(p1) values (1, 'Vincent');
插入时显式指定分区，插入成功。
2.8.相关视图：
# 记录分区的表的信息
user_part_tables
dba_part_tables
all_part_tables
- partition_type：表的分区类型
- subpartitoning_type：子分区类型
- partition_count：表的分区数量
- def_subpartition_count：每个分区子分区数量
- partition_key_count：表中有分区的字段数量
- status：表分区是否生效
- def_tablespace_name：表的默认表空间，不是分区默认的表空间
# 记录分区的表的信息
user_tab_partitions
dba_tab_partitions
all_tab_partitions
- subpartition_count：每个分区子分区数量
- high_value：这分区保存最高的范围
- tablespace_name：这个分区默认的表空间
# 记录表的子分区信息
user_tab_subpartitions
dba_tab_subpartitions
all_tab_subpartitions
- subpartition_name：子分区名
- subpartiton_position：子分区编号，为分区的下的子分区数量
# 查询分区的索引信息
user_part_indexes
dba_part_indexes
all_part_indexes
- locality：全局索引global；本地索引local
- alignment：前缀索引prefixed；非前缀索引non-prefixed
# 记录索引分区的信息
user_ind_partitions
dba_ind_partitions
all_ind_partitions
status：usable有效，unusable无效。而普通索引通过dba_indexes查看。如果分区有子分区，显示N/A，需要通过dba_ind_subpartitions查看分区下面每个子分区的状态
# 记录索引子分区的信息
user
dba
all_ind_subpartitions;
status：usable有效，unusable无效。
3.分区索引的作用与分类
与分区表类似（partitioned table），分区索引（partitioned index）也能够提高系统的可管理性，可用性，可伸缩性，及系统性能。对于分区索引，需要区分创建的是全局索引、还是本地索引。
分区索引既可以与分区表相对独立（全局索引（global index）），也可以采用与分区表相同的分区方式（本地索引（local index））。
普通表可以建分区索引，分区表可以建非分区的 global index，也可以建range或hash分区的global index，也可以建基于分区的local index。使用如下：
什么情况下建什么分区索引?
（1）当有表的分区或子分区操作维护的时候，本地索引提供更好的可用性；（如合并分区时，只有合并的表分区的分区索引失效,而非分区索引以及全局分区索引在合并,删除表分区后全部失效；可以单独重建本地索引；若只有一个分区需要维护，则只有一个本地索引受影响。
（2）本地索引可以提高性能，因为数据大，很多分区必然会被并行的查询。
（3）对于历史表，本地索引是必须，这样在有规律的 drop 分区的时候，比较方便。
（4）本地索引多应用于数据仓库环境中。
（5）在非分区字段上建立 unique 索引只能建全局索引。
（6）全局索引不支持位图索引，全局分区索引全局分区索引只能是 B 树索引。
（7）全局索引多应用于 oltp 系统中。
（8）全局分区索引只按范围或者散列 hash 分区，hash 分区是 10g 以后才支持。
（9）表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用 b做全局分区索引。
（10）本地分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然。一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，本地分区索引具有更高的可用性。
分区索引的类型：
- 本地索引(local index)：自动维护索引分区。其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。
本地索引又可以分为有前缀(prefix)的索引和无前缀(nonprefix)的索引，而全局索引目前只支持有前缀的索引。
- 前缀索引：本地索引的索引列以分区键开头（分区用什么列分的，索引就用什么列创建）
- 非前缀索引：本地索引的列不是以分区键开头，或者不包含分区键列（分区用什么列分的，索引不用什么列创建）
- 全局索引的索引列必须是以索引分区键作为其前几列，全局索引只有前缀索引。
- 全局索引(global index)：更多的手工维护索引分区。即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。
相关视图：
# 记录分区的表的信息
user_part_tables
dba_part_tables
all_part_tables
- partition_type：表的分区类型
- subpartitoning_type：子分区类型
- partition_count：表的分区数量
- def_subpartition_count：每个分区子分区数量
- partition_key_count：表中有分区的字段数量
- status：表分区是否生效
- def_tablespace_name：表的默认表空间，不是分区默认的表空间
# 记录表的分区的信息
user_tab_partitions
dba_tab_partitions
all_tab_partitions
- subpartition_count：每个分区子分区数量
- high_value：这分区保存最高的范围
- tablespace_name：这个分区默认的表空间
# 记录表的子分区信息
user_tab_subpartitions
dba_tab_subpartitions
all_tab_subpartitions
- subpartition_name：子分区名
- subpartiton_position：子分区编号，为分区的下的子分区数量
# 查询分区的索引信息
user_part_indexes
dba_part_indexes
all_part_indexes
- locality：全局索引global；本地索引local
- alignment：前缀索引prefixed；非前缀索引non-prefixed
# 记录索引分区的信息
user_ind_partitions
dba_ind_partitions
all_ind_partitions
status列：usable有效，unusable无效。而普通索引通过dba_indexes查看。如果分区有子分区，显示N/A，需要通过dba_ind_subpartitions查看分区下面每个子分区的状态
# 记录索引子分区的信息
user_ind_subpartitions
dba_ind_subpartitions
all_ind_subpartitions
status列：usable有效，unusable无效。
3.1.本地索引
local index 自动维护索引分区。进行add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区不失效。如果不加local参数那么就是普通表的单列，是不会分区的，所以特别注意是分区表时一定要加上local
SQL> create index index_name on table_name (column_name1，column_name2) local
[online] tablespace tablespace _name
nologging
parallel x;
online：在线与非在线的区别：非在线锁表，优先创建索引，此时DML都被阻塞，所以快；相反，在线锁的是行而非表，通过临时表进行索引的创建，所以不会影响DML操作，但副作用就是慢。如果在生产环境操作，不停服务的话，势必导致创建索引期间仍有DML操作进来。另外如果是大表，那么采用非在线而导致锁表所带来的影响可能会很大。一句话，生产环境不停服的脚本操作，建议使用online。
案例一：本地索引之前缀分区索引（创建的本地索引引用分区列，那么索引自动分区）
1）给分区表table_r的分区字段joindate创建本地索引
SQL> create index idx_table_r_joindate on table_r(joindate) local;
2）查询索引分区信息
SQL> select * from dba_part_indexes where table_name='TABLE_R'; ---查询分区的索引信息
SQL> select * from dba_ind_partitions where index_name='IDX_TABLE_R_JOINDATE'; ---记录索引分区的信息
3）查看索引DDL建表语句
SQL> select dbms_metadata.get_ddl('INDEX','I_TABLE_R1_JOINDATE') from dual;
4）验证统计信息是否使用分区索引
SQL> select * from TABLE_R where joindate=to_date('2010/5/24 13:44:26','yyyy-mm-dd hh24:mi:ss');
案例二：本地索引之非前缀索引（创建的本地索引不引用分区列，索引还是自动分区）
1）给分区表table_r的非分区字段cardid创建本地索引
SQL> create index idx_table_r_cardid on table_r(cardid) local;
2)查询索引分区信息
SQL> select * from dba_part_indexes where table_name='TABLE_R'; ---查询分区的索引信息
SQL> select * from dba_ind_partitions where index_name='IDX_TABLE_R_CARDID'; ---记录索引分区的信息
3）查看索引DDL建表语句
SQL> select dbms_metadata.get_ddl('INDEX','I_TABLE_R1_CARDID') from dual;
4）验证统计信息是否使用分区索引
SQL> select * from table_r1 where cardid=10060248242;
3.2.全局索引
global index手工维护索引分区。进行add/drop/split/truncate表的分区时，全局索引不会维护其索引分区会导致失效。如果不加global参数那么就是普通表的单列
SQL> create index index_name on table_name (column_name1，column_name2) global
[online] tablespace tablespace _name
nologging
parallel x;
online：在线与非在线的区别：非在线锁表，优先创建索引，此时DML都被阻塞，所以快；相反，在线锁的是行而非表，通过临时表进行索引的创建，所以不会影响DML操作，但副作用就是慢。如果在生产环境操作，不停服务的话，势必导致创建索引期间仍有DML操作进来。另外如果是大表，那么采用非在线而导致锁表所带来的影响可能会很大。一句话，生产环境不停服的脚本操作，建议使用online。
案例一：全局索引之前缀range分区索引（全局索引不会维护其索引分区会导致失效）
1）分区表创建全局索引之前需要查看表的分区，并一一引用
2）创建全局索引
SQL> create index idx_table_r_id on table_r(id) global
partition by range(id)
(
partition P10000 values less than (20010000),
partition P20000 values less than (20020000),
partition P30000 values less than (20030000),
partition P40000 values less than (20040000),
partition P50000 values less than (20050000),
partition P_MAX values less than (MAXVALUE)
);
3）查询索引分区信息
SQL> select * from dba_part_indexes where table_name='TABLE_R'; ---查询分区的索引信息
SQL> select * from dba_ind_partitions where index_name='IDX_TABLE_R_ID'; ---记录索引分区的信息
4）查看索引DDL建表语句
SQL> select dbms_metadata.get_ddl('INDEX','I_TABLE_R2_ID') from dual;
5）验证统计信息是否使用分区索引
SQL> select * from table_r2 where id=20000039;
案例二：全局索引之前缀hash分区索引（全局索引不会维护其索引分区会导致失效）
1）创建全局索引。创建range全局索引时需要查看表的分区，因为需要一一引用对应的分区名；创建hash全局索引不需要引用对应的分区名，自定义命名即可，因为hash全局索引使用hash算法自动分配，使得每个分区数据量大致一致（数据的均匀存储）。
SQL> create index idx_table_r2_id on table_r2(id) global
partition by hash(id)
(
partition p_1,
partition p_2,
partition p_3,
partition p_4
);
2）查询索引分区信息
SQL> select * from dba_part_indexes where table_name='TABLE_R2'; ---查询分区的索引信息。索引类型
SQL> select * from dba_ind_partitions where index_name='IDX_TABLE_R2_ID'; ---记录索引分区的信息
3）查看索引DDL建表语句
SQL> select dbms_metadata.get_ddl('INDEX','IDX_TABLE_R2_ID') from dual
4）验证统计信息是否使用分区索引
SQL> select * from table_r2 where id=20000039;
3.3.分区索引的重建和管理
分区索引使用的注意事项：
（1）OLTP（事务处理）系统中，全局索引和本地前缀索引因能减少分区探测的次数从而提供更好的性能。
（2）OLTP（事务处理）系统中，当有表分区或子分区维护操作的时候，本地索引提供更好有效性；非前缀分区索引对于历史数据库非常有用。
（3）DSS（数据仓库）系统中，本地非前缀索引能提高性能，原因是依据范围的并发查询(如BETWEEN)能够并发的扫描到到更多索引分区。历史表的索引尽可能采用本地索引，因此历史表上会有较规律的分宮删除操作，而采用本地索引能降低这类操作的影响。多列上的唯一索引必须是全局的。
重建索引相关语句：
方式一：alter的方式重建全局索引/本地索引：
SQL> alter index index_name rebuild subpartition/partition partition_name [online] [nologging] [tablespace tablespace_name] [parallel x];
rebuild online：在线重建索引
方式二：修改表的方式重建索引：
SQL> alter table table_name modify subpartition/partition partition_name rebuild unusable local indexes;
案例一：table_r2为范围分区，使用全局hash索引。如果删除一个表分区，没有加update index会导致索引失效，所以进行重建（建议加上update indexes就会自动维护索引）
1）table_r2为范围分区
SQL> select * from dba_part_tables where table_name='TABLE_R2';
2）table_r2分了6个区
SQL> select * from DBA_tab_partitions where table_name='TABLE_R2';
3）table_r2表的索引为hash全局索引
SQL> select * from dba_part_indexes where table_name='TABLE_R2';
4）索引分了4个区
SQL> select * from dba_ind_partitions where index_name='I_TABLE_R2_ID';
5）业务需要删除一个表分区
SQL> alter table table_r2 drop partition p40000;
6）导致所有的分区表全局索引全部失效
SQL> select * from dba_ind_partitions where index_name='I_TABLE_R2_ID';
7）对分区表的索引进行重建(分区索引必须对每个分区重建，不能作为整体重建)
alter index I_TABLE_R2_ID rebuild partition P_1 online ;
alter index I_TABLE_R2_ID rebuild partition P_2 online ;
alter index I_TABLE_R2_ID rebuild partition P_3 online ;
alter index I_TABLE_R2_ID rebuild partition P_4 online ;
分区索引重新生效
SQL> select * from dba_ind_partitions where index_name='I_TABLE_R2_ID';
4.分区表运维（Partition Maintenance）
4.1.新增分区
手动新增分区，不同的分区类型操作稍微有些不同。注意间隔分区和引用分区的分区都是自动创建的，因此它们无法手动新增分区。
范围分区可以手动新增分区，注意仅可以在范围分区最大范围的上面新增分区，如果已经定义了最大值分区（maxvalue）或者想要在中间插入一个分区，则只可以使用分裂分区来完成。
alter table members drop partition pmax;
由于建表时定义了p_max，要先删除才能演示，实际应用中要注意p_max分区是否有数据
alter table members add partition p3 values less than (to_date('2023-04-01', 'yyyy-mm-dd'));
哈希分区手动新增分区, 你可以指定分区名，也可以不指定分区名，数据会重新在各分区中进行分布，可能需要一些时间：
alter table hash_part1 add partition p3 tablespace tbs3;
alter table hash_part2 add partition tablespace tbs3;
列表分区直接新增一个分区定义：
alter table list_part1 add partition p_anhui values('HeFei', 'ChuZhou');
4.2.删除分区
使用 alter table … drop partition 可以删除指定的分区，对于范围分区、间隔分区，列表分区，直接指定要删除的分区名即可，间隔分区虽然无法显式新增分区，但是可以显式删除：
alter table members drop partition p3;
引用分区无法显式删除，因为它的分区策略继承自父表，只有当父表删除分区时，子表上的引用分区才会级联删除（前面演示过）。
对于哈希分区，我们无法直接删除分区。如果要减少分区的数量，必须采用一个叫 coalesce partition （融合分区）的操作，下面的示例会将哈希分区的数量减少1个。这个操作虽然减少了一个分区，但是并不会丢失数据，数据会在剩下的分区中重新分布。
alter table hash_part1 coalesce partition;
4.3.置换分区
置换分区指可以用一个非分区表与分区表的某个分区/子分区进行置换（数据段交换）。利用置换分区可以快速将数据载入或者移出分区表，且置换分区操作没有类型限制，所有的分区策略都可以使用此特性。
要置换分区，首先你要创建一个与分区表结构一样的非分区表，我们以前面的范围分区表members作为示例：
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='MEMBERS';
创建一个与members结构一样的表，并插入几条测试数据，我们计划置换members分区p2，但是第二条数据我们插入一条违反该分区规则（create_time <‘2023-03-01’）的数据。
create table mem_ext (
id number,
name varchar2(32),
create_time date);
insert into mem_ext values (3, 'exchanged_data', date '2023-02-01');
insert into mem_ext values (4, 'exchanged_data', date '2023-03-01');
commit;
如果是12cR2以上的版本，你还可以用 create table … for exchange with table … 语句来快速创建一个与分区表完全匹配的非分区表：
create table mem_ext for exchange with table members;
将mem_ext表与members表的p2分区进行置换：
# 由于预先插入违反分区规则的数据导致报错
alter table members exchange partition p2 with table mem_ext;
alter table members exchange partition p2 with table mem_ext without validation;
如果置换的分区中有不符合分区规则的数据（第二条），可以用 without validation 子句跳过数据验证（仅更新数据字典）。
当交换分区或者更新分区键时，可能会导致数据的分区归属变化（下面第一个报错），这时候Oracle就需要在不同分区移动数据，我们可以在建表的时候开启行移动（row movement），或者手动打开，这样当分区键被更新且需要移动分区时，Oracle会自动将数据移动到正确的分区：
update members set create_time='2023-03-03 00:00:00' where id=3;
更新分区键会导致切换分区，报错
alter table members enable row movement;
update members set create_time='2023-03-03 00:00:00' where id=3;
分区键更新后，数据会被移动到正确的分区
4.4.合并分区
利用 alter table 的 merge partition/subpartion 子句，你可以将两个分区合并成一个。合并分区仅适用于范围、间隔、列表分区类型; 哈希和引用分区不适用。
对于范围分区，你只能将相邻两个的分区进行合并，且只能合并到边界高的分区，例如下面，由于p2分区上限更高，只能将分区p1合并至p2，不能将p2合并至p1:
alter table members merge partitions p1, p2 into partition p2 update indexes;
合并分区时，建议带上update indexes来更新索引，或合并后重建。
间隔分区限制同范围分区，你也只能合并相邻的分区，而且合并还回会导致所有低于合并分区的间隔分区都转换为范围分区，合并分区的上沿就是范围分区和间隔分区的分界点，以下面的interval_part表示例，每月1个分区，我们插入数据让3、7、8，11月的间隔分区创建出来
create table interval_part (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH'))
(
partition p1 values less than (to_date('2023-01-01', 'yyyy-mm-dd'))
);
insert into interval_part values(1,'abc', date '2023-03-10');
insert into interval_part values(1,'abc', date '2023-07-10');
insert into interval_part values(1,'abc', date '2023-08-10');
insert into interval_part values(1,'abc', date '2023-11-10');
commit;
可以看到我们插入数据触发的新建分区属于间隔分区（interval=YES）：
select table_name, partition_name, interval from user_tab_partitions where table_name='INTERVAL_PART';
下面将相邻的7，8月分区进行合并（SYS_448, SYS_P449）:
alter table interval_part merge partitions for (to_date('2023-07-10', 'yyyy-mm-dd')), for(to_date('2023-08-10', 'yyyy-mm-dd')) ;
select table_name, partition_name, interval from user_tab_partitions where table_name='INTERVAL_PART';
可以看到7，8月分区SYS_448, SYS_P449消失了，生成了一个新的分区SYS_P451，原先边界范围在合并分区之下的3月分区（SYS_P447）也被转换成了范围分区（interval=NO），而合并分区之上11月的分区（SYS_P450）依然是间隔分区(interval=YES)。
列表分区由于分区之间没有顺序，因此你可以合并任意两个分区，合并后的分区包含两个分区的所有数据，以下面list_part表举例：
create table list_part (
id number,
name varchar2(32))
partition by list(name)
(
partition p1 values ('a', 'b'),
partition p2 values('c', 'd'),
partition p3 values('e', 'f')
);
我们将不相邻的分区p1,p3合并成了p_merged：
select table_name, partition_name from user_tab_partitions where table_name='LIST_PART';
alter table list_part merge partitions p1,p3 into partition p_merged;
select table_name, partition_name from user_tab_partitions where table_name='LIST_PART';
4.5.分裂分区
当某个分区过大时，你可能想要将它分裂成2个分区。分裂分区是合并分区的逆向操作，和合并分区的限制一样，分裂分区也仅适用于范围、间隔、列表分区类型;哈希和引用分区不适用。
分裂操作会重新将数据在2个分区中进行分布，现在以上面一节合并的分区为示例，再将它们分开。
分裂范围分区，我们需要指定一个分裂点（包含在分区内），整个分区将以这个分裂点为边界拆分为2个分区，分裂点会作为第一个分区的上限（不包含），下面示例将范围分区p2拆分为p1和p2：
alter table members split partition p2 at (to_date('2023-02-01', 'yyyy-mm-dd')) into (partition p1, partition p2) update indexes;
分裂间隔分区和分裂范围分区类似，我们也需要指定一个分裂点。且分裂间隔分区和和合并间隔分区一样，也会导致所有低于被分裂分区上限的间隔分区都转换为范围分区，被分裂分区的上限即范围分区和间隔分区的分界点。我们将上面示例的最后一个间隔分区 - 11月的分区（SYS_P450）从11月15号分裂为2个分区：
select table_name, partition_name, interval from user_tab_partitions where table_name='INTERVAL_PART';
alter table interval_part split partition for(date '2023-11-10') at (date '2023-11-15') update indexes;
select table_name, partition_name, interval from user_tab_partitions where table_name='INTERVAL_PART';
分区SYS_P450分裂成了SYS_P467和SYS_P468，同时低于原分区上限的所有分区都会被转换为范围分区（interval=NO）。
分裂列表分区，你需要指定需要分裂出去的值，这些指定的值会分配到第一个分区，原分区剩余的值会分配到第二个分区。
在上面一节列表分区合并操作中，我们将p1和p3合并成了p_merged，现在再将它们分开：
select table_name, partition_name, high_value from user_tab_partitions where table_name='LIST_PART';
alter table list_part split partition p_merged values('a', 'b') into
(
partition p1,
partition p3
);
select table_name, partition_name, high_value from user_tab_partitions where table_name='LIST_PART';
观察分裂前后的分区枚举值，我们指定’a’, 'b’被分裂出去，那么它们将被放入p1，剩余的值会被放入p3。
4.6.移动分区
移动分区可以让你随意将某个分区移动其他表空间，这种情况通常用在需要将分区迁移到另一个存储设备上。同时也可以顺便对分区进行一些其他操作，例如压缩。所有类型的分区策略都支持移动分区。
要移动分区至其他表空间，使用alter table的 move partition 子句，：
alter table interval_part move partition p1 tablespace tbs1 update indexes compress;
移动分区实际是在新目的地新建一个分区，并将原分区删除（drop），即使目的地是相同的表空间也是如此。
4.7.重命名分区
你可以用 alter table … rename partition … to … 来给指定的分区重命名，重命名没有限制，所有分区策略都可以使用：
alter table interval_part rename partition sys_p447 to p2;
4.8.截断分区
需要彻底清除某个分区数据时，你可以用 alter table … truncate partition … 来彻底清除该分区的数据（所有分区策略都适用）。
alter table interval_part truncate partition p2 update indexes;
5.普通表迁移到分区表
5.1.expdp/impdp
通过 expdp/impdp 将普通表数据迁移到分区表（非在线方式，需要停止相关表的业务）
10g引入了最新的数据泵(Data Dump)技术。它可以通过使用并行，在效率上要比exp/imp要高。可以跨平台迁移（能够在不同硬件平台上的oracle之间传递数据），支持全库、用户、表级的备份与恢复。
step 1.查看表数据和索引
SQL> select count(*) from table_m5;
SQL> select * from dba_indexes where table_name='TABLE_M5'; ---有索引
step 2.通过expdp导出表
$ expdp \"/ as sysdba\" directory=bk dumpfile=expdp_orcl_table_%U.dmp tables=username.table_m5 parallel=2 cluster=n
step 3.重命名原表
SQL> alter table table_m5 rename to table_m5_old; ---重命名原表
step 4.将表结构、索引、约束的DDL语句全部复制：
SQL> select dbms_metadata.get_ddl('TABLE','TABLE_M5_OLD','USERNAME') from dual;
SQL> select TABLE_NAME,TABLE_TYPE,INDEX_NAME from dba_indexes where table_name='TABLE_M5_OLD';
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where table_name='TABLE_M5_OLD';
SQL> select dbms_metadata.get_ddl('INDEX','索引名','USERNAME') from dual;
SQL> select dbms_metadata.get_ddl('CONSTRAINT','约束名','USERNAME') from dual;
step 5.创建hash分区表
SQL>
CREATE TABLE "USERNAME"."TABLE_M5"
("ID" NUMBER(20,0) NOT NULL ENABLE,
"NAME" VARCHAR2(20) NOT NULL ENABLE,
"AGE" NUMBER(10,0) NOT NULL ENABLE,
"SEX" VARCHAR2(10) NOT NULL ENABLE,
"CARDID" NUMBER(30,0) NOT NULL ENABLE,
"JOINDATE" DATE NOT NULL ENABLE,
"REGION" VARCHAR2(12) NOT NULL ENABLE,
"TEL" VARCHAR2(12) NOT NULL ENABLE,
"EMAIL" VARCHAR2(30) NOT NULL ENABLE,
"RECOMMEND" VARCHAR2(10),
"IDENTIFIER" VARCHAR2(100),
PRIMARY KEY ("ID")
)
partition by hash(cardid)
partitions 2;
SQL> alter table TABLE_M5 add primary key (ID);
step 6.导入表，验证数据
$ impdp \"/ as sysdba\" directory=bk dumpfile=expdp_orcl_table_01.dmp,expdp_orcl_table_02.dmp table_exists_action=append
SQL> select * from dba_part_tables where table_name='TABLE_M5'; ---记录分区的表的信息
SQL> select * from DBA_tab_partitions where table_name='TABLE_M5'; ---记录表的分区的信息
SQL> select * from dba_part_indexes where table_name='TABLE_M5'; ---查询分区的索引信息
SQL> select count(*) from TABLE_M5;
SQL> select count(*) from TABLE_M5 partition(SYS_P221); ---hash分区名自动分配，通过DBA_tab_partitions
SQL> select count(*) from TABLE_M5 partition(SYS_P222); ---hash分区名自动分配，通过DBA_tab_partitions
step 7.创建分区索引
SQL> create index table_m5_index on table_m5(cardid) local;
SQL> select index_name,status from dba_indexes where table_name='TABLE_M5';
# 分区索引有无有效需要查看dba_ind_partitions视图的status列：usable有效，unusable无效
SQL> select * from dba_part_indexes where table_name='TABLE_M5';
SQL> select * from dba_ind_partitions where index_name='TABLE_M5_INDEX';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_M5_INDEX';
5.2.insert into
step 1.查看表数据和索引
SQL> select count(*) from table_m5;
SQL> select * from dba_indexes where table_name='TABLE_M5'; ---有索引
step 2.重命名原表（切记不要删除，便于分区错误后恢复），并创建分区表（与旧表的结构一致）
SQL> alter table table_m5 rename to table_m5_old; ---重命名原表
step 3.将表结构、索引、约束的DDL语句全部复制：
SQL> select dbms_metadata.get_ddl('TABLE','TABLE_M5_OLD','USERNAME') from dual;
SQL> select TABLE_NAME,TABLE_TYPE,INDEX_NAME from dba_indexes where table_name='TABLE_M5_OLD';
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where table_name='TABLE_M5_OLD';
SQL> select dbms_metadata.get_ddl('INDEX','索引名','USERNAME') from dual;
SQL> select dbms_metadata.get_ddl('CONSTRAINT','约束名','USERNAME') from dual;
step 4.创建间隔分区表
SQL>
CREATE TABLE "USERNAME"."TABLE_M5"
("ID" NUMBER(20,0) NOT NULL ENABLE,
"NAME" VARCHAR2(20) NOT NULL ENABLE,
"AGE" NUMBER(10,0) NOT NULL ENABLE,
"SEX" VARCHAR2(10) NOT NULL ENABLE,
"CARDID" NUMBER(30,0) NOT NULL ENABLE,
"JOINDATE" DATE NOT NULL ENABLE,
"REGION" VARCHAR2(12) NOT NULL ENABLE,
"TEL" VARCHAR2(12) NOT NULL ENABLE,
"EMAIL" VARCHAR2(30) NOT NULL ENABLE,
"RECOMMEND" VARCHAR2(10),
"IDENTIFIER" VARCHAR2(100),
PRIMARY KEY ("ID")
)
partition by range(joindate)
interval (numtoyminterval(1,'month') )
(
partition p_001 values less than (to_date('2015-01-01','YYYY-MM-DD')),
partition p_002 values less than (to_date('2015-02-01','YYYY-MM-DD')),
partition p_003 values less than (to_date('2015-03-01','YYYY-MM-DD'))
);
SQL> alter table TABLE_M5 add primary key (ID);
step 5.insert导入表，验证数据
SQL> insert into table_m5 select * from table_m5_old;
SQL> commit;
SQL> select * from dba_part_tables where table_name='TABLE_M5'; ---记录分区的表的信息
SQL> select * from DBA_tab_partitions where table_name='TABLE_M5'; ---记录表的分区的信息。根据分区字段joindate的时间，自动创建按1个月创建分区
select * from dba_part_indexes where table_name='TABLE_M5'; ---查询分区的索引信息（原普通索引还在）
SQL> select count(*) from TABLE_M5;
SQL> select count(*) from TABLE_M5 partition(P_001); ---间隔分区名自动分配，通过DBA_tab_partitions
SQL> select count(*) from TABLE_M5 partition(P_002); ---间隔分区名自动分配，通过DBA_tab_partitions
..........
step 6.创建分区索引
SQL> create index table_m5_index on table_m5(cardid) local;
SQL> select index_name,status from dba_indexes where table_name='TABLE_M5';
SQL> select * from dba_part_indexes where table_name='TABLE_M5';
SQL> select * from dba_ind_partitions where index_name='TABLE_M5_INDEX';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_M5_INDEX';
5.3.dbms_redefinition在线重定义
通过dbms_redefinition在线重定义的方式将普通表数据迁移到分区表（在线方式，不需要停止相关表的业务，可以进行在线迁移）
在高可用系统中，改变表的定义是一件比较棘手事，尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。而且对于被大量DML语句访问的表，9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。
dbms_redefinition在线重定义能保证数据的一致性，在大部分时间内，表都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
dbms_redefinition 进行分表转换可以实现真正意义上的0秒不停机切换而且完全保证数据一致性，只是在dbms_redefinition.finish_redef_table时有短暂的独占表锁定
在线重定义表具有以下功能：
- 修改表的存储参数
- 将表转移到其他表空间
- 增加并行查询选项
- 增加或删除分区
- 重建表以减少碎片
- 将堆表改为索引组织表或相反的操作
- 增加或删除一个列
调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外，还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、 LOCKANY TABLE和SELECT ANY TABLE的权限。
使用在线重定义的一些限制条件：
- 必须有足够的空间容纳表的两个副本。
- 不能修改主键列。
- 表必须有主键(ORA-12089: cannot online redefine table with no primary key)
- 必须在同一用户中进行重新定义，跨用户不行。
- 在重新定义操作完成之前，添加的新列不能变为非NULL。
- 表不能包含long、bfile或用户定义的类型。
- 不能重新定义群集表。
- 不能重新定义SYS或系统模式中的表。
- 不能重定义带有物化视图日志或在其上定义物化视图的表
- 重新定义时无法进行数据的水平子设置。
- 数据库若是rac，只需在其中一个节点的数据库上实施。
- 实施中部分命令执行时间较长，命令一旦执行后请不要中断命令的执行一条命令执行完毕后，不能再次重复执行该命令。
- 执行命令的数据库用户需要为sys或者system用户
在线重定义的操作流程如下：
- 创建基础表A，如果存在，就不需要操作。
- 创建中间的分区表B。
- 开始重定义，将基表A的数据导入中间分区表B。
- 结束重定义，此时在DB的Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的中间分区表B成了普通表。此时我们可以删除我们创建的中间表B。它已经是普通表。
DBMS_REDEFINITION 包中定义的存储过程：
案例：将非分区表table_m10转为range分区表
step 1.查看表数据和索引
SQL> select count(*) from table_m10;
SQL> select * from dba_indexes where table_name='TABLE_M10'; ---有索引
step 2.创建中间range分区表
SQL> select dbms_metadata.get_ddl('TABLE','TABLE_M10','USERNAME') from dual;
###只需要查看表结构，因为可以通过DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS包的形式将索引、触发器、约束、权限复制，在下面操作
SQL> CREATE TABLE "USERNAME"."TABLE_M10_LS"
("ID" NUMBER(20,0) NOT NULL ENABLE,
"NAME" VARCHAR2(20) NOT NULL ENABLE,
"AGE" NUMBER(10,0) NOT NULL ENABLE,
"SEX" VARCHAR2(10) NOT NULL ENABLE,
"CARDID" NUMBER(30,0) NOT NULL ENABLE,
"JOINDATE" DATE NOT NULL ENABLE,
"REGION" VARCHAR2(12) NOT NULL ENABLE,
"TEL" VARCHAR2(12) NOT NULL ENABLE,
"EMAIL" VARCHAR2(30) NOT NULL ENABLE,
"RECOMMEND" VARCHAR2(10),
"IDENTIFIER" VARCHAR2(100),
PRIMARY KEY ("ID"))
partition by range (ID)
(
partition P10000 values less than (10010000),
partition P20000 values less than (10020000),
partition P30000 values less than (10030000),
partition P40000 values less than (10040000),
partition P50000 values less than (10050000),
partition P_MAX values less than (MAXVALUE)
);
step 3.进行表重定义操作
检查table_m10是否可以进行重定义（只是验证原表是否可以通过dbms_redefinition进行重定义操作的条件）
SQL> begin
dbms_redefinition.can_redef_table('USERNAME','TABLE_M10');
end;
/
step 4.对table_m10进行重定义（同步原表数据到中间分区表）
SQL> begin
dbms_redefinition.start_redef_table(uname => 'USERNAME', ---表所属的用户
orig_table => 'TABLE_M10', ---要重新组织表的名称
int_table => 'TABLE_M10_LS', ---中间表的名称
options_flag => dbms_redefinition.cons_use_pk); ---使用主键约束来重定义表（按照这个标准即可）。options_flag有两个选项：
--dbms_redefinition.cons_use_pk：在重定义时，创建的物化视图是基于主键的刷新
--dbms_redefinition.cons_use_rowid：在重定义时，创建的物化视图是基于rowid刷新。如果表启用了行迁移，数据会乱掉，不建议使用
end;
/
step 5.复制原表的上的依赖关系（包括索引、触发器、约束、权限等）
SQL> declare
num_errors pls_integer;
begin
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'USERNAME',
orig_table => 'TABLE_M10',
int_table => 'TABLE_M10_LS',
copy_indexes =>dbms_redefinition.cons_orig_params, --copy索引
copy_triggers => TRUE, ---copy触发器
copy_constraints => TRUE, ---copy约束
copy_privileges => TRUE, ---copy表的权限
ignore_errors => TRUE,
num_errors => num_errors);
end;
/
step 6.同步原表与中间表的数据（dbms_redefinition.start_redef_table操作时，如果表中有数据，那么会消耗很长的时间，因此原表还在DML操作，中间分区表不是最新的数据，所以要同步最新的数据）
SQL> begin
dbms_redefinition.sync_interim_table( uname => 'USERNAME',
orig_table => 'TABLE_M10',
int_table => 'TABLE_M10_LS');
end;
/
step 7.结束重定义（此操作就是将原表和中间表进行表名转换，原普通表变为了分区表，中间分区表变为了普通表。注：转换只能进行一次，不能回切，会报错）
注：finish_redef_table过程中，原始表被短暂锁定（独占锁定）。如果在第五步sync_interim_table后原表还有DML操作（insert插入），那么第六步就是先独占锁定表，然后再执行一次sync_interim_table数据同步到中间表，保证两表之间的数据完全一致（0秒不停机切换而且数据也会保证一致性，只是有短暂的锁表），之后进行表名转换
SQL> begin
dbms_redefinition.finish_redef_table( uname => 'USERNAME',
orig_table => 'TABLE_M10',
int_table => 'TABLE_M10_LS');
end;
/
step 8.验证table_m10数据并验证是否分区。在dbms_redefinition.finish_redef_table结束重定义的时候，原表的结构直接继承了中间分区表的结构，中间分区表则变成了普通表，分区操作在秒级
SQL> select count(*) from TABLE_M10; ---并对比TABLE_M10_LS数据查看是否缺少
SQL> select * from dba_part_tables where table_name='TABLE_M10'; ---记录分区的表的信息
SQL> select * from DBA_tab_partitions where table_name='TABLE_M10'; ---记录表的分区的信息
SQL> select * from dba_part_indexes where table_name='TABLE_M10'; ---查询分区的索引信息（原普通索引还在）
SQL> select count(*) from TABLE_M10 partition(P10000); ---range分区名，通过DBA_tab_partitions
SQL> select count(*) from TABLE_M10 partition(P20000); ---range分区名，通过DBA_tab_partitions
step 9.创建分区索引。普通表用的普通索引，分区表需要用到专用的local或者global，所以会失效
查询当前表的索引：发现有普通索引，并且还有效
SQL> select * from dba_indexes where table_name='TABLE_M10';
创建分区索引：
SQL> create index table_m10_index on table_m10(cardid) local;
SQL> select index_name,status from dba_indexes where table_name='TABLE_M10'; ---N/A：分区索引。分区索引有无有效需要查看dba_ind_partitions视图的status列：usable有效，unusable无效
SQL> select * from dba_part_indexes where table_name='TABLE_M10';
SQL> select * from dba_ind_partitions where index_name='TABLE_M10_INDEX';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_M10_INDEX';
step 10.删除table_m10_ls中间表（已经由分区表变为了普通表）
SQL> drop table TABLE_M10_LS;
