RelationalDBDesign RelationalDBDesign


Creating Users   «Prev  Next»
Lesson 10Altering a User
Objective Alter a database user

Altering Users in Oracle

You can make changes to an existing user with the ALTER USER command. The syntax for ALTER USER is shown in the following MouseOver.

  1. You are altering an existing user named coin_admin
  2. The new password for this user will be the same as the username.
  3. Changes the user's default tablespace to users.
  4. Changes a user's temporary tablespace to temp.
  5. Changes a user's profile to the default.
  6. Changes the user's quota on the users tablespace to 5000K.
  7. Changes the user's quota on the tools tablespace to 10 megabytes.
  8. Gives the user an unlimited quota on the temp tablespace.

Syntax For Altering User
The various parts of the ALTER USER command are the same as those in the CREATE USER command. For example, to change a user's default tablespace assignment you would issue an ALTER USER command and provide a new DEFAULT TABLESPACE setting.

Alter Oracle Users

If you create users, you need to be able to do things such as change their password, and change various attributes for the user. You use the alter user command for these tasks. Here are some examples of the uses of the alter user command in action:
ALTER USER user1 IDENTIFIED BY new_password;
ALTER USER user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp  
QUOTA 101M ON users QUOTA 0 ON my_data;
ALTER USER user1 ACCOUNT LOCK;
ALTER USER user1 ACCOUNT UNLOCK;
ALTER USER user1 PASSWORD EXPIRE;

In the first example we changed the password of the user1 user. In the next example we altered the default and temporary tablespace settings for the user1 user, and we also changed the quotas on two different tablespaces. The next two examples lock the user1 account, and then unlock the user1 account.
The final example expires the password of the user1 account, which will force the user to change their password the next time they log in.