Useful SQLs from FND_USER table

Just thought of sharing a few commonly used SQLs from FND_USER table. These will work with all versions of Oracle Applications (11i and R12).

If you are new to Oracle Applications, this will come very handy for you. If you are an experienced Oracle EBusiness Suite professional, you might find this too silly and you may skip this :)

Get details of a user by USER_ID or USER_NAME:

SELECT US.USER_ID, US.USER_NAME, US.LAST_LOGON_DATE,
  US.CREATION_DATE, US.CREATED_BY, US.LAST_UPDATE_DATE,
  US.LAST_UPDATED_BY, US.LAST_UPDATE_LOGIN, US.START_DATE,
  US.DESCRIPTION AS DISPLAY_NAME, US.EMPLOYEE_ID,
  US.EMAIL_ADDRESS, US.PERSON_PARTY_ID
FROM APPS.FND_USER US
WHERE US.USER_ID = 13936
-- WHERE USER_NAME = 'ATHOMAS'



List of users, who logged into the system during the past 30 minutes: (As we don’t have any other reliable place to track session length when user is in HTML session, this also can be used as the active users. Users who open forms can be tracked even better by a query below).

SELECT US.USER_ID, US.USER_NAME, US.LAST_LOGON_DATE,
  US.DESCRIPTION AS DISPLAY_NAME, US.EMPLOYEE_ID,
  US.EMAIL_ADDRESS, US.PERSON_PARTY_ID
FROM APPS.FND_USER US
WHERE US.LAST_LOGON_DATE > SYSDATE-0.02


Logins of a single user during the past 7 days: (Can be used to track unauthorized activity, updates, etc. This is linked with LAST_UPDATE_LOGIN of WHO columns)

SELECT LOGIN_ID, USER_ID, START_TIME, END_TIME, PID AS PROCESS_ID, SPID AS SUB_PROCESS, TERMINAL_ID, LOGIN_NAME, SESSION_NUMBER, SERIAL# AS SERIAL, LOGIN_TYPE
FROM APPS.FND_LOGINS FLG
WHERE START_TIME > SYSDATE - 7
AND USER_ID = 13936

Users who use FORM-based sessions: (Users who use only HTML-based windows will be missed in this query. Also the users who opened FORMS without any active form open. The FORMS home navigator window won’t be picked by this query)

SELECT SID AS SESSION_ID, SERIAL# AS SERIAL, MACHINE AS COMPUTER_NAME,
  CLIENT_IDENTIFIER AS CLIENT_USER_ID, ACTION,
  MODULE AS FORMS_MODULE, TO_CHAR (LOGON_TIME, 'dd/mm/yyyy HH24:mi:ss') LOGON_TIME, STATUS
FROM   SYS.GV_$SESSION SESS
WHERE  LOGON_TIME > SYSDATE - 0.3
-- AND MACHINE = 'NRBVLTEBS01' -- change machine name for instance (based on forms/application server)
-- AND CLIENT_IDENTIFIER = 'ATHOMAS' -- enable this, if a specific user is to be tracked
AND MODULE LIKE 'e:%' AND USERNAME = 'APPS' AND PROGRAM like 'frm%' -- string to identify the FORMS sessions


Did I miss anything? Feel free to add them in the COMMENTS section below ... It would be a great help for the EBiz community :)

Oracle DB SQLs – Table Data Backup Steps

 

Business Need:

Before changing a record, previous data needs to be backed up in a table for analysis.

Helpful to analyze change done by custom program or user actions on a table data.

 

Queries:

CREATE TABLE OE_OLINES_NOV23A
AS
SELECT * FROM OE_ORDER_LINES_ALL OL WHERE HEADER_ID = 5801517
/
           
ALTER TABLE OE_OLINES_NOV23A ADD (OL_INSERT_DATE DATE DEFAULT SYSDATE)
/
 
INSERT INTO OE_OLINES_NOV23A
SELECT OL.*, SYSDATE FROM OE_ORDER_LINES_ALL OL WHERE HEADER_ID = 5801517;
 
SELECT * FROM OE_OLINES_NOV23A;
 

 

Notes:

1.    These steps are backing up a single order’s lines. The WHERE clause can be substituted for a time window ( CREATION_DATE > SYSDATE-1 ) or Order TYPE ( LINE_TYPE_ID = 2529 ) or any other parameters that needs to be monitored.

 It may be a good idea to have all temp table have a standard format extensions (Eg: %_DATE or  %_BACK) and delete them periodically (After 1 month after creation)

  

Keywords:

Oracle DB, EBS, OM, Order Lines, OE_ORDER_LINES_ALL, Query, SQL, SQLPLUS

grep - the coolest Unix command ever :)

This is a cool Unix command.

The grep command searches the given file for lines containing a match to the given strings or words. By default, grep displays the matching lines. Use grep to search for lines of text that match one or many regular expressions, and outputs only the matching lines.

Usage Examples:

$ grep ath /etc/passwd

> simple search for a word in a single file


$ grep -r "192.168.1.1" /etc/

> search through the files within a folder


$ egrep -w -i 'thomas|david' filename

> search for 2 words in a single line
 

