Determining your EBS Code Level and Family Pack

There’s lots of posts out there telling you how to determine your EBS patch levels, code levels, etc.  But whenever I google them, I can’t find the one that actually tells me what I want in a simplified way.

Here’s what matters most often to me as the Apps DBA:

select
  abbreviation
,codelevel
from
  ad_trackable_entities
where
  abbreviation in( 'ad','txk','fnd','fwk','atg_pf','icx' )
order by
  abbreviation;

ABBREVIATION                   CODELEVEL                                                                                                                                            
------------------------------ ------------
atg_pf                         C.4                                                                                                                                                   
fnd                            C.4                                                                                                                                                   
fwk                            C.4                                                                                                                                                   
icx                            D.3                                                                                                                                                   
txk                            C.7                                                                                                                                                   

 6 rows selected

Temp usage

We can get information of temporary tablespace usage details with the help of below query:


Detailed Usage


Set lines 1000
set pages 300
col TABLESPACE for a20
col SIZE for a20
col SID_SERIAL for a18
col USERNAME for a20
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program,
a.CLIENT_IDENTIFIER
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

Detailed Usage ABOVE 100MB:


Set lines 1000
set pages 300
col TABLESPACE for a20
col SIZE for a20
col SID_SERIAL for a18
col USERNAME for a20
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||' MB' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program,
a.CLIENT_IDENTIFIER
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr and b.blocks*p.value/1024/1024>='100'
ORDER BY b.tablespace, b.blocks;

To Check the TEMP tablespace :


SELECT ROUND((SELECT SUM (tf.BYTES)
FROM dba_temp_files tf)/1024/1024/1024,2) AS "Total (GB)",
ROUND((SELECT (SUM (tu.blocks))
FROM v$tempseg_usage tu)
* (SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size')
/1024/1024/1024,2) AS "Used (GB)",
ROUND(((select sum(TABLESPACE_SIZE) from DBA_TEMP_FREE_SPACE) - ((SELECT (SUM (tu.blocks))
FROM v$tempseg_usage tu)
* (SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size')))/1024/1024/1024,2) AS "Free (GB)",
ROUND ((SELECT (SUM (tu.blocks))
FROM v$tempseg_usage tu) / (SELECT SUM (tf.blocks)
FROM dba_temp_files tf) * 100,2) AS "Used_pct(%)" FROM DUAL;