How to Identify Deadlock Object

You can get trace file from the alert.log

Example trace file 

Deadlock graph:
                                                       ---------Blocker(s)--------           ---------Waiter(s)---------
Resource Name                        process  session   holds waits  process session   holds waits
TX-00450014-00012495                 78    1213     X                       216         92      X
TX-0044000b-00013342               216        92     X                         78     1213       X


Rows waited on:
  Session 1213: obj - rowid = 0005DD13 - AABd0TAAXAAARF5AAE
  (dictionary objn - 384275, file - 23, block - 70009, slot - 4)
  Session 92: obj - rowid = 0005DD5F - AABd1fAAJAAAMlRABP
  (dictionary objn - 384351, file - 9, block - 51537, slot - 79)



SELECT owner, object_name, object_type 
FROM dba_objects 
WHERE object_id = 384275;

SELECT * 
FROM owner.object name  <---- from above query
WHERE rowid = 'AABd0TAAXAAARF5AAE';

SQL to find reference constraint to specific table

SELECT TABLE_NAME, CONSTRAINT_NAME, STATUS, OWNER
FROM ALL_CONSTRAINTS
WHERE R_OWNER = 'SCOTT'
AND CONSTRAINT_TYPE = 'R'
AND R_CONSTRAINT_NAME IN
(
SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P', 'U')
AND TABLE_NAME = 'SCOTT'
AND OWNER = 'EMPLOYEE'
)
ORDER BY TABLE_NAME, CONSTRAINT_NAME

EXPDP Command in Action

Create dump directory and give grants

 CREATE OR REPLACE DIRECTORY DUMP_DIRAS '/u02/live_dump'
 GRANT READ, WRITE ON DIRECTORY importme TO dba_user;


Export Full Schema

expdp directory=DUMP_DIRE  dumpfile=scott_test.dmp logfile=scott_test.log schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"


Export Tables

expdp directory=DUMP_DIRE  dumpfile=scott_test_table.dmp logfile=scott_test_table.log tables=scott.enployees flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"


Export Schema without specific tables

expdp directory=DUMP_DIRE  dumpfile=scott_test.dmp logfile=scott_test.log schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\" EXCLUDE=TABLE:\"LIKE \'AUDIT%\'\"

Get dump file size Estimate

expdp directory=DUMP_DIRE  schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\" ESTIMATE_ONLY=Y


Export data with where condition 

create file as backup.par with following details

DIRECTORY= DUMP_DIRE
DUMPFILE= scott_table.dmp
LOGFILE= scott_table.log
TABLES=scott_employee
QUERY="where JOB_TYPE = 'ACC' and SALARY >= 10000"
EXCLUDE=statistics

then execute expdp command

expdp parfile=backup.par

Gather Database Statistics

Database Stat Gather
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

Schema stat gather
EXEC DBMS_STATS.gather_schema_stats('TEST');
EXEC DBMS_STATS.gather_schema_stats('TEST', estimate_percent => 15);

