Shulou(Shulou.com)06/01 Report--

Several ways to convert non-partitioned tables into partitioned tables

1. Insert into partition table select * from non-partition table

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-100 HH24:mi:ss')) tablespace test

Partition p_ttpart02 values less than (to_date ('2016-09-11 00 HH24:mi:ss')) tablespace test

Partition p_ttpart03 values less than (to_date ('2016-09-12 00 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-100 HH24:mi:ss')) tablespace test

Partition p_ttpart02 values less than (to_date ('2016-09-11 00 HH24:mi:ss')) tablespace test

Partition p_ttpart03 values less than (to_date ('2016-09-12 00 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. Partition switching technology

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-100 HH24:mi:ss')) tablespace test

Partition p_ttpart02 values less than (to_date ('2016-09-11 00 HH24:mi:ss')) tablespace test

Partition p_ttpart03 values less than (to_date ('2016-09-12 00 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

The above exchange times error is because the data in the non-partitioned table does not meet the storage conditions in the partitioned table, so you can add the without validation option to exchange. However, the storage of the data in the partition table does not meet the conditions of the partition.

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

You can see from the following that all the records are stored in 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

The following query for the non-partitioned table does not have any records.

SQL > select * from tttt

No rows selected

Partition exchange technology actually modifies the data dictionary to complete the operation. Global indexes or global index partitions involving altered data will be set to unusable unless the update indexes clause is attached.

4. Online redefinition technology

Authorize the user

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: cannot redefine table "LINEQI". "TTTT" online without primary key

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

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

ORA-06512: in line 2

Failed to redefine the table through rowid

Alter table tttt add constraint pk_id primary key (id)

SQL > exec dbms_redefinition.start_redef_table ('LINEQI','TTTT','TT_PART'); insert data from TTTT into partitioned table tt_part table

PL/SQL procedure successfully completed

SQL > exec dbms_redefinition.sync_interim_table ('LINEQI','TTTT','TT_PART'); synchronize new data generated when data from TTTT is inserted into the partitioned table tt_part table

PL/SQL procedure successfully completed

SQL > exec dbms_redefinition.finish_redef_table ('LINEQI','TTTT','TT_PART'); end synchronization

PL/SQL procedure successfully completed

Note: TESTRE is a table to be redefined, and TTTT is a partitioned table with the same table structure as TESTRE.

The situation before the end of synchronization

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

What happens after the end of synchronization

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

Actually, the object_id,data_object_id of the corresponding object is exchanged.

-- advantages:

To ensure the consistency of the data, Table T can operate DML normally most of the time.

Lock the table only at the moment of switching, with high availability. This method has strong flexibility and can meet a variety of different needs.

-- moreover, the corresponding authorization and various constraints can be established before the switch, so that no additional management operations are needed after the switch is completed.

--

Deficiency: the implementation is slightly more complex than the above two, and it is suitable for all kinds of situations.

However, online table redefinition is not perfect. Some of the limitations of the Oracle9i redefinition process are listed below:

You must have enough space to maintain two copies of the form.

You can't change the main key bar.

The table must have a primary key.

The table must be redefined in the same outline.

You cannot NOT NULL a new column until the redefinition operation is complete.

-- A table cannot contain LONG, BFILE, and user type (UDT).

-you cannot redefine linked lists (clustered tables).

-- tables cannot be redefined in SYS and SYSTEM outlines.

You cannot redefine a table with a materialized view log (materialized VIEW logs); you cannot redefine a table that contains a materialized view.

-- horizontal diversity (horizontal subsetting) cannot be performed during redefinition

Supplementary partition merging

SQL > alter table testre merge partitions pallettpart02pamphetpart03 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 pallettpart02pamphetpart03 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

Split partition

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

Test,partition p_ttpart03)

Table altered.

The above partition p_ttpart02 tablespace test is not created later, but at the time of split. Before doing split, the partition p_ttpart02 partition table was established manually, and the result was reported directly by split.

The following error.

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

When there is a large amount of data in a partition, it is best to do it in the free time of the business, and remember to query whether the index status is valid after split

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.