Oracle Queries - POiSON WORLD

Archive for the ‘Oracle Queries’ Category

Important Data Guard Queries

January 6th, 2016, posted in Oracle Queries

Standby Database Process Status:

You can run following query on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

ARCH      CLOSING               1      69479     932864        261

ARCH      CLOSING               1      69480     928768        670

ARCH      CLOSING               2      75336     933888        654

ARCH      CLOSING               2      78079     930816        842

ARCH      CLOSING               1      69475     943104         79

RFS       IDLE                  0          0          0          0

...

RFS       RECEIVING             1      69481     688130       1024

MRP0      WAIT_FOR_LOG          2      78080          0          0

RFS       IDLE                  2      78080     873759          3

Last Applied Log:

Run this query on the standby database to see the last applied archivelog sequence number for each thread.

 SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1          69479

2 78079

Apply/transport lags:

v$dataguard_stats view will show the general synchronization status of standby database.

Better to use on 11gR2 even with the latest PSU (Check bugs : 13394040, 7119382, 9968073, 7507011, 13045332, 6874522).

SQL> set lines 200

SQL> col name format a40

SQL> col value format a20

SQL> select * from v$dataguard_stats;

NAME                     VALUE             UNIT        TIME_COMPUTED         DATUM_TIME

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

transport lag            +00 00:09:44      …           07/01/2013 15:49:29   07/01/2013 15:49:27

apply lag                +00 00:09:44      …           07/01/2013 15:49:29   07/01/2013 15:49:27

apply finish time        +00 00:00:00.001  …           07/01/2013 15:49:29

estimated startup time   27                second      07/01/2013 15:49:29

Apply rate:

To find out the speed of media recovery in a standby database, you can use this query:

SQL> set lines 200

SQL> col type format a30

SQL> col ITEM format a20

SQL> col comments format a20

SQL> select * from v$recovery_progress;

START_TIM TYPE             ITEM                 UNITS        SOFAR      TOTAL TIMESTAMP COMMENTS

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

20-JUN-13 Media Recovery   Log Files            Files         3363          0

20-JUN-13 Media Recovery   Active Apply Rate    KB/sec       21584          0

20-JUN-13 Media Recovery   Average Apply Rate   KB/sec        3239          0

20-JUN-13 Media Recovery   Maximum Apply Rate   KB/sec       48913          0

20-JUN-13 Media Recovery   Redo Applied         Megabytes  2953165          0

20-JUN-13 Media Recovery   Last Applied Redo    SCN+Time         0          0 01-JUL-13

20-JUN-13 Media Recovery   Active Time          Seconds     233822          0

20-JUN-13 Media Recovery   Apply Time per Log   Seconds         57          0

20-JUN-13 Media Recovery   Checkpoint Time per  Seconds         11          0

Log

20-JUN-13 Media Recovery   Elapsed Time         Seconds     933565          0

20-JUN-13 Media Recovery   Standby Apply Lag    Seconds        483          0

11 rows selected.

You can also use below before 11gR2. (Deprecated in 11gR2):

SQL> select APPLY_RATE from V$STANDBY_APPLY_SNAPSHOT;

To check Redo apply mode on physical standby database:

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where dest_id=2;

RECOVERY_MODE

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

MANAGED

To check what MRP process is waiting:

select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b where a.sid=b.sid and b.sid=(select SID from v$session where PADDR=(select PADDR from v$bgprocess where NAME='MRP0'))

EVENT                                           WAIT_TIME SECONDS_IN_WAIT

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

parallel recovery control message reply                 0               0

Archive Lag Histogram:

The V$STANDBY_EVENT_HISTOGRAM view came with 11gR2 and shows the historical occurance of archive lags in terms of seconds. For example following output shows that in 07/01/2013 archive lag reached 5 hours and in 06/15/2013 gap was 22 hours which was resolved after more than a week.

SQL> col name format a10

SQL> select * from  V$STANDBY_EVENT_HISTOGRAM;

NAME             TIME UNIT             COUNT LAST_TIME_UPDATED

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

apply lag           0 seconds              0

apply lag           1 seconds              1 04/13/2013 01:40:23

