Oracle | PRVT

Oracle PRVT_UADV Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Utility objects supporting the Undo Advisor

AUTHID DEFINER

Dependencies

DBMS_UNDOADV_LIB	V$PARAMETER	X$KSPPCV2
DBMS_UNDO_ADV	V$ROLLSTAT	X$KSPPI
V$DATABASE	WRI$_ADV_UNDO_ADV

Documented No

First Available 10.1

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvtuadv.plb

Subprograms

CALCULATE_RBU_SIZES	GET_DBID	GET_UNDO_TABLESPACE
COUNT_UNDO_TABLESPACES	GET_UNDO_MANAGEMENT	IS_AUTOTUNE_ENABLED
EXECUTE	GET_UNDO_RETENTION

CALCULATE_RBU_SIZES

Calculates the Rollback/Undo Sizes prvt_uadv.calculate_rbu_sizes( rssz OUT NUMBER, optsz OUT NUMBER, hwmsz OUT NUMBER);

DECLARE

       rssz  NUMBER;

       optsz NUMBER;

       hwmsz NUMBER;

      BEGIN

        prvt_uadv.calculate_rbu_sizes(rssz, optsz, hwmsz);

        dbms_output.put_line('RSSZ  = ' || TO_CHAR(rssz));

        dbms_output.put_line('OPTSZ = ' || TO_CHAR(optsz));

        dbms_output.put_line('HWMSZ = ' || TO_CHAR(hwmsz));

      END;

      /

      

      RSSZ =  88129536

      OPTSZ =

      HWMSZ = 16900096

      

      PL/SQL procedure successfully completed.

COUNT_UNDO_TABLESPACES

Returns the count of UNDO tablespaces (at least in theory ... see demo at right) prvt_uadv.count_undo_tablespaces(utbcnt OUT NUMBER);

conn / as sysdba

      

      DECLARE

       outVal NUMBER;

      BEGIN

        prvt_uadv.count_undo_tablespaces(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      1

      

      ALTER PLUGGABLE DATABASE pdbdev OPEN;

      

      SELECT tablespace_name

      FROM cdb_tablespaces

      WHERE contents = 'UNDO'

      

      TABLESPACE_NAME   CON_ID

      ---------------- -------

      UNDOTBS1               3

      UNDOTBS1               1

      

      DECLARE

       outVal NUMBER;

      BEGIN

        prvt_uadv.count_undo_tablespaces(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      1

      

      -- We are not impressed but then maybe it is only for RAC

EXECUTE

Submits an advisor task for to be executed. This is not something easily demonstrated so the demo at the right is for education purposes only and will return the exceptions shown unless the advisor task exists. prvt_uadv.execute(task_id IN BINARY_INTEGER);

exec prvt_uadv.execute(562);

      BEGIN prvt_uadv.execute(562); END;

      *

      ERROR at line 1:

      ORA-13605: The specified task or object 562 does not exist for the current user.

      ORA-06512: at "SYS.PRVT_UADV", line 9

      ORA-06512: at line 1

GET_DBID

Returns the database DBID prvt_uadv.get_dbid(db_id OUT NUMBER);

SELECT dbid

      FROM v$database;

      

            DBID

      ----------

      1262297360

      

      

      DECLARE

        outVal NUMBER;

      BEGIN

        prvt_uadv.get_dbid(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      1262297360

      

      PL/SQL procedure successfully completed.

GET_UNDO_MANAGEMENT

Returns prvt_uadv.get_dbid(umgmt OUT VARCHAR2);

SQL> show parameter undo_management

      

      NAME            TYPE   VALUE

      --------------- ------ -----

      undo_management string AUTO

      

      DECLARE

        outVal VARCHAR2(30);

      BEGIN

        prvt_uadv.get_undo_management(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      AUTO

      

      PL/SQL procedure successfully completed.

GET_UNDO_RETENTION

Returns prvt_uadv.get_dbid(undo_retention OUT NUMBER);

SQL> show parameter undo_retention

      

      NAME            TYPE    VALUE

      --------------- ------- -----

      undo_retention  integer 43200

      

      DECLARE

        outVal NUMBER;

      BEGIN

        prvt_uadv.get_undo_retention(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      43200

      

      PL/SQL procedure successfully completed.

GET_UNDO_TABLESPACE

Returns the name of the current UNDO tablespace prvt_uadv.get_undo_tablespace(table_space_name OUT VARCHAR2);

DECLARE

        outVal VARCHAR2(128);

      BEGIN

        prvt_uadv.get_undo_tablespace(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      UNDOTBS1

      

      PL/SQL procedure successfully completed.

IS_AUTOTUNE_ENABLED

Returns TRUE if undo auto tuning is enabled prvt_uadv.get_dbid(autotune_enabled OUT BOOLEAN);

DECLARE

       outVal BOOLEAN;

      BEGIN

        prvt_uadv.is_autotune_enabled(outVal);

        IF outVal THEN

          dbms_output.put_line('Enabled');

        ELSE

          dbms_output.put_line('Disabled');

        END IF;

      END;

      /

      Enabled

      

      PL/SQL procedure successfully completed.