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

This article mainly explains "what is the function of oracle online redefinition table". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the function of oracle online redefinition table"?

The online redefinition table has the following features:

Modify the storage parameters of the table

You can move tables to other tablespaces

Increase parallel query options

Add or delete partitions

Rebuild the table to reduce fragmentation

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

Add or delete a column.

Principle: incremental materialized view refresh

Purpose: clean up the debris in the table and free up space (rebuild the table to reduce fragmentation)

Redefine method:

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.

Prerequisites:

1) check whether there is a primary key in the table. Online redefinition requires a primary key in the table.

Select a.constraint_name, a.column_name from dba_cons_columns a, dba_constraints b

Where a.constraint_name = b.constraint_name and b.constraint_type ='P' and a.table_name in ('ZWD_JDMSG_GD','ZB2C_KUCUN_LOG')

2) if any failed operations occur during the online redefinition, you need to dbms_redefinition.abort_redef_table back and forth

1. Check whether it can be redefined

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('user name', 'source table', DBMS_REDEFINITION.CONS_USE_PK);-- primary key mode

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('user name', 'source table', DBMS_REDEFINITION.CONS_USE_ROWID);-- ROWID mode

2. Get the source table building statement

SET SERVEROUTPUT ON

SET LINESIZE 1000

SET FEEDBACK OFF

Set long 99999

Set pagesize 4000

Select dbms_metadata.get_ddl ('TABLE','ZWD_JDMSG_GD','SAPSR3') from dual

3. Create an intermediate table (create statements based on the source table)

4. View dependent objects

SELECT * from dba_dependencies where REFERENCED_NAME in ('ZWD_JDMSG_GD','ZB2C_KUCUN_LOG')

5. Call dbms_redefinition package to perform online redefinition of the table.

EXEC DBMS_REDEFINITION.start_redef_table ('SAPSR3','ZWD_JDMSG_GD','T_TEMP')

Or

-- null represents all the column names as in the original table.

Begin

Dbms_redefinition.start_redef_table ('user', 'original table', 'intermediate table', null,dbms_redefinition.cons_use_rowid)

End

6. Replicate dependent objects to intermediate tables. The latter parameters represent replicating indexes, triggers, constraints, permissions, skipping a replication error, number of errors in the replication process, replication statistics, and replication materialized view logs from the original table, respectively.

(all options are available as needed. Check the value of num_errors after completion. If there are any errors, please check them and deal with them as appropriate.)

DECLARE

Num_errors PLS_INTEGER

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

('user', 'original table', 'temporary table', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors, TRUE, TRUE)

END

7. Check the error message

Select object_name,base_table_name,ddl_txt from DBA_REDEFINITION_ERRORS

Synchronize the data in the intermediate table with the data in the original table (this step is not necessary).

If a large number of DML operations occur between dbms_redefinition.start_redef_table and dbms_redefinition.finish_redef_table execution

Then execute the dbms_redefinition.sync_interim_table once to reduce the locking time when the last step of the dbms_redefinition.finish_redef_table is executed.

EXEC dbms_redefinition.sync_interim_table ('DBSYAN','T_TEST','T_TEMP')

9. End the online redefinition process

EXEC dbms_redefinition.finish_redef_table ('DBSYAN','T_TEST','T_TEMP')

After the FINISH_REDEF_TABLE () procedure, the original table is redefined with the properties, indexes, constraints, authorizations, and triggers of the intermediate table. The constraint of disabled on the intermediate table is in the enabled state on the original table.

10. Check whether the tablespace of the original table has changed, and check whether its data, structure, index and other dependent objects are correct.

11. Failed to execute and rolled back

EXEC dbms_redefinition.abort_redef_table ('DBSYAN','T_TEST','T_TEMP')

Note:

For tables that have been redefined in ROWID mode, an implicit column named MendroWare $is included. It is recommended that you use the following statements to be implicitly listed in the UNUSED state or deleted

ALTER TABLE TABLE_NAME SET UNUSED (Machirowski $)

ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS

Thank you for your reading, the above is the content of "what is the function of oracle online redefinition table". After the study of this article, I believe you have a deeper understanding of the function of oracle online redefinition table, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.