Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to solve ORA-01536 space quota exceeded errors?

Posted on 2008-10-06
10
Medium Priority
?
6,825 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

688 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