RE: Permanent TS for sorting

Well,  

I, for one, thought Mladen's and Carel's posts were damned funny (http://www.webster.com/cgi-bin/dictionary?book=Dictionary&va=sarcasm&x= 0&y=0) :D  

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
 
-----Original Message-----
From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Carel-Jan Engel

Sent: Monday, September 26, 2005 10:46 AM
To: Hollis, Les
Cc: jim_kennedy_at_mentor.com; oracle.tutorials_at_gmail.com;
oracle-l_at_freelists.org
Subject: 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 
	
	



	

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended 
to be for the use of the individual or entity named above. If you are not the 
intended recipient, please be aware that any disclosure, copying, distribution 
or use of the contents of this information is prohibited. Please notify the
sender  of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.


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