Delete Duplicate rows from table

From this sql you can delete duplicate rows which can identified from specific columns

DELETE
FROM table_x
WHERE rowid IN
  (SELECT MAX(rowid)
  FROM table_x
  WHERE (column_a, column_b, column_c) IN
    (SELECT column_a, column_b, column_c
    FROM
      (SELECT column_a, column_b, column_c,
        COUNT(*)
      FROM table_x
      GROUP BY column_a, column_b, column_c
      HAVING COUNT(*) > 1
      )
    )
  GROUP BY column_a, column_b, column_c

  );

How to get high load sqls which are currently running

How to get high load sqls which are currently running 

SELECT a.sid,d.spid ,  a.username, a.machine, c.consistent_gets, c.block_gets,

       c.physical_reads, round((100 * (c.consistent_gets + c.block_gets -

       c.physical_reads)) / (c.consistent_gets + c.block_gets), 2) hitratio,

       b.sql_fulltext 

    FROM v$session a, v$sqlarea b, v$sess_io c , v$process d

    WHERE a.sql_hash_value = b.hash_value

      AND a.sid = c.sid

      AND c.consistent_gets + c.block_gets > 0

      AND a.username IS NOT NULL

      AND a.status = 'ACTIVE'

      and a.paddr      = d.addr

    ORDER BY a.status ASC, c.consistent_gets DESC, c.physical_reads DESC

Drop Oracle Database

With Using RMAN utility 

$ rman tartget /

RMAN> STARTUP FORCE MOUNT
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;


With SQL Plus

Logging to sql plus 
sqlplus / as sysdba

startup mount exclusive restrict;

drop database;

How to find and kill sessions what are locking the tables

Normal Tables

Find the locking sessions from following 

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

Get Session details from this sql  
example : if session id = 421select * from V$Session where sid = 421; 

Kill Sessions from following
alter system kill session '421,7951';



Temporary Tables

Geth the out put from following sql and run the out put.

SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';' FROM V$SESSION WHERE SID IN (
SELECT SID FROM V$LOCK
WHERE id1 = (SELECT object_id FROM all_objects WHERE owner ='CMDC' AND object_name ='TMP_EQUIPSTATUS'));



Lock Objects 


select C.OWNER,
  C.OBJECT_NAME,
  C.OBJECT_TYPE,
  B.sid,
  B.SERIAL#,
  B.STATUS,
  B.OSUSER,
  B.MACHINE
from V$LOCKED_OBJECT a ,
  V$SESSION B,
  DBA_OBJECTS C
where B.sid     = a.SESSION_ID
and a.OBJECT_ID = C.OBJECT_ID;