col evnt for a30
col usr for a10
col osuser for a14
col mach for a8
col prog for a20
col sid for 999
select ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,osuser,substr(a.username,1,10) usr,substr(a.program,1,20) prog,to_char(logon_time,'dd/mm hh24:mm:ss') logon,sql_id,prev_sql_id,substr(event,1,30) evnt,pga_alloc_mem pga
,decode(a.command,0,'UNKNOWN',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT OBJECT',
18,'REVOKE OBJECT',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK',
27,'NO-OP',
28,'RENAME',
29,'COMMENT',
30,'AUDIT OBJECT',
31,'NOAUDIT OBJECT',
32,'CREATE DATABASE LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEG',
37,'ALTER ROLLBACK SEG',
38,'DROP ROLLBACK SEG',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE MATERIALIZED VIEW LOG',
72,'ALTER MATERIALIZED VIEW LOG',
73,'DROP MATERIALIZED VIEW LOG',
74,'CREATE MATERIALIZED VIEW',
75,'ALTER MATERIALIZED VIEW',
76,'DROP MATERIALIZED VIEW',
77,'CREATE TYPE',
78,'DROP TYPE',
79,'ALTER ROLE',
80,'ALTER TYPE',
81,'CREATE TYPE BODY',
82,'ALTER TYPE BODY',
83,'DROP TYPE BODY',
84,'DROP LIBRARY',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
88,'ALTER VIEW',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
100,'LOGON',
101,'LOGOFF',
102,'LOGOFF BY CLEANUP',
103,'SESSION REC',
104,'SYSTEM AUDIT',
105,'SYSTEM NOAUDIT',
106,'AUDIT DEFAULT',
107,'NOAUDIT DEFAULT',
108,'SYSTEM GRANT',
109,'SYSTEM REVOKE',
110,'CREATE PUBLIC SYNONYM',
111,'DROP PUBLIC SYNONYM',
112,'CREATE PUBLIC DATABASE LINK',
113,'DROP PUBLIC DATABASE LINK',
114,'GRANT ROLE',
115,'REVOKE ROLE',
116,'EXECUTE PROCEDURE',
117,'USER COMMENT',
118,'ENABLE TRIGGER',
119,'DISABLE TRIGGER',
120,'ENABLE ALL TRIGGERS',
121,'DISABLE ALL TRIGGERS',
122,'NETWORK ERROR',
123,'EXECUTE TYPE',
128,'FLASHBACK',
129,'CREATE SESSION',
130,'ALTER MINING MODEL',
131,'SELECT MINING MODEL',
133,'CREATE MINING MODEL',
134,'ALTER PUBLIC SYNONYM',
135,'DIRECTORY EXECUTE',
136,'SQL*LOADER DIRECT PATH LOAD',
137,'DATAPUMP DIRECT PATH UNLOAD',
157,'CREATE DIRECTORY',
158,'DROP DIRECTORY',
159,'CREATE LIBRARY',
160,'CREATE JAVA',
161,'ALTER JAVA',
162,'DROP JAVA',
163,'CREATE OPERATOR',
164,'CREATE INDEXTYPE',
165,'DROP INDEXTYPE',
166,'ALTER INDEXTYPE',
167,'DROP OPERATOR',
168,'ASSOCIATE STATISTICS',
169,'DISASSOCIATE STATISTICS',
170,'CALL METHOD',
171,'CREATE SUMMARY',
172,'ALTER SUMMARY',
173,'DROP SUMMARY',
174,'CREATE DIMENSION',
175,'ALTER DIMENSION',
176,'DROP DIMENSION',
177,'CREATE CONTEXT',
178,'DROP CONTEXT',
179,'ALTER OUTLINE',
180,'CREATE OUTLINE',
181,'DROP OUTLINE',
182,'UPDATE INDEXES',
183,'ALTER OPERATOR',
192,'ALTER SYNONYM',
197,'PURGE USER_RECYCLEBIN',
198,'PURGE DBA_RECYCLEBIN',
199,'PURGE TABLESPACE',
200,'PURGE TABLE',
201,'PURGE INDEX',
202,'UNDROP OBJECT',
204,'FLASHBACK DATABASE',
205,'FLASHBACK TABLE',
206,'CREATE RESTORE POINT',
207,'DROP RESTORE POINT',
208,'PROXY AUTHENTICATION ONLY',
209,'DECLARE REWRITE EQUIVALENCE',
210,'ALTER REWRITE EQUIVALENCE',
211,'DROP REWRITE EQUIVALENCE',
212,'CREATE EDITION',
213,'ALTER EDITION',
214,'DROP EDITION',
215,'DROP ASSEMBLY',
216,'CREATE ASSEMBLY',
217,'ALTER ASSEMBLY',
218,'CREATE FLASHBACK ARCHIVE',
219,'ALTER FLASHBACK ARCHIVE',
220,'DROP FLASHBACK ARCHIVE',
225,'ALTER DATABASE LINK',
305,'ALTER PUBLIC DATABASE LINK') command
from gv$session a,gv$process b
where a.paddr=b.addr
and a.inst_id=b.inst_id
and b.username is not null
order by 9 desc ;
KILL OSUSER USR PROG LOGON SQL_ID PREV_SQL_ID EVNT PGA COMMAND
--------------- -------------- ---------- -------------------- -------------- ------------- ------------- ------------------------------ ---------- --------
'370,58214,@2' oracle oracle@*******.int.s 25/07 13:07:02 6hnhqahphpk8n rdbms ipc message 165162756 UNKNOWN
'370,2076,@1' oracle oracle@*******.int.s 25/07 13:07:51 6hnhqahphpk8n rdbms ipc message 163327748 UNKNOWN
'981,14255,@1' oracle oracle@*******.int.s 25/07 13:07:53 wait for unread message on bro 64040708 UNKNOWN
===
SET PAGESIZE 500 linesize 500
COLUMN instance_name FORMAT a9 HEADING 'Instance'
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'
COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right
COLUMN oracle_username FORMAT a14 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a12 HEADING 'O/S User' JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right
COLUMN session_program FORMAT a26 HEADING 'Session Program' TRUNC
COLUMN session_terminal FORMAT a10 HEADING 'Terminal' JUSTIFY right
COLUMN session_machine FORMAT a22 HEADING 'Machine' JUSTIFY right
col kill for a17
prompt
prompt +----------------------------------------------------+
prompt | User Sessions (All) |
prompt +----------------------------------------------------+
BREAK ON instance_name SKIP PAGE
SELECT
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill
-- i.instance_name instance_name
-- , s.sid sid
-- , s.serial# serial_id
, sql_id
,PREV_SQL_ID
, lpad(s.status,9) session_status
, lpad(s.username,14) oracle_username
, lpad(s.osuser,12) os_username
, lpad(p.spid,7) os_pid
-- , s.program session_program
, lpad(s.terminal,10) session_terminal
, lpad(s.machine,19) session_machine
, s.event
,FINAL_BLOCKING_SESSION_STATUS
,FINAL_BLOCKING_SESSION
,p1
,p2
p3
FROM gv$session s
INNER JOIN gv$process p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
INNER JOIN gv$instance i ON (p.inst_id = i.inst_id)
AND s.TYPE != 'BACKGROUND'
and s.status='ACTIVE'
ORDER BY
i.instance_name
, s.sid
/
define 1='XXXX' ---- Change user name !!!!
col u_username head USERNAME for a23
col u_sid head SID for a14
col u_audsid head AUDSID for 9999999999
col u_osuser head OSUSER for a16
col u_machine head MACHINE for a25 truncate
col u_program head PROGRAM for a20
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.username||CASE WHEN s.sid = SYS_CONTEXT('userenv','sid') THEN ' (me)' WHEN s.type = 'BACKGROUND' THEN ' (bg)' END u_username,
p.spid,
s.sql_id,
s.audsid u_audsid,
s.osuser u_osuser,
substr(s.machine,instr(s.machine,'\')) u_machine,
CASE WHEN s.type = 'BACKGROUND' OR REGEXP_LIKE(s.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(s.program,INSTR(s.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(s.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END u_program,
--substr(s.program,instr(s.program,'(')) u_program,
-- s.sql_address,
s.last_call_et lastcall,
s.status,
--, s.logon_time
s.event
from
gv$session s,
gv$process p
where 1=1
and s.paddr=p.addr
and s.inst_id=p.inst_id
--and s.type!='BACKGROUND'
and (lower(s.username) like lower('&1') or (nvl(s.username,'%') = '%' and s.type = 'USER'))
and s.status='ACTIVE'
order by s.inst_id,s.last_call_et desc
/
set linesize 300
COLUMN Block HEADING "Block" FORMAT a10
COLUMN blocking_instance HEADING "Blocking|I#" FORMAT 999999999
COLUMN blocking_instance HEADING "Blocking|I#" FORMAT 999999999
COLUMN blocking_session_status HEADING "Blocking|SID Status" FORMAT a10
COLUMN final_blocking_instance HEADING "Final|Blocking|I#" FORMAT 999999999
COLUMN final_blocking_session HEADING "Final|Blocking|SID" FORMAT 999999999
COLUMN final_blocking_session_status HEADING "Final|Blocking|SID Status" FORMAT a10
COLUMN inst_id HEADING "I#" FORMAT 99
COLUMN username HEADING "DBUser" FORMAT a15
COLUMN osuser HEADING "OSUser" FORMAT a15
COLUMN status HEADING "Status" FORMAT a10
COLUMN state HEADING "State" FORMAT a10
COLUMN logon_time HEADING "LogonTime" FORMAT a18
COLUMN service_name HEADING "ServiceName" FORMAT a20
col EVENT for a30
col kill for a15
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.sql_id,PREV_SQL_ID,event,'Blocked By' Block
, s.blocking_instance
, s.blocking_session
, s.blocking_session_status
, s.final_blocking_instance
, s.final_blocking_session
, s.final_blocking_session_status
FROM gv$session s
where 1=1
-- and s.sid = :SID
-- AND s.inst_id = :INST_ID
-- and s.blocking_session is NOT NULL
order by 1;
set linesize 1000 pagesize 500
col kill for a17
col PLSQL_ENTRY_OBJECT for a20
col PLSQL_ENTRY_SUBPROGRAM for a20
col PLSQL_ENTRY_SUBPROGRAM for a20
col PLSQL_ENTRY_OBJECT for a20
col CLIENT_IDENTIFIER for a20
select
--s.ECID ,s.inst_id, s.SID,s.SERIAL#,con_id,p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier
'''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||'''' kill,s.con_id,
--s.ECID ,
p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier
, ( SELECT max( substr( sql_text , 1, 40 )) FROM gv$sql sq WHERE sq.sql_id = s.sql_id ) AS sql_text
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram
--, 'alter system kill session ' || '''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||''''|| ' immediate;' kill_session
from gv$session s ,gv$process p
where 1=1
-- and s.process='5' --forms OS process ID
--s.program like '%xxx%'
and p.addr=s.paddr
and p.inst_id = s.inst_id
and s.status!='INACTIVE'
;
set linesize 500 pagesize 400
col osuser for a16
col program for a10 trunc
col Locked for a6
col status for a1 trunc print
col "hh:mm:ss" for a8
col SQL_ID for a15
col seq# for 99990 --Sequence number that uniquely identifies the wait. Incremented for each wait
col module for a25
col ACTION for a20
col kill for a19
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
col username for a25
col MACHINE for a37
select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill
,STATUS
,MACHINE
--,con_id --12c
, username
, ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser
, substr(program,instr(program,'/',-1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status,
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss" , sql_id,prev_sql_id, seq# , event,
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state
, substr(module,1,25) module,final_blocking_session_status,sql_exec_start
-- substr(action,1,20) action
from GV$SESSION
where 1=1
--and USERNAME like 'DBFS%'
--and USERNAME not in ('SYS','SYSRAC','DBSNMP' )
--and type = 'USER'
--and STATUS!='INACTIVE'
--and audsid != 0 -- to exclude internal processess
--and SQL_ID='cabcn90dg7ttu'
--and event not like 'SQL*Net message from%'
--and osuser='_USER'
--and con_id =5
--and sid=4605
--and program like 'oracle%'
--and SERVICE_NAME in ('p_p')
and PADDR = (select ADDR from V$PROCESS where SPID = 22289)
order by inst_id, last_call_et desc, sid
/
SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 500 PAGESIZE 50000 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN oracle_username FORMAT a18 HEADING 'Oracle User'
COLUMN os_username FORMAT a18 HEADING 'O/S User'
COLUMN os_pid FORMAT a8 HEADING 'O/S PID'
COLUMN session_terminal FORMAT a10 HEADING 'Terminal' TRUNC
COLUMN session_machine FORMAT a30 HEADING 'Machine' TRUNC
COLUMN session_program FORMAT a40 HEADING 'Session Program' TRUNC
col kill for a23
col EVENT for a20 TRUNC
BREAK ON instance_name SKIP PAGE
SELECT
'''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||'''' kill
--,s.con_id,
,'kill -9 '||spid PKill
, s.status
, s.sql_id
,prev_sql_id
,event
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.terminal session_terminal
, s.machine session_machine
, s.program session_program
FROM gv$session s
INNER JOIN gv$process p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
WHERE 1=1
-- and s.status = 'ACTIVE'
AND s.username IS NOT null
ORDER BY 1
set linesize 300
col PROGRAM for a20
col MACHINE for a20
col SCHEMANAME for a18
col kill for a17
select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill ,sid,serial#,username,sql_id,schemaname,osuser,process,machine,port,program,type,paddr
from gv$session
where PADDR = (select ADDR from V$PROCESS where SPID = &spid)
No comments:
Post a Comment