arc

1. Check archive generation by hour

SET LINES 200

SET PAGES 100

SELECT TO_CHAR(first_time,'DD-MON-YYYY HH24') AS archive_hour,

       COUNT(*) AS archive_count,

       ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) AS size_gb

FROM   v$archived_log

WHERE  first_time >= SYSDATE - 3

AND    dest_id = 1

GROUP BY TO_CHAR(first_time,'DD-MON-YYYY HH24')

ORDER BY archive_hour;

2. Check daily archive generation

SELECT TO_CHAR(first_time,'DD-MON-YYYY') AS archive_date,

       COUNT(*) AS log_count,

       ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) AS total_gb

FROM   v$archived_log

WHERE  first_time >= SYSDATE - 7

AND    dest_id = 1

GROUP BY TO_CHAR(first_time,'DD-MON-YYYY')

ORDER BY MIN(first_time);

3. Find recently growing objects in APPS_TS_TX_IDX

SET LINES 220

SET PAGES 100

COLUMN owner FORMAT A20

COLUMN segment_name FORMAT A45

COLUMN partition_name FORMAT A30

COLUMN segment_type FORMAT A20

SELECT *

FROM (

    SELECT owner,

           segment_name,

           partition_name,

           segment_type,

           ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb

    FROM   dba_segments

    WHERE  tablespace_name = 'APPS_TS_TX_IDX'

    ORDER BY bytes DESC

)

WHERE ROWNUM <= 30;

4. Check recent extent allocation

This helps identify objects that received new extents recently, provided auditing or historical views are available:

SELECT owner,

       segment_name,

       segment_type,

       partition_name,

       COUNT(*) AS extent_count,

       ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS allocated_gb

FROM   dba_extents

WHERE  tablespace_name = 'APPS_TS_TX_IDX'

GROUP BY owner,

         segment_name,

         segment_type,

         partition_name

ORDER BY allocated_gb DESC

FETCH FIRST 30 ROWS ONLY;

5. Check active sessions generating redo

Run the first query, wait 10–15 minutes, and run it again. Sessions with the largest increase are likely generating the redo.

SELECT s.sid,

       s.serial#,

       s.username,

       s.program,

       s.module,

       s.action,

       s.sql_id,

       ROUND(st.value / 1024 / 1024, 2) AS redo_mb

FROM   v$session s

JOIN   v$sesstat st

       ON st.sid = s.sid

JOIN   v$statname sn

       ON sn.statistic# = st.statistic#

WHERE  sn.name = 'redo size'

AND    s.username IS NOT NULL

ORDER BY st.value DESC

FETCH FIRST 30 ROWS ONLY;

6. Check EBS concurrent requests running during the issue

SELECT r.request_id,

       p.user_concurrent_program_name,

       r.phase_code,

       r.status_code,

       r.actual_start_date,

       r.actual_completion_date,

       ROUND((NVL(r.actual_completion_date, SYSDATE)

             - r.actual_start_date) * 24, 2) AS runtime_hours,

       r.oracle_process_id

FROM   apps.fnd_concurrent_requests r

JOIN   apps.fnd_concurrent_programs_vl p

       ON p.concurrent_program_id = r.concurrent_program_id

      AND p.application_id = r.program_application_id

WHERE  r.actual_start_date >= SYSDATE - 2

ORDER BY r.actual_start_date DESC;

7. Check tablespace usage

SELECT df.tablespace_name,

       ROUND(df.total_gb, 2) AS total_gb,

       ROUND(df.total_gb - fs.free_gb, 2) AS used_gb,

       ROUND(fs.free_gb, 2) AS free_gb,

       ROUND((df.total_gb - fs.free_gb) / df.total_gb * 100, 2) AS used_pct

FROM (

    SELECT tablespace_name,

           SUM(bytes) / 1024 / 1024 / 1024 AS total_gb

    FROM   dba_data_files

    WHERE  tablespace_name = 'APPS_TS_TX_IDX'

    GROUP BY tablespace_name

) df

JOIN (

    SELECT tablespace_name,

           SUM(bytes) / 1024 / 1024 / 1024 AS free_gb

    FROM   dba_free_space

    WHERE  tablespace_name = 'APPS_TS_TX_IDX'

    GROUP BY tablespace_name

) fs

ON fs.tablespace_name = df.tablespace_name;

Likely causes include a bulk data load, index rebuild, CREATE INDEX, heavy updates or deletes, statistics collection with index maintenance, clone/post-clone processing, interface imports, or a repeatedly failing concurrent program. The archive-

What to check next

1. Is debug logging enabled?

Check the profile options:

SELECT profile_option_name,

       profile_option_value

FROM apps.fnd_profile_option_values v,

     apps.fnd_profile_options p

WHERE v.profile_option_id = p.profile_option_id

AND p.profile_option_name LIKE 'AFLOG%';

Or from the EBS front end:

FND: Debug Log Enabled

FND: Debug Log Level

If enabled at Statement or Procedure, it can generate very large volumes of log records.

2. Which module is writing to FND_LOG_MESSAGES?

SELECT module,

       COUNT(*) cnt

FROM applsys.fnd_log_messages

WHERE timestamp >= SYSDATE - 3

GROUP BY module

ORDER BY cnt DESC;

3. Which workflow events are busiest?

SELECT event_name,

       COUNT(*)

FROM apps.wf_events

GROUP BY event_name

ORDER BY COUNT(*) DESC;

(If WF_EVENTS does not contain the required data, we can query the relevant runtime workflow tables instead.)

COL profile_level FOR A15

COL profile_option_value FOR A20

SELECT

    DECODE(level_id,

           10001,'SITE',

           10002,'APPLICATION',

           10003,'RESPONSIBILITY',

           10004,'USER',

           10005,'SERVER',

           TO_CHAR(level_id)) profile_level,

    level_value,

    profile_option_value

FROM apps.fnd_profile_option_values v,

     apps.fnd_profile_options p

WHERE v.profile_option_id = p.profile_option_id

AND p.profile_option_name = 'AFLOG_ENABLED';