There are 3 users created under database which has PERM space 50 GB. Each user is allocated 5 GB PERM space & 20 GB Spool space.
Now, the PERM Space left in database- 35 GB.
I've created the another user for whom i allocated 20 GB PERM space.
Now, the PERM Space left in database- 15 GB ==> UNUSED space.
Even though i allocated 20 GB spool space to each user, if i submit a sql query which takes more than 15 GB should be allocated since the unused space is only 15 GB ?? Am i missing out some thing here?
SPOOL is the currently not used perm space on system level, but not on user/database level.
The spool on a database level is only used for inheritance:
When there's no spool assigned for a new user he inherits the owner's spool size.
And when there's spool it may not exceed the owner's size.
Thanks sir. Small clarification needed.
Let us say if only 20 GB un used PERM space left at SYSTEM level. And there are 2 users in a system. If i run queries in both the users simultaneously and each is expected to take more than 15 GB spool space. In this scenario one query has to be aborted right?
The system will run out of spool and return a 2507 error: "Out of spool space on disk", this probably affects all active queries.
I don't know if only a single or multiple sessions will be aborted, you usually try to avoid that problem :-)
Hi, I want to understand what unused PERM space is being used as a SPOOL space in a query. Supposed I have this query:
"Select * from A.1 left outer join B.1 where...."
Am I using the unused PERM of database A and B?
ulrich, please explain... why I have 5TB spool space in a user and it is accessing a table in a database with only 2TB unused space. The system has 576 amps. Based on the explain plan the query will be using at least 576G of spool.
If you will divide 2TB/576 amps, you will get 3.4GB. Could this be the reason why? because it is consuming all the least unused space per amp?
I am not sure that I understand your issue.
Yes, if you have 5TB asssigned to your user and you have 576 VPROCs then you have 3.4G per VPROC for spoolspace. So skew can be limiting your spoolspace! So if you want to use 5GB on one VPROC you get the out of spool error message even if total spool space is only 500GB at this point of time.
The 5TB are not limited to the free space in your DBs of your tables.
To be able to use the full 5 TB you need even distributed data AND 5TB of FREE cyclinders in the whole DB system.