Archives of the TeradataForum

Message Posted: Fri, 30 Aug 2002 @ 17:44:20 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Spool space is assigned to a user at creation time and that value can be altered for existing users. There is no formula to calculate it. Max spool used by a session (since the last DiskSpace table reset) is found in the DBC.DISKSPACE view.

Here is SQL to show you a user's max spool (amount allocated) and peak spool (highest amount used):

SELECT DATABASENAME (Title 'User')
      ,sum(MaxSpool) (format '---,---,---,---,--9')(char(19))(title 'max Spool        ')
      ,sum(PeakSpool) (format '---,---,---,---,--9')(char(19))(title 'Peak Spool        ')
      ,sum(Maxtemp) (format '---,---,---,---,--9')(char(19))(title 'max temp        ')
      ,sum(PeakTemp) (format '---,---,---,---,--9')(char(19))(title 'Peak Temp         ')
FROM DBC.DISKSPACE
WHERE DatabaseName = 'Joe_User'
ORDER BY 1
GROUP BY 1;

Here is SQL to give you information including spool and temporary space allocations for a user:

SELECT A.DatabaseName (CHAR(20))
, A.OwnerName (CHAR(20))
, A.AccountName (CHAR(20))
, B.DefaultDatabase (CHAR(20))
, (A.SpoolSpace / 1000000000) (NAMED Spool_In_gig)
, (A.TempSpace / 1000000000) (NAMED Temp_In_gig)
FROM DBC.DATABASES   A
   , DBC.DBASE   B
WHERE A.DatabaseName = B.DatabaseNamei
  AND A.DatabaseName = 'Joe_User'
ORDER BY 1;

Adam

	<Prev	Next>		<<First	<Prev	Next>	Last>>