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

This article is about how to use online redefinition to add new columns and partitions to the table in Oracle. I think it is very practical, so I share it with you. I hope you can get something after reading this article.

1. Log in to the database with the user to perform the online redefinition operation

SQL > conn pm/pm@jypdb Connected.

two。 Verify that table emp_redef can perform online redefinitions. In this case, the primary key or pseudo primary key can be used for validation.

SQL > exec dbms_redefinition.can_redef_table (uname= > 'HR',tname= >' EMP_REDEF',options_flag= > dbms_redefinition.cons_use_pk); PL/SQL procedure successfully completed.

3. Create an intermediate table hr.int_emp_redef

SQL > create table hr.int_emp_redef (employee_id NUMBER (6) not null, first_name VARCHAR2 (20), last_name VARCHAR2 (25) not null, job_id VARCHAR2 (10) not null, department_id NUMBER (4) not null, mgr NUMBER (5), hiredate DATE DEFAULT (sysdate), sal NUMBER Bonus NUMBER (7) DEFAULT (0) partition by range (employee_id) (partition emp200 values less than (200) tablespace users, partition emp400 values less than (400) tablespace users) Table created

4. Start redefine operation

SQL > begin dbms_redefinition.start_redef_table (uname = > 'hr', orig_table = >' emp_redef', int_table = > 'int_emp_redef', col_mapping = >' employee_id employee_id, first_name first_name,last_name last_name, job_id job_id, department_id+10 department_id,0 bonus', options_flag = > DBMS_REDEFINITION.CONS_USE_PK); end; / PL/SQL procedure successfully completed.

5. Copy dependent objects (automatically create any triggers, indexes, materialized view logs, authorizations and constraints on the table hr.int_emp_redef)

SQL > declare num_errors pls_integer; begin dbms_redefinition.copy_table_dependents (uname = > 'hr', orig_table = >' emp_redef', int_table = > 'int_emp_redef', copy_indexes = > DBMS_REDEFINITION.CONS_ORIG_PARAMS, copy_triggers = > TRUE, copy_constraints = > TRUE, copy_privileges = > TRUE, ignore_errors = > TRUE, num_errors = > num_errors); end / PL/SQL procedure successfully completed.

Note that the ignore_errors parameter needs to be set to TRUE when calling this procedure. The reason is that the primary key constraint is created in the intermediate table, and an error occurs when executing the copye_table_dependents procedure to attempt to copy the primary key constraint and index from the original table. You can ignore these errors, but you must execute the query in the next operation to see if there are any other errors.

6. Query the dba_redefinition_errors view to see error messages

SQL > set long 8000 SQL > set pages 8000 SQL > column object_name heading 'object name' format A20 SQL > column base_table_name heading' base table name' format A10 SQL > column ddl_txt heading 'ddl that caused error' format A40 SQL > select object_name, base_table_name, ddl_txt from dba_redefinition_errors Object name base table ddl that caused error-- SYS_C0023200 EMP_REDEF ALTER TABLE "HR". "INT_EMP_REDEF" MODIFY ("LAST_NAME" CONSTRAINT "TMP$$_SYS_C0023 2000" NOT NULL ENABLE NOVALIDATE) SYS_C0023201 EMP_REDEF ALTER TABLE "HR". "INT_EMP_REDEF" MODIFY ("JOB_ID" CONSTRAINT "TMP$$_SYS_C0023201 0" NOT NULL ENABLE NOVALIDATE) 2 rows selected.

The above error message states that the last_name and job_id of the intermediate table are listed as not null, while the reason table is null, which is negligible.

7. Synchronize intermediate table hr.int_emp_redef

SQL > begin dbms_redefinition.sync_interim_table (uname = > 'hr', orig_table = >' emp_redef', int_table = > 'int_emp_redef'); end; / PL/SQL procedure successfully completed.

8. Complete the redefinition operation

SQL > begin dbms_redefinition.finish_redef_table (uname = > 'hr', orig_table = >' emp_redef', int_table = > 'int_emp_redef'); end; / PL/SQL procedure successfully completed.

The table hr.emp_redef ends the redefinition operation only for a short time when the exclusive mode is locked. After the operation is complete, the table hr.emp_redef will be redefined using all the attributes of the hr.int_emp_ redefence table.

SQL > desc hr.emp_redef Name Type Nullable Default Comments-EMPLOYEE_ID NUMBER (6) FIRST_NAME VARCHAR2 (20) Y LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2 (10) DEPARTMENT_ID NUMBER (4) MGR NUMBER (5) Y HIREDATE DATE Y (sysdate) SAL NUMBER (7) Y BONUS NUMBER (7) SQL > select dbms_metadata.get_ddl (object_type = > 'TABLE') Name = > 'EMP_REDEF',schema = >' HR') from dual DBMS_METADATA.GET_DDL (OBJECT_TYPE= > 'TABLE',NAME= >' EMP_REDEF' SCHEMA= > 'HR')-CREATE TABLE "HR". "EMP_REDEF" ("EMPLOYEE_ID" NUMBER (6) NOT NULL ENABLE, "FIRST_NAME" VARCHAR2 (20) "LAST_NAME" VARCHAR2 (25) NOT NULL ENABLE, "JOB_ID" VARCHAR2 (10) NOT NULL ENABLE, "DEPARTMENT_ID" NUMBER (4) NOT NULL ENABLE, "MGR" NUMBER (5), "HIREDATE" DATE DEFAULT (sysdate), "SAL" NUMBER (7), "BONUS" NUMBER (7) DEFAULT (0) CONSTRAINT "EMP_REDEF_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255STORAGE (BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARTITION BY RANGE (" EMPLOYEE_ID ") (PARTITION" EMP200 "VALUES LESS THAN) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGING STORAGE (INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE" USERS " PARTITION "EMP400" VALUES LESS THAN SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGING STORAGE (INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" row selected.

You can see that the table hr.emp_redef has been successfully redefined online

9. Delete any query intermediate table after the statement is executed, and after the intermediate table is redefined, its structure becomes the table structure of the original table.

SQL > desc hr.int_emp_redef Name Type Nullable Default Comments-EMPLOYEE_ID NUMBER (6) Y FIRST_NAME VARCHAR2 (20) Y LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2 (10) DEPARTMENT_ID NUMBER (4) Y SQL > drop table hr.int_emp_redef purge Table dropped above is how to use online redefinition in Oracle to add new columns and partitions to the table. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.