Forum OpenACS Q&A: Oracle temp tablespace is huge!

I've started reading more docs about tablespace fragmentation and the like, but has anyone here encountered this problem before, and come up with a simple fix for it??

Also, the reason I noticed this in the first place is that I've been fooling with hotbackups using RMAN, and my RMAN backup of the whole database was running absurdly slowly. Turned out it was because RMAN was including the whole 12.7 GB temp tablespace in the backup. Am I correct that there is no reason to every make a physical backup of my temp tablespace?

My Oracle instance creation script "$ORACLE_HOME/assistants/dbca/jlib/ora8run1.sh" had this in it, so presumably that's how my old TEMP tablespace was created:

REM ********** TABLESPACE FOR TEMPORARY **********
CREATE TABLESPACE TEMP DATAFILE '/ora8/m01/app/oracle/oradata/ora8/temp01.dbf' SIZE 150M REUSE
AUTOEXTEND ON NEXT 640K
MINIMUM EXTENT 64K
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;

First I used these queries to check some settings:

-- List all database files and their tablespaces:
select  file_name, tablespace_name, status
  ,bytes   /1000000  as MB
  ,maxbytes/1000000  as MB_max
from dba_data_files ;

-- What temporary tablespace is each user using?:
select username, temporary_tablespace, default_tablespace from dba_users ;

-- List all tablespaces and some settings:
select tablespace_name, status, contents, extent_management
from dba_tablespaces ;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN STATUS
------------------------------ --------- ---------- ---------
SYSTEM                         PERMANENT DICTIONARY ONLINE
TOOLS                          PERMANENT DICTIONARY ONLINE
TEMP                           TEMPORARY DICTIONARY OFFLINE
TMP                            TEMPORARY LOCAL      ONLINE

Now, the above query and the storage clause of the old 'create tablespace TEMP' command seem to tell us the tablespace only allows temporary objects, so it should be safe to assume that no one created any tables or other permanent objects in TEMP by mistake, as I think Oracle would prevent that. However, just to be absolutely certain, I decided to double-check. Checking for any tables in the tablespace is very easy:

-- Show number of tables in the TEMP tablespace - SHOULD be 0:
select count(*)  from dba_all_tables
where tablespace_name = 'TEMP' ;

Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly - note that you'll probably need to connect internal in order to see the sys_objects view:

-- Shows all objects which exist in the TEMP tablespace - should get
-- NO rows for this:
column owner        format a20
column object_type  format a30
column object_name  format a40
select 
  o.owner  ,o.object_name
  ,o.object_type
from sys_objects s
  ,dba_objects o
  ,dba_data_files df
where df.file_id = s.header_file
  and o.object_id = s.object_id
  and df.tablespace_name = 'TEMP' ;

Ok, that all looked good, so then I went and created my new TMP tablespace, changed all my users to use TMP instead of TEMP, and took TMP and its datafile offline:

create temporary tablespace TMP
tempfile '/ora8/m01/app/oracle/oradata/ora8/tmp01.dbf'
size 100M  autoextend on  next 10M  maxsize 500M
extent management local
uniform size 64K  -- should be same as sort_area_size init param
;

alter user USERNAME  temporary tablespace TMP;

alter tablespace TEMP offline normal ;
alter database datafile '/ora8/m01/app/oracle/oradata/ora8/temp01.dbf' offline;

Note that I didn't actually drop the tablespace yet - more on that later.

There isn't much information out there what the uniform size should be. Tom Kyte mentions in passing in his Expert One on One Oracle, and explains a bit more thoroughly on asktom that yes, it should be the same as your sort_area_size. (Sebastiano, if you hadn't pointed it out above, I'd probably never have found it!)

One interesting thing that wasn't immediately clear to me from the docs, is that while it's storage contents were definitely "tempory", this is definitely different using a "tempfile". I think only locally managed tablespaces can use "tempfiles", and perhaps being a "temporary tablespace" means the same thing as using a "tempfile".

Before I created my new TMP tablespace, I had no locally managed tablespaces in this database, and both these queries returned 0:

select count(*) from dba_temp_files ;
select count(*) from v$temp_space_header ;

At any rate, RMAN's "backup database" command still insisted on backing up TEMP, even though its contents were temporary. But export definitely did not export anything from it - which makes sense, since there should be no permanent objects in there to export. The new locally managed temporary tablespace TMP, on the other hand, did not is now ignored completely by RMAN's "backup database" command.

Out of paranoia, I waited a couple days before finally dropping the huge TEMP tablespace with:

drop tablespace TEMP ;

I also did not first do a "alter database datafile '/ora8/m01/app/oracle/oradata/ora8/temp01.dbf' offline drop;", but from what I've since read, that should probably make no difference whatsoever either way.

Here's the annoying part: As I write this, my drop tablespace TEMP command is still running!

Apparently, since it's a dictionary managed tablespace, Oracle needs to delete (in my case) 170k or so rows from the system fet$ table, which has 1 row for each free extent. And Oracle is absurdly slow in doing so. Why Oracle is so slow, I haven't found any explanation for. But it is. And the recomended fix is just to wait, and let Oracle suck up 50% CPU for several days if necessary.

From what I've read, you should be able to kill the drop tablespace command at any time in order to stop using up CPU, and then restart it later to continue the process of deleting rows from fet$ without any trouble, but I haven't tried that.

Here's a query to show you the number of rows in fet$ for the TEMP tablespace - it should slowly keep going down:

select  ts.name, count(*)
from fet$ fet, ts$ ts
where fet.ts# = ts.ts# 
  and ts.name = 'TEMP'
group by ts.name ;

Here are some links directly related to this slow drop tablespace problem: Metalink: one, two; Ask Tom, OraFAQ. And a few more links that are sort-of related: Metalink: three, four, five.

(Thanks to Michael Bryzek for pointing out several of those links to me, and giving me the fet$ query above.)

Btw, 170k extents in a tablespace is ridiculously many. Apparently it's only ever a problem for drop and coalesce operations, but some of the links above basically say that anything over 1000 or so extents is bad, it means you messed up in how you defined the tablespace in the first place.