From oracle-l-bounce@freelists.org Wed Jun 23 07:24:04 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5NCNnK16746 for ; Wed, 23 Jun 2004 07:23:59 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5NCNd616677 for ; Wed, 23 Jun 2004 07:23:49 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7215472C889; Wed, 23 Jun 2004 07:07:07 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11697-75; Wed, 23 Jun 2004 07:07:07 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 38EE172CA26; Wed, 23 Jun 2004 07:07:02 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 23 Jun 2004 07:05:17 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2971C72C872 for ; Wed, 23 Jun 2004 07:05:16 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11727-14 for ; Wed, 23 Jun 2004 07:05:15 -0500 (EST) Received: from blockhead.mincom.com (blockhead1.mincom.com [203.202.173.251]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 789F272C863 for ; Wed, 23 Jun 2004 07:05:13 -0500 (EST) Received: (from uucp@localhost) by blockhead.mincom.com (8.9.3/8.9.3) id WAA68549 for ; Wed, 23 Jun 2004 22:26:45 +1000 (EST) (envelope-from graeme.farmer@mincom.com) Received: from bnepms01.mincom.oz.au(172.17.65.84) via SMTP by blockhead.mincom.oz.au, id smtpdE68541; Wed Jun 23 22:26:39 2004 Received: from TQXBNECLU03.root.tequinox.com (unverified [172.31.4.22]) by bnepms01.mincom.oz.au (Content Technologies SMTPRS 4.3.12) with ESMTP id for ; Wed, 23 Jun 2004 22:26:29 +1000 Received: by tqxbneclu03.root.tequinox.com with Internet Mail Service (5.5.2657.72) id ; Wed, 23 Jun 2004 22:26:28 +1000 Message-ID: From: Graeme Farmer To: "'oracle-l@freelists.org'" Subject: RE: Global Temporary Table Mystery Date: Wed, 23 Jun 2004 22:26:27 +1000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3410 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: graeme.farmer@mincom.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org sys@gfprd> rollback; Rollback complete. -----Original Message----- From: Mercadante, Thomas F [mailto:thomas.mercadante@labor.state.ny.us] Sent: Wednesday, 23 June 2004 10:20 PM To: 'oracle-l@freelists.org' Subject: RE: Global Temporary Table Mystery 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@rsiz.com] Sent: Wednesday, June 23, 2004 7:55 AM To: oracle-l@freelists.org 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Mercadante, Thomas F Sent: Wednesday, June 23, 2004 7:49 AM To: 'oracle-l@freelists.org' 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@mincom.com] Sent: Tuesday, June 22, 2004 5:59 PM To: 'oracle-l@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@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@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. ===== Pete Barnett Lead Database Administrator The Regence Group pnbarne@regence.com __________________________________ 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@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 ----------------------------------------------------------------- -- 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@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- -- 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@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 -----------------------------------------------------------------