• Arun Kumar

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.


Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user. There are two simple ways to achieve this: Generate SQL for each table In this method,

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback