Spaces

Space Terminology

	Perm	Spool	Temp
Max	The maximum number of bytes available for table, index and permanent journal storage in a database or user	A value used to limit the number of bytes the system will consume to create spool files for a user	A value used to limit the number of bytes the system will use to store data for global temporary tables for a user
Current	The total number of bytes in use to store the tables, subtables, and permanent journals contained in the database or user	The number of bytes currently in use for running transactions	The number of bytes in use for global temporary tables
Peak	The largest number of bytes actually used to store data in this user since the value was last reset	The maximum number of bytes used by a transaction run for this user since the value was last reset	The maximum number of bytes used by global temporary tables for a user since the value was last reset

Space Limits:

• Perm and spool space limits are assigned at the database or user level and not at table level
• When creating users or databases, perm space limits are deducted from the available space of the immediate owner
• The spool and temp space limits should not exceed that of the immediate owner at the time of you create the object
• If you do not specify the spool or temp limit, the new object inherits the limit from its immediate owner

Example of space terminology:

Lets say we have a user HR with MaxPerm space 25 MB, MaxSpool space 50 MB and MaxTemp space 30 MB. Now HR creates two users HR01 and HR02 with 6 MB, 4 MB MaxPerm space respectively and assigning 35 MB MaxSpool space to HR01, as there is spool assigned to HR02, it spool space defaults to its immediate parent MaxSpool space i.e. 50 MB. Now HR is left with 15 MB of MaxPerm space. Later HR drops HR01, HR MaxPerm space increases to 21 MB, since it regains Perm space fro HR01. As there is no MaxTemp space assigned to HR01 and HR02 it is defaulted to their immediate parent to 30 MB.

Giving one user to another:

• When you give one object to another object in the hierarchy, all the space allocated to the object goes with it. If you drop the object the space goes to the immediate owner
• When you give database or user, all descendants (child) of the given object remain descendants (child) of the given object
• When you give an object to a new parent, the ownership of space is transferred  and limits remain the same

Related Posts