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

11G DBMS_REDEFINITION modifies table data types

1. Get source table structure information

SYS@honor1 > SET LONG 999999SYS@honor1 > SELECT DBMS_METADATA.GET_DDL ('TABLE','TEST_REDE','HR') FROM DUAL CREATE TABLE "HR". "TEST_REDE" ("OWNER" VARCHAR2 (30), "OBJECT_NAME" VARCHAR2 (128), "SUBOBJECT_NAME" VARCHAR2 (30), "OBJECT_ID" VARCHAR2 (20), "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2 (19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2 (19) "STATUS" VARCHAR2 (7), "TEMPORARY" VARCHAR2 (1), "GENERATED" VARCHAR2 (1), "SECONDARY" VARCHAR2 (1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2 (30) TABLESPACE "USERS" two。 Verify that it can be redefined online

# because the table does not have a primary key, you can only use ROWID. If the table has a primary key, you can delete CONS_USE_ROWID and default to PKBEGINDBMS_REDEFINITION.CAN_REDEF_TABLE ('HR','TEST_REDE',DBMS_REDEFINITION.CONS_USE_ROWID); END;/3. Create an intermediate table and modify the column data types that need to be modified

CREATE TABLE "HR". "TEST_REDE_INTER" ("OWNER" VARCHAR2 (30), "OBJECT_NAME" VARCHAR2 (1288), "SUBOBJECT_NAME" VARCHAR2 (30), "OBJECT_ID" VARCHAR2 (20), "DATA_OBJECT_ID" VARCHAR2 (10)-- need to modify the data types "OBJECT_TYPE" VARCHAR2 (19), "CREATED" DATE, "LAST_DDL_TIME" DATE "TIMESTAMP" VARCHAR2 (19), "STATUS" VARCHAR2 (7), "TEMPORARY" VARCHAR2 (1), "GENERATED" VARCHAR2 (1), "SECONDARY" VARCHAR2 (1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2 (30) TABLESPACE "USERS" 4. Start online redefinition

Alter session force parallel dml parallel 4 alter session force parallel query parallel 4 BEGINDBMS_REDEFINITION.START_REDEF_TABLE (uname = > 'HR',orig_table = >' TEST_REDE',int_table = > 'TEST_REDE_INTER',col_mapping = >' OWNER OWNER, OBJECT_NAME OBJECT_NAME,SUBOBJECT_NAME SUBOBJECT_NAME,OBJECT_ID OBJECT_ID,to_char (DATA_OBJECT_ID) DATA_OBJECT_ID,-- due to change of data type Need to manually convert OBJECT_TYPE OBJECT_TYPE,CREATED CREATED,LAST_DDL_TIME LAST_DDL_TIME,TIMESTAMP TIMESTAMP,STATUS STATUS,TEMPORARY TEMPORARY,GENERATED GENERATED,SECONDARY SECONDARY,NAMESPACE NAMESPACE,EDITION_NAME EDITION_NAME',options_flag = > DBMS_REDEFINITION.CONS_USE_ROWID) END;/5. Synchronize intermediate data to reduce finish_redef_table time

Begin dbms_redefinition.sync_interim_table (uname = >'& USERNAME', orig_table = >'& SOURCE_TAB', int_table = >'& INT_TAB'); end;/6. Copy related constraints, dependent object

# if you change the column name or add a column, manually create related indexes and other objects such as DECLARE num_errors PLS_INTEGER if necessary BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (uname = >'& USERNAME', orig_table = >'& SOURCE_TAB', int_table = >'& INT_TAB' Copy_indexes = > DBMS_REDEFINITION.cons_orig_params, copy_triggers = > TRUE, copy_constraints = > TRUE, copy_privileges = > TRUE Ignore_errors = > FALSE, num_errors = > num_errors, copy_statistics = > TRUE) END;/7. Complete online redefinition

Begindbms_redefinition.finish_redef_table (uname = >'& USERNAME', orig_table = >'& SOURCE_TAB', int_table = >'& INT_TAB'); end

Reference:

Https://blog.csdn.net/bikeorcl/article/details/103974032

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.