Creating Users   «Prev  Next»

Lesson 10Altering a User
Objective Alter a database user

Altering Users in Oracle

Oracle provides a way so that you can make changes to an existing user. This command is known as the ALTER USER command. The syntax for ALTER USER is shown in the following diagram.

Syntax for altering User

Altering user syntax
Altering user syntax 
ALTER USER coin_admin
 IDENTIFIED BY coin_admin
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp 
 PROFILE default
 PASSWORD EXPIRE
 QUOTA 5000K ON users
 QUOTA 10M tools
 QUOTA UNLIMITED ON temp;
  1. ALTER USER coin_admin: You are altering an existing user named coin_admin.
  2. IDENTIFIED BY coin_admin: The new password for this user will be the same as the username.
  3. DEFAULT TABLESPACE users: Changes the user's default tablespace to users.
  4. TEMPORARY TABLESPACE temp: Changes a user's temporary tablespace to temp.
  5. PROFILE default: Changes a user's profile to the default.
  6. QUOTA 5000K ON users: Changes the user's quota on the users tablespace to 5000K.
  7. QUOTA 10M tools: Changes the user's quota on the tools tablespace to 10 megabytes.
  8. QUOTA UNLIMITED ON temp: Gives the user an unlimited quota on the temp tablespace.



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.
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.

SEMrush Software