| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Global Temporary Table Mystery
This is what I get also on 8.1.7.3 - no tablespace name is entered in
USER_TABLES.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Graeme Farmer [mailto:graeme.farmer_at_mincom.com]
Sent: Tuesday, June 22, 2004 5:59 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Global Temporary Table Mystery
Hey Peter,
Can you post the queries and output you are using? The following indicates behaviour contrary to that which you describe:
sys_at_ora817> create global temporary table gftmp (a number);
Table created.
sys_at_ora817> select tablespace_name from dba_tables where table_name =
'GFTMP';
TABLESPACE_NAME
1 row selected.
sys_at_ora817> select count(*) from dba_segments where segment_name = 'GFTMP';
COUNT(*)
0
1 row selected.
sys_at_ora817> insert into gftmp values (1);
1 row created.
sys_at_ora817> select tablespace_name from dba_tables where table_name =
'GFTMP';
TABLESPACE_NAME
1 row selected.
sys_at_ora817> select count(*) from dba_segments where segment_name = 'GFTMP';
COUNT(*)
0
1 row selected.
sys_at_ora817> select * from v$sort_usage ;
USER SESSION_ SESSION_NUM SQLADDR SQLHASH TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK#EXTENTS BLOCKS SEGRFNO#
------------------------------ -------- ----------- -------- ---------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ---------- SYS 500A8C5C 4 516AF2DC 2776179706 TEMP TEMPORARY DATA 6001 3817 1 16 1
1 row selected.
Regards,
Graeme.
-----Original Message-----
From: Peter Barnett [mailto:regdba_at_yahoo.com]
Sent: Wednesday, 23 June 2004 7:31 AM
To: Oracle-l
Subject: 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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Limited unless expressly stated otherwise. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org putReceived on Wed Jun 23 2004 - 06:45:29 CDT
'unsubscribe' in the subject line.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
 |
 |