ORACLE DBA

QUERY TO GET SESSION DETAILS

SELECT * -- SID, SERIAL#, STATUS, SERVER , USERNAME
FROM V$SESSION
WHERE SID in
(
'43',
'58',
'197',
'223'
);

QUERY TO GET LONG RUNNING SESSIONS

select * from v$lock where type='TX' and lmode>0;


QUERY TO GET SID  ON A SPECIFIC DB OBJECT

select * from V$ACCESS where OBJECT='TABLE_NAME';


COMMAND TO KILL  A SESSION

ALTER SYSTEM KILL SESSION '58,29498';


COMMAND TO GET LONG RUNNING QUERIES
select sess.sid, sess.username, sql_text
from v$sqlarea sqlarea, v$session sess
where sess.sql_hash_value = sqlarea.hash_value
and   sess.sql_address    = sqlarea.address
and   sess.username is not null;


--------------------

Delete Duplicate records.

delete 
from exchange_rate f
where  f.RATE_DATE = TRUNC(SYSDATE-53)
 AND exchange_rate_id not in (
  select min(exchange_rate_id)
  from   exchange_rate s
  where  f.FROM_CURRENCY = s.FROM_CURRENCY
  and    f.TO_CURRENCY = s.TO_CURRENCY
  and    f.RATE_DATE = s.RATE_DATE
);

Check the existing duplicate records:

select * from exchange_rate
where  (FROM_CURRENCY, TO_CURRENCY,RATE_DATE) in (
  select FROM_CURRENCY, TO_CURRENCY,RATE_DATE
  from   exchange_rate
  WHERE RATE_DATE > TRUNC(SYSDATE-4)
  group  by FROM_CURRENCY, TO_CURRENCY,RATE_DATE
  having count(*) > 1
);