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;
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.