Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Say I have created these two accounts:

CREATE USER u1 IDENTIFIED BY "&&u1Password"
    DEFAULT TABLESPACE u1tbs
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON u1tbs
    QUOTA 256M ON users
    QUOTA 0M ON ket;

CREATE USER u2 IDENTIFIED BY "&&u2Password"
    DEFAULT TABLESPACE u2tbs
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON u2tbs
    QUOTA 256M ON users
    QUOTA 0M ON u1tbs;

I have granted u2 full access to u1's schema. All tables in u1 are in the tablespace u1tbs as you can see. Where will data go when u2 adds data to u1's schema? If so won't it contradict with quota 0M on ...?

Edit: Question should have been on dba.stack...

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 22:53

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 4 down vote accepted

Quota calculation is based on table owner, not on the user that inserts (or deletes or updates) data.

For example if u1 creates a table, u2 inserts 300MB of data, then u3 deletes 100MB of that same data and u4 updates 100MB of that data, only the quota of u1 is checked, for other users, just permissions at the table level (grant insert/delete etc) are checked.

In the example you give, there is no contradiction, u2 inserts into the table owned by u1, so only the quota of u1 is checked.

Note there is no need to specify QUOTA 0M as the default is not to allow quota on tablespaces.

http://docs.oracle.com/cd/B10500_01/server.920/a96521/users.htm#15298

You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does two things:

Users with privileges to create certain types of objects can create those objects in the specified tablespace.

Oracle limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.

share|improve this answer
    
Great, that's what I have presumed but was uncertain. The Quota 0M should remain I guess for safety reasons. I don't want folks to create anyhting inadvertently. –  Michael-O Feb 9 '12 at 9:21
    
Actually, I would leave out the quota 0M as that is the default. (I updated answer) –  Patrick Feb 9 '12 at 9:25
    
Thanks again, I will remove the superfluous statements. –  Michael-O Feb 9 '12 at 9:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.