Find Temp Tables used by Application Engine

I've been running into situations where our application engines are not clearing the temp tables even after process instance has been deleted.

Also, multiple occurrences of the process halting for no reason.

Oracle's workaround is to clear the temp tables and restart the process.

But which temp tables do you clear?

Try this script.

The criteria on AE_APPLID is where you place your AE program name.

SET SERVEROUTPUT ON;

DROP TABLE PS_LON_TEMP_TABLES;

CREATE TABLE PS_LON_TEMP_TABLES (DESCR VARCHAR2(30) NOT NULL) TABLESPACE PLLARGE STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80;

GRANT ALL ON PS_LON_TEMP_TABLES TO SYSADM;

DROP TABLE PS_LON_TEMP_FINAL;

CREATE TABLE PS_LON_TEMP_FINAL (DESCR VARCHAR2(30) NOT NULL) TABLESPACE PLLARGE STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80;

GRANT ALL ON PS_LON_TEMP_FINAL TO SYSADM;

DELETE FROM PS_LON_TEMP_TABLES;

DELETE FROM PS_LON_TEMP_FINAL;

COMMIT;

INSERT INTO PS_LON_TEMP_TABLES (DESCR)
SELECT '%' || RECNAME || '%' FROM PSAEAPPLTEMPTBL;
--WHERE AE_APPLID = 'FS_JGEN'; 

COMMIT;

DECLARE
  CURSOR cur_marlon IS
  SELECT DESCR FROM PS_LON_TEMP_TABLES;

  v_string VARCHAR2(30);

BEGIN
  OPEN cur_marlon;
  LOOP
    FETCH cur_marlon INTO v_string;
    EXIT WHEN cur_marlon%NOTFOUND;

    INSERT INTO PS_LON_TEMP_FINAL
    SELECT REPLACE(A.TABLE_NAME, 'PS_', '') FROM DBA_TABLES A WHERE A.OWNER = 'SYSADM' AND A.TABLE_NAME LIKE v_string AND NOT EXISTS (SELECT B.RECTYPE FROM PSRECDEFN B WHERE B.RECNAME = REPLACE(A.TABLE_NAME, 'PS_', '') AND RECTYPE <> 7);
  END LOOP;
  CLOSE cur_marlon;
END;