RE: Permanent TS for sorting
- From: Carel-Jan Engel <cjpengel.dbalert@xxxxxxxxx>
- To: "Hollis, Les" <Les.Hollis@xxxxxx>
- Date: Mon, 26 Sep 2005 18:46:26 +0200
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@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:
