Actual vs Effective space in Teradata

Just imagine even after having 100 GB free you get an error,"No more room in database". Strange, isn't it? 

In Teradata, even after having free space in your database it might get this error"No more room in database". Reason behind is Actual vs Effective Database space for table. Cause of Skew Factor table can take more space than its size. 

e.g. In administrator for any table it will show 5 gb  as Actual Space(Current Perm) but it might take 10gb as Effective Space from your database. So even if it looks that you have ample amount free space you might be running out of space.

Below is the query for finding of actual vs Effective space along with WasteSpace and Skew factor.

SELECT DatabaseName, TableName                   ,SUM(CurrentPerm) /1024**3  AS ActualSpaceGB ,MAX(CurrentPerm)*(HASHAMP()+1) /1024**3  EffectiveSpaceGB ,EffectiveSpaceGB-ActualSpaceGB  AS WasteSpaceGB ,100*((EffectiveSpaceGB-ActualSpaceGB)/ EffectiveSpaceGB) AS "WasteSpace%" ,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) AS SkewFactor FROM DBC.TableSize WHERE DatabaseName='DatabasName' GROUP BY 1,2 ORDER BY 5 DESC

Revisit and redefine your indexes and your issue will be resolved. :)

-B.regards,

Kiran