How to generate a data model from data dictionary tables in Oracle?


You wanted to generate a data model from data dictionary tables in Oracle


The Oracle data dictionary is a collection of tables and related views that enable us to view the structure of the Oracle database. By querying these tables and views, we can obtain information about every object and every user of the database.


The data dictionary is packaged with a series of views owned by the SYS user. These views, known as static data dictionary views, present information contained in tables that are updated when Oracle processes a Data Definition Language (DDL) statement.

There is a second set of views known as dynamic views or dynamic performance views, and commonly referred to as V$ views.

These V$ views are based on a set of internal memory structures maintained by Oracle as virtual tables (begin with an “X$” prefix).

In conclusion, Just as the static data dictionary views provide information about the database, the V$ views provide information about the active instance.

Describe Data Dictionary Views

There are quite a number of data dictionary views available in Oracle and remembering them is quite difficult, so Oracle had created data dictionary views to document the details of data dictionary views.


View DICTIONARY outputs available data dictionary views and their purpose.


SELECT  table_name        ,comments   FROM dictionary  WHERE table_name = 'ALL_TAB_COLS';


   table_name   |  comments                                         --------------------------------------------------------------------   ALL_TAB_COLS  | Columns of user's tables, views and clusters    


View DICT_COLUMNS describes the columns of a data dictionary view and their purpose.


SELECT column_name       ,comments   FROM dict_columns  WHERE table_name = 'ALL_TAB_COLS';


column_name comments COLLATION   Collation name COLLATED_COLUMN_ID  Reference to the actual collated column’s internal sequence number TABLE_NAME  Table, view or cluster name COLUMN_NAME Column name DATA_TYPE   Datatype of the column DATA_TYPE_MOD   Datatype modifier of the column DATA_TYPE_OWNER Owner of the datatype of the column DATA_LENGTH Length of the column in bytes DATA_PRECISION  Length: decimal digits (NUMBER) or binary digits (FLOAT) DATA_SCALE  Digits to right of decimal point in a number NULLABLE    Does column allow NULL values? COLUMN_ID   Sequence number of the column as created DEFAULT_LENGTH  Length of default value for the column DATA_DEFAULT    Default value for the column NUM_DISTINCT    The number of distinct values in the column LOW_VALUE   The low value in the column HIGH_VALUE  The high value in the column DENSITY The density of the column NUM_NULLS   The number of nulls in the column NUM_BUCKETS The number of buckets in histogram for the column LAST_ANALYZED   The date of the most recent time this column was analyzed SAMPLE_SIZE The sample size used in analyzing this column CHARACTER_SET_NAME  Character set name CHAR_COL_DECL_LENGTH    Declaration length of character type column GLOBAL_STATS    Are the statistics calculated without merging underlying partitions? USER_STATS  Were the statistics entered directly by the user? AVG_COL_LEN The average length of the column in bytes CHAR_LENGTH The maximum length of the column in characters CHAR_USED   C if maximum length is specified in characters, B if in bytes V80_FMT_IMAGE   Is column data in 8.0 image format? DATA_UPGRADED   Has column data been upgraded to the latest type version format? HIDDEN_COLUMN   Is this a hidden column? VIRTUAL_COLUMN  Is this a virtual column? SEGMENT_COLUMN_ID   Sequence number of the column in the segment INTERNAL_COLUMN_ID  Internal sequence number of the column HISTOGRAM   QUALIFIED_COL_NAME  Qualified column name USER_GENERATED  Is this an user-generated column? DEFAULT_ON_NULL Is this a default on null column? IDENTITY_COLUMN Is this an identity column? EVALUATION_EDITION  Name of the evaluation edition assigned to the column expression UNUSABLE_BEFORE Name of the oldest edition in which the column is usable UNUSABLE_BEGINNING  Name of the oldest edition in which the column becomes perpetually unusable OWNER   NA

Listing Owners/Schemas in a Schema

SYS.ALL_TABLES describes the relational tables accessible to the current user. The column owner holds the schema names that can be accessed by the user.

