Long running SQL and verify database locks


CPU usage

 SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec" 
FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' -- CPU 
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

ORDER BY st.value;
SET ECHO OFF
COL HOURS          FORMAT 999,990.99
COL MESSAGE        FORMAT A22
COL OPNAME         FORMAT A16        HEA "OPERATION"
COL PCT_COMPLETE   FORMAT 99.9        HEA "PCT" 
COL SERIAL#        FORMAT 99999
COL SID            FORMAT 9999
COL STARTED        FORMAT A16        HEA "START TIME"
COL TARGET         FORMAT A16
COL TARGET_DESC    FORMAT A16
COL REMAIN_MIN     FORMAT 99,999,999
COL ELASPSED_MIN   FORMAT 99,999,999
COL UNITS          FORMAT A10
COL USERNAME       FORMAT A15
set linesize 120
-- SET ECHO ON
SELECT SID
--      ,SERIAL#
      ,USERNAME
      ,OPNAME
      ,TO_CHAR(START_TIME,'MM/DD HH24:MI:SS') STARTED
      , decode(totalwork, 0, 100,SOFAR/TOTALWORK*100)   PCT_COMPLETE
      ,ELAPSED_SECONDS/60       ELASPSED_MIN
--     ,TOTALWORK
--      ,UNITS
     ,TIME_REMAINING/60         REMAIN_MIN
   FROM gV$SESSION_LONGOPS
   WHERE  USERNAME != 'SYS'
--    and SOFAR/TOTALWORK*100 !=100
   and  TIME_REMAINING/60 !=0 
   ORDER BY ELAPSED_SECONDS DESC
;



Jobs

set lines 130
col LOG_USER from a15
select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN from dba_jobs;
SELECT JOB,WHAT,BROKEN,TO_CHAR(NEXT_DATE,'YY-MM-DD HH24:MI:SS') FROM DBA_JOBS;

lock

SELECT a.serial# as serial,a.sid,a.username,b.type,b.ctime,lmode,a.osuser,c.sql_text
FROM v$session a,v$lock b, v$sqlarea c
WHERE b.type in ('TM','TX','UL') and a.sid=b.sid and lmode > 0 and ((a.PREV_HASH_VALUE = c.hash_value and a.prev_sql_addr = c.address and a.sql_hash_value = 0) or (c.hash_value=a.sql_hash_value and c.address = a.sql_address))
;



sleep 30
SELECT job_name, job_class, operation, status, to_char(log_date, 'Dy DD-Mon-YYYY HH24:MI:SS')  FROM DBA_SCHEDULER_JOB_LOG where log_date>=sysdate-1 and owner in('XXX','XXX') and status!='SUCCEEDED';

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more