Code Example. Truncate Table

============================================
General - Code Example for Truncating table

============================================
Code Example for Truncating table

============================================
Not Partitioned Table


============================================
Code Example for Truncating regular table

FUNCTION TRUNCATE_TABLE(p_table_name IN VARCHAR,

                        p_rerun_limit IN NUMBER, 
                        p_sleep_sec IN NUMBER) RETURN NUMBER IS

  v_status        NUMBER;
  v_rerun_ind     NUMBER;  
  v_rerun_counter NUMBER;  
  v_sql_str       VARCHAR2(1000);
  v_msg_str       VARCHAR2(1000);
  v_module_name   VARCHAR2(30);
BEGIN
  v_module_name := 'TRUNCATE_TABLE';
  v_rerun_counter := 1;
  v_rerun_ind := 1;  
  v_sql_str := 'TRUNCATE TABLE '||p_table_name;
  write_sga_w_log(v_module_name,'Before Execution of :'||v_sql_str);
  
  WHILE v_rerun_ind = 1 LOOP
  
    BEGIN
      EXECUTE IMMEDIATE v_sql_str;
 v_rerun_ind := 0;
 v_status := 0;
    EXCEPTION
      WHEN OTHERS THEN
   v_msg_str := 'Attempt #'||TO_CHAR(v_rerun_counter)||' Failed. Oracle Error: '||SQLERRM;
        write_sga_w_log(v_module_name,v_msg_str);
DBMS_LOCK.sleep(p_sleep_sec);
   v_rerun_counter := v_rerun_counter + 1;
v_status := -1;
IF v_rerun_counter > p_rerun_limit THEn
 v_rerun_ind := 0;
END IF;
    END;
  END LOOP;

  v_msg_str := 'Procedure Finished with Status : '||TO_CHAR(v_status) ||' After 'TO_CHAR(v_rerun_counter)||' Attempts.';
  write_sga_w_log(v_module_name,v_msg_str);
  RETURN v_status;

EXCEPTION
  v_msg_str := 'Unexpected Exception in Procedure '||v_module_name||'. Error Details: '||SQLERRM;
  write_sga_w_log(v_module_name,v_msg_str);
  v_status := -1;
  RETURN v_status;
  
END TRUNCATE_TABLE;

============================================
Partitioned Table


============================================

Code Example for Truncating partitioned table

  PROCEDURE TRUNCATE_SGA_W_EVENTS(p_days_to_keep IN NUMBER) IS

    v_sql_str      VARCHAR2(1000);

    v_base_sql_str VARCHAR2(1000);    

    v_module_name  VARCHAR2(30);

    v_msg_text     VARCHAR2(1000);

    v_max_days     NUMBER(2);

    CURSOR get_partitions_cur (pc_max_days IN NUMBER, pc_days_to_keep IN NUMBER) IS

    SELECT partition_name 

      FROM USER_TAB_PARTITIONS

     WHERE table_name = 'SGA_W_EVENTS'     

       AND  (TO_NUMBER(SUBSTR(partition_name,3)) > TO_NUMBER(TO_CHAR(SYSDATE,'DD'))) AND (TO_NUMBER(SUBSTR(partition_name,3)) < TO_NUMBER(TO_CHAR(SYSDATE,'DD'))+(pc_max_days)) 

     UNION ALL

     SELECT partition_name

       FROM USER_TAB_PARTITIONS 

      WHERE table_name = 'SGA_W_EVENTS'     

       AND  (TO_NUMBER(SUBSTR(partition_name,3)) < TO_NUMBER(TO_CHAR(SYSDATE,'DD')) -pc_days_to_keep);

  BEGIN

    v_module_name := 'TRUNCATE_SGA_W_EVENTS';

    

    v_msg_text  := 'Procudure Starting';

    WRITE_SGA_W_LOG(v_module_name,v_msg_text);    

    v_max_days := TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'DD'))-p_days_to_keep;

    

    v_base_sql_str := 'ALTER TABLE SGA_W_EVENTS TRUNCATE PARTITION XXX';    

    v_msg_text  := 'Base SQL: '||v_base_sql_str;

    WRITE_SGA_W_LOG(v_module_name,v_msg_text);

    

    FOR get_partitions_rec IN get_partitions_cur(v_max_days,p_days_to_keep) LOOP

      v_sql_str := REPLACE(v_base_sql_str,'XXX',get_partitions_rec.partition_name);

      EXECUTE IMMEDIATE v_sql_str;

      v_msg_text := 'Partition  '||get_partitions_rec.partition_name||' Truncated.';

      WRITE_SGA_W_LOG(v_module_name,v_msg_text);

    END LOOP;

    v_msg_text  := 'Procudure Finished Successfully';

    WRITE_SGA_W_LOG(v_module_name,v_msg_text);

  EXCEPTION

    WHEN OTHERS THEN

      v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);

      WRITE_SGA_W_LOG(v_module_name,v_msg_text);

      DBMS_OUTPUT.put_line(v_msg_text );

  END TRUNCATE_SGA_W_EVENTS;

