RE: Global Temporary Table Mystery
- From: Graeme Farmer <graeme.farmer@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 07:58:58 +1000
Hey Peter,
Can you post the queries and output you are using? The following indicates
behaviour contrary to that which you describe:
sys@ora817> create global temporary table gftmp (a number);
Table created.
sys@ora817> select tablespace_name from dba_tables where table_name =
'GFTMP';
TABLESPACE_NAME
------------------------------
1 row selected.
sys@ora817> select count(*) from dba_segments where segment_name = 'GFTMP';
COUNT(*)
----------
0
1 row selected.
sys@ora817> insert into gftmp values (1);
1 row created.
sys@ora817> select tablespace_name from dba_tables where table_name =
'GFTMP';
TABLESPACE_NAME
------------------------------
1 row selected.
sys@ora817> select count(*) from dba_segments where segment_name = 'GFTMP';
COUNT(*)
----------
0
1 row selected.
sys@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@xxxxxxxxx]
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.
=====
Pete Barnett
Lead Database Administrator
The Regence Group
pnbarne@xxxxxxxxxxx
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
