R12 directory structure diagram

Get all the Concurrent Program Request Details

Get all the Concurrent Program Request Details

select   request_id,
           parent_request_id,
           fcpt.user_concurrent_program_name Request_Name,
           fcpt.user_concurrent_program_name program_name,
           DECODE(fcr.phase_code,
                        'C','Completed',
                        'I','Incactive',
                        'P','Pending',
                        'R','Running') phase,
           DECODE(fcr.status_code,
                        'D','Cancelled',
                        'U','Disabled',
                        'E','Error',
                        'M','No Manager',
                        'R','Normal',
                        'I','Normal',
                        'C','Normal',
                        'H','On Hold',
                        'W','Paused',
                        'B','Resuming',
                        'P','Scheduled',
                        'Q','Standby',
                        'S','Suspended',
                        'X','Terminated',
                        'T','Terminating',
                        'A','Waiting',
                        'Z','Waiting',
                        'G','Warning','N/A') status,
           round((fcr.actual_completion_date - fcr.actual_start_date),3) * 1440 as Run_Time,
           round(avg(round(to_number(actual_start_date - fcr.requested_start_date),3) *  1440),2) wait_time,
           fu.User_Name Requestor,
           fcr.argument_text parameters,
           to_char (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,
           to_char(actual_start_date, 'MM/DD/YY HH24:mi:SS') startdate,
           to_char(actual_completion_date, 'MM/DD/YY HH24:mi:SS') completiondate,
           fcr.completion_text
From    fnd_concurrent_requests fcr,
           fnd_concurrent_programs fcp,
           fnd_concurrent_programs_tl fcpt,
           fnd_user fu
Where 1=1
and      fcr.concurrent_program_id = fcp.concurrent_program_id
and      fcp.concurrent_program_id = fcpt.concurrent_program_id
and      fcr.program_application_id = fcp.application_id
and      fcp.application_id = fcpt.application_id
and      fcr.requested_by = fu.user_id
and      fcpt.language = 'US'
and      fcr.actual_start_date like sysdate
GROUP BY  request_id,
                 parent_request_id,
                 fcpt.user_concurrent_program_name,
                 fcr.requested_start_date,
                 fu.User_Name,
                 fcr.argument_text,
                 fcr.actual_completion_date,
                 fcr.actual_start_date,
                 fcr.phase_code,
                 fcr.status_code,
                 fcr.resubmit_interval,
                 fcr.completion_text,
                 fcr.resubmit_interval,
                 fcr.resubmit_interval_unit_code,
                 fcr.description
Order by 1 desc


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

How to change user's password in Oracle 11g and change back it to original

How to change user's password in Oracle 11g and change back it to original

Imp points
(1) Note down current password found in data dictionary.
(2) Modify password
(3) Do required tasks
(4) Reset the password


Get current password :-
SQL> SET LONG 100000
SQL> SELECT dbms_metadata.get_ddl('USER','TEST') FROM dual;

   CREATE USER "TEST" IDENTIFIED BY VALUES 'S:659106CEC6E63EE94597855D276029184257176283D5456B83C1A711ABD8;42CE85A96B6A78FA';
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

Password is in encrypted format.

Now change password temporarily:-
SQL> alter user TEST identified by 1234;

Login to the user with new password:-
SQL> conn test/1234
Connected.

Once the job is done we can change password back to the original :-

SQL> alter user TEST identified by values 'S:659106CEC6E63EE94597855D276029184257176283D5456B83C1A711ABD8;42CE85A96B6A78FA';


DONE

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

ERROR :

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

Solution:-

Create System Environment variable in that PC with following name and value :

Name : JAVA_TOOL_OPTIONS
Value : -Djava.vendor="Sun Microsystems Inc."

Table Lock

>> TABLE LOCK

select * from dba_waiters;

select * from dba_blockers;

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S;

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S where SID='< >';

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;

++++++++++++++++++++++++++++++++++++++++++++++++

>> Table Lock

1)     Run the Following Script to Get Locked Tables in Session
BEGIN
   DBMS_OUTPUT.enable (1000000);

   FOR do_loop IN (SELECT session_id,
                          a.object_id,
                          xidsqn,
                          oracle_username,
                          b.owner owner,
                          b.object_name object_name,
                          b.object_type object_type
                     FROM v$locked_object a, dba_objects b
                    WHERE xidsqn != 0 AND b.object_id = a.object_id)
   LOOP
      DBMS_OUTPUT.put_line ('.');
      DBMS_OUTPUT.put_line ('Blocking Session : ' || do_loop.session_id);
      DBMS_OUTPUT.
       put_line (
            'Object (Owner/Name): '
         || do_loop.owner
         || '.'
         || do_loop.object_name);
      DBMS_OUTPUT.put_line ('Object Type : ' || do_loop.object_type);

      FOR next_loop
         IN (SELECT sid
               FROM v$lock
              WHERE id2 = do_loop.xidsqn AND sid != do_loop.session_id)
      LOOP
         DBMS_OUTPUT.put_line ('Sessions being blocked : ' || next_loop.sid);
      END LOOP;
   END LOOP;
END;

2)     Get SID & Serial Number to Kill Session

SQL> select * from v$session where sid in 'SID,SERIAL#'
SQL> alter system kill session 'SID,SERIAL#'