DB Tables & Queries

**Query to fetch the Nth highest value of a column from table

select * from 

    (select Emp_name, Salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as Row_num from Employees_table ) table1

where Row_num = n;

**Query to check oracle applications version through back end   

select * from product_component_version;

select release_name from apps.fnd_product_groups;    

SELECT * FROM apps.ad_bugs WHERE months_between (sysdate,CREATION_DATE)< 6

SELECT * FROM apps.ad_applied_patches WHERE MONTHS_BETWEEN(SYSDATE,CREATION_DATE)<6

select bug_number,creation_date from ad_bugs where bug_number='7579700';

select * from v$version where sid = '127';

select * from V$Container; 

SELECT * FROM DBA_DML_LOCKS;

**Query to get the table names and schema owner from the database

SELECT table_name, owner FROM dba_tables WHERE table_name LIKE 'XX_XX%'Order by owner

**Query to get the custom views

 SELECT view_name, owner FROM dba_views WHERE view_name LIKE 'XX_XX%'Order by owner

**Query to get the custom PLSQL objects 

SELECT name,type FROM dba_source WHERE name LIKE 'XX%'Order BY type,name

**Query to get the PLSQL code 

SELECT text FROM ALL_SOURCE WHERE NAME LIKE '%XX_STD%';

SELECT * FROM dba_dependencies WHERE referenced_name='XX_ORDERS_TABLE'

SELECT * FROM dba_objects WHERE object_name = 'XX_ORDERS_TABLE'  

SELECT * FROM all_objects WHERE object_name like 'PO%ARCHIVE%';

Select * from all_tables;

SELECT * FROM user_objects WHERE object_name = 'XX_ORDERS_TABLE' ;    

**Query to fetch patch and bugs

select * from ad_bugs where bug_number='1598060' ;

SELECT * FROM APPS.FND_PRODUCT_DEPENDENCIES;

select * from dba_network_acls;

SELECT directory_path  FROM all_directories    WHERE directory_name = 'DIR1111';

**Query to create DB Link

CREATE DATABASE LINK ERP.ABC.COM

 CONNECT TO READONLY

 IDENTIFIED BY <password>                 

 USING '(DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ERP.ABC.COM)(PORT = 123))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ERP)

    )  )';

DROP DATABASE LINK link_name;

**XML Report Table
select * from XDO_TEMPLATES_B where template_code like 'XLAAPRPT05'; 

select * from xdo_lobs where lob_type = 'TEMPLATE' and file_name like 'XLAAPRPT05%';

** Org Tables
select * from org_organization_definitions;
select * from hr_all_organization_units;
SELECT * FROM hr_organization_information;

select * from HR_OPERATING_UNITS;