D1-04數(shù)據(jù)字典入門(80頁)

1、Oracle 9i/10g/11g常用數(shù)據(jù)字典入門趙元杰2012.82內(nèi)容提要內(nèi)容提要v數(shù)據(jù)字典構(gòu)成v數(shù)據(jù)字典初步v動態(tài)視圖初步v開發(fā)有關(guān)數(shù)據(jù)字典vDBA與性能視圖3數(shù)據(jù)字典構(gòu)成數(shù)據(jù)字典構(gòu)成v概述：vOracle DBMS的重要信息 v管理所有對象、數(shù)據(jù)庫的邏輯、物理結(jié)構(gòu)的信息(元數(shù)據(jù)-metadata) v對DBA來說是最重要的工具, 對用戶和開發(fā)人員也很重要 v基本數(shù)據(jù)字典創(chuàng)建：vCATALOG.SQL、CATPROC.SQL 運(yùn)行PL/SQL創(chuàng)建基本數(shù)據(jù)字典腳本 ；v要以SYSDBA登錄運(yùn)行；v參考資料Oracle database reference中的：“Creating the D

2、ata Dictionary ”4數(shù)據(jù)字典構(gòu)成數(shù)據(jù)字典構(gòu)成v附加數(shù)據(jù)字典創(chuàng)建：v創(chuàng)建附加的數(shù)據(jù)字典：CATBLOCK.SQL - 建立動態(tài)顯示視圖 CATEXP7.SQL - 導(dǎo)出數(shù)據(jù)到Oracle7 . . .v創(chuàng)建附加的數(shù)據(jù)字典資料參考：vOracle database reference中的：“Creating Additional Data Dictionary Structures”v創(chuàng)建附加數(shù)據(jù)字典要以Sysdba登錄；v腳本路徑與名字/rdbms/admin5數(shù)據(jù)字典構(gòu)成數(shù)據(jù)字典構(gòu)成 v數(shù)據(jù)也稱靜態(tài)視圖=Static Viewsv數(shù)據(jù)字典包含以下兩個部分： v基表 l存儲數(shù)據(jù)庫的

3、說明l使用 CREATE DATABASE 命令創(chuàng)建 v數(shù)據(jù)字典視圖 l用于簡化基表信息l通過公共同義詞訪問l使用 catalog.sql 腳本創(chuàng)建 6數(shù)據(jù)字典內(nèi)容數(shù)據(jù)字典內(nèi)容 v數(shù)據(jù)字典提供有關(guān)以下方面的信息： v邏輯數(shù)據(jù)庫結(jié)構(gòu)和物理數(shù)據(jù)庫結(jié)構(gòu)v對象的定義和空間分配v完整性約束v用戶v角色v權(quán)限v審計 7主要的數(shù)據(jù)字典主要的數(shù)據(jù)字典SELECT * FROM dictionary;TablesIndexesViewsUsersSchemasProcedures等等等等8數(shù)據(jù)字典視圖數(shù)據(jù)字典視圖-dictvDICTIONARY 數(shù)據(jù)字典： vDICTIONARY是存放數(shù)據(jù)字典的表vDICT是D

4、ICTIONARY的同義詞SQL desc DICTIONARY 名稱 是否為空? 類型 - - - TABLE_NAME VARCHAR2(30) COMMENTS VARCHAR2(4000)SQL desc dict 名稱 是否為空? 類型 - - - TABLE_NAME VARCHAR2(30) COMMENTS VARCHAR2(4000)9數(shù)據(jù)字典視圖數(shù)據(jù)字典視圖-dictvDICTIONARY 數(shù)據(jù)字典： v查詢DICTI SQLselect * from dict where table_name like USER_% 2* Order by Table_nameSQL /

5、TABLE_NAME COMMENTS- -USER_ADDM_FDG_BREAKDOWN.USER_ALL_TABLES Description of all object and relational tables owned by the usersUSER_AQ_AGENT_PRIVSUSER_ARGUMENTS Arguments in object accessible to the userUSER_ASSEMBLIES Description of the users own assembliesUSER_ASSOCIATIONS All assocations defined

