Useful Scripts

To Check date and Time in SQL:

SQL> select to_char (sysdate,'DD-MM-YY HH24:MI') from dual;
================================
To check how many rows in a table at specific time:

SQL> select count(*) from scott.dept as of timestamp to_timestamp ('18-09-15 01:57','DD-MM-YY HH24:MI')
==========================
-- Show blocking sessions
-- Blocking sessions occur when an insert, update, delete is being issued and a commit has not been performed, this locks the row and prevents other users from making any changes to it.

SELECT blocking_session
, username
, sid
, serial#
, wait_class
, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;

=================================

-- Find the sql running behind an SID in Oracle Database

select a.sid, a.serial#, b.sql_text
   from v$session a, v$sqlarea b
    where a.sql_address=b.address
     and a.sid = &sid;

================================

-- you can display CPU for any Oracle user session with this script:

select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
===============================

-- This script will display OS busy statistics

select
   'OS Busy Time' series, to_char(snaptime, 'yyyy-mm-dd hh24 hh24:mi') snap_time,
   round(busydelta / (busydelta + idledelta) * 100, 2) "CPU Use (%)"
from
   (select
      s.begin_interval_time snaptime,
      os1.value - lag(os1.value) over (order by s.snap_id) busydelta,
      os2.value - lag(os2.value) over (order by s.snap_id) idledelta
   from
      dba_hist_snapshot s,
      dba_hist_osstat os1,
      dba_hist_osstat os2
   where
   s.snap_id = os1.snap_id
   and
   s.snap_id = os2.snap_id
   and
   s.instance_number = os1.instance_number
   and
   s.instance_number = os2.instance_number
   and
   s.dbid = os1.dbid and s.dbid = os2.dbid
   and
   s.instance_number = (select instance_number from v$instance)
   and
   s.dbid = (select dbid from v$database)
   and
   os1.stat_name = 'BUSY_TIME'
   and
   os2.stat_name = 'IDLE_TIME')

================================