http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4003.htm
Examples
Changing User Identification: Example The following statement changes the password of the user sidney (created in „Creating a Database User: Example“) second_2nd_pwd and default tablespace to the tablespace example:
ALTER USER sidney
IDENTIFIED BY second_2nd_pwd
DEFAULT TABLESPACE example;
The following statement assigns the new_profile profile (created in „Creating a Profile: Example“) to the sample user sh:
ALTER USER sh
PROFILE new_profile;
In subsequent sessions, sh is restricted by limits in the new_profile profile.
The following statement makes all roles granted directly to sh default roles, except the dw_manager role:
ALTER USER sh
DEFAULT ROLE ALL EXCEPT dw_manager;
At the beginning of sh’s next session, Oracle Database enables all roles granted directly to sh except the dw_manager role.
