Creating User / Schema in Oracle Database

To log into an Oracle database, you can use a user system or sys commonly used to create a user / schema. You can login to Oracle database by using sqlplus program. The trick is as follows:
Open a command prompt (cmd) in your Windows.
Type the command sqlplus system / [password] .Password here is the password of the user system that must be entered when first installing the Oracle database.

Create User

Once you have successfully logged into the Oracle database by using the System user, it is time you create a new user as needed. The syntax is as follows:

CREATE USER nama_user
IDENTIFIED BY password
DEFAULT TABLESPACE nama_tablespace
QUOTA UNLIMITED ON nama_tablespace;

Example:

CREATE USER codeitworld
IDENTIFIED BY code1tw0rld
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

User created.

If you leave User created, it means that you have successfully created the user in the Oracle database. Congratulations! The next step is to display the user list in the Oracle database.

Displays the User List

To display a list of existing users in the Oracle database, you can use the query as below, by first logging in using SYS or SYSTEM user.

select * from all_users;

Grant Access Right (Grant)

Before you use the user you have created. You must grant GRANT CONNECT permissions first so that the user you create can login to the Oracle database. If you do not give GRANT CONNECT to your database user, when you try to login to database you will get error message as below.

conn codeitworld;
Enter password:
ERROR:
ORA-01045: user CODEITWORLD;lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

To grant GRANT CONNECT access, you can use the command below :

GRANT CONNECT TO codeitworld;

Grant succeeded.

If the “Grant succeeded” message exits, you have successfully granted access rights. The next step you just try to login with the user database you just created, using the command as below.

conn codeitworld
Enter password:
Connected.

Congratulations you have successfully created the user and login with the user you have created.

There is another SQL command used to display the active user (which you are currently using):

show user;
USER is "CODEITWORLD"