apply lag           2 seconds              1 04/13/2013 01:40:24

apply lag           3 seconds              1 04/13/2013 01:40:25

apply lag           4 seconds              1 04/13/2013 01:40:26

...

apply lag          25 seconds              3 05/21/2013 06:31:19

apply lag          26 seconds              3 05/21/2013 06:31:20

apply lag          27 seconds              3 05/21/2013 06:31:23

apply lag          28 seconds              5 05/21/2013 06:31:22

apply lag          29 seconds              1 05/15/2013 07:47:46

apply lag          30 seconds              4 05/21/2013 06:31:24

...

apply lag          44 seconds              8 06/26/2013 00:33:14

apply lag          45 seconds              8 06/26/2013 00:33:15

apply lag          46 seconds              8 06/26/2013 00:33:17

apply lag          47 seconds              8 06/26/2013 00:33:18

apply lag          48 seconds              9 06/26/2013 00:33:19

...

apply lag          57 seconds             29 06/26/2013 06:33:02

apply lag          58 seconds             25 06/26/2013 06:33:27

apply lag          59 seconds             28 06/26/2013 06:33:28

apply lag           1 minutes              0

apply lag           2 minutes           9316 06/30/2013 18:33:45

apply lag           3 minutes          94601 07/01/2013 14:23:11

apply lag           4 minutes         209262 07/01/2013 14:56:13

apply lag           5 minutes         355744 07/01/2013 16:02:33

apply lag           6 minutes         522176 07/01/2013 16:03:30

apply lag           7 minutes         634199 07/01/2013 16:01:10

...

apply lag          47 minutes          28174 07/01/2013 05:14:53

apply lag          48 minutes          28231 07/01/2013 05:14:49

apply lag          49 minutes          27099 07/01/2013 05:14:44

apply lag          50 minutes          26532 07/01/2013 05:14:40

...

apply lag           3 hours           564493 07/01/2013 05:00:08

apply lag           4 hours           511628 06/22/2013 07:43:26

apply lag           5 hours           448572 06/22/2013 07:34:03

apply lag           6 hours           369037 06/22/2013 07:09:59

apply lag           7 hours           206117 06/21/2013 00:53:27

apply lag           8 hours           137932 06/21/2013 00:33:53

apply lag           9 hours           137091 06/21/2013 00:03:33

apply lag          10 hours            98103 06/20/2013 23:26:34

apply lag          11 hours           104157 06/20/2013 22:53:12

apply lag          12 hours           102141 06/20/2013 22:14:07

apply lag          13 hours            89214 06/20/2013 21:32:22

apply lag          14 hours            64880 06/20/2013 21:04:29

apply lag          15 hours            43471 06/20/2013 21:01:45

apply lag          16 hours            38075 06/20/2013 20:59:37

apply lag          17 hours            38449 06/20/2013 20:55:34

apply lag          18 hours            22049 06/16/2013 01:22:55

apply lag          19 hours            19873 06/16/2013 00:53:55

apply lag          20 hours            15985 06/15/2013 23:52:16

apply lag          21 hours            13290 06/15/2013 03:08:49

apply lag          22 hours             7330 06/15/2013 02:07:26

apply lag          23 hours             1606 02/15/2013 22:16:11

apply lag           1 days              3216 02/15/2013 22:00:42

apply lag           2 days             16768 02/15/2013 20:54:06

144 rows selected.

Redo switch report of primary database can be seen with the following query. This information may be helpful when investigating the possible causes of archive gaps, apply lags or data guard performance issues.

SQL> SET PAGESIZE 9999

SQL> col day format a15

SQL> SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM gv$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log ) B;

DAY                 COUNT#       MIN#       MAX# DAILY_AVG_MB

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

2013-07-01             442     147345     147566       452608

2013-06-30             526     147083     147347       538624

2013-06-29             532     146817     147082       544768

2013-06-28             928     146353     146816       950272

2013-06-27             760     145973     146352       778240

2013-06-26             708     145619     145972       724992

2013-06-25             560     145338     145618       573440

2013-06-24             498     145090     145339       509952

2013-06-23             104     145038     145089       106496

2013-06-22             338     144869     145037       346112

