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)
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:
wow.....so much info in one single place.
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