Oracle 12C新特性-線上把非分割槽錶轉為分割槽表
Oracle 12C 新特性 - 線上把非分割槽錶轉為分割槽表
對比 Oracle 11G 、 12C 線上將非分割槽錶轉換成分割槽表
一：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
--------------- ------------------
PK_T1_ID INDEX
T1 TABLE PARTITION
T1 TABLE PARTITION
T1 TABLE PARTITION
T1 TABLE PARTITION
歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2675347/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 非分割槽錶轉換成分割槽表
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle
- PG的非分割槽表線上轉分割槽表
- oracle分割槽表和分割槽表exchangeOracle
- 非分割槽錶轉換成分割槽表以及注意事項
- oracle 19C新特性——混合分割槽表Oracle
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- ORACLE 19c 新特性之混合分割槽表Oracle
- MySQL線上轉分割槽表（以及TiDB）MySqlTiDB
- oracle將表配置為分割槽表Oracle
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- ORACLE分割槽表梳理系列Oracle
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- 【12.2】Oracle 12C R2新特性-外部表支援分割槽了（Partitioning External Tables）Oracle
- Linux 分割槽擴容（根分割槽擴容，SWAP 分割槽擴容，掛載新分割槽為目錄）Linux
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- 【ASK_ORACLE】使用insert語句將普通錶轉換成分割槽表Oracle
- oracle 普通表-分割槽表改造流程Oracle
- 線上重定義與普通表改為分割槽表
- 線上重定義的操作步驟__普通錶轉換為分割槽表的實驗案例
- 【MYSQL】 分割槽表MySql
- 線上重定義方式將普通表修改為分割槽表
- 對oracle分割槽表的理解整理Oracle
- Oracle SQL調優之分割槽表OracleSQL
- Oracle12c：建立主分割槽、子分割槽，實現自動分割槽插入效果Oracle
- 移動分割槽表和分割槽索引的表空間索引