assigning a tablespace to a user - how?

I presume you meant the SYSTEM tablespace and not the SYS one :o) By default, unless you specify a default tablespace when creatring your users, they will end up using (and therefore abusing) the system tablespace and this is a bad thing.

Usually, I have a tablespace for each user named the same as the user, but in some of our apps, the tablespace names are hard coded and so I have to use the same ones.
Usually, the temporary tablespace name is TEMP. I always give zero quota to SYSTEM - save problems later in life ! And I have a role set up on all of my databases which is granted to all new users as in the following :

create role unifarce;
grant create session, create table, create view, create sequence, create synonym, create trigger to unifarce;

So a proper (!) example would be :

create tablespace myuser
datafile '/data1/myuser.dbf' size 2000m
default storage (initial .. next .. pctincrease 0 maxextents ... etc);

create user myuser identified by password default tablespace myuser
temporay tablespace temp
quota 0 on system
quota unlimited on myuser;

grant unifarce to myuser;

If I need to, I can also grant quota on other tablespaces during the create user, or later using the alter user <name> quota <something> on <tablespace> command.

If you have no CD and no docs, get on over to http://technet.oracle.com and download the docs from there.
Always assuming that the poer supply in back up in the Bay Area and that the web site is working again.

HTH. Norman.

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com


------------------------------------------------------------------------

-----Original Message-----

From: Christoph Kukulies [mailto:kuku_at_gil.physik.rwth-aachen.de] Posted At: Wednesday, September 26, 2001 8:16 AM Posted To: server
Conversation: assigning a tablespace to a user - how? Subject: assigning a tablespace to a user - how?

When I create a user and this user creates tables this seems to go by default into the sys tablespace.

By which statement can I assign a certain tablespace to a user?

Can this be looked up in the web (docs in the web) somewhere? I'm without my Oracle CD and documentation at the moment.

-- 
Chris Christoph P. U. Kukulies kuku_at_gil.physik.rwth-aachen.de