Oracle: DBA's day out

How do you check whether Trace is enabled to particular concurrent program from the back end?

How do you check whether Trace is enabled to particular concurrent program from the back end?

Solution:

1. select CONCURRENT_PROGRAM_ID,USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%Program Name%'; (You will get Id)

2. select concurrent_program_id,enable_trace from apps.fnd_concurrent_programs where concurrent_program_id ='concurrent program id';

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

ORA-04021 Timeout when compiling package

ORA-04021 Timeout when compiling package

1) Find who is accessing the object using below sql
     SELECT * FROM v$access WHERE object = 'XXRH_SUPX_ORCL_PKG';

2) Check session details using below query. SID will be from the result of above query
    SELECT * FROM V$SESSION WHERE SID IN (1040,1529,1016)

3) Kill the session which is accessing the package.
   ALTER SYSTEM KILL SESSION '1016,58335'

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

Extract User Password

Extract User Password
------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE APPS.get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/
--------------------------------------
CREATE OR REPLACE PACKAGE BODY APPS.get_pwd
AS
   FUNCTION decrypt (
      KEY     IN VARCHAR2,
      VALUE   IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

---------------------------------------
SELECT usr.user_name,
       get_pwd.
        decrypt (
          (SELECT (SELECT get_pwd.
                           decrypt (fnd_web_sec.get_guest_username_pwd,
                                    usertable.encrypted_foundation_password)
                     FROM DUAL)
                     AS apps_password
             FROM fnd_user usertable
            WHERE usertable.user_name =
                     (SELECT SUBSTR (
                                fnd_web_sec.get_guest_username_pwd,
                                1,
                                INSTR (fnd_web_sec.get_guest_username_pwd,
                                       '/')
                                - 1)
                        FROM DUAL)),
          usr.encrypted_user_password)
          PASSWORD
FROM fnd_user usr
WHERE usr.user_name = 'TESTUSER';

Privilege to view Package body without Execute Grant

Privilege to view Package body without Execute Grant

SELECT DBMS_METADATA.get_ddl ('PACKAGE', 'HR_PERSON_API', 'APPS') FROM DUAL;

How to check if a certain Patch was applied to Oracle Applications instance using 'adpatch'?

How to check if a certain Patch was applied to Oracle Applications instance using 'adpatch'?

Method 1

Check Patches applied in Oracle Applications Manager (OAM).

a) Connect to OAM:

http://hostname.domain:PORT/servlets/weboam/oam/oamLogin

Go to Site Map-->Maintenance-->Applied Patches

Enter Patch ID and press 'Go'

See if Patch was returned.

Method 2

Use 'adphrept' utility
Patch History report usage:

adphrept query_depth \
bug_number or ALL \
bug_product or ALL \
end_date_from (mm/dd/rr or ALL) \
end_date_to (mm/dd/rr or ALL) \
patchname/ALL \
patchtype/ALL \
level/ALL \
language/ALL \
appltop/ALL \
limit to forms server? (Y/N) \
limit to web server? (Y/N) \
limit to node server? (Y/N) \
limit to admin server? (Y/N) \
only patches that change DB? (Y/N)

Specify 1 or 2 or 3 for query_depth

1-> Details of patches only
2-> Details of patches and their Bug Fixes only
3-> Details of patches their Bug Fixes and Bug Actions

Example: To get the complete patch details for patches applied in Dec 2000:

On UNIX:

$ cd $AD_TOP/patch/115/sql
$ sqlplus /
SQL> @adphrept.sql 3 ALL ALL 12/01/00 12/31/00 ALL ALL ALL ALL ALL N N N N N

Method 3
Use the following sql.
set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('','');
--
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,
p_patchlist(i));
println('..Patch ' || p_patchlist(i) || ' was ' || p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/
Note: Please enter Patch number in place of and , e.g '3240000'
Example Output
when p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943'):
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was EXPLICIT
..Patch 4733943 was EXPLICIT
.
dcollierpc8:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was NOT_APPLIED
..Patch 4733943 was NOT_APPLIED

Method 4

You might also use the following query, however the most reliable methods are the described above.
SQL> SELECT DISTINCT RPAD(a.bug_number,
11)|| RPAD(e.patch_name,
11)|| RPAD(TRUNC(c.end_date),
12)|| RPAD(b.applied_flag, 4) BUG_APPLIED
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number in ('','');
ORDER BY 1 DESC;