RE: Permanent TS for sorting

  • From: "Hollis, Les" <Les.Hollis@xxxxxx>
  • To: <jim_kennedy@xxxxxxxxxx>, "Carel-Jan Engel" <cjpengel.dbalert@xxxxxxxxx>, <oracle.tutorials@xxxxxxxxx>
  • Date: Mon, 26 Sep 2005 10:59:29 -0500

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






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


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


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

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

set hea off
set pagesize 0


SPOOL 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.

Other related posts: