15

I am using Teradata. In that I am getting 'no more spool space in Database'. My database utilization is 85%. Is there any relationship between this error and DB utilization factor ? Any studies on this would be more helpful for me to resolve this. Share me your ideas to avoid this.

user1601052
  • 181
  • 2
  • 2
  • 7
  • 1
    avoid it by simplifying your sql. you can also ask your teradata administrator to grant you more spool space. – Beth Sep 18 '12 at 15:21
  • 1
    Spool space is a shared resource with limits placed upon users at the Profile or User level. Furthermore, spool space for a given user is shared across ALL active sessions. The one thing that caught my eye was your indication that the Database Utilization was at 85%. Could you clarify what this 85% accounts for. Is this 85% of the available perm space on the entire system or just a specific database? Bob Duell gave a good explanation as to where to begin to look for problems with a particular query that is reporting a lack of spool space. – Rob Paller Sep 18 '12 at 17:40

4 Answers4

10

Spool space problems occur either when you have an inefficient query or when statistics have not been properly collected on the tables you are using. It can also happen with tables where the primary index was poorly chosen (high skew). Spool is an attribute of the user account you are using to connect to the Teradata environment; it is not really an attribute of the database itself.

The only way to know for certain is to look at the EXPLAIN plan for your query.

If your query is inefficient, rewrite it. If statistics need to be collected or if the index needs to be altered, contact the DBA responsible for the tables you are using.

If there is a particular query that is giving you an "out of spool" error, update this question with the complete text of the query.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • Thank you all.@Rob paller 85% is available used perm space in my database,not entire system. – user1601052 Sep 20 '12 at 16:21
  • Bob duell--I have done collect statistics on my tables.But i dnt know exactly What this will exactly do? Can u explain.And how can i check queries performance with 'explain' ? – user1601052 Sep 20 '12 at 16:25
2

I was not able to resolve my "out of spool" error by the methods above. I resolved the error by moving a rank function into its own small table without any join or extraneous columns.

JT Lehman
  • 21
  • 1
1

Spool space can occur when you use tables having large data. If you are using multiple tables, check if you are using alias names instead of referring the complete table. Using alias names actually narrows down the data by the joins. Also see if functions like oreplace which consume more data are being used. Try using regular expressions in that case.

Veda Chaithanya Deepika
  • 66
  • 3
0

Eventually, you might create too low spool space.

You need to specify a new value for SPOOL in a MODIFY PROFILE or MODIFY USER statement, depending on where the user spool is defined. The syntax is:

MODIFY [PROFILE profile_name | USER user_name ] AS SPOOL = 100000000 bytes ;
msangel
  • 9,895
  • 3
  • 50
  • 69
  • Of course, but the OP is probably an end user and he will not be able to modify his spool, only the DBA can :-) – dnoeth Mar 24 '21 at 18:39