SELECT 'ALTER TABLE '||table_name||' TRUNCATE SUBPARTITION '||subpartition_name||';' FROM USER_TAB_SUBPARTITIONS WHERE table_name = 'TABLE_NAME';

SELECT 'ALTER TABLE '||table_name||' TRUNCATE PARTITION '||partition_name||';' 

FROM USER_TAB_PARTITIONS WHERE table_name = 'TABLE_NAME'

For Example:

ALTER TABLE TABLE_NAME TRUNCATE SUBPARTITION P_65_S_20230605;

ALTER TABLE TABLE_NAME TRUNCATE PARTITION P_65;

Code Example. Multi Delete from several Tables.

General
PL/SQL block to DELETE, UPDATE, INSERT many rows in several a Tables using FORALL.

===================================

Code

===================================

PROCEDURE MultiDeleteBulk(p_return_value OUT NUMBER,
                          p_chunk_size   IN NUMBER) IS

----------------------------------------------------------------------------------
-- Propose : Delete from Several tables in Bulk.
----------------------------------------------------------------------------------
  TYPE ROWID_RESULT_TABLE  IS TABLE OF ROWID                        INDEX BY BINARY_INTEGER;
  TYPE IMSI_RESULT_TABLE   IS TABLE OF SGA_W_SUBSCRIBER.imsi%TYPE   INDEX BY BINARY_INTEGER; 
  TYPE MSISDN_RESULT_TABLE IS TABLE OF SGA_W_SUBSCRIBER.msisdn%TYPE INDEX BY BINARY_INTEGER;

  v_rowid_result    ROWID_RESULT_TABLE;
  v_imsi_result     IMSI_RESULT_TABLE;
  v_msisdn_result   MSISDN_RESULT_TABLE;
    
  v_row_count      NUMBER;
  v_rows_to_delete NUMBER;

  v_date_to_check  DATE;
v_module_name    VARCHAR2(30);

