程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle數據庫維護常用的SQL代碼示例

Oracle數據庫維護常用的SQL代碼示例

編輯:Oracle數據庫基礎

Oracle數據庫維護是作為一名數據庫管理員必須要掌握的技能。數據庫維護的操作有很多種,本文我們總結了18種常用的數據庫維護操作的SQL代碼示例,接下來就讓我們一起來了解一下這部分內容吧。

1、求當前會話的SID,SERIAL#

  1. SELECT Sid, Serial#  
  2. FROM V$session  
  3. WHERE Audsid = Sys_Context('USERENV', 'SESSIONID'); 

2、查詢session的OS進程ID

  1. SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#,  
  2. s.Osuser, s.Machine  
  3. FROM V$process p, V$session s, V$bgprocess b  
  4. WHERE p.Addr = s.Paddr  
  5. AND p.Addr = b.Paddr  
  6. And (s.sid=&1 or p.spid=&1)  
  7. UNION ALL  
  8. SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,  
  9. s.Serial#, s.Osuser, s.Machine  
  10. FROM V$process p, V$session s  
  11. WHERE p.Addr = s.Paddr  
  12. And (s.sid=&1 or p.spid=&1)  
  13. AND s.Username IS NOT NULL; 

3、根據sid查看對應連接正在運行的sql

  1. SELECT /*+ PUSH_SUBQ */  
  2. Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,  
  3. Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,  
  4. Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,  
  5. Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,  
  6. SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status  
  7. FROM V$sqlarea  
  8. WHERE Address = (SELECT Sql_Address  
  9. FROM V$session  
  10. WHERE Sid = &sid ); 

4、查找object為哪些進程所用

  1. SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name,  
  2. a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner,  
  3. a.OBJECT Object_Name,  
  4. Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,  
  5. p.Program Oracle_Process, s.Terminal Terminal, s.Program Program,  
  6. s.Status Session_Status  
  7. FROM V$session s, V$Access a, V$process p  
  8. WHERE s.Paddr = p.Addr  
  9. AND s.TYPE = 'USER' 
  10. AND a.Sid = s.Sid  
  11. AND a.OBJECT = '&obj' 
  12. ORDER BY s.Username, s.Osuser 

5、查看有哪些用戶連接

  1. SELECT s.Osuser Os_User_Name,  
  2. Decode(Sign(48 - Command),1,To_Char(Command),  
  3. 'Action Code #' || To_Char(Command)) Action,  
  4. p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal,  
  5. s.Program Program, s.Username User_Name,  
  6. s.Fixed_Table_Sequence Activity_Meter, '' Query, 0 Memory,  
  7. 0 Max_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_Num  
  8. FROM V$session s, V$process p  
  9. WHERE s.Paddr = p.Addr  
  10. AND s.TYPE = 'USER' 
  11. ORDER BY s.Username, s.Osuser 

6、根據v.sid查看對應連接的資源占用等情況

  1. SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic#  
  2. FROM V$statname n, V$sesstat v  
  3. WHERE v.Sid = &sid  
  4. AND v.Statistic# = n.Statistic#  
  5. ORDER BY n.CLASS, n.Statistic# 

