Oracle 將普通錶轉換為分割槽表
DB:11.2.0.30
將普通錶轉換為區分表
一.利用原表重建分割槽表
SQL>create table yoon ( id number primary key ,time date );
Table created.
SQL>insert into yoon select rownum,created from dba_objects;
74930 rows created.
SQL>select count(*) from yoon;
COUNT(*)
----------
74930
SQL>create table yoon_new (ID,TIME) partition by range(time)
(partition p1 values less than (to_date('2011-10-01','YYYY-MM-DD')),
partition p2 values less than (to_date('2012-10-01','YYYY-MM-DD')),
partition p3 values less than (to_date('2013-10-01','YYYY-MM-DD')),
partition p4 values less than (MAXVALUE))
as select id,time from yoon;
Table created.
SQL>select table_owner,table_name,partition_name from dba_tab_partitions where table_name='YOON_NEW';
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
YOON YOON_NEW P1
YOON YOON_NEW P2
YOON YOON_NEW P3
YOON YOON_NEW P4
SQL>alter table yoon rename to yoon_old;
Table altered.
SQL>alter table yoon_new rename to yoon;
Table altered.
SQL>select count(*) from yoon partition(p1);
COUNT(*)
----------
74445
SQL> select count(*) from yoon partition(p2);
COUNT(*)
----------
0
SQL> select count(*) from yoon partition(p3);
COUNT(*)
----------
0
SQL> select count(*) from yoon partition(p4);
COUNT(*)
----------
485
優點:方法簡單、易用,由於採用DDL語句,不會產生UNDO,且只產生少量REDO,效率相對較高,而且建表完成後資料已經在分佈到各個分割槽中了.
缺點:1.適用於修改不頻繁的表,在閒時進行操作,表的資料量不宜太大.
2.表太大,在匯入資料的時候會產生大量的UNDO;非要採用這種方式,將資料分批次匯入.
二.交換分割槽
SQL> create table yoon ( id number primary key,time date ) ;
Table created.
SQL> insert into yoon select rownum,created from dba_objects;
74930 rows created.
SQL> commit;
Commit complete.
SQL> create table yoon_new ( id number primary key,time date ) partition by range(time)
2 (partition p1 values less than (to_date('2015-10-01','YYYY-DD-MM')),
3 partition p2 values less than (maxvalue));
Table created.
SQL> ALTER TABLE YOON_NEW EXCHANGE PARTITION P1 WITH TABLE YOON ;
Table altered.
SQL> select count(*) from yoon;
COUNT(*)
----------
0
SQL> select count(*) from yoon_new;
COUNT(*)
----------
74930
SQL> alter table yoon rename to yoon_old;
Table altered.
SQL> alter table yoon_new rename to yoon;
Table altered.
SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name='YOON';
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
YOON YOON P1
YOON YOON P2
三.線上重定義
SQL> create table yoon ( id number primary key,time date ) ;
Table created.
SQL> insert into yoon select rownum,created from dba_objects;
74930 rows created.
SQL> commit;
Commit complete.
SQL> EXEC DBMS_REDEFINITION.can_redef_table(user,'YOON',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
SQL> create table yoon_new ( id number primary key,time date ) partition by range(time)
2 (partition p1 values less than (to_date('2011-01-01','YYYY-DD-MM')),
3 partition p2 values less than (to_date('2012-01-01','YYYY-DD-MM')),
4 partition p3 values less than (to_date('2013-01-01','YYYY-DD-MM')),
5 partition p4 values less than (maxvalue));
Table created.
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER,'YOON','YOON_NEW','ID ID,TIME TIME',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('YOON','YOON','YOON_NEW');
PL/SQL procedure successfully completed.
S
QL> select table_name from user_tables;
TABLE_NAME
------------------------------
YOON_NEW
YOON
SQL> select count(*) from yoon_new;
COUNT(*)
----------
74930
SQL> select count(*) from yoon;
COUNT(*)
----------
74930
SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name='YOON';
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
YOON YOON P1
YOON YOON P2
YOON YOON P3
YOON YOON P4
線上重定義能夠保證資料的一致性,在大部分時間中,表YOON都可以正常進行DML操作,在切換的瞬間鎖表,具有狠高的可用性,具有狠強的靈活性,能夠滿足各種不同的需求.可以在切換前建立各種約束,做到切換後不用任何額外的管理操作.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28939273/viewspace-1323286/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 將普通錶轉換為分割槽表
- 普通錶轉換為分割槽表
- 【分割槽】如何將一個普通錶轉換為分割槽表
- ORACLE將普通錶轉變為分割槽表方法Oracle
- Oracle線上將普通錶轉分割槽表Oracle
- Oracle Database將普通錶轉換為分割槽表遇到的問題OracleDatabase
- 將非分割槽錶轉換為分割槽表
- 將mysql非分割槽錶轉換為分割槽表MySql
- ORACLE普通錶轉換成分割槽表的操作Oracle
- 將一個非分割槽錶轉換為分割槽表
- ORACLE 普通錶轉換成分割槽表(線上重定義)Oracle
- 批次轉換分割槽表為普通表
- 批量轉換分割槽表為普通表
- 利用線上重定義功能將普通錶轉換為分割槽表問題
- 【ASK_ORACLE】使用insert語句將普通錶轉換成分割槽表Oracle
- 普通錶轉換分割槽表-線上重定義
- ORACLE 普通錶轉換成分割槽表(線上重定義)(zt)Oracle
- 線上表格重定義研究,比如將普通錶轉換為分割槽表等
- 線上重定義 ?普通錶轉換成分割槽表
- 普通錶轉換成分割槽表的四種方法
- 非分割槽錶轉換為分割槽表和partition indexIndex
- 非分割槽錶轉換成分割槽表
- ORACLE 11g 範圍分割槽錶轉換INTERVAL分割槽表Oracle
- 高手幫忙,超大普通錶轉分割槽表?
- 堆錶轉換成分割槽表
- MySQL普通錶轉換成分割槽表的兩種方法舉例MySql
- 使用ORACLE線上重定義將普通表改為分割槽表Oracle
- Oracle普通表修改為分割槽表的方法Oracle
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle
- 非分割槽錶轉換成分割槽表以及注意事項
- 線上重定義的操作步驟__普通錶轉換為分割槽表的實驗案例
- Oracle 12C新特性-線上把非分割槽錶轉為分割槽表Oracle
- oracle將表配置為分割槽表Oracle
- oracle9i 普通表改為分割槽表Oracle
- oracle 普通表-分割槽表改造流程Oracle
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- ORACLE將不同表改為分割槽表Oracle
- 使用dbms_redefinition包線上把大資料量非分割槽錶轉換為分割槽表大資料