BEGIN
  v_return_value := 0;
  v_module_name := 'MultiDeleteBulk';

  WRITE_TO_LOG(v_module_name, 'Procedure Started: ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

  IF p_chunk_size < 10000 THEN
    v_rows_to_delete := 10000
  ELSE
    v_rows_to_delete := p_chunk_size;
  END IF;    
  WRITE_TO_LOG('PurgeHomeBound', 'Deleting in chunks of : '||v_rows_to_delete);

  v_date_to_check := SYSDATE-7;
  WRITE_TO_LOG(v_module_name, 'Deleting Rows older than: '||TO_CHAR(v_date_to_check,'YYYYMMDD hh24:mi:ss'));

  -- Select the rows to be deleted.
  SELECT /*+ PARALLEL (SGA_W_SUBSCRIBER 6) */
         SGA_W_SUBSCRIBER.imsi, 
         SGA_W_SUBSCRIBER.msisdn, 
         SGA_W_SUBSCRIBER.ROWID 
    BULK COLLECT
    INTO v_imsi_result, v_msisdn_result, v_rowid_result
    FROM SGA_W_SUBSCRIBER
   WHERE is_roaming = 0
     AND non_purge_ind = 0
     AND GREATEST(NVL(ts_e_last_lu, ts_last_roamer_event), NVL(ts_last_roamer_event, ts_e_last_lu)) <  v_date_to_check
     AND ROWNUM < (v_rows_to_delete + 1); 

    --Delete from Table A
    FORALL rowidIndex IN NVL(v_rowid_result.FIRST, 1) .. NVL(v_rowid_result.LAST,0)
    DELETE FROM SGA_W_SUBSCRIBER
    WHERE ROWID = v_rowid_result(rowidIndex);

    v_row_count := SQL%ROWCOUNT;
    WRITE_TO_LOG(v_module_name, 'Number of rows Deleted from IPN_W_SRL_CONTROL: ' || v_row_count);                               

    --Delete from Table B
    FORALL imsiIndex IN NVL(v_imsi_result.FIRST, 1) .. NVL(v_imsi_result.LAST,0)
    DELETE FROM IPN_W_SRL_CONTROL
    WHERE imsi = v_imsi_result(imsiIndex);
      
    v_row_count := SQL%ROWCOUNT;
    WRITE_TO_LOG(v_module_name, 'Number of rows Deleted from IPN_W_SRL_CONTROL: ' || v_row_count);                               

    --Delete from Table C
    FORALL msisdniIndex IN NVL(v_msisdn_result.FIRST, 1) .. NVL(v_msisdn_result.LAST,0)
    DELETE FROM SGA_W_NUMBERS
    WHERE SGA_W_NUMBERS.mobile_number = v_msisdn_result(msisdniIndex);

    v_row_count := SQL%ROWCOUNT;
    WRITE_TO_LOG(v_module_name, 'Number of rows Deleted from SGA_W_NUMBERS: ' || v_row_count);                               
 
    COMMIT;
    
    WRITE_TO_LOG(v_module_name,'Number of rows deleted: ' || TO_CHAR(P_RetValue));
    WRITE_TO_LOG(v_module_name,'Completed Successfully at: '||TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

EXCEPTION
  WHEN OTHERS THEN
    p_return_value := -1;
    RAISE_APPLICATION_ERROR(-20000, v_module_name||' '||'Unexpected Error: ' || SQLCODE || SQLERRM);
END MultiDeleteBulk;

Code Example. Keep last data from a huge table.

========================
General
========================
Code example, of keeping only 7 days old data from a huge table, using temp table during the process.

CREATE TABLE MY_TABLE_BAK AS SELECT * FROM MY_TABLE WHERE 1=2;

ALTER TABLE MY_TABLE_BAK NOLOGGING;

INSERT /*+ APPEND */ INTO MY_TABLE_BAK SELECT * FROM MY_TABLE WHERE MY_TABLE.ts_last_modified > SYSDATE - 7;

COMMIT;

TRUNCATE TABLE MY_TABLE;

INSERT /*+ APPEND */ INTO MY_TABLE SELECT * FROM MY_TABLE_BAK;

COMMIT;

DROP TABLE MY_TABLE_BAK;


========================
PL/SQL code example

========================
This is a similar logic, in PL/SQL code, only more generic, with a job.

The code is split to four files:
admin_util_header.sql
admin_util_body.sql
permissions
create job

admin_util_header.sql
CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL;

  PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2);
  PROCEDURE purgeOldData (p_table_name IN VARCHAR2, p_days_to_keep IN NUMBER);
  

END ADMIN_UTIL;

admin_util_body.sql

CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL AS

-- GRANT UNLIMITED TABLESPACE TO 

-- GRANT CREATE TABLE TO 

--SELECT * FROM SGA_W_LOG_VW WHERE procedure_name = 'ADMIN_UTIL.purgeOldData';

--CREATE OR REPLACE VIEW SGA_W_LOG_VW AS 

--SELECT * FROM 

--( SELECT PROCEDURE_NAME,data,TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss') ts_last_modified

--FROM SGA_W_LOG WHERE ts_last_modified > SYSDATE-1 ORDER BY ts_last_modified DESC) 

--WHERE ROWNUM < 21;

-----------------------------------------------------------

  PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS

  PRAGMA AUTONOMOUS_TRANSACTION;

  BEGIN

    INSERT INTO SGA_W_LOG (procedure_name, data, ts_last_modified)

    VALUES (p_module_name, p_msg_text, SYSDATE);

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      NULL;

  END WRITE_SGA_W_LOG;   

-----------------------------------------------------------

--==============================================================

  PROCEDURE testBlock IS

    v_long_number_in  VARCHAR2(30);

    v_long_number     VARCHAR2(30);

  BEGIN

    v_long_number_in := '#D0043676227830464';

    

    IF SUBSTR(v_long_number_in, 1, 4) = '#D00' THEN

      v_long_number := SUBSTR(v_long_number_in, 5);

    ELSIF SUBSTR(v_long_number_in, 1, 2) = '00' THEN

      v_long_number := SUBSTR(v_long_number_in, 3);

    ELSE  

      v_long_number := v_long_number_in;

    END IF;

    

  END testBlock; 

--==============================================================

FUNCTION TRUNCATE_TABLE(p_table_name IN VARCHAR,p_rerun_limit IN NUMBER, p_sleep_sec IN NUMBER) RETURN NUMBER IS

  v_status        NUMBER;

  v_rerun_ind     NUMBER;  

  v_rerun_counter NUMBER;  

  v_sql_str       VARCHAR2(1000);

  v_msg_str       VARCHAR2(1000);

  v_module_name   VARCHAR2(30);

BEGIN

  v_module_name := 'TRUNCATE_TABLE';

  v_rerun_counter := 1;

  v_rerun_ind := 1;  

  v_sql_str := 'TRUNCATE TABLE '||p_table_name;

  write_sga_w_log(v_module_name,'Before Execution of :'||v_sql_str);

  

  WHILE v_rerun_ind = 1 LOOP

  

    BEGIN

      EXECUTE IMMEDIATE v_sql_str;

   v_rerun_ind := 0;

   v_status := 0;

    EXCEPTION

      WHEN OTHERS THEN

     v_msg_str := 'Attempt #'||TO_CHAR(v_rerun_counter)||' Failed. Oracle Error: '||SQLERRM;

        write_sga_w_log(v_module_name,v_msg_str);

   DBMS_LOCK.sleep(p_sleep_sec);

     v_rerun_counter := v_rerun_counter + 1;

   v_status := -1;

   IF v_rerun_counter > p_rerun_limit THEN

     v_rerun_ind := 0;

   END IF;

    END;

  END LOOP;

  v_msg_str := 'Procedure Finished with Status : '||TO_CHAR(v_status) ||' After '||TO_CHAR(v_rerun_counter)||' Attempts.';

  write_sga_w_log(v_module_name,v_msg_str);

  RETURN v_status;

EXCEPTION

  WHEN OTHERS THEN 

    v_msg_str := 'Unexpected Exception in Procedure '||v_module_name||'. Error Details: '||SQLERRM;

    write_sga_w_log(v_module_name,v_msg_str);

    v_status := -1;

    RETURN v_status;

  

END TRUNCATE_TABLE;

 

 

  PROCEDURE purgeOldData (p_table_name IN VARCHAR2, p_days_to_keep IN NUMBER) IS

    v_table_name        VARCHAR2(30);

    v_table_backup      VARCHAR2(30);

    v_sql_str           VARCHAR2(1000);

    v_msg_text          VARCHAR2(1000);

    v_module_name       VARCHAR2(30);

    v_status            NUMBER;

    v_time_stamp_field  VARCHAR2(30);

v_step              VARCHAR2(60);

    

  BEGIN

    v_module_name := 'ADMIN_UTIL.purgeOldData';

    v_table_name  := p_table_name;

    v_table_backup := SUBSTR(p_table_name,1,26)||'_BAK';

    

    IF v_table_name = 'TEST_DBA_TABLES' THEN

      v_time_stamp_field := 'last_analyzed';

    ELSIF v_table_name = 'IPN_IBR_CELL_REPORT_DATA' THEN  

      v_time_stamp_field := 'ts_start';    

    ELSE

      v_sql_str := 'Unknown table in purgeOldData code. Please Edit procedure!!! Terminating Execution';

      WRITE_SGA_W_LOG(v_module_name,v_sql_str);      

      RETURN;

    END IF;    

    

    BEGIN  

 DBMS_LOCK.sleep(2);

      v_sql_str := 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 600';

 v_step :=SUBSTR(v_sql_str,1,60);

      WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

      EXECUTE IMMEDIATE v_sql_str;

      WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');

    EXCEPTION

      WHEN OTHERS THEN

      WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');  

 RAISE;

    END;

    

    BEGIN

 DBMS_LOCK.sleep(2);

      v_sql_str := 'DROP TABLE '||v_table_backup;

 v_step :=SUBSTR(v_sql_str,1,60);

      WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

      EXECUTE IMMEDIATE v_sql_str;

 WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');

      

    EXCEPTION

      WHEN OTHERS THEN        

        WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');          

    END;

    BEGIN 

      DBMS_LOCK.sleep(2);

      v_sql_str := 'CREATE TABLE '||v_table_backup||' AS SELECT * FROM '||v_table_name||' WHERE 1=2';

 v_step :=SUBSTR(v_sql_str,1,60);

      WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

      EXECUTE IMMEDIATE v_sql_str;

      WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');

    EXCEPTION

      WHEN OTHERS THEN        

        WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');  

        RAISE;

    END;

    BEGIN  

 DBMS_LOCK.sleep(2);

      v_sql_str := 'ALTER TABLE '||v_table_backup||' NOLOGGING';

 v_step :=SUBSTR(v_sql_str,1,60);

      WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

      EXECUTE IMMEDIATE v_sql_str;

      WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');

    EXCEPTION

      WHEN OTHERS THEN        

        WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');  

        RAISE;

    END;

    BEGIN

 DBMS_LOCK.sleep(2);

      v_sql_str := 'INSERT /*+ APPEND */ INTO '||v_table_backup||' SELECT * FROM '||v_table_name||' WHERE '||v_table_name||'.'||v_time_stamp_field||' > SYSDATE - '||TO_CHAR(p_days_to_keep);

 v_step :=SUBSTR(v_sql_str,1,60);

      WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

      EXECUTE IMMEDIATE v_sql_str;

      WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');

      COMMIT;

    EXCEPTION

      WHEN OTHERS THEN        

        WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');  

        RAISE;

    END;

 

    BEGIN

 DBMS_LOCK.sleep(2);

      v_sql_str := 'TRUNCATE TABLE '||v_table_name;

 v_step :=SUBSTR(v_sql_str,1,60);

      WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

      v_status := TRUNCATE_TABLE(v_table_name,10,10);

      WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');

    EXCEPTION

      WHEN OTHERS THEN        

        WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');  

        RAISE;

    END;

    

    BEGIN  

 DBMS_LOCK.sleep(2);  

      v_sql_str := 'INSERT /*+ APPEND */ INTO '||p_table_name||' SELECT * FROM '||v_table_backup;

 v_step :=SUBSTR(v_sql_str,1,60);

      WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

      EXECUTE IMMEDIATE v_sql_str;

      WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');

      COMMIT;

    EXCEPTION

      WHEN OTHERS THEN        

        WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');  

        RAISE;

    END;

BEGIN

 DBMS_LOCK.sleep(2);

      v_sql_str := 'DROP TABLE '||v_table_backup;

 v_step :=SUBSTR(v_sql_str,1,60);

      WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

      EXECUTE IMMEDIATE v_sql_str;

      WRITE_SGA_W_LOG(v_module_name, v_sql_str||' Done');

    EXCEPTION

      WHEN OTHERS THEN        

        WRITE_SGA_W_LOG(v_module_name, v_step||' Failed');          

    END;

    DBMS_LOCK.sleep(2);

    v_sql_str := 'PURGE_OLD_DATA From Table '||v_table_name||' is Completed';

v_step :=SUBSTR(v_sql_str,60);

    WRITE_SGA_W_LOG(v_module_name, 'Running '||v_sql_str);

  

  EXCEPTION

    WHEN OTHERS THEN

      v_msg_text := 'Error in procedure '||v_module_name||' in step: '||v_step||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);

      WRITE_SGA_W_LOG(v_module_name,v_msg_text);

      DBMS_OUTPUT.put_line(v_msg_text );  

      

  END purgeOldData;

END ADMIN_UTIL;

permissions

GRANT CREATE TABLE TO  XXX;

create job

DECLARE

   v_job_number NUMBER(10);

BEGIN

 DBMS_JOB.SUBMIT (JOB => v_job_number, 

                  WHAT => 'ADMIN_UTIL.purgeOldData (''IPN_IBR_CELL_REPORT_DATA'', 2);', 

                  NEXT_DATE => TRUNC(SYSDATE + 1)+6/24+30/1440, 

                  INTERVAL => 'TRUNC(SYSDATE + 1)+6/24+30/1440'

 );

 COMMIT;

END;

/

Code Example for batch calling sqlplus

======================
General
======================
This is a simple code example, for batch script calling a general run_sql.bat file, which is calling a sqlplus.

======================
Files List
======================
refresh_updates.bat
run_sql.bat
refresh_updates.sql
REFRESH_UPDATES Procedure

======================
Files Content
======================

refresh_updates.bat
ECHO OFF
setlocal
cls

SET SQL_FILE=refresh_updates.sql

ECHO.
ECHO =============================================
ECHO refresh_updates.bat is starting
ECHO =============================================
ECHO.

SET DB_NAME=my_user/my_pass@orainst1
ECHO Running %SQL_FILE% on DB %DB_NAME%
call run_sql.bat %DB_NAME% %SQL_FILE%
ECHO Done
ECHO.


