Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
SELECT table_name ,comments FROM dictionary WHERE table_name = 'ALL_TAB_COLS';
Output
table_name | comments -------------------------------------------------------------------- ALL_TAB_COLS | Columns of user's tables, views and clusters
DICT_COLUMNS
View DICT_COLUMNS describes the columns of a data dictionary view and their purpose.
Example
SELECT column_name ,comments FROM dict_columns WHERE table_name = 'ALL_TAB_COLS';
Output
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.
Example
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.
Example
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.
Example
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.
Example
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;
933 Views
