Oracle Database - Data Dictionary

Card Puncher Data Processing


A data dictionary is a repository of metadata. The data dictionary of Oracle is stored in the SYS schema.

Each Oracle database has a data dictionary, which is a set of tables and views that serve as a reference about the database.

For example, a data dictionary stores information about both the logical and physical structure of the database.

A data dictionary also stores the valid users of an Oracle database, information about integrity constraints defined for tables in the database, and the amount of space allocated for a schema object and how much of that space is in use, among much other information.

A data dictionary is created when a database is created. To accurately reflect the status of the database at all times, the data dictionary is automatically updated by Oracle Database in response to specific actions, such as when the structure of the database is altered. Database users cannot modify the data dictionary. Various database processes rely on the data dictionary to record, verify, and conduct ongoing work. For example, during database operation, Oracle Database reads the data dictionary to verify that schema objects exist and that users have proper access to them.

Dynamic Performance Views

Initially, you can just check this two views:

  • vsesstat (work done)
  • and vsession_event (time spent not working)

Data Dictionary Table and views

USER_TABLES Lists each table that belongs to your Oracle user.
USER_TAB_COMMENTS Shows comments on the tables and views.
USER_TAB_COLUMNS Tells you the names, data types, default values, etc. of each column in each table.
USER_COL_COMMENTS Shows comments on the columns.
USER_CONSTRAINTS Gives you all constraints (either single- or multi-column), such as primary key, foreign key, not null, check constraints, etc.
USER_CONS_COLUMNS Maps constraints to columns (since a constraint can act on one or many columns).
USER_INDEXES Lists indexes defined on columns (either defined explicitly when creating the data model or defined automatically by Oracle, as is the case with indexes on primary keys).
USER_IND_COLUMNS Maps indexes to columns.
USER_VIEWS Lists all views, along with the text used to originally create them.
USER_SYNONYMS Lists the synonyms and original table names.
USER_SEQUENCES Lists all sequences, including min value, max value, and amount by which to increment.
USER_TRIGGERS Contains trigger names, criteria for activating each trigger, and the code that is run.
USER_SOURCE Contains the source code for all PL/SQL objects, including functions, procedures, packages, and package bodies.

Example Admin Table

  • all_objects
select *
from all_objects
where object_type in ('TABLE','VIEW')
  • obj$
select name from obj$ where status = 3;
  • vparameter
SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
  • user_segments (for table, index, …)
select segment_name,   bytes/1024/1024 "Size in MB"  from   user_segments where  segment_name like 'CUSTOMERS_%';
  • all_synonyms
select * from all_synonyms where Owner like 'PUBLIC' and SYNONYM_NAME = UPPER('user_sdo_styles')
  • dba_registry
SELECT comp_name, status, SUBSTR(version,1,10) AS version FROM dba_registry;


Discover More
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - DDL Locks (data dictionary lock)

A data dictionary (DDL) lock protects the definition of a schema object while an ongoing DDL operation (CREATE, ALTER, DROP) acts on or refers to the object. Only individual schema objects that are modified...
Card Puncher Data Processing
Oracle Database - Data Files

Every Oracle database has one or more physical datafiles (OS File), which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the...
Card Puncher Data Processing
Oracle Database - Index Statistics

Statistics on Index stored in the data dictionary : DBA_INDEXES DBA_IND_STATISTICS Default Index Values When Statistics Are Missing Index Statistic Default Value Used by Optimizer Levels ...
Card Puncher Data Processing
Oracle Database - Optimizer Statistics

Optimizer statistic in Oracle You can collect exact or estimated statistics physical storage characteristics and data distribution in these schema objects by using the DBMS_STATS...
Card Puncher Data Processing
Oracle Database - SYSTEM Tablespace

The system tablespace is reserved for the data dictionary.
Oracle Database Sga
Oracle Database - Shared Pool

The Shared pool is a shared memory area where cursors and PL/SQL objects are stored. The shared pool is a shared memory that store: Cached data dictionary information and Shared Cursors and PL/SQL...
Card Puncher Data Processing
Oracle Database - TableSpace

A tablespace is a logical data structure which is physically represented by one or more datafiles physically. From a analysis point of view, a tablespace is a group of one or more: physic datafile....
Card Puncher Data Processing
PL/SQL - Cursor

A cursor in the context of Oracle. A cursor is a SQL datatype. A cursor is a pointer to a private SQL area that stores information processing a specific: SELECT or DML statement. The cursor data...

Share this page:
Follow us:
Task Runner