Oracle Data Dictionary
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Object Categories
X$ In memory structures (arrays)
V$ Views based on X$ structures
GV% Global views: Same as V$ except include instance identifier as the first column
CDB_ All objects in all databases within the CDB including CDB$ROOT and PDB$SEED
DBA_ All objects in the current database
ALL_ All objects owned by the user and on which the user has been granted privileges
USER_ All objects owned by the user
 
Examples
Catalog Tables SELECT /*+ FIRST_ROWS(10) */ object_name
FROM dba_objects_ae
WHERE owner = 'SYS'
AND object_type = 'TABLE'
AND object_name LIKE '%$'
ORDER BY 1;
Catalog Views SELECT /*+ FIRST_ROWS(10) */ object_name
FROM dba_objects_ae
WHERE owner = 'SYS'
AND object_type = 'VIEW'
AND object_name LIKE '%$'
ORDER BY 1;
DBA Dictionary Views SELECT view_name
FROM dba_views_ae
WHERE owner = 'SYS'
AND view_name LIKE 'DBA%'
ORDER BY 1;
Dictionary Views for schema owner and for objects where permissions have been granted SELECT view_name
FROM dba_views_ae
WHERE owner = 'SYS'
AND view_name LIKE 'ALL%'
ORDER BY 1;
Dictionary Views for objects owned by the current schema SELECT view_name
FROM dba_views_ae
WHERE owner = 'SYS'
AND view_name LIKE 'USER%'
ORDER BY 1;
Views available for DBA not available for ALL and USER SELECT view_name
FROM dba_views_ae
WHERE owner = 'SYS'
AND view_name LIKE 'DBA%'
AND SUBSTR(view_name, 5) NOT IN (
  SELECT SUBSTR(view_name, 5)
  FROM dba_views_ae
  WHERE owner = 'SYS'
  AND view_name LIKE 'ALL%'
  UNION
  SELECT SUBSTR(view_name, 6)
  FROM dba_views_ae
  WHERE owner = 'SYS'
  AND view_name LIKE 'USER%');
ALL views not available for USER SELECT view_name
FROM dba_views_ae
WHERE owner = 'SYS'
AND view_name LIKE 'ALL%'
AND SUBSTR(view_name, 5) NOT IN (
  SELECT SUBSTR(view_name, 6)
  FROM dba_views_ae
  WHERE owner = 'SYS'
  AND view_name LIKE 'USER%');
USER Views not available as ALL SELECT view_name
FROM dba_views_ae
WHERE owner = 'SYS'
AND view_name LIKE 'USER%'
AND SUBSTR(view_name, 6) NOT IN (
  SELECT SUBSTR(view_name, 5)
  FROM dba_views_ae
  WHERE owner = 'SYS'
  AND view_name LIKE 'ALL%');
Additional dictionary views of importance col property_value format a28
col description format a60

SELECT property_name, property_value, description
FROM database_properties
ORDER BY 1;
 
Other Queries
Using CAT view desc cat

SELECT * FROM cat;
TAB view SELECT o.name, DECODE(o.type#, 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM'), t.tab#
FROM sys.tab$ t, sys.obj$ o
WHERE o.owner# = userenv('SCHEMAID')
AND o.type# >=2
AND o.type# <=5
AND o.linkname is null
AND o.obj# = t.obj# (+)
Another TAB view conn sys@pdbdev as sysdba

desc tab

SELECT * FROM tab$;

conn uwclass/uwclass@pdbdev

SELECT * FROM tab;
COL view conn sys@pdbdev as sysdba

desc col

set pagesize 0

SELECT text
FROM dba_views_ae
WHERE view_name = 'COL';

conn uwclass/uwclass@pdbdev

set linesize 121
col coltype format a15
col tname format a20
col cname format a20
break on tname skip page

SELECT tname, colno, cname, coltype, width, scale, precision
FROM col
ORDER BY 1,2;
 
In Memory Structures
X$ Fixed Tables Follow the link at page bottom.
 
Dynamic Performance Views on Memory Structures (Magic Views)
GV$ and V$ conn / as sysdba

SELECT object_name
FROM dba_objects_ae
WHERE object_name LIKE '%V_$%'
AND object_type = 'VIEW'
ORDER BY 1;
 
Reverse Engineering
Identify parameter defaults from functions and procedures when you cannot access the source code conn / as sysdba

CREATE OR REPLACE PROCEDURE RevEng1(inval IN VARCHAR2 := 'MORGAN') AUTHID DEFINER IS
BEGIN
  NULL;
END RevEng;
/

Procedure created.

SELECT object_id
FROM dba_objects
WHERE object_name = 'REVENG1');

 OBJECT_ID
----------
    116504


SELECT piece
FROM idl_char$
WHERE obj# = 116504;

PIECE
-----------------------------------------------------------------------------
"DEFINER"REVENG1"00EBC7B84C5ABA1A06E19A15FA1631FB"INVAL"VARCHAR2"MORGAN"6""


CREATE OR REPLACE PROCEDURE RevEng2(val1 IN VARCHAR2 :=     'MORGAN',
                                    val2 IN DATE     DEFAULT SYSDATE)
AUTHID DEFINER IS
BEGIN
  NULL;
END RevEng2;
/

SELECT object_id
FROM dba_objects
WHERE object_name = 'REVENG2');

 OBJECT_ID
----------
    116505


SELECT piece
FROM idl_char$
WHERE obj# = 116505;

PIECE
-----------------------------------------------------------------------------
"DEFINER"REVENG2"A49A13F05342E8ECE435D571D745630A"VAL1"VARCHAR2"MORGAN"VAL2"DATE "SYSDATE"6"SYS"SYS_STUB_FOR_PURITY_ANALYSIS"PRDS"DE0D653FCD4CFAD500FF0FC43D33DE5
7"74E3E828BDE795AC1DD1B0C2F001DD9B""


Note the reference to sys.sys_stub_for_purity_analysis.prds and follow the link
below in the Related Topics section


Key
Object Name
Parameter Name
Data Type
Default Value

Related Topics
Dynamic Performance Views
GV$ and V$ Views
SYS_STUB_FOR_PURITY_ANALYSIS
X$ Arrays
What's New In 19c
What's New In 20c-21c