Today, when doing data extraction, we find that there is a data volume of business table reached 5000W, so we want to change this table to partition table. The partition table is a bit like this:
1. Improve query performance: Queries on partitioned objects can search only the partitions they care about and improve the retrieval speed.
2. Enhanced usability: If one partition of the table fails, the data of the table in other partitions is still available;
3, Maintenance Convenience: If a partition of the table fails, you need to repair the data, only repair the partition;
4. Balanced I/O: Different partitions can be mapped to disk to balance I/O and improve overall system performance.
Step One: First verify the table you want to redefine online, see if the table can be redefined, and if not, you will be prompted with an error message
Sql> Execute dbms_redefinition.can_redef_table (' CDR ', ' hdsd00_0203_zyfymxb ');
PL/SQL procedure successfully completed.
Step two: Create the same partition table as the source table. The built-in statement is too long to be pasted here, this step can be registered with the PL/SQL tool to build the table, to avoid the sqlplus environment under the SYS user, resulting in the table cannot be found.
Step three: perform the online redefinition of the table;
Sql> Execute dbms_redefinition.start_redef_table (' CDR ', ' hdsd00_0203_zyfymxb ', ' Hdsd00_0203_zyfymxb_bak ');
PL/SQL procedure successfully completed.
Step four: Perform data synchronization between the intermediate table and the data source table.
Sql> Execute dbms_redefinition.sync_interim_table (' CDR ', ' hdsd00_0203_zyfymxb ', ' Hdsd00_0203_zyfymxb_bak ');
PL/SQL procedure successfully completed.
Step five: Execution ends the online definition process.
Sql> Execute dbms_redefinition.finish_redef_table (' CDR ', ' hdsd00_0203_zyfymxb ', ' Hdsd00_0203_zyfymxb_bak ');
PL/SQL procedure successfully completed. Supplemental steps: Step Sixth: During the online redefinition process, there may be a variety of reasons for not following the above steps, interrupted. In this case, an error similar to the following is reported in the execution of the preceding operation:
Sql> Execute dbms_redefinition.can_redef_table (' CDR ', ' hdsd00_0203_zyfymxb ');
BEGIN dbms_redefinition.can_redef_table (' CDR ', ' hdsd00_0203_zyfymxb '); END;
*
ERROR at line 1:
Ora-23539:table "CDR". " HDSD00_0203_ZYFYMXB "currently being redefined
Ora-06512:at "SYS. Dbms_redefinition ", line 143
Ora-06512:at "SYS. Dbms_redefinition ", line 1635
Ora-06512:at Line 1
At this point we need to stop the process to come back.
Sql> Execute dbms_redefinition.abort_redef_table (' CDR ', ' hdsd00_0203_zyfymxb ', ' Hdsd00_0203_zyfymxb_bak ');
PL/SQL procedure successfully completed.
There is another situation when our table data is large, you may perform the above procedure will not be an error and no response, this is the system is performing the above process, do not quit, wait patiently for a while.
Online redefinition is also limited, and online redefinition cannot be used in the following cases:
1, you must have enough space to maintain two copies of the form.
2. You cannot change the primary key bar. The table must have a primary key.
3, Maintenance Convenience: If a partition of the table fails, you need to repair the data, only repair the partition; You must redefine the table in the same outline.
4. You cannot have a NOT NULL constraint on new columns until the redefine operation is complete.
5. The table cannot contain long, bfile, and user types (UDTs).
6. The linked list (clustered tables) cannot be redefined.
7. The table cannot be redefined in SYS and the system outline.
8. You cannot redefine a table with materialized view logs (materialized view logs), and you cannot redefine a table that contains materialized views.
9. You cannot perform a lateral diversity (horizontal subsetting) during the redefinition process.
--------------------------------------------------------------------------------------------------------------- -----------------------------------
For more details please see the introduction (reprint):
In a highly available system, if you need to change the definition of a table is a tricky issue, especially for 7x24 systems. The basic syntax provided by Oracle basically satisfies general modifications, but it is not possible to change an index organization table to a heap table when the normal heap table is changed to a partitioned table. And, fortunately for tables accessed by a large number of DML statements, Oracle provides an online redefinition table feature starting with the 9i release, and by invoking the Dbms_redefinition package, you can modify the table structure while allowing DML operations.
The online redefine table has the following features:
1. Modify the table storage parameters;
2. You can transfer a table to another table space;
3. Add parallel query options;
4. Add or delete partitions;
5. Rebuilding the table to reduce fragmentation;
6. Change the heap table to an indexed organization table or vice versa;
7. Add or remove a column.
Calling the Dbms_redefinition package requires the Execute_catalog_role role, in addition to the Create any table, ALTER any table, DROP any table, and LOCK any Permissions for table and select any table.
The steps to redefine the table online are as follows:
1. Select a redefinition method:
There are two methods of redefinition, one based on the primary key and the other based on rowID. The rowID method cannot be used to index an organization table, and there is a hidden column m_row$$ when redefined. The default is the way the primary key is used.
2. Call Dbms_redefinition. The can_redef_table () process, if the table does not meet the redefined conditions, will be an error and give the reason.
3. Create an empty intermediate table in one scenario and create an intermediate table based on the structure you expect to get after redefining. For example: Using the partition table, add the column and so on.
4. Call Dbms_redefinition. The start_redef_table () procedure and provides the following parameters: the name of the table being redefined, the name of the intermediate table, the mapping rule for the column, and the redefinition method.
If the mapping method is not provided, all columns included in the intermediate table are considered to be used for the redefinition of the table. If the mapping method is given, only the columns given in the mapping method are considered. If the redefinition method is not given, the primary key method is considered to be used.
5. Create triggers, indexes, and constraints on the intermediate tables and authorize them accordingly. Any integrity constraint that contains an intermediate table should have the state set to disabled.
When the redefinition is complete, triggers, indexes, constraints, and authorizations established on the intermediate table replace the triggers, indexes, constraints, and authorizations on the redefined table. The constraint on the disabled on the intermediate table will be enable on the redefined table.
6. (optional) if dbms_redefinition is executed. Start_redef_table () process and execution dbms_redefinition. The finish_redef_table () process performs a large number of DML operations directly on the redefined table, so you can choose to perform one or more sync_interim_table () processes to reduce the last step of execution finish_redef_table () The time at which the procedure was locked.
7. Execute dbms_redefinition. The finish_redef_table () process completes the redefinition of the table. In this process, the original table is locked for a short period of time by exclusive mode, which is related to the amount of data in the table. After the finish_redef_table () procedure is executed, the original table is redefined with the properties, indexes, constraints, authorizations, and triggers of the intermediate table. The disabled constraint on the intermediate table is enabled on the original table.
8. (optional) You can rename indexes, triggers, and constraints. For tables that have been redefined in the ROWID way, an implicit column m_row$$ is included. It is recommended to use the following statements to set the unused state or delete by implication.
ALTER TABLE table_name SET UNUSED (m_row$$);
ALTER TABLE table_name DROP UNUSED COLUMNS;
The following is the result of a redefinition operation:
The original table is redefined based on the attributes and attributes of the intermediate table;
The triggers, indexes, constraints, and authorizations established between the start_redef_table () and finish_redef_table () operations on the intermediate table are now defined on the original table. The disabled constraint on the intermediate table is enabled on the original table.
Triggers, indexes, constraints, and authorizations defined on the original table are built on the intermediate table and are deleted when the intermediate table is deleted. The index of the original enabled state on the Origin table, established on the intermediate table, and is in the disabled state.
Any stored procedures and cursors that are defined on the original table become invalid and are compiled automatically when the next call is made.
Dbms_redefinition can be executed if an error occurs during execution or if an artificial opt-out is made. Abort_redef_table () process.
Where the uname parameter refers to the user;
There is no way for Oracle's normal table to be converted directly into a partitioned table by modifying the properties, and must be transformed by the way it is rebuilt, with three more efficient methods described below, and their respective characteristics.
Method One: Use the original table to reconstruct the partition table.
Steps:
Sql> CREATE TABLE T (ID number PRIMARY KEY, time DATE);
The table is created.
Sql> INSERT into T SELECT ROWNUM, CREATED from Dba_objects;
6264 rows have been created.
Sql> COMMIT;
Submit complete.
Sql> CREATE TABLE t_new (ID, Time) PARTITION by RANGE (time)
2 (PARTITION P1 VALUES less THAN (to_date (' 2004-7-1 ', ' yyyy-mm-dd ')),
3 PARTITION P2 VALUES less THAN (to_date (' 2005-1-1 ', ' yyyy-mm-dd ')),
4 PARTITION P3 VALUES less THAN (to_date (' 2005-7-1 ', ' yyyy-mm-dd ')),
5 PARTITION P4 VALUES less THAN (MAXVALUE))
6 as SELECT ID, time from T;
The table is created.
Sql> RENAME T to T_old;
The table has been renamed.
Sql> RENAME t_new to T;
The table has been renamed.
Sql> SELECT COUNT (*) from T;
COUNT (*)
----------
6264
Sql> SELECT COUNT (*) from T PARTITION (P1);
COUNT (*)
----------
0
Sql> SELECT COUNT (*) from T PARTITION (P2);
COUNT (*)
----------
6246
Sql> SELECT COUNT (*) from T PARTITION (P3);
COUNT (*)
----------
18
Advantage: The method is simple and easy to use, because the use of DDL statements does not produce undo, and only a small amount of redo, the efficiency is relatively high, and the completion of the table after the data is distributed in the various partitions.
Insufficient: Additional considerations are required for consistency of data. Since there is almost no way to ensure consistency by manually locking the T-table, the direct modification of the CREATE TABLE statement and the rename t_new to T statement may be lost, and if consistency is to be ensured, the data will be checked after the statement is executed, and the cost is relatively large. In addition, access to t that executes between two rename statements fails.
It is suitable for the non-frequent table, in the idle operation, the table data volume should not be too large.
Method Two: The method of using the swap partition.
Steps:
Sql> CREATE TABLE T (ID number PRIMARY KEY, time DATE);
The table is created.
Sql> INSERT into T SELECT ROWNUM, CREATED from Dba_objects;
6264 rows have been created.
Sql> COMMIT;
Submit complete.
Sql> CREATE TABLE t_new (ID number PRIMARY KEY, Time DATE) PARTITION by RANGE (time)
2 (PARTITION P1 VALUES less THAN (to_date (' 2005-7-1 ', ' yyyy-mm-dd ')),
3 PARTITION P2 VALUES less THAN (MAXVALUE));
The table is created.
sql> ALTER table T_new EXCHANGE PARTITION P1 with table T;
The table has changed.
Sql> RENAME T to T_old;
The table has been renamed.
Sql> RENAME t_new to T;
The table has been renamed.
Sql> SELECT COUNT (*) from T;
COUNT (*)
----------
6264
Advantages: Only the definition of partitions and tables in the data dictionary has been modified, and no data has been modified or copied, the most efficient. If there is no further requirement for the distribution of data in the partition, the implementation is relatively straightforward. After performing the rename operation, you can check if there is data in the t_old, and if so, insert the data directly into T, and you can guarantee that the operation for T insertion will not be lost.
Insufficient: There is still a consistency issue, and before the swap partition rename t_new to T, queries, updates, and deletions can cause errors or access to data. If data is required to be distributed across multiple partitions, split operations that require partitioning can increase the complexity of operations and reduce efficiency.
Applies to the operation of a table that contains large amounts of data to go to a partition in a partitioned table. You should try to operate at leisure.
Method Three: Oracle9i above, using the online redefinition function
Steps:
Sql> CREATE TABLE T (ID number PRIMARY KEY, time DATE);
The table is created.
Sql> INSERT into T SELECT ROWNUM, CREATED from Dba_objects;
6264 rows have been created.
Sql> COMMIT;
Submit complete.
Sql> EXEC dbms_redefinition. Can_redef_table (USER ', ' T ', dbms_redefinition. CONS_USE_PK);
The PL/SQL process has completed successfully.
Sql> CREATE TABLE t_new (ID number PRIMARY KEY, Time DATE) PARTITION by RANGE (time)
2 (PARTITION P1 VALUES less THAN (to_date (' 2004-7-1 ', ' yyyy-mm-dd ')),
3 PARTITION P2 VALUES less THAN (to_date (' 2005-1-1 ', ' yyyy-mm-dd ')),
4 PARTITION P3 VALUES less THAN (to_date (' 2005-7-1 ', ' yyyy-mm-dd ')),
5 PARTITION P4 VALUES less THAN (MAXVALUE));
The table is created.
Sql> EXEC dbms_redefinition. Start_redef_table (' USER ', ' T ', ' t_new ',-
> ' ID ID, time time ', dbms_redefinition. CONS_USE_PK);
Can be changed to:
Sql> EXEC dbms_redefinition. Start_redef_table (' USER ', ' T ', ' t_new ')
The PL/SQL process has completed successfully.
sql> EXEC dbms_redefinition.sync_interim_table (' USER ', ' T ', ' t_new ')
Now, synchronize the intermediate table with the original table. (You need to do this only if you want to update the table T.) ）
Sql> EXEC dbms_redefinition. Finish_redef_table (USER ', ' T ', ' t_new ');
The PL/SQL process has completed successfully.
If the organization fails, then you have to take special steps to get it started again. Because the redefinition process requires you to create a snapshot of the table, you must call Dbms_redefinition in order to restart the process. Abort_redef_table to release the snapshot.
Dbms_redefinition. The abort_redef_table process has three parameters, that is, the user (schema), the original table (original table name) name, and the name of the table (holding table). It "stacks up" and allows you to start to reorganize the table.
Sql> SELECT COUNT (*) from T;
Sql> SELECT COUNT (*) from T PARTITION (P2);
Sql> SELECT COUNT (*) from T PARTITION (P3);
It is necessary to note that the structure of the original and intermediate tables is exchanged at the same time, and the data backup of the original table is in the intermediate table.
Pros: Data consistency is ensured, and for most of the time, the DML operation can be performed normally by the table T. Only in the momentary lock table of the switch, it has high availability. This approach is highly flexible and can be met for a variety of different needs. Furthermore, it is possible to authorize and establish various constraints before switching, so that no additional administrative action is required after the switchover is complete.
Insufficient: the implementation is slightly more complex than the above two.
Suitable for all 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 cannot change the primary key bar. The table must have a primary key. Table redefinition must be done in the same outline. You cannot make a NOT NULL constraint on new columns until the redefine operation is complete. Tables cannot contain long, bfile, and user types (UDTs). You cannot redefine a linked list (clustered tables). Tables cannot be redefined in SYS and the system outline. You cannot redefine a table with materialized view logs (materialized view logs), and you cannot redefine a table that contains materialized views. You cannot perform a lateral diversity (horizontal subsetting) during the redefinition process.
Oracle already has data tables to create TABLE partitions-online redefinition
