| Dictionary Objects |
| 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 |
| DBA_ |
All objects in the 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# (+) |
conn / as sysdba
desc tab
SELECT * FROM tab;
conn uwclass/uwclass
SELECT * FROM tab; |
| COL view |
conn / 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 Tables |
Use the link at page bottom to see the page on these objects |
| |
| 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; |
