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

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete

Was this Post Helpful?

Feel free to suggest your opinions in the comments section!