DBMS_REDEFINITION Package

Very few operations in the Oracle database require a full table lock for any length of time. One of those very few operations is an ‘alter table’ statement. Because of this issue, Oracle has created the DBMS_REDEFINITION package for making online table changes. What follows is an example of how to use the DBMS_REDEFINITION package to expand a column in a table. In our example, we will be modifying TESTTAB1 by expanding a column. Here is the current layout of TESTTAB1:

SQL> desc testtab1
Name Null? Type
—————————————– ——– ——————-
RENCOL13 NOT NULL CHAR(26)
RENCOL12 NOT NULL CHAR(1)
RENCOL11 NOT NULL CHAR(2)
RENCOL10 NOT NULL CHAR(2)
RENCOL9 NOT NULL CHAR(8)
RENCOL8 NOT NULL CHAR(8)
RENCOL7 NOT NULL CHAR(10)
RENCOL6 NOT NULL CHAR(10)
RENCOL5 NOT NULL CHAR(8)
RENCOL4 NOT NULL CHAR(10)
RENCOL3 NOT NULL CHAR(21)
RENCOL2 NOT NULL CHAR(25)
RENCOL1 NOT NULL CHAR(30)

SQL> select index_name from dba_indexes where table_name=’TESTTAB1′;

INDEX_NAME
——————————
TESTTAB1INDEX01
TESTTAB1INDEX02
TESTTAB1INDEX03
TESTTAB1INDEX04
TESTTAB1INDEX05

These are the steps involved when using DBMS_REDEFINITION to change a table layout.

1. Create a table containing the identical layout of the original table. Do not include constraints or indexes, only the column definitions. Make sure that any added or changed columns are included. This will be referred to as the interim table.

Below is the layout of our interim table which will have the changed column definition. Right now it is identical to TESTTAB1, except that it is empty, and has no constraints or indices defined:

SQL> desc testtab2
Name Null? Type
—————————————– ——– ——————

RENCOL13 NOT NULL CHAR(26)
RENCOL12 NOT NULL CHAR(1)
RENCOL11 NOT NULL CHAR(2)
RENCOL10 NOT NULL CHAR(2)
RENCOL9 NOT NULL CHAR(8)
RENCOL8 NOT NULL CHAR(8)
RENCOL7 NOT NULL CHAR(10)
RENCOL6 NOT NULL CHAR(10)
RENCOL5 NOT NULL CHAR(8)
RENCOL4 NOT NULL CHAR(10)
RENCOL3 NOT NULL CHAR(21)
RENCOL2 NOT NULL CHAR(25)
RENCOL1 NOT NULL CHAR(30)

2. Modify the interim table to the intended layout of the original table.

SQL> alter table testtab2 modify (rencol9 char(20));

Table altered.

3. Verify that it is possible to redefine this table online. You will get an error if you cannot:

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE(‘OWNER’,’TESTTAB1′);

PL/SQL procedure successfully completed.

4. Next, determine if Oracle will use the rowid or the primary key for this redefine operation. This is the setting for the ‘options_flag’ in the START_REDEF_TABLE procedure. If the table has a primary key defined, use the primary key option (DBMS_REDEFINITION.cons_use_pk). If not, the rowid option is used (DBMS_REDEFINITION.cons_use_rowid).

5. Create the DBMS_REDEFINITION commands that will be required. Generally there are four procedures used after the check with CAN_REDEF_TABLE. Those are START_REDEF_TABLE, COPY_TABLE_DEPENDENTS, SYNC_INTERIM_TABLE, and FINISH_REDEF_TABLE. It is useful to create the commands first so that they can just be copy/pasted into SQLPlus or SQL Developer. Here are the commands that will be used:

SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.START_REDEF_TABLE(
uname=>’OWNER’,
orig_table=>’TESTTAB1′,
int_table=>‘TESTTAB2’,
options_flag=>DBMS_REDEFINITION.cons_use_rowid);
end;
/

SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => ‘OWNER’,
orig_table => ‘TESTTAB1’,
int_table => ‘TESTTAB1’,
copy_indexes => DBMS_REDEFINITION.cons_orig_params,
copy_triggers => TRUE, — Default
copy_constraints => TRUE, — Default
copy_privileges => TRUE, — Default
ignore_errors => FALSE, — Default
num_errors => l_num_errors);
DBMS_OUTPUT.put_line(‘l_num_errors=’ || l_num_errors);
END;
/

SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>’OWNER’,
orig_table=>’TESTTAB1′,
int_table=>‘TESTTAB2’);
end;
/

SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>’OWNER’,
orig_table=>’TESTTAB1′,
int_table=>‘TESTTAB2’);
end;
/

6. Start the operation by running the ‘START_REDEF_TABLE’ procedure. The START_REDEF_TABLE procedure initiates the table redefinition:

SQL> SET SERVEROUTPUT ON
SQL> begin
2 DBMS_REDEFINITION.START_REDEF_TABLE(uname=>’OWNER’,
3 orig_table=>’TESTTAB1′,
4 int_table=>‘TESTTAB2’,
5 options_flag=>DBMS_REDEFINITION.cons_use_rowid);
6 end;
7 /

PL/SQL procedure successfully completed.

7. After the start operation completes, run the copy table dependents option. Note that if this operation runs for any significant length of time, the SYNC_INTERIM_TABLE command can be run in a separate session (see step 8) to minimize the time required on the finish command:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
5 uname => ‘OWNER’,
6 orig_table => ‘TESTTAB1’,
7 int_table => ‘TESTTAB1’,
8 copy_indexes => DBMS_REDEFINITION.cons_orig_params,
9 copy_triggers => TRUE, — Default
10 copy_constraints => TRUE, — Default
11 copy_privileges => TRUE, — Default
12 ignore_errors => FALSE, — Default
13 num_errors => l_num_errors);
14 DBMS_OUTPUT.put_line(‘l_num_errors=’ || l_num_errors);
15 END;
16 /
l_num_errors=0

PL/SQL procedure successfully completed.

8. If the COPY_TABLE_DEPENDENTS procedure runs long (over 30 seconds), run this command periodically to keep the data in sync and minimize time on the final step:
SQL> begin
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>’OWNER’,
3 orig_table=>’TESTTAB1′,
4 int_table=>‘TESTTAB2’);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> /
PL/SQL procedure successfully completed.

SQL> /
PL/SQL procedure successfully completed.

9. After the COPY_TABLE_DEPENDENTS procedure finishes, you can add any additional indices and constraints that may be needed. They are applied to the interim table, and must be done before running the ‘FINISH_REDEF_TABLE’ procedure. Run the ‘SYNC_INTERIM_TABLE’ procedure if needed after adding any constraints or indices.

10. Run the ‘FINISH_REDEF_TABLE’ procedure to finish the procedure. Note that at this point a very brief table lock is required. It may be necessary, though it is unlikely, to kill other user processes that are running. The lock is required as Oracle locks out changes as the data dictionary switches from the old table to the new table.
SQL> begin
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>’OWNER’,
3 orig_table=>’TESTTAB1′,
4 int_table=>‘TESTTAB2’);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> desc testtab1
Name Null? Type
—————————————– ——– ——————-
RENCOL13 NOT NULL CHAR(26)
RENCOL12 NOT NULL CHAR(1)
RENCOL11 NOT NULL CHAR(2)
RENCOL10 NOT NULL CHAR(2)
RENCOL9 NOT NULL CHAR(20)
RENCOL8 NOT NULL CHAR(8)
RENCOL7 NOT NULL CHAR(10)
RENCOL6 NOT NULL CHAR(10)
RENCOL5 NOT NULL CHAR(8)
RENCOL4 NOT NULL CHAR(10)
RENCOL3 NOT NULL CHAR(21)
RENCOL2 NOT NULL CHAR(25)
RENCOL1 NOT NULL CHAR(30)

SQL> select index_name from dba_indexes where table_name=’TESTTAB1′;

INDEX_NAME
——————————
TESTTAB1INDEX01
TESTTAB1INDEX02
TESTTAB1INDEX03
TESTTAB1INDEX04
TESTTAB1INDEX05

11. Drop the interim table that was created.

SQL> drop table testtab1;

Table dropped.

SQL>

12. The process is complete, be sure and verify that everything looks correct.

I have provided one example of the use of the DBMS_REDEFINITION package. It is also commonly used to partition unpartitioned tables, and there are numerous examples of that usage available with a simple google search.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: