Removing unused columns online using DBMS_REDEFINITION

  • Published on Jul 27 2013
  • # 747
  • oracle

We need to drop a number of unused columns on a fairly large (and important) table in our database.

Example below uses DBMS_REDEFINITION in combination with a ‘Create table as select’ ( CTAS ) to achieve this.
I will create the interin table using a CTAS copy – Oracle will not copy the unused columns in a CTAS create table statement. After that we copy all the table dependents (indexes, grants, constraints) etc to the interim table.
Finally we start and finish the redefinition using DBMS_REDEFINITION.START_REDEF_TABLE and DBMS_REDEFINITION.FINISH_REDEF_TABLE.

Redefining Tables Online
DBMS_REDEFINITION

Here is a complete test on my local database;

Remove old DBMS_REDEFINITION task (I ran my test a couple of times )

SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE ( 'REMI', 'DEMO', 'DEMO_INTERIM');

PL/SQL procedure successfully completed.

Create test table demo with three columns and populate it with some data

SQL> create table demo ( k int primary key, l int, m int)
  2  /

Table created.

SQL> insert into demo select object_id, object_id, object_id from all_objects
  2  /

71058 rows created.

Now let’s add some ‘dependant’ objects on demo table like an index, grant, and a trigger – I want to see how/if dbms_redefinition.copy_table_dependents will work ok.

SQL> create index idx_demo on demo (m)
  2  /

Index created.

SQL> grant select on demo to system
  2  /

Grant succeeded.

SQL> create trigger trg_demo
  2        BEFORE INSERT OR UPDATE on demo
  3        FOR EACH ROW
  4  begin
  5     null;
  6  end;
  7  /

Trigger created.

[REMI@ORCL:HCNLLS079L]
SQL>

Let’s check out objects created

SQL> select table_name, column_name, hidden_column
  2  from user_tab_cols
  3  where table_name = 'DEMO'
  4  /

TABLE_NAME   COLUMN_NAME                HIDDEN_COLUM
------------ -------------------------- ------------
DEMO         K                          NO
DEMO         L                          NO
DEMO         M                          NO

SQL> select * from user_triggers where table_name = 'DEMO'
  2  /

TRIGGER_NAME           TABLE_OWNER                    BASE_OBJECT_TYPE TABLE_NAME
---------------------- ------------------------------ ---------------- ------------
TRG_DEMO               REMI                           TABLE            DEMO

Set DEMO.L column unused and check it out in user_tab_cols.

SQL> alter table demo set unused (l)
  2  /

Table altered.

SQL> select table_name, column_name, hidden_column
  2  from user_tab_cols
  3  where table_name = 'DEMO'
  4  /

TABLE_NAME   COLUMN_NAME                HIDDEN_COLUM
------------ -------------------------- ------------
DEMO         K                          NO
DEMO         SYS_C00002_13072710:50:01$ YES
DEMO         M                          NO

Check if demo table can be redefined online by executing dbms_redefinition.can_redef_table. Procedure should return ‘PL/SQL procedure successfully completed.’

SQL> begin
  2    dbms_redefinition.can_redef_table( uname => 'REMI', tname => 'DEMO') ;
  3  end;
  4  /

PL/SQL procedure successfully completed.

Create the interim table. I create it using a CTAS to copy the table structure. The ‘where 1 = 2’ part means no rows will be copied.

SQL> create table demo_interim
  2  as
  3  select * from demo
  4  where 1 = 2
  5  /

Table created.

OK Now let’s start the redefinition process – needs briefly exclusive access on DEMO table.

SQL> begin
  2     dbms_redefinition.start_redef_table(
  3                     uname => 'REMI',
  4                     orig_table => 'DEMO',
  5                     int_table => 'DEMO_INTERIM'
  6     ) ;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Copy all the dependent database objects. Oracle will create all triggers, indexes, materialized view logs, grants, and constraints on demo table.

This is done with the procedure dbms_redefinition.copy_table_dependents.

SQL> declare
  2     num_errors PLS_INTEGER;
  3  begin
  4     dbms_redefinition.copy_table_dependents( 'REMI', 'DEMO', 'DEMO_INTERIM',
  5     dbms_redefinition.cons_orig_params, true, true, true, true, num_errors);
  6     dbms_output.put_line ( num_errors ) ;
  7  end;
  8  /
0

