將非分區(qū)表轉(zhuǎn)化成分區(qū)表幾種實(shí)現(xiàn)方式

我們提供的服務(wù)有：做網(wǎng)站、網(wǎng)站建設(shè)、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、德欽ssl等。為超過(guò)千家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的德欽網(wǎng)站制作公司

1、insert into 分區(qū)表 select * from 非分區(qū)表

SQL> select * from ttpart;

        ID V_DATE

---------- -------------------

         1 2016-09-11 14:23:46

         1 2016-09-10 14:23:55

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

create table tt_part(id number,v_date date)

partition by range(v_Date)

(

 partition p_ttpart01 values less than (to_date('2016-09-10 00:00:00','yyyy-mm-dd HH24:mi:ss'))  tablespace test,

  partition p_ttpart02 values less than (to_date('2016-09-11 00:00:00','yyyy-mm-dd HH24:mi:ss')) tablespace test,

 partition p_ttpart03 values less than (to_date('2016-09-12 00:00:00','yyyy-mm-dd HH24:mi:ss')) tablespace test

)

;

SQL> insert /*+ append */ into tt_part select * from ttpart;

4 rows created.

SQL> select * from tt_part;

        ID V_DATE

---------- -------------------

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

         1 2016-09-10 14:23:55

         1 2016-09-11 14:23:46

SQL>  select * from tt_part partition(p_ttpart01);

        ID V_DATE

---------- -------------------

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

2、expdp/impdp

SQL> select * from tttt;

        ID V_DATE

---------- -------------------

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

         1 2016-09-10 14:23:55

         1 2016-09-11 14:23:46

create table tt_part(id number,v_date date)

partition by range(v_Date)

(

 partition p_ttpart01 values less than (to_date('2016-09-10 00:00:00','yyyy-mm-dd HH24:mi:ss'))  tablespace test,

  partition p_ttpart02 values less than (to_date('2016-09-11 00:00:00','yyyy-mm-dd HH24:mi:ss')) tablespace test,

 partition p_ttpart03 values less than (to_date('2016-09-12 00:00:00','yyyy-mm-dd HH24:mi:ss')) tablespace test

)

;

[oracle@orcl impdp]$ expdp lineqi/lineqi directory=impdp_dir dumpfile=lineqi_tttt.dmp tables=\(TTTT\)

[oracle@orcl impdp]$ impdp lineqi/lineqi directory=impdp_dir dumpfile=lineqi_tttt.dmp REMAP_TABLE=lineqi.tttt:lineqi:tt_part TABLE_EXISTS_ACTION=append;

SQL> select * from tt_part;

        ID V_DATE

---------- -------------------

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

         1 2016-09-10 14:23:55

         1 2016-09-11 14:23:46

SQL>  select * from tt_part partition(p_ttpart01);

        ID V_DATE

---------- -------------------

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

SQL> select * from tt_part partition(p_ttpart02);

        ID V_DATE

---------- -------------------

         1 2016-09-10 14:23:55

3、分區(qū)交換技術(shù)

SQL> select * from tttt;

        ID V_DATE

---------- -------------------

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

         1 2016-09-10 14:23:55

         1 2016-09-11 14:23:46

create table tt_part(id number,v_date date)

partition by range(v_Date)

(

 partition p_ttpart01 values less than (to_date('2016-09-10 00:00:00','yyyy-mm-dd HH24:mi:ss'))  tablespace test,

  partition p_ttpart02 values less than (to_date('2016-09-11 00:00:00','yyyy-mm-dd HH24:mi:ss')) tablespace test,

 partition p_ttpart03 values less than (to_date('2016-09-12 00:00:00','yyyy-mm-dd HH24:mi:ss')) tablespace test

)

;

SQL> select table_name,partition_name from user_tab_partitions where table_name='TT_PART';

TABLE_NAME                 PARTITION_NAME

-------------------- -----------------------

TT_PART                      P_TTPART01

TT_PART                      P_TTPART02

TT_PART                      P_TTPART03

