Showing posts with label sql queries. Show all posts
Showing posts with label sql queries. Show all posts

Sunday, 27 December 2015

Useful Queries for Oracle Applications DBA'S



Script to check if datainstall / hrglobal was effective in completing its actions.


select
  substr(APPLICATION_SHORT_NAME,1,11) APPLICATION,
  substr(LEGISLATION_CODE,1,3) LEG,
  decode(nvl(STATUS,'NULL')
  ,'I','Installed'
               ,' ','Not Installed') STATUS,
  decode(action,'F','Force Install'
                ,'C','Clear'
                ,'U','Upgrade'
                ,'I','Install') ACTION,
  last_update_date
from hr_legislation_installations
where (status is not null or action is not null) order by ACTION, STATUS, legislation_code;


Sample Output

APPLICATI LEG    STATUS        ACTION        LAST_UPDATE_DAT
--------- ------ ------------- ------------- ---------------
PAY       AE     Installed                   18-OCT-15
PER       AE     Installed                   18-OCT-15
PAY       AU     Installed                   18-OCT-15
PER       AU     Installed                   18-OCT-15
PER       CA     Installed                   18-OCT-15
PAY       CA     Installed                   18-OCT-15
PER       CN     Installed                   18-OCT-15
PAY       CN     Installed                   18-OCT-15
PER       ES     Installed                   18-OCT-15
CM        GB     Installed                   18-OCT-15
PAY       GB     Installed                   18-OCT-15
PER       GB     Installed                   18-OCT-15
PER       IE     Installed                   18-OCT-15
PAY       IE     Installed                   18-OCT-15
PER       NL     Installed                   18-OCT-15
PAY       SA     Installed                   18-OCT-15
PER       SA     Installed                   18-OCT-15
PER       SG     Installed                   18-OCT-15
PAY       SG     Installed                   18-OCT-15
GHR       US     Installed                   18-OCT-15
CM        US     Installed                   18-OCT-15
PAY       US     Installed                   18-OCT-15
PER       US     Installed                   18-OCT-15
PER              Installed                   18-OCT-15

24 rows selected.


Script to Check size of fnd_lobs table



SELECT sum( bytes)/1024/1024 size_in_MB
FROM user_segments
WHERE (segment_name = 'FND_LOBS'
OR segment_name in (
SELECT segment_name
FROM user_lobs
WHERE table_name = 'FNS_LOBS'
UNION
SELECT index_name
FROM user_lobs
WHERE table_name = 'FND_LOBS'

) );


Sample Output:


   SIZE_GB SEGMENT_NAME                                                                      SEGMENT_TYPE
---------- --------------------------------------------------------------------------------- ------------------
.873779297 SYS_LOB0000034032C00004$$                                                         LOBSEGMENT

Take Backup of a Package and Package Body

Login to sqlplus as the user who owns the package/package body.

SQL>spool package_name.bkp

SQL>select text from user_source where name='&PACKAGE NAME' and type='PACKAGE';

SQL>select text from user_source where name='&PACKAGE NAME' and type='PACKAGE BODY';

SQL>Spool off;