"[链接]0. 前提说明：默认情况下，oracle 的分区表对于分区字段不允许进行 update，对分区字段行进 update，就会报错——ORA-14402: 更新分区关键字列将导致分区的更改。但是可以通过打开表的 row movement 属性来允许对分区字段的 update 操作，不建议这么操 ...."

eric
Dharma 43 号会员
分享 • 0 回帖 • 401 浏览 • 4 年前

Oracle 分区表定义与修改

0. 前提说明：

默认情况下，oracle 的分区表对于分区字段不允许进行 update，对分区字段行进 update，就会报错——ORA-14402: 更新分区关键字列将导致分区的更改。但是可以通过打开表的 row movement 属性来允许对分区字段的 update 操作，不建议这么操作。
2. 分区表尽量不要建主键，因为建主键的同时会建一个唯一性的全局索引，在 add/drop/truncate 分区表时如果不指定 update global indexes 则会使索引失效，导致数据无法插入。
将主键建成本地索引方法：建一个包含 (分区列、主键列) 的唯一性复合索引，然后在这个索引上建主键
*   create unique index I_PCT_HISTORY_RECORD on PCT_HISTORY_RECORD (GID,create_date) local; #一定要包含分区列否则会报ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集
*   alter table PCT_HISTORY_RECORD add primary key (GID,create_date);
*   alter table PCT_HISTORY_RECORD add primary key (GID,create_date) USING INDEX LOCAL;#创建索引与主键
全局索引在 drop partition、truncate partition 后都会失效，对于 Global index，Oracle 提供了一参数 update global indexes，可避免 truncate 或 drop partition 时索引失效问题，另外一种方法是 rebuild，这 2 种方法各有利弊，在生产上不同的环境方法也不一样，要平衡操作 ddl 的时间和重建索引哪个时间更少，以决定是否需要附加 update indexes 子句。

update global indexes，此种方法主要针对的是全局索引
rebuild，支持全局索引和本地索引

alter table test_partas truncate partition p0;
alter table test_partas drop partition p0;
alter index PK_ID rebuild online;
alter table test_partas truncate partition p1 update indexes;

1. 新建分区表

适用场景：环境初始化新建表，如果不存在删除分区的情况，可以和正常建表一样建立主键和全局唯一性索引，但普通索引字段建议走本地索引。

-- Create table

create table PCT_HISTORY_RECORD

(

gid VARCHAR2(32) not null,

code VARCHAR2(50),

name VARCHAR2(50),

is_publish NUMBER(1) default 0 not null,

remark VARCHAR2(200),

create_id VARCHAR2(32) not null,

create_date DATE default SYSDATE not null,

modify_id VARCHAR2(32),

modify_date DATE,

is_active NUMBER(1) default 0 not null,

is_delete NUMBER(1) default 0 not null,

uda1 VARCHAR2(50),

uda2 VARCHAR2(50),

uda3 VARCHAR2(50),

uda4 VARCHAR2(50),

uda5 VARCHAR2(50),

uda6 VARCHAR2(50),

uda7 VARCHAR2(50),

uda8 VARCHAR2(50),

uda9 VARCHAR2(50),

uda10 VARCHAR2(50),

deleted VARCHAR2(32),

data_role VARCHAR2(32) default -1 not null,

data_role1 VARCHAR2(32) default -1,

data_role2 VARCHAR2(32) default 0,

uda1c VARCHAR2(100),

uda1n VARCHAR2(100),

uda2c VARCHAR2(100),

uda2n VARCHAR2(100),

uda3c VARCHAR2(100),

uda3n VARCHAR2(100),

uda4c VARCHAR2(100),

uda4n VARCHAR2(100),

uda5c VARCHAR2(100),

uda5n VARCHAR2(100)

)

PARTITION BY RANGE(create_date)

INTERVAL(NUMTOYMINTERVAL(1，'MONTH'))

(

PARTITION P1_PCT_HISTORY_RECORD VALUES LESS THAN(TO_DATE('2019-01-01','YYYY-MM-DD'))

TABLESPACE PLATFORM_DATA

);

-- Add comments to the table

comment on table PCT_HISTORY_RECORD is 'PCT数据表';

-- Create/Recreate primary, unique and foreign key constraints:创建主键和索引

alter table PCT_HISTORY_RECORD add constraint PK_PCT_HISTORY_RECORD primary key (GID) using index tablespace PLATFORM_INDEX;

--创建唯一性本地索引，注意分区字段必须加上

create unique index I_PCT_HISTORY_RECORD on PCT_HISTORY_RECORD (GID,CREATE_DATE) tablespace PLATFORM_INDEX LOCAL;

-- 创建普通本地索引