6、 by the user.10內(nèi)容提要內(nèi)容提要v數(shù)據(jù)字典構(gòu)成v數(shù)據(jù)字典初步v動態(tài)視圖初步v開發(fā)有關(guān)數(shù)據(jù)字典vDBA與性能視圖11數(shù)據(jù)字典所屬用戶數(shù)據(jù)字典所屬用戶 vOracle標(biāo)準(zhǔn)用戶：vSYS: 數(shù)據(jù)字典與視圖的所有者 (最高權(quán)限)vSYSTEM: 使用不同工具的表vPUBLIC: 虛構(gòu)(“dummy”)用戶; 擁有所有用戶有的權(quán)限 v一般用戶與數(shù)據(jù)字典 ：v8i及以前版本：07_DICTIONARY_ACCESSIBLITY=TRUE ，普通用戶也能訪問SYS下的數(shù)據(jù)字典v9i 與10g : 07_DICTIONARY_ACCESSIBLITY=FALSE ，普通用戶不能訪問SYS下的數(shù)據(jù)

7、字典v你可設(shè)置初始化07_DICTIONARY_ACCESSIBLITY=TRUE 12數(shù)據(jù)字典數(shù)據(jù)字典命名命名v數(shù)據(jù)字典命名：vDBA_ 具有DBA角色可訪問,包含所有用戶的對象；vUSER_ 一般用戶可訪問,包含當(dāng)前用戶的對象；vALL_ 包含當(dāng)前用戶對象與其它用戶授予訪問的對象；v數(shù)據(jù)字典特點(diǎn)：vDBA_ /USER_ 內(nèi)容以大寫存儲；vDBA_xxx/USER_xxx 以英語復(fù)數(shù)命名13數(shù)據(jù)字典數(shù)據(jù)字典內(nèi)容的包含關(guān)系內(nèi)容的包含關(guān)系 v數(shù)據(jù)字典包含關(guān)系：vDBA_xxx包含USER_xxx；v三種靜態(tài)視圖集v按范圍分類為： lDBA：所有方案中的視圖 lALL：用戶可以訪問的視圖 lUS

8、ER：用戶方案中的視圖 USER_xxx 數(shù)據(jù)庫中的所有對象數(shù)據(jù)庫中的所有對象 ALL_xxx 當(dāng)前用戶可以訪問的對象當(dāng)前用戶可以訪問的對象 DBA_xxx 當(dāng)前用戶所擁有的對象當(dāng)前用戶所擁有的對象 14數(shù)據(jù)字典數(shù)據(jù)字典間依賴關(guān)系間依賴關(guān)系v數(shù)據(jù)字典之間存在依賴關(guān)系 vUSER_TABLES-存儲表的基本數(shù)據(jù)：vUSER_TAB_COLUMNS-存儲表的列的數(shù)據(jù)vUSER_CONSTRAINTS -存儲表的約束. . .vUSER_INDEXES-存儲索引的基本數(shù)據(jù)：vUSER_IND_COLUMNS-存儲索引列的數(shù)據(jù)15數(shù)據(jù)字典數(shù)據(jù)字典 同義詞同義詞v數(shù)據(jù)字典同義詞：v公共同義詞(publi

9、c synonyms)v簡化和方便查詢vUSER_TABLES(=TABS) v同義詞信息存儲在：vDBA_SYNONYMSv直接查詢這個數(shù)據(jù)字典1616/30數(shù)據(jù)字典數(shù)據(jù)字典 同義詞同義詞vOracle 系統(tǒng)數(shù)據(jù)字典與同義詞： v查詢數(shù)據(jù)字典與同義詞對應(yīng)關(guān)系：SQL desc dba_synonyms 名稱 是否為空? 類型 - - - OWNER NOT NULL VARCHAR2(30) SYNONYM_NAME NOT NULL VARCHAR2(30) TABLE_OWNER VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) DB_LINK

10、VARCHAR2(128)SQL col owner for a20SQL col synonym_name for a20SQL col table_owner for a20SQL col table_name for a26SQL select owner,synonym_name,table_owner,table_name from dba_synonyms 2 where table_name=USER_TABLES;OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME- - - - PUBLIC USER_TABLES SYS USER_TABLES

11、PUBLIC TABS SYS USER_TABLES1717/30數(shù)據(jù)字典數(shù)據(jù)字典 同義詞同義詞vOracle 系統(tǒng)數(shù)據(jù)字典與同義詞： v查詢數(shù)據(jù)字典與同義詞對應(yīng)關(guān)系：SQL select table_name,synonym_name from dba_synonyms 2* where Owner=PUBLIC order by owner,table_name;TABLE_NAME SYNONYM_NAME- -/1000323d_DelegateInvocationHa /1000323d_DelegateInvocationHa.TABLE_NAME SYNONYM_NAME- -

12、ABSPATH ABSPATHADM_RLMGR_PRIVILEGES ADM_RLMGR_PRIVILEGESALERT_TYPE ALERT_TYPE.USER_SYNONYMS SYNUSER_SYNONYMS USER_SYNONYMSUSER_SYS_PRIVS USER_SYS_PRIVSUSER_TABLES TABSUSER_TABLES USER_TABLESUSER_TABLESPACES USER_TABLESPACESUSER_TAB_COLS USER_TAB_COLSUSER_TAB_COLUMNS COLS.sun/util/calendar/ZoneInfoFi

13、le sun/util/calendar/ZoneInfoFile已選擇27699行。1818/30數(shù)據(jù)字典數(shù)據(jù)字典 同義詞同義詞vOracle 系統(tǒng)數(shù)據(jù)字典與同義詞： v數(shù)據(jù)字典與同義詞名稱對應(yīng)：SQLselect table_name,synonym_name from dba_synonyms 2 where Owner=PUBLIC AND SUBSTR(table_name,1,3) in (ALL,DBA,USE) 3* order by owner,table_name;TABLE_NAME SYNONYM_NAME- -ALL$AW_CUBE_AGG_LEVELS ALL_AW

14、_CUBE_AGG_LEVELS.DBA_SYS_PRIVS DBA_SYS_PRIVSDBA_TABLES DBA_TABLES.TABLE_NAME SYNONYM_NAME- -USER_SYNONYMS SYNUSER_SYNONYMS USER_SYNONYMSUSER_SYS_PRIVS USER_SYS_PRIVSUSER_TABLES TABSUSER_TABLES USER_TABLES.USER_XML_TABLES USER_XML_TABLESUSER_XML_TAB_COLS USER_XML_TAB_COLSUSER_XML_VIEWS USER_XML_VIEWS

15、USER_XML_VIEW_COLS USER_XML_VIEW_COLS已選擇1790行。1919/30數(shù)據(jù)字典數(shù)據(jù)字典 同義詞同義詞vOracle 系統(tǒng) 獲取同義詞創(chuàng)建腳本： v同義詞創(chuàng)建腳本獲取方法：SQL SELECT DBMS_METADATA.get_ddl (SYNONYM, t.synonym_name, t.owner) 2 from DBA_SYNONYMS t 3 where owner = PUBLIC AND SUBSTR(table_name,1,4) = USER 4* order by owner,table_nameSQL /DBMS_METADATA.GET

16、_DDL(SYNONYM,T.SYNONYM_NAME,T.OWNER)- CREATE OR REPLACE PUBLIC SYNONYM USER_ADDM_FDG_BREAKDOWN FOR SYS.USER_AD. CREATE OR REPLACE PUBLIC SYNONYM USER_XML_VIEW_COLS FOR SYS.USER_XML_VIE已選擇467行。20內(nèi)容提要內(nèi)容提要v數(shù)據(jù)字典構(gòu)成v數(shù)據(jù)字典初步v動態(tài)視圖初步v開發(fā)有關(guān)數(shù)據(jù)字典vDBA與性能視圖21Oracle 系統(tǒng)視圖vOracle 系統(tǒng)視圖：v動態(tài)性能視圖=Dynamic Performance Views

17、v記錄了數(shù)據(jù)庫運(yùn)行時信息和統(tǒng)計數(shù)據(jù)v多數(shù)動態(tài)性能視圖被實時更新以反映數(shù)據(jù)庫當(dāng)前狀態(tài)v態(tài)性能視圖 可實時反映Oracle數(shù)據(jù)庫的狀態(tài)，為診斷性能和性能調(diào)整提供依據(jù)。vGV$ 和 V$視圖：vGV$含義為Global V$(在RAC環(huán)境下使用)v每個V$視圖都有一個對應(yīng)的GV$視圖存在22Oracle 系統(tǒng)視圖vOracle 系統(tǒng) 動態(tài)性能視圖：vV$. Oracle系統(tǒng)的視圖Session dataWait eventsMemory allocationsRunning SQLUNDO usageOpen cursorsRedo log usageand so onOracle instance