SELECT DISTINCT owner   FROM all_tables ;

Listing Tables in a Schema

The column table_name in SYS.ALL_TABLES holds the table names accessible by the user

SELECT table_name   FROM all_tables WHERE owner = 'myowner'

Listing Table’s Columns

ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user.

 SELECT column_name        ,data_type        ,data_length        ,data_precision        ,nullable        ,column_id   FROM all_tab_columns  WHERE owner = 'myowner'    AND table_name = 'mytable'  ORDER BY column_id;

Listing Indexed Columns

SYS.ALL_IND_COLUMNS Describes the columns of indexes on all tables accessible to the current user.


SELECT table_name,        index_name,        column_name,        column_position   FROM all_ind_columns  WHERE table_name  = 'mytable'    AND table_owner = 'myowner'

Listing Constraints

ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user

ALL_CONS_COLUMNS describes columns that are accessible to the current user and that are specified in constraints.


SELECT ac.table_name,         ac.constraint_name,          acc.column_name,          ac.constraint_type       FROM all_constraints ac,        all_cons_columns acc      WHERE ac.table_name      = 'mytable'        AND ac.owner           = 'myowner'        AND ac.table_name      = acc.table_name        AND ac.owner           = acc.owner        AND ac.constraint_name = acc.constraint_name;

Listing Foreign Keys Without Corresponding Indexes

Use the following SQL for identifying foreign keys without indexes.


SELECT acc.table_name,          acc.constraint_name,          acc.column_name,          aic.index_name     FROM all_cons_columns acc,          all_constraints  ac,          all_ind_columns  aic    WHERE acc.table_name      = 'mytable'      AND acc.owner           = 'myowner'      AND ac.constraint_type  = 'R'      AND acc.owner           = ac.owner      AND acc.table_name      = ac.table_name      AND acc.constraint_name = ac.constraint_name      AND acc.owner           = aic.table_owner (+)      AND acc.table_name      = aic.table_name (+)      AND acc.column_name     = aic.column_name (+)      AND aic.index_name IS NULL;

Data Model

I frequently use below query to understand the database details before starting any analysis or development. This SQL is prepared based on above learnings.


