Oracle Data Dictionary

on
Oracle Data Dictionary


General Information
Library Note
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.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.
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 TablesSELECT /*+ FIRST_ROWS(10) */ object_name
FROM dba_objects_ae
WHERE owner = 'SYS'
AND object_type = 'TABLE'
AND object_name LIKE '%$'
ORDER BY 1;
Catalog ViewsSELECT /*+ 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 ViewsSELECT 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 grantedSELECT 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 schemaSELECT 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 USERSELECT 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 USERSELECT 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 ALLSELECT 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 importancecol property_value format a28
col description format a60

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

SELECT * FROM cat;
TAB viewSELECT 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 viewconn / as sysdba

desc tab

SELECT * FROM tab$;

conn uwclass/uwclass

SELECT * FROM tab;
COL viewconn / as sysdba

desc col

set pagesize 0

SELECT text
FROM dba_views_ae
WHERE view_name = 'COL';

conn uwclass/uwclass

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 TablesFollow 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;

Comments

Post a Comment

Oracle DBA Information