RE: TEMPORARY TABLES

From: Suhen Pather <Suhen.Pather_at_strandbags.com.au>
Date: Wed, 09 Jan 2002 21:25:21 -0800
Message-ID: <F001.003EBD5A.20020109205519@fatcity.com>

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<font size=3 color=navy

face="Times New Roman">Peter,

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>           

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>            <font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-bidi-font-weight: bold'>When a temporary table is created, a dictionary definition of the table is also created.

<span

style='font-size:10.0pt;font-family:Arial;mso-bidi-font-weight:bold'>Space is not actually allocated for the table until it is populated<font size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial; color:navy'>

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>           

<span

style='font-size:10.0pt;font-family:Arial;color:navy'>GTT tables are not dropped when a session or transaction ends, the session specific data is only deleted (truncated)           

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>            The table definition still exists and temporary segments are cleaned up.

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<span

style='font-size:10.0pt;font-family:Arial;color:navy'>Regards Suhen

<span

style='font-size:10.0pt;font-family:Arial;mso-bidi-font-weight:bold'> 

<span

style='font-size:12.0pt'>
<span style='font-size:10.0pt;

font-family:sans-serif'>Hi

I
am working on some development here and have decided to use temporary tables to do some work as they seemed to be the best way to acheive what I wanted and the data is only required for the sesion.

I
seem to be experiencing a problem where the only way the temporary tables are removed is by actually dropping them or by  bouncing the database.

I
thought that they were supposed to be session specific or program module specific.

Perhaps
I misundestood the docs

I
am creating them as follows

v_CreateString
:= 'CREATE GLOBAL TEMPORARY TABLE t_tmp_cal1 ( part NUMBER, readings NUMBER) ON COMMIT PRESERVE ROWS';                 EXECUTE
IMMEDIATE  v_CreateString;
and

v_CreateString
:= 'CREATE GLOBAL TEMPORARY TABLE t_tmp_cal2 ( part NUMBER) ON COMMIT DELETE ROWS';        
EXECUTE
IMMEDIATE v_CreateString;  

Oracle8i
Enterprise Edition Release 8.1.7.0.0 - Production

I have looked at metalink and found nothing specific to point the way

Any
help appreciated

<table class=MsoNormalTable border=0 cellpadding=0 width=582 style='width:436.5pt;
 mso-cellspacing:1.5pt;margin-left:.5in'>     

  <span style='font-size:
  12.0pt;font-family:"Courier New"'> 
  --

---

  Peter McLarty               E-mail:
  Peter.Mclarty_at_mincom.com
  Technical Consultant        WWW: http://www.mincom.com   APAC Technical Services     Phone: +61 (0)7 3303 3461   Brisbane,  Australia        Mobile: +61 (0)402 094   238
                       
       Facsimile: +61 (0)7 3303 3048

---

  A great pleasure in life is doing what people say you cannot do.   

     - Walter Bagehot (1826-1877 British Economist)

---

  Mincom "The People, The Experience, The Vision"   

---

     

  <span
  style='font-size:12.0pt'>     

<span

style='font-size:12.0pt'>

<span style='mso-ansi-font-size:12.0pt;

mso-bidi-font-size:12.0pt'>--
<span style='mso-ansi-font-size:12.0pt;

mso-bidi-font-size:12.0pt'>This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Received on Wed Jan 09 2002 - 23:25:21 CST