Truncating empty temporary tables in Oracle for PeopleSoft applications

Truncating temporary tables is an effective way of resetting the high-watermark level in Oracle databases.

The following script just truncates those tables that are empty, as sometimes temporary tables containing data should not be deleted as the data may belong to processes in error.

DECLARE

   sqlstatement    VARCHAR2 (100);

   fulltablename   VARCHAR2 (40);

   t_count         NUMBER;

   CURSOR c1

   IS

      SELECT owner || '.' || table_name

        FROM all_tables a

       WHERE EXISTS (

                SELECT 'x'

                  FROM psrecdefn b

                 WHERE b.rectype = 7

                   AND a.table_name LIKE 'PS_' || b.recname || '%');

BEGIN

   OPEN c1;

   LOOP

      FETCH c1

       INTO fulltablename;

      EXIT WHEN c1%NOTFOUND;

      sqlstatement :=

            'select count(*) from dual where exists (select NULL from '

         || fulltablename

         || ')';

      EXECUTE IMMEDIATE sqlstatement

                   INTO t_count;

      IF t_count = 1

      THEN

         DBMS_OUTPUT.put_line ('WARNING: ' || fulltablename || ' has data, so it will not be truncated.');

      ELSE

         sqlstatement := 'truncate table ' || fulltablename;

         EXECUTE IMMEDIATE sqlstatement;

         DBMS_OUTPUT.put_line (fulltablename || ' was truncated');

      END IF;

   END LOOP;

END;

Note: the script should be run when no process is running, as it is not blocking the tables after checking if they are empty. So, between the check and the truncate, someone may insert data. In any case, changing the script to lock the table should not be difficult.