Scripts

Scripts to compile invalid objects in Oracle


sqlplus -s / as sysdba << EOF
set heading off;
set verify off;
set feedback off;
set trims on;
spool compile_objects.sql
prompt set echo on
select 'Alter '||rtrim(object_type, ' BODY')||' '||owner||'.'||object_name
||' compile'||decode(object_type, 'PACKAGE BODY', ' BODY', NULL)||chr(10)||'/'
from dba_objects
where object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE',
'PACKAGE BODY', 'TRIGGER', 'VIEW','MATERIALIZED VIEW')
and status = 'INVALID'
and owner='APPS'



Displays space usage for each datafile

SQL> SET SERVEROUTPUT ON
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 255
SQL> SET FEEDBACK OFF
SQL> SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
 Substr(df.file_name,1,40) "File Name",
 Round(df.bytes/1024/1024,2) "Size (M)",
 Round(e.used_bytes/1024/1024,2) "Used (M)",
 Round(f.free_bytes/1024/1024,2) "Free (M)",
 Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
 FROM DBA_DATA_FILES DF,
 (SELECT file_id,
 Sum(Decode(bytes,NULL,0,bytes)) used_bytes
 FROM dba_extents
 GROUP by file_id) E,
 (SELECT Max(bytes) free_bytes,
 file_id
 FROM dba_free_space
 GROUP BY file_id) f
 WHERE e.file_id (+) = df.file_id
 AND df.file_id = f.file_id (+)
 ORDER BY df.tablespace_name,
 df.file_name;


Script to find Roll out patch in Oracle Apps
set heading off
set feedback off
set line 132
set trimspool on
prompt
prompt ATG Patches
select decode(bug_number,
'6077487','R12.TXK.A.DELTA.3',
'6141000','12.0.3- RUP3',
'6077669','R12.ATG_PF.A.Delta.3',
'5484000’,’12.0.2 RUP2’,
'6510214',' R12.AD.A.DELTA.4',
'5484000','Oracle E-Business Suite 12.0.2 Release Update Pack (RUP2)',
'6145693','R12 RAPIDCLONE CONSOLIDATED FIXES JAN/2008',
‘6776948’,’R12 RAPIDCLONE CONSOLIDATED FIXES JUL/2008’,
'7237006', 'R12.ATG_PF.A.delta.6' ,
'Other / error'
) ||
' is installed'
from apps.ad_bugs where
bug_number in ('6077487','6141000','6077669'.’5484000’,'6510214','5484000','6145693',’67769
48’, '7237006')
/