Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve online redefinition of oracle and ordinary tables to partition tables, I hope you will learn something after reading this article, let's discuss it together!

I. what is online redefinition

To understand what online redefinition techniques are, I want to start with table partitioning. In the process of operation and maintenance of production system, one of the requirements often encountered is how to transform an ordinary table with a very large amount of data into a partitioned table. Partitioning was first introduced in the oracle8.0 version to support the physical decomposition of a table or index into smaller, more manageable parts.

Here are some of the features of the online redefinition table:

1. Modify the storage parameters of the table

two。 You can move tables to other tablespaces

3. Increase parallel query options

4. Add or delete partitions

5. Rebuild the table to reduce fragmentation

6. Change the heap table to an index to organize the table or vice versa

7. Add or delete a column.

Online redefinition benefits:

-improve the availability of data

-data segments become smaller, reducing the burden of management

-improve the performance of some queries

-distribute data modifications across multiple separate partitions to reduce competition

Partition tables are widely used in databases in various industries, but some business systems underestimate the growth of system data and performance capacity in the design stage, or do not take into account the data archiving requirements in the process of operation and maintenance. often there is no partition design for the table. After a long period of data accumulation in the production operation, it is found that the table is getting larger and larger, and the performance of some queries or inserting data is becoming slower and slower, so there is an urgent need for table partition transformation.

Then the problem comes, the business system is often working online on July 24, and the transformation process must involve the change of the table structure. If the table is rebuilt, it will have a great impact on the operation of the system. Usually, a planned downtime window is set up to do this kind of maintenance operation.

Of course, the transformation of partition tables is only one of many scenarios of data reorganization or redefinition. Under the background that the demand for data changes is more and more complex, and the cost of system downtime is significantly higher, limited functions of online reorganizing data, such as create indexes online and rebuilding indexes online, have been designed since Oracle 8i. And in 9i to further expand this ability, the introduction of online data redefinition.

Online redefinition technology allows database administrators to flexibly modify the physical properties, table data and table structure of the table when there are read and write data operations on the table.

II. Online redefined usage scenarios

You can consider using online redefinition techniques when you have the following change requirements, which are often encountered during operation and maintenance:

-modify the physical properties and storage parameters of the table

-migrate tables to a different tablespace

-remove table fragments and free up space

-add, delete, or rename fields to the table

-change the data in the table in large quantities

III. The implementation principle of online redefinition

Oracle provides a dbms_redefinition package for online redefinition operations, which consists of the following three processes:

Dbms_redefinition.start_redef_table

This process first creates a quickly refreshed materialized view as a transition table, then loads the data from the source table into the transition table, and creates a materialized view log on the source table to support fast refresh of synchronous data.

Dbms_redefinition.sync_interim_table

Used to synchronize data from the source table to the transition table.

Dbms_redefinition.finish_redef_table

There are many steps in this process, which need to be paid special attention to when doing online redefinition, but the execution time is usually very short:

1) call dbms_redefinition.sync_interim_table once to synchronize the data.

2) Lock the source table, and the table data is no longer allowed to change after locking.

3) call dbms_redefinition.sync_interim_table again to synchronize the data.

4) Exchange the table names of the source table and the transition table.

5) delete materialized view and materialized view log.

6) release table lock resources.

Fourth, the experiment transforms the ordinary table into the partition table

Let's apply this technology through an actual case, and we need to figure out several problems in this practice:

a. The operation of online redefinition.

b. How long it will take to redefine a table with 20 million data volumes.

c. Whether and how table-related operations are affected during online redefinition.

1. Check user permissions

The following permissions are required to run the dbms_redefinition package:

-execute privilege to dbms_redefinition

-create any table

-alter any table

-drop any table

-lock any table

-select any table

-create any index

-create any trigger

Grant execute on dbms_redefinition to SCOTT

Grant create any table to SCOTT

Grant alter any table to SCOTT

Grant drop any table to SCOTT

Grant lock any table to SCOTT

Grant select any table to SCOTT

Grant create any index to SCOTT

Grant create any trigger to SCOTT

After entering the user, execute the following SQL to check and confirm:

Select * from session_privs

two。 Experiment to create a source table and insert data

