RE: Permanent TS for sorting

Quote From Carel-Jan Engel    "You only need to issue 'ALTER USER deepak
TEMPORARY TABLESPACE system';"

 

 

 

 

 

And one thing you DO NOT want to do is to put everyone in SYSTEM
tablespace for a temporary tablespace.

 

One of the main reasons Oracle provided a "default  temporary tablespace
" capability with 9i was to avoid users using SYSTEM and badly
defragmenting it

 

If you don't already have one, create a tablepspace "TEMP"  (or whatever
you want to call it) as type temporary and allocate tempfile space for
it.

 

Then alter the database  default temporary tablespace to that tablespace
you just created

 

 

Then switch all users to that tablespace using the sc ript provided by
Carel-Jan Engel

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kennedy, Jim
Sent: Monday, September 26, 2005 9:24 AM
To: Carel-Jan Engel; oracle.tutorials@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Permanent TS for sorting

 





-----Original Message-----
From: Carel-Jan Engel [mailto:cjpengel.dbalert@xxxxxxxxx]
Sent: Sun 9/25/2005 11:14 PM
To: oracle.tutorials@xxxxxxxxx
Cc: Kennedy, Jim; oracle-l@xxxxxxxxxxxxx
Subject: Re: Permanent TS for sorting

No,

You only need to issue 'ALTER USER deepak TEMPORARY TABLESPACE system';
In that way the default temporary tablespace is overruled for the user
specified.

If you want to do this for all users you can execute somethng like this
from SQL*Plus.

set hea off
set pagesize 0

SELECT 'ALTER USER '||USERNAME||' TEMPORARY TABLESPACE system;'
FROM  DBA_USERS

SPOOL deepak.sql
/
@deepak.sql

That helps you to change all users in an efficient way.


Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok)
===


On Mon, 2005-09-26 at 11:02 +0530, DBA Deepak wrote:

> Will it not use the default temp TS of the Database?
>
>



Carel-Jan is correct.  But why do you want to do this?  It is less
effecient than using the temporary tablespace.  You can create a 2nd
temp tablespace (as temporary) and have it use that if you are tring to
use a different one.  With the perm tablespace you are going to generate
a lot more redo and extent allocation/deallocation.
Jim 

Other related posts: