Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表

148次閱讀

共計(jì) 6675 個(gè)字符，預(yù)計(jì)需要花費(fèi) 17 分鐘才能閱讀完成。

這篇文章主要介紹“Oracle 怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表”，在日常操作中，相信很多人在 Oracle 怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”O(jiān)racle 怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表”的疑惑有所幫助！接下來，請(qǐng)跟著丸趣 TV 小編一起來學(xué)習(xí)吧！

一：oracle 11G 在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表

Online Redefinition

二：Oracle 12C
在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表

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 將非分區(qū)表轉(zhuǎn)換為分區(qū)表

在線重定義 Online Redefinition

1. 數(shù)據(jù)庫版本

— 數(shù)據(jù)庫 19C，相當(dāng)于 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. 創(chuàng)建測(cè)試表插入測(cè)試數(shù)據(jù)

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. 啟動(dòng)在線重定義

SQL exec dbms_redefinition.start_redef_table(CJC , T1 , T1_TEMP

PL/SQL procedure successfully completed.

6. 檢查中間表數(shù)據(jù)

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. 模擬生產(chǎn)環(huán)境數(shù)據(jù)變化

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. 中間表同步數(shù)據(jù)

SQL exec dbms_redefinition.sync_interim_table(CJC , T1 , T1_TEMP

PL/SQL procedure successfully completed.

查詢同步后數(shù)據(jù)：

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. 結(jié)束在線重定義

SQL exec dbms_redefinition.finish_redef_table(CJC , T1 , T1_TEMP

PL/SQL procedure successfully completed.

11. 驗(yàn)證數(shù)據(jù)

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. 查看各分區(qū)數(shù)據(jù)

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
將非分區(qū)表轉(zhuǎn)換為分區(qū)表

在 12C 中在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表要相對(duì) 11G 容易了許多，

只需要一條語句即可搞定:ALTER TABLE table_name MODIFY table_partitioning_clauses ……

1. 創(chuàng)建測(cè)試表入測(cè)試數(shù)據(jù)

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. 在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表，索引轉(zhuǎn)換成全局索引

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

————— ——————

到此，關(guān)于“Oracle 怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！

正文完