如何从Oracle中的数据字典表生成数据模型？
问题：
您想从Oracle中的数据字典表生成数据模型
解：
Oracle数据字典是表和相关视图的集合，使我们能够查看Oracle数据库的结构。通过查询这些表和视图，我们可以获得有关数据库的每个对象和每个用户的信息。
介绍
数据字典与SYS用户拥有的一系列视图打包在一起。这些视图称为静态数据字典视图，它们提供表中包含的信息，这些表在Oracle处理数据定义语言(DDL)语句时进行更新。
还有另一组视图，称为动态视图或动态性能视图，通常称为V$视图。
这些V$视图基于Oracle作为虚拟表维护的一组内部内存结构（以“X$”开头）。
总之，就像静态数据字典视图提供有关数据库的信息一样，V$视图提供有关活动实例的信息。
描述数据字典视图
Oracle中有很多可用的数据字典视图，并且记住它们非常困难，因此Oracle创建了数据字典视图以记录数据字典视图的详细信息。
字典
查看词典输出可用的数据字典视图及其用途。
示例
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
DICT_COLUMNS
视图DICT_COLUMNS描述了数据字典视图的列及其用途。
示例
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
列出架构中的所有者/架构
SYS.ALL_TABLES描述了当前用户可访问的关系表。列所有者保存用户可以访问的架构名称。
SELECT DISTINCT owner FROM all_tables ;
列出架构中的表
SYS.ALL_TABLES中的table_name列保存用户可访问的表名
SELECT table_name FROM all_tables WHERE owner = 'myowner'
列表表的列
ALL_TAB_COLUMNS描述了当前用户可访问的表，视图和群集的列。
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;列出索引列
SYS.ALL_IND_COLUMNS描述当前用户可访问的所有表上的索引列。
示例
SELECT table_name,
index_name,
column_name,
column_position
FROM all_ind_columns
WHERE table_name = 'mytable'
AND table_owner = 'myowner'上市限制
ALL_CONSTRAINTS在当前用户可访问的表上描述约束定义
ALL_CONS_COLUMNS描述了当前用户可访问且在约束中指定的列。
示例
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;列出没有对应索引的外键
使用以下SQL来识别没有索引的外键。
示例
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;资料模型
在开始任何分析或开发之前，我经常使用以下查询来了解数据库的详细信息。该SQL是根据上述经验编写的。
示例
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;