18、23Oracle 系統(tǒng)視圖vOracle 系統(tǒng)視圖：vV$. Oracle系統(tǒng)的視圖；vX$. Oracle系統(tǒng)的動態(tài)視圖。v視圖特點(diǎn)：vV$xxx/X$xxx大小寫存儲vV$xxx/X$xxx以英語單數(shù)命名24Oracle 系統(tǒng)視圖vOracle 系統(tǒng)普通的動態(tài)性能視圖：vV$. Oracle系統(tǒng)的視圖25Oracle 系統(tǒng)視圖vOracle 系統(tǒng)普通的動態(tài)性能視圖：vV$. Oracle系統(tǒng)的視圖SQL SELECT sql_text, executions FROM v$sql WHERE cpu_time 200000;SQL SELECT * FROM v$session WHERE

19、 machine = EDRSR9P1 and logon_time SYSDATE - 1;SQL SELECT sid, ctime FROM v$lock WHERE block 0;abc26Oracle 系統(tǒng)視圖vOracle 系統(tǒng) 動態(tài)性能視圖：vOracle系統(tǒng) 版本與動態(tài)視圖個數(shù)27性能視圖 分類vHigh Hvailability & Recoverability：vBackup/Archives/RecoveryvLogminervFlashback DatabasevStanby Database & DataGuardvSpecitic Technolo

20、gies：vOracle StreamsvOLAPvReplication/Materialized views28性能視圖 分類vDatabase and Instance Performance：vCache/QueuesvOverall SystemvSession/Resource AllocationvCursors/SQL StatementsvLatches/LocksvMetricsvmultithreaded/shared Serversvrollback segments/system managed undo segmentsvparallel queryvI/O29開發(fā)

21、人員與數(shù)據(jù)字典開發(fā)人員與數(shù)據(jù)字典v查看表的存儲擴(kuò)展情況v查詢user_extents 可看到每次擴(kuò)展的大?。?SQL SELECT SEGMENT_TYPE,segment_name, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS 2 FROM DBA_EXTENTS 3* WHERE OWNER = SCOTT;SEGMENT_TYPE SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BYTES BLOCKS- - - - - -TABLE DEPT USERS 0 65536 8TABLE EMP USERS 0 1048

22、576 128TABLE EMP USERS 1 1048576 128TABLE SALGRADE USERS 0 65536 8TABLE AUDIT_EMP USERS 0 65536 8TABLE JOB_TST USERS 0 65536 8TABLE TEST USERS 0 65536 8INDEX PK_DEPT USERS 0 65536 8INDEX PK_EMP USERS 0 65536 8已選擇9行。30性能視圖 分類vDatabase and Instance Configurations：vDatabase /InstancevRedo LogsvSecurity

23、/PrivilegesvParametersvcontrol files31內(nèi)容提要內(nèi)容提要v數(shù)據(jù)字典構(gòu)成v數(shù)據(jù)字典分類v開發(fā)有關(guān)數(shù)據(jù)字典vDBA與性能視圖32開發(fā)人員與數(shù)據(jù)字典開發(fā)人員與數(shù)據(jù)字典v開發(fā)人員可查看USER_的數(shù)據(jù)字典v下表是基本的數(shù)據(jù)字典33USER_OBJECTS 與 ALL_OBJECTS vUSER_OBJECTS:v當(dāng)前用戶的對象信息v包括:lDate createdlDate of last modificationlStatus (valid or invalid)vALL_OBJECTS:v當(dāng)前用戶可訪問的對象的信息34USER_OBJECTS v查看 用戶對象信

24、息v建議 維護(hù)人員了解當(dāng)前用戶的對象類型： SQL conn scott/zhao_scott_123$已連接。已連接。SQL desc user_objects 名稱名稱 是否為空是否為空? 類型類型 - - OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TE

25、MPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30)35USER_OBJECTS v查看 用戶對象信息v建議 維護(hù)人員了解當(dāng)前用戶的對象類型： SQL col object_type for a20SQL col object_name for a26SQLSELECT object_name, object_type, created, status 2 FROM user_objects ORDER BY object_type ;

