Development DBA cheat sheet

-- Finding a session information

select s.machine  as "Client Machine",

       s.osuser   as "Client OS User",

       s.program  as "Client Program",

       s.username as "Database User",

       p.spid     as "Server Process ID"

  from v$session s,

       v$process p

 where s.sid     = 358

   and s.serial# = 560

   and p.addr    = s.paddr;

-- Finding a session information, RAC 

select s.machine       as "Client Machine",

       s.osuser        as "Client OS User",

       s.program       as "Client Program",

       s.username      as "Database User",

       p.spid          as "Server Process ID",

       i.instance_name as "Database Instance",

       i.host_name     as "Server Hostname"

  from gv$session  s,

       gv$process  p,

       gv$instance i

 where s.sid     = 269

   and s.serial# = 6360

   and p.addr    = s.paddr

   and p.inst_id = s.inst_id

   and i.inst_id = s.inst_id;

-- Finding blocking sessions

select blocker.sid      as "Blocking SID",

       blocker.serial#  as "Blocking Serial#",

       blocker.username as "Blocking User",

       waiter.sid       as "Waiting SID",

       waiter.serial#   as "Waiting Serial#",

       waiter.username  as "Waiting User"

  from v$session blocker,

       v$session waiter

 where waiter.blocking_session = blocker.sid

   and waiter.blocking_session_status = 'VALID';

-- Get all remote sessions
select INST_ID, SID, SERIAL#, USERNAME, OSUSER, PROGRAM, MACHINE 

  from gv$session 

 where MACHINE not in (select HOST_NAME from gv$instance);

-- Killing a session

alter system kill session 'sid,serial#';

-- Killing a session, RAC

alter system kill session 'sid,serial#,@inst_id';

-- Finding current session's PID

select distinct sid from v$mystat;

select sys_context('USERENV','SID') from dual;

-- Tracing current session

alter session set timed_statistics=true;

alter session set max_dump_file_size=unlimited;

alter session set sql_trace=true;

-- Stop tracing

alter session set sql_trace=false;

-- Tracing current session, another method

exec dbms_monitor.session_trace_enable(binds => true);

-- Stop tracing

exec dbms_monitor.session_trace_disable;

-- Tracing a different session

exec dbms_monitor.session_trace_enable( session_id => 358,

                                        serial_num => 560,

                                        waits      => true,

                                        binds      => true);

-- Stop tracing

exec dbms_monitor.session_trace_disable(358, 560);

-- Tracing a user's session using trigger

create or replace trigger set_user_trace

 after logon on database

 when (user = 'MURTY')

begin

  execute immediate 'alter session set timed_statistics=true';

  execute immediate 'alter session set max_dump_file_size=unlimited';

  execute immediate 'alter session set sql_trace=true';

end;

/

drop trigger set_user_trace;

-- Setting a session identifier

exec dbms_session.set_identifier('Trace_Me');

-- Finding SID, SERIAL# using a session identifier

select SID, SERIAL# from v$session where CLIENT_IDENTIFIER = 'Trace_Me';

-- Tracing using a session identifier

exec dbms_monitor.client_id_trace_enable(client_id => 'Trace_Me', waits => true, binds => true);

-- Stop tracing

exec dbms_monitor.client_id_trace_disable(client_id => 'Trace_Me');

--Finding trace file name and path using session ID, RAC & Non-RAC

select i.HOST_NAME as HOSTNAME,

       pa.VALUE || '/' || i.INSTANCE_NAME ||  '_ora_' || pr.SPID || '.trc' as TRACE_FILE

  from gv$session   s,

       gv$process   pr,

       gv$parameter pa,

       gv$instance  i

 where s.SID = 358 and s.SERIAL# = 560 -- s.CLIENT_IDENTIFIER = 'Trace_Me'

  and i.INST_ID   = s.INST_ID

  and pa.INST_ID  = s.INST_ID

  and pa.NAME     = 'user_dump_dest'

  and pr.INST_ID  = s.INST_ID

  and pr.ADDR     = s.PADDR;

-- Explain plan for a query

explain plan for select * from tab1;

select * from table(dbms_xplan.display);

-- Explain plan for just executed query (need to be serveroutput off)

set serveroutput off;

select * from tab1;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Consolidating trace files

Using SID.SERIAL#

 $ trcsess output=a.trc session=358.560 mydb_ora_555.trc mydb_ora_666.trc

Using Client ID

 $ trcsess output=a.trc clientid=Trace_Me mydb_ora_555.trc mydb_ora_666.trc

Wild card in input files

 $ trcsess output=a.trc session=358.560 mydb*

Using tkprof

Recording SQLs

 $ tkprof a.trc record=rec.sql output=tk.txt

Output with execution plan

 $ tkprof a.trc explain=scott/tiger@mydb output=tk.txt