| Temporary Tablespace defined with: Datafile vs Tempfile. [message #60309] |
Sat, 31 January 2004 11:50  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
What is the concepts and the differences between creating a temporary tablepsace with datafile and creating temprorary tablespace with tempfile?
How to make a default temporary tablespace as default on Oracle 8i? I can't execute an:
ALTER TABLESPACE NEW_TBS
DEFAULT TEMPORARY TABLESPACE; as on Oracle 9i...
How can I flush or free the space used on a temporary tablespace in Oracle 8i?
Many thanks for your answers!
Regards,
Patrick Tahiri.
|
|
|
|
| Re: Temporary Tablespace defined with: Datafile vs Tempfile. [message #60333 is a reply to message #60309] |
Mon, 02 February 2004 09:18  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
Tablespaces with datafiles that are of type 'TEMPORARY' are usually backed up and are allocated in full(ie file on OS) when created.
On the other hand, true temporary tablespaces that make use of tempfiles are not backed up and grow as required(ie initial allocation completes quickly for this reason).
You cannot allocate a default temporary tablespace in 8i,but you specify them during user creation(and hence control it ).
Sort segment once allocated remains( and gets reused) until instance shutdown.Its perfectly OK to have them 100% used.Its designed to be such.There's no dynamic allocation & deallocation after reaching a steady state.
-Thiru
|
|
|
|
| Re: Temporary Tablespace defined with: Datafile vs Tempfile. [message #60336 is a reply to message #60333] |
Mon, 02 February 2004 10:34 |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Thank you very much Thiru!
But, if one is using datafile for a temprorary tablespace: how will transactions/sort operations which need the temporary tablespace (with datafile) will react and which will be the behave of such a temporary tablespace when created with datafile? Will the data file fills up and not release any more the used space? So no more transactions ca use this used space? What will be then the difference at this level with a tempfile?
Again, many thanks for your great tips!
Best regards,
Patrick Tahiri.
|
|
|
|
| Re: Temporary Tablespace defined with: Datafile vs Tempfile. [message #60340 is a reply to message #60336] |
Mon, 02 February 2004 14:49 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
When it comes to sort segment/extent allocation in a temporary tablespace,the behaviour is the same with either datafiles or tempfiles. The allocated sort segment can be used by multiple transactions and keeps getting reused. The allocated extents do not get deallocated,they just get reused in either case.
In case of tempfile, the tempfile initially grows as the sort extents are allocated and once the full size is reached,they behave the same as datafiles, except that they never need to be backed up. They can be recreated much faster than datafiles becos of the sparse allocation.
-Thiru
|
|
|
|
| Re: Temporary Tablespace defined with: Datafile vs Tempfile. [message #60342 is a reply to message #60340] |
Mon, 02 February 2004 16:01 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Follow up:
I think I misspoke a little bit on the 2nd paragraph in the previous post. What I meant to say was that tempfiles are sparesely allocated when you create the them intially. Once a sort to disk is made,they get their full allocation on certain platforms. Thats the reason you find tempporary tablespace using tempfiles getting created much faster than datafile based temporary tablespaces.
However the sort extents behave the same in both the cases.
-Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
