Re: Default Tablespace Questions

In article <097065ae.3322c88d_at_usw-ex0101-007.remarq.com>,   Seajay <seajaysNOseSPAM_at_hotmail.com.invalid> wrote:
> I have a user who has a default tablespace set, the user also
> has the 'unlimited tablespace' system privilege.
>
> However, when I try to use the DBA Studio in Oracle 8i to
> change 'Object Privileges' to give access to the tablespace I
> can't make changes. (Insufficient privilige)
>
> We have ONLY just started trying to use Oracle so I'm very new
> at it. What I want to do is:
>
> Create a new user who has full access to a particular tablespace
> but no access to any others (unless its necessary for something
> fundamental).
>
> Also, if a default tablespace is set, do I HAVE to still include
> the tablespace name in SQL queries, or will it assume the
> tablespace if it is not included?
>
> Thanks,
>
> Seajay.
>
> email: seajays_at_hotmail.com
> web: www.mufasa.freeserve.co.uk
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
>

To create a user having access to one tablespace (outside of the temporary tablespace) and no others the following will accomplish this goal:

create user <user> identified by <password> default tablespace <tablespace> temporary tablespace temp quota umlimited on <tablespace>;

grant connect, create session to <user>;

The user created will have access only to the default tablespace assigned and no other (outside of the temp tablespace used for temp tables and sorts).

You never have to include the tablespace in SQL queries, just the table name and, sometimes, the owner. Oracle knows which tablespace to access for the table in the query since that is specified either explicitly or by inference in the 'create table ...' statement:

EXPLICIT TABLESPACE DECLARATION EXAMPLE create table ice_cream (
flavor varchar2(20),
brand varchar2(20))
tablespace deserts; -- explicit tablespace declaration

If the user has a default tablespace of toppings:

IMPLICIT TABLESPACE DECLARATION EXAMPLE create table other_stuff (

kind           varchar2(10),
amount         number);

The table created in the last statement will end up in the toppings tablespace since that is the default for the user and no other tablespace was specified.

Again, there is no need to include the tablespace in the SQL query; Oracle knows where the tables are.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.