DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: increase index tablespace

  1. #1
    Join Date
    Dec 2002
    Posts
    62

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    62
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    check the quota of the index segment owner on that index tablespace. Then try increasing the quota...
    -nagarjuna

  4. #4
    Join Date
    Dec 2002
    Posts
    62
    I checked dba_ts_quotas
    it does not showes any row for indx tablespace
    is there any other place to check

  5. #5
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    check the quota of the index segment owner on that index tablespace. Then try increasing the quota...
    -nagarjuna

  6. #6
    Join Date
    Dec 2002
    Posts
    62
    can you please explain it which steps should i follow

  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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..
    -nagarjuna

  8. #8
    Join Date
    Dec 2002
    Posts
    62
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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...
    -nagarjuna

  10. #10
    Join Date
    Dec 2002
    Posts
    62
    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'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width