26、OBJECT_NAME OBJECT_TYPE CREATED STATUS- - - -EMP_FUN FUNCTION 17-3月月 -12 VALIDPK_EMP INDEX 30-3月月 -10 VALIDSYS_C0011170 INDEX 11-12月月-11 VALIDPK_INSURED_AUTOS INDEX 11-12月月-11 VALIDPK_DEPT INDEX 30-3月月 -10 VALIDBACKTRACE_DRAWBACK PACKAGE 27-3月月 -12 VALIDBACKTRACE_DRAWBACK PACKAGE BODY 27-3月月 -12 VAL

27、IDEMP_STAT PROCEDURE 17-3月月 -12 VALIDINSERT_JOB_TST PROCEDURE 29-3月月 -12 VALIDP PROCEDURE 27-3月月 -12 INVALIDLIST_EMP PROCEDURE 29-3月月 -12 VALID.已選擇已選擇23行。行。36USER_TABLES v查看 用戶表的基本信息v只存儲表的基本信息與屬性數(shù)據(jù)v表的列信息user_tab_columnsv表的注釋user_tab_comments： SQL desc user_tables 名稱名稱 是否為空是否為空? 類型類型 - - - TABLE_NAME

28、 NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER. NUM_ROWS

29、NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER.37USER_TABLES v查看 用戶表的基本信息v查詢表的基本信息： SQL col table_name for a20SQL col tablespace_name for a26SQL col INITIAL_EXTENT for 999,999,999SQL select tab

30、le_name,tablespace_name,INITIAL_EXTENT,PCT_FREE,PCT_USED 2 FROM USER_TABLES ;TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT PCT_FREE PCT_USED- - - - -DEPT USERS 65,536 10BONUS USERS 10SALGRADE USERS 65,536 10TAB1 USERS 2,097,152 10TAB2 USERS 2,097,152 10AUDIT_EMP USERS 65,536 10JOB_TST USERS 65,536 10TES

31、T USERS 65,536 10MY_TAB USERS 10SUM_EMP TEMP 10 40EMP USERS 2,097,152 10已選擇已選擇11行。行。38USER_TAB_COLUMNS 列的信息 v表的列的信息v查詢表的列詳細(xì)信息： SQL desc USER_TAB_COLUMNS 名稱名稱 是否為空是否為空? 類型類型 - - - TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA

32、_TYPE_OWNER VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT NUMBER LOW_VALUE RAW(32) HIGH_VALUE RAW(32) DENSITY NUMBER NUM_NULLS NUMBER NUM_BUCKETS NUMBER LAST_ANALYZED DATE SA

33、MPLE_SIZE NUMBER CHARACTER_SET_NAME VARCHAR2(44) CHAR_COL_DECL_LENGTH NUMBER GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) AVG_COL_LEN NUMBER CHAR_LENGTH NUMBER CHAR_USED VARCHAR2(1) V80_FMT_IMAGE VARCHAR2(3) DATA_UPGRADED VARCHAR2(3) HISTOGRAM VARCHAR2(15)39USER_TAB_COLUMNS 列的信息 v表的列的信息v查詢表的列詳細(xì)信息

34、： SQLSELECT column_name, data_type, data_length, 2 data_precision, data_scale, nullable 3 FROM user_tab_columns 4* WHERE table_name = EMPSQL /COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE NU- - - - - -EMPNO NUMBER 22 4 0 NENAME VARCHAR2 10 YJOB VARCHAR2 9 YMGR NUMBER 22 4 0 YHIREDATE D

35、ATE 7 YSAL NUMBER 22 7 2 YCOMM NUMBER 22 7 2 YDEPTNO NUMBER 22 2 0 Y已選擇已選擇8行。行。40USER_CONSTRAINTS表的約束信息 v表的約束的信息vUSER_CONSTRAINTS 當(dāng)前用戶表的約束信息： SQL set line 90SQL desc USER_CONSTRAINTS 名稱名稱 是否為空是否為空? 類型類型 - - - OWNER VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_

36、NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VAR

