ORA-01536: space quota exceeded for tablespace

ORA-01536: space quota exceeded for tablespace


1. TO FIND OUT THE QUOTA FOR A USER ON THAT TABLESPACE
USE FOLLOWING QUERY:-


SELECT *
FROM dba_ts_quotas
WHERE USERNAME='ISLBAS'
AND TABLESPACE_NAME='ISLSYS'



2. Increase the quota for that user by this following command



ALTER USER ISLBAS QUOTA UNLIMITED ON ISLSYS;



OR
==

another cause may happen this error

this cause is :-
your are working on a table which owned by another user
so first need to find out that user. then give the owner user
quota unlimited to the tablespace .


For this :- you have to do

1.

select NAME,TYPE
from dba_dependencies
where REFERENCED_NAME='STFACMAS';



2.

select OWNER,OBJECT_NAME
from dba_objects
where OBJECT_NAME='DBT_STFACMAS_CD_CURBAL';


3.

grant unlimited tablespace to ISLBAS;

Index Monitoring whether they are being used

Index Monitoring whether they are being used
================================================
Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.

To start monitoring the usage of an index, issue this statement:

ALTER INDEX index MONITORING USAGE;

Later, issue the following statement to stop the monitoring:

ALTER INDEX index NOMONITORING USAGE;

The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active.

Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX...MONITORING USAGE statement is issued, the view information is left unchanged.


Example:

ALTER INDEX STFACMAS_IDX1 MONITORING USAGE;


select * from V$OBJECT_USAGE ;


select * from stfacmas
where crdnum ='5127724201336015';


select * from V$OBJECT_USAGE ;