FilesExpand file tree

 main

/

simplecool.sql

Copy path

BlameMore file actions

BlameMore file actions

Latest commit

 

History

History

History

31 lines (21 loc) · 1.1 KB

 main

/

simplecool.sql

Top

File metadata and controls

• Code
• Blame

31 lines (21 loc) · 1.1 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

-- Plan from the Shared Pool

select * from table(dbms_xplan.display_cursor('SQL_ID',null,'ALL'));

-- Plan from the AWR

select * from table(dbms_xplan.display_awr('SQL_ID',null,null,'ALL'));

select * from table(dbms_xplan.display_awr('SQL_ID',null,DBID,'ALL'));

-- kill all sessions executing a bad SQL

select

'alter system kill session '''|| s.SID||',' || s.serial# ||''';'

from v$session s

where s.sql_id='&SQL_ID';

-- kill all sessions executing a bad SQL but in a RAC environment

SELECT 'alter system kill session '''|| SID|| ','|| SERIAL#|| ',@'|| inst_id|| ''' immediate ;'

FROM gv$session

WHERE sql_id = '&sql_id'

-- Kill all sessions waiting for specific events by a specific user

select 'alter system kill session '''|| s.SID||',' || s.serial# ||''';'

from gv$session s

where 1=1

and (event='latch: shared pool' or event='library cache lock') and s.USERNAME='DBSNMP';

-- generate commands to kill all sessions from a specific user on specific instance

select 'alter system kill session '''|| SID||',' || serial# ||''' immediate;' from gv$session where username='BAD_USER' and inst_id=1;