Re: pl/sql rollback segs

Lane Hignight 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

I suggest you this 3 solutions:

1. try to execute this procedure as SYS or INTERNAL user
2. if you cannot use SYS or INTERNAL you'd GRANT SELECT ON SYS.DBA_ROLLBACK_SEGS TO <USER> WITH GRANT OPTION;
3. alternatively you'd create your procedure under SYS and then GRANT EXECUTE ON GETMETROZIP TO <USER>

Bye.

--
Mauro Giovara (mauro.giovara_at_its.it)
Phone: +39 11 6853066
Fax: +39 11 6853583 Received on Wed May 27 1998 - 07:46:50 CDT