这篇文章主要介绍“Oracle怎么把非分区表转为分区表”，在日常操作中，相信很多人在Oracle怎么把非分区表转为分区表问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle怎么把非分区表转为分区表”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
一：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怎么把非分区表转为分区表”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
