9i Online Table Redefinitions

From: Howard J. Rogers <howardjr_at_www.com>
Date: Mon, 17 Sep 2001 13:30:50 +1000
Message-ID: <3ba56da5@news.iprimus.com.au>

I know it's a bit early, but if any one has experience of this I'd be grateful!

Online table redefinitions appear to work by taking a 'snapshot' of the original table, and merging that snapshot into the interim table created. Those snapshots are, of course, materialized views.

Trouble is, any table with a materialized view on it is not a candidate for an online redefintion.

Why is that a trouble? Because if you start a redefinition, but make a syntax error somewhere later in the command to start_redef_table, it appears that the materialized view needed to do the redefinition is created at the very beginning. Then, of course, the thing blows out at the syntax error. So you fix up the syntax error, and try again: only this time, the thing fails with an ORA_12091: cannot online redefine table "SCHEMA"."TABLE_NAME" with materialized views.

A quick query of dba_mviews results in zero records being selected... and hence my problem. How can you get rid of the temporary materialized view so that your gleaming new syntax can do its stuff on a second run-through?

I've tried 'execute dbms_redefinition.finish_redef_table, but that requires three parameters, including the name of the interim table -which bombs out with another error because, since the original 'start_redef' procedure failed, there *isn't* an interim table yet -not one properly registered *as* the interim table, anyway.

In fact, the only thing that appears to get rid of the spurious materialized view and allow a second chance is a drop of the original table, followed by a re-create! Hardly what I'd call an 'online' option.

Anyone found out how to 'pop the stack' and permit a second attempt at an online redef?

Just in case I haven't made much sense, here's the session script:

SQL> create table r_test(
2 col1 char(5) constraint r_test_pk primary key, 3 col2 number(7,2));
Table created.

SQL> insert into r_test values ('aaaa',1238.90);
SQL> insert into r_test values ('bbbb',4329.30);
SQL> commit;

Commit complete.

SQL> select * from r_test;
COL1 COL2
----- ----------

aaaa      1238.9
bbbb      4329.3

SQL> create table rtest_hold(
2 dept_code char(5) constraint rtest_pk primary key, 3 salary number(7,2));
Table created.

So now theres an r_test table, badly defined, and an rtest_hold interim table with the improved definition. Now I try to start the redefinition, but attempting to uppercase the values in Col1 during the process:

SQL> execute dbms_redefinition.start_redef_table( -
> 'SCOTT','R_TEST','RTEST_HOLD', -
> 'upper(col1) dept_code,-
> col2 salary')

BEGIN dbms_redefinition.start_redef_table( 'SCOTT','R_TEST','RTEST_HOLD', 'upper(col1) dept_code,

*
ERROR at line 1:

ORA-12016: materialized view does not include all primary key columns
ORA-12016: materialized view does not include all primary key columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 138
ORA-06512: at line 1

Despite the documentation saying an 'upper' function is legitimate here, I can't get the damn thing to work. Anyway -the point is, the thing has bombed out at the 'upper' function (with a typically informative error message!). So let's get rid of the thing and try again:

SQL> execute dbms_redefinition.start_redef_table( -
> 'SCOTT','R_TEST','RTEST_HOLD', -
> 'col1 dept_code,-
> col2 salary')

BEGIN dbms_redefinition.start_redef_table( 'SCOTT','R_TEST','RTEST_HOLD', 'col1 dept_code, col2 sa

*
ERROR at line 1:
ORA-12091: cannot online redefine table "SCOTT"."R_TEST" with materialized views

ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 138
ORA-06512: at line 1

There are NO real materialized views on this table. But I can't proceed further beyond this point, because Oracle thinks there is.

If we have any online redefinition experts in-house, I'd be grateful for advice on how to move on beyond this point!

Regards
HJR Received on Sun Sep 16 2001 - 22:30:50 CDT