| 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
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
WHERE owner = 'SYS'
AND object_type = 'VIEW'
AND object_name LIKE '%$'
ORDER BY 1; |
| DBA Dictionary Views |
SELECT view_name
FROM dba_views
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
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
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
WHERE owner = 'SYS'
AND view_name LIKE 'DBA%'
AND SUBSTR(view_name, 5) NOT IN (
SELECT SUBSTR(view_name, 5)
FROM dba_views
WHERE owner = 'SYS'
AND view_name LIKE 'ALL%'
UNION
SELECT SUBSTR(view_name, 6)
FROM dba_views
WHERE owner = 'SYS'
AND view_name LIKE 'USER%'); |
ALL views not available for USER |
SELECT view_name
FROM dba_views
WHERE owner = 'SYS'
AND view_name LIKE 'ALL%'
AND SUBSTR(view_name, 5) NOT IN (
SELECT SUBSTR(view_name, 6)
FROM dba_views
WHERE owner = 'SYS'
AND view_name LIKE 'USER%'); |
USER Views not available as ALL |
SELECT view_name
FROM dba_views
WHERE owner = 'SYS'
AND view_name LIKE 'USER%'
AND SUBSTR(view_name, 6) NOT IN (
SELECT SUBSTR(view_name, 5)
FROM dba_views
WHERE owner = 'SYS'
AND view_name LIKE 'ALL%'); |
| |
| 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
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 |
conn / as sysdba
SELECT name
FROM gv
WHERE type = 'TABLE'
ORDER BY 1;
desc x
SELECT COUNT(*)
FROM x;
SELECT view_name
FROM dba_views
WHERE view_name = 'X';
SELECT object_type
FROM dba_objects
WHERE object_name = 'X'; |
X$ Fixed Tables |
| X – Kernel Cache
(96) |
| x |
kernel cache, block file
wait |
| x |
kernel cache, block wait |
| x |
kernel cache, controlfile
checkpoint progress |
| x |
kernel cache, file I/O |
| x |
kernel cache, lock file
header |
| x |
kernel cache, lock file
index |
| x |
kernel cache, lock undo
header |
| x |
kernel cache, lock undo
index |
| X – Kernel Generic
(41) |
| x |
kernel generic, heap LRUs |
| x |
kernel generic, library
cache lock |
| x |
kernel generic, library
cache object |
| x |
kernel generic, library
cache pin |
| x |
kernel generic, library
cache status |
| X – Kernel Query
(18) |
| x |
kernel query, fixed table
columns |
| x |
kernel query, fixed table |
| x |
kernel query, fixed
procedure |
| x |
kernel query, fixed size |
| x |
kernel query, fixed table |
| x |
kernel query, fixed view |
| x |
kernel query, fixed view
table |
| X – Kernel Security
(8) |
| x |
kernel security, system
role |
| X – Kernel Services
(143) |
| x |
kernel services, memory
fixed SGA |
| x |
kernel services, memory
fixed SGA vectors |
| x |
kernel services, memory
java_pool summary |
| x |
kernel services, memory
LRU |
| x |
kernel services, memory
large_pool summary |
| x |
kernel services, memory |
| x |
kernel services, memory
process pool |
| x |
kernel services, memory
SGA definition |
| x |
kernel services, memory
shared pool |
| x |
kernel services, memory
shared pool reserved |
| x |
kernel services, memory
shared_pool summary |
| x |
kernel services, memory
user pool |
| x |
kernel services, enqueue
status |
| x |
kernel services, user long
operation |
| x |
kernel services, user
locale value |
| x |
kernel services, user
process |
| X – Lock Element (1) |
| x |
lock element |
|
| |
| Dynamic
Performance Views on Memory Structures (Magic Views) |
GV$ and V$ |
conn / as sysdba
SELECT object_name
FROM dba_objects
WHERE object_name LIKE '%V_$%'
AND object_type = 'VIEW'
ORDER BY 1; |
Free