Finding Operating Process With Oracle Process across by Session ID.

========== Query =========

set line 200
col sid format 999999
col username format a20
col osuser format a15
col PROGRAM format a15
col terminal format a15
col module format a15

select p.spid os_process_id,
       s.sid session_id,
       s.serial#,
       s.username,
       s.status,
       s.last_call_et,
       p.program,
       p.terminal,
       logon_time,
       module,
       s.osuser
from v$session s, v$process p
where s.paddr= p.addr
and p.spid='&spid'
order by p.spid;

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

SQL> set line 200
col sid format 999999
col username format a20
col osuser format a15
col PROGRAM format a15
col terminal format a15
col module format a15
SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> select p.spid os_process_id,
  2         s.sid session_id,
  3         s.serial#,
  4         s.username,
  5         s.status,
  6         s.last_call_et,
  7         p.program,
  8         p.terminal,
  9         logon_time,
 10         module,
 11         s.osuser
 12  from v$session s, v$process p
 13  where s.paddr= p.addr
 14  and p.spid='&spid'
 15  order by p.spid;
Enter value for spid: 11403790
old  14: and p.spid='&spid'
new  14: and p.spid='11403790'

OS_PROCESS_ID            SESSION_ID    SERIAL# USERNAME             STATUS   LAST_CALL_ET PROGRAM         TERMINAL        LOGON_TIM MODULE          OSUSER
------------------------ ---------- ---------- -------------------- -------- ------------ --------------- --------------- --------- --------------- ---------------
11403790                       4574      28565 PRODUSR              ACTIVE             39 oracle@DBSRV1    UNKNOWN         22-JUL-15 JDBC Thin Client


SQL>

Find the session and SQL query which was previously locked.

This Query will help you to find lock history.

SQL> SELECT  DISTINCT A.INST_ID, A.SQL_ID ,A.INST_ID,A.BLOCKING_SESSION,A.BLOCKING_SESSION_SERIAL#,A.USER_ID,S.SQL_TEXT,A.MODULE
  2  FROM  GV$ACTIVE_SESSION_HISTORY A  ,GV$SQL S
  3  WHERE A.SQL_ID=S.SQL_ID
  4  AND BLOCKING_SESSION IS NOT NULL
  5  AND A.USER_ID <> 0;

   INST_ID SQL_ID           INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL#    USER_ID SQL_TEXT
---------- ------------- ---------- ---------------- ------------------------ ---------- -------------------------------------------------------
         1 0jgf8xdjf2h8w          1               74                        9         84  UPDATE REGIONS  SET REGION_NAME='TEST'

SQL>

Note : It will show data if your current log is enough.

Finding Lock In Oracle Database using SQL Statements.

1. Find All Object Which are Currently Locked By Session.

SELECT OBJECT_NAME,S.INST_ID,S.SID,S.SERIAL#,S.OSUSER,S.SERVER,S.MACHINE, S.STATUS,P.PNAME,SQL_TEXT, SQL_FULLTEXT
FROM GV$LOCKED_OBJECT L,GV$SESSION S,GV$PROCESS P,DBA_OBJECTS O, GV$SQLAREA T
WHERE L.OBJECT_ID=O.OBJECT_ID
AND S.SQL_ADDRESS =T.ADDRESS
AND S.SQL_HASH_VALUE =T.HASH_VALUE
AND L.SESSION_ID=S.SID
AND S.PADDR=P.ADDR;

2.  Lists all DML locks and all outstanding requests for a DML lock.

SELECT * FROM DBA_DML_LOCKS;

3. Holding a lock on an object for which another session is waiting.

SELECT * FROM DBA_BLOCKERS;

4. Blocking Session Details.

SELECT BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS,SECONDS_IN_WAIT
FROM  V$SESSION
WHERE  BLOCKING_SESSION IS NOT NULL
ORDER BY BLOCKING_SESSION;

Finding SQL Statements History in RAC/Single Instance Database.

1. Session Wise Latest Running SQL Statement.

SELECT T.INST_ID,S.USERNAME, S.SID, S.SERIAL#,T.SQL_ID,T.SQL_TEXT "Last SQL"
FROM GV$SESSION S, GV$SQLAREA T
WHERE S.SQL_ADDRESS =T.ADDRESS
AND S.SQL_HASH_VALUE =T.HASH_VALUE;

2. Currently Executing SQL Statements.

SELECT I.INST_ID,S.SID,SERIAL#,A.SQL_ID,S.EVENT,A.SQL_TEXT,A.SQL_FULLTEXT,S.USERNAME,I.HOST_NAME,MACHINE,S.SECONDS_IN_WAIT SEC_WAIT,
TO_CHAR(LOGON_TIME,'DD-MON-RR HH24:MI') "Session Login Time"
FROM GV$SESSION S,GV$SQLAREA A,GV$INSTANCE I
WHERE S.USERNAME IS NOT NULL
AND S.STATUS='ACTIVE'
AND S.SQL_ADDRESS=A.ADDRESS
AND S.INST_ID=A.INST_ID
AND I.INST_ID = A.INST_ID
AND SQL_TEXT NOT LIKE 'SELECT SID,SERIAL#,A.SQL_ID,A.SQL_TEXT%';