How to create a Oracle PL/SQL function that returns random numbers?

How to create a function that returns random numbers?

Can you imagine an Oracle PL/SQL Function that returns Random Numbers? This is very useful for testing features in multiple scenarios.
Oracle comes with a built-in package “dbms_random.value” for this purpose. The function will return a random float number between a given range.

Eg:
Create a input with YES and NO answers. Test both without changing any underlying criteria. To test the changes within a webpage, just refresh the page a few times without any change to the database or inputs.

CREATE OR REPLACE FUNCTION GET_RANDOM
RETURN NUMBER
IS
      P_RETVALUE    NUMBER;
BEGIN

      P_RETVALUE := 0;
    
      SELECT floor(mod(dbms_random.value(3,10),2))
      INTO P_RETVALUE
      FROM DUAL;
    
      RETURN P_RETVALUE;

EXCEPTION
WHEN OTHERS THEN
      RETURN 0;
END;
/

COMMIT
/


The function is called using the below call:
select get_random() from dual

By changing the value in the MOD function, it is possible to have more than 2 values. Also by adding an IF .. THEN ELSE loop after the SELECT STATEMENT, it is possible to have non-numeric values.

Eg:
IF P_RETVALUE = 0 THEN
      RETURN "Equal"
ELSE IF P_RETVALUE = 1 THEN
      RETURN "Less"
ELSE
      RETURN "More"
END IF
 

Oracle Database - Identify locking sessions

Here is a simple SQL to identify locking sessions within an Oracle Database:
SELECT 'SID ' || L1.SID ||' is blocking ' || L2.SID BLOCKING
FROM V$LOCK L1, V$LOCK L2
WHERE L1.BLOCK =1 AND L2.REQUEST > 0
AND L1.ID1=L2.ID1
AND L1.ID2=L2.ID2

Here is a sample output:
SID 776 is blocking 465

All locking sessions are stored in the table V$LOCK.

Note: The locking can happen due to any reason. It is not necessarily caused due to Applications or User Activity. Look everywhere possible to identify the problem. Likely places to look first ... Concurrent program, running for a long time, user FORM Sessions, SQL*Plus or TOAD sessions with ROWID selection or UPDATEs and the current session is not COMMITed, Running SQL Scripts, DataLoad jobs, etc are just to name a few.
 

Kill Oracle 11i or R12 FORM Java/JInitiator Session

Here is a common problem, Oracle EBusiness Suite users have ... You opened a Forms window. You searched for a specific data or LOV Results. The page is trying to open the new results, but hanging for a long time. What is the way out?

Solution: Oracle forms are displayed by JInitiator - an Oracle developed custom Java Application. To start a new session afresh, we need to kill Java Executable program and the browser program (Eg: internet Explorer).

Steps:
1. Open Windows Command prompt (Start -> Run -> <cmd> -> <ENTER>)

2. Run the following command to kill all java sessions: (This will kill JInitiator windows)
taskkill /f /im java.exe

3. Run the following command to kill all browser sessions: (This will kill Internet Explorer windows. If you use any browser other than IE, please change the command accordingly.)
taskkill /f /im iexplore.exe

4. Close Command Prompt

This solution will work for all versions of Oracle Applications (R12 and 11i). But this will work only when you access Apps using Microsoft Windows :)

Note: This process will terminate ALL Browser sessions in the machine. Make sure that all the possible data saved before running the command. This will also help to get rid of all Oracle Sessions and Cookie effects.
 

Steps - Kill Oracle or SQL Session - sqlplus

You are connected to TOAD. You ran a complex query and the window is hanging for a long time. Even the CANCEL button does not respond appropriately. What to do?

A very common problem, Oracle Developers have. Some quick solutions are <CNTL> C and <CNTL> D buttons. But many times, they also fail.

Here is a more complete and perfect way to kill the session.


Step 1: Identify the session - SID and Serial

Use appropriate WHERE clauses to identify the hanging session. You may use client (Toad/SQL - MODULE), OSUSER (Operating System User, Eg: CORP/AbThomas), MACHINE (Eg: server_dns_name) or TIMESTAMPS.


-- get non-unix sql sessions, kill one session
SELECT S.OSUSER, SUBSTR(S.SID || ',' || S.SERIAL#, 0, 20) AS KILL_STRING, MODULE, MACHINE, LOGON_TIME
FROM   V$SESSION S
where OSUSER = 'athomas'
-- AND MODULE LIKE 'T%' -- 'SQ%'
-- AND MACHINE LIKE '%NRBVUEBSAS02%'
-- AND MODULE LIKE 'JDBC Thin Client%'

In the results from above query, the module SQL is SQL Plus client, TOAD is Toad Client and anything related to Java or JVM will be the connections created from web server. (The Application Server connects to Database server through JDBC conneciton).

Step 2: Open another window, close the session:
Identify the Session Id and Serial (Second column in the above query) and substitute in the below line ...

ALTER SYSTEM KILL SESSION '876,24615'

The connection will be lost  with a message. Still you have the SQLs in the screen. In this way, all your query will be preserved, even though the connection is lost.


Simple steps. But very useful many times ... NJoy :)