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;

Views: 8

PeopleSoft Jobs in US

Groups

Videos

  • Add Videos
  • View All

© 2024   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service