3. Currently Waiting Session With SQL Statements.

SELECT W.SID, W.EVENT, W.WAIT_CLASS_ID, W.WAIT_CLASS, W.WAIT_TIME, W.SECONDS_IN_WAIT, W.WAIT_TIME_MICRO, Q.SQL_ID, Q.PLAN_HASH_VALUE,
SQL_TEXT, SQL_FULLTEXT
FROM V$SESSION_WAIT W, V$SESSION S, V$SQL Q
WHERE W.SID=S.SID
AND S.SQL_ID=Q.SQL_ID
AND W.WAIT_TIME =0
AND W.EVENT<>'SQL*Net message from client'
AND W.WAIT_CLASS<>'Idle'
ORDER BY W.SID;

Note : 

W.WAIT_TIME > 0 - Value is the duration of the last wait in hundredths of a second
W.WAIT_TIME =-1 - Duration of the last wait was less than a hundredth of a second
W.WAIT_TIME =-2 - Parameter TIMED_STATISTICS was set to false

4. Latest Running SQL Statements.

SELECT H.INST_ID,H.SAMPLE_TIME,H.SESSION_ID,H.SESSION_SERIAL#,H.SQL_ID
FROM GV$ACTIVE_SESSION_HISTORY H
WHERE H.SQL_ID IS NOT NULL
ORDER BY 1 DESC;

5. Long Time Running SQL Statements.

SELECT SID,OPNAME,SQL_ID, SQL_PLAN_HASH_VALUE, SQL_EXEC_START,TARGET,ROUND(SOFAR/TOTALWORK*100,2) "Percent Done" ,
START_TIME,LAST_UPDATE_TIME,TIME_REMAINING
FROM V$SESSION_LONGOPS;

6. Active Session Running More Then 30 Min.

SELECT USERNAME,MACHINE,INST_ID,SID,SERIAL#,PROGRAM,TO_CHAR(LOGON_TIME,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) MINUTES_LOG_ON,ROUND(LAST_CALL_ET/60,1) MINUTES_CURRENT_SQL
FROM GV$SESSION
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL
AND ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 30
ORDER BY MINUTES_LOG_ON DESC;

7. Active Transaction Details with SQL ID.

SELECT SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID,T.STATUS, START_TIME,USERNAME, T.USED_UBLK, T.USED_UREC
  FROM V$TRANSACTION T, V$SESSION S
 WHERE T.ADDR = S.TADDR;

Finding INSERT, UPDATE, DELETE rows in table from oracle database.

EXECUTE DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

This procedure flushes in-memory monitoring information for all tables in the dictionary. Corresponding entries in the *_TAB_MODIFICATIONS views are updated immediately, without waiting for the Oracle database to flush them periodically.


  SELECT TABLE_OWNER,
         TABLE_NAME,
         INSERTS,
         UPDATES,
         DELETES,
         TRUNCATED,
         (CASE WHEN DROP_SEGMENTS = 1 THEN 'YES' ELSE 'NO' END) DROPED,
         TIMESTAMP
    FROM DBA_TAB_MODIFICATIONS
   WHERE TABLE_OWNER = USER
ORDER BY TABLE_NAME

Export/Generat user scripts in physical directory in Oracle Database.

In this article I am try to export user scripts in my physical directory. I have use an external table for maintain schema which I want to export.  


--- CREATING DIRECTORY FOR WRITE FILE

CREATE OR REPLACE DIRECTORY
DATA_DIR AS
'D:\DUMP\';

--- GRANT READ AND WRITE PERMITION TO SPECIFIC USER

GRANT READ, WRITE ON DIRECTIORY DATA_DIR TO TEST_USER;

-- CREATING EXTERNAL TABLE FOR COLLECT USER INFORMATION

CREATE TABLE EXT_TABLE
(
   USER_NAME         VARCHAR2 (200),
   REPLACE_ALLOWED   VARCHAR2 (10),
   COMMENTS          VARCHAR2 (300)
)
ORGANIZATION EXTERNAL
    (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
LOGFILE 'data.log'
BADFILE 'data.txt'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION('USER_REPLACE.csv')
)
REJECT LIMIT UNLIMITED;

-- CREATING PROCEDURE TO WRITE USER SCRIPTS

