" 1、靜態數據字典 " "1.1、實用靜態數據字典" "1.2、運用靜態數據字典" " 2、動態數據字典 " "2.1、實用動態性能視圖" "2.2、運用動態性能視圖" " 3、死鎖 " "3.1、定位死鎖" "3.2、解鎖方法" "3.3、強制刪除已連接用戶" " 4、總結 " 數據字典是 Or ...
數據字典是 Oracle 中存放資料庫信息的地方,用於描述數據。比如一個表的創建者信息、創建時間信息、所屬表空間信息、用戶訪問許可權信息等。數據字典由表和視圖構成,數據字典中的表是不允許被直接訪問的,但可以訪問數據字典中的視圖(前提是要有足夠的許可權)。
數據字典中的表和視圖屬於 SYS 用戶,被存放到 SYSTEM 表空間中,數據字典中的視圖都是只讀的,可以被查詢,但不能被修改。Oracle 中的數據字典有靜態和動態之分:
- 1、靜態數據字典:在用戶訪問這類數據字典時,數據不會發生改變的。
- 2、動態數據字典:依賴資料庫運行的性能,反映資料庫運行的一些內在信息,所以在訪問這類數據字典時往往是動態變化的。
1、靜態數據字典
靜態數據字典中的視圖可根據視圖名稱的首碼分為三類,分別是:
- 1、
USER_*
視圖:此類視圖提供了當前用戶所擁有的對象信息,即當前用戶模式下所有對象的相關信息。 - 2、
ALL_*
視圖:此類視圖提供了當前用戶能夠訪問的對象信息,即當前用戶能夠訪問到的所有對象的相關信息。 - 3、
DBA_*
視圖:此類視圖提供了資料庫中所有的對象信息。前提是當前用戶具有訪問這些資料庫對象的許可權,否則查不到數據。
1.1、實用靜態數據字典
表空間
SELECT * FROM DBA_TABLESPACES; -- 資料庫中所有的表空間信息(USER_TABLESPACES)
SELECT * FROM DBA_TS_QUOTAS; -- 資料庫中所有的表空間配額信息(USER_TS_QUOTAS)
SELECT * FROM DBA_DATA_FILES; -- 表空間和數據文件對應關係
SELECT * FROM DBA_TEMP_FILES; -- 臨時表空間和數據文件對應關係
SELECT * FROM DBA_SEGMENTS; -- 分配給資料庫中所有段的存儲信息(USER_SEGMENTS)
SELECT * FROM DBA_EXTENTS; -- 資料庫中所有表空間中包含段的區域信息(USER_EXTENTS)
角色、許可權
SELECT * FROM DBA_ROLES; -- 當前資料庫中的所有角色信息
SELECT * FROM SESSION_ROLES; -- 當前用戶被授予的所有角色
SELECT * FROM SESSION_PRIVS; -- 當前用戶被授予的所有許可權
SELECT * FROM ROLE_SYS_PRIVS t WHERE t.ROLE='CONNECT'; -- 查詢 CONNECT 角色擁有的系統許可權
SELECT * FROM ROLE_TAB_PRIVS t WHERE t.ROLE='CONNECT'; -- 查詢 CONNECT 角色擁有的對象許可權
用戶、用戶角色、用戶許可權
SELECT * FROM DBA_USERS; -- 當前資料庫的所有用戶信息(USER_USERS、ALL_USERS)
SELECT * FROM DBA_ROLE_PRIVS; -- 資料庫中所有的角色許可權信息(USER_ROLE_PRIVS)
SELECT * FROM DBA_SYS_PRIVS; -- 資料庫中所有的系統許可權信息(USER_SYS_PRIVS)
SELECT * FROM DBA_TAB_PRIVS; -- 資料庫中所有的對象許可權信息(USER_TAB_PRIVS、ALL_TAB_PRIVS)
SELECT * FROM DBA_COL_PRIVS; -- 資料庫中所有的對象列許可權信息(USER_COL_PRIVS、ALL_COL_PRIVS)
SELECT * FROM USER_TAB_PRIVS_RECD; -- 查詢當前用戶擁有的對象許可權
SELECT * FROM USER_TAB_PRIVS_MADE; -- 查詢當前用戶已授予出去的對象許可權
SELECT * FROM USER_COL_PRIVS_RECD; -- 查詢當前用戶擁有的關於列的對象許可權
SELECT * FROM USER_COL_PRIVS_MADE; -- 查詢當前用戶授予出去的列的對象許可權
表、列:
SELECT * FROM DBA_TABLES; -- 資料庫中所有的表信息(USER_TABLES、ALL_TABLES)
SELECT * FROM DBA_TAB_COLUMNS; -- 資料庫中所有的列信息(USER_TAB_COLUMNS、ALL_TAB_COLUMNS)
註釋:
SELECT * FROM DBA_TAB_COMMENTS; -- 資料庫中所有的表註釋(USER_TAB_COMMENTS、ALL_TAB_COMMENTS)
SELECT * FROM DBA_COL_COMMENTS; -- 資料庫中所有的列註釋(USER_COL_COMMENTS、ALL_COL_COMMENTS)
約束:
SELECT * FROM DBA_CONSTRAINTS; -- 資料庫中所有的約束信息(USER_CONSTRAINTS、ALL_CONSTRAINTS)
SELECT * FROM DBA_CONS_COLUMNS; -- 資料庫中所有的約束與列信息(USER_CONS_COLUMNS、ALL_CONS_COLUMNS)
索引:
SELECT * FROM DBA_INDEXES; -- 資料庫中所有的索引信息(USER_INDEXES、ALL_INDEXES)
SELECT * FROM DBA_IND_COLUMNS; -- 資料庫中所有的索引與列信息(USER_IND_COLUMNS、ALL_IND_COLUMNS)
視圖:
SELECT * FROM DBA_VIEWS; -- 資料庫中所有的視圖信息(USER_VIEWS、ALL_VIEWS)
存儲過程:
SELECT * FROM DBA_PROCEDURES; -- 資料庫中所有的視圖信息(USER_PROCEDURES、ALL_PROCEDURES)
觸發器:
SELECT * FROM DBA_TRIGGERS; -- 資料庫中所有的觸發器信息(USER_TRIGGERS、ALL_TRIGGERS)
任務:
SELECT * FROM DBA_JOBS; -- 資料庫中所有的任務信息(USER_JOBS、ALL_JOBS)
SELECT * FROM DBA_SCHEDULER_JOBS; -- 資料庫中所有調度程式作業的信息(USER_SCHEDULER_JOBS、ALL_SCHEDULER_JOBS)
序列:
SELECT * FROM DBA_SEQUENCES; -- 資料庫中所有的序列信息(USER_SEQUENCES、ALL_SEQUENCES)
同義詞:
SELECT * FROM DBA_SYNONYMS; -- 資料庫中所有的同義詞信息(USER_SYNONYMS、ALL_SYNONYMS)
所有模式對象:包括表、視圖、函數、存儲過程、觸發器、包、索引、序列等,還可以通過object_type
欄位來查詢指定類型的對象信息。
SELECT * FROM DBA_OBJECTS; -- 資料庫中所有的模式對象信息(USER_OBJECTS、ALL_OBJECTS)
1.2、運用靜態數據字典
查詢T_STAFF
表上的索引信息
SELECT t.* FROM USER_INDEXES t WHERE t.table_name='T_STAFF';
SELECT t.* FROM USER_IND_COLUMNS t WHERE t.table_name='T_STAFF';
查詢當前用戶模式下的所有索引
SELECT t1.table_type,t1.table_name,t1.index_name,t1.uniqueness,t2.column_name
FROM USER_INDEXES t1,USER_IND_COLUMNS t2
WHERE t1.table_name=t2.table_name AND t1.index_name=t2.index_name
ORDER BY 1,2,3,4,5;
查詢被禁用的觸發器
SELECT t.owner,t.trigger_name,t.trigger_type,t.triggering_event,t.table_owner||'.'||t.table_name tname
FROM dba_triggers t WHERE t.owner='DEMO' AND t.status<>'ENABLED' ORDER BY 1,2;
查詢對象的定義語句
SELECT t.text FROM USER_SOURCE t WHERE t.name='FN_NOW' ORDER BY t.line;
查詢編譯無效的對象
SELECT t.owner,t.object_name,t.object_type,t.last_ddl_time
FROM dba_objects t WHERE t.owner='DEMO' AND t.status<>'INVALID' ORDER BY 1,2;
查詢 DEMO 用戶模式中的T_STAFF
占用空間大小
SELECT t.owner,t.segment_type,t.segment_name,SUM(t.bytes)/1024/1024 "大小(M)"
FROM DBA_SEGMENTS t
WHERE t.owner='DEMO' AND t.segment_name='T_STAFF'
GROUP BY t.owner,t.segment_type,t.segment_name
ORDER BY 1,2,3;
統計表空間使用情況
WITH t1 AS(
SELECT t.tablespace_name,SUM(t.bytes)/1024/1024 sum_bytes
FROM DBA_DATA_FILES t GROUP BY t.tablespace_name
), t2 AS(
SELECT t.tablespace_name,SUM(t.bytes)/1024/1024 sum_bytes,MAX(t.bytes)/1024/1024 max_bytes
FROM DBA_FREE_SPACE t GROUP BY t.tablespace_name
)
SELECT t1.tablespace_name "表空間名稱",ROUND(t1.sum_bytes,2) "表空間大小(m)",
ROUND(t1.sum_bytes-t2.sum_bytes,2) "已使用空間(m)",ROUND(t2.sum_bytes,2) "空閑空間(m)",
ROUND((t1.sum_bytes-t2.sum_bytes)/t1.sum_bytes*100,2) "已用比例(%)",
ROUND(t2.max_bytes,2) "最大空閑塊(m)"
FROM t1,t2 WHERE t1.tablespace_name=t2.tablespace_name ORDER BY 4 DESC;
2、動態數據字典
由於動態數據字典中的數據會在資料庫打開和使用時不斷更新,且其內容主要與性能相關,故又被稱之為動態性能視圖。動態性能視圖提供有關內部磁碟結構和記憶體結構的數據,這些數據可以被查詢但不能被修改。Oracle DBA 使用動態性能視圖監視和調優資料庫。
實際的動態性能視圖都是由首碼V_$
標識的,每個動態性能視圖都有對應的同義詞,這些同義詞有一個統一的首碼V$
。事實上首碼為V_$
的視圖是不允許用戶訪問的,只能訪問首碼是V$
的同義詞。因為數據是動態的,所以不能保證動態性能視圖上讀取數據的一致性。
幾乎每個V$
視圖,都已一個與之對應的GV$
視圖,即全局V$
視圖。查詢GV$
視圖會從所有限定的示例中檢索V$
視圖。除V$
中的信息之外,每個GV$
視圖還包含一個名為INST_ID
的數據類型為 NUMBER 的列。INST_ID
列顯示從中獲取關聯的V$
視圖信息的實例編號。INST_ID
列可以用作過濾器,從可用實例的子集中檢索V$
信息。
2.1、實用動態性能視圖
SELECT * FROM V$CONTROLFILE; -- 控制文件信息
SELECT * FROM V$TABLESPACE; -- 控制文件中的表空間信息
SELECT * FROM V$DATABASE; -- 控制文件中有關資料庫的信息
SELECT * FROM V$DATAFILE; -- 控制文件中的數據文件信息
SELECT * FROM V$TEMPFILE; -- 臨時文件信息
SELECT * FROM V$RESOURCE; -- 有關係統資源的資源名稱和地址信息
SELECT * FROM V$RESOURCE_LIMIT; -- 有關係統資源的全局資源使用信息
SELECT * FROM V$INSTANCE; -- 當前實例的狀態,包含 Oracle 的版本信息
SELECT * FROM V$SESSTAT; -- 活動會話的統計信息
SELECT * FROM V$SESSION; -- 每個當前會話的會話信息
SELECT * FROM V$SESSION_WAIT; -- 活動會話正在等待的資源或事件
SELECT * FROM V$SESSION_EVENT; -- 會話等待事件的信息
SELECT * FROM V$SYSTEM_EVENT; -- 某個事件的等待事件彙總信息
SELECT * FROM V$LOCK; -- 資料庫當前持有的鎖和未完成的鎖定或閂鎖請求
SELECT * FROM V$ACCESS; -- 當前對對象施加的鎖的信息,這些鎖是為了確保在執行 SQL 時不會從庫緩存中過期
SELECT * FROM V$SQL; -- 共用 SQL 區域的統計信息,通常在查詢執行結束時更新
SELECT * FROM V$SQLTEXT; -- SGA 中屬於共用 SQL 游標的 SQL 語句文本
SELECT * FROM V$SQLAREA; -- 共用 SQL 區域的統計信息
SELECT * FROM V$SQLSTATS; -- SQL 游標的基本性能統計信息
SELECT * FROM V$CONTEXT; -- 當前會話中的參數信息
SELECT * FROM V$LICENSE; -- 基本許可信息
SELECT * FROM V$RESERVED_WORDS ORDER BY 1; -- ORACLE 保留字
事實上,Oracle 提供了很多動態性能視圖,其中有一個視圖是V$FIXED_TABLE
,該視圖中包含資料庫中的所有動態性能表、視圖和派生表的信息。其中部分V$
表 (例如V$ROLLNAME
) 是指真正的表, 因此沒有列出。
2.2、運用動態性能視圖
SELECT * FROM V$VERSION; -- 資料庫中核心庫組件的版本號,每個組件占一行
SELECT * FROM V$OPTION; -- 資料庫中功能選項,已安裝的則 value 為 true
SELECT * FROM V$PARAMETER; -- 有關會話當前有效的初始化參數的信息,isdefault=true 表示當前值是預設值
查看有問題的語句的查詢計劃
SELECT * FROM DBA_HIST_SQL_PLAN t;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('cfz686a6qp0kg'));
檢查用戶當前操作極其使用的資源
SELECT t1.sid,t1.username,t2.sql_text FROM V$SESSION t1,V$SQLTEXT t2
WHERE t1.sql_address=t2.address AND t1.sql_hash_value=t2.hash_value ORDER BY t1.username,t1.sid,t2.piece;
查詢 CUP 時間超過 2000000 微妙的語句
SELECT t.sql_text,t.executions FROM V$SQLSTATS t WHERE t.cpu_time>2000000;
查詢最近 7 天,磁碟增量讀取數高於 10 萬的語句
SELECT t1.snap_id,t1.disk_reads_delta,t1.disk_reads_total,t1.executions_delta,t1.executions_total,
t1.disk_reads_delta/t1.executions_delta rds_exec_ratio,t1.sql_id,t2.sql_text
FROM DBA_HIST_SQLSTAT t1,DBA_HIST_SQLTEXT t2
WHERE t1.sql_id=t2.sql_id AND t1.disk_reads_delta>100000
ORDER BY t1.disk_reads_delta DESC;
查詢磁碟讀取數大於 10 萬的語句,disk_reads 磁碟讀取數,executions 執行次數
SELECT t2.username,t1.disk_reads,t1.executions,t1.disk_reads/t1.executions rds_exec_ratio,t1.sql_text,t1.elapsed_time,t1.cpu_time
FROM V$SQLAREA t1,DBA_USERS t2
WHERE t1.parsing_user_id=t2.user_id AND t1.disk_reads>100000
ORDER BY t1.disk_reads DESC;
查詢緩衝區數量最多的 10 個語句
WITH t2 AS(
SELECT t1.buffer_gets,
RANK() OVER(ORDER BY t1.buffer_gets DESC) AS rank_buf_gets,
TO_CHAR(100*RATIO_TO_REPORT(buffer_gets) OVER(),'999.99') rate_buf_gets,
t1.address,t1.sql_text
FROM V$SQL t1)
SELECT t2.* FROM t2 WHERE t2.rank_buf_gets<=10;
測定數據的命中率
SELECT 1-(SUM(DECODE(t.name,'physical reads',VALUE,0))/(SUM(DECODE(t.name,'db block gets',VALUE,0))+
(SUM(DECODE(t.name,'consistent gets',VALUE,0))))) read_hit_ratio FROM V$SYSSTAT t;
測定數據字典的命中率
SELECT SUM(gets),SUM(getmisses),(1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100 hitrate FROM V$ROWCACHE;
測定共用 SQL 和 PL/SQL 的命中率
SELECT SUM(t.pins) executions,SUM(t.pinhits) hits,((SUM(t.pinhits)/SUM(t.pins))*100) pinhitratio,
SUM(reloads) misses,((SUM(t.pins)/(SUM(t.pins)+SUM(t.reloads)))*100) relhitratio FROM V$LIBRARYCACHE t;
下麵的這個值應該大於 15
SELECT t.sql_id,COUNT(1) bind_count FROM V$SQL_BIND_CAPTURE t
WHERE t.child_number=0 GROUP BY t.sql_id HAVING COUNT(1)>20 ORDER BY COUNT(1);
確定需要固定的 PL/SQL 對象,搜索那些需要空間大於100KB的對象
SELECT t.name,t.sharable_mem FROM V$DB_OBJECT_CACHE t WHERE t.sharable_mem>100000
AND t.type IN('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') AND t.kept='NO';
查詢數據文件使用情況
SELECT t1.file#,t1.name,t1.status,t1.bytes,t2.phyrds,t2.phyblkrd,t2.phywrts,t2.phyblkwrt
FROM V$DATAFILE t1,V$FILESTAT t2 WHERE t1.file#=t2.file#;
查詢表空間的使用情況
SELECT t1.tablespace_name,t1.file_name,t1.bytes,t2.phyrds,t2.phyblkrd,t2.phywrts,t2.phyblkwrt
FROM DBA_DATA_FILES t1,V$FILESTAT t2 WHERE t1.file_id=t2.file#;
查詢表空間碎片化程度
SELECT t.tablespace_name,SUM(t.bytes) sum_bytes,MAX(t.bytes) max_bytes,COUNT(t.block_id) cnt
FROM DBA_FREE_SPACE t GROUP BY t.tablespace_name;
查詢碎片化程度最高於 5 的存儲對象
WITH t2 AS(
SELECT t1.segment_type,t1.segment_name,COUNT(1) OVER(PARTITION BY t1.segment_name) cnt
FROM DBA_SEGMENTS t1
)
SELECT DISTINCT t2.* FROM t2 WHERE t2.cnt>5;
3、死鎖
死鎖是指兩個或兩個以上的進程在執行過程中,因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去。此時稱系統處於死鎖狀態或系統產生了死鎖,這些永遠在互相等待的進程稱為死鎖進程。由於資源占用是互斥的,當某個進程提出申請資源後,使得有關進程在無外力協助下,永遠分配不到必需的資源而無法繼續運行,這就產生了一種特殊現象——死鎖。
3.1、定位死鎖
1、定位造成死鎖的用戶和機器
SELECT t1.username,t1.lockwait,t1.status,t1.machine,t1.program
FROM V$SESSION t1 WHERE EXISTS(SELECT 1 FROM V$LOCKED_OBJECT t2 WHERE t1.sid=t2.session_id);
用 DBA 用戶執行如下語句,如果能查詢到數據,就說明資料庫中存在死鎖。上述語句中的欄位說明:
- username:發生死鎖的資料庫用戶。
- lockwait:死鎖等待。有內容的就是被死鎖的,沒有內容的就是造成死鎖的。
- status:死鎖狀態。ACTIVE 狀態表示被死鎖。
- machine:死鎖語句所在的機器。
- program:產生死鎖的那個應用程式名稱。
2、定位造成死鎖的語句
-- 用 DBA 用戶執行以下語句,可以查看到被鎖的語句(後執行的語句被先執行的語句鎖住)
SELECT t1.sql_id,t1.sql_text FROM V$SQL t1 WHERE t1.hash_value IN(
SELECT t2.sql_hash_value FROM V$SESSION t2 WHERE t2.sid IN(
SELECT t3.session_id FROM V$LOCKED_OBJECT t3
)
);
-- 另一種寫法,可以查到更多信息
SELECT t2.username,t2.serial#,t1.id1,t3.sql_text FROM V$LOCK t1,V$SESSION t2,V$SQLTEXT t3
WHERE t1.kaddr=t2.lockwait AND t2.sql_address=t3.address AND t2.sql_hash_value=t3.hash_value;
-- 查詢資料庫中死鎖相關信息
SELECT t1.owner,t1.object_name 被鎖對象名,t2.session_id,t2.oracle_username 登錄用戶,
t2.os_user_name 登錄機器用戶名,t2.process,t2.locked_mode 鎖模式,t3.machine 機器名,
t3.terminal 終端用戶名,t3.logon_time 登錄資料庫時間,t3.status,t3.sid,t3.serial#,t3.program
FROM all_objects t1,v$locked_object t2,v$session t3
WHERE t1.object_id=t2.object_id AND t2.process=t3.process
ORDER BY 1,2;
3.2、解鎖方法
一般來說只要將產生死鎖的語句提交就可以解鎖了,但實際上用戶往往不知道死鎖是那裡造成的。當然迫不得以的話,將程式關閉並重新啟動肯定是可以解鎖的。下麵介紹一種常見的解鎖方法(即直接把有問題的會話 Kill 掉):
1、定位死鎖的進程
SELECT t2.username,t1.object_id,t1.session_id,t2.serial#,t1.oracle_username,t1.os_user_name,t2.program,t2.terminal
FROM V$LOCKED_OBJECT t1,V$SESSION t2 WHERE t1.session_id=t2.sid;
2、Kill 掉這個死鎖的進程
ALTER SYSTEM KILL SESSION '[sid/session_id],[serial#]'; -- session_id 和 serial# 的值來自第一步的執行結果
3.3、強制刪除已連接用戶
當一個用戶被連接之後,就無法直接刪除該用戶了。如果要強制刪除,就得先 Kill 掉會話進程。強制刪除一個已連接用戶的步驟:
1、查詢該用戶的會話信息,得到用戶的 sid 和 serial#。
SELECT t.sid,t.serial# FROM V$SESSION t WHERE t.username='user_name';
2、Kill 掉該用戶的所有會話進程。
ALTER SYSTEM KILL SESSION 'sid, serial#';
3、刪除用戶及用戶對象。
DROP USER user_name CASCADE;
4、總結
本文第一節介紹了 Oracle 中的數據字典及常見數據字典和簡單應用;第二節主要講述了 Oracle 中的死鎖,著重介紹瞭如何定位死鎖及如何解鎖。
Oracle 中的數據字典非常多,正常人肯定是無法全部記住的,其實也沒必要記住,只需要記住 DICTIONARY 視圖就可以了,因為這個視圖中記錄了所有的數據字典的名稱和描述,就像是一個數據字典的字典。所以當我們需要查詢數據字典中的信息卻又不知道該查那個數據字典時,就可以到 DICTIONARY 視圖裡找,該視圖還有個同名詞 DICT。
本文鏈接:http://www.cnblogs.com/hanzongze/p/Oracle-Dictionary.html
版權聲明:本文為博客園博主 韓宗澤 原創,作者保留署名權!歡迎通過轉載、演繹或其它傳播方式來使用本文,但必須在明顯位置給出作者署名和本文鏈接!本人初寫博客,水平有限,若有不當之處,敬請批評指正,謝謝!