All Forums Database
Jawed_Khan 6 posts Joined 03/10
06 May 2010
Temporary Tables in Teradata

Hi All,

Theoretically I know that Temporary Tables require Temp Space only.
Now, I have a DB where Perm space is in MB's say 30 MB and Temp Space is 1000 GB
But while creating Temp tables the DB is giving error "2644: No more room in Database"
Can anyone help ?

Regards
JK

CarlosAL 332 posts Joined 04/08
07 May 2010

From de docco:

"Although space usage for materialized global temporary tables is charged to temporary space, creating the global temporary table definition requires an adequate amount of permanent space."

HTH.

Cheers.

Carlos.

Subhash.Gander 21 posts Joined 07/09
07 May 2010

Jawed,

For storing data GT is dependent on temp space.
But DDL of GT or any other table consumes permanent space.

so as carlos said you need to have adequate amount of permanent space.

Jawed_Khan 6 posts Joined 03/10
11 May 2010

Thanks........

JK

Jawed_Khan 6 posts Joined 03/10
11 May 2010

One more thing Guys......
When I create View it doesn't take any permanent space.
But if I base my logic on the above reasons then view or any other non-perm occupying objects should also require some perm space for DDL.
But that is not the case since view doesn't require any....
Any justifications ?

Regards
JK

Jim Chapman 449 posts Joined 09/04
12 May 2010

A view does use a (usually) trivial amount of perm space due to dictionary rows. Since those rows are in system tables, DBC.TVM and DBC.TVFields, the space is charged to DBC.

The creation of a global temp table additionally causes a table header (minimum size 512 bytes) to be inserted on each AMP. This header encodes the table's metadata for fast access by the AMPs during DML operations. It is a trade-off of space usage for the benefit of efficient parallel processing.

You must sign in to leave a comment.