From oracle-l-bounce@freelists.org Mon Sep 26 11:01:50 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j8QG1nBi004542 for ; Mon, 26 Sep 2005 11:01:49 -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 j8QG1d6H004480 for ; Mon, 26 Sep 2005 11:01:39 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4E75E1EFAE9; Mon, 26 Sep 2005 11:01:36 -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 01892-08; Mon, 26 Sep 2005 11:01:36 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BCFAC1EF4CF; Mon, 26 Sep 2005 11:01:35 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5C2B3.48B6F29E" Subject: RE: Permanent TS for sorting Date: Mon, 26 Sep 2005 10:59:29 -0500 Message-ID: <683E0A1C82D75246BD9F4E01F77CBB0F01851196@pscdalpexch02.perotsystems.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Permanent TS for sorting Thread-Index: AcXCYaho4bhUO423S8Gvp8qxkVarbgARFM8ZAAMmUzA= From: "Hollis, Les" To: , "Carel-Jan Engel" , Cc: X-OriginalArrivalTime: 26 Sep 2005 15:59:33.0285 (UTC) FILETIME=[49319D50:01C5C2B3] X-archive-position: 25889 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Les.Hollis@ps.net Precedence: normal Reply-To: Les.Hollis@ps.net 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=-2.9 required=5.0 tests=AWL,BAYES_00,HTML_50_60, HTML_FONTCOLOR_UNKNOWN,HTML_MESSAGE autolearn=no version=2.63 ------_=_NextPart_001_01C5C2B3.48B6F29E Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Quote From Carel-Jan Engel "You only need to issue 'ALTER USER deepak TEMPORARY TABLESPACE system';" =20 =20 =20 =20 =20 And one thing you DO NOT want to do is to put everyone in SYSTEM tablespace for a temporary tablespace. =20 One of the main reasons Oracle provided a "default temporary tablespace " capability with 9i was to avoid users using SYSTEM and badly defragmenting it =20 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. =20 Then alter the database default temporary tablespace to that tablespace you just created =20 =20 Then switch all users to that tablespace using the sc ript provided by Carel-Jan Engel =20 ________________________________ 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 =20 -----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 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=20 ------_=_NextPart_001_01C5C2B3.48B6F29E Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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@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; 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 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

------_=_NextPart_001_01C5C2B3.48B6F29E-- -- http://www.freelists.org/webpage/oracle-l