Find locks on object

Couple of different queries listed to find locks on objects / tables ...etc.

1. How to find any session is locked on a specific object:

Use case, one of the user trying to update a particular object, which is hanging to update.  Used below query to see who is accessing that object and cleared it.

Query:

select a.sid, s.serial#, s.status from v$access a, v$session s where a.owner='&Schema_Name' AND a.object='&Object_Name' and a.sid=s.sid;

Example:

select a.sid, s.serial#, s.status from v$access a, v$session s where a.owner='APPS' AND a.object='XXC_FS_RWP_BEE_PKG' and a.sid=s.sid;

2. Find Locks on Table Level:

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;

Query to find Concurrent Requests submitted by a particular user

Query to find Concurrent Requests submitted by a particular User:

SELECT
    request_id,
    request_date,
    user_concurrent_program_name,
    responsibility_name,   
    argument_text,   
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    and user_name = upper('&USERNAME')
ORDER BY REQUEST_DATE DESC;

How to find database growth on a Monthly Basis

SQL query to find database growth month wise and year.  

select to_char(CREATION_TIME,'RRRR') Year, to_char(CREATION_TIME,'MM') Month, round(sum(bytes)/1024/1024/1024) GB

from   v$datafile

group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')

order by   1, 2;

Sample Output:

YEAR MO         GB

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

2019 02         96

2019 03         65

2019 04        239

2019 05         32

2019 06         64

2019 07         95

2019 09        315

2019 10         31

2019 11        375

2020 04        313

10 rows selected.

How to check product patch level or code level oracle apps?

To obtain the current patchset levels for each specific E-Business Suite functional product use the following steps:

1. Log into OAM.

(Responsibility "Oracle Applications Manager", menu "OAM Support Cart")
and Navigate:

2. Support Cart --> Applications Signature --> Collect --> Check "Product Information" box
--> Click on "View" (eyeglasses)

This will display the following patchset information:

Application Name
Current Patch Level
Product Version
Status (Installed, Shared Product, Inactive)

3. The patchset level information can be retrieved directly from sqlplus with the following diagnostic script:

$AD_TOP/sql/adutconf.sql

4. Use the script below to find patchset level.

SELECT app_short_name, MAX(patch_level)

FROM apps.ad_patch_driver_minipks

GROUP BY app_short_name ;

Followed the below Metalink Document:

E-Business Suite Applications Manager Steps To Get The Patchset Level of Each Specific Oracle Applications Functional Product in R12 (Doc ID 550654.1)

Query to find runtime of a concurrent program

The following query finds total run-time in minutes for a concurrent program.

SELECT /*+ rule */
       rq.parent_request_id                   "Parent Req. ID",
       rq.request_id                          "Req. ID",
       tl.user_concurrent_program_name        "Program Name",
       rq.actual_start_date                   "Start Date",
       rq.actual_completion_date              "Completion Date",
       ROUND((rq.actual_completion_date -
           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"      
  FROM applsys.fnd_concurrent_programs_tl  tl,
       applsys.fnd_concurrent_requests     rq
 WHERE tl.application_id        = rq.program_application_id
   AND tl.concurrent_program_id = rq.concurrent_program_id
   AND tl.LANGUAGE              = USERENV('LANG')
   AND rq.actual_start_date IS NOT NULL
   AND rq.actual_completion_date IS NOT NULL
   AND tl.user_concurrent_program_name = 'PRC: Generate Draft Revenue for a Range of Projects'  -- <change it>
   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date
 ORDER BY rq.request_id DESC;

Kill all inactive sessions in a Database.

If you want to kill all inactive sessions in the database, it is hard to find and kill one by one.  Use below statement to create kill statements at a time and run them to kill all inactive session.

select 'alter system kill session '||sid||','||serial#|| ';' from v$session where status='INACTIVE';

Find Concurrent Request ID from SID

Find Concurrent Request id from SID:

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
   WHERE s.sid in ('&sid')
  AND s.paddr = c.addr
  AND a.oracle_process_id = c.spid
  AND a.phase_code = UPPER ('R');

Output:

   INST_ID REQUEST_ID        SID    SERIAL# SPID
---------- ---------- ---------- ---------- ------------------------
         1   20602689      15647       2673 5617

Blocking Session Details

How to find blocking / locking session details:

set lines 145
col sid noprint

select distinct
       s1.sid,s1.username || '@' || s1.machine ||
       ' ( INST=' || s1.inst_id || ' SID=' || s1.sid||','||s1.serial# ||'  '||s1.status||': '||s1.last_call_et||' SQLID: '||s1.sql_id||' )  is blocking '
       || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' AS blocking_status
  from gv$lock l1, gv$session s1, gv$lock l2, gv$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
   and l1.inst_id = s1.inst_id
order by s1.sid;

Output:

BLOCKING_STATUS
-------------------------------------------------------------------------------------------------------------------------------------------------
APPS@mydbserver ( INST=1 SID=6323,26775  ACTIVE: 2494 SQLID: 08pfumd9shm53 )  is blocking APPS@mydbserver ( INST=1 SID=6323 )

1 row selected.

SQL>

Drop Unused UNDO Tablespace

1) Check how many UNDO tablespaces are available on your database:

SQL> select tablespace_name, contents from dba_tablespaces where contents = 'UNDO';

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
UNDOTBS1                       UNDO
UNDOTBS2                       UNDO


2) Check which UNDO tablespace is being used currently:

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1


