Oracle DBMS_REGISTRY_SYS
Version 19.2.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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
 
CAPITALIZE_SINGLE_QUOTED (new 19c)
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;
 
CATCON_QUERY (new 19c)
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;

SELECT dbms_registry_sys.catcon_query('ORDIM')
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('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
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;
 
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);
 
 
INSERT_PROPS_DATA
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
 
POPULATE
Undocumented dbms_registry_sys.populate;
exec dbms_registry_sys.populate;
 
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
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
 
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
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
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
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
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;

Related Topics
DBMS_REGISTRY
DBMS_REGISTRY_EXTENDED
DBMS_REGISTRY_SERVER
DBMS_REGISTRY_SIMPLE
Packages
What's New In 18cR3
What's New In 19cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved