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 ?
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."
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.
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 ?
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.