ALTER-USER command

ALTER-USER

 

    ALTER USER command
PURPOSE:
To change any of these characteristics of a database user:
* password
* default tablespace for object creation
* tablespace for temporary segments created for the user
* tablespace access and tablespace quotas
* limits on database resources
* default roles
SYNTAX:
ALTER USER user
[IDENTIFIED {BY password | EXTERNALLY}]
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace] ...
[PROFILE profile]
[DEFAULT ROLE { role [, role] ...
| ALL [EXCEPT role [, role] ...]
| NONE}]
where:
user
is the user to be altered.
IDENTIFIED
indicates how Oracle permits user access.
BY
specifies a new password for the user.  The password
does not appear in quotes and is not case-sensitive.
The password can only contain single-byte characters
from your database character set regardless of
whether this character set also contains multi-byte
characters.
EXTERNALLY
indicates that Oracle verifies user access with the
operating system, rather than with a password.  See
the CREATE USER command.
Although you do not need privileges to change your own password, you
must have ALTER USER system privilege to change from BY password to
EXTERNALLY or vice-versa.
DEFAULT TABLESPACE
specifies the default tablespace for object creation.
TEMPORARY TABLESPACE
specifies the tablespace for the creation of temporary segments for
operations such as sorting that require more space than is available
in memory.
QUOTA
establishes a space quota of integer bytes on the tablespace for the
user.  This quota is the maximum space in tablespace that can be
allocated for objects in the user's schema. You can use K or M to
specify the quota in kilobytes or megabytes.  You need not have
quota on the tablespace to establish a quota on the tablespace for
another user.  See the CREATE USER command.
If you reduce an existing quota to a value below the space allocated
for existing objects in the user's schema in the tablespace, no more
space in the tablespace can be allocated to objects in the schema.
Note that an ALTER USER statement can contain multiple QUOTA clauses
for multiple tablespaces.
UNLIMITED
places no limit on the space in the tablespace
allocated to objects in the user's schema.
PROFILE
changes the user's profile to profile.  In subsequent sessions, the
user is subject to the limits defined in the new profile.
To assign the default limits to the user, assign the user the
DEFAULT profile.
DEFAULT ROLE
establishes default roles for the user.  Oracle enables the user's
default roles at logon.  By default, all roles granted to the user
are default roles.
ALL
makes all the roles granted to the user default
roles, except those listed in the EXCEPT clause.
NONE
makes none of the roles granted to the user default
roles.
PREREQUISITES:
You must have ALTER USER privilege.  However, you can change your
own password without this privilege.
SEE:
CREATE PROFILE, CREATE ROLE, CREATE TABLESPACE, CREATE USER