Link to home
Create AccountLog in
Avatar of futureDBA
futureDBA

asked on

Temp tablespace is 20GB???!!

my system crashed because I ran out of space on my HD, when i checked for large files, the following was returned


[root@db /]# find . -type f -size +50000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'
./usr/bin/oracle: 159M
./usr/lib/locale/locale-archive: 95M
./usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/jre/lib/rt.jar: 58M
./u01/app/oracle/product/11.2.0/xe/dbs/ts_production.dbf: 11G
./u01/app/oracle/product/11.2.0/xe/bin/oracle: 159M
./u01/app/oracle/oradata/XE/undotbs1.dbf: 51M
./u01/app/oracle/oradata/XE/temp.dbf: 20G
./u01/app/oracle/oradata/XE/sysaux.dbf: 751M
./u01/app/oracle/oradata/XE/system.dbf: 381M
./u01/app/oracle/oradata/XE/users.dbf: 101M
./u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_7qcm7bk2_.log: 51M
./u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_7qcm773v_.log: 51M

Open in new window


What is the optimal size for a temp tablespace, and can i resize the current.

furthermore,  it says that ts_production is 11GB, in reality that shouldnt be anymore than 2 gigs worth of data.

most of my queries are to a remote server using ODBC,

in /tmp i get a sql.log transaction file that gets pretty big so i have to delete it every couple of days, but I am having a hard time understanding the rest of whats going on as far as space and how to remedy, any help or pointing in the right direction would be greatly appreciated. thanks
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>What is the optimal size for a temp tablespace,

There is no 'optimal'.  It depends on your database and how it is used.

20G might be 'normal' for how you use the database.  Then again, a developer might have ran a very bad cartesian join...

>>and can i resize the current.

for TEMP, typically not.  The best way is to create a new default temp tablespace then drop the old one.

>> it says that ts_production is 11GB, in reality that shouldnt be anymore than 2 gigs worth of data.

Allocated and used are two different things.  Oracle does not 'shrink' datafiles when you delete data.

How big did you create the original datafile?
Did you do a large load then delete a lot of data?
Avatar of futureDBA

ASKER

i am doing entry level dba work

a bad "cartesian join" might be possible but how do I determine that being the situation.
Not sure what version you are using.  I would check on V$SORT_USAGE or V$TEMPSEG_USAGE (it depends on version).  That will tell you how much of that temp space is actually being used.  You will have to monitor this over time though.
ANONYMOUS	ANONYMOUS	00000000919DDA80	7	000000008B5C18C8	1874671316	4dqasqjrvudqn	TEMP	TEMPORARY	LOB_DATA	201	2521344	1	128	1

Open in new window

SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I resolved the issue myself using

alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;


but since i did not ask how to resize the temp tablespace, i will award the points between slight and johnsone
Resizing TEMP will not fix "ts_production is 11GB, in reality that shouldnt be anymore than 2 gigs worth of data"

It will also likely not save you for long.  My guess is TEMP is set to autoextend so the next bad SQL will cause it to grow again...

What you did was a band-aid not a fix.