Hello,

We have 'MYROWID' column in all tables of our database which will have unique value. There are 1000 tables I need to update where records are ranging upto 30,00,000. Altough I have written procedure to do this which takes around 4 to 5 hours to update MYROWID column in all tables. Please guide me in making this procedure faster. Can I use multiple session in procedure, If yes how? or any other method. Ultimately I want to reduce this time.

CREATE OR REPLACE PROCEDURE UpdateRowId IS
CURSOR C1 IS
SELECT TNAME
FROM TAB
WHERE TABTYPE = 'TABLE' ;

Row_Rec C1%ROWTYPE ;
nCursorId INTEGER ;
nRowsProcessed INTEGER ;

BEGIN
nCursorId := DBMS_SQL.OPEN_CURSOR ;
OPEN C1 ;
LOOP
FETCH C1 INTO Row_Rec ;
EXIT WHEN C1%NOTFOUND ;

DBMS_SQL.PARSE ( nCursorId, 'UPDATE ' || Row_Rec.TNAME
|| ' SET MYROWID = SEQMYROWID.NEXTVAL ', dbms_sql.v7 );
nRowsProcessed := DBMS_SQL.EXECUTE ( nCursorId ) ;
COMMIT;
END LOOP ;
CLOSE C1 ;
DBMS_SQL.CLOSE_CURSOR ( nCursorId ) ;
END UpdateRowId ;
/


Thanks & Regards
Shailesh