資料結構
網路
關係資料庫管理系統 (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+ 次瀏覽
