increase index tablespace

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

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

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

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

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

can you please explain it which steps should i follow

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..

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

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...

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'

hey man, the error is all about user npt having much quota on the tablespace. The error is not because of tablespace not having space.. That's why I was dealing with dba_ts_quotas view. Pls do give me the output.. make it ASAP as my night shift ends now and I may leave as soon as my reliever comes ;)

The out put is
USERNAME TABLESPACE_NAME BYTES
------------------------------ -------------------------- --------
rep1 USERS 419164160
rep2 USERS 423403520
2 rows selected.

THis is tag team time...

Right, now one of thoise users needs to have their quota increased on the tablespace INDX.

select username, tablespace_name, bytes from dba_ts_quotas
where tablespace_name = 'INDX';


select owner, sum(bytes/1024/1024) from dba_segments
where owner in ('REP1', 'REP2') and tablespace_name = 'INDX'
group by owner;

I run this query but i get this

SVRMGR> select username,tablespace_name,bytes from dba_ts_quotas where tablespac
e_name='INDX';
USERNAME TABLESPACE_NAME BYTES
------------------------------ ------------------------------ ----------
0 rows selected.

and the second query also returns o rows

SVRMGR> select owner,bytes from dba_segments where owner in (REP1','REP2') and tablespace_name='INDX';
OWNER BYTES
------------------------------ ----------
0 rows selected.

Hi Nagarjuna,
Sorry I was late yesterday to post the query.
I have posted the query result can you please tell me where the problem is.
Thanks

any body please help
Thanks

stop spam would you?

select owner, sum(bytes/1024/1024) from dba_segments
where tablespace_name = 'INDX'
group by owner;

Just to find what users have already created objects in the INDX tablesapce. Of cousre, this won't be helpful if the user TRYING to create an object has ZERO quote already. Usially when an import is occuring it'll sy "Importing objects... " in the log, you need to see which user is having the problems inmporting. Then take it from there. I.e., changed that user's quota for the INDX tablespace.