Blocking Session

How to find blocking session and kill the session from database


During adpatch ,deadlock will happen when some jobs depends on resource where another job its holding the resource.Using adctrl ,you can find two jobs in running state there will be no log updates.
* To find blocking session jobs below query will useful. It will return two rows.

select process,sid, blocking_session from v$session where blocking_session is not null;
E.g.
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365

* Second step to find the serial number for the Blocking Session to kill
select SERIAL# from v$session where SID=<SID number>
E.g.
SQL> select SERIAL# from v$session where SID=365;
SERIAL#
———-
130

* Final step to kill the blocking session
alter system kill session ‘SID,SERIAL#’;
E.g.
SQL> alter system kill session ’365,130′;
System altered.




What's your database version? Please show us all 5 digits of the version (select from v$version);*

In 10.1_

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;

no rows selected
In 10.2*
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;

no rows selected
 
In 11g _(showing a real lock situation)_


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.


SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;

       SID SQL_ID        USERNAME                       BLOCKING_SESSION
---------- ------------- ------------------------------ ----------------
       145 62ttrb9xf43zr SCOTT                                      26

1 row selected.
 
 

Find and resolve Blocking Session


In the life of Oracle/Oracle Apps DBA, we usually fall in the pit of blocking session. First we need to find whether any blocking session is happening by the following query:-

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

If there is any blocking session, we must check the "second_in_wait" as well as the sid which is blocking. If the session wait is higher and the its inactive for long time, then we can get rid of that session by:-

alter system kill session 'sid,serial#' immediate;
for eg: alter system kill session '1140,188' immediate;

Other Useful Queries related with Locks & sessions:-

SELECT o.object_name,
v.session_id sid,
v.oracle_username,
decode(l.type,'TM', 'DML enqueue (TM)', 'TX', 'Transaction enqueue (TX)', 'UL', 'User supplied (UL)', l.type),
decode(l.lmode,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.lmode,'990')) holding,
decode(l.request,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.request,'990')) wanting,
l.block,
s.terminal,
s.machine
FROM sys.v_$locked_object v,
sys.v_$lock l,
sys.dba_objects o,
sys.v_$session s
WHERE l.sid = v.session_id
AND s.sid = v.session_id
AND v.object_id = o.object_id;

**********************************************************
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held, s.event
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
and l.sid = s.sid
and v.session_id = l.sid
and s.process = v.process
order by oracle_username
, session_id


select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));



***************************************
select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#
from v$active_session_history ash, v$event_name enm
where ash.event#=enm.event#
-- and SESSION_ID=213 and SAMPLE_TIME>=(sysdate-&minute/(24*60));

CAN ALSO USE THE BELOW


select do.object_name,sid,s.serial#,s.osuser,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, --s.session_id,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

Tablespace scripts




Check Table space
SQL>  SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


