This script reports the tablespace quota allocated for each database user and the amount of tablespace consumed by that user. TABLESPACE QUOTA DETAILS OF ALL THE USERS: set pagesize 200 set lines 200 col ownr format a20 justify c heading ‘Owner’ col name format a20 justify c heading ‘Tablespace’ trunc col qota format a12 justify […]

Use below query to monitor currently running queries with parallel threads. col username for a9 col sid for a8 set lines 299 select s.inst_id, decode(px.qcinst_id,NULL,s.username, ‘ – ‘||lower(substr(s.program,length(s.program)-4,4) ) ) “Username”, decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” , to_char( px.server_set) “Slave Set”, to_char(s.sid) “SID”, decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) “QC SID”, px.req_degree “Requested DOP”, px.degree “Actual DOP”, p.spid […]

Below script will display information about the distributed pending transactions in oracle. COL local_tran_id FORMAT a13 COL in_out FORMAT a6 COL database FORMAT a25 COL dbuser_owner FORMAT a15 COL interface FORMAT a3 SELECT local_tran_id, in_out, database, dbuser_owner, interface FROM dba_2pc_neighbors /

Below script will display execution history of an sql_id from AWR. It will join dba_hist_sqlstat and dba_hist_sqlsnapshot table to get the required information. select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,’dd-mon-yy hh24:mi’) btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes, executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) “avg duration (sec)” from dba_hist_SQLSTAT a, dba_hist_snapshot b where sql_id=’&sql_id’ […]

Below script will give information about the CPU usage and wait events class information of every minute for last 2 hours. As this query uses gv$active_session_history, so make sure you have TUNING license pack of oracle, before using this. set lines 288 col sample_time for a14 col CONFIGURATION head “CONFIG” for 99.99 col ADMINISTRATIVE head […]

Below script is useful in getting CPU, memory and core, socket information of a database server from SQL prompt. SCRIPT: set pagesize 299 set lines 299 select STAT_NAME,to_char(VALUE) as VALUE ,COMMENTS from v$osstat where stat_name IN (‘NUM_CPUS’,’NUM_CPU_CORES’,’NUM_CPU_SOCKETS’) union select STAT_NAME,VALUE/1024/1024/1024 || ‘ GB’ ,COMMENTS from v$osstat where stat_name IN (‘PHYSICAL_MEMORY_BYTES’) OUTPUT: STAT_NAME VALUE COMMENTS ——————————– […]

Below script is useful in getting user commit statistics information in the oracle database. user commits is the number of commits happening the database. It will be helpful in tracking the number of transactions in the database.   col STAT_NAME for a20 col VALUE_DIFF for 9999,999,999 col STAT_PER_MIN for 9999,999,999 set lines 200 pages 1500 […]

Below script can be used to find the active transactions in the oracle database. col name format a10 col username format a8 col osuser format a8 col start_time format a17 col status format a12 tti ‘Active transactions’ select s.sid,username,t.start_time, r.name, t.used_ublk “USED BLKS”, decode(t.space, ‘YES’, ‘SPACE TX’, decode(t.recursive, ‘YES’, ‘RECURSIVE TX’, decode(t.noundo, ‘YES’, ‘NO UNDO […]

Use below query to find the sessions using a lot of CPU. col program form a30 heading “Program” col CPUMins form 99990 heading “CPU in Mins” select rownum as rank, a.* from ( SELECT v.sid, program, v.value / (100 * 60) CPUMins FROM v$statname s , v$sesstat v, v$session sess WHERE s.name = ‘CPU used […]

Below is the script to find the respective sid of an server proccess id. col sid format 999999  col username format a20  col osuser format a15  select b.spid,a.sid, a.serial#,a.username, a.osuser  from v$session a, v$process b  where a.paddr= b.addr  and b.spid=’&spid’  order by b.spid; SEE ALSO – Get ospid from sid