SET DB_NAME=my_user/my_pass@orainst2
ECHO Running %SQL_FILE% on DB %DB_NAME%
call run_sql.bat %DB_NAME% %SQL_FILE%
ECHO Done
ECHO.


ECHO.
ECHO =============================================
ECHO refresh_updates on has finished
ECHO =============================================

SET /p temp="Enter to Close this Window"

run_sql.bat
SET db_name=%1
SET sql_file_name=%2

sqlplus -s %db_name% @%sql_file_name% 

refresh_updates.sql

SET TERMOUT ON

SET SHOW OFF

SET VERIFY OFF 

SET HEAD ON

SET LINE 500

SET FEEDBACK ON

SET PAGES 500

SET TRIMS ON

BEGIN

  REFRESH_UPDATES;

END;

/

EXIT;

REFRESH_UPDATES Procedure

CREATE OR REPLACE PROCEDURE REFRESH_UPDATES IS

BEGIN

  DBMS_STATS.flush_database_monitoring_info;

END;

USER_TAB_MODIFICATIONS and DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO by Example

=========================================
General 
=========================================
How to see all updated tables in database?

=========================================
Theory
=========================================

USER_TAB_MODIFICATIONS lists DML operations on a table.

There is also ALL_TAB_MODIFICATIONS and SYS.DBA_TAB_MODIFICATIONS
(But there is no DBA_TAB_MODIFICATIONS synonym)

