User Management in Oracle

Users are the ultimate end people who will be using Oracle database. Before a user can access the database, the DBA must create the user inside the database.

By just creating a new user will not make the new user access the database. There are necessary roles and privileges that must be assigned to the user.

Check all users inside database

Use below command to check all the users inside the database:

SQL> select username, account_status, default_tablespace from dba_users;

Check current user

Use below command to check the username with which you are connected to the database:

SQL> show user;

Lock / Unlock user

Below commands are used to lock / unlock a user account inside the database:

SQL> alter user scott account unlock;
SQL> alter user scott account lock;

Create new user

Use below command to create new user inside database:

SQL> create user usr1 identified by usr1;

Use below command to create new user inside database by assigning a default tablespace:

SQL> create user usr2 identified by usr2 default tablespace users;

Change user password

Use below command to change user password inside the database:

SQL> alter user usr1 identified by oracle;

Check database default tablespace

When you create a new user without specifying a default tablespace, database default tablespace is assigned to the user. Use below command to find database default tablespace:

SQL> select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%DEFAULT%';

Change user default tablespace

Use below command to change default tablespace of a user:

SQL> alter user usr1 default tablespace example;

Note: The objects created in the old tablespace remain unchanged even after changing a default tablespace for a user

Tablespace quota

You can specify a limit onto how much tablespace quota (size) a user can use with below command:

SQL> Alter user usr1 quota 10M on users;

Note: Allocating quota doesn’t represent reserving the space. If 2 or more users are sharing a tablespace, quota will filled up in first come first serve basis.

Was this article helpful?

Related Articles

Leave a Comment