Have a look at this thread
Important thing to note is that when temp tablespace usage increases, new extents are allocated to the temp files and these extents are not de-allocated again (until instance/database is restarted). So your way to "alert" you when tempfile is 75% used, might not be a good idea.
I would suggest you to create 2 separate temporary tablespaces for both of these users performing 2 different tasks and assign these temp to each of them respectively. This will help you to monitor the temp usage more efficiently.
Now, will the 2users division help the temp file not filling up ?
No. Temp usage is related to the work required by your sessions executing the queries and not related to the number of users. Only way to stop temp filling is to tune your query in a way that it runs efficiently with minimum required data fetching during execution.
AWR report does not actually show any significant useful information regarding temp tablespaces because excessive temp usage is usually symptom rather than the problem.
We also want to automate this process that when temp file is filled upto 85% of original , then mail would be sent to requested users by script.
Use OEM to send you alerts for this. Otherwise you can use following query to see the current temp usage (This query was copied from some website - not my code)
SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE", a.sid||','||a.serial# SID_SERIAL, a.username, a.program, a.module FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks;
I agree with what Salman said and i suggest the you also check this note
Any further help is most welcome.
Kindly mark the post as helpful/correct upon your judge to Help others to find the answer.
Thanks Salman for your thoughts.
I believe we do not have OEM on our server side but still I would double check and come back.There is client side DBA to manage but some how this idea of having 2 diff user's initially stuck our mind. But how will oracle come to know that loading has started and simultaneously reporting is to be done.
For Ex :
User Load => Temp A
User Report => Temp B
And Temp A + Temp B would be the combined size of Temp ? Is this what you mean ?
On the automated script, need is of plsql procedure that would be part of our database and send the required mail when temp would be 85% of its original value.
Any pointers on this will help me sort this thing out.
As these alert facilities are in the dbms_alert package which is part of database control, and can be managed through OEM, please try to read documentation.
You already have this facility (and pay for it by your license) so please do not try to reinvent the wheel.
Senior Oracle DBA