How do I list all context namespaces in Oracle DB?

Question

If I create a context namespace:-

CREATE OR REPLACE CONTEXT hr_security
    USING hr.pkg_security
    ACCESSED GLOBALLY;

How can I list all such namespaces and look up their properties. For example, hr_security can be access globally and can be set from pkg_security package.

Answer 1

You can query the DBA_CONTEXT (or [ALL_CONTEXT][1]) view depending on your privileges and what contexts you're looking at. ALL_CONTEXT will list all the contexts that have attributes set in the current session. DBA_CONTEXT lists all the contexts in the database. However, you need to have additional privileges to be able to query the DBA_CONTEXT view (the SELECT ANY DICTIONARY privilege or SELECT_CATALOG_ROLE role would be more than sufficient but you can also be granted access to that view specifically)

SELECT namespace,
       schema,
       package,
       type
  FROM dba_context

will have a row for the HR_SECURITY namespace showing that it is associated with the PKG_SECURITY package with a TYPE of ACCESSED GLOBALLY.

Answer 2

Alternatively you can use ALL_OBJECTS if you lack grants for DBA_CONTEXT. But to list all global contexts which were created by respective user would require additional naming convention to distinguish who created this global context (as in your example owner of object will be SYS and not HR)

SELECT * FROM ALL_OBJECTS AO WHERE AO.OBJECT_TYPE = 'CONTEXT' AND AO.OBJECT_NAME LIKE 'HR%' AND AO.OWNER = 'SYS';