tamp tablespace in oracle

I am not a DBA so maybe I write couple of things false.

in our team everybody use same user for our production database to use all tables.

we build some big SQL queries, with WITH clause etc. If sort operation or immediate result could be stored in temp tablespace as far I know if there is no space in memory.

We are using Tableau so we need to generate couple of extracts which based on this big SQl Queries. in the last time we see that some of them fail because of the ORA-12801: error signaled in parallel query server P02Z ORA-01652: unable to extend temp segment by 128 in tablespace TEMP .

How does it work if same user run 2 SQL queries, they are in different sessions I think. does the SQLs use the same temp tablespace? I mean if the temp tablespace is set to 1 GB or so then both of them use this 1GB? In scenarions if both of them need to use 500MB it is highly probable that 3 queries will not have enough space and all of them will fail?

Solution

Yes, the TEMP tablespace is shared for all users. If it is 1GB then all users are sharing the same 1GB. If it cannot extend, then either:

The datafile is not set to auto-extend. In this case you can alter the file to extend itself automatically until it reaches the maximum size allowed by the OS or the file system runs out of space.
The datafile has reached its maximum size (usually 32GB) and cannot extend. In this case you can add another file to the tablespace, assuming there is still unused storage available.
The disk or partition holding the file is full. If this is the case, you can add a file to TEMP and locate it on a file system with space to grow. If you don't have any unused storage available on your server you will have to add more.

If you have no options to increase or extend the storage for TEMP, then you need to manage its use by tuning SQL to avoid it or limiting concurrent usage of particular reports or queries.

A little time spent tuning the SQL to minimize unnecessary sorting is generally a good idea, regardless.