3) Validate that, any object that it is using other undo_tablespace than current undo_tablespace.

set pagesize 100
set lines 200
set long 999
col username for a9
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
  SELECT segment_name
  FROM dba_segments
 WHERE tablespace_name = 'UNDOTBS2');

4) If none of the object is using second undo_tablespace, then use below command to drop the unused undo_tablespace.

SQL> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

Database Size and Free Space

Check your database size and free space:

col 'Database Size' for a15
col 'Free Space' for a15
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free Space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;

DBA Directory

1) How to check existing DBA Directories in a database:

col OWNER for a10
col DIRECTORY_NAME for a35
col DIRECTORY_PATH for a75
set lines 200
select * from dba_directories;

2) Create a new DBA Directory or modify existing directory path:

create or replace directory DMPDIR as '/u01/datapump';

3) Grant required privileges to the DBA Directory to access by a user.

grant read,write on directory DMPDIR to <username>;

4) Grant required privileges to the DBA Directory to access by every user.

grant read,write on directory DMPDIR to public;

Cancel Concurrent Request From Backend

Run this as 'APPS' user:

1) Concurrent Request Status:

select status_code,phase_code from fnd_concurrent_requests where  request_id=&1;  

2) Cancel Concurrent Request:

update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id=&1; 
commit; 

3)  Update Concurrent request as 'Complete Normal'

update fnd_concurrent_requests set status_code='C',phase_code='C' where request_id=&1;

commit;

4)  Cancel All Pending Concurrent Requests

Updating base tables directly is not supported or recommended by Oracle – this update would be OK to execute in a non-production instance. In a production instance, use APIs or cancel the pending requests through the forms interface.

update FND_CONCURRENT_REQUESTS set phase_code='C',status_code='D' where phase_code='P' ;

commit;

How To Retrive APPS Password – R12

Steps to find apps password in R12:

    1)    Connect as SYSTEM or SYS USER
    2)    Create Function to decrypt the encrypt password
    3)    Query for the encrypted password
    4)    Query for decrypt the password
    5)    validate the apps password 


STEP 1:
[oracle@localhost]$sqlplus [system or sys]/<password>

STEP 2:
$SQL>create FUNCTION apps.decrypt_get_pwd(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/

Function created.

STEP 3:
$SQL>select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';
ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS

STEP 4:
$SQL>SELECT apps.decrypt_get_pwd('GUEST/ORACLE',FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS) from dual;

Output:
APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS')
..........................................................................................................
welcome

STEP 5:
$SQL> conn apps/welcome
Connected.

Find inactive FORMS Sessions older than 2hours

Script to find inactive forms sessions older than 2hours:

SELECT
p.spid,
s.process,
s.status,
s.machine,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et/3600 Last_Call_ET,
s.action,
s.program,
s.module,
s.sid,
s.serial#,
s.CLIENT_IDENTIFIER
FROM
V$SESSION s
, V$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'oracle'
AND
s.last_call_et/3600 > 2
and
s.program like '%frmweb%'
and
s.status='INACTIVE' order by logon_time;

Find Session(SID and Serial#) details using Process ID:

Find database session details using a process id(PID).

SELECT S.CLIENT_IDENTIFIER, s.status , s.TYPE , s.username , s.osuser , s.server ,
s.machine , s.module , s.logon_time , s.process , p.spid,
p.pid, s.SID, s.audsid, SYSDATE - (s.last_call_et / 86400) 
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+) AND s.process ='&process';

Tablespace usage

To check tablespace total size, free space, used and free percentage:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024 * 1024) "Size (GB)",
       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;


To check Temp tablespace total size, used and free space.

SELECT   A.tablespace_name tablespace, D.gb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_used,
         D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 gb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 / 1024 gb_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.gb_total;

Find SPID from SID and Serial#

Find SPID from SID and Serial#:

select spid from gv$process where addr in (select paddr from gv$session where sid=&sid and serial#=&serial);