Re: 9i table redefinition in 8.1.7

jfixsen_at_gbronline.com (Jason Fixsen) wrote in message news:<6e42c0ae.0209231304.e6af71e_at_posting.google.com>...
> Hello. I'm implementing the 9i table redef procedure in 8.1.7. I've
> got it to work, but it's not as clean as I'd like. I have 2 quick
> questions, but first here's my session info:
>
> -- jay is the original table, jay_new is the new table
>
> drop materialized view jay_new
> /
>
> drop table jay_new
> /
>
> -- create the new table
> create table jay_new
> as
> select *
> from jay
> where 1=2
> /
>
> -- first, create the snapshot log to start tracking any new changes
> CREATE SNAPSHOT LOG ON jay
> STORAGE
> (
> INITIAL 1M
> NEXT 1M
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
> MINEXTENTS 1
> MAXEXTENTS UNLIMITED
> )
> TABLESPACE dataload
> NOLOGGING
> with primary key
> /
>
>
> -- now, create the snapshot so you can do a refresh on it
> -- to sync up the tables.
> -- NOTE: The prior step will create a snapshot log and start tracking
> -- changes. When you run this, it basically truncates the above log
> -- and starts the changes again.
> create snapshot jay_new
> on prebuilt table with reduced precision
> refresh fast with primary key
> as
> select *
> from jay
> /
>
>
> -- now, you are ready to insert the current rows since you have the
> -- log tracking changes in the background.
>
> -- However, first you have to call this or you get a ORA-01732: data
> manipulation operation not legal
>
> -- on this view error. I guess it's an unsupported oracle feature.
> exec DBMS_SNAPSHOT.SET_I_AM_A_REFRESH(true);
>
> -- insert the rows in bulk using parallel/append
> insert into jay_new select * from jay
> /
>
>
> -- set this again
> exec DBMS_SNAPSHOT.SET_I_AM_A_REFRESH(false);
>
>
> -- this loads records that were modified after the snapshot log was
> created.
> -- This will be the longer of the two refreshes since it's updating
> changes
> -- that occured while the table was being created.
> -- NOTE: this does it's thing and then truncate the snapshot log
> exec dbms_mview.refresh('jay_new');
>
>
> -- CREATE THE INDEXES HERE
> -- lock the table
> lock table jay in exclusive mode
> /
>
>
> -- sync one last time. NOTE: THE REFRESH RELEASES THE LOCK FOR SOME
> REASON
> -- NEED TO FIGURE OUT HOW TO NOT MAKE IT DO THAT. DBMS_MVIEW MUST BE
> DOING A
> -- COMMIT. THIS IS QUESTION 1. WHY?
> exec dbms_mview.refresh('jay_new');
> lock table jay in exclusive mode
> /
>
>
> -- this isn't real elegant, but it works. You're just going to have a
> user processing
> -- error if something is waiting on the table. QUESTION 2: HOW DO I
> SWITCH
> OUT THE TABLES?
>
> drop table jay
> /
>
> rename jay_new to jay
> /
>
>
>
> ---------------------------
> -- clean up you mess
> ---------------------------
> drop materialized view jay_new
> /
>
>
> drop snapshot log on jay
> /
>
>
> I put my questions in the code above. The first one is:
>
> 1) is there a way to do a refresh while maintaining the lock on the
> source table?
>
> 2) if a user process is waiting on table JAY, but first I want to
> replace JAY with JAY_NEW, and then run the user process, is there a
> way to do it without having the user process die?
>
>
> Thanks in advance.
>
> Jason Fixsen (jfixsen_at_gbronline.com)

Here's a little piece of knowledge I thought I'd pass along to those trying to implement the DBMS_REDEFINITION equivalent in 8.1.7.

I have table A which I want to rebuild into the a new table B. I create a snapshot log on A, create a new table B, then create a materialized view/snapshot B on the pre-built table. I then insert the rows into B from A, sync up the rows from the log, lock it, sync again, drop table A and rename the underlying table B to table A, and then finally drop the materialized view B. Here's the problem: 8.1.7 (and maybe 8.1.6 also) has a bug (tar 1335477 in metalink) when dropping a materialized view in that it doesn't get all the underlying objects cleaned up, and essentially the new table A (formerly table B) looks like everything is ok in dba_tables, dba_objects, etc, but internally it still thinks it's a materialized view which becomes apparent when you try to update a row and get the error:

ORA-01732: data manipulation operation not legal on this view.

Or try dropping the table and get an error saying you have to run DROP MATERIALIZED VIEW instead, but then you try that and it doesn't exist (see the solution to this down below).

For the update problem, you can get around it by running DBMS_SNAPSHOT.SET_I_AM_A_REFRESH(true); before the update command, but that's only good for the session.

After doing some research, I think the problem is partly due to the column tab$.property

--log in as SYS
select OWNER,OBJECT_NAME,object_type,object_id,property from dba_objects o,tab$ t
where object_name in ('TABLE_A')
and o.object_id = t.obj#

It'll be set to 100663296 which has something to do with the table being a snapshot.

If you are trying to drop the newly created table A (formerly the materialized view), you can't do it as I've already stated. If you do the following you'll be able to drop the table, but it doesn't work for being able to update the table.

SQL>select object_id from user_objects where object_name='TABLE_A';

 OBJECT_ID

      3677

1 row selected.

SQL>update tab$ set property=33554432 where obj#=3677;

1 row updated.

SQL>drop table s2;

Table dropped.

SQL>select * from dba_objects where object_name='S2';

no rows selected

A snapshot table has a property value of X'6000000'.

Note: this problem can occur with versions 8.1.6 and 8.1.7 but not in 8.1.5 (property value was X'2000000'=33554432 and does allow the drop table statement).

I went to metalink and found the TAR, but it said that it couldn't give out instruction on how to fix the update problem via that medium and that you have to enter a new tar and I guess they'll send you the instructions.

In the meantime, I'm doing a CTAS on table A and will go through the drop procedure above and then rename the table, which sounds crazy, but since nobody can update that table anyways, it works.

I'm going to post this on the oracle group in google under my original ticket that I had asked you about. I hope to get it all figured out, because aside from this one, albeit major problem, the process kicked.

Thanks,

Jason

PS. Another piece of advice that I learned is to definitely create the primary key index on table B after you do the inserts into it. Otherwise, when you run the REFRESH, it will be doing FTS for each row in the log that needs updated instead of using the index. If you have a lot of rows in table B, that is a major performance implication. Received on Wed Sep 25 2002 - 15:53:52 CDT