Table Stat gather
EXEC DBMS_STATS.gather_table_stats('TEST', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('TEST', 'EMPLOYEES', estimate_percent => 15);

Index Stat gather
EXEC DBMS_STATS.gather_index_stats('TEST', 'EMPLOYEES_IDX_01');
EXEC DBMS_STATS.gather_index_stats('TEST', 'EMPLOYEES_IDX_01', estimate_percent => 15);

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;

PROCEDURE to send email

This Procedure have two input parameters for email body.


CREATE OR REPLACE PROCEDURE "SEND_EMAIL"(
    p_message_body IN VARCHAR2,
    p_recipient1   IN VARCHAR2 DEFAULT 'saman@test.com',
    p_recipient2   IN VARCHAR2 DEFAULT 'kamal@test.com',
    p_sender       IN VARCHAR2 DEFAULT 'oracle_database@test.com',
    p_subject      IN VARCHAR2 DEFAULT 'PRODUCTION DATABASE EMAIL',
    p_cc           IN VARCHAR2 DEFAULT 'nimal@test.com',
    p_mailport     IN NUMBER DEFAULT 25,
    p_mailhost     IN VARCHAR2 DEFAULT 'localhost' )
IS
  l_mail_conn UTL_SMTP.connection;
  crlf      CHAR(2) := CHR(13)||CHR(10);
  l_message VARCHAR2(32767);
BEGIN
  l_message   := 'Date: '||TO_CHAR(sysdate,'dd Mon yy hh24:mi:ss')||crlf;
  l_message   := l_message||'From: '||p_sender||crlf;
  l_message   := l_message||'To: '||p_recipient1||';'||p_recipient2||';'||crlf;
  l_message   := l_message||'CC: '||p_cc||crlf;
  l_message   := l_message||'Subject: '||p_subject||crlf;
  l_message   := l_message||crlf||p_message_body;
  l_mail_conn := utl_smtp.open_connection(p_mailhost, p_mailport);
  utl_smtp.helo(l_mail_conn, p_mailhost);
  utl_smtp.mail(l_mail_conn, p_sender);
  utl_smtp.rcpt(l_mail_conn, p_recipient1);
  utl_smtp.rcpt(l_mail_conn, p_recipient2);
  utl_smtp.rcpt(l_mail_conn, p_recipient3);
  utl_smtp.rcpt(l_mail_conn, p_recipient4);
  utl_smtp.rcpt(l_mail_conn, p_cc);
  utl_smtp.data(l_mail_conn, l_message);
  utl_smtp.quit(l_mail_conn);
EXCEPTION
WHEN OTHERS THEN
  raise_application_error( -20000,'Failed to send mail due to the following error: ' ||crlf|| sqlerrm);
END;

Rename Datafile Name / Change Datafile path ( Oracle)

Create Tablespace as EXAMPLE_DATA01

CREATE TABLESPACE EXAMPLE_DATA01 DATAFILE '/u01/app/oracle/oradata/EXAMPLE_DATA01_01.dbf' SIZE 10M AUTOEXTEND ON MAXSIZE 20m;

--Check datafiles current path
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/system01.dbf
/u01/app/oracle/oradata/sysaux01.dbf
/u01/app/oracle/oradata/undotbs01.dbf
/u01/app/oracle/oradata/users01.dbf
/u01/app/oracle/oradata/EXAMPLE_DATA01_01.dbf


ALTER TABLESPACE EXAMPLE_DATA01 OFFLINE NORMAL;

--From File system change copy datafile to new path
mv /u01/app/oracle/oradata/EXAMPLE_DATA01_01.dbf /u01/app/oracle/oradata/newdata/EXAMPLE_DATA01_01.dbf

ALTER TABLESPACE EXAMPLE_DATA01 RENAME DATAFILE '/u01/app/oracle/oradata/EXAMPLE_DATA01_01.dbf' TO '/u01/app/oracle/oradata/newdata/EXAMPLE_DATA01_01.dbf';

ALTER TABLESPACE EXAMPLE_DATA01 online;

How to Update One table from Another tables data which have join

We want to update First table TICKET_ID from second table TICKET_ID where TICKET_NAME = 'NEW',
we should have to update first table ( ID ) matching data to second table (U_ID).

CREATE TABLE TABLE1
(ID,F_NAME,L_NAME,TICKET_ID);

CREATE TABLE TABLE2
(T_ID,TICKET_ID,U_ID,TICKET_NAME);


SET SERVEROUTPUT ON
DECLARE
  CURSOR C1
  IS
    SELECT A.ID,
      A.TICKET_ID
    FROM TABLE2 A,
      WHERE A.TICKET_NAME = 'NEW';
COUNT_UP NUMBER;
BEGIN
  BEGIN
   COUNT_UP := 0;
    FOR C2 IN C1
    LOOP
      UPDATE TABLE1
      SET TICKET_ID   = C2.TICKET_ID
      WHERE ID = C2.U_ID;
      COUNT_UP := COUNT_UP + 1;
    END LOOP;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  DBMS_OUTPUT.PUT_LINE('Number of Record Update : '||COUNT_UP);
END;