mylearning4oracle

Execute the following query to determine who is using a particular UNDO or Rollback Segment: ============================================================================================= SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,         NVL(s.username, 'None') orauser,         s.program,         r.name undoseg,         t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"    FROM sys.v_$rollname    r,         sys.v_$session     s,         sys.v_$transaction t,         sys.v_$parameter   x  WHERE s.taddr = t.addr      AND r.usn   = t.xidusn(+)     AND x.name  = 'db_block_size'  Execute the following query to determine who is using a TEMP Segment: ======================================...

select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"  from sys.v_$datafile  where to_char(creation_time,'RRRR')='2017'  group by to_char(creation_time, 'MM-RRRR')  order by  to_char(creation_time, 'MM-RRRR');  select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb   from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb  from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb  from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts#  AND tsu.snap_id = sp.snap_id  AND ts.tsname = dt.tablespace_na...

#!/usr/bin/ksh ## Description : This script will go through each and every ASM disk and validate Queue_Depth,Reserve_Policy,pvid and also provides the basic disk information. function asm_details { cd /home/oracle/ export SID=`ps -ef | grep pmon | grep ASM | awk -Fpmon_ '{print $2}'` export ORACLE_SID=$SID export ORACLE_HOME=`grep $SID /etc/oratab | cut -d: -f2` export PATH=$ORACLE_HOME/bin:$PATH ct=$(date "+%Y.%m.%d-%H.%M.%S") $ORACLE_HOME/bin/kfod status=TRUE disk=all dscvgroup=TRUE > Disk_details_$ct export f_name=Disk_details_$ct printf "=======================================================================================================================\n" > asm_disk_details_$ct printf "%-22s | %-10s |%-15s | %-10s |%-11s | %-10s |%-4s | %-12s | %-10s\n "  Disk_Name Perm Owner size-MB Status DG Q_Depth Res_Policy pvid>> asm_disk_details_$ct printf "==============================================================...

ASH analysis : Who is blocking the SQL between start time and end time. @ash_wait_chains username||':'||program2||event2 sql_id='3rtbs9vqukc71' "timestamp'2013-10-05 01:00:00'" "timestamp'2013-10-05 03:00:00'"           -- Usage:      --     @ash_wait_chains <grouping_cols> <filters> <fromtime> <totime> -- -- Example: --     @ash_wait_chains username||':'||program2||event2 session_type='FOREGROUND' sysdate-1/24 sysdate -- -- Other: --     This script uses only the in-memory V$ACTIVE_SESSION_HISTORY, use --     @dash_wait_chains.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive -- --     Oracle 10g does not  have the BLOCKING_INST_ID column in ASH so you'll need --     to comment out this column in this script. This may give you somewhat --     incorrect results in RAC environment with ...

Top of Form 1 ASH, awr, Performance Tuning, scripts Top Recent Wait Events col EVENT format a60 select * from ( select active_session_history.event, sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history where active_session_history.event is not null group by active_session_history.event order by 2 desc) where rownum < 6 / Top Wait Events Since Instance Startup col event format a60 select event, total_waits, time_waited from v$system_event e, v$event_name n where n.event_id = e.event_id and n.wait_class !='Idle' and n.wait_class = (select wait_class from v$session_wait_class  where wait_class !='Idle'  group by wait_class having sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class where wait_class !='Idle' group by (wait_class))) order by 3; List Of Users Currently Waiting col username fo...