Thursday, April 11, 2013

PeopleSoft Application Engine Drop Temp Tables & re-Create

Several time based on the requirements we have to remove and rebuild the temp tables which are specified in AE.

Below are the steps I followed to delete.

1. Find out the temp tables which are used by specific table using below query.

select table_name from all_tables where table_name like '%ABCTAO3%';

2. Using below sql generate the Drop Script.

select 'DROP TABLE '||table_name||';' from all_tables where table_name like '%ABCTAO3%';

3. Execute the generated script in Database. I prefer to run these in DataMover.

Then build the table in Appdesigner.

Note:- Sometime after deleting temp tables from Database, I tried to build from AE the temp table again it doesnot create temp table instances. (How I know its created or not - look at the build script which generated). So I have to increase or decrease temp table instance count,save the AE and then change the instance count again back to original and save then Build the temp table.

for more information about temp table, you could follow the link too..