create index I_PCT_HISTORY_RECORD_1 on PCT_HISTORY_RECORD (code) tablespace PLATFORM_INDEX LOCAL;

create index I_PCT_HISTORY_RECORD_2 on PCT_HISTORY_RECORD (is_publish) tablespace PLATFORM_INDEX LOCAL;

--如果不修改，新增的分区都会放到user表空间

alter table PCT_HISTORY_RECORD modify default attributes tablespace PLATFORM_DATA;

2. Oracle 普通表 -> 分区表，11g 在线重定义

适用场景：数据量百万或千万级，不能停服务的情况

环境准备：MES 正常表格定义，有主键，有索引，表空间与索引表空间随意，并准备好数据

– Create table

create table PCT_HISTORY_RECORD

(

gid VARCHAR2(32) not null,

code VARCHAR2(50),

name VARCHAR2(50),

is_publish NUMBER(1) default 0 not null,

remark VARCHAR2(200),

create_id VARCHAR2(32) not null,

create_date DATE default SYSDATE not null,

modify_id VARCHAR2(32),

modify_date DATE,

is_active NUMBER(1) default 0 not null,

is_delete NUMBER(1) default 0 not null,

uda1 VARCHAR2(50),

uda2 VARCHAR2(50),

uda3 VARCHAR2(50),

uda4 VARCHAR2(50),

uda5 VARCHAR2(50),

uda6 VARCHAR2(50),

uda7 VARCHAR2(50),

uda8 VARCHAR2(50),

uda9 VARCHAR2(50),

uda10 VARCHAR2(50),

deleted VARCHAR2(32),

data_role VARCHAR2(32) default -1 not null,

data_role1 VARCHAR2(32) default -1,

data_role2 VARCHAR2(32) default 0,

uda1c VARCHAR2(100),

uda1n VARCHAR2(100),

uda2c VARCHAR2(100),

uda2n VARCHAR2(100),

uda3c VARCHAR2(100),

uda3n VARCHAR2(100),

uda4c VARCHAR2(100),

uda4n VARCHAR2(100),

uda5c VARCHAR2(100),

uda5n VARCHAR2(100)

);

– Create/Recreate indexes

create index I_PCT_HISTORY_RECORD_1 on PCT_HISTORY_RECORD (CODE);

create index I_PCT_HISTORY_RECORD_2 on PCT_HISTORY_RECORD (is_publish);

– Create/Recreate primary, unique and foreign key constraints

alter table PCT_HISTORY_RECORD

add constraint PK_PCT_HISTORY_RECORD primary key (GID) using index ;

–插入随机数据

INSERT INTO PCT_HISTORY_RECORD(GID,CODE,NAME,IS_PUBLISH,REMARK,CREATE_ID,CREATE_DATE)

select sys_guid(),

dbms_random.string(‘A’, round(dbms_random.value(5,10))) CODE,

dbms_random.string(‘B’, round(dbms_random.value(5,10))) NAME,

1 AS IS_PUBLISH,

NULL AS REMARK,

‘ERIC’ AS CREATE_ID,

trunc(sysdate) - dbms_random.value(1, 365) create_date

from dual connect by rownum<=200000

创建重定义临时表，注意此处没有索引，主键等内容

create table PCT_HISTORY_RECORD_TEMP

(

gid VARCHAR2(32) not null,

code VARCHAR2(50),

name VARCHAR2(50),

is_publish NUMBER(1) default 0 not null,

remark VARCHAR2(200),

create_id VARCHAR2(32) not null,

create_date DATE default SYSDATE not null,

modify_id VARCHAR2(32),

modify_date DATE,

is_active NUMBER(1) default 0 not null,

is_delete NUMBER(1) default 0 not null,

uda1 VARCHAR2(50),

uda2 VARCHAR2(50),

uda3 VARCHAR2(50),

uda4 VARCHAR2(50),

uda5 VARCHAR2(50),

uda6 VARCHAR2(50),

uda7 VARCHAR2(50),

uda8 VARCHAR2(50),

uda9 VARCHAR2(50),

uda10 VARCHAR2(50),

deleted VARCHAR2(32),

data_role VARCHAR2(32) default -1 not null,

data_role1 VARCHAR2(32) default -1,

data_role2 VARCHAR2(32) default 0,

uda1c VARCHAR2(100),

uda1n VARCHAR2(100),

uda2c VARCHAR2(100),

uda2n VARCHAR2(100),

uda3c VARCHAR2(100),

uda3n VARCHAR2(100),

uda4c VARCHAR2(100),

uda4n VARCHAR2(100),

uda5c VARCHAR2(100),

uda5n VARCHAR2(100)

)PARTITION BY RANGE(create_date)

INTERVAL(NUMTOYMINTERVAL(1，‘MONTH’))

(

PARTITION P1_PCT_HISTORY_RECORD VALUES LESS THAN(TO_DATE(‘2019-01-01’,‘YYYY-MM-DD’))

TABLESPACE PLATFORM_DATA

) ;

–修改新增分区的默认表空间

alter table PCT_HISTORY_RECORD_TEMP modify default attributes tablespace PLATFORM_DATA;

开始重定义
确定是否可以重定义

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘UNIMAX4_IMOM’,‘PCT_HISTORY_RECORD’);

开始重定义

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(‘UNIMAX4_IMOM’,‘PCT_HISTORY_RECORD’,‘PCT_HISTORY_RECORD_TEMP’);

复制表对象属性 (例如触发器、索引等，此处没有执行)，创建主键和索引

复制后原来的索引都是全局索引，就没有执行复制属性的操作。

DECLARE

error_count pls_integer := 0;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

uname => ‘UNIMAX4_IMOM’,

orig_table => ‘PCT_HISTORY_RECORD’,

int_table => ‘PCT_HISTORY_RECORD_TEMP’,

ignore_errors => TRUE,

num_errors => error_count);

DBMS_OUTPUT.PUT_LINE(’errors := ’ || TO_CHAR(error_count));

END;

create unique index IDX_PCT_HISTORY_RECORD on PCT_HISTORY_RECORD_TEMP (GID,CREATE_DATE) tablespace PLATFORM_INDEX LOCAL;

create index IDX_PCT_HISTORY_RECORD_1 on PCT_HISTORY_RECORD_TEMP (code) tablespace PLATFORM_INDEX LOCAL;

create index IDX_PCT_HISTORY_RECORD_2 on PCT_HISTORY_RECORD_TEMP (is_publish) tablespace PLATFORM_INDEX LOCAL;

同步数据，对开始重定义后修改的数据执行同步

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE (‘UNIMAX4_IMOM’,‘PCT_HISTORY_RECORD’,‘PCT_HISTORY_RECORD_TEMP’);

收集中间表的统计信息

EXEC DBMS_STATS.gather_table_stats(‘UNIMAX4_IMOM’, ‘PCT_HISTORY_RECORD_TEMP’, cascade => TRUE);

完成重定义

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘UNIMAX4_IMOM’,‘PCT_HISTORY_RECORD’,‘PCT_HISTORY_RECORD_TEMP’);

删除临时表

drop table PCT_HISTORY_RECORD_TEMP PURGE;

8. 修改索引，约束名称和原表一致

因为在创建索引时，索引名称没有加 TEMP，修改了索引的前缀，所以此处不重新调整索引名称。

在 FINISH_REDEF_TABLE 之前，可以使用 abort_redef_table 停止重定义，然后删除 PCT_HISTORY_RECORD_TEMP，如果不 Abort，删除不了

EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE (‘UNIMAX4_IMOM’,‘PCT_HISTORY_RECORD’,‘PCT_HISTORY_RECORD_TEMP’);

3. Oracle 普通表 -> 分区表，12c 在线重定义

适用场景：12c 的运行环境，但不确定索引会怎么处理

DBMS_REDEFINITION.REDEF_TABLE (

uname IN VARCHAR2,

tname IN VARCHAR2,

table_compression_type IN VARCHAR2 := NULL,

table_part_tablespace IN VARCHAR2 := NULL,

index_key_compression_type IN VARCHAR2 := NULL,

index_tablespace IN VARCHAR2 := NULL,

lob_compression_type IN VARCHAR2 := NULL,

lob_tablespace IN VARCHAR2 := NULL,

lob_store_as IN VARCHAR2 := NULL,

refresh_dep_mviews IN VARCHAR2 := ‘N’,

dml_lock_timeout IN PLS_INTEGER := NULL);

示例：

BEGIN

DBMS_REDEFINITION.REDEF_TABLE(

uname => ‘C##BB’,

tname => ‘T1’,

table_compression_type => NULL,

table_part_tablespace => ‘BB’,

index_key_compression_type => NULL,

index_tablespace => NULL,

lob_compression_type => NULL,

lob_tablespace => NULL,

lob_store_as => NULL);

END;

来源：https://blog.csdn.net/xxzhaobb/article/details/82182945

Oracle 普通表 -> 分区表，停服务修改

适用场景：在线重定义的表数据量特别的大，可能过亿的数据量

来源：https://www.cnblogs.com/jyzhao/p/4378753.html

创建分区表，注意此处没有索引，主键等内容

create table PCT_HISTORY_RECORD_TEMP

