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
Solved

how to solve ORA-01536 space quota exceeded errors?

Posted on 2008-10-06
10
5,056 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

856 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