7、查詢耗資源的進程(top session)

  1. SELECT s.Schemaname Schema_Name,  
  2. Decode(Sign(48 - Command),  
  3. 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,  
  4. Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,  
  5. s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,  
  6. s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value  
  7. FROM V$sesstat St, V$session s, V$process p  
  8. WHERE St.Sid = s.Sid  
  9. AND St.Statistic# = To_Number('38')  
  10. AND ('ALL' = 'ALL' OR s.Status = 'ALL')  
  11. AND p.Addr = s.Paddr  
  12. ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC 

8、查看鎖(lock)情況

  1. SELECT /*+ RULE */  
  2. Ls.Osuser Os_User_Name, Ls.Username User_Name,  
  3. Decode(Ls.TYPE,  
  4. 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock',  
  5. 'TX', 'Transaction enqueue lock', 'UL', 'User supplIEd lock') Lock_Type,  
  6. o.Object_Name OBJECT,  
  7. Decode(Ls.Lmode,  
  8. 1, NULL, 2, 'Row Share', 3, 'Row Exclusive',  
  9. 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',  
  10. NULL) Lock_Mode,  
  11. o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2  
  12. FROM Sys.Dba_Objects o,  
  13. (SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1,  
  14. l.Id2  
  15. FROM V$session s, V$lock l  
  16. WHERE s.Sid = l.Sid) Ls  
  17. WHERE o.Object_Id = Ls.Id1  
  18. AND o.Owner <> 'SYS'  
  19. ORDER BY o.Owner, o.Object_Name 

9、查看等待(wait)情況

  1. SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum_Value  
  2. FROM V$waitstat Ws, V$sysstat Ss  
  3. WHERE Ss.NAME IN ('db block gets', 'consistent gets')  
  4. GROUP BY Ws.CLASS, Ws.COUNT 

10、求process/session的狀態

  1. SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial#  
  2. FROM V$process p, V$session s  
  3. WHERE s.Paddr = p.Addr; 

11、求誰阻塞了某個session(10g)

  1. SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_Time  
  2. FROM V$session  
  3. WHERE State IN ('WAITING')  
  4. AND Wait_Class != 'Idle'; 

12、查會話的阻塞

  1. col user_name format a32  
  2. SELECT /*+ rule */  
  3. Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name,  
  4. o.Owner, o.Object_Name, s.Sid, s.Serial#  
  5. FROM V$locked_Object l, Dba_Objects o, V$session s  
  6. WHERE l.Object_Id = o.Object_Id  
  7. AND l.Session_Id = s.Sid  
  8. ORDER BY o.Object_Id, Xidusn DESC;  
  9. col username format a15  
  10. col lock_level format a8  
  11. col owner format a18  
  12. col object_name format a32  
  13. SELECT /*+ rule */  
  14. s.Username,  
  15. Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock_Level,  
  16. o.Owner, o.Object_Name, s.Sid, s.Serial#  
  17. FROM V$session s, V$lock l, Dba_Objects o  
  18. WHERE l.Sid = s.Sid  
  19. AND l.Id1 = o.Object_Id(+)  
  20. AND s.Username IS NOT NULL; 

13、求等待的事件及會話信息/求會話的等待及會話信息

  1. SELECT Se.Sid, s.Username, Se.Event, Se.Total_Waits, Se.Time_Waited,  
  2. Se.Average_Wait  
  3. FROM V$session s, V$session_Event Se  
  4. WHERE s.Username IS NOT NULL  
  5. AND Se.Sid = s.Sid  
  6. AND s.Status = 'ACTIVE' 
  7. AND Se.Event NOT LIKE '%SQL*Net%'  
  8. ORDER BY s.Username;  
  9. SELECT s.Sid, s.Username, Sw.Event, Sw.Wait_Time, Sw.State,  
  10. Sw.Seconds_In_Wait  
  11. FROM V$session s, V$session_Wait Sw  
  12. WHERE s.Username IS NOT NULL  
  13. AND Sw.Sid = s.Sid  
  14. AND Sw.Event NOT LIKE '%SQL*Net%'  
  15. ORDER BY s.Username; 

14、求會話等待的file_id/block_id

  1. col event format a24  
  2. col p1text format a12  
  3. col p2text format a12  
  4. col p3text format a12  
  5. SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3  
  6. FROM V$session_Wait  
  7. WHERE Event NOT LIKE '%SQL%'  
  8. AND Event NOT LIKE '%rdbms%'  
  9. AND Event NOT LIKE '%mon%'  
  10. ORDER BY Event;  
  11. SELECT NAME, Wait_Time  
  12. FROM V$latch l  
  13. WHERE EXISTS (SELECT 1  
  14. FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3  
  15. FROM V$session_Wait  
  16. WHERE Event NOT LIKE '%SQL%'  
  17. AND Event NOT LIKE '%rdbms%'  
  18. AND Event NOT LIKE '%mon%') x  
  19. WHERE x.P1 = l.Latch#); 

15、求會話等待的對象

  1. col owner format a18  
  2. col segment_name format a32  
  3. col segment_type format a32  
  4. SELECT Owner, Segment_Name, Segment_Type  
  5. FROM Dba_Extents  
  6. WHERE File_Id = &File_Id  
  7. AND &Block_Id BETWEEN Block_Id AND Block_Id + Blocks - 1; 

16、求出某個進程,並對它進行跟蹤

  1. SELECT s.Sid, s.Serial#  
  2. FROM V$session s, V$process p  
  3. WHERE s.Paddr = p.Addr  
  4. AND p.Spid = &1;  
  5. Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, TRUE);  
  6. Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, FALSE); 

17、求當前session的跟蹤文件

  1. SELECT P1.VALUE || '/' || P2.VALUE || '_ora_' || p.Spid || '.ora' Filename  
  2. FROM V$process p, V$session s, V$parameter P1, V$parameter P2  
  3. WHERE P1.NAME = 'user_dump_dest' 
  4. AND P2.NAME = 'instance_name' 
  5. AND p.Addr = s.Paddr  
  6. AND s.Audsid = Userenv('SESSIONID')  
  7. AND p.Background IS NULL  
  8. AND Instr(p.Program, 'CJQ') = 0; 

18、求出鎖定的對象

  1. SELECT Do.Object_Name, Session_Id, Process, Locked_Mode  
  2. FROM V$locked_Object Lo, Dba_Objects Do  
  3. WHERE Lo.Object_Id = Do.Object_Id; 

關於Oracle數據庫維護常用的SQL代碼示例就介紹到這裡了,希望本次的介紹能夠對您有所收獲!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved