We need a "quiet point" at both start and end of the process.
For example, I did this in isolation:
SQL> create table t (
2 x int primary key,
3 c clob );
Table created.
SQL>
SQL>
SQL> insert into t
2 select rownum, rpad('x',32000,'x')
3 from dual
4 connect by level <= 3000;
3000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> set timing on
SQL> alter table t move lob(c) store as ( tablespace users ) online;
Table altered.
Elapsed: 00:00:05.65So I need 5 seconds to move the data.
Then I did the following tests:
Session 1: delete from t where x= 1;
Session 2: alter table move; (which waits)
When I committed session 1, session 2 returned AFTER 5 seconds, thus the move operation had not done any work until session 1 committed.
Then I flipped it
Session 1: alter table move; (starts)
Session 2: delete from t where x= 1;
Session 1 never returns, but when I commit session 2, session 1 'instantly' completes, ie, most of the work was done.
We need a moment of no txn activity to commence and complete the operation.