2013-06-21             748     144495     144868       765952

2013-06-20             748     144121     144494       765952

2013-06-19             952     143645     144120       974848

2013-06-18             882     143204     143644       903168

2013-06-17             914     142746     143203       935936

2013-06-16             454     142520     142747       464896

2013-06-15            1520     141760     142519      1556480

2013-06-14            1862     140829     141759      1906688

2013-06-13             970     140343     140828       993280

2013-06-12             598     140045     140345       612352

2013-06-11             550     139770     140044       563200

2013-06-10             516     139511     139769       528384

2013-06-09             178     139423     139512       182272

2013-06-08             296     139275     139422       303104

2013-06-07             490     139030     139274       501760

2013-06-06             572     138744     139029       585728

2013-06-05             488     138499     138743       499712

Following PL/SQL code can be used to find out time difference between primary and standby database. (Run on primary database, not for real-time apply)
Following output shows 10 minutes and 54 seconds of time difference.

set serveroutput on;

DECLARE

   v_diff NUMBER := 0;

   v_hrs NUMBER := 0;

   v_min NUMBER := 0;

   v_sec NUMBER := 0;

   p_dte1 DATE;

   p_dte2 DATE;

   date1 long;

   date2 long;

   BEGIN

   date1 := 'select sysdate from dual';

   date2 := 'select MAX (next_time) FROM gv$archived_log where APPLIED=''YES''';

   execute immediate date1 into p_dte1;

   execute immediate date2 into p_dte2;

   v_diff := ABS(p_dte2 - p_dte1);

   v_hrs := TRUNC(v_diff, 0)*24;

   v_diff := (v_diff - TRUNC(v_diff, 0))*24;

   v_hrs := v_hrs + TRUNC(v_diff, 0);

   v_diff := (v_diff - TRUNC(v_diff, 0))*60;

   v_min := TRUNC(v_diff, 0);

   v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);

   DBMS_OUTPUT.put_line(

   TO_CHAR(v_hrs) ||' '||

   TO_CHAR(v_min) ||' '||

   TO_CHAR(v_sec) );

END;

0 10 54

PL/SQL procedure successfully completed.

This one will give the same output with the above code, but this must be run on standby database.

set serveroutput on;

DECLARE

   v_diff NUMBER := 0;

   v_hrs NUMBER := 0;

   v_min NUMBER := 0;

   v_sec NUMBER := 0;

   p_dte1 DATE;

   p_dte2 DATE;

   date1 long;

   date2 long;

   BEGIN

   date1 := 'select sysdate from dual';

   date2 := 'select max(TIMESTAMP) from v$recovery_progress';

   execute immediate date1 into p_dte1;

   execute immediate date2 into p_dte2;

   v_diff := ABS(p_dte2 - p_dte1);

   v_hrs := TRUNC(v_diff, 0)*24;

   v_diff := (v_diff - TRUNC(v_diff, 0))*24;

   v_hrs := v_hrs + TRUNC(v_diff, 0);

   v_diff := (v_diff - TRUNC(v_diff, 0))*60;

   v_min := TRUNC(v_diff, 0);

   v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);

   DBMS_OUTPUT.put_line(

   TO_CHAR(v_hrs) ||' '||

   TO_CHAR(v_min) ||' '||

   TO_CHAR(v_sec) );

END;

0 10 54

PL/SQL procedure successfully completed.

The last one is a shell command and lists the archive log apply records of standby database alert log with the corresponding times at the end of the line. This is useful to see a clean picture of redo apply status on the standby database.

tail -10000 /u01/app/oracle/product/diag/rdbms/testdb/TESTDB/trace/alert_TESTDB.log |awk -v x="" '{if (index($0,"Media Recovery Log ")!=0) print $0" "x; else if($1=="Mon"||$1=="Tue"||$1=="Wed"||$1=="Thu"||$1=="Fri"||$1=="Sat"||$1=="Sun") x=$0}'

Media Recovery Log +DATA/…/thread_1_seq_69468.904.819643305 Mon Jul 01 14:42:14 2013

Media Recovery Log +DATA/…/thread_1_seq_69469.899.819643701 Mon Jul 01 14:48:51 2013

