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

Table on-line redefinition principle:

The online redefinition of oracle table is realized by prebuilt materialized view. After executing DBMS_REDEFINITION.START_REDEF_TABLE (), we can find that the corresponding materialized view and materialized view log are added by querying, user_mviews and user_mview_logs and viewing prebuilt materialized view, while SYNC_INTERIM_TABLE () is actually just an incremental refresh of materialized view. Finally, when FINISH_REDEF_TABLE () is executed, the original table will be locked. To prevent the DML on the table, the materialized view performs a refresh. After the refresh is completed, the materialized view and the corresponding log will be deleted, and the intermediate table will be rename into a target table. Note: if the business system is busy, it will block the business in the last step.

Begin DBMS_REDEFINITION.CAN_REDEF_TABLE ('chunqiu','WL_FLOW_INFO', DBMS_REDEFINITION.CONS_USE_PK); end

Begin dbms_redefinition.start_redef_table ('chunqiu','WL_FLOW_INFO','WL_FLOW_INFO_tmp'); end

Begin dbms_redefinition.sync_interim_table ('chunqiu','WL_FLOW_INFO','WL_FLOW_INFO_tmp'); end

Begin dbms_redefinition.finish_redef_table ('chunqiu','WL_FLOW_INFO','WL_FLOW_INFO_tmp'); end

If the operation fails, find the reason, and you can do it again after the following processing:

Begin DBMS_REDEFINITION.abort_redef_table ('chunqiu','WL_FLOW_INFO','WL_FLOW_INFO_tmp'); end

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.