将非分区表转化成分区表
|
将非分区表转化成分区表几种实现方式 1、insert into 分区表 select * from 非分区表 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', 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,'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、分区交换技术 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,'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 上面交换时报错，是因为非分区表中的数据不满足分区表中存放条件，这时可以加上without validation选项进行交换。但数据在分区表中的存放与进行分区时的条件不符合。 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 下面查询非分区表，则没有任何记录。 SQL> select * from tttt; no rows selected 分区交换技术其实是修改数据字典来完成操作的，Global索引或涉及到数据改动了的global索引分区会被置为unusable，除非附加update indexes子句。 4、在线重定义技术 给用户授权 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','TT_PART'); begin dbms_redefinition.start_redef_table('LINEQI','TT_PART'); end; ORA-12089: 不能联机重新定义无主键的表 "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','TT_PART'); 将TTTT中的数据插入到分区表tt_part表中 PL/SQL procedure successfully completed SQL> exec dbms_redefinition.sync_interim_table('LINEQI','TT_PART');同步将TTTT中的数据插入到分区表tt_part表时所产生的新数据 PL/SQL procedure successfully completed SQL> exec dbms_redefinition.finish_redef_table('LINEQI','TT_PART');结束同步 PL/SQL procedure successfully completed 说明:TESTRE是要进行重定义的表，TTTT是与TESTRE相同表结构的分区表 同步结束之前的情况 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_TTPART01 89233 89320 TABLE PARTITION TTTT P_TTPART02 89234 89321 TABLE PARTITION TTTT P_TTPART03 89235 89322 TABLE PARTITION 同步结束之后的情况 TESTRE 89232 TABLE TESTRE P_TTPART01 89233 89320 TABLE PARTITION TESTRE P_TTPART02 89234 89321 TABLE PARTITION TESTRE P_TTPART03 89235 89322 TABLE PARTITION TTTT 89319 89319 TABLE 其实是交换相应对象的object_id,data_object_id --优点： --保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。 --只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。 --而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。 -- --不足：实现上比上面两种略显复杂,适用于各种情况。 --然而，在线表格重定义也不是完美无缺的。下面列出了Oracle9i重定义过程的部分限制: --你必须有足以维护两份表格拷贝的空间。 --你不能更改主键栏。 --表格必须有主键。 --必须在同一个大纲中进行表格重定义。 --在重定义操作完成之前，你不能对新加栏加以NOT NULL约束。 --表格不能包含LONG、BFILE以及用户类型（UDT）。 --不能重定义链表（clustered tables）。 --不能在SYS和SYSTEM大纲中重定义表格。 --不能用具体化视图日志（materialized VIEW logs）来重定义表格；不能重定义含有具体化视图的表格。 --不能在重定义过程中进行横向分集（horizontal subsetting） 补充分区合并 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 分裂分区 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并没有之后创建好，而是在split时创建的。之前在做split时是手动把partition p_ttpart02分区表建立好的，结果做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 对于某一个分区中有大量数据时，最好在业务空闲时间去做,并且split后记得查询索引状态是否有效 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |