数据结构
网络
关系数据库管理系统 (RDBMS)
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C语言编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理学
化学
生物学
数学
英语
经济学
心理学
社会学
服装设计
法学如何在Oracle中从数据字典表生成数据模型？
问题
您想从Oracle中的数据字典表生成数据模型。
解决方案
Oracle数据字典是表和相关视图的集合，使我们能够查看Oracle数据库的结构。通过查询这些表和视图，我们可以获取有关数据库中每个对象和每个用户的的信息。
介绍
数据字典包含由SYS用户拥有的系列视图。这些视图，称为静态数据字典视图，显示包含在表中的信息，这些信息在Oracle处理数据定义语言(DDL)语句时会更新。
还有一组称为动态视图或动态性能视图的视图，通常称为V$视图。
这些V$视图基于Oracle维护的一组内部内存结构作为虚拟表（以“X$”为前缀）。
总之，正如静态数据字典视图提供有关数据库的信息一样，V$视图提供有关活动实例的信息。
描述数据字典视图
Oracle中有很多数据字典视图，记住它们非常困难，因此Oracle创建了数据字典视图来记录数据字典视图的详细信息。
DICTIONARY
视图DICTIONARY输出可用的数据字典视图及其用途。
示例
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描述当前用户可访问的关系表。owner列包含用户可以访问的模式名称。
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;
1K+ 次浏览