Query to report tablespaces having less than 20% free space left considering auto-extensible db-files:- SQL>  select tablespace_name,(round(sum(Free_Space_MB),2)) "Free_Space_MB",(round(sum(Total_Space_MB),2))  "Total_MB",(round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) "% Free" from (select a.tablespace_name,a.file_name,(((a.maxbytes-a.bytes) + nvl(b.Free_Space_B,0))/1024/1024) Free_Space_MB,(maxbytes/1024/1024)  Total_Space_MB from (select * from dba_data_files where autoextensible='YES' and increment_by != 0 and maxbytes >= bytes) a left outer join (select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b on a.file_id=b.file_id UNION select a.tablespace_name,a.file_name,(nvl(b.Free_Space_B,0)/1024/1024) Free_Space_MB,(bytes/1024/1024) Total_Space_MB from (select * from dba_data_files where autoextensible='NO' or (autoextensible='YES' and maxbytes < bytes )) a left outer join  (select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b on a.file_id=b.file_id) where tablespace_name in (select tablespace_name from dba_tablespaces where contents not in ('UNDO','TEMPORARY')) group by tablespace_name having (round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) < 20;


 DB size with Tablespace

set head on
set pagesize 30
select NVL(tablespace_name,'** Total **') "Tablespace Name",
sum("allocated") "Allocated(M)",
sum("used") "Used(M)",
sum("free") "Free(M)",
sum(df_cnt) "#(File)"
from
(
select a.tablespace_name, trunc(b.assigned/1048576) "allocated",
trunc((b.assigned-a.free)/1048576) "used",
trunc(a.free/1048576) "free",
df_cnt
from
(
select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name ) a,
(
select tablespace_name, sum(bytes) assigned, count(*) df_cnt
from dba_data_files
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
UNION ALL
SELECT tablespace_name||'[TMP]', trunc(sum(bytes)/1048576), null, null, count(*) df_cnt
from dba_temp_files
group by tablespace_name
)
group by rollup(tablespace_name) ;



Table Truncated
The below given table Truncated. Please check it once.
 SQL> truncate table APPLSYS.FND_LOG_MESSAGES;
Table truncated.

ADD More SPACE
 ALTER TABLESPACE temp2 ADD TEMPFILE '/sap_temp/IPLUAT/db/apps_st/data/temp2_02.dbf' SIZE 5000M;

Add tablespace on QA:-
login sys as sysdba
Tablespace name- APPS_TS_TX_DATA

SQL> alter tablespace APPS_TS_TX_DATA add datafile '/idata1/IFFQAEBS1/data1/a_txn_data47.dbf' size 10G;

Tablespace name- APPS_TS_TX_IDX.

/idata1/IFFQAEBS1/data1/a_txn_ind19.dbf

SQL> select * from dba_data_files where tablespace_name like '%APPS_TS_TX_IDX'

SQL>  select * from dba_data_files where tablespace_name like '%APPS_TS_TX_IDX' order by file_id;

SQL> alter tablespace APPS_TS_TX_IDX add datafile '/idata1/IFFQAEBS1/data1/a_txn_ind21.dbf' size 4G;

Tablespace altered.
SQL> Alter tablespace APPS_TS_TX_IDX add datafile '/idata/IFFQAEBS/data/a_txn_ind02.dbf' size 10G;

Tablespace altered.
 
 Increase the Size of Tablespace

If you want to increase the size of tablespace, its so simple.
You can do this by enterprise manager console. Increase the size of datafile for particular tablespace.

OR
For Example

ALTER DATABASE
DATAFILE ‘/u03/oradata/ userdata02. dbf’
RESIZE 200M;

If you don’t have any free space on that partition of disk then you can add another
datafile on the other partition  for particular tablespace.

For example-

ALTER TABLESPACE app_data
ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;

Now you can insert data with in this tablespace.

SQL> select * from dba_tablespaces;

SQL> select * from user_tablespaces;

SQL> select * from dba_data_files;

SQL> select * from dba_free_space;

SQL> desc dba_free_space;

 Name              Null?    Type
 ----------------- -------- ------------
 TABLESPACE_NAME            VARCHAR2(30)
 FILE_ID                    NUMBER
 BLOCK_ID                   NUMBER
 BYTES                      NUMBER
 BLOCKS                     NUMBER
 RELATIVE_FNO               NUMBER




Check each of the Data, Index and temporary table-spaces for extend and blocks Allocation details.

SQL> SET LINES 1000
SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS;




SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=’STAR01D’;


SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;
SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG
WHERE STATUS= 'CURRENT'
Table and tablespace location

Finding out who owns a table and what tablespace it is in is a pretty
common need of the DBA. In this query, we use the dba_tables view to
find the owner and tablespace name of the EMP table.

SQL>  select owner, table_name, tablespace_name
  2   from dba_tables
  3  where table_name='EMP';

OWNER                TABLE_NAME           TABLESPACE_NAME
-------------------- -------------------- -------------
SCOTT                EMP                  USERS
POLL                 EMP                  USERS


SQL>  select tablespace_name from dba_tablespaces where tablespace_name='APPS_PS_SX_DATA';

TABLESPACE_NAME
------------------------------
APPS_PS_SX_DATA

As we can see from this query, we have two tables called EMP, owned by two different users (Scott and Poll). Both tables are contained in the USERS tablespace.
A good exercise for you might be to try to join this query with a view like DBA_EXTENTS and figure out just how big these tables are allocated.

Check location of   DATA_FILE

SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
/u01/sonu/data/system01.dbf
/u01/sonu/data/system02.dbf
/u01/sonu/data/system03.dbf
/u01/sonu/data/system04.dbf
/u01/sonu/data/system05.dbf
/u01/sonu/data/system06.dbf
/u01/sonu/data/system07.dbf
/u01/sonu/data/system08.dbf
/u01/sonu/data/system09.dbf



 Check Data Transfer:-

select s.sid, s.username, s.osuser, to_char(sysdate-(last_call_et/24/60/60),'HH24:MI:SS') last_call, sq.address,
  sq.disk_reads / decode(sq.executions,0,1,sq.executions) DiskReadperExec,
  sq.buffer_gets / decode(sq.executions,0,1,sq.executions) buffer_gets,
  sq.executions, sq.ROWS_PROCESSED,
  decode(sq.executions,0,0,sq.CPU_TIME/sq.EXECUTIONS/1000000) cpu,
  decode(sq.executions,0,0,sq.ELAPSED_TIME/sq.EXECUTIONS/1000000) elapsed,
  decode(s.audsid,userenv('sessionid'),'show sessions',sq.sql_text) sql_text
from v$sql sq, v$session s
where s.status='ACTIVE'
--and sq.executions > 0
and s.sql_hash_value = sq.hash_value
and s.sql_Address = sq.address
/*and s.SQL_CHILD_NUMBER = sq.child_number*/
and s.user# = sq.parsing_user_id
order by elapsed ;
 TABLE LIST 

select owner, table_name, num_rows, LAST_ANALYZED
From dba_tables
where table_name in (
'AP_INVOICES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL', 'AR_PAYMENT_SCHEDULES_ALL',
'RA_CUSTOMER_TRX_ALL', 'RA_CUSTOMER_TRX_LINES_ALL' ,
'HZ_PARTIES', 'HZ_CUST_ACCOUNTS',
'AS_SALES_LEADS', 'AS_ACCESSES_ALL_ALL',
'BOM_STRUCTURES_B', 'BOM_COMPONENTS_B',
'CS_INCIDENTS_ALL_B',
'FA_ADJUSTMENTS', 'FA_DEPRN_DETAIL', 'FA_DEPRN_SUMMARY',
'FND_USERS',
'GL_JE_HEADERS', 'GL_JE_LINES',
'MRP_GROSS_REQUIREMENTS', 'MRP_RECOMMENDATIONS', 'MRP_FULL_PEGGING',
'MRP_BOM_COMPONENTS', 'MTL_MATERIAL_TRANSACTIONS',
'MTL_TRANSACTION_ACCOUNTS', 'MTL_SYSTEM_ITEMS_B',
'HR_ORGANIZATION_INFORMATION', 'HR_OPERATING_UNITS',
'MTL_PARAMETERS',
'OE_ORDER_HEADERS_ALL', 'OE_ORDER_LINES_ALL',
'PO_HEADERS_ALL', 'PO_LINES_ALL', 'PO_VENDORS',
'WF_ITEM_ACTIVITY_STATUSES', 'WF_ITEM_ATRIBUTE_VALUES',
'WF_NOTIFICATIONS', 'WF_NOTIFICATION_ATTRIBUTES' ,
'WSH_DELIVERY_DETAILS' , 'WSH_DELIVERY_ASSIGNMENTS',
'WSH_NEW_DELIVERIES', 'WSH_DELIVERY_LEGS',
'WSH_TRIP_STOPS', 'WSH_TRIPS' )
order by table_name ;
Check Table locked

Query to find locked tables in 11g.


Run following from sysdba:

select owner||'.'||object_name obj
,oracle_username||' ('||s.status||')' oruser
,os_user_name osuser
,machine computer
,l.process unix
,''''||s.sid||','||s.serial#||'''' ss
,r.name rs
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from v$locked_object l
,dba_objects o
,v$session s
,v$transaction t
,v$rollname r
where l.object_id = o.object_id
and s.sid=l.session_id
and s.taddr=t.addr
and t.xidusn=r.usn
order by osuser, ss, obj


To kill a perticular session :
alter system kill session '<>';
e.g.
alter system kill session '101,1120';


Steps for releasing lock on a table:

 <APPS>

Finding Locks  select session_id "sid",SERIAL# "Serial",  substr(object_name,1,20) "Object",  substr(os_user_name,1,10) "Terminal",  substr(oracle_username,1,10) "Locker",  nvl(lockwait,'active') "Wait",  decode(locked_mode,  2, 'row share',  3, 'row exclusive',  4, 'share',  5, 'share row exclusive',  6, 'exclusive', 'unknown') "Lockmode",  OBJECT_TYPE "Type"  FROM  SYS.V_$LOCKED_OBJECT A,  SYS.ALL_OBJECTS B,  SYS.V_$SESSION c  WHERE  A.OBJECT_ID = B.OBJECT_ID AND  C.SID = A.SESSION_ID  ORDER BY 1 ASC, 5 Desc  Finding Blocking sessions :  select l1.sid, ' IS BLOCKING ', l2.sid  from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0  and l1.id1=l2.id1 and l1.id2=l2.id2  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 ; Sessions with highest CPU consumption :  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 <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours  ORDER BY st.value;  Sessions with the highest time for a certain wait  : SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited  FROM v$session_event se, v$session s, v$process p  WHERE se.event = '&event_name'  AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours  AND se.sid = s.sid  AND s.paddr = p.addr  ORDER BY se.time_waited;  Sessions with highest DB Time usage : SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"  , stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"  FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p  WHERE sn.name = 'DB time' -- CPU  AND st.statistic# = sn.statistic#  AND st.sid = s.sid  AND sncpu.name = 'CPU used by this session' -- CPU  AND stcpu.statistic# = sncpu.statistic#  AND stcpu.sid = st.sid  AND s.paddr = p.addr  AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours  AND st.value > 0;      Step1:To verify the lock object Here is the import query: --------------------------------------------------------------- SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode FROM dba_objects o, gv$locked_object l WHERE o.object_id = l.object_id; Step 2: Find the serial# for the sessions holding the lock: SQL> select SERIAL# from v$session where SID=667; SERIAL# ---------- 21091 SQL> alter system kill session '667,21091'; System altered.

 This is to get the pkg-body definition from database select TEXT from dba_source where name=upper('xxiff_om_orders_conv_pkg') and owner='APPS' and LINE <10000  and TYPE='PACKAGE BODY';






This is to get the pkg definition from database select TEXT from dba_source where name=upper('xxiff_om_orders_conv_pkg') and owner='APPS' and LINE <10000  and TYPE='PACKAGE BODY';

Before patching - check the invalids and copy paste the output in PAL ( invalids - tab) set pages 199 set lines 199 col OBJECT_NAME for a30 select OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME, CREATED from dba_objects where status='INVALID';

Package Object Scripts:- tauseef scripts

set pagesize 0 set trimspool on spool invp.lst select 'Alter ' || decode(Object_type,'PACKAGE BODY','PACKAGE',Object_type ) ||' ' ||'"'||object_name||'"' || ' Compile ' ||decode(Object_type,'PACKAGE BODY','Body;',';' ) from user_objects WHERE STATUS = 'INVALID' order by decode(Object_type, 'PACKAGE BODY','8', 'PACKAGE','7','FUNCTION','6','PROCEDURE','5', 'VIEW','4','9'||Object_type ) / spool off @invp.lst



FNDOPP Manager Log file =========================== SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp WHERE fcpp.processor_id = fcp.concurrent_process_id AND fcpp.action_type = 6 AND fcpp.concurrent_request_id = &&Request_id


 Find TABLE with SPACE

SQL> select TABLESPACE_NAME,(TABLESPACE_SIZE)/(1024*1024*1024) SIZE_IN_GB,(ALLOCATED_SPACE)/(1024*1024*1024) ALLOCATED_IN_GB,(FREE_SPACE)/(1024*1024*1024) FREE_IN_GB FROM dba_temp_free_space;

TABLESPACE_NAME                SIZE_IN_GB ALLOCATED_IN_GB FREE_IN_GB
------------------------------ ---------- --------------- ----------
TEMP1                                  12      1.81445313 11.0576172
TEMP2                                  40       .00390625 39.9960938

SQL> select tablespace_name,sum(BYTES/1024/1024) from dba_free_space where tablespace_name like '%UNDO%' group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES/1024/1024)
------------------------------ --------------------
APPS_UNDOTS1                                  49004

SQL> select tablespace_name,sum(BYTES/1024/1024) from dba_free_space where tablespace_name like 'APPS_DEM1' group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES/1024/1024)
------------------------------ --------------------
APPS_DEM1                                     44371


How to find FND-LOG with Concurrent Request id <10543095>

spool /tmp/10543095.xls <EXCEL format>

select log_sequence, module,message_text
from fnd_log_messages
where transaction_context_id in (select transaction_context_id
from fnd_log_transaction_context
where transaction_id = 10543095)
order by log_sequence;


How to Use Spool
 
SQL> SET LINESIZE 999 VERIFY OFF FEEDBACK OFF PAGESIZE 50000 SQL > SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SQL> spool /tmp/sonu.xls
Run scripts
SQL> spool off 




 
-- list all tablespaces with their associated files, the 
-- tablespace's allocated space, free space, and the 
-- next free extent:
 
clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
 
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
 
 
 
-- list datafiles, tablespace names, and size in MB:
 
col file_name format a50
col tablespace_name format a10
 
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
 
 
 
-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann 
 
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

11i and R12 file structures





INST_TOP is newly introduced in R12. Following figures show the snapshot of file structures both in R11i and R12.


Important File Locations


File Oracle Apps 11i Oracle Apps R12
Environment Source file APPSORA.env APPS<SID>_<hostname>.env
Context File (Middle tier) $APPL_TOP/admin/$TWO_TASK.xml $INST_TOP/appl/admin/$TWO_TASK_<hostname>.xml
tnsnames.ora (OH) $ORACLE_HOME/network/admin/<CONTEXT> $INST_TOP/ora/10.1.2/network/admin
listener.ora $ORACLE_HOME/network/admin/<CONTEXT>$INST_TOP/ora/10.1.2/network/admin
appsweb.cfg $OA_HTML/bin $INST_TOP/ora/10.1.2/forms/server
tnsnames.ora (Apache) $IAS_ORACLE_HOME/network/admin/<CONTEXT> $INST_TOP/ora/10.1.3/network/admin
jsev.properties $IAS_ORACLE_HOME/Apache/Jserv/etc $INST_TOP/ora/10.1.3/opmn/conf/opmn.xml
httpd.conf $IAS_ORACLE_HOME/Apache/Apache/conf$ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf
apps.conf $IAS_ORACLE_HOME/Apache/Apache/conf $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf
formservlet.ini $IAS_ORACLE_HOME/Apache/Jserv/etc $ORACLE_HOME/forms/server/default.env
topfile.txt $APPL_TOP/admin $APPL_CONFIG_HOME/admin
adovars.env $APPL_TOP/admin $APPL_CONFIG_HOME/admin
adjborg2.txt$APPL_TOP/admin$APPL_CONFIG_HOME/admin
SSL Certificates$COMMON_TOP/admin/certs$INST_TOP/certs
AD scripts logs$COMMON_TOP/admin/log/<SID_hostname> $LOG_HOME/appl/admin/log
Concurrent Request logs$APPLCSF $APPLCSF
Apache logs$IAS_ORACLE_HOME/Apache/Apache/logs$LOG_HOME/ora/10.1.3/Apache
Jserv logs$IAS_ORACLE_HOME/Apache/Jserv/logs $LOG_HOME/ora/10.1.3/j2ee
javacache.log$COMMON_TOP/rgf/<SID_hostname> $LOG_HOME/appl/rgf


Environmental variables
Variable Oracle Apps 11i Oracle Apps R12
APPL_TOP$HOME/<SID>appl $HOME/apps/apps_st/appl
COMMON_TOP $HOME/<SID>comn $HOME/apps/apps_st/comn
ORACLE_HOME (applmgr) $HOME/<SID>ora/8.0.6 $HOME/apps/tech_st/10.1.2
IAS_ORACLE_HOME $HOME/<SID>ora/iAS $HOME/apps/tech_st/10.1.3
ORACLE_HOME (oracle) $HOME/<SID>db/10.2.0 $HOME/db/tech_st/10.2.0
ORADATA $HOME/<SID>data $HOME/db/apps_st/data
JAVA_TOP, OA_JAVA $COMMON_TOP/java $COMMON_TOP/java/classes
OA_HTML $COMMON_TOP/html $COMMON_TOP/webapps/oacore/html
FND_SECURE $FND_TOP/secure/<SID> $INST_TOP/appl/fnd/12.0.0/secure
ADMIN_SCRIPTS_HOME $COMMON_TOP/admin/scripts/<SID> $INST_TOP/admin/scripts
LOG_HOME - $INST_TOP/logs
FORMS_WEB_CONFIG_FILE- $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg
AF_JLIB - $COMMON_TOP/java/lib
JAVA_BASE - $COMMON_TOP/java
INST_TOP - $HOME/inst/apps/<CONTEXT>
ORA_CONFIG_HOME - $INST_TOP/ora
APPLCSF $COMMON_TOP/admin $LOG_HOME/appl/conc

Blocking sessions

find blocking sessions

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work.
From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You'll often have to identify these sessions in order to improve your application to avoid as many blocking sessions as possible.

find blocking sessions with v$session

SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL

find blocking sessions using v$lock

SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   v$lock l1, v$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2

 Blocking sessions with all available information

The next query prints a few more information, it let's you quickly see who's blocking who. Run this query and you can immediately call the colleague who's locking your table:

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 ;

Identifying blocked objects

The view v$lock we've already used in the queries above exposes even more information. There are differnet kind of locks.
If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one sessions finished (commit or rollback), you'll never have to wait forever.
The following queries shows you all the TM locks: 

SELECT sid, id1 FROM v$lock WHERE TYPE='TM'
SID ID1
92 20127
51 20127                               

The ID you get from this query refers to the actual database object which can help you to identify the problem, look at the next query:

SELECT object_name FROM dba_objects WHERE object_id=20127
 
There queries should help you to identify the cause of your blocking sessions!


How to find blocking session and kill the session from database


During adpatch ,deadlock will happen when some jobs depends on resource where another job its holding the resource.Using adctrl ,you can find two jobs in running state there will be no log updates.

To find blocking session jobs below query will useful. It will return two rows. select process,sid, blocking_session from v$session where blocking_session is not null;
E.g.
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365


Second step to find the serial number for the Blocking Session to kill select SERIAL# from v$session where SID=<SID number>
E.g.
SQL> select SERIAL# from v$session where SID=365;
SERIAL#
———-
130



Final step to kill the blocking session alter system kill session ‘SID,SERIAL#’;
E.g.
SQL> alter system kill session ’365,130′;
System altered.

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...