CREATE OR REPLACE PROCEDURE SP_USER_PRIVILEGES_SCRIPTS
(P_DIRECTORY VARCHAR2,P_FILE_NAME VARCHAR2)
IS
  V_CHNK_SIZE PLS_INTEGER;
  V_CLOB_USER CLOB;
  V_CLOB_DATA CLOB;
  V_OUTPUT UTL_FILE.FILE_TYPE;
  V_FILE_NAME VARCHAR2(1000);
 
  PROCEDURE USER_FILE_WRITE(P_WRITE_TAG VARCHAR2) IS
      BEGIN
        V_FILE_NAME:=P_FILE_NAME||'_'||P_WRITE_TAG||'.SQL';
        V_CLOB_DATA:=NULL;
       
        FOR INDUSR IN (SELECT USER_NAME  FROM EXT_TABLE WHERE REPLACE_ALLOWED='Y') LOOP

         BEGIN
            SELECT REPLACE(REPLACE(DBMS_METADATA.GET_DDL(P_WRITE_TAG, INDUSR.USER_NAME),'"',' '),'
 ','')||';'  INTO V_CLOB_USER
            FROM DUAL;
          END;
          V_CLOB_DATA:=V_CLOB_DATA||V_CLOB_USER;
         END LOOP;
        
          V_OUTPUT := UTL_FILE.FOPEN( P_DIRECTORY, V_FILE_NAME, 'wb', MAX_LINESIZE => 32767 );

          V_CHNK_SIZE := 3000;
          FOR I IN 1 .. CEIL( LENGTH( V_CLOB_DATA ) / V_CHNK_SIZE )
          LOOP
           
            UTL_FILE.PUT_RAW( V_OUTPUT, UTL_RAW.CAST_TO_RAW( SUBSTR( V_CLOB_DATA, ( I - 1 ) * V_CHNK_SIZE + 1, V_CHNK_SIZE ) ) );
             UTL_FILE.FFLUSH(V_OUTPUT);
          END LOOP;
       
          UTL_FILE.FCLOSE(V_OUTPUT);
     END;
    
   PROCEDURE GRANT_FILE_WRITE(P_WRITE_TAG VARCHAR2) IS
  BEGIN
    V_FILE_NAME:=P_FILE_NAME||'_'||P_WRITE_TAG||'.SQL';
    V_CLOB_DATA:=NULL;
 BEGIN
        FOR INDUSR IN (SELECT USER_NAME  FROM EXT_TABLE WHERE REPLACE_ALLOWED='Y') LOOP

         BEGIN
            SELECT REPLACE(REPLACE(REPLACE(DBMS_METADATA.GET_GRANTED_DDL(P_WRITE_TAG, INDUSR.USER_NAME),'"',' '),'
 ',';'),' . ','.')  INTO V_CLOB_USER
            FROM DUAL;
          END;
          V_CLOB_DATA:=V_CLOB_DATA||V_CLOB_USER;
         END LOOP;
  END;
  V_OUTPUT := UTL_FILE.FOPEN( P_DIRECTORY, V_FILE_NAME, 'wb', MAX_LINESIZE => 32767 );

  V_CHNK_SIZE := 3000;
  FOR I IN 1 .. CEIL( LENGTH( V_CLOB_DATA ) / V_CHNK_SIZE )
  LOOP
   
    UTL_FILE.PUT_RAW( V_OUTPUT, UTL_RAW.CAST_TO_RAW( SUBSTR( V_CLOB_DATA, ( I - 1 ) * V_CHNK_SIZE + 1, V_CHNK_SIZE ) ) );
    UTL_FILE.FFLUSH(V_OUTPUT);
  END LOOP;
  UTL_FILE.FCLOSE(V_OUTPUT);
 END;

 BEGIN

  USER_FILE_WRITE('USER');
  GRANT_FILE_WRITE('ROLE_GRANT');
  GRANT_FILE_WRITE('SYSTEM_GRANT');
  GRANT_FILE_WRITE('OBJECT_GRANT');
 
END;
/

-- EXECUTE PROCEDURE AND COLLECT SCRIPTS FROM DIRECTORIES

EXEC SP_USER_PRIVILEGES_SCRIPTS('DATA_DIR','TEST_SCRIPTS');

Oracle Table Locking Details.

When an transaction (INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE) occurred in table. Oracle database lock table. It's called DML lock. The purpose of a DML lock, also called a data lock, is to guarantee the integrity of data being accessed concurrently by multiple users.

Row Locks (TX): A row lock, also called a TX lock, is a lock on a single row of a table. A transaction acquires a row lock for each row modified by using DML( INSERT, UPDATE, DELETE, MERGE) and SELECT ... FOR UPDATE. The row lock exists until the transaction commits or rolls back.

Table Locks (TM): A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE,DELETE, MERGE, and SELECT ... FOR UPDATE. These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.

Manual Lock : User can lock table manually for interrupting access.

Example:-

SQL> CONN RND/rnd@ORCL
Connected.
SQL>
SQL> CREATE TABLE CURR
  2  (
  3    CURRENCY_CD  VARCHAR2(9 BYTE),
  4    EXCH_RT_EUR  NUMBER,
  5    EXCH_RT_USD  NUMBER
  6  );

Table created.

SQL> INSERT INTO CURR(CURRENCY_CD, EXCH_RT_EUR, EXCH_RT_USD) VALUES ('BDT',1,80);

1 row created.
------------------------------------------------------------------------------------------------------------
Now connect sys schema using another terminal. Because when you exit from current terminal session will be close and transaction will be ROLLBACK.
-----------------------------------------------------------------------------------------------------------------
SQL> CONN /AS SYSDBA
Connected.
SQL> set line 200
SQL> SELECT A.SESSION_ID,
  2         A.ORACLE_USERNAME,
  3         A.OS_USER_NAME,
  4         B.OWNER "OBJECT OWNER",
  5         B.OBJECT_NAME,
  6         B.OBJECT_TYPE,
  7         A.LOCKED_MODE
  8    FROM (SELECT OBJECT_ID,
  9                 SESSION_ID,
 10                 ORACLE_USERNAME,
 11                 OS_USER_NAME,
 12                 LOCKED_MODE
 13            FROM V$LOCKED_OBJECT) A,
 14         (SELECT OBJECT_ID,
 15                 OWNER,
 16                 OBJECT_NAME,
 17                 OBJECT_TYPE
 18            FROM DBA_OBJECTS) B
 19   WHERE A.OBJECT_ID = B.OBJECT_ID;

SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   OBJECT OWNER
---------- ------------------------------ ------------------------------ ------------------------------
OBJECT_NAME                                                                                                              OBJECT_TYPE         LOCKED_MODE
-------------------------------------------------------------------------------------------------------------------------------- ------------------- -----------
         8 RND                            SPFTL\rajib.pradhan            RND
CURR                                                                                                                     TABLE                         3


SQL>
------------------------------------------------------------------------------------------------------
Now apply commit from your previous terminal.
-------------------------------------------------------------------------------------------------------

SQL> commit;

Commit complete.

------------------------------------------------------------------------------------------------------
Now check lock object using sys terminal.
------------------------------------------------------------------------------------------------------

SQL>  SELECT A.SESSION_ID,
  2          A.ORACLE_USERNAME,
  3          A.OS_USER_NAME,
  4          B.OWNER "OBJECT OWNER",
  5          B.OBJECT_NAME,
  6          B.OBJECT_TYPE,
  7          A.LOCKED_MODE
  8     FROM (SELECT OBJECT_ID,
  9                  SESSION_ID,
 10                  ORACLE_USERNAME,
 11                  OS_USER_NAME,
 12                  LOCKED_MODE
 13             FROM V$LOCKED_OBJECT) A,
 14          (SELECT OBJECT_ID,
 15                  OWNER,
 16                  OBJECT_NAME,
 17                  OBJECT_TYPE
 18             FROM DBA_OBJECTS) B
 19    WHERE A.OBJECT_ID = B.OBJECT_ID;

no rows selected


SQL>

How to find object lock session ..

Using this query you can find all locking session


SELECT SID  FROM V$LOCK
 WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
 WHERE OWNER='RND'
 AND OBJECT_NAME='DEPT');

How to find Locked object in Oracle Database.

Hello dear, Using this example you can find out your locked object list from your database. You must to have DBA prevailed.
 

SQL> CREATE TABLE AAA(ID NUMBER);

Table created.

SQL> INSERT INTO  AAA VALUES(10);

1 row created.

SQL> SELECT A.SID,A.SERIAL#, A.USERNAME,C.OS_USER_NAME,A.TERMINAL,
  2  B.OBJECT_ID,B.OBJECT_NAME OBJECT_NAME
  3  FROM V$SESSION A, DBA_OBJECTS B, V$LOCKED_OBJECT C
  4  WHERE A.SID = C.SESSION_ID
  5  AND B.OBJECT_ID = C.OBJECT_ID;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
OS_USER_NAME                   TERMINAL          OBJECT_ID
------------------------------ ---------------- ----------
OBJECT_NAME
--------------------------------------------------------------------------------
       195          7 RND
SPFTL\rajib.pradhan            N1020                215166
AAA

SQL> commit;

Commit complete.

SQL> SELECT A.SID,A.SERIAL#, A.USERNAME,C.OS_USER_NAME,A.TERMINAL,
  2  B.OBJECT_ID,B.OBJECT_NAME OBJECT_NAME
  3  FROM V$SESSION A, DBA_OBJECTS B, V$LOCKED_OBJECT C
  4  WHERE A.SID = C.SESSION_ID
  5  AND B.OBJECT_ID = C.OBJECT_ID;

no rows selected

SQL>