Skip to Main Content
APEX Performance Oracle APEX and DGX Spark Clusters.

Oracle APEX 24.2 Internal Table Defragmentation

APEX PL/SQL SQL
Oracle Application Express 24.2 stores its metadata in a versioned engine schema (typically APEX_240200) along with companion schemas APEX_PUBLIC_USER, FLOWS_FILES, and APEX_LISTENER / APEX_REST_PUBLIC_USER. Over time — particularly after application imports/exports, workspace provisioning, developer activity logging, and APEX upgrades — the WWV_FLOW_* internal tables accumulate significant fragmentation. This guide provides the SQL and PL/SQL needed to: Discover the actual APEX engine schema dynamically in each PDB Identify which APEX internal tables are fragmented and worth reclaiming Defragment those tables using either an online method (no outage) or an outage method (faster, but requires APEX downtime) Warning: Never modify data in APEX internal tables directly. The procedures in this guide only reorganize physical storage — they do not alter any rows or metadata.

Sample

BEGIN
   apex_defrag.defrag_apex_online(
       p_execute         => TRUE,
       p_frag_threshold  => 10,
       p_min_reclaimable => 5
   );
END;
/

====================================================
APEX DEFRAg ENGINE (WITH LOGGING)
RUN ID: 20260414122432
MODE  : EXECUTE
====================================================
1. APEX_240200.WWV_FLOW_DATA | FRAG: 25.8 | MB: 3.309597015380859375
2. APEX_240200.WWV_FLOW_SESSIONS$ | FRAG: 40.8 | MB: 2.4488983154296875
ERROR: ORA-10631: SHRINK clause should not be specified for this object
3. APEX_240200.WWV_FLOW_PAGE_PLUGS | FRAG: 29.2 | MB: 2.3692760467529296875
4. APEX_240200.WWV_FLOW_STEP_ITEMS | FRAG: 22.5 | MB: 1.310394287109375
====================================================
TOTAL OBJECTS: 4
ERRORS       : 1
====================================================

OWNER           TABLE NAME                     ACTION           FRAG %      RECLAIM MB STATUS       END TIME
--------------- ------------------------------ --------------- ------- --------------- ------------ -------------------
APEX_240200     WWV_FLOW_STEP_ITEMS            SHRINK             22.5            1.31 SUCCESS      2026-04-14 12:24:32
APEX_240200     WWV_FLOW_PAGE_PLUGS            SHRINK             29.2            2.37 SUCCESS      2026-04-14 12:24:32
APEX_240200     WWV_FLOW_SESSIONS$             SHRINK             40.8            2.45 SUCCESS      2026-04-14 12:24:32
APEX_240200     WWV_FLOW_DATA                  SHRINK             25.8            3.31 SUCCESS      2026-04-14 12:24:32
APEX_240200     WWV_FLOW_STEP_ITEMS            SHRINK             23.4            1.37 SUCCESS      2026-04-14 11:59:44

Create User

CREATE USER apex_defrag IDENTIFIED BY "__PASSWORD__"
   DEFAULT TABLESPACE sysaux
   TEMPORARY TABLESPACE temp
   QUOTA UNLIMITED ON sysaux;

Assign Privileges

-- Basic connection privileges
GRANT CREATE SESSION TO apex_defrag;
GRANT CREATE PROCEDURE TO apex_defrag;
GRANT CREATE TABLE TO apex_defrag;

-- Dictionary access (required for defrag logic)
GRANT SELECT ANY DICTIONARY TO apex_defrag;

-- Object-level dictionary views (optional but commonly needed)
GRANT SELECT ON all_synonyms TO apex_defrag;

-- DBA views (required for your APEX defrag queries)
GRANT SELECT ON dba_tables TO apex_defrag;
GRANT SELECT ON dba_segments TO apex_defrag;
GRANT SELECT ON dba_tablespaces TO apex_defrag;

-- DML/DDL operations on target tables
GRANT ALTER ANY TABLE TO apex_defrag;
GRANT SELECT ANY TABLE TO apex_defrag;

Create Objects


CREATE TABLE apex_defrag.defrag_apex_log (
   run_id            NUMBER,
   owner             VARCHAR2(128),
   table_name        VARCHAR2(128),
   action_taken      VARCHAR2(50),
   frag_pct_before   NUMBER,
   reclaimable_mb    NUMBER,
   status            VARCHAR2(30),
   error_msg         VARCHAR2(4000),
   start_time        DATE,
   end_time          DATE
);
/

apex_defrag.defrag_apex_online

