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.
It's not actually possible to specify a different tablespace when importing using the oracle imp
utility. However, as a workaround, you can pre-create the tables by doing a ROWS=N
import into the USERS
tablespace, then alter table mytable move tablespace BLOG_DATA;
for each table to move them to the new tablespace, then do the import again with the IGNORE=Y
parameter to ignore the table creation errors and import all of the data.
If the data was exported using Data Pump (expdp
), (as an aside, everyone should be using this these days, rather than the old legacy exp
/imp
utilities) you can easily import into another tablespace using the REMAP_TABLESPACE
parameter.
eg:
impdp scott/tiger@ZOMG file=blog_data.dmp directory=mydir remap_tablespace=USERS:BLOG_DATA
Best Answer
A schema itself can not be stored nor can have changed tablespace en bloc in any way. In fact, it is just a meta-structucre. Instead - there is
DEFAULT TABLESPACE
attribute of underlyingUSER
. If you change it, then new objects are created in this tablespace by default (unless you excplicitly specify another one).In addition, each type of structure (Table/Index/Partition...) has it's own way of moving to another tablespace. (i.e. moving a table does not move indexes built upon it - which would be rather undesirable, because you want to store indexes in another filesystem for better performance). This means, that you will have to move contents of the schema object by object.