Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Oracle how to turn a non-partition table into a partition table". In daily operation, I believe many people have doubts about how to turn a non-partition table into a partition table in Oracle. The editor consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubts of "how to convert a non-partition table into a partition table by Oracle". Next, please follow the editor to study!

One: oracle 11G converts non-partitioned tables to partitioned tables online

Online Redefinition

Second: Oracle 12C converts non-partitioned tables to partitioned tables online

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}] )

]

]

One: oracle 11G converts non-partitioned tables to partitioned tables

Redefine Online Redefinition online

1. Database version

-Database 19C, equivalent to version 12.2.0.3, also supports 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

two。 Create a test table to insert test data

SQL > conn cjc/cjc@cjcpdb

SQL > create table T1 (id number,adr varchar2, acc number)

SQL >

Insert into T1 values (1 recording dapuchaiyin 10)

Insert into T1 values (2 recordings dunhuajie 20)

Insert into T1 values (3 recordings Xiaopuchaiqiao 30)

Insert into T1 values (4 recordings fuerhedong page101)

Insert into T1 values (5 recordings fuyuanjiequ 130)

Insert into T1 values (6 people songyuanjieqi 125)

Insert into T1 values (7 recordsbajiazhiyuan 166)

Insert into T1 values

Insert into T1 values (9 recordings hanconggouth256)

Insert into T1 values

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. Check to see if this table can be redefined online

-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. Establish the intermediate tables required for online redefinition

SQL >

Create table t1_temp (id number,adr varchar2, 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. Start online redefinition

SQL > exec dbms_redefinition.start_redef_table ('CJC',' T1ms, 'T1s temps')

PL/SQL procedure successfully completed.

6. Check the intermediate table data

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. Simulated production environment data change

SQL > insert into T1 values

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. The original table was modified, but the intermediate table was not updated.

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. Intermediate table synchronization data

SQL > exec dbms_redefinition.sync_interim_table ('CJC',' T1ms, 'T1s temps')

PL/SQL procedure successfully completed.

Query the synchronized data:

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. End online redefinition

SQL > exec dbms_redefinition.finish_redef_table ('CJC',' T1ms, 'T1s temps')

PL/SQL procedure successfully completed.

11. Validate data

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.

twelve。 View data for each partition

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. Check and delete the intermediate table

SQL > drop table t1_temp purge

Table dropped.

Two: Oracle 12C converts non-partitioned tables to partitioned tables

Converting a non-partitioned table to a partitioned table online in 12C is much easier than 11G.

It can be done with only one sentence: ALTER TABLE table_name MODIFY table_partitioning_clauses.

1. Create a test table to enter the test data

SQL > conn cjc/cjc@cjcpdb

-drop table T1 purge

SQL > create table T1 (id number,adr varchar2, acc number)

Insert into T1 values (1 recording dapuchaiyin 10)

Insert into T1 values (2 recordings dunhuajie 20)

Insert into T1 values (3 recordings Xiaopuchaiqiao 30)

Insert into T1 values (4 recordings fuerhedong page101)

Insert into T1 values (5 recordings fuyuanjiequ 130)

Insert into T1 values (6 people songyuanjieqi 125)

Insert into T1 values (7 recordsbajiazhiyuan 166)

Insert into T1 values

Insert into T1 values (9 recordings hanconggouth256)

Insert into T1 values

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

two。 Convert non-partitioned tables to partitioned tables online, and indexes to global indexes

SQL >

Alter table t1 modify

Partition by range (acc)

(partition PAR01 values less than)

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. Check

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

--

At this point, the study on "how to convert a non-partition table into a partition table by Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.