RE: Global Temporary Table Mystery

Then how did you come to the conclusion that the segments were being created
in SYSTEM? The only way this is possible is if the user performing
transactions against the GTT's has a TEMPORARY TABLESPACE set to SYSTEM.

Maybe you could post some sample output to clarify your point.


Regards,
Graeme.

-----Original Message-----
From: Mercadante, Thomas F [mailto:thomas.mercadante@xxxxxxxxxxxxxxxxx]
Sent: Wednesday, 23 June 2004 9:49 PM
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 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@xxxxxxxxxxxxx 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
-----------------------------------------------------------------
----------------------------------------------------------------
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 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: