Solved

how to solve ORA-01536 space quota exceeded errors?

Posted on 2008-10-06
10
5,322 Views
Last Modified: 2013-12-21
Hi Experts,
User is getting an errror ORA-01536 space quota exceeded .
I checked the freespace on that particular tablespace
I found that 15gb of space is available.
could you please help me on this?

Thanks.
0
Comment
Question by:daniesh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 22655764
That error means......

ORA-01536: space quota exceeded for tablespace 'string'

Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace.

Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner.

========================================
it means that whatever user you are using for the database has exceeded the tablespace quotas assigned to the user.

meaning if we say only 1GB can by used SCOTT user on a tablespace USER_DATA, then if you try to create big tables or if your tables grow in size by DML ( insert / updates etc ) which if in case exceed want more space than 1 GB, then u will get this error.

You need to ask your DBA to give you more quota ( if you want full quota then UNLIMITED ) or just drop some of your unwanted database objects ( tables, indexes ) to free up some space, so that you will not get this error.

just read thru the below as well :

http://www.experts-exchange.com/Database/Oracle/Q_20159293.html
http://en.allexperts.com/q/Oracle-1451/ora-01536-space-quota.htm
0
 

Author Comment

by:daniesh
ID: 22655834
HI,
How i will know that which user needs to give unlimited quota.
how i will the quota given to the user?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 22656342
below is the quota clause when used when creating an user...

CREATE USER oracle5
IDENTIFIED BY oracle5
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA UNLIMITED ON uwdata
QUOTA 10M ON data_med;

to alter an existing user...

ALTER USER uwclass
QUOTA 100K ON XDB;

For more information on how to alter an existing user.. see the below link.. many examples are given there with good explanation.

http://www.psoug.org/reference/user.html   ---> search for alter user in this link

=====================================

--  with below query you can get to know which user has what amount of quota
-- max_bytes will give you the maximum limit for each tablespace
select *
from dba_ts_quotas
where username ='MIS_OWNER' -- change the username here accordingly

 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:daniesh
ID: 22662328
Hi
please find the max_bytes for user and tablespace onsite_idx.
---------- ------------------------------ ----------------------------
        -1 ONSITE_IDX                     REGISTRY_APP_RIGHTRACK

we can see that max bytes is -1 that we are getting for user REGISTRY_APP_RIGHTRACK and tablespace onsite_idx
in this way, do we have to increase the max quota on user REGISTRY_APP_RIGHTRACK.

thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 22665100
what is the oracle user name you are using ?

can u provide the output for the same user :

select *
from dba_ts_quotas
where username ='MIS_OWNER' - change your user name here with column headings
0
 

Author Comment

by:daniesh
ID: 22665160
I am getting this errror when i was using righttrack application user
thats why i am using REGISTRY_APP_RIGHTRACK
select * from dba_ts_quotas where username='REGISTRY_APP_RIGHTRACK';

thanks
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 22665741
where is the output of this query ?

select * from dba_ts_quotas where username='REGISTRY_APP_RIGHTRACK' ;

0
 

Author Comment

by:daniesh
ID: 22670670
this is the output

---------- ------------------------------ ----------------------------
        -1 ONSITE_IDX                     REGISTRY_APP_RIGHTRACK
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 22674543
can you try :

ALTER USER REGISTRY_APP_RIGHTRACK QUOTA unlimited ON ONSITE_IDX  ;
0
 

Author Comment

by:daniesh
ID: 22691068
thanks for the help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question