Oracle Database - Data Dictionary

> Database > Oracle Database

1 - About

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.

Advertising

3 - Dynamic Performance Views

Initially, you can just check this two views:

  • v$sesstat (work done)
  • and v$session_event (time spent not working)

4 - 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.
Advertising

5 - Example Admin Table

  • all_objects
SELECT *
FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
  • obj$
SELECT name FROM obj$ WHERE STATUS = 3;
  • v$parameter
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;

6 - Reference

Advertising