Solved

how to solve ORA-01536 space quota exceeded errors?

Posted on 2008-10-06
10
4,849 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query question 8 73
oracle RMAN - trying to duplicate a database 5 29
migration MS SQL database to Oracle 30 61
dbms_crypto.decrypt   errors out 6 31
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

770 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