Oracle 12cR2 DBMS_REGISTRY

Oracle DBMS_REGISTRY_SYS Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.

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