好用 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