How to Get Oracle E-Bussiness Suite Tables list

You can use the following select statement to get the Oracle E-Bussiness Suite Tables List for each application:

SELECT A.APPLICATION_ID,
       A.APPLICATION_NAME,
       A.APPLICATION_SHORT_NAME,
       A.DESCRIPTION APPLICATION_DESCRIPTION,
       A.BASEPATH,
       A.PRODUCT_CODE,
       P.TABLE_NAME,
       P.USER_TABLE_NAME,
       P.DESCRIPTION
  FROM FND_TABLES P,
       FND_APPLICATION_VL A
 WHERE A.APPLICATION_ID = P.APPLICATION_ID
Advertisements

SQL Statement to Get Session Information Which Used any Database Object

To get the session information which used any database objects you can use the following sql statement:

SELECT S.SID,
       S.SERIAL#,
       S.USERNAME,
       TO_CHAR(S.LOGON_TIME,'MMDDYYYY:HH24:MI') LOGON_TIME,
       TYPE,
       STATUS,
       PROCESS,
       SQL_ADDRESS,
       SQL_HASH_VALUE,
       P.OPTIMIZER_MODE,
       P.HASH_VALUE,
       P.ADDRESS,
       P.CPU_TIME,
       P.ELAPSED_TIME,
       P.SQL_TEXT
  FROM V$SQLAREA P,
       V$SESSION S
 WHERE S.SQL_HASH_VALUE = P.HASH_VALUE
   AND S.SQL_ADDRESS    = P.ADDRESS
   AND S.USERNAME       IS NOT NULL
   AND UPPER(P.SQL_TEXT) like '%'||UPPER(:P_OBJ_NAME)||'%'