Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表
這篇文章主要介紹“Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表”，在日常操作中，相信很多人在Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”O(jiān)racle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),大邑縣企業(yè)網(wǎng)站建設(shè),大邑縣品牌網(wǎng)站建設(shè),網(wǎng)站定制,大邑縣網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,大邑縣網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學習、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。
一：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，相當于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)建測試表插入測試數(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.啟動在線重定義
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.驗證數(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ū)表要相對11G容易了許多，
只需要一條語句即可搞定:ALTER TABLE table_name MODIFY table_partitioning_clauses ......
1.創(chuàng)建測試表入測試數(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ū)表”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
分享標題：Oracle怎么把非分區(qū)表轉(zhuǎn)為分區(qū)表
分享網(wǎng)址：http://muchs.cn/article48/jogpep.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、商城網(wǎng)站、網(wǎng)站設(shè)計、虛擬主機、、品牌網(wǎng)站制作
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 成都小程序推廣——如何區(qū)分小程序的好壞 2017-05-19
- 企業(yè)在成都小程序開發(fā)中需要注意什么 2022-08-25
- 小程序開發(fā)公司哪家好？ 2023-02-08
- 小程序開發(fā)能否撐起微信的大未來，帶給我們?nèi)碌氖澜?/a> 2022-06-25
- 廣州小程序開發(fā)之小程序如何裂變 2022-01-19
- 上海電商小程序開發(fā)前景在哪里？ 2020-12-29
- 微信小程序開發(fā)對線下實體店有何幫助？ 2022-10-31
- 想做小程序開發(fā)，中老年用戶群體的發(fā)掘必不可少 2021-03-04
- 小程序開發(fā)，企業(yè)商家需要考慮的變現(xiàn)問題 2017-03-14
- 旅游行業(yè)注意了?。?！加入小程序，讓你腰包漲漲漲 2013-12-13
- 成都社區(qū)團購小程序開發(fā) 2022-05-29
- 小程序定制費用高不高？該怎么選擇定制公司？ 2015-11-08