Usually spool space level can be defined either at user or database level.since user will only be running queries,we need a temp. space to hold the intermediate table data but why you need a spool space level to be defined at database level and what is the use of defining it at database level?
you are right, there is little use of the spool limit for a database. One possible scenario is when you create a user right below that database in the database hierarchy, and don't specify the spool limit for that user, then the spool limit is inherited from the database to the user. Very rarely happens though.
Please remember that using profiles to manage spool limits is even a better practice than using user level limits.
Thanks Vlad,got it..
And one more thing,as per my understanding both temp. and spool space is the total unused space in the whole DB.
In that case,i came across the below scenario which could not prove the above rule.
Scenario:A volatile temporary table is created by joining two tables and the table is used in only once in the whole query.When i asked my fellow colleague why we cannot use the join directly in the query without creating a temp. table,and he told me that this would reduce the spool space if other tables in the same query could occupy more spool space.Careting temp.table in which way will reduce the spool space,since both are unused space in DB,if spool space is full then temp. space will also be full.
And i came through a post in the forum stating that creating temporary table will reduce the spool space.
Can u just tell how the above scenario will work..