Re: pl/sql rollback segs

In article <356AF09B.1205_at_otaywater.gov>,   lhignight_at_otaywater.gov wrote:
>
> Anyone have an example of pl/sql code which gets the status of a
> rollback segment, then brings it online if it's not already online?
> I've tried the code below, but I get errors when referencing
> sys.dba_rollback_segs..
>
> Thanks - Lane
> ----------------------------------------------------------------------
> PROCEDURE GETMETROZIP IS
> /* this procedure should check rollback status and alter rollback
> segment if necessary,
> for now, this code is in client.. */
>
> lv_Status varchar2(10);
> dbinternal Integer;
> BEGIN
> dbinternal := DBMS_SQL.OPEN_CURSOR;
> SELECT STATUS INTO lv_Status FROM SYS.DBA_ROLLBACK_SEGS
> WHERE SEGMENT_NAME='RBCONV0';
> IF lv_Status = 'OFFLINE'
> THEN
> DBMS_SQL.PARSE(dbinternal, 'ALTER ROLLBACK SEGMENT RBCONV0
> ONLINE', dbms_sql.v7);
> END IF;
> SET TRANSACTION USE ROLLBACK SEGMENT RBCONV0;
> UPDATE COREPARCELCONSTRUCT A SET A.SITUS_ZIP = (SELECT B.SITEZIP FROM
> METROSCAN B WHERE B.PARCEL=A.COUNTYASSESSORPARCEL_ID);
> DBMS_SQL.PARSE(dbinternal, 'ALTER ROLLBACK SEGMENT RBCONV0 OFFLINE',
> dbms_sql.v7);
> DBMS_SQL.CLOSE_CURSOR(dbinternal);
> END; -- Procedure
> ------------------------------------------------------------------
> Error
> (1):PLS-00201: identifier 'SYS.DBA_ROLLBACK_SEGS' must be declared
>

Hi,

Your code is absolutely Ok. I guess you tried compiling the code with a under- privilaged user. Try creating the procedure with a user who has 'SELECT ANY TABLE' privilage. It should work.

Best of Luck,

Saravanan
(saro_at_psoux141.sgp.hp.com)

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu May 28 1998 - 01:19:42 CDT