Re: Tablespaces

In article <2iqunc$sr3_at_rdxsunhost.aud.alcatel.com>, hallre_at_aud.alcatel.com (Robert E. Hall) writes:
> Got a couple of problems that I thought were relatively easy to figure out, but
> I can't seem to find the answer. Any replies will be greatly appreciated.
>
> 1) Is one particular Oracle user associated with a particular tablespace?
> If so, what view or table do you query to see the association or ownership?
>
> 3) By granting the privilege "Create any table", does that allow you to create a
> table in any tablespace?
>
I assume that since you mention the CREATE ANY TABLE privilege, that you are working in ORACLE7.

Users are associated with a tablespace when they are granted quota on a tablespace. You grant quota by using the ALTER USER command.

        ALTER USER OPS$FRED QUOTA UNLIMITED ON USERS_T01; You can see tablespace quotas by querying the DBA_TS_QUOTAS or USER_TS_QUOTAS

The CREATE ANY TABLE privilege give the user the ability to create a table in anyone's schema. They still have to have quota on the tablespace where they wish to create the table. The UNLIMITED TABLESPACE privilege is what gives the user the ability to create objects in any tablespace. Note that you cannot grant the UNLIMITED tablespace to a user through a role, it must be granted specifically to the user.

All this is documented in the ORACLE7 Server SQL Language Reference Manual.

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285