Re: 9i Online Table Redefinitions

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

> >

> >

> >

> >

> >

> >

> >

> >

> >

> >

>

>