Re: dropping global temp tables

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Thu, 29 Jul 2004 14:13:02 -0600
Message-id: <41095A4E.37B8A0F@sun.com>

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_at_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_at_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_at_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
-----------------------------------------------------------------

Received on Thu Jul 29 2004 - 15:20:17 CDT