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

7 thoughts on “Useful Queries for Oracle Applications DBA’S”

This is really interesting, You’re a very skilled blogger. I’ve joined your feed and look forward to seeking more of your magnificent post. Also, I’ve shared your site in my social networks!