dynamic sql

From: Charles J. Fisher <cfisher_at_rhadmin.org>
Date: Tue, 30 Oct 2001 17:42:32 GMT
Message-ID: <Pine.LNX.4.33.0110301137430.22853-100000@galt.rhadmin.org>

Hi. I need a way to take all of the non-system rollback segments on/offline on any Oracle server, from 7.3.4 to any release of 8.

Is this the best way to do it? It works up to 8.1.7.

DECLARE
   cur INTEGER;
   rc INTEGER;
   CURSOR roll_curs IS

      SELECT segment_name FROM dba_rollback_segs WHERE segment_name != 'SYSTEM'; BEGIN
   cur := DBMS_SQL.OPEN_CURSOR;

   FOR rname IN roll_curs LOOP

      DBMS_SQL.PARSE(cur,
         'alter rollback segment '||rname.segment_name||' offline',
            DBMS_SQL.V7);
      rc := DBMS_SQL.EXECUTE(cur);

   END LOOP;    DBMS_SQL.CLOSE_CURSOR(cur);
END;
/

---

   / Charles J. Fisher              | "A fanatic is one who can't change his /
  /  cfisher_at_rhadmin.org            |  mind and won't change the subject."  /
 /   http://rhadmin.org:81          |                 -- Winston Churchill /
---------------------------------------------------------------------------

Received on Tue Oct 30 2001 - 11:42:32 CST