Monthly Archives: March 2015

remove long running db session

set pages 100
col username       format a20
col “SID,SESSION#” format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# “SID,SESSION#”
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,session_id;

select ‘ALTER SYSTEM KILL SESSION ”’ || sid  || ‘,’ || serial# || ”’ IMMEDIATE;’ from v$session where sid in ( select sid from dbms_lock_allocated d,v$lock l where d.lockid = l.id1 and ctime >30);

 

set head off;
set pagesize 2000;
select ‘ALTER SYSTEM KILL SESSION ”’ || sid  || ‘,’ || serial# || ”’ IMMEDIATE;’
from v$session where sid in ( select sid from dbms_lock_allocated d,v$lock l where d.lockid = l.id1 and ctime >30);
set linesize 140
set pages 100
col username       format a20
col “SID,SESSION#” format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# “SID,SESSION#”
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,session_id;

Adding jdbc connection string for failover

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hst20w110m3)(PORT=1511))(SERVICE_NAME=PS1SID))(ADDRESS=(PROTOCOL=TCP)(HOST=hst20w114m3)(PORT=1511))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVICE_NAME=PS2SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180))))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hst20w110m3)(PORT=1511))(SERVICE_NAME=PS1SID)(ADDRESS=(PROTOCOL=TCP)(HOST=hst20w114m3)(PORT=1511))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVICE_NAME=PS2SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180))))

 

jdbc:oracle:thin:@(DESCRIPTION_LIST = (FAILOVER = yes)(LOAD_BALANCE = no)(DESCRIPTION =(LOAD_BALANCE = yes)(ADDRESS = (PROTOCOL= TCP) (HOST= uathst5w80m7) (PORT = 1611)) (CONNECT_DATA = (SERVICE_NAME = US1SID)))(DESCRIPTION =(LOAD_BALANCE = yes)(ADDRESS = (PROTOCOL= TCP) (HOST= uathst5w80m7) (PORT= 1611))(CONNECT_DATA = (SERVICE_NAME = US1SID))))
jdbc:oracle:thin:@(DESCRIPTION_LIST = (FAILOVER = yes)(LOAD_BALANCE = no)(DESCRIPTION =(LOAD_BALANCE = yes)(ADDRESS = (PROTOCOL= TCP) (HOST= hst20w110m3) (PORT = 1511)) (CONNECT_DATA = (SERVICE_NAME = PS1SID)))(DESCRIPTION =(LOAD_BALANCE = yes)(ADDRESS = (PROTOCOL= TCP) (HOST= hst20w114m3) (PORT= 1511))(CONNECT_DATA = (SERVICE_NAME = PS2SID))))
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=orcl)))

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sithst5w88m7.xyzcorp.com)(PORT =1690)))(CONNECT_DATA=(SERVICE_NAME=SS1SIDX)))
jhstc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sithst4w100m7.xyzcorp.com)(PORT =1881)))(CONNECT_DATA=(SERVICE_NAME=SR1BPMS)))