OraFAQ Forum: Server Administration » Stop a user from using over 18GB of Temp Space

Home » RDBMS Server » Server Administration » Stop a user from using over 18GB of Temp Space

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Stop a user from using over 18GB of Temp Space [message #63638]

Fri, 22 October 2004 00:56

JOHN
Messages: 182
Registered: April 1998

Senior Member

Hello All,

Environment: Oracle 8.1.7.4 on Solaris 8

A user querying the database via SQL*Plus managed to write a query that used 18 GB of Temp tablespace in Production !!!

I don't want to stop them running long running queries as I am using resource manager to stop them hogging the CPU but I do want to stop them grabbing 18 GB of temp tablespace. I've been looking at user profiles but don't seem to find anything which is suitable.

Can anyone offer any advice?

Thanks in avance.

John.

Report message to a moderator

Re: Stop a user from using over 18GB of Temp Space [message #63640 is a reply to message #63638]

Fri, 22 October 2004 11:39

croK
Messages: 170
Registered: April 2002

Senior Member

Maybe it is because an order by clause in select statement.
To avoid using disk space for sorts, you can modify sort_Area_size so sorts will use more memory instead of disk space. Increasing SORT_AREA_SIZE size improves the efficiency of large sorts.

Hope it helps.

Report message to a moderator

Re: Stop a user from using over 18GB of Temp Space [message #63642 is a reply to message #63638]

Fri, 22 October 2004 13:45

andrew again
Messages: 2577
Registered: March 2000

Senior Member

You probably have the default of file "auto extend" enabled for the file(s) making up TEMP. When a user enters a bad query (containing a cartesean join), the number or rows in the result goes up to a crazy number, and extends TEMP to the size you see to accomodate the resultset.

You get a cartesean join when joins between tables are missing e.g. If you select from DEPT, EMP without a join, you will get (number of rows in DEPT)*(number of rows in EMP)

Report message to a moderator

Re: Stop a user from using over 18GB of Temp Space [message #63655 is a reply to message #63638]

Mon, 25 October 2004 10:05

Sanjay
Messages: 236
Registered: July 2000

Senior Member

You can limit a quota in the TEMP tablespace.

alter user x quota y on TEMP;
--
Sanjay

Report message to a moderator

Previous Topic:	install DBMS 7.3.2 on solaris 9 ?
Next Topic:	table size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 13:42:19 CST 2025