-
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
oracle-support@indiatimes.com
-
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
-
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|