OraFAQ Forum: Server Administration » How can I set quota on TEMP tablespace.

Home » RDBMS Server » Server Administration » How can I set quota on TEMP tablespace.

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

How can I set quota on TEMP tablespace. [message #52980]

Thu, 22 August 2002 11:19

Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida

Senior Member

- alter user X quota 5M on temp;
- X is the only user logged in the system.
- X runs a big SELECT which uses TEMP tablespace for sort process.
- I expect the query to fail due to the quota. When the query is over, I see that TEMP tablespace has ~ 10MB space used (it started with 0MB).

Is this possible ?

The TEMP tablespace is of type temporary.
Oracle 8.1.6.2.0
AIX 4.3.3

Quota on 'permanent' tablespace is working fine (like 'quota 20M on USERS')

Report message to a moderator

Re: How can I set quota on TEMP tablespace. [message #52981 is a reply to message #52980]

Thu, 22 August 2002 12:01

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I was under an impression that,
to create temp segments, the user does not need any quota at all.
even though the user is assigned with some quota over the temp tablespace, during a sort the quota limit  will be overruled.
or simply the quota is not affective with temp segments.
someone!, throw me some light, if i am wrong.

Report message to a moderator

Re: How can I set quota on TEMP tablespace. [message #52992 is a reply to message #52981]

Fri, 23 August 2002 01:22

santosh
Messages: 85
Registered: October 2000

Member

Hi,
I had come across problem of execution of programs those are using cursor with huge data over 20 hundred thousand records. This used to give me problem with temp tablspace . So i did following

Alter tablespace TEMP
Add tempfile '/oracle/dbf/temp02.dbf'
SIZE 100 M autoextend on ;

I have oracle 9i on unix platform

now my programs run fine.
Hope you may follow this too.
Santosh

Report message to a moderator

Re: How can I set quota on TEMP tablespace. [message #52997 is a reply to message #52992]

Fri, 23 August 2002 04:13

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

No.
we are not speakin of allocation space to temp tablespace.
question is

Is quota setting on a temp tablespace effective or not?
I strongly beleive, it is NOT

Report message to a moderator

Re: How can I set quota on TEMP tablespace. [message #53045 is a reply to message #52992]

Mon, 26 August 2002 12:32

Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida

Senior Member

Here is what I got in Metalink - interesting
============================================

See the followintg note

See Note:1054952.6

ORA-01652: Trying to Set Quotas for Users on Temp Tablespace

Problem Explanation:
====================
Tablespace quotas are not considered during temporary segment creation:

* Temp segments DO NOT consume any quota that a user might possess.
* Temp segments can be created in a tablespace for which a user does not have a quota.

This is a Oracle 7 bulletin but this is still documented in the 816 Concepts Manual, chapter 26

Report message to a moderator

Previous Topic:	datafile problem
Next Topic:	How to find out the maximum row size...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 10:18:04 CST 2024