dropping global temp tables 2004-07-29 - By Daniel Fink
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 (See ORA-04043.ora-code.com): 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 (See ORA-04043.ora-code.com): 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 (See ORA-14452.ora-code.com): 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 (See ORA-04043.ora-code.com): 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@(protected)
>
> 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@(protected) 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@(protected)
> 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
