Oracle 12c DBMS_REGISTRY

Oracle DBMS_REGISTRY_SYS Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,

Purpose

Internal functions used by SYS during upgrade/downgrade

AUTHID

DEFINER

Dependencies

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

Documented

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;

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