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:
- is there a way to do a refresh while maintaining the lock on the
source table?
- 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)
Received on Mon Sep 23 2002 - 16:04:13 CDT
