Database Object query to fetch the details based on Limited Information

1. Find a Table/View name based on limited information using the '%Selective Search%'

SELECT * FROM all_objects WHERE object_type in ('TABLE','VIEW') AND object_name like '%%';

Note: If any special character in the Object Name like '_ or $' then use the below format

SELECT * FROM all_objects WHERE object_type in ('TABLE','VIEW') AND object_name like 'V%$%' escape '\'; '

2. Find a Column name from the Table based on limited information using the '%Selective Search%'

SELECT * FROM all_tab_columns WHERE column_name like '%FILE%NAME%' AND owner = 'AP';

3. Find a package name from the database based on limited information using the '%Selective Search%'

SELECT * FROM all_source WHERE type = 'PACKAGE BODY' AND text like '%<Custom Search>%'; 

4. Find the Arguments/Log/Output information for the Concurrent Request

SELECT * FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name like 'XX%Program%Name%';

SELECT * FROM fnd_concurrent_requests WHERE concurrent_program_id = <Concurrent Program ID> order by request_id desc;

5. Find the File information path based on File name given in the History section rather than Database Object Name <FVGTSUTB.pls / apinvutb.pls >

SELECT * FROM ad_files WHERE filename like '%apinvutb%';

Then find the $Header Information$ using the Unix Query from the respective server,

strings -a $SQLAP_TOP/patch/115/sql/apinvutb.pls |grep '$Header'

ap patch/115/sql apiimvtb.pls 120.219.12020000.39
ap patch/115/sql apiimvts.pls 120.36.12020000.6
ap patch/115/sql apinvutb.pls 120.79.12020000.13
ap patch/115/sql apinvuts.pls 120.23.12020000.2