If you have ever worked with oracle database then you must have come across with different types of user accounts such as sys, hr, OE, Scott and many more. These are the user accounts which oracle creates for you. What if you want to create your own user accounts? In this tutorial you will learn how to create a user account in oracle database.
Among several different ways, creating a user account using Create User statement is the most common one. Using “create user” statement you can create and configure new database user and by using this database user you can log on to your database.
Create and user both are the keywords followed by the username. As username you can give any name of your choice. After that we have IDENTIFIED BY clause which helps the oracle engine in authenticating the user. There are three ways of authenticating a user
Default Tablespace: In the default tablespace you have to specify the name of tablespace where this user will create its object. By objects I mean tables, indexes, synonyms etc. This is an optional clause, if you omit this clause then all the objects created by this user gets stored in the database default tablespace which is “users” tablespace in most cases.
Temporary Tablespace: All the temporary segments created by this user get stored here.
Temporary segment such as temporary tables; Similar to default tablespace you have to specify the
name of the temporary table space here. But make sure the table space you are going to specify here
must be temporary tablespace. This is an optional clause so if you omit this clause then all the temporary
objects created by this user get stored in the database default temporary tablespace which is “temp” tablespace.
Always remember the name of tablespace you will specify here must be a temporary tablespace and should have a standard block size.Also it should not be an undo tablespace or a tablespace with automatic segment-space management
Quota clause: Quota clause can help you in specifying the maximum amount of space that the user can allocate in the tablespace. In single create user statement you can have multiple quota clause. As you can see here our quota keyword is followed by size. You can either specify a limited size such as 50M or 100M, M stands for Megabyte here or just write UNLIMITED for allocating space without any limits. After the size you have to specify the name of tablespace using ON clause.
Profile clause: Using profile clause you can specify the profile you want to assign to the user. By Assigning profile you can limit the amount of database resources the user can use.
It’s also an optional clause hence if you omit this clause database will assign a default profile to your user.
Password Expire clause: This is the kind of settings which force your user to change the password on the first login attempt or say before the user can log in to the database. This is also an optional clause.
Account clause: Using Account clause you can set the status of your user account. If you set the
status on lock then oracle will create your user account but disable its access. This means that
you will have your user account but you cannot use it. If you set the status on UNLOCK
then you are all free to use it. Unlock means status will be open. By default its set on
It’s again optional if you omit this clause. In case you do omit it then the Oracle will set the status of your account to default state on UNLOCK.
Here’s how to do this.
Step 1: if on Windows machine just set your oracle SID to your database in which you have created this user
Hope you enjoyed reading please share my videos and blog with your friends. Thanks & Have a great day!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.