Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: a permanent tablespace as a temporary tablespace

RE: a permanent tablespace as a temporary tablespace

From: Mark W. Farnham <>
Date: Fri, 27 Oct 2006 14:23:43 -0400
Message-ID: <>

As regards "If it ain't broke..." I tend to agree with that sentiment. Yet I also prefer to change the oil in my car at the recommended intervals.

Here, the downside of leaving it in original equipment, version 6 style temporary tablespace in permanent tablespace is that you may be generating a lot of really unneeded redo log information. If that is the case, and if your temporary storage is not on some lame slower storage (which would be a really bad choice, but I've seen it), then there really is only upside to moving to true temporary storage. I suppose in the window between when you switch the users to the new area there could be logons that are still pointing to the old temp, so you will want to wait a bit before you try to drop the old temporary tablespace and certainly make sure that every user has a valid temp other than the old one before you drop the old tablespace.

Finally, since it really is a permanent tablespace, there is no way to know for sure that someone has not developed a process that stashes "INTERIM USER OBJECTS", that is, what folks used to do before global temporary tables existed, in the TEMP tablespace. So on that grounds you may want to leave it around for a while as well and check it for activity. If someone does have what I call INTERIM USER OBJECTS to avoid confusion with temporary segments and they have good grounds for not using global temporary tables instead, you can redirect them to a more appropriately named new tablespace.

Since it is dictionary managed and otherwise "old school", an easy way to check for use is to coalesce the storage in this tablespace completely into one piece and then wait a business cycle or two to see if anyone fragments the free space at all. If the existing temp is gigantic and you really need the storage back, you might risk dropping it and building a merely large one in its place. Again, this is all just to check for inappropriate usage without making something possibly fail.



-----Original Message-----
From: []On Behalf Of Roger Xu
Sent: Friday, October 27, 2006 12:25 PM
To:; Oracle-L_at_Freelists. Org (E-mail) Subject: RE: a permanent tablespace as a temporary tablespace

You are right, it was upgraded from 8i three years ago. Everything is still Dictionary-managed. Thanks, Roger

-----Original Message-----
[]On Behalf Of Grant Allen Sent: Friday, October 27, 2006 11:22 AM
To: Oracle-L_at_Freelists. Org (E-mail)
Subject: Re: a permanent tablespace as a temporary tablespace

On 10/28/06, Roger Xu <> wrote:
> Hi,
> I inherited a SAP R/3 4.0B database in Solaris 9 / Oracle 9i.
> And it uses a permanent tablespace for temporary tablespace.
> Any comments and thoughts? It has been like this for at least
> 3 year and nobody report any problems because of this.

Two thoughts. Possibly the instance was upgraded from 8i (or earlier) ... 9i won't let you create a new user with temp tbs set to a perm tbs, but it's probably "kind" to existing users if the instance is upgraded (someone will doubtless correct me on this).

Secondly, if it ain't broke ...



This e-mail is intended solely for the person or entity to which it is
addressed and may contain confidential and/or privileged information. Any
review, dissemination, copying, printing or other use o
This email has been scanned for all viruses by the MessageLabs Email
Security System.

Received on Fri Oct 27 2006 - 13:23:43 CDT

Original text of this message