RE: Permanent TS for sorting

Before I get flamed by anyone on this list:

Please read Mladens first answer to he OP. I was just adding some topping to the cake. SYSTEM seems to be the right tablespace for that purpose. ;-)

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===
On Mon, 2005-09-26 at 10:59 -0500, Hollis, Les wrote:
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kennedy, Jim
> Sent: Monday, September 26, 2005 9:24 AM
> To: Carel-Jan Engel; oracle.tutorials_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Subject: RE: Permanent TS for sorting
>
>
>
>
>
>
>
> -----Original Message-----
> From: Carel-Jan Engel [mailto:cjpengel.dbalert_at_xs4all.nl]
> Sent: Sun 9/25/2005 11:14 PM
> To: oracle.tutorials_at_gmail.com
> Cc: Kennedy, Jim; oracle-l_at_freelists.org
> 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
>

--
http://www.freelists.org/webpage/oracle-l