37、CHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14)41USER_CONSTRAINTS表的約束信息 v表的約束的信息vUSER_CONSTRAINTS約束的信息： SQL col constraint_name for a28SQL col search_condition for a30SQL col r_constraint_name for a28SQL set line 180SQLSELECT constraint_name, constraint_type, 2 search

38、_condition, r_constraint_name, 3 delete_rule, status 4 FROM user_constraints 5* WHERE table_name = EMPSQL /CONSTRAINT_NAME CO SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RULE STATUS- - - - - -PK_EMP P ENABLEDFK_DEPTNO R PK_DEPT NO ACTION ENABLED42USER_CONSTRAINTS表的約束信息 v表的約束的信息vUSER_CONS_COLUMNS 約束的列的

39、信息： SQL set line 90SQL desc USER_CONS_COLUMNS 名稱名稱 是否為空是否為空? 類型類型 - - - OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER43USER_CONSTRAINTS表的約束信息 v表的約束的信息vUSER_CONS_COLUMNS 約束的列的信息： SELECT constraint_name, co

40、lumn_nameFROM user_cons_columnsWHERE table_name = EMP;44約束與依賴關(guān)系依賴關(guān)系查詢v查看表的依賴關(guān)系v刪除某個表前要查詢該是否其它表存在關(guān)系： /* 找出模式下的某個表的子表找出模式下的某個表的子表 */SELECT table_name “child tables” FROM user_constraintsWHERE r_constraint_name IN(SELECT constraint_name FROM user_constraints WHERE table_name = UPPER(&parent_table)A

41、ND constraint_type =P ) ;45約束信息查詢信息查詢v從約束數(shù)據(jù)字典可查詢所有的信息：v創(chuàng)建表時說明了CHECK約束，如：vCONSTRAINTS abcv CHECK(age between 18 and 65 ),v可user_constraints查詢到CHECK約束的詳細(xì)：SQL set long 1000SQL select SEARCH_CONDITION from user_constraints;SEARCH_CONDITION-age between 18 and 6546查詢查詢 視圖信息 v用戶視圖的信息：v將查詢結(jié)構(gòu)變?yōu)閯?chuàng)建腳本 ：set line

42、size 150set pagesize 1000set arraysize 8set feedback offset heading offset long 5000col view_name for a20col text for a80select CREATE OR REPLACE VIEW |VIEW_NAME| AS ,TEXT FROM USER_VIEWS order BY VIEW_NAME; 47查詢查詢 序列信息v序列信息：v查詢 ：set linesize 150set pagesize 1000- 設(shè)置輸出環(huán)境變量設(shè)置輸出環(huán)境變量 set pagesize 0 set

43、 linesize 200 DEFINE seq_name = &1 - 運(yùn)行時給出實際的序列名運(yùn)行時給出實際的序列名 - 將結(jié)果輸出到將結(jié)果輸出到res_seq.sql文件中文件中 spool res_seq.sql - 產(chǎn)生改變序列的命令產(chǎn)生改變序列的命令 select alter sequence | sequence_name | cycle nocache minvalue 0 maxvalue | to_char(last_number) | ; from user_sequences where sequence_name = upper(&seq_name) ;

44、48查詢查詢 序列信息v序列確認(rèn) ：v查詢 USER_SEQUENCES 用戶序列的信息 v如果指定為 NOCACHE時LAST_NUMBER表示序列的最后數(shù)值：set linesize 150set pagesize 1000SELECTsequence_name, min_value, max_value, increment_by, last_numberFROMuser_sequences;49確認(rèn)序列SQL SQL select SEQUENCE_NAME,MAX_VALUE,LAST_NUMBER from DBA_SEQUENCES 2 where LAST_NUMBER+10=

45、 max_value;未選定行未選定行v 查詢序列例子：v查詢序列目前的最后值與最大值是否很接近：50索引信息v 索引信息：vUSER_INDEXES 關(guān)于索引的信息.vUSER_IND_COLUMNS 索引列的信息.SQL select table_name,index_name,UNIQUENESS,tablespace_name 2 from dba_indexes where owner=SCOTT order by table_name; TABLE_NAME INDEX_NAME UNIQUENESS TABLESPACE_NAME- - - -BB_PRODUCT SYS_IL0

