temporary table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: temporary table

  1. #1
    Join Date
    May 2002
    Posts
    35
    Hi friends

    could some out there tell me where the temporary tables get created .. when using the create global temporary.... command ... i wanted to know about the data storage for this .. like which tablespace , segments etc .. and in which scenarios it is used in an OLTP environment

    thanks

    sharmila

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    Temporary tables are created in users default temporary tablespace.There is no data storage parameters for temporary tablespace.Temporary tables are beneficial in OLTP environment as many computations take place on the client side and the temporary tables get dropped whenever the session closes.

    Regards,
    Rohit Nirkhe,Oracle/Apps DBA,OCP 8i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by rohitsn
    ... the temporary tables get dropped whenever the session closes.
    Temporary tables get dropped only with the DROP TABLE statement. If session closes (or if commits, depends on the ON COMMIT clause of CREATE GLOBAL TEMPORARY TABLE statement) only the storage is dropped. The temporary table's definiton in the Data Dictionary persists.
    Ales

  4. #4
    Join Date
    May 2002
    Posts
    35
    hi rohit

    i have my own doubts as you said that temporary tables are created in temporary tablespace... but i feel only temporary segments get created in the temporary tablespace.

    when i try to list out the colums from the views then the tablespace_name column is empty ..when i try to check out with dba_tables..

    where does the temporary table get created .. is it in the swap area

    regards,

    sharmila

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Well, it's a bit tricky but I believe it's exactly as rohitsn wrote.

    Try to create a small temporary tablespace and set it yourself as the default temporary tablespace. Then create a global temp table and try to overfill it. You get the error Oracle cannot extend the temporary segment.
    Code:
    system@oracle> create temporary tablespace t1
      2  tempfile 'd:\oracle\oradata\t1.tmp' size 1M
      3  extent management local
      4  uniform size 64k
      5  /
    
    Tablespace created.
    
    system@oracle> alter user system temporary tablespace t1;
    
    User altered.
    
    system@oracle> create global temporary table t
      2  as select * from all_objects
      3  where 1=0
      4  /
    
    Table created.
    
    system@oracle> insert into t select * from all_objects;
    insert into t select * from all_objects
                *
    ERROR at line 1:
    ORA-01652: unable to extend temp segment by 32 in tablespace T1
    I still have not found a view where I can see the storage of GTT.

    Ales

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width