-
i have a table named dept.
first i insert a lot of row and then delete it.
so high water mark is higher than it should be.
i should rebuild the table.
also i have a table emp and have a foreign key to dept.
question: how to rebuild dept ONLINE?
one choice is export and import.
another choice is create table as select.
1.
if we export and import.
we need first disable foreign between dept and emp.
then we truncate table dept or drop table dept.
question:if somebody insert some record into emp which refer
to noexist deptno, what happen?
2.
same thing for choice two.
can somebody tell me the detail of how to rebuild in order to
reset high water mark ONLINE?
-
alter table dept move tablespace lalalalala storage(initial 12345 next 12345 minextents 1 maxextents 1024 pctincrease 0)
if you disable foreign key then nothing will happen to the user inserting to emp, the problems arises when you enable foreign key and the deptno in both tables do not match
-
To preserve consistency, you could lock the dependency tables in the exclusive mode, then do the changes and then release the lock. Or you could take the database to the restrited user mode and then do the changes and open it.
Sam
