Temp tablespace is 20GB???!!

futureDBA
futureDBA used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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?

Author

Commented:
i am doing entry level dba work

a bad "cartesian join" might be possible but how do I determine that being the situation.
johnsoneSenior Oracle DBA

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>a bad "cartesian join" might be possible but how do I determine that being the situation.

You sort of just need to know.  You can probably write some SQL that goes through the plan tables and looks for plans that execute a cartesian.  However, a cartesian can be selected by the optimizer so they are not all bad.

Just monitor your system?  If TEMP was 1 Gig today and 20 Gig tomorrow, what happened?

Like I mentioned, maybe 20Gig is normal for your system.
Senior Oracle DBA
Commented:
Based on what you posted, which appears to be something from one of the sort usage views (column headings would have been nice), that user is certainly not using much space.  128 blocks isn't that much.

Of course, that is the current usage at one moment in time.  You would need to monitor that over time to see where your usage goes.

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial