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
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.
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.
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.
system@oracle> create temporary tablespace t1
2 tempfile 'd:\oracle\oradata\t1.tmp' size 1M
3 extent management local
4 uniform size 64k
system@oracle> alter user system temporary tablespace t1;
system@oracle> create global temporary table t
2 as select * from all_objects
3 where 1=0
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.