WITH temp AS  ( SELECT owner ,                       table_name                  FROM all_tables                 WHERE owner = 'myowner'                   AND table_name in ('mylist_of_tables'))    , cols AS (               SELECT                      atc.owner,                      atc.table_name,                      atc.column_name,                      atc.column_id,                      atc.data_type,                      atc.data_length,                      atc.data_precision,                      atc.data_scale,                      atc.nullable,                      at.num_rows                     ,(at.blocks * 8 * 1024) / 1024 / 1024 AS size_mb                     ,at.status                     ,at.last_analyzed                     ,at.partitioned                FROM                     all_tab_columns atc,                     all_tables at,                     temp               WHERE                     atc.owner = temp.owner                 AND atc.table_name = temp.table_name                 AND at.owner = temp.owner                 AND at.table_name = temp.table_name                )    ,tmp_constraints AS (               SELECT                      a.owner,                      a.table_name,                      b.column_name,                            a.constraint_name,                      a.constraint_type                 FROM                      all_constraints   a,                      all_cons_columns  b,                      temp                WHERE                      a.owner = b.owner                  AND a.table_name = b.table_name                  AND a.owner = temp.owner                  AND a.table_name = temp.table_name                        AND a.constraint_name = b.constraint_name                  AND a.constraint_type IN ('C', 'P', 'U', 'V', 'O')                )     , index_cols AS (              SELECT DISTINCT                    'YES' AS index_avail,                    a.table_name,                    a.column_name,                    a.table_owner as owner               FROM                    all_ind_columns a,                    temp              WHERE                   a.table_owner = temp.owner               AND a.table_name = temp.table_name               ),      db_ri AS (           SELECT DISTINCT                  'YES' AS db_ri_avail,                  a.table_name,                  a.owner            FROM                  all_constraints a,                  temp            WHERE                   constraint_type = 'R'                AND a.owner = temp.owner                AND a.table_name = temp.table_name                )   ,check_constraints AS       ( SELECT *           FROM tmp_constraints a           WHERE constraint_type = 'C')   ,primary_constraints AS         (SELECT *            FROM tmp_constraints a           WHERE constraint_type = 'P')   ,unique_constraints AS         (SELECT *            FROM tmp_constraints a           WHERE constraint_type = 'U')    ,with_ck_on_view AS          (SELECT *             FROM tmp_constraints a            WHERE constraint_type = 'V')    ,with_ro_on_view AS          (SELECT *             FROM tmp_constraints a            WHERE constraint_type = 'O')    ,s1 AS          (SELECT DISTINCT                  cols.owner,                  cols.table_name,                  cols.num_rows,                  cols.size_mb,                  cols.status,                  cols.last_analyzed,                  cols.partitioned,                  db_ri.db_ri_avail,                  cols.column_name,                  cols.column_id,                  cols.data_type,                  cols.data_length,                  cols.data_precision,                  cols.data_scale,                  cols.nullable,                  index_cols.index_avail,                  ck.constraint_name AS CHECK_CONSTRAINT_NAME,                  pk.constraint_name AS PK_CONSTRAINT_NAME,                  uk.constraint_name AS UK_CONSTRAINT_NAME,                  ckv.constraint_name AS VW_CONSTRAINT_NAME,                  rov.constraint_name AS RD_CONSTRAINT_NAME            FROM                 cols,                 check_constraints    ck,                 primary_constraints  pk,                 unique_constraints   uk,                 with_ck_on_view      ckv,                 with_ro_on_view      rov,                 index_cols,                 db_ri           WHERE                 cols.owner = ck.owner (+)             AND cols.table_name = ck.table_name (+)             AND cols.column_name = ck.column_name (+)             AND cols.owner = pk.owner (+)             AND cols.table_name = pk.table_name (+)             AND cols.column_name = pk.column_name (+)             AND cols.owner = uk.owner (+)             AND cols.table_name = uk.table_name (+)             AND cols.column_name = uk.column_name (+)             AND cols.owner = ckv.owner (+)             AND cols.table_name = ckv.table_name (+)             AND cols.column_name = ckv.column_name (+)             AND cols.owner = rov.owner (+)             AND cols.table_name = rov.table_name (+)             AND cols.column_name = rov.column_name (+)             AND cols.owner = index_cols.owner (+)             AND cols.table_name = index_cols.table_name (+)             AND cols.column_name = index_cols.column_name (+)             AND cols.owner = db_ri.owner (+)             AND cols.table_name = db_ri.table_name (+)                )       SELECT             ROWNUM        AS REC_ID,            'DATA_SCAN'    AS ASSET_CODE,            (SELECT Banner FROM v$version WHERE banner LIKE 'Oracle%')  AS database_version,             s1.owner,             s1.table_name,             s1.num_rows,             s1.size_mb,             s1.status,             s1.last_analyzed,             s1.partitioned,             s1.db_ri_avail,             s1.column_name,             s1.column_id,             s1.data_type,             s1.data_length,             s1.data_precision,             s1.data_scale,             s1.nullable,             s1.index_avail,             s1.CHECK_CONSTRAINT_NAME,             s1.PK_CONSTRAINT_NAME,             s1.UK_CONSTRAINT_NAME,             s1.VW_CONSTRAINT_NAME,             s1.RD_CONSTRAINT_NAME,             (SELECT 'Yes'                FROM all_part_key_columns apkc               WHERE s1.owner = apkc.owner and s1.table_name = and s1.column_name = apkc.column_name) partition_column,                    (SELECT 'PROD,,PARALLEL'                     FROM   all_part_key_columns apkc                     WHERE  s1.owner = apkc.owner and s1.table_name = and s1.column_name = apkc.column_name) partition_unit                FROM s1;

Updated on: 05-Dec-2020


Kickstart Your Career

Get certified by completing the course

Get Started