Set Transaction use Rollback segment

[brownjd]

My question is : When using the set transaction use rollback segment in a plsql procedure, I am receiving an error saying the statement must be the first. I have the following procedure to analyze tables based upon a small window of time and want to use the larger rollback segment but when I put inside the loop after the loop statement I still am getting an error. If I leave it where I have it now, it works, but the after the dbms_stats procedure completes the first loop thru, it immediately grabs another rollback. Anyone have any ideas?

BEGIN
DBMS_TRANSACTION.use_rollback_segment (v_rb_name);

FOR tab IN find_alls
LOOP

v_whattime := TO_NUMBER (REPLACE (TO_CHAR (SYSDATE, 'HH24:MI'), ':', ''));

IF (v_whattime <= 2030)
THEN
/* Mark the start time */
v_msg := tab.msg;
logprocess ('ANALYZETABLE', 'START', 0, v_procnum, v_msg);

BEGIN

DBMS_STATS.gather_table_stats (
tab.owner,
tab.table_name,
NULL,
35,
FALSE ,
'FOR ALL COLUMNS SIZE 1',
16,
'DEFAULT',
TRUE
);
logprocess ('ANALYZETABLE', 'END', v_procnum, v_returned, NULL);
EXCEPTION
WHEN OTHERS
THEN
logerror (
'Error in analyze table process '
|| v_msg
|| ' '
|| TO_CHAR (SQLCODE)
|| ': '
|| SUBSTR (SQLERRM, 1, 500)
);
END; -- End of Begin inside the exception handler
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
logerror (
'Error in analyze table process '
|| v_msg
|| ' '
|| TO_CHAR (SQLCODE)
|| ': '
|| SUBSTR (SQLERRM, 1, 500)
);
END analyzetable;
/

 

[sem]

You may issue commit right before setting a new transaction.
The idea is that DBMS_STATS.gather_table_stats calls ddl operations implying implicit commit, thus starting a new transaction.