DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ORA-01536: space quota exceeded for tablespace

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Lightbulb ORA-01536: space quota exceeded for tablespace

    Hi

    I have encountered the error ORA-01536 and I checked that the tablespace was only 50% used.

    I have earlier grant sysdba & unlimited tablespace to user A and I revoked the sysdba role from user A a couple of days later. After that, I encountered the error ORA-01536 when trying to create a table as User A.

    My question is once the dba role has been assigned then revoke, will it affect the quota assigned? I have no clue what caused the error ORA-01536 until i tried to grant unlimited tablespace to user A and everything was ok after that!

    Any advices please? Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    seems obvious to me, you revoked the privlege from the user so he had no quota anymore, you needed to put it back to make it work

  3. #3
    Join Date
    Jan 2000
    Posts
    387
    Revoke of just the dba assigned temporarily will removed all quota assigned? My understanding is that when you assigned a role, it should not affect the quota for the default tablespace. Wrong??

    How do I check what's the quota for individual users? There are no rows in dba_ts_quotas...
    Last edited by mooks; 05-12-2005 at 04:41 AM.

  4. #4
    Join Date
    Jan 2000
    Posts
    387
    One question : Is it because the role assigned is DBA? Other roles assigned and revoked will not caused this error?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    no rows in dba_ts_quota means he either have unlimited tablespace privilege or you have not specifically done

    alter user XX quota etc etc

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    UNLIMITED TABLESPACE is kind of "wierd" system privilege. It is not granted to any role (and in fact it CAN NOT BE GRANTED to any role!), yet when you grant some powerfull roles (like DBA and I think RESOURCE too - it might even be version dependand AFAIK) to user, Oracle "silently" adds that UNLIMITED TABLESPACE privilege to that user directly. And when you revoke such role from user, Oracle silently revokes UNLIMITED TABLESPACE privilege from user too!

    Now, when someone gets UNLIMITED TABLESPACE granted, it doesn't meen taht quotas on all tablespaces are set to unlimited to him. His quotas on tablespaces remain exactly the same - if he had quota 0 on tablespace USERS before, he still has quota 0 on that tablespace now. But when he has UNLIMITED TABLESPACE privilege, this takes priority over any quota settings and therefore he will be able to use space in that tablespace for new extents. However, if you later revoke UNLIMITED TABLESPACE from that user, his quota 0 will be put in effect and he will no longer be able to use any additional available space from tablespace USERS.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2000
    Posts
    387
    1. The value -1 is unlimited tablespace. I will alter the user. Thanks!

    2. Same Question : Will revoke of roles remove quotas assigned to user?

  8. #8
    Join Date
    Jan 2000
    Posts
    387
    Originally posted by jmodic
    UNLIMITED TABLESPACE is kind of "wierd" system privilege. It is not granted to any role (and in fact it CAN NOT BE GRANTED to any role!), yet when you grant some powerfull roles (like DBA and I think RESOURCE too - it might even be version dependand AFAIK) to user, Oracle "silently" adds that UNLIMITED TABLESPACE privilege to that user directly. And when you revoke such role from user, Oracle silently revokes UNLIMITED TABLESPACE privilege from user too!
    Thank you jmodic!

    So in conclusion only role DBA and RESOURCE will be granted unlimited tablespace to user!

    I will be careful with these 2 roles. Thank you!

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