Media Recovery Log +DATA/…/thread_2_seq_78072.741.819643579 Mon Jul 01 14:49:24 2013

Media Recovery Log +DATA/…/thread_1_seq_69470.956.819643639 Mon Jul 01 14:50:30 2013

Media Recovery Log +DATA/…/thread_2_seq_78073.1129.819644003 Mon Jul 01 14:53:55 2013

Media Recovery Log +DATA/…/thread_1_seq_69471.1123.819643961 Mon Jul 01 14:54:10 2013

Media Recovery Log +DATA/…/thread_1_seq_69472.861.819644303 Mon Jul 01 14:58:54 2013

Media Recovery Log +DATA/…/thread_2_seq_78074.1136.819644507 Mon Jul 01 15:02:14 2013

Media Recovery Log +DATA/…/thread_1_seq_69473.1024.819644695 Mon Jul 01 15:05:24 2013

Media Recovery Log +DATA/…/thread_2_seq_78075.936.819644933 Mon Jul 01 15:09:25 2013

Media Recovery Log +DATA/…/thread_1_seq_69474.904.819645085 Mon Jul 01 15:11:57 2013

Media Recovery Log +DATA/…/thread_1_seq_69475.911.819645509 Mon Jul 01 15:19:01 2013

Media Recovery Log +DATA/…/thread_2_seq_78076.899.819645377 Mon Jul 01 15:19:17 2013

Media Recovery Log +DATA/…/thread_1_seq_69476.1018.819646001 Mon Jul 01 15:27:12 2013

Media Recovery Log +DATA/…/thread_2_seq_78077.1060.819645837 Mon Jul 01 15:27:53 2013

Media Recovery Log +DATA/…/thread_1_seq_69477.956.819645995 Mon Jul 01 15:28:48 2013

Media Recovery Log +DATA/…/thread_2_seq_78078.861.819646339 Mon Jul 01 15:32:50 2013

Media Recovery Log +DATA/…/thread_1_seq_69478.1123.819646363 Mon Jul 01 15:33:17 2013

Media Recovery Log +DATA/…/thread_1_seq_69479.741.819646705 Mon Jul 01 15:38:57 2013

Media Recovery Log +DATA/…/thread_2_seq_78079.890.819646767 Mon Jul 01 15:40:00 2013

Media Recovery Log +DATA/…/thread_1_seq_69480.904.819647027 Mon Jul 01 15:44:21 2013

Media Recovery Log +DATA/…/thread_2_seq_78080.911.819647307 Mon Jul 01 15:48:59 2013

Media Recovery Log +DATA/…/thread_1_seq_69481.1136.819647365 Mon Jul 01 15:49:57 2013

Media Recovery Log +DATA/…/thread_1_seq_69482.1018.819647679 Mon Jul 01 15:55:10 2013

Media Recovery Log +DATA/…/thread_2_seq_78081.936.819647855 Mon Jul 01 15:58:03 2013

Media Recovery Log +DATA/…/thread_1_seq_69483.1024.819648003 Mon Jul 01 16:00:35 2013

read more no comments

ORA-01153: an incompatible media recovery is active

November 16th, 2015, posted in Oracle Queries

Problem :

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

-----------------------------------------------------------------------------------------------------
                                          OR
-----------------------------------------------------------------------------------------------------

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


Solution :

This indicates that currently running media recovery process i.e. standby active

SQL> alter database recover managed standby database cancel; --- Cancel first

Database altered. 


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

read more no comments

ORA-01153: an incompatible media recovery is active On standby Database

September 24th, 2015, posted in Oracle Queries

Problem

sys@standby> startup mount;

ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             490735648 bytes
Database Buffers          335544320 bytes
Redo Buffers                6606848 bytes
Database mounted.
sys@standby> alter database recover managed standby database using current logfile disconnect;

alter database recover managed standby database using current logfile disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Cause

This indicates a currently running media recovery process.

Action

sys@standby> alter database recover managed standby database cancel;

sys@standby> alter database recover managed standby database using current logfile disconnect;

Note

When shutting down physical standby database, firstly turn off media recovery process. Otherwise the next time when starting up redo apply again, you will encounter error ORA-01153.

