Query to Find Free Space in a Tablespace

undefine tbsp
set lines 152
set echo off

col tablespace_name for a20
select utbs.tablespace_name,
round(utbs.mb) “Allocated Used/Unused MB”,
round(Ftbs.mb) “Allocated_Free MB”,
round((100/utbs.mb)*Ftbs.mb) “%Allocated_Free MB”,
decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) “Space_AutoExtensible MB”,
Ftbs.MaxBytes “MaxChunk MB”
from
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like ‘%&&TBSP%’
order by round(Ftbs.mb)
/

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

Autoconfig Error during SETUP Phase (Oracle Applications DBA 11i)

Autoconfig failed during setup phase:

While running the script adgendbc.sh

Unable to update GUEST_USER_PWD in database to GUEST/ORACLE1 –
java.sql.SQLException: ORA-06550: line 1, column 323:
PLS-00201: identifier ‘FND_VAULT.PUT’ must be declared
ORA-06550: line 1, column 323:
PL/SQL: Statement ignored

Cause

AdminAppServer.java 115.32 calls fnd_app_server_pkg.update_server passing 5 parameters.
The versions of FND_APP_SERVER_PKG (AFSCASRS.pls/AFSCASRB.pls) accepts 5 parameters.

These version used in the database is older than the version at OS filesystem level.

Solution

1. Run following SQL query :

select text
from all_source
where name =’FND_APP_SERVER_PKG’ and line < 7;

2. If the versions in the database are older than the ones at the OS level, go to the

$FND_TOP/patch/115/sql directory and recreate the spec/body by running :

– sqlplus apps/<passwd> @AFSCASRS.pls

– sqlplus apps/<passwd> @AFSCASRB.pls

3. Rerun Autoconfig.

Concurrent Requests Failing with APP-FND-00362

Issue:

All the custom concurrent requests failing to run.
Logfile shows the following error:

APP-FND-00362: Routine afpbep cannot execute request &REQUEST for program &PROGRAM,
because the environment variable &BASEPATH is not set for the application to which
the concurrent program executable &EXECUTABLE belongs.

Cause: BASEPATH dir not set as environment variable.

Solution:

Find the application for which concurrent program is defined..

Go to Application Developer–> Concurrent Program –> Define
and query for the concurrent request(which is failing)

(For e.g. If you get the Application : Business Online)

Now go to Application Developer–>Register Application

Query for the Application to get BASEPATH.

(e.g XX_TOP is the basedirectory for Custom Application)

At the application tier check if XX_TOP is not set..
$echo $XX_TOP

Add the env variable XX_TOP=/../../..
 to applTop env or custom env and restart concurrent manager

Resubmit the request which have failed.

Oracle Applications Database Administrator Interview Questions





1. How to verify if a patch is applied or not (11i and R12)


A: Query from ad_bugs table.(11i and r12)
select bug_number,created.last_update_date from ad_bugs where bug_number=’&patchnum’;


R12.2


select ad_patch.is_patch_applied(‘R12’,-1,20034256) from dual;


expected results:
EXPLICIT = applied
NOT APPLIED = not applied / aborted


2.What is the difference between ad_bugs and ad_applied_patches?


A: If a patch is applying multiple bug fixes, the details of all bugs fixed by the patch can be found from ad_bugs,
ad_applied_patches has information only about patches applied using adpatch.




Two tables to check if the patch is applied or not:


This table includes the defined bugs on the system: 
SELECT   bug_number 
FROM     apps.ad_bugs
WHERE   bug_number LIKE ‘%’&patchnum’%’;


This table includes patches applied on the system:
SELECT patch_name 
FROM   apps.ad_applied_patches
WHERE patch_name LIKE ‘%’&patchnum’%’


3. How to check if a forms patch is applied?


A: There is no specific way to check if a forms patch is applied. Forms Patches are usually applied through Shell scripts.