CREATE OR REPLACE PROCEDURE apex_defrag.defrag_apex_online (
   p_execute         IN BOOLEAN DEFAULT FALSE,
   p_frag_threshold  IN NUMBER  DEFAULT 20,
   p_min_reclaimable IN NUMBER  DEFAULT 10
)
AUTHID DEFINER
AS
   v_run_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'));
   v_count  NUMBER := 0;
   v_errors NUMBER := 0;
   ------------------------------------------------------------------
   CURSOR c_tables IS
       SELECT
           t.owner,
           t.table_name,
           t.iot_type,
           t.temporary,
           t.nested,
           s.bytes / 1024 / 1024 AS allocated_mb,
           (NVL(t.num_rows,0) * NVL(t.avg_row_len,0)) / 1024 / 1024 AS actual_mb,
           GREATEST(
               (s.bytes - NVL(t.num_rows,0) * NVL(t.avg_row_len,0)) / 1024 / 1024,
               0
           ) AS reclaimable_mb,
           ROUND(
               (s.bytes - NVL(t.num_rows,0) * NVL(t.avg_row_len,0))
               / NULLIF(s.bytes,0) * 100,
               1
           ) AS frag_pct
       FROM dba_tables t
       JOIN dba_segments s
         ON s.owner = t.owner
        AND s.segment_name = t.table_name
        AND s.segment_type = 'TABLE'
        AND t.owner IN ( SELECT DISTINCT table_owner 
                         FROM all_synonyms 
                         WHERE synonym_name = 'WWV_FLOW' AND owner = 'PUBLIC' 
                       )
       WHERE t.num_rows > 0
         AND s.bytes > p_min_reclaimable * 1024 * 1024
         AND ROUND(
               (s.bytes - NVL(t.num_rows,0) * NVL(t.avg_row_len,0))
               / NULLIF(s.bytes,0) * 100,
               1
         ) >= p_frag_threshold
       ORDER BY reclaimable_mb DESC;
   ------------------------------------------------------------------
   FUNCTION can_shrink(
       p_iot_type VARCHAR2,
       p_temp     VARCHAR2,
       p_nested   VARCHAR2
   ) RETURN BOOLEAN
   IS
   BEGIN
       IF p_iot_type IS NOT NULL THEN
           RETURN FALSE;
       END IF;
       IF p_temp = 'Y' THEN
           RETURN FALSE;
       END IF;
       IF p_nested = 'YES' THEN
           RETURN FALSE;
       END IF;
       RETURN TRUE;
   END;
   ------------------------------------------------------------------
   PROCEDURE log_result(
       p_owner   VARCHAR2,
       p_table   VARCHAR2,
       p_action  VARCHAR2,
       p_frag    NUMBER,
       p_mb      NUMBER,
       p_status  VARCHAR2,
       p_error   VARCHAR2
   ) IS
   BEGIN
       INSERT INTO apex_defrag.defrag_apex_log (
           run_id,
           owner,
           table_name,
           action_taken,
           frag_pct_before,
           reclaimable_mb,
           status,
           error_msg,
           start_time,
           end_time
       )
       VALUES (
           v_run_id,
           p_owner,
           p_table,
           p_action,
           p_frag,
           p_mb,
           p_status,
           p_error,
           SYSDATE,
           SYSDATE
       );
   END;
   ------------------------------------------------------------------
   PROCEDURE exec_sql(p_sql VARCHAR2) IS
   BEGIN
       EXECUTE IMMEDIATE p_sql;
   EXCEPTION
       WHEN OTHERS THEN
           v_errors := v_errors + 1;
           DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
   END;
BEGIN
   DBMS_OUTPUT.PUT_LINE('====================================================');
   DBMS_OUTPUT.PUT_LINE(' APEX DEFRAg ENGINE (WITH LOGGING)');
   DBMS_OUTPUT.PUT_LINE(' RUN ID: ' || v_run_id);
   DBMS_OUTPUT.PUT_LINE(' MODE  : ' || CASE WHEN p_execute THEN 'EXECUTE' ELSE 'REPORT' END);
   DBMS_OUTPUT.PUT_LINE('====================================================');
   ------------------------------------------------------------------
   FOR r IN c_tables LOOP
       v_count := v_count + 1;
       DBMS_OUTPUT.PUT_LINE(
           v_count || '. ' ||
           r.owner || '.' || r.table_name ||
           ' | FRAG: ' || r.frag_pct ||
           ' | MB: ' || r.reclaimable_mb
       );
       ------------------------------------------------------------------
       -- NOT SHRINKABLE
       ------------------------------------------------------------------
       IF NOT can_shrink(r.iot_type, r.temporary, r.nested) THEN
           log_result(
               r.owner,
               r.table_name,
               'SKIP',
               r.frag_pct,
               r.reclaimable_mb,
               'NOT_ELIGIBLE',
               NULL
           );
           CONTINUE;
       END IF;
       ------------------------------------------------------------------
       -- EXECUTE MODE
       ------------------------------------------------------------------
       IF p_execute THEN
           BEGIN
               exec_sql(
                   'ALTER TABLE "'||r.owner||'"."'||r.table_name||'" ENABLE ROW MOVEMENT'
               );
               exec_sql(
                   'ALTER TABLE "'||r.owner||'"."'||r.table_name||'" SHRINK SPACE CASCADE'
               );
               log_result(
                   r.owner,
                   r.table_name,
                   'SHRINK',
                   r.frag_pct,
                   r.reclaimable_mb,
                   'SUCCESS',
                   NULL
               );
           EXCEPTION
               WHEN OTHERS THEN
                   v_errors := v_errors + 1;
                   log_result(
                       r.owner,
                       r.table_name,
                       'SHRINK',
                       r.frag_pct,
                       r.reclaimable_mb,
                       'FAILED',
                       SQLERRM
                   );
           END;
       ELSE
           log_result(
               r.owner,
               r.table_name,
               'REPORT',
               r.frag_pct,
               r.reclaimable_mb,
               'SKIPPED',
               NULL
           );
       END IF;
   END LOOP;
   ------------------------------------------------------------------
   DBMS_OUTPUT.PUT_LINE('====================================================');
   DBMS_OUTPUT.PUT_LINE(' TOTAL OBJECTS: ' || v_count);
   DBMS_OUTPUT.PUT_LINE(' ERRORS       : ' || v_errors);
   DBMS_OUTPUT.PUT_LINE('====================================================');
   
   COMMIT ;