read more no comments

SQL Query / Code To Change English Date To Arabic

September 2nd, 2015, posted in Oracle Queries

SELECT

to_char(SYSDATE,’day dd month yyyy’, ‘nls_calendar=”Arabic Hijrah”’),
to_char(SYSDATE,’day dd month yyyy’, ‘nls_calendar=”English Hijrah”’),
to_char(SYSDATE,’day dd month yyyy’, ‘nls_calendar=”gregorian”’)

FROM DUAL;

read more no comments

Forms : Could not reserve record (2 tries). Keep trying

August 5th, 2015, posted in Oracle Queries

Second Way :

One of your end users attempts to modify a record, and the user is prompted with a dialog box asking “Could not reserve record [2 tries]. Keep trying?” The user answers “yes” and after being prompted a few more times the user gives up. Ultimately the user gets a “FRM-40501: ORACLE error: unable to reserve record for update or delete.” When this happens it’s nice to have some scripts ready to go to quickly diagnose what is causing the contention, so appropriate action can be taken. The forms don’t wait to the obtain the lock for very long, so it’s a little more difficult to determine the blocking lock. This first script provides a listing of the possible locks and some relevant E-Business Suite information for digging further.

Query 1:

select vs.audsid audsid,
 locks.sid sid,
 vs.serial# serial#,
 vs.username oracle_user,
 vs.osuser os_user,
 vs.program program,
 vs.module module,
 vs.action action,
 vs.process process,
 decode(locks.lmode,
       1, NULL,
       2, 'Row Share',
       3, 'Row Exclusive',
       4, 'Share',
       5, 'Share Row Exclusive',
       6, 'Exclusive', 'None') lock_mode_held,
 decode(locks.request,
       1, NULL,
       2, 'Row Share',
       3, 'Row Exclusive',
       4, 'Share',
       5, 'Share Row Exclusive',
       6, 'Exclusive', 'None') lock_mode_requested,
 decode(locks.type,
       'MR', 'Media Recovery',
       'RT', 'Redo Thread',
       'UN', 'User Name',
       'TX', 'Transaction',
       'TM', 'DML',
       'UL', 'PL/SQL User Lock',
       'DX', 'Distributed Xaction',
       'CF', 'Control File',
       'IS', 'Instance State',
       'FS', 'File Set',
       'IR', 'Instance Recovery',
       'ST', 'Disk Space Transaction',
       'TS', 'Temp Segment',
       'IV', 'Library Cache Invalidation',
       'LS', 'Log Start or Log Switch',
       'RW', 'Row Wait',
       'SQ', 'Sequence Number',
       'TE', 'Extend Table',
       'TT', 'Temp Table',
       locks.type) lock_type,
 objs.owner object_owner,
 objs.object_name object_name,
 objs.object_type object_type,
 round( locks.ctime/60, 2 ) lock_time_in_minutes
from v$session vs,
         v$lock locks,
         dba_objects objs,
         dba_tables tbls
where locks.id1 = objs.object_id
 and vs.sid = locks.sid
 and objs.owner = tbls.owner
 and objs.object_name =  tbls.table_name
 and objs.owner != 'SYS'
 and locks.type = 'TM'
 order by lock_time_in_minutes;

To determine the table(s) the form is trying to lock, use the Help->Record History menu option; this provides the base table or view for the form block.

Look through the result set from Query 1 for an object_name (typically a table or view) in the same vicinity as your table or view. If you have a form block based on a view, it may be helpful to look up the tables behind the view. If the action starts with an ‘FRM:%’, then another forms session has the lock.

One thing to note: You’ll often see the same user blocking themselves. This could be a training issue, or it could be due to a previous forms session that crashed, but the f60webmx process did not die. If this is the case, you can kill the application server OS process (based on the process value in Query 1).

Query 2 provides further details for results in Query 1 that are forms sessions – simply plop in the AUDSID from Query 1.

Query 2:

