Do you think it is normal ?
Yes, it may be perfectly normal.
Would you share some thoughts on this?
If you worry about those 200G, then you can get back that space :
- create a new much smaller temporary tablespace
- assign it to users as default temporary tablespace (this can be done in one shot)
- drop the 200G TS (you may have to wait a bit for the drop to succeed, if it's in use).
P.S. probably your new TS will grow again....
1 person found this helpful
it could be normal as it depends of the amount of sorting and aggregation as you have mentioned. you can view and monitor the sessions while the process is running for your reference.
there is no need to re size your tablespace as you will be running this process frequently. one thing you need to analyze is your storage capacity of the database.
Global temporary tables are created on Temporary tablespaces, and also temporary tablespaces are used by Sorting,Aggregation and Hash joins.
you should keep using Global temporary tables. it also useful in terms of reducing redo generation.
Thanks you for your response.
I think you didn't get the whole picture. One Answer I got that it is normal. Thank you for that.
I am aware about how to resize the Tablespace. But the question here is, does 250gb size of database required the 200 GB of TEMP Tablespace. Once my report completed these space is automatically release from temp TS.
Now let me flash on real glitch.
The query we are running here is generated by MicroStrategy query builder tool and it has numerous joins on temporarily created tables ( Heap tables ) which are very huge in size.
Can I rely on MicroStrategy Query Tool or I have to re-build all those query manually?
Is there any thing which can improve in this process that do not generate huge temp/Sort data and increase performance?