increase index tablespace

[ayushsingh]

Hi all
When I am populating the table with index it is getting this error--

error msg: ORA-01536: space quota exceeded for tablespace 'INDX'

So i should increase the tablespace what are the steps to increase a index tablespace


thanks

[ayushsingh]

and here is the dba_data_files query
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELAT
IVE_F AUT MAXBYTES MAXBLOCKS INCREMENT_ USER_BYTES USER_BLOCK
6 INDX 339437568 165741 AVAILABLE
6 YES 8589930496 4194302 1 339435520 165740

which shows that autoextend is yes

[nagarjuna]

check the quota of the index segment owner on that index tablespace. Then try increasing the quota...

[ayushsingh]

I checked dba_ts_quotas
it does not showes any row for indx tablespace
is there any other place to check

[nagarjuna]

check the quota of the index segment owner on that index tablespace. Then try increasing the quota...

[ayushsingh]

can you please explain it which steps should i follow

[nagarjuna]

select owner, index_name from dba_indexes where table_name='< /

this gives the index name and it's owner.

The query the dba_ts_quotas view using the following

select tablespace_name, bytes from dba_ts_quotas
where username='<>'
/

this gives the quotas on all of the tablespaces. And increase the quota as required using the following syntex..

alter user <>
quota <> on <>
/


I think this will do..

[ayushsingh]

I am sorry i am not able to find out the owner because with that table name there are many owner
this is the query of dba_free_space
TABLESPACE_NAME FREE_BLK FREE_M BIG_CHUNCK NUM_CHUNCK
------------------------------ ---------- ---------- ---------- ----------
INDX 65 0 30 5

[nagarjuna]

Did I mention dba_free_space any where?? hummm.. crazy DBA you are..

try this

select username, tablespace_name, bytes from dba_ts_quotas
where username in
(select owner from dba_indexes where table_name='<>')
/

from the output, you should be able to see the tablespace name on which user is not able to extend.. and find out the corresponding username and use the following syntex to increase the quota

alter user <>
quota <> on <>
/


If you dont know what to do with these, just give me the out put of 1st query.. I will supply you the feedback...

[ayushsingh]

Thanks but the first query is showing the users tablespace and the error log shows this:---
error no: -1536
error msg: ORA-01536: space quota exceeded for tablespace 'INDX'