OracleNotes

Blog

  • Oracle EBS Housekeeping Jobs

    For a long time, I searched for a comprehensive list of housekeeping jobs [Standard EBS jobs provided by Oracle] that should be scheduled periodically on the EBS instance. Since I could not find a single source, came up with my own after a lot of reading… There may be more, but these should be scheduled on the EBS to keep the instance performing healthy… The arguments and run interval may need to be modified to suit the workload and business requirement… but this will get you started… Few jobs may need multiple scheduling with different parameters…

    Job       : Gather Schema Statistics 
    Executable: FNDGSCST 
    Frequency : Daily 
    Parameters: ALL, 10, , NOBACKUP, , LASTRUN, GATHER AUTO, , N 
    Frequency : Weekly 
    Parameters: ALL, 10, 4, NOBACKUP, , LASTRUN, GATHER, , Y  


    Job       : JTF Item InterMedia Index Optimizing operation
    Executable: JTFOPTI 
    Frequency : Weekly 
    Parameters: FULL, 2 


    Job       : JTF Item InterMedia Index Sync Operation
    Executable: JTFSYNC 
    Frequency : Weekly 
    Parameters: (None) 


    Job       : MES InterMedia Index Sync Operation
    Executable: AMVSYNC 
    Frequency : Weekly 
    Parameters: (None) 


    Job       : OCM InterMedia Index Synchronizing for Attribute Bundles 
    Executable: IBCSYNCATTR 
    Frequency : Weekly 
    Parameters: (None) 


    Job       : Purge Debug Log and System Alerts FNDLGPRG 
    Frequency : Weekly 
    Parameters: (Date – 30 days)


    Job       : Purge FND_STATS History Records 
    Executable: FNDPGHST 
    Frequency : Weekly 
    Parameters: DATE, 01-Jan-00, 30-JUN-11 


    Job       : Purge Obsolete Generic File Manager Data 
    Executable: FNDGFMPR Daily 
    Parameters: Y, , 


    Job       : Purge Obsolete Workflow Runtime Data FNDWFPR 
    Frequency : Weekly
    Parameters: , , 60, PERM, N, 500, N Weekly , , 60, TEMP, N, 500, N 


    Job       : Purge Signon Audit data FNDSCPRG 
    Frequency : Weekly 
    Parameters: (Date – 60 days)


    Job       : Workflow Agent Activity Statistics Concurrent Program  
    Executable: FNDWFAASTATCC 
    Frequency : Daily 
    Parameters: (None) 


    Job       : Workflow Background Process 
    Executable: FNDWFBG 
    Frequency : 12 HOURS 
    Parameters: , , , N, N, Y 
    Frequency : 1 HOUR
    Parameters: , , , N, Y, N 
    Frequency : 5 MINUTES 
    Parameters: , , , Y, N, N 


    Job       : Workflow Control Queue Cleanup 
    Executable: FNDWFBES_CONTROL_QUEUE_CLEANUP 
    Frequency :  12 HOURS 
    Parameters: (None) 


    Job       : Workflow Directory Services User/Role Validation
    Executable: FNDWFDSURV 
    Frequency : Daily 
    Parameters: 10000, Y, Y, Y 


    Job       : Workflow Mailer Statistics Concurrent Program
    Executable: FNDWFMLRSTATCC 
    Frequency : Daily 
    Parameters: (None) 


    Job       : Workflow Work Items Statistics Concurrent Program
    Executable: FNDWFWITSTATCC 
    Frequency : Daily 
    Parameters: (None)  


    Enjoy!

  • Tablespace Free Space and Max Size

    Earlier, provided a small script to find the maximum growable size of a tablespace… Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk…All size is in MB.

    SELECT a.tablespace_name TSNAME, 
        ROUND(SUM(a.growable)/1048576) growable, 
        ROUND(SUM(a.tots)/1048576) Tot_Size,
        ROUND(SUM(a.sumb)/1048576) Tot_Free,
        ROUND(SUM(a.sumb)*100/sum(a.tots)) Pct_Free,
        ROUND(SUM(a.largest)/1048576) Large_Ext
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, 0 growable
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY rollup(a.tablespace_name)
ORDER by a.tablespace_name

    Enjoy!