Home » SQL & PL/SQL » SQL & PL/SQL » temporary table (10g)
temporary table [message #362980] Thu, 04 December 2008 13:31 Go to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

when a global temporary table is created, is it deleted at the close of the session?

or by default are only rows deleted in the table which the table itself exists?

is there anyway to ensure that the table is deleted at the end of the session?
Re: temporary table [message #362985 is a reply to message #362980] Thu, 04 December 2008 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
when a global temporary table is created, is it deleted at the close of the session?

No.

Quote:
or by default are only rows deleted in the table which the table itself exists?

Yes.

Quote:
is there anyway to ensure that the table is deleted at the end of the session?

DROP TABLE but this is NOT the purpose of a temporary table in Oracle which is to be reused and even used by several sessions at the same time.

Regards
Michel
Re: temporary table [message #363239 is a reply to message #362980] Fri, 05 December 2008 09:03 Go to previous messageGo to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

in my script i create temporary table. do some inserts etc and finally a select. then i do a drop table. but i get :

drop table tablespace_rep
*

ERROR at line 1:
ORA-00942: table or view does not exist


are there a kind of temporary tables in oracle which only exist for a session and automatically get dropped at the end of the session? if a table survives the session with or without rows what is "temporary" about it?
Re: temporary table [message #363245 is a reply to message #363239] Fri, 05 December 2008 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00942: table or view does not exist

Then the table does not exist. Do you REALLY have a table named tablespace_something?

Quote:
are there a kind of temporary tables in oracle which only exist for a session and automatically get dropped at the end of the session?

No.

Quote:
if a table survives the session with or without rows what is "temporary" about it?

Data are temporary.

Regards
Michel
Re: temporary table [message #363249 is a reply to message #362980] Fri, 05 December 2008 09:39 Go to previous messageGo to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

as you can see below i have the drop table right next to the select. the select works fine, but after that the drop gives an error that the table does not exist.

select ts_name "TABLESPACE_NAME",
status "Status", tssize "Size(MB)",
used "Used(MB)", free "Free(MB)",
(CASE
WHEN (free * 90/100)/maxspaceused IS NULL
OR (free * 90/100)/maxspaceused < 0 THEN free
ELSE (free * 90/100)/maxspaceused
END) "Days to fill 90%"
from tablespace_rep;

drop table tablespace_rep;

/
spool off
exit


--

i have these option on the top of the sql script. not sure if they are affecing it in anyway.

set linesize 200
set feedback off
set verify off
set termout off

set markup html on spool on
spool /tmp/space_report_&1..html
Re: temporary table [message #363252 is a reply to message #363249] Fri, 05 December 2008 09:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
as you can see below

I can see nothing as you didn't post any execution.

drop table tablespace_rep;

/

You execute twice the statement, so of course the second does not work.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: temporary table [message #363256 is a reply to message #362980] Fri, 05 December 2008 09:55 Go to previous messageGo to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

>You execute twice the statement

where am i doing it twice?

appreciate it if you could be more specific as i'm not very familiar with plsql.
Re: temporary table [message #363259 is a reply to message #363256] Fri, 05 December 2008 10:14 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
the forward slash executes the previously executed statement. therefore you run the drop, then the / runs the drop again.
Re: temporary table [message #363261 is a reply to message #362980] Fri, 05 December 2008 10:24 Go to previous message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

guys thanks again! works fine now!
Previous Topic: Procedure to fetch values from a select
Next Topic: user paramter @
Goto Forum:
  


Current Time: Thu Dec 08 16:23:39 CST 2016

Total time taken to generate the page: 0.26553 seconds