From oracle-l-bounce@freelists.org Mon Sep 26 18:10:26 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j8QNAPit001355 for ; Mon, 26 Sep 2005 18:10:25 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j8QN9m6H001250 for ; Mon, 26 Sep 2005 18:09:49 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 61D071EF3C5; Mon, 26 Sep 2005 18:09:44 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17804-04; Mon, 26 Sep 2005 18:09:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 796241EF34D; Mon, 26 Sep 2005 18:09:43 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=SjL+zulugu2jEEqa9UwWEplI3YlbeRApMcQ2nnlZllZgeb+d+NPp3D2NcmtxQH2QYquXLxW1q7+98NeuKM9lE3tH6zWOOrc1VRgGdqlnU2+6itZ5YaQrsgI4iYGy2WVl31cN2u2rNPiIzUiRoxmUOrQjaK/3sc6qdQHlDa1KRF4= Message-ID: <392977e5050926160746f159d5@mail.gmail.com> Date: Tue, 27 Sep 2005 09:07:43 +1000 From: To: Ron.Reidy@arraybiopharma.com Subject: Re: Permanent TS for sorting Cc: cjpengel.dbalert@xs4all.nl, "Hollis, Les" , jim_kennedy@mentor.com, oracle.tutorials@gmail.com, oracle-l@freelists.org In-Reply-To: <17CAB0BF27BCFC47B0E4554A0E2F962B8734F6@fiji.arraybp.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8769_5823373.1127776063828" References: <17CAB0BF27BCFC47B0E4554A0E2F962B8734F6@fiji.arraybp.com> X-archive-position: 25918 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rjsearle@gmail.com Precedence: normal Reply-To: rjsearle@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.5 required=5.0 tests=AWL,BAYES_00, HTML_FONTCOLOR_BLUE,HTML_MESSAGE,NO_REAL_NAME autolearn=no version=2.63 ------=_Part_8769_5823373.1127776063828 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Someone finally got the joke. :-O mind you it's a long way to say RTFM! On 9/27/05, Reidy, Ron wrote: > > Well, > I, for one, thought Mladen's and Carel's posts were damned funny ( > http://www.webster.com/cgi-bin/dictionary?book=3DDictionary&va=3Dsarcasm&= x=3D0&y=3D0) > :D > -- > Ron Reidy > Lead DBA > Array BioPharma, Inc. > -----Original Message----- > *From:* oracle-l-bounce@freelists.org [mailto: > oracle-l-bounce@freelists.org] *On Behalf Of *Carel-Jan Engel > *Sent:* Monday, September 26, 2005 10:46 AM > *To:* Hollis, Les > *Cc:* jim_kennedy@mentor.com; oracle.tutorials@gmail.com; > oracle-l@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 > > =3D=3D=3D > If you think education is expensive, try ignorance. (Derek Bok) > =3D=3D=3D 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 defragmentin= g > 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@freelists.org [mailto:oracle-l-bounce@freelists.or= g] > *On Behalf Of *Kennedy, Jim > *Sent:* Monday, September 26, 2005 9:24 AM > *To:* Carel-Jan Engel; oracle.tutorials@gmail.com > *Cc:* oracle-l@freelists.org > *Subject:* RE: Permanent TS for sorting > > > > > > > -----Original Message----- > From: Carel-Jan Engel [mailto:cjpengel.dbalert@xs4all.nl > ] > Sent: Sun 9/25/2005 11:14 PM > To: oracle.tutorials@gmail.com > Cc: Kennedy, Jim; oracle-l@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 > > =3D=3D=3D > If you think education is expensive, try ignorance. (Derek Bok) > =3D=3D=3D > > > 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 effecien= t > 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 o= ne. > 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 b= e > 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. > > ------=_Part_8769_5823373.1127776063828 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Someone finally got the joke. :-O  mind you it's a long way to say RTF= M!

On 9/27/05, = Reidy, Ron <Ron.Reid= y@arraybiopharma.com> wrote:
Well,
=  
I, for one, th= ought Mladen's and Carel's posts were damned funny ( http://www.webster.com/cgi-bin/dictionary?book=3DDictionary&va=3Dsarcas= m&x=3D0&y=3D0) :D
=  
--
Ron Reidy
Lead DBA
Array BioPharm= a, Inc.
 
-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Care= l-Jan Engel
Sent: Monday, September 26, 2005 10:46 AM
To: Hollis, = Les
Cc: jim_kennedy@men= tor.com ; oracle.tutorials@gmail.= com; oracle-l@freelists.org
Subject: RE: Permanent TS for sorting<= br>
Before I get flamed by anyone on this list:

Plea= se read Mladens first answer to he OP.
I was just adding some topping t= o the cake.
SYSTEM seems to be the right tablespace for that purpose.
;-)
Best regards,

Carel-Jan Engel

=3D=3D=3D
If you think e= ducation is expensive, try ignorance. (Derek Bok)
=3D=3D=3D
On Mon, 2005-09-26 at 10:59 -0500, Hollis, Les wrote:=20
Quote Fr= om Carel-Jan Engel    "You only need to issue 'ALTER USER deepak TEMPORARY TABLE= SPACE system';"

 

 
<= br> 

 


 =

And one thin= g you DO NOT want to do is to put everyone in SYSTEM tablespace for a tempo= rary tablespace.

 

One of the main re= asons Oracle provided a "default  temporary tablespace " capability wi= th 9i was to avoid users using SYSTEM and badly defragmenting it

 

If you don't alrea= dy have one, create a tablepspace "TEMP"  (or whatever you want to cal= l it) as type temporary and allocate tempfile space for it.

 

Then alter the dat= abase  default temporary tablespace to that tablespace you just create= d

 
 

Then switch all users to that tablespa= ce using the sc ript provided by Carel-Jan Engel


 


From:= oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of = Kennedy, Jim
Sent: Monday, September 26, 2005 9:24 AM
To: Carel-Jan Engel; oracle.tutorials@gmail.com
Cc: oracle-l@freelists.org
Subject: RE: Permanent TS for sorting






-----Original Message-----
From: Carel-Jan Engel [ mailto:cjpengel.dbalert@xs4all.nl]
Sent: Sun 9/25/2005 11:14 PM

To: oracle.tutorials@gmail.com
Cc: Kennedy, Jim; o= racle-l@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 execut= e somethng like this

from SQL*Plus.

set hea off
set p= agesize 0

SEL= ECT 'ALTER USER '||USERNAME||' TEMPORARY TABLESPACE system;'<= br> FROM  DBA_USERS

SPOOL deepak.sql
/
@deepak.sql

T= hat helps you to change all users in an efficient way.

Best regards,
Carel-Jan Engel
<= br>=3D=3D=3D
If you think education is expensive, t= ry ignorance. (Derek Bok)

=3D=3D=3D=


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 les= s 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 us= e 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=20
or entity named above. If you are not the intended recipient, please be= aware that any disclosure,
copying, distribution or use of the content= s of this information is prohibited. Please notify the sender
of the de= livery error by replying to this message, or notify us by telephone (877-63= 3-2436, ext. 0),=20
and then delete it from your system.
=20


------=_Part_8769_5823373.1127776063828-- -- http://www.freelists.org/webpage/oracle-l