Oracle 12cR2 DBMS_REGISTRY

Oracle DBMS_REGISTRY_SYS Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"

~ Sen. Tammy Duckworth

Purpose Internal functions used by SYS during upgrade/downgrade

AUTHID DEFINER

Dependencies

CONTAINER$	DBMS_STANDARD	REGISTRY$
DBA_REGISTRY	DBMS_STATS	REGISTRY$HISTORY
DBA_SEGMENTS	DBMS_SYSTEM	REGISTRY$LOG
DBA_TRIGGERS	DBMS_SYS_ERROR	REGISTRY$SCHEMAS
DBMS_ASSERT	DUAL	USER$
DBMS_OUTPUT	OBJ$	UTL_FILE
DBMS_REGISTRY	PLITBLM	V$INSTANCE
DBMS_REGISTRY_SIMPLE	PROPS$	V$OPTION
DBMS_SESSION

Documented No

First Available 2006

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/dbmscr.sql

Subprograms

CHECK_COMPONENT_DOWNGRADES	PATCH_SCRIPT	TIME_STAMP
CPU_SCRIPT	POPULATE	TIME_STAMP_COMP_DISPLAY
DBDWG_SCRIPT	POPULATE_101	TIME_STAMP_DISPLAY
DBUPG_SCRIPT	POPULATE_102	UPDATE_PROPS_DATA
DELETE_PROPS_DATA	POPULATE_92	UTLMMIG_SCRIPT_NAME
DIAGNOSTICS	RECORD_ACTION	VALIDATE_CATALOG
DIR_EXISTS_AND_IS_WRITABLE	RELOD_SCRIPT	VALIDATE_CATJAVA
DROP_USER	REMOVAL_SCRIPT	VALIDATE_CATPROC
GATHER_STATS	RESOLVE_CATJAVA	VALIDATE_COMPONENTS
GEN_PFILE_FROM_MEMORY	SELECT_PROPS_DATA	VALIDATE_RDBMS_IN_NORMAL_MODE
GET_PFILE_PATH	SET_REGISTRY_CONTEXT	VALIDATE_RDBMS_IN_UPGRADE_MODE
INSERT_PROPS_DATA

CHECK_COMPONENT_DOWNGRADES

Undocumented dbms_registry_sys.check_component_downgrades;

exec dbms_registry_sys.check_component_downgrades;

CPU_SCRIPT

