revoking DBA privs and ORA-01536
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: revoking DBA privs and ORA-01536

  1. #1
    Join Date
    Mar 2001
    Posts
    82

    revoking DBA privs and ORA-01536

    I have a user that has no dba privilege. I granted the the user the DBA privilge.
    Minutes later I revoked the dba privilege from the user. As soon as I revoked the privilege,
    I started getting:

    ORA-01536: space quota exceeded for tablespace

    Any explanations?
    Last edited by randy; 02-20-2003 at 04:45 PM.

  2. #2
    Join Date
    Feb 2003
    Location
    Kolkata, India
    Posts
    33
    Can you tell the exact REVOKE statement you used to revoke "DBA privilge"?
    ("`-''-/").___..--''"`-._
    `6_ 6 ) `-. ( ).`-.__.`)
    (_Y_.)' ._ ) `._ `. ``-..-'
    _..`--'_..-_/ /--'_.' ,'
    (((' (((-((('' ((((

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    i believe that the role granted unlimited tablespace to the user -- revoking it removed that privilege, but the user probably left tables in a TS they then had no quota on
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Feb 2003
    Location
    Kolkata, India
    Posts
    33
    Originally posted by slimdave
    i believe that the role granted unlimited tablespace to the user -- revoking it removed that privilege, but the user probably left tables in a TS they then had no quota on
    OK.. but does revoke change the quota of existing users on TS?

    If so what may be the correct approach to revoke the DBA role under such circumstances?
    ("`-''-/").___..--''"`-._
    `6_ 6 ) `-. ( ).`-.__.`)
    (_Y_.)' ._ ) `._ `. ``-..-'
    _..`--'_..-_/ /--'_.' ,'
    (((' (((-((('' ((((

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    idon't think that granting the dba role establishes quotas in the same way as granting them through "alter user .. quota ... on ...".

    MAybe the sol'n would be not to grant the dba role - pick out the exact privs you need.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Mar 2001
    Posts
    82
    This is the syntax that I used to revoke the dba:

    revoke dba from username;

  7. #7
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    hmm, Try granting him DBA role again and see if he gets this error. If he still gets Tablespace Quota Error, then it has got nothing to do with revoke. Check if he is accessing the right Tablespace, default tablespace, Quota, etc. In short, Assign proper Tablespace and required Quota to the User.
    -- Dilip

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