I have heard of DBMS_REDEFINITION and decided to test it (using ROWIDs), in order to redefine a non-partitioned table to be a partitioned one.

I have wrote a two scripts:
1) Activate a procedure that performs "insert into... as select", it inserts 1M recs in about 20 seconds, then rest for 30 seconds and then restart another cycle which performs the inserts again ...etc'.
2) Is a script which waits (once) for 30 seconds and than performs redefinition of the same table above.

I have put 5M rows into the table and than started the following test -

I have activated the both scripts and saw that the inserts started (saw a lot of I/O writes) and than the redefinition (script 2) has started (the method START_REDEF_TABLE).

After a few seconds I have noticed that the I/O writes went down to ~zero and left it to run as is for 10 hours
After 10 hours I've checked and the saw that nothing has happened so I've checked V$LOCKED_OBJECT, I saw that both the inserts are blocked (I could identified it from using the SQL_HASH_VALUE) and that there are some locked objects called COLS$ etc'.

Made the same test twice again:
1) After it got stuck I've killed the redefinition session and saw how the inserts are not blocked anymore.
2) After it got stuck I've killed the inserts session and saw that how redefinition of the table completes in several minutes.

As far as I understood from the documentation it was not supposed to happen but seems to me that I have managed to created some sort of dead lock.

Do you have any idea how to solve it or what seems to be the problem?

1. I can supply all scripts.
2. DB Server is runs on win2k.


Tal Olier (otal@mercury.co.il).