Oracle怎么把非分区表转为分区表-乐山大橙子建站

新闻中心

这里有您想知道的互联网营销解决方案

Oracle怎么把非分区表转为分区表

这篇文章主要介绍“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怎么把非分区表转为分区表”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！

网站栏目：Oracle怎么把非分区表转为分区表
本文路径：http://lswzjz.com/article/jogpep.html

新闻中心

其他资讯