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.
sqlstatement VARCHAR2 (100);
fulltablename VARCHAR2 (40);
SELECT owner || '.' || table_name
FROM all_tables a
WHERE EXISTS (
FROM psrecdefn b
WHERE b.rectype = 7
AND a.table_name LIKE 'PS_' || b.recname || '%');
EXIT WHEN c1%NOTFOUND;
'select count(*) from dual where exists (select NULL from '
EXECUTE IMMEDIATE sqlstatement
IF t_count = 1
DBMS_OUTPUT.put_line ('WARNING: ' || fulltablename || ' has data, so it will not be truncated.');
sqlstatement := 'truncate table ' || fulltablename;
EXECUTE IMMEDIATE sqlstatement;
DBMS_OUTPUT.put_line (fulltablename || ' was truncated');
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.