RE: Temporary Tablespaces
- From: "Hollis, Les" <Les.Hollis@xxxxxx>
- To: "Bryan Wells" <bunjibry@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 9 Jan 2005 13:27:09 -0600
1: create default temporary tablespace tspacename
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M; =20
(Set your uniform size to a reasonable size based on your database size.
DO NOT use small extents. Use 5m, 10m, even up to 100m if your tspace
is large enough If you set it too small, you run in to the iissue of
PMON running a long time to clean up the huge number of small extents
each time the instance starts.)
After tablespace is created,=20
ALTER DATABASE=20
DEFAULT TEMPORARY TABLESPACE temp;
From here on, you do not need to specify the temporary tablespace as
part of your create user script, Oracle will set it for you.
Now, =20
Spool change.sql
SQL> select 'alter user '||username||' default tablespace temp;' from
dba_users where username not in ('SYS','SYSTEM');
SQL> @change.sql << run your spool file >>
It will alter all of your users to the new tspace name except sys and
system....they should remain is system tablespace for their default.
You should now be able to just drop the current temporary tablespace.
Of course, it will not drop as long as a user is currently using
it...either wait on those users or kill their session...or bounce the
DB if you have that luxury, to kick them out. =20
-----Original Message-----
From: Bryan Wells [mailto:bunjibry@xxxxxxxxx]=20
Sent: Sunday, January 09, 2005 1:12 PM
To: Hollis, Les; Oracle-L
Subject: Re: Temporary Tablespaces
9.2.0.1 on W2K Advanced Server. no they are not set to "default:
On Sun, 9 Jan 2005 13:06:44 -0600, Hollis, Les <Les.Hollis@xxxxxx>
wrote:
> What version? Are you using "default" temporary tablespace if on 9i?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