This is an SQL showing DML operations during last 2 days:

SELECT TABLE_NAME, INSERTS ,UPDATES,DELETES, TRUNCATED, DROP_SEGMENTS,TIMESTAMP   
  FROM USER_TAB_MODIFICATIONS 
 WHERE timestamp > SYSDATE -2 
 ORDER BY timestamp DESC ; 

TABLE_NAME              INSERTS    UPDATES    DELETES TRUNCATED DROP_SEGMENTS TIMESTAMP
-------------------- ---------- ---------- ---------- --------- ------------- -----------
GSM_MSC_PREFIXES            259          6         33 NO                    0 03/05/2016
SH_REFRESH_LOG              775        775          0 NO                    0 03/05/2016
MLOG$_GSM_MSC_PREFIXE     10405       8163       4584 NO                    0 03/05/2016
MLOG$_SHORT_CODES          3229       3219         39 NO                    0 03/05/2016
MLOG$_SHORT_CODE_NET       3081       3057       2325 NO                    0 03/05/2016
MLOG$_HSC_GATE_SC        778649     542725     517226 NO                    0 03/05/2016
MLOG$_GSM_NDC_PREFIXES   195356     193049     145975 NO                    0 03/05/2016
SHORT_CODE_NETWORK           39          2          7 NO                    0 03/05/2016
SHORT_CODES                   9         11          3 NO                    0 03/05/2016
HSC_GATE_SC               16644       2194      11806 NO                    0 03/05/2016
GSM_NDC_PREFIXES           4578        133        215 NO                    0 03/05/2016
SGA_W_LOG                   189          0          0 NO                    0 03/05/2016
SUPPORT_CNT_LOG             581          0          0 NO                    0 02/05/2016

