Oracle PRVT_UADV
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
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;

SQL> SELECT tablespace_name
  2  FROM cdb_tablespaces
  3  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);
SQL> 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 (new 18c)
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 (new 18c)
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 (new 18c)
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 (new 18c)
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 (new 18c)
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.

Related Topics
Built-in Functions
Built-in Packages
DBMS_UNDO_ADV
Tablespaces
What's New In 18cR3
What's New In 19cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx