| Temporary tablespaces [message #55130] |
Mon, 06 January 2003 13:53  |
Nick
Messages: 64
Registered: February 2000
|
Member |
|
|
When are segments allocated in the temporary tablespace
( created by using CREATE TEMPORARY TABLESPACE...)
as opposed to segments being allocated in the
tablespace defined in "CREATE USER .... TEMPORARY TABLESPACE USERS ....." ?
Thanks in advance.
--Nick
|
|
|
|
| Re: Temporary tablespaces [message #55131 is a reply to message #55130] |
Mon, 06 January 2003 14:20  |
N.A. Jam
Messages: 23
Registered: January 2003
|
Junior Member |
|
|
Neither of the statements you noted were intended for temporary segment creation.
You can CREATE one or more TEMPORARY TABLESPACEs to hold temporary segments mostly used when Oracle decides it needs disk space for sorting for instance.
You can CREATE USER and assign it to only one TEMPORARY TABLESPACE <tempspace_x>.
I hope this sorts it out a little bit and thanks for giving a chance to temporarily escape from the realities of work.
Cheers!
|
|
|
|
| Re: Temporary tablespaces [message #55133 is a reply to message #55130] |
Mon, 06 January 2003 15:01 |
Nick
Messages: 64
Registered: February 2000
|
Member |
|
|
Thnx for the reply , I guess it answers my question to some extent , though in hindsight I think I should have phrased my question as follows:-
Assuming that there exist temporary tablespace (A tablespace created by the CREATE TEMPORARY TABLESPACE TEMP1.... command) and also permanent tablespace that can be used to create temporary segments when required ( CREATE USER ...TEMPORARY TABLESPACE USERS... command), will Oracle use the "USERS" tablespace to create a temporary segment for a sort operation or the TEMP1 tablespace? Is there any order in which they are used or can we even predict which will be used?
Thanks.
|
|
|
|
| Re: Temporary tablespaces [message #55135 is a reply to message #55130] |
Mon, 06 January 2003 15:30 |
N.A. Jam
Messages: 23
Registered: January 2003
|
Junior Member |
|
|
Genarally, when a user executes a statement for instance, if Oracle deams it necessary to use a temporary segment, it will be created on the user's temporary tablespace. Now, at times Oracle issues internal sql statements in addition to the user's statement. If in turn these addtional statements requires temporary segment it will then be created under the SYSTEM's temporary tablespace, TEMP for instance.
There is a table you can check if a segment is being used for sorting. This table also contains the tablespace on which the sort is being done. Check out V$SORT_USAGE, if you are interested.
I guess we can still say, even if you see multiple tablespaces being used for temporary segment as your process is being ran, that their still is just one temporary tablespace to a user (the other one may be SYSTEM's).
I'm not aware of anyway you can distribute temp usage among tablespace per session.
Cheers!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
