RE: Global Temporary Table Mystery
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 08:20:03 -0400
Mark & Graeme,
You got the wrong guy. I'm not having the problem. Peter is.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Wednesday, June 23, 2004 7:55 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Global Temporary Table Mystery
I was intrigued by what is meant early in the thread by "The temporary
tablespace for the application in question is TEMP."
Last I checked, users had temporary tablespaces.
I think it's time for a query into dba_users to find out which users are
using SYSTEM for the temporary tablespace and you will probably find that
either the logon or some stored procedure creator or table creator is on
that list.
Good luck!
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Mercadante, Thomas F
Sent: Wednesday, June 23, 2004 7:49 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: 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@xxxxxxxxxx]
Sent: Tuesday, June 22, 2004 5:59 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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: