Tuesday, May 17, 2011

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.

9 comments:

Cheap Wildcard SSL said...
This comment has been removed by a blog administrator.
Anonymous said...

Thank you friend for sharing this query to truncate temporary tables. Its really necessary to do this task on timely basis to improve the efficiency. I will also note down the point that you have mentioned above.
sap migration

sap basis said...

Thanks for the code. Adding a y/n option would be useful.

shiva max said...

Hi im sanjay sap peoplesoft consultant. I just browsing blogs there I found your blog is interesting.. thank for posting… keep on postingpeopeoplesoft-techical

john williams said...

Hi im sanjay sap peoplesoft consultant. I just browsing blogs there I found your blog is interesting.. thank for posting… peoplesoft-techical

PeopleSoft Journal said...

Good post. Really usefull.

Eva Lona said...

That was realy helpfull thanks.

Eva Lona said...

I was just browsing blogs there I found your blog is interesting.. thanks for posting… keep on posting mypeoplesoft

john bill said...

Great job, appreciated well post like it, see peoplesoft community