Overview of Data Dictionary

Let us get an overview of Data Dictionary which can be used to understand the internals of what is going on when ever we execute DDL Statements.

  • Data Dictionary is nothing but system views/tables which will give us the metadata of the objects such as tables, indexes etc that are created.
  • In Oracle, the Data Dictionary is exposed as views.
  • We have views which begin with user_ which will give the details about Data Dictionary.
    • user_objects - General view about all important Database objects such as Tables, Indexes, Sequences etc.
    • user_tables - View about Table level information. It will return one entry for each table we create in the database.
    • user_indexes - View about Index level information. There can be multiple indexes for a given table.
    • user_seqeunces - View about Sequence level information. There will be one entry for each sequence that is created in the Database.
    • user_tab_columns - View about columns in a Table or View. There will be one entry for each column in a Table or View.
    • user_constraints - View about constraints defined on a Table. There will be one entry for each constraint that is defined on a given Table.
    • and more

For now we will use user_objects - SELECT * FROM user_objects;

Here are some of the important queries against Data Dictionary Views about the metadata that is being updated when ever we perform DDL operations to create or alter the tables.

SELECT object_name, object_type, status, timestamp
FROM user_objects
ORDER BY timestamp DESC;

SELECT *
FROM user_tables
WHERE table_name = 'REGIONS';

SELECT table_name, index_name, index_type
FROM user_indexes
WHERE table_name = 'REGIONS';

SELECT *
FROM user_sequences
WHERE sequence_name = 'REGIONS_S';

SELECT table_name, column_name, data_type,
  data_length, data_scale, data_precision
FROM user_tab_columns
WHERE table_name = 'REGIONS';

SELECT table_name, constraint_name, 
  constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'REGIONS';