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
1
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