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
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
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?
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.
johnsone

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
futureDBA

ASKER
ANONYMOUS	ANONYMOUS	00000000919DDA80	7	000000008B5C18C8	1874671316	4dqasqjrvudqn	TEMP	TEMPORARY	LOB_DATA	201	2521344	1	128	1

Open in new window

SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
futureDBA

ASKER
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
slightwv (䄆 Netminder)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.