how to solve ORA-01536 space quota exceeded errors?

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.
danieshAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
where is the output of this query ?

select * from dba_ts_quotas where username='REGISTRY_APP_RIGHTRACK' ;

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
danieshAuthor Commented:
HI,
How i will know that which user needs to give unlimited quota.
how i will the quota given to the user?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
danieshAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
danieshAuthor Commented:
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
 
danieshAuthor Commented:
this is the output

---------- ------------------------------ ----------------------------
        -1 ONSITE_IDX                     REGISTRY_APP_RIGHTRACK
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
can you try :

ALTER USER REGISTRY_APP_RIGHTRACK QUOTA unlimited ON ONSITE_IDX  ;
0
 
danieshAuthor Commented:
thanks for the help.
0
All Courses

From novice to tech pro — start learning today.