sonumb

Data Dictionary in Oracle 본문

개발자 이야기/DBMS_일반

Data Dictionary in Oracle

sonumb 2020. 1. 2. 14:17

출처: http://psoug.org/reference/data_dict.html

 

Oracle Data Dictionary Catalog DBA ALL USER V$ GV$

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 an

psoug.org

 

Object categories Desc.
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# (+)

or 

 

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$fixed_table
WHERE type = 'TABLE'
ORDER BY 1;

desc x$ksppi

SELECT COUNT(*)
FROM x$ksppi;

SELECT view_name
FROM dba_views
WHERE view_name = 'X$KSPPI';

SELECT object_type
FROM dba_objects
WHERE object_name = 'X$KSPPI';
X$KC - Kernel Cache (96)
x$kcbfwait kernel cache, block file wait
x$kcbwait kernel cache, block wait
x$kcccp kernel cache, controlfile checkpoint progress
x$kcfio kernel cache, file I/O
x$kclfh kernel cache, lock file header
x$kclfi kernel cache, lock file index
x$kcluh kernel cache, lock undo header
x$kclui kernel cache, lock undo index
X$KG - Kernel Generic (41)
x$kghlu kernel generic, heap LRUs
x$kgllk kernel generic, library cache lock
x$kglob kernel generic, library cache object
x$kglpn kernel generic, library cache pin
x$kglst kernel generic, library cache status
X$KQ - Kernel Query (18)
x$kqfco kernel query, fixed table columns
x$kqfdt kernel query, fixed table
x$kqfp kernel query, fixed procedure
x$kqfsz kernel query, fixed size
x$kqfta kernel query, fixed table
x$kqfvi kernel query, fixed view
x$kqfvt kernel query, fixed view table
X$KZ - Kernel Security (8)
x$kzsro kernel security, system role
X$KS - Kernel Services (143)
x$ksmfs kernel services, memory fixed SGA
x$ksmfsv kernel services, memory fixed SGA vectors
x$ksmjs kernel services, memory java_pool summary
x$ksmlru kernel services, memory LRU
x$ksmls kernel services, memory large_pool summary
x$ksmmem kernel services, memory
x$ksmpp kernel services, memory process pool
x$ksmsd kernel services, memory SGA definition
x$ksmsp kernel services, memory shared pool
x$ksmspr kernel services, memory shared pool reserved
x$ksmss kernel services, memory shared_pool summary
x$ksmup kernel services, memory user pool
x$ksqst kernel services, enqueue status
x$ksulop kernel services, user long operation
x$ksulv kernel services, user locale value
x$ksupr kernel services, user process
X$LE - Lock Element (1)
x$le 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;
반응형
저작자표시 비영리 변경금지
'개발자 이야기/DBMS_일반' Related Articles more