13 rows selected

=========================================
Example
=========================================

Now lets perform Update on another table:

UPDATE DB_INSTALLED_VERSIONS

   SET app_upgrade_id = 881

 WHERE app_name = 'IG2-SA DB' AND app_upgrade_id = 81;

1 row updated

SQL> commit;

Commit complete

EXECUTE DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT TABLE_OWNER, 

       TABLE_NAME, 

       INSERTS ,

       UPDATES, 

       DELETES,

       (inserts+updates+deletes) total_dml

  FROM DBA_TAB_MODIFICATIONS 

WHERE table_owner NOT IN ('SYS')

ORDER BY total_dml;

TABLE_OWNER         TABLE_NAME                INSERTS   UPDATES    DELETES  TOTAL_DML

----------------- --------------------------- -------- -------- ---------- ----------

CHE_SWISS_IPNQQ   DB_PROC_SFI_SUBSCRIBER_TEMP 10066349        0   10066112   20132461

CHE_SWISS_IPNQQ   SGA_SUBSCRIBER_SFI           6657283  3409066    6671100   16737449

CHE_SWISS_IPNQQ   GA_W_COUNTERS_HISTORY        4493598        0    6459166   10952764

CHE_SWISS_IPNQQ   SGA_W_IPN_SUBSCRIBER          139831 10432759     230121   10802711

CHE_SWISS_IPNQQ   SGA_W_PSMS_SUBSCRIBER          11461  3465714       8500    3485675

CHE_SWISS_IPNQQ   SGA_W_SUBS_CAMP_HISTORY       103602        0      41595     145197

CHE_SWISS_IPNQQ   IPN_W_ACTUAL_DISTRIBUTION_A    65373        0      65369     130742

CHE_SWISS_IPNQQ   IPN_W_ACTUAL_DISTRIBUTION_B    61619        0      61611     123230

CHE_SWISS_IPNQQ   REP_DAILY_DNORM                42570    22719      39100     104389

CHE_SWISS_IPNQQ   REP_MONTHLY_DNORM              52101      396      51391     103888

CHE_SWISS_TSTQQ   REP_DAILY_DNORM                20616    20616      20616      61848

COL TABLE_OWNER FOR A30

COL TABLE_NAME FOR A30

SELECT TABLE_OWNER, 

       TABLE_NAME, 

       INSERTS ,

       UPDATES, 

       DELETES, 

       TRUNCATED, 

       DROP_SEGMENTS,

       TIMESTAMP   

  FROM DBA_TAB_MODIFICATIONS 
 WHERE timestamp > SYSDATE -2 
 ORDER BY timestamp DESC ; 