PL/SQL procedure successfully completed.

Check out the (temporary) dependent objects that were created by oracle. Oracle created them as TMP$$_ ***. Also notice the MLOG$_DEMO and RUPD$_DEMO internal tables which are used by oracle to keep track of the deltas (inserts, updates and deletes) on the DEMO table in the time lag between the start and finish dbms_redefinition calls.

SQL> select * from user_objects
  2  /

OBJECT_NAME                      DATA_OBJECT_ID OBJECT_TYPE     CREATED  STATUS  
-------------------------------- -------------- --------------- -------- ------- 
DEMO                                      74103 TABLE           27-07-13 VALID   
SYS_C0011664                              74104 INDEX           27-07-13 VALID   
IDX_DEMO                                  74105 INDEX           27-07-13 VALID   
DEMO_INTERIM                              74106 TABLE           27-07-13 VALID   
MLOG$_DEMO                                74107 TABLE           27-07-13 VALID   
RUPD$_DEMO                                      TABLE           27-07-13 VALID   
TMP$$_SYS_C00116640                       74109 INDEX           27-07-13 VALID   
TMP$$_IDX_DEMO0                           74110 INDEX           27-07-13 VALID   
TMP$$_TRG_DEMO0                                 TRIGGER         27-07-13 VALID   
TRG_DEMO                                        TRIGGER         27-07-13 INVALID 

10 rows selected.

OK let’s do an update on our DEMO while our online redefinition is in progress. Notice the delta that is being written do the MLOG$_DEMO table.
Nice.

SQL> delete from demo where k = 100;

1 row deleted.

SQL> select * from MLOG$_DEMO  ;

         K SNAPTIME D O CHANGE_VECTOR$$
---------- -------- - - ----------------------------------
       100 01-01-00 D O 00

SQL> roll
Rollback complete.
SQL> select * from MLOG$_DEMO  ;

no rows selected

[REMI@ORCL:HCNLLS079L]
SQL>

OK out interim table looks OK to replace our live table.

Finish the redefinition. Will need exclusive access on the table (like the start) Oracle will wait if the table is in use by any transaction.

SQL> begin
  2     dbms_redefinition.finish_redef_table(
  3                     uname => 'REMI',
  4                     orig_table => 'DEMO',
  5                     int_table => 'DEMO_INTERIM'
  6     ) ;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Check if unused columns on demo table are gone.

SQL> select table_name, column_name, hidden_column
  2  from user_tab_cols
  3  where table_name = 'DEMO'
  4  /

TABLE_NAME   COLUMN_NAME                HIDDEN_COLUM
------------ -------------------------- ------------
DEMO         K                          NO
DEMO         M                          NO
[REMI@ORCL:HCNLLS079L]
SQL>

Looking good. The unused column is gone.

Now check if all ‘dependant’ objects are present.

SQL> select * from user_triggers where table_name = 'DEMO'
  2  /

TRIGGER_NAME           TRIGGER_TYPE     TRIGGERING_EVENT           TABLE_OWNER                    BASE_OBJECT_TYPE TABLE_NAME
---------------------- ---------------- -------------------------- ------------------------------ ---------------- ------------
TRG_DEMO               BEFORE EACH ROW  INSERT OR UPDATE           REMI                           TABLE            DEMO

SQL> select * from dba_tab_privs where table_name = 'DEMO'
  2  /

GRANTEE    OWNER          TABLE_NAME   GRANTOR   PRIVILEGE
---------- -------------- ------------ --------- ---------------
SYSTEM     REMI           DEMO         REMI      SELECT

SQL> select * from user_indexes where table_name = 'DEMO'
  2  /

                               INDEX
INDEX_NAME                     TYPE        TABLE_OWNER    TABLE_NAME
------------------------------ ----------- -------------- ------------
SYS_C0011664                   NORMAL      REMI           DEMO
IDX_DEMO                       NORMAL      REMI           DEMO

SQL> select * from user_constraints where table_name = 'DEMO'
  2  /

OWNER             CONSTRAINT_NAME      C TABLE_NAME
----------------- -------------------- - ------------
REMI              SYS_C0011664         P DEMO

[REMI@ORCL:HCNLLS079L]
SQL>

All this was triggered by an Oracle Golden Gate configuration where we have to replicate the table in question to another database having the same table – only on the replicat site the table did NOT have the unused columns …

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>