SELECT
        F.AUDSID,
        S.SID,
        S.SERIAL#,
        L.USER_ID,
        L.TERMINAL_ID,
        L.LOGIN_NAME,
        R.RESP_APPL_ID,
        R.RESPONSIBILITY_ID,
        F.FORM_ID,
        F.FORM_APPL_ID,
        L.PID,
        L.PROCESS_SPID,
        NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)) TIME,
        USR.USER_NAME,
        a.application_name,
        RSP.RESPONSIBILITY_NAME,
        FRM.USER_FORM_NAME,
        s.program,
        s.action,
        s.module,
        s.state,
        s.event,
        s.wait_class,
        s.seconds_in_wait
FROM FND_RESPONSIBILITY_TL RSP,
        FND_FORM_TL FRM,
        FND_USER USR,
        FND_LOGINS L,
        FND_LOGIN_RESPONSIBILITIES R,
        FND_LOGIN_RESP_FORMS F,
        GV$SESSION S,
        fnd_application_tl A
WHERE F.AUDSID = &ENTER_FORM_AUDSID
AND R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND L.LOGIN_ID = R.LOGIN_ID
AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND F.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
AND R.RESP_APPL_ID = RSP.APPLICATION_ID
AND RSP.LANGUAGE = 'US'
AND RSP.application_id = a.application_id
AND a.language = 'US'
AND F.FORM_ID = FRM.FORM_ID
AND F.FORM_APPL_ID = FRM.APPLICATION_ID
AND FRM.LANGUAGE = 'US'
AND F.AUDSID = S.AUDSID;

If a concurrent program holds the lock, Query 3 provides a bit more information. Here we can see the user, concurrent program, how long it’s been running and log/output files.

Query 3:

select fcr.request_id,
         fcr.requested_by,
         fu.user_name,
         fcr.program_application_id,
         fcr.concurrent_program_id,
         fcr.actual_start_date,
         fat.application_name,
         fcp.concurrent_program_name,
         fcpt.user_concurrent_program_name,
         fcr.description,
         fcr.logfile_node_name,
         fcr.outfile_name,
         fcr.logfile_name,
         fcr.completion_text,
         fcr.parent_request_id,
         vs.process,
         vs.state,
         vs.event,
         vs.wait_class,
         vs.seconds_in_wait
         from v$session vs,
              fnd_concurrent_requests fcr,
              fnd_application_tl fat,
              fnd_concurrent_programs fcp,
              fnd_concurrent_programs_tl fcpt,
              fnd_user fu
         where vs.audsid =  &ENTER_CONC_PROCESS_AUDSID
         and vs.process = fcr.os_process_id
         and fcr.actual_completion_date is null
         and fcr.program_application_id = fat.application_id
         and fcr.program_application_id = fcp.application_id
         and fcr.concurrent_program_id = fcp.concurrent_program_id
         and fcr.program_application_id = fcpt.application_id
         and fcr.concurrent_program_id = fcpt.concurrent_program_id
         and fcr.requested_by = fu.user_id;

This should be enough information to chase down the offender (someone out for coffee and not save that latest change first?) or possibly even point to a process that needs attention.

*********************************************************************************************************************
Also Check : http://aliimmam.com/locking-and-unlocking-tables-in-oracle/

Also Check : http://aliimmam.com/lock-tables-and-unlock-tables-syntax/

Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well. Source : http://dwhlaureate.blogspot.com/2014/07/how-to-unlock-locked-table-in-oracle.html

*********************************************************************************************************************

POiSON WORLD

Archive for the ‘Oracle Queries’ Category

Important Data Guard Queries

ORA-01153: an incompatible media recovery is active

Problem :

ORA-01153: an incompatible media recovery is active On standby Database

SQL Query / Code To Change English Date To Arabic

Forms : Could not reserve record (2 tries). Keep trying

Second Way :

Follow Me On Social Networks :

Recent Posts

Recent Comments

Categories

Archives

Share It With Others :

POiSON WORLD

Archive for the ‘Oracle Queries’ Category

Important Data Guard Queries

ORA-01153: an incompatible media recovery is active

Problem :

ORA-01153: an incompatible media recovery is active On standby Database

SQL Query / Code To Change English Date To Arabic

Forms : Could not reserve record (2 tries). Keep trying

Second Way :

Follow Me On Social Networks :

Recent Posts

Recent Comments

Categories

Archives

Tags

Share It With Others :