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

Posted by Andrew Piskorski on
I'm using Oracle (and plan to upgrade to soon). Sebastiano, thanks for your suggestion! I decided to create a new tablespace named TMP, and then drop my old huge TEMP tablespace. Here's how I did it:

My Oracle instance creation script "$ORACLE_HOME/assistants/dbca/jlib/" 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

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 ;

------------------------------ --------- ---------- ---------
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
  o.owner  ,o.object_name
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 ;
Once I created my locally managed TMP tablespace, both of the above queries returned 1.

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 ;
Note that I didn't add the including contents or cascade constraints clauses to the drop tablespace, because I figured if there are any objects or constraints in there, then something must be wrong.

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, count(*)
from fet$ fet, ts$ ts
where fet.ts# = ts.ts# 
  and = 'TEMP'
group by ;

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.