TABLE_NAME              INSERTS    UPDATES    DELETES TRUNCATED DROP_SEGMENTS TIMESTAMP
-------------------- ---------- ---------- ---------- --------- ------------- -----------
GSM_MSC_PREFIXES            259          6         33 NO                    0 03/05/2016
SH_REFRESH_LOG              775        775          0 NO                    0 03/05/2016
MLOG$_GSM_MSC_PREFIXE     10405       8163       4584 NO                    0 03/05/2016
MLOG$_SHORT_CODES          3229       3219         39 NO                    0 03/05/2016
MLOG$_SHORT_CODE_NET       3081       3057       2325 NO                    0 03/05/2016
MLOG$_HSC_GATE_SC        778649     542725     517226 NO                    0 03/05/2016
MLOG$_GSM_NDC_PREFIXES   195356     193049     145975 NO                    0 03/05/2016
SHORT_CODE_NETWORK           39          2          7 NO                    0 03/05/2016
SHORT_CODES                   9         11          3 NO                    0 03/05/2016
HSC_GATE_SC               16644       2194      11806 NO                    0 03/05/2016
GSM_NDC_PREFIXES           4578        133        215 NO                    0 03/05/2016
SGA_W_LOG                   189          0          0 NO                    0 03/05/2016
SUPPORT_CNT_LOG             581          0          0 NO                    0 02/05/2016

13 rows selected


The updated table does not appear in USER_TAB_MODIFICATIONS.
Why...???

Checking on the status of table DB_INSTALLED_VERSIONS, it does not differ from another table, that does appear in the list.

SELECT table_name, status, last_analyzed, global_stats, user_stats, monitoring
  FROM USER_TABLES 
 WHERE table_name = 'DB_INSTALLED_VERSIONS';

TABLE_NAME                     STATUS   LAST_ANALYZED GLOBAL_STATS USER_STATS MONITORING
------------------------------ -------- ------------- ------------ ---------- ----------
DB_INSTALLED_VERSIONS          VALID    04/03/2014 22 YES          NO         YES
GSM_MSC_PREFIXES               VALID    22/07/2015 21 YES          NO         YES


What does USER_TAB_MODIFICATIONS  report for table GSM_MSC_PREFIXES but does not report for DB_INSTALLED_VERSIONS?


=========================================
Permission
=========================================
To be able execute user must have 
"ANALYZE ANY" permission, and not "EXECUTE ON DBMS_STATS", as one might expect.

For example:
sqlplus / as sysdba
GRANT ANALYZE ANY TO some_user;
=========================================
Solution
=========================================
To get to see DML operation, need to execute:

BEGIN

 DBMS_STATS.flush_database_monitoring_info;

END;

/

PL/SQL procedure successfully completed

Now there is a lot more information...:
SELECT TABLE_NAME, INSERTS ,UPDATES,DELETES, TRUNCATED, DROP_SEGMENTS,TIMESTAMP   
  FROM USER_TAB_MODIFICATIONS 
 WHERE timestamp > SYSDATE -2 
ORDER BY timestamp DESC ; 

TABLE_NAME            INSERTS    UPDATES    DELETES    TRUNCATED DROP_SEGMENTS TIMESTAMP
---------------------  ---------- ---------- ---------- --------- ------------- ---------
REPORT_PLMN_NDC                1          0          0 NO                    0 04/05/2016
DB_INSTALLED_VERSIONS         45          5          0 NO                    0 04/05/2016
SH_REFRESH_LOG               806        806          0 NO                    0 04/05/2016

REPORTS_PLMN_NDC            7467          0          0 NO                    0 04/05/2016

=========================================

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

=========================================

This procedure flushes in-memory monitoring information for all tables in the dictionary. 

Corresponding entries in the:

*_TAB_MODIFICATIONS

*_TAB_STATISTICS

*_IND_STATISTICS

 views are updated immediately, without waiting for the Oracle database to flush them periodically. 

This procedure is useful when you need up-to-date information in those views. 

Because the GATHER_*_STATS procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics.