共计 6675 个字符，预计需要花费 17 分钟才能阅读完成。
这篇文章主要介绍“Oracle 怎么把非分区表转为分区表”，在日常操作中，相信很多人在 Oracle 怎么把非分区表转为分区表问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle 怎么把非分区表转为分区表”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
一：oracle 11G 在线将非分区表转换为分区表
Online Redefinition
二：Oracle 12C
在线将非分区表转换为分区表
ALTER TABLE table_name MODIFY table_partitioning_clauses
[filter_condition]
[ONLINE]
[UPDATE INDEXES [ ( index { local_partitioned_index | global_partitioned_index | GLOBAL}
[, index { local_partitioned_index | global_partitioned_index | GLOBAL} ]… )
]
]
一：oracle 11G 将非分区表转换为分区表
在线重定义 Online Redefinition
1. 数据库版本
— 数据库 19C，相当于 12.2.0.3 版本，也支持 Online Redefinition
SQL select banner_full from v$version;
BANNER_FULL
———————————————————————
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 CJCPDB READ WRITE NO
2. 创建测试表插入测试数据
SQL conn cjc/cjc@cjcpdb
SQL create table t1(id number,adr varchar2(100),acc number);
SQL
insert into t1 values(1, dapuchai ,10);
insert into t1 values(2, dunhua ,20);
insert into t1 values(3, xiaopuchai ,30);
insert into t1 values(4, fuerhe ,101);
insert into t1 values(5, fuyuanjie ,130);
insert into t1 values(6, songyuanjie ,125);
insert into t1 values(7, bajiazhi ,166);
insert into t1 values(8, yaotun ,105);
insert into t1 values(9, hanconggou ,256);
insert into t1 values(10, jiangdong ,270);
commit;
SQL alter table t1 add constraint pk_t1_id primary key (id);
SQL col adr for a15
SQL select * from t1;
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
4 fuerhe 101
5 fuyuanjie 130
6 songyuanjie 125
7 bajiazhi 166
8 yaotun 105
9 hanconggou 256
10 jiangdong 270
10 rows selected.
3. 检查下这张表是否可以在线重定义
—dbms_redefinition.cons_use_rowid
—dbms_redefinition.cons_use_pk
SQL exec dbms_redefinition.can_redef_table(CJC , T1 ,dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
4. 建立在线重定义需要的中间表
SQL
create table t1_temp(id number,adr varchar2(100),acc number)
partition by range(acc)(
partition PAR01 values less than (100),
partition PAR02 values less than (200),
partition PAR03 values less than (300),
partition PARMAX values less THAN (MAXVALUE)
);
SQL alter table t1_temp add constraint pk_t1_temp_id1 primary key (id);
5. 启动在线重定义
SQL exec dbms_redefinition.start_redef_table(CJC , T1 , T1_TEMP
PL/SQL procedure successfully completed.
6. 检查中间表数据
SQL select * from t1_temp;
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
4 fuerhe 101
5 fuyuanjie 130
6 songyuanjie 125
7 bajiazhi 166
8 yaotun 105
9 hanconggou 256
10 jiangdong 270
10 rows selected.
7. 模拟生产环境数据变化
SQL insert into t1 values(20, yansan ,208);
1 row created.
SQL delete t1 where id=4;
1 row deleted.
SQL update t1 set adr= dashitou where id=7;
1 row updated.
SQL commit;
Commit complete.
8. 原表被修改，中间表并没有更新
SQL select * from t1;
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
5 fuyuanjie 130
6 songyuanjie 125
7 dashitou 166
8 yaotun 105
9 hanconggou 256
10 jiangdong 270
20 yansan 208
10 rows selected.
SQL select * from t1_temp;
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
4 fuerhe 101
5 fuyuanjie 130
6 songyuanjie 125
7 bajiazhi 166
8 yaotun 105
9 hanconggou 256
10 jiangdong 270
10 rows selected.
9. 中间表同步数据
SQL exec dbms_redefinition.sync_interim_table(CJC , T1 , T1_TEMP
PL/SQL procedure successfully completed.
查询同步后数据：
SQL select * from t1_temp;
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
5 fuyuanjie 130
6 songyuanjie 125
8 yaotun 105
7 dashitou 166
9 hanconggou 256
10 jiangdong 270
20 yansan 208
10 rows selected.
10. 结束在线重定义
SQL exec dbms_redefinition.finish_redef_table(CJC , T1 , T1_TEMP
PL/SQL procedure successfully completed.
11. 验证数据
SQL select * from T1;
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
5 fuyuanjie 130
6 songyuanjie 125
7 dashitou 166
8 yaotun 105
9 hanconggou 256
10 jiangdong 270
20 yansan 208
10 rows selected.
SQL select * from t1_temp;
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
5 fuyuanjie 130
6 songyuanjie 125
8 yaotun 105
7 dashitou 166
9 hanconggou 256
10 jiangdong 270
20 yansan 208
10 rows selected.
12. 查看各分区数据
SQL col table_name for a10
SQL col partition_name for a10
SQL select table_name, partition_name from user_tab_partitions where table_name = T1
TABLE_NAME PARTITION_
———- ———-
T1 PAR01
T1 PAR02
T1 PAR03
T1 PARMAX
SQL select * from T1 partition(PAR01);
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
SQL select * from T1 partition(PAR02);
ID ADR
ACC
———- ————— ———-
5 fuyuanjie 130
6 songyuanjie 125
8 yaotun 105
7 dashitou 166
SQL select * from T1 partition(PAR03);
ID ADR
ACC
———- ————— ———-
9 hanconggou 256
10 jiangdong 270
20 yansan 208
13. 检查并删掉中间表
SQL drop table t1_temp purge;
Table dropped.
二：Oracle 12C
将非分区表转换为分区表
在 12C 中在线将非分区表转换为分区表要相对 11G 容易了许多，
只需要一条语句即可搞定:ALTER TABLE table_name MODIFY table_partitioning_clauses ……
1. 创建测试表入测试数据
SQL conn cjc/cjc@cjcpdb
—drop table t1 purge;
SQL create table t1(id number,adr varchar2(100),acc number);
insert into t1 values(1, dapuchai ,10);
insert into t1 values(2, dunhua ,20);
insert into t1 values(3, xiaopuchai ,30);
insert into t1 values(4, fuerhe ,101);
insert into t1 values(5, fuyuanjie ,130);
insert into t1 values(6, songyuanjie ,125);
insert into t1 values(7, bajiazhi ,166);
insert into t1 values(8, yaotun ,105);
insert into t1 values(9, hanconggou ,256);
insert into t1 values(10, jiangdong ,270);
commit;
SQL alter table t1 add constraint pk_t1_id primary key (id);
SQL col adr for a15
SQL select * from t1;
SQL col table_name for a10
SQL col partition_name for a10
SQL select table_name, partition_name from user_tab_partitions where table_name = T1
no rows selected
2. 在线将非分区表转换为分区表，索引转换成全局索引
SQL
alter table t1 modify
partition by range (acc)
(partition PAR01 values less than (100),
partition PAR02 values less than (200),
partition PAR03 values less than (300),
partition PARMAX values less than (MAXVALUE)
) online
update indexes
(
pk_t1_id GLOBAL
);
Table altered.
3. 检查
SQL col table_name for a10
SQL col partition_name for a10
SQL select table_name, partition_name from user_tab_partitions where table_name = T1
TABLE_NAME PARTITION_
———- ———-
T1 PAR01
T1 PAR02
T1 PAR03
T1 PARMAX
SQL select * from T1 partition(PAR01);
ID ADR
ACC
———- ————— ———-
1 dapuchai 10
2 dunhua 20
3 xiaopuchai 30
SQL select * from T1 partition(PAR02);
ID ADR
ACC
———- ————— ———-
4 fuerhe 101
5 fuyuanjie 130
6 songyuanjie 125
7 bajiazhi 166
8 yaotun 105
SQL select * from T1 partition(PAR03);
ID ADR
ACC
———- ————— ———-
9 hanconggou 256
10 jiangdong 270
SQL col index_name for a15
SQL col index_type for a10
SQL select index_name,index_type,status from user_indexes;
INDEX_NAME INDEX_TYPE STATUS
————— ———- ——–
PK_T1_ID NORMAL VALID
SQL col segment_name for a15
SQL select segment_name,segment_type from user_segments;
SEGMENT_NAME SEGMENT_TYPE
————— ——————
到此，关于“Oracle 怎么把非分区表转为分区表”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
