Oracle Flashback Query: Recovering at the Row Level (Recover deleted rows)

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:

SELECT * FROM EMPLOYEE AS OF TIMESTAMP 
   TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE name = 'JOHN';

Restoring John's information to the table EMPLOYEE requires the following update:

INSERT INTO employee 
    (SELECT * FROM employee AS OF TIMESTAMP 
     TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.

See Also: Oracle Database Application Developer's Guide - Fundamentals for a more extensive discussion of the use of the SELECT... AS OF SQL statement and extensive examples of its use. Oracle Database SQL Reference for more details on the syntax of the SELECT... AS OF form of the SELECT statement.

Check Current Applied Patch & Patch level status of all modules

Check Current Applied Patch


SELECT patch_name, patch_type,
maint_pack_level,
creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC

Query used to view the patch level status of all modules

SELECT a.application_name,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id;

Cloned date of oracle application and Version

Query to get Version details..


SELECT product, VERSION, status
FROM product_component_version;


Query to  get cloned date of an oracle instance 


SELECT resetlogs_time FROM v$database

How to get Login Passwords for application & Data Base instances

Most often, oracle apps developers have to work in different DB instances for development as well as for testing purposes. In such situations we need to get access to different oracle application instances and data base (dev/test/crp etc.,) instances. So we have to request & follow a lengthy approval process to get those login details, some times you may end up in loosing your delivery schedules.

There is a simple way by which you can get the logins/passwords of your DB & Application with out chasing the DBAs.

Oracle follows an encryption algorithm to encrypt user passwords. Most references to the encryption algorithm point to either the PL/SQL package APPS.FND_WEB_SEC or the Java class "oracle.apps.fnd.security.WebSessionManagerProc".

For decryption and encryption, the following calls are made:

APPS.FND_WEB_SEC >

oracle.apps.fnd.security.WebSessionManagerProc>

oracle.apps.fnd.security.AolSecurity>

oracle.apps.fnd.security.AolSecurityPrivate

The actual encryption and decryption routines are in the "oracle.apps.fnd.security.AolSecurityPrivate" Java class. This Java class is stored both in the database as a Java Stored Procedure and in the operating system directory $COMMON_TOP/java.

Create a package specification: get_pwd

-- Package Specification

CREATE OR REPLACE PACKAGE get_pwd AS

FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2;

END get_pwd;

Create the package body: get_pwd

-- Package Body 

CREATE OR REPLACE PACKAGE BODY 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;

Call the package function as shown below:

/** Run this on toad, Get the DB apps password */

SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))

FROM DUAL)), usertable.encrypted_foundation_password)

FROM DUAL) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')

,1

, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')

- 1

)

FROM DUAL))

Call the package function as shown below:

/** Run this on toad, Get the application usernames and passwords */

SELECT usertable.user_name

, (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))

FROM DUAL)), usertable.encrypted_foundation_password)

FROM DUAL) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE

UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')

,1

, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')

- 1

)

FROM DUAL))))

,usertable.encrypted_user_password)

FROM DUAL) AS encrypted_user_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER ('username') -- Here username is application login such as 'OPERATIONS'

Find Vacation rule details of an user

select * from apps.WF_ROUTING_RULES
where role='<USER NAME>'

we can also check vacation rules for other users by
Oracle Administrator > Workflow status monitor > Administration > Vacation Rules

Need not login as the same user to check for vacation rule details.

Script to find the users logged in to application

SELECT ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address) AS email_address,fl.end_time,fl.start_time,(fl.end_time-fl.start_time)
FROM apps.per_people_x ppx, apps.fnd_user fu, apps.fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppx.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('INTERFACE', 'SYSADMIN', 'GUEST')
AND fu.user_name in (<enter the user names seperated by comma ','>)
AND fl.end_time is not null
GROUP BY ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address)
,fl.end_time
,fl.start_time
ORDER BY 2Bookmark

Query to find the user, responsibility and concurrent program details of submitted requests

Select B.user_concurrent_program_name,C.user_name,D.responsibility_name, A.* 
from apps.fnd_concurrent_requests A, apps.fnd_concurrent_programs_tl B, Apps.fnd_user C, apps.fnd_responsibility_tl D
 where 1=1
and  B.user_concurrent_program_name like 
 '<Enter the concurrent program name>'
 and B.concurrent_program_id=A.concurrent_program_id
 and A.requested_by=C.user_id
 and A.responsibility_id=D.responsibility_id
and b.language=userenv('LANG')
and d.language=userenv('LANG')
 order by request_date desc

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

Including the Out put views 


SELECT E.FILE_NAME,B.USER_CONCURRENT_PROGRAM_NAME,C.USER_NAME,D.RESPONSIBILITY_NAME, A.* 
from apps.fnd_concurrent_requests A, apps.fnd_concurrent_programs_tl B, Apps.fnd_user C, apps.fnd_responsibility_tl D , apps.FND_CONC_REQ_OUTPUTS_V E
 WHERE 1=1
and  B.user_concurrent_program_name like  'Hologic Printed Purchase Order Report'
 and B.concurrent_program_id=A.concurrent_program_id
 AND A.REQUESTED_BY=C.USER_ID
 and c.user_name ='LMEDEIROS'
 AND A.RESPONSIBILITY_ID=D.RESPONSIBILITY_ID
 AND E.REQUEST_ID(+)=A.REQUEST_ID
and b.language=userenv('LANG')
and d.language=userenv('LANG')
 order by request_date ;