Re: dropping global temp tables

  • From: Daniel Fink <Daniel.Fink@xxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 29 Jul 2004 14:13:02 -0600

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@xxxxxxxxxxx
>
> 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@xxxxxxxxxxx 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@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: