Monday, August 3, 2015

Oracle Apps Useful Queries

Select object_id, session_id, oracle_username, os_user_name,
Process, locked_mode
From sys.v_$locked_object;

Select a.object_name, b.oracle_username
From all_objects a, v$locked_object b
Where a.object_id = b.object_id
And a.object_name like 'po%';

SELECT object_type
FROM   user_objects
WHERE object_name =<OBJECT_NAME>;

SELECT owner, object_type
FROM     all_objects
WHERE  object_name =<OBJECT_NAME>;

SELECT owner, object_type, object_name
FROM     dba_objects
WHERE  status = ‘INVALID’
AND       object_name IN (SELECT referenced_name
                          FROM    dba_dependencies
                          WHERE  name =<INVALID_OBJECT_NAME>);

SELECT  table_name, column_name
FROM   all_tab_columns
WHERE column_name like 'PO_HEADER%’;

Finding and Deleting Duplicate Rows
SELECT *
FROM <tableA> a
WHERE rowid  <>  (SELECT max(rowid)
FROM <tableB> b
WHERE a.<column1> = b.<column1>
AND a.<column2> = b.<column2> -- make sure all columns are compared
AND a.<column3> = b.<column3>;

Note:  Duplicate rows which contain only NULL values will not be identified by above statement.

Get The Code For a View:
SQL>     set long 10000

SELECT text
FROM    user_views
WHERE view_name =<VIEW_NAME>;

SELECT text
FROM     dba_source
WHERE  name =<PACKAGE_NAME >
AND        text LIKE ‘%Header:%’;

For example, look for the 2 files that make up the package “PO_INQ_SV’:
TEXT
----------------------------------------------------------------------------------
/*   $Header:  POXPOVPS.pls   80.1.7016.2  98/02/24   20:00:59  porting ship $   */
/*   $Header:  POXPOVPB.pls   80.3.7016.4  98/07/01   16:47:41  porting ship $   */

Notice the results give us the file names that make up the Header/Spec and Body of the package. The B and the S at the end of the 7 letters indicate the Body and Spec file.

Select text from dba_source where name = 'xxx_xxx_xxx' and text like '%$Header:%';
or
strings -a aprcvmtb.pls | grep 'CREATE'

This will return a string of the package name like:
CREATE OR REPLACE PACKAGE BODY AP_RECT_MATCH_PKG AS

SQL>     set long 10000

SELECT text
FROM    user_source
WHERE name =<PACKAGE_NAME>;

SELECT text
FROM    dba_errors
WHERE name =<PACKAGE_NAME>;

Check to see what Indexes are on a Table:
SELECT index_name
FROM    all_indexes
WHERE table_name =<TABLE_NAME>;

SELECT column_name, index_name
FROM     all_ind_columns
WHERE  table_name =<TABLE_NAME>;
U = unique index
N = non-unique index

Find Triggers on a Table:
SELECT trigger_name
FROM   all_triggers
WHERE table_name =<TABLE_NAME>;

Find Directory where Trace Files are Stored:
SELECT value
FROM     v$parameter
WHERE  name = ‘user_dump_dest’;

User_dump_dest  is an initialization parameter defined in the init.ora file.

1. Get the person_id for the employee
    select person_id     from per_people_f
    where last_name like &LASTNAME

2. Now use the person_id in the following SQL
    select count(*) from wf_users
    where orig_system_id = &PERSON_ID
    and status = 'ACTIVE'

If count is greater than one, you got duplicates

Please launch the applications and then use Help -> Diagnostics ->
Examine and then select $Profile in block and then org_id as field

Get this value and then set the context using SQL:
SQL> Exec dbms_application_info.set_client_info(&org_id)

To check if multi-org is setup
Select multi_org_flag from FND_PRODUCT_GROUPS;

  SELECT request_id,
         req.CONCURRENT_PROGRAM_ID,
         prog.DESCRIPTION,
         Request_date,
         printer,
         number_of_copies,
         usr.user_name
    FROM fnd_concurrent_requests req,
         fnd_concurrent_programs_tl prog,
         fnd_user usr
   WHERE     printer <> 'noprint'
         AND requested_start_date > '14-AUG-06'
         AND status_code = 'C'
         AND number_of_copies > 0
         AND prog.CONCURRENT_PROGRAM_ID = req.CONCURRENT_PROGRAM_ID
         AND prog.language = 'US'
         AND req.requested_by = usr.user_id

ORDER BY request_date DESC

2 comments:

Atul Vishwakarma said...

wow.....so much info in one single place.

Jat said...

Excellent article. Very informative.It is good to such a good amount of scripts at one place.

I have read many article in this blog.It is indeed very site and give very helpful information

hats off to You!!!

Post a Comment

Best Blogger TipsGet Flower Effect