oracle-Online redefine Operation document

oracle-Online redefine Operation document

October 8, 2015

15:51

The approximate operating procedure for online redefinition is as follows:

(1) Create the underlying table A, if present, no action is required.

(2) Create a temporary partition Table B.

(3) Start redefining, import the data from base table A into temporary partition table B.

(4) End the redefinition, at which point 2 tables have been swapped in the Name directory of the DB. That is, the base table A is a partitioned table, and the temporary partition Table B we created becomes the normal table. At this point we can delete the temporary table B we created. It is already a regular table.

Let's look at an example:

1. Creating Basic tables and indexes

Sql> CREATE TABLE unpar_table (ID number (TEN) primary key,create_date date); Table created. sql> INSERT INTO unpar_table Select RowNum, created from Dba_objects; 72491 rows created. Sql> CREATE index Create_date_ind on unpar_table (create_date); Index created. Sql> commit; Commit complete.

2. Collecting statistics for a table

sql> exec dbms_stats.gather_table_stats (ownname=> ' dev ',tabname=> ' unpar_table ', cascade = TRUE); PL/SQL procedure successfully completed.

3. Create a temporary partition table

Sql> CREATE TABLE par_table (ID number primary key, time date) partition by range (time) (partition P1 values less than (To_date (' 2004-7-1 ', ' yyyy-mm-dd ')), partition P2 values less than (to_date (' 2005-1-1 ', ' yyyy-mm-dd '), partition P3 Values less than (to_date (' 2005-7-1 ', ' yyyy-mm-dd '), partition P4 values less than (MaxValue)); Table created.

4. Redefining the operation

4.1 Checking the reasonableness of the redefinition

sql> exec dbms_redefinition.can_redef_table (uname=> ' dev ',tname=> ' unpar_table '); PL/SQL procedure successfully completed.

4.2 If 4.1 does not have a problem, start redefining, this process may have to wait a while.

Note here: If the partition table and the original table column names are the same, you can do this in the following way:

Sql> BEGIN Dbms_redefinition.start_redef_table ( uname = ' Dev ', orig_table = ' unpar_table ', int_table = ' par_table '); End / BEGIN * ERROR at line 1: Ora-42016:shape of interim table does not match specified column mapping Ora-06512:at "SYS. Dbms_redefinition ", line 52 Ora-06512:at "SYS. Dbms_redefinition ", line 1646 Ora-06512:at Line 2 If the column name of the partition table is inconsistent with the original table, the mapping relationship needs to be re-specified at the beginning of the redefinition: Sql> EXEC dbms_redefinition. Start_redef_table (' Dev ', ' unpar_table ', ' par_table ', ' ID ID, create_date time ', dbms_redefinition. CONS_USE_PK); PL/SQL procedure successfully completed. Once this is done, the data is synchronized to the temporary partition table.

4.3 Synchronizing the new table, which is an optional operation

Sql> begin Dbms_redefinition.sync_interim_table ( uname = ' DEV ', orig_table = ' unpar_table ', int_table = ' par_table '); End / PL/SQL procedure successfully completed.

4.4 Create an index, the online redefinition only redefine the data, and the index needs to be established separately.

Sql> CREATE index Create_date_ind2 on par_table (time); Index created.

4.5 Collecting statistics for new tables

sql> exec dbms_stats.gather_table_stats (ownname=> ' dev ',tabname=> ' par_table ', cascade = TRUE); PL/SQL procedure successfully completed.

4.6 End Redefine

Sql> begin Dbms_redefinition.finish_redef_table ( uname = ' Dev ', orig_table = ' unpar_table ', int_table = ' par_table '); End / PL/SQL procedure successfully completed. Meaning to end the redefinition: The base table unpar_table and the temporary partition table par_table are exchanged. At this point the temporary partition table par_table into a normal table, and our base table unpar_table into a partitioned table. When we redefine, the base table unpar_table is capable of DML operations. Only a short lock table will be available when the 2 tables are switched.

5. Delete temporary tables

sql> DROP TABLE par_table; The table has been deleted.

6. Index renaming

sql> ALTER INDEX create_date_ind2 RENAME to Create_date_ind; The index has changed.

7. Verification

Sql> Select partitioned from user_tables where table_name = ' unpar_table '; PAR --- YES Sql> Select Partition_name from user_tab_partitions where table_name = ' unpar_table '; Partition_name ------------------------------ P1 P2 P3 P4 Sql> Select COUNT (*) from unpar_table; COUNT (*) ---------- 72500 Sql> Select COUNT (*) from unpar_table partition (P4); COUNT (*) ---------- 72500 Sql> Select COUNT (*) from par_table; COUNT (*) ---------- 72500 Sql> Select COUNT (*) from unpar_table partition (P1); COUNT (*) ---------- 0 Sql> Select COUNT (*) from unpar_table partition (P2); COUNT (*) ---------- 0

Created with Microsoft OneNote 2010
A place to hold all your notes and information

oracle-Online redefine Operation document