We are using version 11.2.0.4 version of oracle. As per our current design for reports we have plsql procedures written to populate global temporary tables and the plsql procedure contains lot of logic inside it to transform the data and it does populate some intermediate global temporary tables inside the logic and finally it populates the final global temporary table which is used by the report tool to fetch data out of it and write to files which then sent to customers. So in this way we endup generating multiple same reports at same time for different customers without any overlap.
Initially i was under assumption that it must be some bad execution plan having hash join spilling to temp, but that was not the issue, these are inserts into global temporaray table. Currently the issue which we are facing is , all these data residing in the intermediate global temporaray table stays in the TEMPSPACE area through out the report execution and during big customers data for larger duration(2-3 month duration) these record count increases 150million+ and these temp space usage bumps up to 80GB+ for single report and if we run few big customer report triggers simultaneously as per their pre defined schedule, we endup getting error out due to tempspace full. And i think this must also be slowing down the read of data by reports from final global temporary tables, as because the data resides in tempspace which is nothing but disk, so reads from disk going to be slow. So i need expert advice if we can opt any alternate solution to fix this high tempspace usage issue?