Forum OpenACS Q&A: Response to Oracle temp tablespace is huge!
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 ;
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 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.
