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

Problem:

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

Solution:

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.

Introduction

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.

DICTIONARY

View DICTIONARY outputs available data dictionary views and their purpose.

Example

<p>SELECT  table_name
       ,comments
  FROM dictionary
 WHERE table_name = 'ALL_TAB_COLS';</p>

Output

<p>   table_name   |  comments                                        
--------------------------------------------------------------------
  ALL_TAB_COLS  | Columns of user's tables, views and clusters    </p>

DICT_COLUMNS

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

Example

<p>SELECT column_name
      ,comments
  FROM dict_columns
 WHERE table_name = 'ALL_TAB_COLS';</p>

Output

<p>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</p>

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.

<p>SELECT DISTINCT owner
  FROM all_tables
;</p>

Listing Tables in a Schema

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

<p>SELECT table_name
  FROM all_tables
WHERE owner = 'myowner'</p>

Listing Table’s Columns

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

<p> 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;</p>

Listing Indexed Columns

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

Example

<p>SELECT table_name,
       index_name,
       column_name,
       column_position
  FROM all_ind_columns
 WHERE table_name  = 'mytable'
   AND table_owner = 'myowner'</p>

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.

Example

<p>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;</p>

Listing Foreign Keys Without Corresponding Indexes

Use the following SQL for identifying foreign keys without indexes.

Example

<p>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;</p>

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.

Example

<p>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 = apkc.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 = apkc.name and s1.column_name = apkc.column_name) partition_unit
               FROM s1;</p>