Create table unpar_table (id number (10), create_date date,name varchar2 (100), up_date date)

-- insert 2 million data initial test data using batch binding technology

Declare

Type t_mid is table of unpar_table%rowtype index by binary_integer

L_tab_mid t_mid

Begin

For i in 1.. 2000000 loop

L_tab_mid (I) .id: = I

L_tab_mid (I) .create_date: = sysdate

L_tab_mid (I) .name: = lpad ('axim, 100a')

L_tab_mid (I) .up_date: = sysdate

End loop

Forall i in 1.. L_tab_mid.count

Insert into unpar_table values l_tab_mid (I)

Commit

End

/

-- add primary key constraints and index to table unpar_table

Alter table unpar_table add (constraint unpar_table_pk primary key (id))

Create index create_date_ind on unpar_table (create_date)

Note: redefine the method online. There are two methods of redefinition, one based on the primary key and the other based on ROWID. The ROWID approach cannot be used for indexing and organizing tables, and there will be a hidden column named Mendrowski $after redefinition. The primary key is used by default. (if a redefined table is based on a primary key, it must have a primary key in order to redefine.)

-- collect statistics

Exec dbms_stats.gather_table_stats (ownname = > 'SCOTT',TABNAME = >' UNPAR_TABLE',cascade = > true)

3. Create a partitioned intermediate table as required

Create table par_table (id number (10), create_date date,name varchar2 (100), up_date date)

Partition by range (create_date)

