weird-java-blog

[weird-java-blog]

Useful Oracle admin queries

Get all  BLOCKING transactions and their sessions of particular LOCK TYPE:

SELECT distinct xidusn, xidslot, xidsqn, lll.sid, "SERIAL#", lll.BLOCK, lll.lmode, lll.request from v$transaction left join v$lock lll

on type='TX' and ID1=xidusn*power(2,16)+xidslot and id2=xidsqn and block=1

left join v$session sess on sess.sid=lll.sid

order by xidusn, xidslot, xidsqn

Get SQL queries for all sessions:

select s.sql_text, t.*

from v$session t, v$sql s

where t.sql_id = s.sql_id

Get all objects locked by sessions:

select * from v$lock l left join all_objects on id1=object_id left join v$session s on s.sid=l.sid

Get all lock types for this Oracle version:

select * from v$lock_type

Kill session:

alter system kill session 'sid,serial#' immediate;

Both parameters you can get from queries above.

Get sql queries for all inactive sessions:

select * from V$sqltext_With_Newlines t, V$session s where t.ADDRESS =  s.SQL_ADDRESS and t.HASH_VALUE = s.SQL_HASH_VALUE and s.STATUS = 'INACTIVE'

Changing trace file name before enabling:

alter system set tracefile_identifier='identifier';

Enabling full trace:

BEGIN DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits => TRUE, binds => TRUE, instance_name => 'instanceName');  END;

All trace files will be saved here:

select * from v$parameter pr where pr.NAME like('%user_dump_dest%')

[weird-java-blog]

Schrodinger's variable

What do you think this snippet of code prints?

public static void main(String[] args) {         System.out.println(test()); } public static int test(){         int i = 0;         try{                 i = 2;                 return i;         } finally{                 i = 12;                 System.out.println("finally block " + i);         } }

Code prints:

finally block 12 2

So, according to sysout, firstly i = 2, then finally block executes, assigns i = 12, prints “finally block 12″ and then try block returns i = 2. Actually, finally won't update the value begin returned to the caller, because that value has already being placed in another part of memory. Let’s try to use return in finally block:

public static void main(String[] args) {      System.out.println(test()); } public static int test(){       int i = 0;       try{              i = 2;              return i;       } finally{              System.out.println("finally block " + i);              return 12;       } }

Code prints:

finally block 2 12

Here return actually changes return value. although it’s very bad style.