Printable View
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
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
[email protected]
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.Quote:
Originally posted by rohitsn
... the temporary tables get dropped whenever the session closes.
Ales
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
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.
I still have not found a view where I can see the storage of GTT.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
Ales