How to create new user in oracle database, change password, lock/unlock of oracle database user

Summary

In this article you have clear idea about how to create new oracle database user account and how to change oracle user’s password. Moreover, you will learn how to lock and unlock of oracle database user account. Oracle user default profile and tablespace change process also will be described in this article. You will get the query how to force user to change oracle user’s password. Besides, you will learn how to check the status of current oracle user as well as how can check all oracle user status like open user or expired user.

Oracle Database user create

 Basic syntax to create new oracle database user account are given below

Sql> CREATE USER username

    IDENTIFIED BY password

    DEFAULT TABLESPACE tablespacename

    QUOTA {size | UNLIMITED} ON tablespacename

    PROFILE profilename

    PASSWORD EXPIRE

    ACCOUNT {LOCK | UNLOCK};

We can create a new oracle database user without default setting like tabspace and profile using below query.

Now we will show how to create new oracle user with default setting. First we login to oracle database using sysdba

Sql> sqlplus / as sysdba

Sql> create user TEST identified by password TEST123

Here we create oracle database user which name is TEST and user’s password is TEST123.

Following query we can see created user tablespace, profile and authentication type.

SELECT

    username,

    default_tablespace,

    profile,

    authentication_type

FROM

    dba_users

WHERE

    username='TEST';

We can change default tablespace name and profile for oracle user account which steps are given below

Default tablespace name change

We want to change our default tablespace for oracle user account and we set TBS1 as default tablespace.  Following steps are shown below.

Connect oracle database as sysdba

Sqlplus / as sysdba

Sql> alter user TEST default tablespace TBS1;

Default profile change for oracle user account

Using bellow query we can change oracle user account default profile name

Connect oracle database as sysdba

Sqlplus / as sysdba

Sql> alter user TEST profile newprofilename;

Oracle database user password change

Now we describe how to password change of oracle database user

Login oracle database by sysdba

Password change syntax given below

Sql> alter user TEST identified by password TEST321;

When we create oracle database user TEST user’s password was TEST123 after change password new password will be TEST321

Oracle database users lock and unlock procedure

Bellow description we will show how to lock oracle database user account and unlock oracle database user account.

Login oracle database as sysdba

Sqlplus / as sysdba

Sql> alter user TEST account lock;

This query we can lock oracle database user now we show how can unlock oracle database user.

Sql> alter user TEST account unlock;

If we want to unlock oracle database user as well as we change users password below query we need to execute

Sql> alter user TEST identified by TEST12 account unlock;

Oracle database user account status check like open user and expired user check

We can check all database open user list using below query

Sql> select username,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where account_status='OPEN';

Query output will show all database open user.

Now we will show how to check  Expired and Locked user list

Sql> select username, ACCOUNT_STATUS, EXPIRY_DATE from dba_users where account_status=' EXPIRED & LOCKED';

We also check specific user detail using below query

select

username,

ACCOUNT_STATUS,

EXPIRY_DATE

from dba_users

where username='TEST';

To set the password expired for particular oracle database user, you use the following query

Sql> alter user username password expired;

When expired user login oracle database inserting previous password, the find an message password has been expired and asking for set new password.

Done!