Re: 9i Online Table Redefinitions

God, I hate replying to my own posts...

But I completely overlooked the
dbms_redefinition.abort_redef_table('SCHEMA','TABLENAME','INTERIM_TABLENAME' ) procedure, which does exactly what I needed it to do.

I would still like to know why the 'upper(column)' function doesn't work as a redefinition exercise, though -since the documentation says it will.

Regards
HJR "Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3ba56da5_at_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:43:51 CDT