建站
咨詢
售后
建站咨詢
新聞中心
將非分區(qū)表轉(zhuǎn)化成分區(qū)表幾種實現(xiàn)方式
我們提供的服務(wù)有：做網(wǎng)站、網(wǎng)站建設(shè)、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、德欽ssl等。為超過千家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(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
上面交換時報錯，是因為非分區(qū)表中的數(shù)據(jù)不滿足分區(qū)表中存放條件，這時可以加上without validation選項進行交換。但數(shù)據(jù)在分區(qū)表中的存放與進行分區(qū)時的條件不符合。
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ū)表，則沒有任何記錄。
SQL> select * from tttt;
no rows selected
分區(qū)交換技術(shù)其實是修改數(shù)據(jù)字典來完成操作的，Global索引或涉及到數(shù)據(jù)改動了的global索引分區(qū)會被置為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)機重新定義無主鍵的表 "LINEQI"."TTTT"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 56
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: 在 line 2
通過rowid來重定義表失敗
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表時所產(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
說明:TESTRE是要進行重定義的表，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
其實是交換相應(yīng)對象的object_id,data_object_id
--優(yōu)點：
--保證數(shù)據(jù)的一致性，在大部分時間內(nèi)，表T都可以正常進行DML操作。
--只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。
--而且，可以在切換前進行相應(yīng)的授權(quán)并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。
--
--不足：實現(xiàn)上比上面兩種略顯復(fù)雜,適用于各種情況。
--然而，在線表格重定義也不是完美無缺的。下面列出了Oracle9i重定義過程的部分限制:
--你必須有足以維護兩份表格拷貝的空間。
--你不能更改主鍵欄。
--表格必須有主鍵。
--必須在同一個大綱中進行表格重定義。
--在重定義操作完成之前，你不能對新加欄加以NOT NULL約束。
--表格不能包含LONG、BFILE以及用戶類型（UDT）。
--不能重定義鏈表（clustered tables）。
--不能在SYS和SYSTEM大綱中重定義表格。
--不能用具體化視圖日志（materialized VIEW logs）來重定義表格；不能重定義含有具體化視圖的表格。
--不能在重定義過程中進行橫向分集（horizontal subsetting）
補充分區(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并沒有之后創(chuàng)建好，而是在split時創(chuàng)建的。之前在做split時是手動把partition p_ttpart02分區(qū)表建立好的，結(jié)果做split直接報
下面錯誤。
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
對于某一個分區(qū)中有大量數(shù)據(jù)時，最好在業(yè)務(wù)空閑時間去做,并且split后記得查詢索引狀態(tài)是否有效
分享標(biāo)題：將非分區(qū)表轉(zhuǎn)化成分區(qū)表
文章網(wǎng)址：http://fisionsoft.com.cn/article/pegiop.html
