| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Global Temporary Table Mystery
We have an application using Global Temporary Tables.
The other day I was checking performance questions and
noticed a number of this application's tables were in
the SYSTEM tablespace.
After consulting with the developer I made arrangements to export and import them into the correct tablespace. It now turns out that these are GTTs. I checked the table names in dba_segments and no space is currently allocated to these tables.
According to Tom Kyte:
When you create a temporary table -- it will not use
ANY tablespace. It will
use the TEMPORARY tablespace of the current schema
when accessed at runtime.
When you create a temp table -- no space allocated.
when you insert into temp
table, the space will be gotten from your temporary
tablespace (or if the
temporary table is used in a stored procedure with
definer rights - the
temporary tablespace of the owner of the table)
This is clearly not what happened. I have found other references that say the same thing. The temporary tablespace for the application in question is TEMP. It is a 40G tablespace that usually has a few gig free.
Any ideas as to why these GTTs may be using SYSTEM? It is an Oracle 8.1.7.3 database. The application was written in Oracle Forms.
 |
 |