SQL> alter table tt_part exchange partition P_TTPART03  with table tttt;

alter table tt_part exchange partition P_TTPART03  with table tttt

                                                              *

ERROR at line 1:

ORA-14099: all rows in table do not qualify for specified partition

上面交換時(shí)報(bào)錯(cuò)，是因?yàn)榉欠謪^(qū)表中的數(shù)據(jù)不滿足分區(qū)表中存放條件，這時(shí)可以加上without validation選項(xiàng)進(jìn)行交換。但數(shù)據(jù)在分區(qū)表中的存放與進(jìn)行分區(qū)時(shí)的條件不符合。

SQL> alter table tt_part exchange partition P_TTPART03  with table tttt without validation;

Table altered.

SQL> select * from tt_part;

        ID V_DATE

---------- -------------------

         1 2016-09-11 14:23:46

         1 2016-09-10 14:23:55

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

SQL> select * from tt_part partition(P_TTPART02);

no rows selected

從下面可以看出所有的記錄都存放在P_TTPART03

SQL>  select * from tt_part partition(P_TTPART03);

        ID V_DATE

---------- -------------------

         1 2016-09-11 14:23:46

         1 2016-09-10 14:23:55

         1 2016-09-09 14:24:01

         1 2016-09-08 14:24:06

下面查詢非分區(qū)表，則沒(méi)有任何記錄。

SQL> select * from tttt;

no rows selected

分區(qū)交換技術(shù)其實(shí)是修改數(shù)據(jù)字典來(lái)完成操作的，Global索引或涉及到數(shù)據(jù)改動(dòng)了的global索引分區(qū)會(huì)被置為unusable，除非附加update indexes子句。

4、在線重定義技術(shù)

給用戶授權(quán)

SQL>GRANT CREATE SESSION, CREATE ANY TABLE,ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE  ,SELECT ANY TABLE,CREATE ANY INDEX,CREATE ANY TRIGGER TO lineqi;

SQL> GRANT EXECUTE_CATALOG_ROLE TO lineqi;

SQL> exec dbms_redefinition.can_redef_table('LINEQI','TTTT',dbms_redefinition.cons_use_rowid);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_redefinition.start_redef_table('LINEQI','TTTT','TT_PART');

 

begin dbms_redefinition.start_redef_table('LINEQI','TTTT','TT_PART'); end;

 

ORA-12089: 不能聯(lián)機(jī)重新定義無(wú)主鍵的表 "LINEQI"."TTTT"

ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 56

ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1498

ORA-06512: 在 line 2

通過(guò)rowid來(lái)重定義表失敗

alter table tttt add constraint pk_id primary key (id)

 

SQL> exec dbms_redefinition.start_redef_table('LINEQI','TTTT','TT_PART'); 將TTTT中的數(shù)據(jù)插入到分區(qū)表tt_part表中

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_redefinition.sync_interim_table('LINEQI','TTTT','TT_PART');同步將TTTT中的數(shù)據(jù)插入到分區(qū)表tt_part表時(shí)所產(chǎn)生的新數(shù)據(jù)

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_redefinition.finish_redef_table('LINEQI','TTTT','TT_PART');結(jié)束同步

 

PL/SQL procedure successfully completed

說(shuō)明:TESTRE是要進(jìn)行重定義的表，TTTT是與TESTRE相同表結(jié)構(gòu)的分區(qū)表

同步結(jié)束之前的情況

select * from user_objects t where t.OBJECT_NAME in ('TTTT','TESTRE')

OBJECT_NAME      SUBOBJECT_NAME  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE 

TESTRE 89319   89319 TABLE

TTTT 89232 T TABLE

TTTT P_TTPART0189233 89320 TABLE PARTITION

TTTT P_TTPART0289234 89321 TABLE PARTITION

TTTT P_TTPART0389235 89322 TABLE PARTITION

同步結(jié)束之后的情況

TESTRE 89232 TABLE

TESTRE P_TTPART0189233 89320 TABLE PARTITION