Undocumented dbms_registry_sys.cpu_script(comp_id IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_registry_sys.cpu_script('APEX')

      FROM dual;

DBDWG_SCRIPT

Returns the name of the script that downgrades the component dbms_registry_sys.dbdwg_script(comp_id IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_registry_sys.dbdwg_script('APEX')

      FROM dual;

DBUPG_SCRIPT

Returns the name of the script that upgrades the component dbms_registry_sys.dbupg_script(comp_id IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_registry_sys.dbupg_script('APEX')

      FROM dual;

DELETE_PROPS_DATA (new 12.1)

Delete a registry property dbms_registry_sys.delete_props_data(pname IN VARCHAR2) RETURN BOOLEAN;

TBD

DIAGNOSTICS

Undocumented dbms_registry_sys.diagnostics RETURN NUMBER;

SELECT dbms_registry_sys.diagnostics

      FROM dual;

DIR_EXISTS_AND_IS_WRITABLE (new 12.1)

Returns TRUE if a writable directory exists dbms_registry_sys.dir_exists_and_is_writable(dirname IN VARCHAR2) RETURN BOOLEAN;

SELECT directory_name

      FROM dba_directories

      ORDER BY 1;

      

      BEGIN

        IF dbms_registry_sys.dir_exists_and_is_writable('XMLDIR') THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

DROP_USER

One might assume that this can be used to drop a user. One would assume so incorrectly it seems. dbms_registry_sys,drop_user(username IN VARCHAR2);

SQL> exec dbms_registry_sys.drop_user('MECHID');

      

      PL/SQL procedure successfully completed.

      

      SQL> SELECT username FROM dba_users WHERE username like 'M%';

      

      USERNAME

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

      MLIB

      MGMT_VIEW

      MECHID

      MDDATA

      MDSYS

GATHER_STATS

Undocumented dbms_registry_sys.gather_stats(comp_id IN VARCHAR2); -- Default (NULL) gathers for all components

-- see also {$ORACLE_HOME}/rdbms/admin/cmpupend.sql
exec dbms_registry_sys.gather_stats(NULL);

GEN_PFILE_FROM_MEMORY (new 12.1)

Essentially identical with CREATE PFILE FROM MEMORY; dbms_registry_sys.gen_pfile_from_memory RETURN VARCHAR2;

SELECT dbms_registry_sys.gen_pfile_from_memory

      FROM dual;

GET_PFILE_PATH (new 12.1)

Given a directory object name returns the fully resolved path name in the host operating system dbms_registry_sys.get_pfile_path(dirname IN VACHAR2) RETURN VARCHAR2;

SELECT dbms_registry_sys.get_pfile_path('CTEMP')

      FROM dual;

INSERT_PROPS_DATA (new 12.1)

Insert a registry property dbms_registry_sys.insert_props_data( pname IN VARCHAR2, pvalue IN VARCHAR2, pcomment IN VARCHAR2) RETURN BOOLEAN;

SELECT COUNT(*)

      FROM dba_registry;

      

      BEGIN

        IF dbms_registry_sys.insert_props_data('TEST', 'TEST', 'TEST') THEN

          dbms_output.put_line('Inserted It');

        ELSE

          dbms_output.put_line('Fail');

        END IF;

      

        IF dbms_registry_sys.select_props_data('TEST') THEN

          dbms_output.put_line('Found It');

        END IF;

      

        IF dbms_registry_sys.update_props_data('TEST', 'TEST2') THEN

          dbms_output.put_line('Updated It');

        END IF;

      

        IF dbms_registry_sys.delete_props_data('TEST') THEN

          dbms_output.put_line('Deleted It: I Presume');

        END IF;

      END;

      /

-- nothing failed but I've no idea what it did ... so strongly recommend not doing it except on a throw-away database

PATCH_SCRIPT

Returns the name of the script that patches the component dbms_registry_sys.patch_script(comp_id IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_registry_sys.patch_script('APEX')

      FROM dual;

      

      SELECT dbms_registry_sys.patch_script('XDB')

      FROM dual;

POPULATE

Undocumented dbms_registry_sys.populate;

exec dbms_registry_sys.populate;

POPULATE_101

Undocumented dbms_registry_sys.populate_101;

exec dbms_registry_sys.populate_101;

POPULATE_102

Undocumented dbms_registry_sys.populate_102;

exec dbms_registry_sys.populate_102;

POPULATE_92

Undocumented dbms_registry_sys.populate_92;

exec dbms_registry_sys.populate_92;

RECORD_ACTION

Undocumented dbms_registry_sys.record_action( action IN VARCHAR2, action_id IN NUMBER, comments IN VARCHAR2);

TBD

RELOD_SCRIPT

Returns the name of the script that reloads the component dbms_registry_sys.relod_script(comp_id IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_registry_sys.relod_script('APEX')

      FROM dual;

REMOVAL_SCRIPT

Returns the name of the script that removes the component dbms_registry_sys.removal_script(comp_id IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_registry_sys.removal_script('APEX')

      FROM dual;

RESOLVE_CATJAVA (new 12.1)

Undocumented dbms_registry_sys.resolve_catjava;

SQL> exec dbms_registry_sys.resolve_catjava;

      Updating Classes....

      After Update Invalid Class Count = 0

      

      PL/SQL procedure successfully completed.

SELECT_PROPS_DATA (new 12.1)

Undocumented dbms_registry_sys.select_props_data(pname IN VARCHAR2) RETURN BOOLEAN;

See INSERT_PROPS_DATA Demo Above

SET_REGISTRY_CONTEXT

Undocumented dbms_registry_sys.set_registry_context(ctx_variable IN VARCHAR2, ctx_value IN VARCHAR2);

TBD

TIME_STAMP

Undocumented dbms_registry_sys.time_stamp(comp_id IN VARCHAR2) RETURN VARCHAR2;

-- see {$ORACLE_HOME}/rdbms/admin/cmpupend.sql. This example collects optimizer stats for all server component schemas

SELECT dbms_registry_sys.time_stamp('STATS_BGN') as timestamp FROM dual;

-- STATS_BGN is not a registry component last time I looked

TIME_STAMP_COMP_DISPLAY (new 12.1)

Undocumented: Returned the current date-time when tested dbms_registry_sys.time_stamp_comp_display(comp_id IN VARCHAR2) RETURN VARCHAR2;

conn / as sysdba

      

      col comp_name format a35

      

      SELECT comp_id, comp_name, modified

      FROM dba_registry

      ORDER BY 1;

      

      SELECT dbms_registry_sys.time_stamp_comp_display('CATALOG')

      FROM dual;

TIME_STAMP_DISPLAY (new 12.1)

Undocumented: Returned the current date-time when tested dbms_registry_sys.time_stamp_display(comp_id IN VARCHAR2) RETURN VARCHAR2;

conn / as sysdba

      

      col comp_name format a35

      

      SELECT comp_id, comp_name, modified

      FROM dba_registry

      ORDER BY 1;

      

      SELECT dbms_registry_sys.time_stamp_display('CATALOG')

      FROM dual;

UPDATE_PROPS_DATA (new 12.1)

Update a registry property dbms_registry_sys.update_props_data ( pname IN VARCHAR2, pvalue IN VARCHAR2) RETURN BOOLEAN;

See INSERT_PROPS_DATA Demo Above

UTLMMIG_SCRIPT_NAME (new 12.1)

The script utlmmig.sql is a mini migration script that replaces bootstrap tables with new definitions and new indexes dbms_registry_sys.utlmmig_script_name RETURN VARCHAR2;

SQL> SELECT dbms_registry_sys.utlmmig_script_name

         2 FROM dual;

      

      UTLMMIG_SCRIPT_NAME

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

      ?/rdbms/admin/utlmmig.sql

      

      sPrvVersion = nPrevMajorVer = 0

      sRetFunc = ?/rdbms/admin/utlmmig.sql

VALIDATE_CATALOG

Undocumented dbms_registry_sys.validate_catalog;

exec dbms_registry_sys.validate_catalog;

VALIDATE_CATJAVA

Undocumented dbms_registry_sys.validate_catjava;

exec dbms_registry_sys.validate_catjava;

VALIDATE_CATPROC

Undocumented dbms_registry_sys.validate_catproc;

exec dbms_registry_sys.validate_catproc;

VALIDATE_COMPONENTS

Undocumented dbms_registry_sys.validate_components;

exec dbms_registry_sys.validate_components;

VALIDATE_RDBMS_IN_NORMAL_MODE (new 12.1)

Validates a database registry entry when not in upgrade mode dbms_registry_sys.validate_rdbms_in_normal_mode(comp_id IN VARCHAR2);

exec dbms_registry_sys.validate_rdbms_in_normal_mode('CATALOG');

      

      exec dbms_registry_sys.validate_rdbms_in_normal_mode('RAC');

VALIDATE_RDBMS_IN_UPGRADE_MODE (new 12.1)

Undocumented and I'd recommend on general principle not running this on a system not currently being upgraded and one that is purely instantiated for learning purposes. dbms_registry_sys.validate_rdbms_in_upgrade_mode(comp_id IN VARCHAR2);

exec dbms_registry_sys.validate_rdbms_in_upgrade_mode ('CATALOG');