| drop and create a temporary tablespace [message #113210] |
Thu, 31 March 2005 11:46  |
cover
Messages: 2
Registered: March 2005
|
Junior Member |
|
|
Our production db (8i) has a unique temporary tablespace (dictionnary-managed datafile).
I plan to use a tempfile instead of it.
Here is the procedure I want to use.
1. Create the tempfile
create temporary tablespace temp
tempfile [datafile_path & size]
extent management local
uniform size 128K;
2. For each user, alter the default's temporary tablespace to the new one
alter user [username] temporary tablespace temp;
3. Check, with the following request, if the old temp tablespace is still in use
SELECT (SELECT username FROM v$session WHERE saddr = session_addr) uname, v.*
FROM v$sort_usage v;
4. Drop the old temporary tablespace
Here are my questions before doing it :
1. Can I drop the old temporary tablespace while the database is running ?
2. Do I have to backup the tempfile when hotbackuping the db ?
Thanks for your help
Regards
|
|
|
|
| Re: drop and create a temporary tablespace [message #113217 is a reply to message #113210] |
Thu, 31 March 2005 12:56  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
1. I believe so, yes, as long as nothing is currently using the tablespace, in which case you'll need to wait for that transaction to finish.
2. No.
You may want to check out the database default temp tablespace.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
