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_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