46、000049354C00002$ UNIQUE USERSCLOB_TST SYS_IL0000049385C00003$ UNIQUE USERSCUSTOMERFUNDINFO IDX1 NONUNIQUE USERSDEPT PK_DEPT UNIQUE USERSEMP PK_EMP UNIQUE USERSEMPLOYEE SYS_C005088 UNIQUE USERS 6 rows selected. 當(dāng)當(dāng)UNIQUENESS列的值為列的值為UNIQUE時，表示該索引是唯一索引；時，表示該索引是唯一索引；當(dāng)當(dāng)UNIQUENESS列的值為列的值為NONUNIQUE時，表示該索引是非

47、唯一索引。時，表示該索引是非唯一索引。51索引信息v 索引信息：v1.查詢索引的基本信息：v2.查詢索引與表的信息：SELECT index_name, table_name,uniquenessFROM user_indexesWHERE table_name = EMPLOYEES; SELECT INDEX_NAME, TABLE_NAMEFROM USER_INDEXESWHERE TABLE_NAME = EMP_LIB;52索引信息v 索引信息：v1.索引列的數(shù)據(jù)字典結(jié)構(gòu)：v2.查詢索引的列的信息：DESCRIBE user_ind_columns SELECT INDEX_NAM

48、E, COLUMN_NAME,TABLE_NAMEFROM user_ind_columnsWHERE INDEX_NAME = LNAME_IDX;53索引信息-列的詳細(xì)v查看索引列詳細(xì)信息v查詢索引的列的信息，可在程序中使用： SQL COLUMN INDEX_NAME FORMAT A15SQL COLUMN TABLE_NAME FORMAT A15SQL SELECT c.COLUMN_NAME, COLUMN_POSITION, 2 i.INDEX_NAME, i.TABLE_NAME, i.TABLE_OWNER 3 FROM user_ind_columns c, user_i

49、ndexes i 4 where c.INDEX_NAME=i.INDEX_NAME;COLUMN_NAME COLUMN_POSITION INDEX_NAME TABLE_NAME TABLE_OWNE- - - - -A1 1 AI A ZYJB1 1 BI B ZYJB2 2 BI B ZYJC1 1 CI C ZYJC2 2 CI C ZYJ已選擇已選擇6行。行。SQL54同義詞(Synonym)信息v查同義詞同義詞信息 vuser_synonyms-9i 10g 11g 版本：版本：col table_name for a20col comments for a80set line

50、size 160set trimspool on-SELECT * FROM user_synonyms; 操作實踐：腳本v實踐覆蓋下面的標(biāo)題:v查詢表及其列的信息 v查詢約束信息v查詢視圖信息v查詢序列信息 v查詢同義詞的視圖的信息v查詢索引的視圖的信息v增加注釋到表與從數(shù)據(jù)字典查詢注釋內(nèi)容56樣例0：約束信息v查約束信息 vuser_constraints/user_cons_columns-9i 10g 11g 版本：版本：col table_name for a20col comments for a80set linesize 160set trimspool on-SELECT c

51、onstraint_name, column_nameFROM user_cons_columnsWHERE table_name = EMP; 57樣例0：必須認(rèn)識的DICTv查dict(用spool 將結(jié)果記錄到OS文件) v開發(fā)人員必須的技巧-9i 10g 11g 版本：版本：col table_name for a20col comments for a80set linesize 160set trimspool onSPOOl c:all_dictselect table_name,comments from dict where table_name like V%; TABLE

52、_NAME COMMENTS- -V$JAVA_LIBRARY_CACHE Synonym for V_$JAVA_LIBRARY_CACHE_MEMORY_MEMORYV$JAVA_POOL_ADVICE Synonym for V_$JAVA_POOL_ADVICE已選擇已選擇481行。行。spoo off58樣例1：查詢表基本信息 v查詢某個表的引用關(guān)系v用DROP刪除某個表前，要確認(rèn)該表是否存儲引用關(guān)系v查詢某個表的子表：/* 找出模式下的某個表的子表找出模式下的某個表的子表 */SELECT table_name “child tables” FROM user_constraint