TESTRE P_TTPART0289234 89321 TABLE PARTITION

TESTRE P_TTPART0389235 89322 TABLE PARTITION

TTTT 89319 89319 TABLE

其實(shí)是交換相應(yīng)對(duì)象的object_id,data_object_id

--優(yōu)點(diǎn)：

--保證數(shù)據(jù)的一致性，在大部分時(shí)間內(nèi)，表T都可以正常進(jìn)行DML操作。

--只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強(qiáng)的靈活性，對(duì)各種不同的需要都能滿足。

--而且，可以在切換前進(jìn)行相應(yīng)的授權(quán)并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。

--

--不足：實(shí)現(xiàn)上比上面兩種略顯復(fù)雜,適用于各種情況。

--然而，在線表格重定義也不是完美無(wú)缺的。下面列出了Oracle9i重定義過(guò)程的部分限制:

--你必須有足以維護(hù)兩份表格拷貝的空間。

--你不能更改主鍵欄。

--表格必須有主鍵。

--必須在同一個(gè)大綱中進(jìn)行表格重定義。

--在重定義操作完成之前，你不能對(duì)新加欄加以NOT NULL約束。

--表格不能包含LONG、BFILE以及用戶類型（UDT）。

--不能重定義鏈表（clustered tables）。

--不能在SYS和SYSTEM大綱中重定義表格。

--不能用具體化視圖日志（materialized VIEW logs）來(lái)重定義表格；不能重定義含有具體化視圖的表格。

--不能在重定義過(guò)程中進(jìn)行橫向分集（horizontal subsetting）

補(bǔ)充分區(qū)合并

SQL> alter table testre merge partitions p_ttpart02,p_ttpart03 into partition p_ttpart02;

alter table testre merge partitions p_ttpart02,p_ttpart03 into partition p_ttpart02

                                    *

ERROR at line 1:

ORA-14275: cannot reuse lower-bound partition as resulting partition

SQL> alter table testre merge partitions p_ttpart02,p_ttpart03 into partition p_ttpart03;

Table altered.

SQL> select table_name,partition_name from user_tab_partitions where table_name='TESTRE';

TABLE_NAME                  PARTITION_NAME

--------------------- ---------------------

TESTRE                      P_TTPART01

TESTRE                      P_TTPART03

分裂分區(qū)

SQL> alter table testre split partition P_TTPART03 at (to_date('2016-09-11 00:00:00','yyyy-mm-dd HH24:mi:ss')) into (partition p_ttpart02 tablespace 

test,partition p_ttpart03);

Table altered.

上面partition p_ttpart02 tablespace test并沒(méi)有之后創(chuàng)建好，而是在split時(shí)創(chuàng)建的。之前在做split時(shí)是手動(dòng)把partition p_ttpart02分區(qū)表建立好的，結(jié)果做split直接報(bào)

下面錯(cuò)誤。

ORA-14080: partition cannot be split along the specified high bound

SQL> col table_name for a35

SQL> col partition_name for a40

SQL> select table_name,partition_name from user_tab_partitions where table_name='TESTRE';

TABLE_NAME                          PARTITION_NAME

----------------------------------- ----------------------------------------

TESTRE                              P_TTPART01

TESTRE                              P_TTPART02

TESTRE                              P_TTPART03

SQL> select * from testre partition(p_ttpart03);

        ID V_DATE

---------- -------------------

         1 2016-09-11 14:23:46

SQL>  select * from testre partition(p_ttpart02);

        ID V_DATE

---------- -------------------

         2 2016-09-10 14:23:55

對(duì)于某一個(gè)分區(qū)中有大量數(shù)據(jù)時(shí)，最好在業(yè)務(wù)空閑時(shí)間去做,并且split后記得查詢索引狀態(tài)是否有效

當(dāng)前名稱：將非分區(qū)表轉(zhuǎn)化成分區(qū)表
文章位置：http://aaarwkj.com/article8/pegiop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、云服務(wù)器、服務(wù)器托管、微信小程序、域名注冊(cè)、定制開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)