system tablespace --

[lnr]

Hi

I created a user by

create user xy identified by xy (and i didnt mention in what tablespace it should get creaed ).
I think it gets created in system tablespace,can i drop that user and recreate it.

I want to know exact syntax

Thanks
lnr

[pando]

just alter users's default tablespace to another tablespace you want, you dont have to drop the user unless that user has already objects created in tablespace system
you have all the commands in Oracle Docs

[kmesser]

alter user xy default tablespace users;

[Upeshp]

Yes, It is right that you don't have to drop the user but you can always alter user and change its characteristics.......but any time u create a user you have to have practice of defining atleast mandetory part of it.....it's like this,

Create user xyz identified by xyz
default tablespace abc
temporary tablespace temp..............

[Dave_A]

As Upeshp says, don't forget to alter the users temporary tablespace as well as the default tablespace - disk sorts taking place in the system tablespace is a *bad* thing...

HTH

David.