Re: dynamic sql

From: Trifon Anguelov <trifona_at_pacbell.net>
Date: Tue, 30 Oct 2001 17:59:03 GMT
Message-ID: <HTBD7.6416$hF2.2483230739@newssvr14.news.prodigy.com>

"Charles J. Fisher" <cfisher_at_rhadmin.org> wrote in message news:Pine.LNX.4.33.0110301137430.22853-100000_at_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
/
> --------------------------------------------------------------------------
-
>

Charles,

Whatever script you write, just make sure no active transactions are using any of the rollback segments you are trying to offline. Also you need at least two rollback segments active in any given time, so do the math right.

Join xidusn.v$transaction and usn.v$rollname to get the name.v$rollname (rollback segment currently in use from transaction). You will not be able to take active rollback segment offline.

May be you have to include a loop to check when the active transaction will release the busy rollback segment and then take it offline.

Hope that helps.

Trifon Anguelov
Senior Oracle DBA

http://www.dbaclick.com Received on Tue Oct 30 2001 - 11:59:03 CST