From oracle-l-bounce@freelists.org Thu Jul 29 15:20:17 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6TKK2w15573 for ; Thu, 29 Jul 2004 15:20:12 -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 i6TKJp615526 for ; Thu, 29 Jul 2004 15:20:01 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9131872EC67; Thu, 29 Jul 2004 14:52:40 -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 22687-70; Thu, 29 Jul 2004 14:52:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ABFE372EC85; Thu, 29 Jul 2004 14:51:01 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Jul 2004 14:49:21 -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 8409C72EAFB for ; Thu, 29 Jul 2004 14:47:46 -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 21218-99 for ; Thu, 29 Jul 2004 14:47:46 -0500 (EST) Received: from nwkea-mail-1.sun.com (nwkea-mail-1.sun.com [192.18.42.13]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C168A72EAD3 for ; Thu, 29 Jul 2004 14:45:48 -0500 (EST) Received: from phys-giza-2 ([129.147.4.101]) by nwkea-mail-1.sun.com (8.12.10/8.12.9) with ESMTP id i6TKD7J6018237 for ; Thu, 29 Jul 2004 13:13:07 -0700 (PDT) Received: from conversion-daemon.giza-mail1.Central.Sun.COM by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) id <0I1M00L01QTR2T@giza-mail1.Central.Sun.COM> (original mail from Daniel.Fink@Sun.COM) for oracle-l@freelists.org; Thu, 29 Jul 2004 14:13:07 -0600 (MDT) Received: from sun.com (vpn-129-150-33-43.Central.Sun.COM [129.150.33.43]) by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTP id <0I1M008KNQTTBC@giza-mail1.Central.Sun.COM> for oracle-l@freelists.org; Thu, 29 Jul 2004 14:13:07 -0600 (MDT) Date: Thu, 29 Jul 2004 14:13:02 -0600 From: Daniel Fink Subject: Re: dropping global temp tables To: oracle-l@freelists.org Message-id: <41095A4E.37B8A0F@sun.com> MIME-version: 1.0 X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U) Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Accept-Language: en References: <154540478.20040729154308@gennick.com> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 6464 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Daniel.Fink@Sun.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Jonathan, Do you receive an error? Depending on how the table is created, I cannot drop the table if there are rows in it. Regards, Daniel Fink Example: SQL> create global temporary table gtt_1 (gtt_col number); Table created. SQL> desc gtt_1 Name Null? Type -------------------------------------------------------------------- -------- ------------------- GTT_COL NUMBER SQL> drop table gtt_1; Table dropped. SQL> desc gtt_1 ERROR: ORA-04043: object gtt_1 does not exist SQL> create global temporary table gtt_1 (gtt_col number); Table created. SQL> insert into gtt_1 values (1); 1 row created. SQL> select * from gtt_1; GTT_COL ---------- 1 SQL> drop table gtt_1; Table dropped. SQL> desc gtt_1; ERROR: ORA-04043: object gtt_1 does not exist SQL> create global temporary table gtt_1 (gtt_col number) on commit preserve rows; Table created. SQL> insert into gtt_1 values (1); 1 row created. SQL> select * from gtt_1; GTT_COL ---------- 1 SQL> commit; Commit complete. SQL> select * from gtt_1; GTT_COL ---------- 1 SQL> drop table gtt_1; drop table gtt_1 * ERROR at line 1: ORA-14452: attempt to create, alter or drop an index on temporary table already in use SQL> truncate table gtt_1; Table truncated. SQL> drop table gtt_1; Table dropped. SQL> desc gtt_1 ERROR: ORA-04043: object gtt_1 does not exist SQL> Jonathan Gennick wrote: > Sorry if this is the dumb question for the day, but how does > one go about dropping the definition for a global temp > table? DROP TABLE doesn't do the job for me. > > Best regards, > > Jonathan Gennick --- Brighten the corner where you are > http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com > > Join the Oracle-article list and receive one > article on Oracle technologies per month by > email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, > or send email to Oracle-article-request@gennick.com and > include the word "subscribe" in either the subject or body. > > ---------------------------------------------------------------- > 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 -----------------------------------------------------------------