dba:oracle:howtos:dbms_redefinition_usage
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | Last revisionBoth sides next revision | ||
dba:oracle:dbms_redefinition_usage [2022/02/11 11:12] – ↷ Page moved from oracle:dbms_redefinition_usage to dba:oracle:dbms_redefinition_usage dodger | dba:oracle:howtos:dbms_redefinition_usage [2023/01/31 08:26] – removed - external edit (Unknown date) 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== [HOWTO] DBMS_REDEFINITION usage ====== | ||
- | |||
- | ====== Previous Requirements ====== | ||
- | |||
- | Name of te table to compact/ | ||
- | |||
- | ====== Official documentation ====== | ||
- | |||
- | [[https:// | ||
- | \\ | ||
- | (Oracle can move this document at any time) | ||
- | |||
- | ====== Step 1: check if DBMS_REDEFINITION can be used ====== | ||
- | |||
- | There are multiple ways to use DBMS_REDEFINITION. | ||
- | I mainly use 2: | ||
- | * Using PK | ||
- | * Using ROWID | ||
- | \\ | ||
- | I've been always be able to finish the whole process with any of this two methods. | ||
- | |||
- | |||
- | ===== Step 1.1: Check Using PK ===== | ||
- | |||
- | <code sql> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.CAN_REDEF_TABLE( | ||
- | | ||
- | | ||
- | | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | |||
- | ===== Step 1.2: Using ROWID ===== | ||
- | <code sql> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.CAN_REDEF_TABLE( | ||
- | | ||
- | | ||
- | | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | |||
- | ====== Step 2: Creating pivot table ====== | ||
- | Or interim as named in oracle. | ||
- | I prefer to use DBMS_METADATA to control the 100% of the process. | ||
- | |||
- | ===== Step 2.1: Using CREATE .. SELECT ===== | ||
- | |||
- | This is the most basic way to create the pivot table: | ||
- | <code sql> | ||
- | create table SCHEMA_NAME.INT_V as select * from SCHEMA_NAME.TABLENAME WHERE 1 = 2; | ||
- | </ | ||
- | |||
- | |||
- | ===== Step 2.2: Using DBMS_METADATA ===== | ||
- | Obtain metadata: | ||
- | <code sql> | ||
- | SELECT DBMS_METADATA.GET_DDL(' | ||
- | </ | ||
- | |||
- | Then modify the table name and if there' | ||
- | |||
- | |||
- | ====== Step 3: begin redefinition ====== | ||
- | ===== Step 3.1: Using PK ===== | ||
- | |||
- | <code sql> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.START_REDEF_TABLE( | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | ===== Step 3.1: Using ROWID ===== | ||
- | |||
- | <code sql> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.START_REDEF_TABLE( | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | ===== Step 3.1: Using ROWID with virtual columns===== | ||
- | |||
- | If the table has virtual columns you'll obtain a nice: | ||
- | ORA-01733: virtual column not allowed here | ||
- | |||
- | When using DBMS_REDEFINITION. | ||
- | A little of tunning must be done while redefining the table. | ||
- | |||
- | Is necessary to specify the **NON-VIRTUAL** columns and pass them to DBMS_REDEFINITION ('' | ||
- | |||
- | <code sql> | ||
- | declare | ||
- | l_colmap varchar(512); | ||
- | begin | ||
- | l_colmap := ' | ||
- | |||
- | dbms_redefinition.start_redef_table | ||
- | ( | ||
- | | ||
- | | ||
- | | ||
- | col_mapping | ||
- | options_flag => DBMS_REDEFINITION.cons_use_rowid ); | ||
- | end; | ||
- | / | ||
- | </ | ||
- | |||
- | ====== Step 4: copy table objects====== | ||
- | |||
- | <code sql> | ||
- | DECLARE | ||
- | num_errors PLS_INTEGER; | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(' | ||
- | DBMS_REDEFINITION.CONS_ORIG_PARAMS, | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | Then check the errors: | ||
- | |||
- | <code sql> | ||
- | select object_name, | ||
- | </ | ||
- | |||
- | If there' | ||
- | |||
- | ====== Step 5: Sync tables ====== | ||
- | |||
- | <code sql> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.SYNC_INTERIM_TABLE(' | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | ====== Step 6: FINISH IT! ====== | ||
- | |||
- | <code sql> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.FINISH_REDEF_TABLE(' | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | |||
- | ====== Example ====== | ||
- | |||
- | <code sql> | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | |||
- | BEGIN | ||
- | DBMS_REDEFINITION.CAN_REDEF_TABLE( | ||
- | | ||
- | | ||
- | | ||
- | END; | ||
- | 7 / | ||
- | BEGIN | ||
- | * | ||
- | ERROR at line 1: | ||
- | ORA-12089: cannot online redefine table " | ||
- | ORA-06512: at " | ||
- | ORA-06512: at " | ||
- | ORA-06512: at line 2 | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.CAN_REDEF_TABLE( | ||
- | | ||
- | | ||
- | | ||
- | END; | ||
- | 7 / | ||
- | |||
- | PL/SQL procedure successfully completed. | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | |||
- | TXT | ||
- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ||
- | |||
- | CREATE TABLE " | ||
- | | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | ) SEGMENT CREATION IMMEDIATE | ||
- | PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 | ||
- | | ||
- | TABLESPACE " | ||
- | |||
- | |||
- | 1 row selected. | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | |||
- | CREATE TABLE " | ||
- | | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | " | ||
- | ) SEGMENT CREATION IMMEDIATE | ||
- | PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 | ||
- | | ||
- | | ||
- | |||
- | Table created. | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | |||
- | BEGIN | ||
- | DBMS_REDEFINITION.START_REDEF_TABLE( | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | END; | ||
- | 9 / | ||
- | |||
- | |||
- | PL/SQL procedure successfully completed. | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | SYS@BAVEL12R_1> | ||
- | |||
- | DECLARE | ||
- | num_errors PLS_INTEGER; | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(' | ||
- | DBMS_REDEFINITION.CONS_ORIG_PARAMS, | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | 8 / | ||
- | |||
- | Num_errors = 7 | ||
- | |||
- | PL/SQL procedure successfully completed. | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | SYS@BAVEL12R_1> | ||
- | 1* select object_name, | ||
- | |||
- | OBJECT_NAME | ||
- | ----------------------------------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ | ||
- | SYS_C0017232 | ||
- | SYS_C0017233 | ||
- | SYS_C0017234 | ||
- | SYS_C0017238 | ||
- | SYS_C0017236 | ||
- | SYS_C0017237 | ||
- | SYS_C0017235 | ||
- | |||
- | 7 rows selected. | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.SYNC_INTERIM_TABLE(' | ||
- | END; | ||
- | / | ||
- | |||
- | |||
- | PL/SQL procedure successfully completed. | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | BEGIN | ||
- | DBMS_REDEFINITION.FINISH_REDEF_TABLE(' | ||
- | END; | ||
- | 4 / | ||
- | |||
- | PL/SQL procedure successfully completed. | ||
- | |||
- | SYS@BAVEL12R_1> | ||
- | |||
- | </ | ||
- | |||
dba/oracle/howtos/dbms_redefinition_usage.txt · Last modified: 2023/01/31 08:26 by dodger