Oracle | DBMS_REGISTRY

Oracle DBMS_REGISTRY_SYS 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 Internal functions used by SYS during upgrades and downgrades.

AUTHID DEFINER

Dependencies

CONTAINER$	DBMS_SYSTEM	REGISTRY$LOG
DBA_TRIGGERS	DBMS_SYS_ERROR	REGISTRY$SCHEMAS
DBMS_ASSERT	DBMS_TTS	USER$
DBMS_CRYPTO	DUAL	UTL_FILE
DBMS_OUTPUT	OBJ$	UTL_RAW
DBMS_PLUGTS	PLITBLM	V$INSTANCE
DBMS_REGISTRY	PROPS$	V$OPTION
DBMS_REGISTRY_SIMPLE	REGISTRY$	V$PARAMETER
DBMS_SESSION	REGISTRY$DATABASE	V$PARAMETER2
DBMS_STANDARD	REGISTRY$HISTORY	XOQ_VALIDATE
DBMS_STATS

Documented No

First Available 2006

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

Security Model Owned by SYS with no priviileges granted

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

Subprograms

CAPITALIZE_SINGLE_QUOTED	NO_CATALOG	TIME_STAMP
CATCON_QUERY	POPULATE	TIME_STAMP_COMP_DISPLAY
CHECK_COMPONENT_DOWNGRADES	RECORD_ACTION	TIME_STAMP_DISPLAY
DBDWG_SCRIPT	RELOD_SCRIPT	UPDATE_PROPS_DATA
DBUPG_SCRIPT	REMOVAL_SCRIPT	UTLMMIG_SCRIPT_NAME
DELETE_PROPS_DATA	RESOLVE_CATJAVA	VALIDATE_CATALOG
DIAGNOSTICS	SELECT_PROPS_DATA	VALIDATE_CATJAVA
DROP_USER	SET_REGISTRY_CONTEXT	VALIDATE_CATPROC
GATHER_STATS	SET_XE_DATABASE	VALIDATE_COMPONENTS
INSERT_PROPS_DATA

CAPITALIZE_SINGLE_QUOTED