(

gid VARCHAR2(32) not null,

code VARCHAR2(50),

name VARCHAR2(50),

is_publish NUMBER(1) default 0 not null,

remark VARCHAR2(200),

create_id VARCHAR2(32) not null,

create_date DATE default SYSDATE not null,

modify_id VARCHAR2(32),

modify_date DATE,

is_active NUMBER(1) default 0 not null,

is_delete NUMBER(1) default 0 not null,

uda1 VARCHAR2(50),

uda2 VARCHAR2(50),

uda3 VARCHAR2(50),

uda4 VARCHAR2(50),

uda5 VARCHAR2(50),

uda6 VARCHAR2(50),

uda7 VARCHAR2(50),

uda8 VARCHAR2(50),

uda9 VARCHAR2(50),

uda10 VARCHAR2(50),

deleted VARCHAR2(32),

data_role VARCHAR2(32) default -1 not null,

data_role1 VARCHAR2(32) default -1,

data_role2 VARCHAR2(32) default 0,

uda1c VARCHAR2(100),

uda1n VARCHAR2(100),

uda2c VARCHAR2(100),

uda2n VARCHAR2(100),

uda3c VARCHAR2(100),

uda3n VARCHAR2(100),

uda4c VARCHAR2(100),

uda4n VARCHAR2(100),

uda5c VARCHAR2(100),

uda5n VARCHAR2(100)

)PARTITION BY RANGE(create_date)

INTERVAL(NUMTOYMINTERVAL(1，‘MONTH’))

(

PARTITION P1_PCT_HISTORY_RECORD VALUES LESS THAN(TO_DATE(‘2019-01-01’,‘YYYY-MM-DD’))

TABLESPACE PLATFORM_DATA

) ;

–修改新增分区的默认表空间

alter table PCT_HISTORY_RECORD_TEMP modify default attributes tablespace PLATFORM_DATA;

设置新建分区表为 nologging，重命名原表 T 为 T_OLD

alter table PCT_HISTORY_RECORD_TEMP nologging;

rename PCT_HISTORY_RECORD to PCT_HISTORY_RECORD_BAK;

并行直接路径插入 (10w 条数据 0.016 秒完成)

alter session enable parallel dml;

insert /*+ append parallel(p,10) / into PCT_HISTORY_RECORD_TEMP p select /+ parallel(n,10) */ * from PCT_HISTORY_RECORD_BAK n;

commit;

重命名历史表的索引和约束名

alter index PK_PCT_HISTORY_RECORD rename to PK_PCT_HISTORY_RECORD_BAK;

alter table PCT_HISTORY_RECORD_BAK rename constraint PK_PCT_HISTORY_RECORD to PK_PCT_HISTORY_RECOR_BAK;

alter index I_PCT_HISTORY_RECORD_1 rename to I_PCT_HISTORY_RECORD_1_BAK;

alter index I_PCT_HISTORY_RECORD_2 rename to I_PCT_HISTORY_RECORD_2_BAK;

4. 给新分区表 T_PART 创建主键及索引

alter table PCT_HISTORY_RECORD_TEMP add constraint PK_PCT_HISTORY_RECORD_TEMP primary key (GID) tablespace PLATFORM_INDEX LOCAL nologging parallel 32;

create index I_PCT_HISTORY_RECORD_1 on PCT_HISTORY_RECORD_TEMP (code) tablespace PLATFORM_INDEX LOCAL nologging parallel 32;

create index I_PCT_HISTORY_RECORD_2 on PCT_HISTORY_RECORD_TEMP (is_publish) tablespace PLATFORM_INDEX LOCAL nologging parallel 32;

修改索引和表为 logging，noparallel

alter index I_PCT_HISTORY_RECORD_1 logging noparallel;

alter index I_PCT_HISTORY_RECORD_2 logging noparallel;

alter table PCT_HISTORY_RECORD_TEMP logging;

6.rename 表，恢复 T 表的相关应用

rename PCT_HISTORY_RECORD_TEMP to PCT_HISTORY_RECORD;

4. 分区表模拟创建随机数据

create table test as

select sys_guid(),

dbms_random.string(‘A’, round(dbms_random.value(5,10))) M1,

dbms_random.string(‘B’, round(dbms_random.value(5,10))) M2,

dbms_random.string(‘C’, round(dbms_random.value(5,10))) M3,

ROUND(dbms_random.value(5,10),4) N1,

ROUND(dbms_random.value(5000,10000)) N2,

rownum seq,

trunc(sysdate) - dbms_random.value(1, 365) create_date

from dual connect by rownum<=200000

Oracle 分区表定义与修改

0. 前提说明：

1. 新建分区表

2. Oracle 普通表 -> 分区表，11g 在线重定义

3. Oracle 普通表 -> 分区表，12c 在线重定义

4. 分区表模拟创建随机数据

相关帖子

随便看看

赞助商我要投放

欢迎访问

艾普工华达摩院论坛