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