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?
It's possible that this particular datafile was created with the default
pctincrease 50and with
autoextend on, but without a
maxsizelimitation. I suggest to drop and recreate the temporary tablespace, but before doing anything make sure that no permanent object has been created by accident in this tablespace.
To create a brand new temporary tablespace, use something like:
create temporary tablespace TEMP tempfile '/ora8/data/temp01.dbf' size 50M autoextend on next 10M maxsize 300M extent management local uniform size 64K;A couple of notes: you have to use
datafile(and this file won't ever show up in
dba_data_files), and the value of the
uniform sizeshould be equal to the value of the
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
-- 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
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:
Once I created my locally managed TMP tablespace, both of the above queries returned 1.
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:
Note that I didn't add the
drop tablespace TEMP ;
cascade constraintsclauses 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
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 ;
(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.
delete from fet$ where file#=:1 and block#=:2 and ts#=:3".
set linesize 180 select p.spid as os_pid ,s.sid ,s.serial# --,p.username as os_user ,s.username ,s.status --,p.terminal ,p.program ,sql.sql_text from v$session s, v$sqltext sql ,v$process p where sql.address = s.sql_address and sql.hash_value = s.sql_hash_value and p.addr = s.paddr --and p.spid in (4656) and upper(s.username) = 'SYS' order by s.username ,s.sid ,s.serial# ,sql.piece ;
os_pid will be the PID that top shows sucking up