53、sWHERE r_constraint_name IN(SELECT constraint_name FROM user_constraints WHERE table_name = UPPER(&parent_table)AND constraint_type =P ) ;59v查詢索引列有關(guān)的數(shù)據(jù)字典：vUSER_IND_COLUMNS -索引列的次序COLUMN_POSITION ；SQL select table_name,index_name,COLUMN_POSITION 2 from user_ind_columns 3* where table_name=EMP;TAB

54、LE_NAME INDEX_NAME COLUMN_POSITION- - -EMP PK_EMP 1樣例2：查詢索引信息60v索引信息查詢的必要性：v了解訪問表的索引列信息，在WHERE 條件句使用它SQL-查詢表所包含的索引名稱查詢表所包含的索引名稱:SQLselect index_name, table_name from USER_INDEXES WHERE table_name=xxxxx;SQL- 使用使用DBMS_METADATA.GET_DDL包獲得索引信息：包獲得索引信息：SQLSELECT ( INDEX,index_name) from DUAL;樣例3：查詢索引信息61

55、樣例4：查詢無效的視圖v查詢處于無效狀態(tài)的視圖 v開發(fā)人員經(jīng)常維護(hù)視圖工作v查詢無效的視圖，然后有針對性地進(jìn)行編譯：-9i 10g 11g 版本：版本：SQLSELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTSWHERE object_type=VIEW and STATUS=INVALID;-編譯無效視圖命令為：編譯無效視圖命令為：SQLALTER VIEW SCHEMA.view COMPILE;62樣例5：導(dǎo)出視圖創(chuàng)建腳本v從user_views中到處視圖的創(chuàng)建腳本 v開發(fā)人員從開發(fā)環(huán)境到應(yīng)用的實際環(huán)境：-8i 9i 1

56、0g 11g 版本：版本：SET LONG 9999SET TRIMSPOOL ONSET heading offCOLUMN text FORMAT A150 SET COLSEP SELECT -|view_name, 1 seq, chr(10)|CREATE OR REPLACE VIEW | lower ( view_name ) | AS , text , ; FROM user_views ORDER BY view_name, seq;-9i 10g 11g 版本：版本： set long 9999SELECT DBMS_METADATA.get_ddl(VIEW,EMP20)

57、 from dual;63樣例6：查詢序列信息v查詢序列例子v查詢序列目前的最后值與最大值是否很接近 ：-8i 9i 10g 11g 版本：版本：SQL SQL select SEQUENCE_NAME,MAX_VALUE,LAST_NUMBER from DBA_SEQUENCES 2 where LAST_NUMBER+10= max_value;未選定行未選定行DBA_SEQUENCESSEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NU

58、MBER 64樣例7：查詢同義詞SQL col synonym_name for a20SQL col table_name for a20SQL select owner,synonym_name,table_name,table_owner,db_link from dba_synonyms 2 where rownum select * from user_synonyms;SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK- - - -DEPTMENT SCOTT DEPTEMP1 SCOTT EMPEMP2 SCOTT EMPSQL select

59、table_name,count(*) from user_synonyms 2* having count(*)1 group by table_name ;TABLE_NAME COUNT(*)- -EMP 2v 查詢同義詞例子2：v列出一個對象有多個同義詞：66樣例9：同義詞維護(hù)技巧-批量創(chuàng)建同義詞的樣例：批量創(chuàng)建同義詞的樣例：SQL SET HEAD OFFSQL SET PAGESIZE 0SQL SET FEEDBACK OFFSQL SPOOL create_synonym.SQLSQL SELECT CREATE PUBLIC SYNONYM | object_name | F

60、OR | owner | . | object_name | ; FROM ALL_OBJECTS WHERE OWNER = SCOTT; v 自動產(chǎn)生創(chuàng)建同義詞腳本-例子3：v下面查詢語句可自動產(chǎn)生創(chuàng)建同義詞批命令：67內(nèi)容提要v數(shù)據(jù)字典構(gòu)成v數(shù)據(jù)字典分類v開發(fā)有關(guān)數(shù)據(jù)字典vDBA與性能視圖68故障診斷和調(diào)整故障診斷和調(diào)整Instance/DatabaseV$DATABASE TV$INSTANCE TV$OPTION TV$PARAMETER T/P V$BACKUP TV$PX_PROCESS_SYSSTAT T/PV$PROCESS TV$WAITSTAT T/PV$SYSTEM_EVENT T/PMemoryV$BUFFER