Title: Dynamically Setting the Transaction to Use the Largest Rollback Segment

Author:Neminath Gadgade, a Software Engineer for Eccubed Inc. in Wilton, Connecticut.

This script dynamically sets the transaction to use the largest rollback segment in PL/SQL. This is usefull for batch operations
like buld data loads that contain long transactions thus eliminating the need to dynamically allocate additional extents which can
reduce overall system performance.

Example
BEGIN
UseBigRollbackSegment;
. DMLS...
. DMLS...
END;

/* ***************************************************************************** */
/* Author : Neminath Gadagade.                                                    */
/* Email  : ngadgade@eccubed.com                                                  */
/* Purpose: This procedure Dynamically sets transaction to use the largest        */
/*          available rollback Segment in the Oracle database                     */
/* ********************************************************************************/
CREATE OR REPLACE PROCEDURE UseBigRollbackSegment
AS
l_string      VARCHAR2(200) := 'set transaction use rollback segment ';
l_open_cursor INTEGER;
l_rollback_segment VARCHAR2(30);
l_execute NUMBER ;
BEGIN
    SELECT segment_name
      INTO l_rollback_segment
    FROM   dba_rollback_segs
    WHERE  status='ONLINE'
      AND  ROWNUM=1
      AND  initial_extent IN (
             SELECT MAX(initial_extent)
             FROM dba_rollback_segs
             WHERE status='ONLINE');
   IF l_rollback_segment is not null then
     l_string := l_string ||l_rollback_segment;
     COMMIT;
     l_open_cursor := dbms_sql.open_cursor;
     dbms_sql.parse(l_open_cursor,l_string,dbms_sql.v7);
     l_execute := dbms_sql.execute(l_open_cursor);
   END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
show errors