好用 Oracle SQL command(for DBA)

隨手記下來，當做筆記用

— 1. 查詢所有 session, 確認是否有人使用某一個 package

SELECT *
  FROM v$access va, v$session vs
 WHERE va.object = 'XXWIP_CLOT_TEMP_T'
   AND va.sid = vs.sid;

 

— 2. 查詢是否有人正 lock 某一個 package

SELECT * FROM sys.dba_ddl_locks WHERE NAME = 'XXINVPKG';

 

— 3. 查詢某個 table 是否被人 lock

SELECT oracle_username, os_user_name, locked_mode, object_name, object_type
  FROM v$locked_object a, all_objects b
 WHERE a.object_id = b.object_id
   AND object_name LIKE 'XXWIP_CLOT%';

 

— 4. 所有執行 session 的 sql command

SELECT c.spid     unix_pid, --b1,
       b.osuser   os_user, --c1,
       b.username oracle_user, --c2,
       b.sid      sid, --b2,
       b.serial#  serial#, --b3,
       a.sql_text
  FROM v$sqltext a, v$session b, v$process c
 WHERE a.address = b.sql_address
      --   and b.status     = 'ACTIVE' 
      /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
   AND b.paddr = c.addr
   AND a.hash_value = b.sql_hash_value
 ORDER BY c.spid, a.hash_value, a.piece