What happens if I rebuild a table online?

[ssmith]

I want to rebuild an 'active' table online, e.g. alter table ... move .... Will this cause any problems to users, i.e. locks etc.
Thanks.

[noor]

I found this in METALINK :

"When you move a table, Oracle will take out an exclusive lock on it as it does with any other ALTER TABLE operation. Any DML the users issue will hang waiting for the lock to be released. "

[pando]

in 8i you get a lock, in 9i you should be able to move online

[jmodic]

Originally posted by pando
in 8i you get a lock, in 9i you should be able to move online

No, even in 9i (at least in release 1) you can't move table online (that is by using command "ALTER TABLE .... MOVE ONLINE;" unless it is index-organized table (IOT). So you still get a lock on the table and no DML activities are possible for the duration of the MOVE process.

Mybe this has changed in 9iRel2, haven't check yet....

[pando]

I havent checked neither, I was told by a guy who works as on-site dba in oracle (but then again this guy is clueless.... even working in oracle but well I took his word since he could get some interesting notes from web iv heh)

[julian]

Jurij's right. Even in 9iR2, there is no 'alter table emp rebuild online' command. But there is a new package called DBMS_REDEFINITON. With it, you can rebuild tables online by using incrementally maintainable materialized views. There are some restrictions however:

1. The table must have a primary key.

2. Tables with materialized views and snapshot logs cannot be rebuilt online.

3. MVIEW container tables and AQ tables cannot be reorganized online.

4. IOT overflow tables cannot be reorganized online.

5. Tables containing user-defined types, bfile or long columns cannot be rebuilt online.

[kgb]

When you movig table all indexes become invalid.