| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 9i Online Table Redefinitions
In article <3ba570b1$1_at_news.iprimus.com.au>, "Howard says...
>
>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
it works...
tkyte_at_TKYTE9I.US.ORACLE.COM> create table t NOLOGGING
2 as
3 select * from all_objects;
Table created.
tkyte_at_TKYTE9I.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id);
Table altered.
tkyte_at_TKYTE9I.US.ORACLE.COM> exec dbms_redefinition.can_redef_table( user, 'T' );
PL/SQL procedure successfully completed.
tkyte_at_TKYTE9I.US.ORACLE.COM> create table t_interim 2 (
3 object_id number primary key, 4 object_type varchar2(18), 5 owner varchar2(30), 6 oname varchar2(30), 7 created date, 8 last_ddl_time date, 9 status varchar2(7), 10 flags varchar2(5)
Table created.
tkyte_at_TKYTE9I.US.ORACLE.COM> declare
2 l_colmap varchar(512);
3 begin
4 l_colmap := 5 'object_id, 6 object_type, 7 upper(owner) owner, 8 object_name oname, 9 created, 10 last_ddl_time, 11 status, 12 temporary || ''/'' || generated || ''/'' || secondary flags '; 13 14 dbms_redefinition.start_redef_table 15 ( uname => user, 16 orig_table => 'T', 17 int_table => 'T_INTERIM', 18 col_mapping => l_colmap );19 end;
PL/SQL procedure successfully completed.
tkyte_at_TKYTE9I.US.ORACLE.COM> begin
2 dbms_redefinition.sync_interim_table( user, 'T', 'T_INTERIM' );
3 end;
4 /
PL/SQL procedure successfully completed.
tkyte_at_TKYTE9I.US.ORACLE.COM> begin
2 dbms_redefinition.finish_redef_table( user, 'T', 'T_INTERIM' );
3 end;
4 /
PL/SQL procedure successfully completed.
>
>"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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Mon Sep 17 2001 - 08:21:28 CDT
 |
 |