END;
/

Compile 

ALTER PROCEDURE apex_defrag.defrag_apex_online COMPILE;
SHOW ERRORS;

Run in REPORT mode 

SET SERVEROUTPUT ON LINESIZE 200 PAGESIZE 100
BEGIN
  apex_defrag.defrag_apex_online(
      p_execute         => FALSE,
      p_frag_threshold  => 20,
      p_min_reclaimable => 10
  );
END;
/

Run in EXECUTE mode (real defrag)

SET SERVEROUTPUT ON LINESIZE 200 PAGESIZE 100
BEGIN
  apex_defrag.defrag_apex_online(
      p_execute         => TRUE,
      p_frag_threshold  => 20,
      p_min_reclaimable => 10
  );
END;
/

Example tuned execution (production-safe)

SET SERVEROUTPUT ON LINESIZE 200 PAGESIZE 100
BEGIN
   apex_defrag.defrag_apex_online(
       p_execute         => TRUE,
       p_frag_threshold  => 30,   -- only heavily fragmented tables
       p_min_reclaimable => 50    -- only large wins
   );
END;
/

Example aggressive cleanup run

SET SERVEROUTPUT ON LINESIZE 200 PAGESIZE 100
BEGIN
   apex_defrag.defrag_apex_online(
       p_execute         => TRUE,
       p_frag_threshold  => 15,
       p_min_reclaimable => 5
   );
END;
/

Monitor Processing table


COLUMN run_id            FORMAT 9999999999        HEADING 'RUN ID'
COLUMN owner             FORMAT A15              HEADING 'OWNER'
COLUMN table_name        FORMAT A30              HEADING 'TABLE NAME'
COLUMN action_taken      FORMAT A15              HEADING 'ACTION'
COLUMN frag_pct_before   FORMAT 9990.0           HEADING 'FRAG %'
COLUMN reclaimable_mb    FORMAT 999,999,990.00   HEADING 'RECLAIM MB'
COLUMN status            FORMAT A12              HEADING 'STATUS'
COLUMN error_msg         FORMAT A40              HEADING 'ERROR MESSAGE'
COLUMN start_time        FORMAT A19              HEADING 'START TIME'
COLUMN end_time          FORMAT A19              HEADING 'END TIME'

SET SERVEROUTPUT ON LINESIZE 200 PAGESIZE 100

SELECT
   -- run_id,
   owner,
   table_name,
   action_taken,
   frag_pct_before,
   reclaimable_mb,
   status,
--  SUBSTR(error_msg,1,100) AS error_msg,
--  TO_CHAR(start_time,'YYYY-MM-DD HH24:MI:SS') AS start_time,
   TO_CHAR(end_time,'YYYY-MM-DD HH24:MI:SS') AS end_time
FROM apex_defrag.defrag_apex_log
ORDER BY run_id DESC;

– Track impact via segments

COLUMN owner        FORMAT A15  HEADING 'OWNER'
COLUMN segment_name FORMAT A40  HEADING 'SEGMENT NAME'
COLUMN mb           FORMAT 999,999,990.00 HEADING 'SIZE (MB)'
COLUMN segment_type FORMAT A15 HEADING 'TYPE'

SET SERVEROUTPUT ON LINESIZE 200 PAGESIZE 100

WITH data AS
   (
       SELECT
           owner,
           segment_type,
           segment_name,
           ROUND(bytes / 1024 / 1024,0 ) AS mb
       FROM
           dba_segments
       WHERE
           owner = 'APEX_240200'
       ORDER BY
           bytes DESC
   )
SELECT
   owner,
   segment_type,
   segment_name,
   mb
FROM DATA
WHERE
   ROWNUM <= 20
;

 
www.1apx.com
_