Oracle Concepts: Alter Oracle Users

Next are some examples of the uses of the create/alter/drop user command in action:

To create oracle users:

   CREATE USER username
      IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS 'external_name'}
         options;

options:
   DEFAULT TABLESPACE tablespace
   TEMPORARY TABLESPACE tablespace
   QUOTA int {K | M} ON tablespace
   QUOTA UNLIMITED ON tablespace
   PROFILE profile_name
   PASSWORD EXPIRE
   ACCOUNT {LOCK|UNLOCK}

To seek information about users:

SELECT username, account_status FROM DBA_USERS;

Password change

ALTER USER myuser IDENTIFIED BY new_password;

Change various attributes for the users

alter the default and temporary tablespace

ALTER USER username

DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp

QUOTA 100M ON users QUOTA 0 ON user_data;

lock and unlock user account

ALTER USER username ACCOUNT LOCK;

ALTER USER username ACCOUNT UNLOCK;

ALTER USER username PASSWORD EXPIRE;

Drop user:

ADROP USER username CASCADE;

ALTER USER

Change the properties of a user.

Syntax:

   ALTER USER username options;

   ALTER USER username,... {GRANT|REVOKE} proxy_options;

options:
   IDENTIFIED BY password [REPLACE old_password]
   IDENTIFIED EXTERNALLY
   IDENTIFIED GLOBALLY AS external_name
   DEFAULT TABLESPACE tablespace
   TEMPORARY TABLESPACE tablespace
   QUOTA int {K | M} ON tablespace
   QUOTA UNLIMITED ON tablespace
   PROFILE profile_name
   DEFAULT ROLE role [,role,...]
   DEFAULT ROLE ALL [EXCEPT role,...]
   DEFAULT ROLE NONE
   PASSWORD EXPIRE
   ACCOUNT {LOCK|UNLOCK}