(

Partition unpar_table_1 values less than (to_date ('01Accord 01Universe 2012))

Partition unpar_table_2 values less than (to_date ('01Accord 01Accord 2013))

Partition unpar_table_3 values less than (to_date ('01Accord 01Accord 2014))

Partition unpar_table_4 values less than (to_date ('01Accord 01CMR 2015D Universe DYYYY')

Partition unpar_table_5 values less than (to_date ('01Accord 01max 2016))

Partition unpar_table_6 values less than (to_date ('01Accord 01max 2017))

Partition unpar_table_7 values less than (maxvalue)

);

The above steps complete the preparations and begin the online redefinition process.

4. Check whether the source table has the conditions for online redefinition

Exec dbms_redefinition.can_redef_table ('SCOTT','UNPAR_TABLE')

-- time-consuming inspection

SQL > exec dbms_redefinition.can_redef_table ('SCOTT','UNPAR_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

5. Start an online redefinition, which is equivalent to initialization and takes a long time

Exec dbms_redefinition.start_redef_table ('SCOTT','UNPAR_TABLE','PAR_TABLE')

-- time-consuming inspection

SQL > exec dbms_redefinition.start_redef_table ('SCOTT','UNPAR_TABLE','PAR_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:43.29

6. Create constraints and indexes on intermediate tables and collect statistics

Doing this step in advance can prevent performance problems caused by no indexes available for the new table after the redefinition is complete.

Oracle provides dbms_redefinition.copy_table_dependents procedures for replicating indexes, constraints, triggers, permissions, and other dependencies on the source table that relate to the intermediate table, but there are many BUG in this package that can be used selectively.

Alter table par_table add (constraint unpar_table_pk2 primary key (id))

-- time-consuming: Elapsed: 0000VOUR 08.93

Create index create_date_ind2 on par_table (create_date)

-- time-consuming: Elapsed: 000010.07

Exec dbms_stats.gather_table_stats (ownname = > 'SCOTT',TABNAME = >' PAR_TABLE',cascade = > true)

-- time-consuming: Elapsed: 0000VOUR 02.89

Note:

If a large number of DML operations are performed directly on the redefined table during the execution of the DBMS_REDEFINITION.START_REDEF_TABLE () procedure and the DBMS_REDEFINITION.FINISH_REDEF_TABLE () procedure, you can choose to execute the SYNC_INTERIM_TABLE () procedure one or more times to reduce the locking time when the last step executes the FINISH_REDEF_TABLE () procedure.

7. Synchronize the data manually, and synchronously refresh the data generated in the previous step

Exec dbms_redefinition.sync_interim_table ('SCOTT','UNPAR_TABLE','PAR_TABLE')

-- time-consuming inspection

SQL > exec dbms_redefinition.sync_interim_table ('SCOTT','UNPAR_TABLE','PAR_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22

8. Complete the online redefinition process: after execution, the table names of the intermediate table and the source table are interchanged

Exec dbms_redefinition.finish_redef_table ('SCOTT','UNPAR_TABLE','PAR_TABLE')

-- time-consuming inspection

SQL > exec dbms_redefinition.finish_redef_table ('SCOTT','UNPAR_TABLE','PAR_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.64

9. Delete the intermediate table and rename the index back

At this time, the intermediate table is already the original unpartitioned ordinary table, and the source table has become a partitioned table.

-- check the partition table and the general table first

Select table_name,partition_name,num_rows from user_tab_partitions where table_name like'% PAR_TABLE%'

Select a. Segmentmentcategory type sum (a.bytes/1024/1024) size_m from user_segments a where a.segment_name like'% PAR_TABLE%' group by a segmentmentcategory type

Select table_name,index_name,status from user_indexes where table_name like'% PAR_TABLE%'

SQL > select table_name,partition_name,num_rows from user_tab_partitions where table_name like'% PAR_TABLE%'

TABLE_NAME PARTITION_NAME NUM_ROWS

-

UNPAR_TABLE UNPAR_TABLE_7 2000000

UNPAR_TABLE UNPAR_TABLE_6 0

UNPAR_TABLE UNPAR_TABLE_5 0

UNPAR_TABLE UNPAR_TABLE_4 0

UNPAR_TABLE UNPAR_TABLE_3 0

UNPAR_TABLE UNPAR_TABLE_2 0

UNPAR_TABLE UNPAR_TABLE_1 0

7 rows selected.

SQL > select a. Segmentmentnames. Segmentmenttypes: sum (a.bytes/1024/1024) size_m from user_segments a where a.segment_name like'% PAR_TABLE%' group by a segmentmentnames

SEGMENT_NAME SEGMENT_TYPE SIZE_M

UNPAR_TABLE TABLE PARTITION 288

PAR_TABLE TABLE 280

UNPAR_TABLE_PK INDEX 35

UNPAR_TABLE_PK2 INDEX 44

SQL > select table_name,index_name,status from user_indexes where table_name like'% PAR_TABLE%'

TABLE_NAME INDEX_NAME STATUS

-

UNPAR_TABLE CREATE_DATE_IND2 VALID

UNPAR_TABLE UNPAR_TABLE_PK2 VALID

PAR_TABLE CREATE_DATE_IND VALID

PAR_TABLE UNPAR_TABLE_PK VALID

-- Operation

Drop table par_table purge

Alter table unpar_table rename constraint unpar_table_pk2 to unpar_table_pk

Alter index unpar_table_pk2 rename to unpar_table_pk

Alter index create_date_ind2 rename to create_date_ind

-- validate the query

SQL > select table_name,index_name,status from user_indexes where table_name like'% PAR_TABLE%'

TABLE_NAME INDEX_NAME STATUS

-

UNPAR_TABLE CREATE_DATE_IND VALID

UNPAR_TABLE UNPAR_TABLE_PK VALID

SQL > select a. Segmentmentnames. Segmentmenttypes: sum (a.bytes/1024/1024) size_m from user_segments a where a.segment_name like'% PAR_TABLE%' group by a segmentmentnames

SEGMENT_NAME SEGMENT_TYPE SIZE_M

UNPAR_TABLE TABLE PARTITION 288

UNPAR_TABLE_PK INDEX 44

At this point, the transformation of table partitioning using online redefinition has been completed.

5. Problems needing attention in online redefinition

Using the online redefinition technique, the following situations need to be noted:

-if offline operations can solve the problem, do not use online redefinition such as some static data, historical data archive migration, you can use CTAS, alter table move, or export import to complete

-the table space should have at least more remaining space than the space used by the source table

-the process of online redefinition takes a long time, but has the least impact on the business

-pay attention to the transaction operations on the source table. If it is too frequent, serious waiting may occur.

After reading this article, I believe you have a certain understanding of "how to achieve online redefinition of oracle and how to change ordinary tables to partition tables". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.