Delete a registry property dbms_registry_sys.capitalize_single_quoted(comp IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_registry_sys.capitalize_single_quoted('Morgan''s Library')

      FROM dual;

      

      
      DBMS_REGISTRY_SYS.CAPITALIZE_SINGLE_QUOTED('MORGAN''SLIBRARY')

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

      MORGAN'S LIBRARY

CATCON_QUERY

Delete a registry property

Warning: This could be highly destructive. Do not run this on any database that has value. dbms_registry_sys.catcon_query(comp_id IN VARCHAR2) RETURN NUMBER;

col comp_name format a40

      

      SELECT comp_id, comp_name

      FROM dba_registry

      ORDER BY 1;

      

      SELECT dbms_registry_sys.catcon_query('JAVAVM')

      FROM dual;

CHECK_COMPONENT_DOWNGRADES

Undocumented dbms_registry_sys.check_component_downgrades;

exec dbms_registry_sys.check_component_downgrades;

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('JAVAVM')

      FROM dual;

      

      DBMS_REGISTRY_SYS.DBDWG_SCRIPT('JAVAVM')

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

      ?/javavm/install/jvmdwgrd.sql

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('CATJAVA')

      FROM dual;

      

      DBMS_REGISTRY_SYS.DBUPG_SCRIPT('CATJAVA')

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

      ?/rdbms/admin/nothing.sql

DELETE_PROPS_DATA

Delete a registry property dbms_registry_sys.delete_props_data(pname IN VARCHAR2) RETURN BOOLEAN; PRAGMA SUPPLEMENTAL_LOG_DATA(delete_props_data, UNSUPPORTED);

TBD

DIAGNOSTICS

Undocumented dbms_registry_sys.diagnostics RETURN NUMBER;

SELECT dbms_registry_sys.diagnostics

      FROM dual;

      

      DIAGNOSTICS

      -----------

                0

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

      

      PL/SQL procedure successfully completed.

INSERT_PROPS_DATA

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

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

NO_CATALOG (new 21c)

Returns TRUE if the component is listed dbms_registry_sys.no_catalog(comp IN VARCHAR2) RETURN BOOLEAN; PRAGMA SUPPLEMENTAL_LOG_DATA(no_catalog, READ_ONLY);

BEGIN

        IF dbms_registry_sys.no_catalog('RAC') THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      F

      

      PL/SQL procedure successfully completed.

POPULATE

Undocumented dbms_registry_sys.populate; PRAGMA SUPPLEMENTAL_LOG_DATA(populate, UNSUPPORTED);

exec dbms_registry_sys.populate;

      

      PL/SQL procedure successfully completed.

RECORD_ACTION

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

TBD

RELOD_SCRIPT

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

SELECT dbms_registry_sys.relod_script('JAVAVM')

      FROM dual;

      

      DBMS_REGISTRY_SYS.RELOD_SCRIPT('JAVAVM')

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

      ?/javavm/install/jvmrelod.sql

REMOVAL_SCRIPT

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

SELECT dbms_registry_sys.removal_script('JAVAVM')

      FROM dual;

      

      DBMS_REGISTRY_SYS.REMOVAL_SCRIPT('JAVAVM')

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

      ?/rdbms/admin/nothing.sql

      

      JAVAVM cannot be removed as it has the following dependencies:

      Component: LCTR Namespace: SERVER

      Component: ORDIM Namespace: SERVER

      Component: SDO Namespace: SERVER

RESOLVE_CATJAVA

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

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

SET_XE_DATABASE (new 21c)

Fix for XE Seed bug 31454474 dbms_registry_sys.set_xe_database;

SELECT dbms_registry_sys.set_xe_database;

      

      PL/SQL procedure successfully completed.

TIME_STAMP

Undocumented

Demo code at right, written by Oracle, can be found in cmpupend.sql dbms_registry_sys.time_stamp(comp_id IN VARCHAR2) RETURN VARCHAR2;

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

      

      TIMESTAMP

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

      COMP_TIMESTAMP STATS_BGN 2020-12-18 02:58:14

      DBUA_TIMESTAMP STATS_BGN FINISHED 2020-12-18 02:58:14 Container=CDB$ROOT 
      Id=1

      DBUA_TIMESTAMP STATS_BGN NONE 2020-12-18 02:58:14

TIME_STAMP_COMP_DISPLAY

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;

      

      
      DBMS_REGISTRY_SYS.TIME_STAMP_COMP_DISPLAY('CATALOG')

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

      COMP_TIMESTAMP CATALOG STARTED 2020-12-18 02:59:59 Container=CDB$ROOT Id=1

TIME_STAMP_DISPLAY

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;

      

      DBMS_REGISTRY_SYS.TIME_STAMP_DISPLAY('CATALOG')

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

      DBUA_TIMESTAMP CATALOG STARTED 2020-12-18 03:01:11 Container=CDB$ROOT Id=1

UPDATE_PROPS_DATA

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

See INSERT_PROPS_DATA Demo Above

UTLMMIG_SCRIPT_NAME

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;

SELECT dbms_registry_sys.utlmmig_script_name

      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;

      

      PL/SQL procedure successfully completed.

VALIDATE_CATJAVA

Undocumented dbms_registry_sys.validate_catjava;

exec dbms_registry_sys.validate_catjava;

      

      PL/SQL procedure successfully completed.

VALIDATE_CATPROC

Undocumented dbms_registry_sys.validate_catproc;

exec dbms_registry_sys.validate_catproc;

      

      PL/SQL procedure successfully completed.

VALIDATE_COMPONENTS

Undocumented dbms_registry_sys.validate_components;

exec dbms_registry_sys.validate_components;

      

      PL/SQL procedure successfully completed.