All Forums Analytics
19 Nov 2007
Regarding Space Management In Teradata

I am unable to Create the Table is Edwtest_Ads_Temp .but it is allowing to insert the new rows. I have checked the space .Still 879 Mb Space is available.And even no amp is full.I have used following queries to get the conclusion.1)sel sum(maxpermspace),from DBC.DataBaseSpace where Databaseid='00007408'XB;( '00007408'XB is Database id for the database I am referring) Result:=2122317760.00 Bytes 2) sel sum( currentperm) from DBC.TableSize where databasename='Edwtest_ads_Temp' ;Result= 1200550400.00 BytesSo Sum Of Current Perm Space is Less than Maxpermspace3)sel max(currentperm) from DBC.TableSize group by vproc where databasename='Edwtest_ads_Temp' ;In above query Max Of currentpermspace of each amp is less than the Maxpermspace/80(80 is number of amps)Amp is full only when it reaches =26528972=2122317760.00(Max Perm Space)/80Amp number which is occupied highest space is 50 I.e. 10132992.00 Bytescould you please clarify

dnoeth 4628 posts Joined 11/04
19 Nov 2007

Hi Praneet,DBC.TableSize accessed in your third query reports currentperm per table per AMP.So those 10132992 Bytes on AMP 50 are used by the largest table within that database, because you used MAX instead of SUM:"SUM(currentperm) from DBC.TableSize group by vproc"Better useselect max(currentperm), max(maxperm) from dbc.diskspace where databasename='Edwtest_ads_Temp'to see if a single AMP is close to the limit.Dieter

Dieter

20 Nov 2007

I have seen the DDl of Diskspace it is taking only tables which are having tableid=DBSpace.TableID = '000000000000'XBThis tableid belongs to Table name ALL.Could you explain why should we consider only those table which belongs to ALL?

dnoeth 4628 posts Joined 11/04
20 Nov 2007

Hi Praneet,dbc.databasespace is the base table used by dbc.tablesize, dbc.diskspace and dbc.allspaceThe ALL table is a dummy and represents all tables within a database -> dbc.diskspaceIt's a bit confusing because dbc.databasespace and dbc.allspace include info on both the table level and the database level within the same result set.Dieter

Dieter

20 Nov 2007

in the first reply you said I should have taken Sum(currentpermspace) instead of max(currentpermspace).But from the second queruy I would be know the current Perm space.There I am taking sum(CurrentPermspace).I should get error (No More room in Database) only when1.Cuurent Perm Space reaches the MaxPerm Space2. If there is any amp is full.3) if dbc has no space.To verify First two I have used these three queries.My first quey will check how much space is allocated to that database second query will check how much currentperm space is beeen utilised.Third query will check for the whther amp is full?Anywas dbc has space because I am able to create a table in other database.

Fred 1096 posts Joined 08/04
20 Nov 2007

No. As Dieter indicated, you get the "No more room in database" error when the database CurrentPerm on any AMP reaches the MaxPerm for that AMP. None of your three queries is testing that condition. Dieter's suggested check of dbc.DiskSpace does.

21 Nov 2007

Yes I understood.Thanks a lot to dnoeth and Fred Pluebell

rbasu26 1 post Joined 06/14
26 Jun 2014

Hi All,
Can anybody please tell me what is the significance of tablename "ALL" in dbc.allspace?

Fred 1096 posts Joined 08/04
26 Jun 2014

Please start a new topic for new questions.
Rows with Tablename "All" are track database space. Consider using views DBC.TableSizeV and DBC.DiskSpaceV.

aspiratech 2 posts Joined 07/14
04 Jul 2014

Can you explain how you arrange space in Teredata, i am getting confused how to get my data correct place, please suggest me some ideas

You must sign in to leave a comment.