Re: 9i Online Table Redefinitions

Excellent. Thanks Pete.

Now if only the documentation would say that you can't apply a function to a primary key....

Regards
HJR "Pete Sharman" <peter.sharman_at_oracle.com> wrote in message news:fYpp7.3$vj2.2924_at_inet16.us.oracle.com...
> Howard
>
> What's left behind is a materialized view log. I found that when I made a
> typo in the mapping of columns from the source table to the interim one
and
> control-C'ed out of it. You can either drop the materialized view log, or
> call the abort procedure as you've found out. As for the upper problem,
> it's because it's the primary key column I believe. Look at this. I've
> used the same definitions as you, but instead of using an upper on the
> primary key, let's modify the salary (should work):
>
> SQL> execute dbms_redefinition.start_redef_table( -
>
> > 'OLN', 'R_TEST','RTEST_HOLD', -
>
> > 'col1 dept_code,-
>
> > col2+10 salary');
>
>
> PL/SQL procedure successfully completed.
>
>
> SQL> execute
> dbms_redefinition.finish_redef_table('OLN','R_TEST','RTEST_HOLD');
>
> So you can put the function on something else. But let's look at an
example
> with upper too:
>
>
> PL/SQL procedure successfully completed.
>
>
>
>
> SQL> create table r_test1(
>
> 2 col1 char(5) constraint r_test_pk1 primary key,
>
> 3 col2 char(5));
>
>
> Table created.
>
>
> SQL> insert into r_test1 values ('aaaaa','bbbbb');
>
>
> 1 row created.
>
>
> SQL> insert into r_test1 values ('bbbbb','ccccc');
>
>
> 1 row created.
>
>
> SQL> commit;
>
>
> Commit complete.
>
>
> SQL> create table r_test2(
>
> 2 deptno char(5) constraint r_test_pk2 primary key,
>
> 3 empname char(5));
>
>
> Table created.
>
>
>
> SQL> execute dbms_redefinition.start_redef_table ( -
>
> > 'oln','r_test1','r_test2', -
>
> > 'col1 deptno, -
>
> > upper(col2) empname')
>
>
> PL/SQL procedure successfully completed.
>
>
> SQL>
>
>
> --
> HTH. Additions and corrections welcome.
>
> Pete
> Author of "Oracle8i: Architecture and Administration Exam Cram"
> Now got a life back again that the book is released!
>
> "Controlling developers is like herding cats."
> Kevin Loney, Oracle DBA Handbook
>
> "Oh no, it's not. It's much harder than that!"
> Bruce Pihlamae, long-term Oracle DBA
>
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:3ba570b1$1_at_news.iprimus.com.au...
> > 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 Mon Sep 17 2001 - 14:22:11 CDT