SQLPlus

SQLPlus is an executable program for the terminal, and it is bundled in Oracle Database XE, that serves as a tool for resources and database management.

SQLPlus doesn't have a visible user interface. Thus, it only runs in the terminal. It is oftentimes used for quick tasks in managing the database.

Running SQLPlus

To run SQLPlus, open any available terminal or command shell in your computer (Command Prompt on Windows). Then, type sqlplus and press enter on your keyboard.

Additional Note:

Once you opened the terminal, you will see the current home directory and the prompt arrow. 

On Windows, it may be like this, where [USER] is the username of a user who is currently logged in:
C:\Users\[USER]> sqlplus

On Linux, totally different, which is only a dollar sign, or sometimes it also displays the username and the computer name—and are separated with an at sign (@):
$ sqlplus

or (Linux Mint):

username@COMPUTERNAME:~$ sqlplus

Administrator's Credentials

Username: sys as sysdba

Password: password

Now, type sqlplus in your terminal and enter. sqlplus prompts for the username and password (from the administrator, which is shown above) used to connect to the database server. When typing for a password, the characters you typed will not display, so you have to use your awareness if you think you have typographical errors.

NOTE: If SQLPlus denies your login transaction 3 times—especially when entering the wrong password or logging in using a nonexistent account, SQLPlus will exit immediately.

Creating a User Account

To create a user account, type this following line and press Enter:

CREATE USER ict12a IDENTIFIED BY prog3113;

What this line does is it basically creates an account (ict12a) with its corresponding password (prog3113). Take note that it is not mandatory to capitalize keywords; there are only capitalized to easily indicate that these are indeed keywords. Once executed, it should display "User created," indicating a successful creation of the account with the provided credentials.

Granting Permissions to the User

A freshly created user doesn't have the essential permissions; even logging in would need one vital permission—CREATE SESSION—the system creates a session for the user connected to the database; by allowing the system to create sessions for the specific user, the granted user will be able to log in.

To allow the user to run various system operations, type this line of code on the terminal to grant multiple permissions to the user:

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM TO ict12a;

These are the permissions or privileges mentioned:

CREATE SESSION - Allows the user to connect to the database.

CREATE TABLE - Allows the user to create and manage tables.

CREATE VIEW - Allows the user to create views.

CREATE SYNONYM - Allows the user to create synonyms.

For more information about views and synonyms, surf across the internet (search on Google) for reliable web sources or articles about them.

Creating a Table Might Not Work

A user cannot create a table if they don't have any privileges on the system tablespace. There are two ways to resolve this issue. Before that, connect to the database using the administrator's credentials, by typing conn on the terminal, then type those credentials correctly.

Once connected, you can either do the following to resolve the issue:

1. Making the tablespace quota from the user unlimited.

ALTER USER ict12a QUOTA UNLIMITED ON SYSTEM;

2. Granting the unlimited tablespace privilege to the user.

GRANT UNLIMITED TABLESPACE TO ict12a;

This will now allow the grantee (the user granted) to create tables without any error. Type conn on the terminal again, then type the user's credentials correctly.

Additional Notes

To clear the screen, just type host cls and enter. It is helpful specifically for decluttering printed text.