Queries To find object details
16) To check Locks on a Table
The relevant query is as follows:
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%’;
17) Find an Object’s Type:
The relevant query is as follows:
SELECT object_type
FROM user_objects
WHERE object_name = ‘’;
18) Check for Existence Of Any Object In The Database:
The relevant query is as follows:
SELECT owner, object_type
FROM all_objects
WHERE object_name = ‘’;
ie. object_name = PO_HEADERS_ALL
OWNER OBJECT_TYPE
———————————————————-
APPS_APPDEMO SYONONYM
PODEMO TABLE
19) Check Database for Invalid Objects:
The relevant query is as follows:
SELECT owner, object_name, object_type
FROM all_objects
WHERE status = ‘INVALID’;
SELECT count(*)
FROM from dba_objects
WHERE status = ‘INVALID’;
20) To Find Dependent Objects Of An Invalid Object:
The relevant query is as follows:
SELECT owner, object_type, object_name
FROM dba_objects
WHERE status = ‘INVALID’
AND object_name IN (SELECT referenced_name
FROM dba_dependencies
WHERE name = ‘’);
This will bring results down only one level of dependencies.
21) Finding Columns and Tables
The all_tab_columns is a useful table that can be queried for such information.
For example:
SELECT table_name, column_name
FROM all_tab_columns
WHERE column_name like ‘PO_HEADER%’;
TABLE_NAME COLUMN_NAME
—————————— —————— ————————
PO_ACCRUAL_RECONCILE_TEMP PO_HEADER_ID
PO_ACCRUAL_WRITE_OFFS PO_HEADER_ID
INVFV_MOVEMENT_STATISTICS PO_HEADER_NUMBER
MTL_MOVEMENT_STATISTICS_V PO_HEADER_ID
FINANCIALS_PURGES_V PO_HEADERS
You can also find all tables from,
select * from all_objects
where object_name like ‘%PO_HEADERS_ALL%’
and object_type = ‘TABLE’
22) Finding and Deleting Duplicate Rows
The following statement will find and display all duplicate rows in a table, except the row with the maximum rowid:
SELECT *
FROM a
WHERE rowid (SELECT max(rowid)
FROM b
WHERE a. = b.
AND a. = b. — make sure all columns are compared
AND a. = b.;
Note: Duplicate rows which contain only NULL values will not be identified by above statement.
23) Get The Code For a View:
The relevant query is as follows:
SQL> set long 10000
SELECT text
FROM user_views
WHERE view_name = ‘’;
24) Checking Version of Database Package:
The relevant query is as follows:
SELECT text
FROM dba_source
WHERE 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.
25) How to Determine the File Name for a Package
The relevant query is as follows:
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
26) View Code for a Package:
The relevant query is as follows:
SQL> set long 10000
SELECT text
FROM user_source
WHERE name = ‘’;
27) Display Package Errors:
The relevant query is as follows:
SELECT text
FROM dba_errors
WHERE name = ‘’;
28) Check to see what Indexes are on a Table:
The relevant query is as follows:
SELECT index_name
FROM all_indexes
WHERE table_name = ‘’;
’;
”;
1
29) Check to see what Columns are Indexed on a Table:
The relevant query is as follows:
SELECT column_name, index_name
FROM all_ind_columns
WHERE table_name = ‘
U = unique index
N = non-unique index
30) Find Triggers on a Table:
The relevant query is as follows:
SELECT trigger_name
FROM all_triggers
WHERE table_name = ‘
31) Find Directory where Trace Files are Stored:
The relevant query is as follows:
SELECT value
FROM v$parameter
WHERE name = ‘user_dump_dest’;
User_dump_dest is an initialization parameter defined in the init.ora file.
32) Check for Multiple/Duplicate Logins
The relevant query is as follows:
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
33) Obtaining Org_Id and Setting Context
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)
34) To check if multi-org is setup
The relevant query is as follows:
Select multi_org_flag from FND_PRODUCT_GROUPS;
35) To find out details of concurrent request submitted with certain conditions
The relevant query is as follows:
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
1
No comments:
Post a Comment