Working with Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Working with Oracle Database

In this article, I am going to give you a brief introduction to how to work with Oracle Database. Please read our previous article, where we discussed the step-by-step process to download and install Oracle 19c Software in Windows 10 64-bit Operating System. When we install Oracle software internally two components are installed. Those are as follows.

1. Oracle Client
2. Oracle Server

Oracle Client:

By using the Oracle Client tool user can perform the following three operations.

1. Users can connect to the oracle server
2. Users can send requests to the oracle server
3. Users can receive responses from the oracle server.

Example: SQL Plus, Toad, SQL Developer, SQL Navigator, etc.

Oracle Server:

Oracle Server manages two more sub-components internally. Those are as follows.

1. Instance
2. Database

The instance will act as temporary memory which will allocate from ram and store data/information temporarily whereas a database is a permanent memory that will allocate from hard disk and store data permanently.

Working with Oracle Database:

When we want to work on the oracle database then we need to follow the following two steps.

1. Connect to Oracle: If we want to connect to the oracle database, then we need a database tool called “SQL Plus” which was inbuilt in Oracle software. Apart from SQL Plus, we can also use Toad, SQL Developer, SQL Navigator, etc.
2. Communicate with database: If we want to communicate with the database then we need a database communication language is called “SQL”.

Difference between SQL Plus and SQL:

SQL Plus:

1. It is a database tool/client tool.
2. It is used to connect to the oracle database
3. It can use as an editor for writing & executing the SQL Queries, PL/SQL code.

SQL

1. It is a database language.
2. It is used to communicate with the database
3. It provides sub-languages that will use to perform some operations (DD, DM, DQL/DR, TC, DCL) over the database.

How to connect to the Oracle database?

Before connecting to the oracle database we need to know the types of editions in oracle software. Every oracle software is having two types of editions. Those are as follows.

1. Oracle Express Edition (partial supporting features)
2. Oracle Enterprise Edition (fully supporting features)

The above two editions are having the default username is “system” and the password is created at the time of oracle software installation.

Oracle Expression Edition:

1. Supporting partial features
2. Not supporting recycle bin, flashback, purge, partition tables, etc.

Oracle Enterprise Edition

1. Supporting all features(complete)

Steps to connect to oracle:

1. Go to All Programs
2. Go to oracle 12c/18c/19c Folder
3. Open Application Development Folder
4. Click on SQL Plus Icon which will open the below window

Then Enter the username and password

1. Enter username: System
2. Enter password: abcd1988 (at installation time password)

Once you entered the username and password, press the enter button as shown in the below image. Here, the password is invisible.

If you provide the correct user credentials, then you will get the login successful message and you will be connected to the Oracle database as shown in the below image.

Note: To clear the SQL Plus screen use the following command.

clear screen;

How to create a new username & password in the oracle database?

You need to use the following syntax to create a new user in the Oracle database.

Syntax: create user <username> identified by <password>;

Example: CREATE USER ANURAG IDENTIFIED BY Anurag1234;

Here, ANURAG is the username and Anurag1234 is the password. So, let us execute the above query in the SQL Plus editor as shown in the below image.

If you are getting the above error, then execute the alter session set “_ORACLE_SCRIPT”=true; Command as shown below.

Now, again execute the create user command, and this time the user should be created as shown in the below image.

Note: User is created but this user is a dummy user because this user is not having permission to connect and create new tables in the database. So, permissions must be given to the user (ANURAG) by using the “grant” command by dba(system). Every user in the oracle server is called “schema”.

Granting Permissions to User in Oracle Database:

We need to use the Grant command to give permission to the user. The syntax is given below.

Grant Connect, Resource to ANURAG;

Here,

1. Connect => to connect to oracle database
2. Resource => to create new tables in the database.

So, let us execute the above command in SQL Plus as shown in the below image.

As you can see, we are getting Grant succeeded message.

How to Change the Password of a user in the Oracle database?

Let us change the password of ANURAG from Anurag1234 to Anurag123456. First, connect to the database using the ANURAG username and password as shown in the below image.

Once you are connected to the database. Then clear the screen by executing the Clear Screen; command. Now, let us see how to change the password. The following is the process to change the password.

Type PASSWORD and press enter as shown in the below image.

Once you press the enter button, it will be showing one message i.e. Changing Password for ANURAG, and also asking you to enter the OLD password. Enter the OLD password i.e. Anurag1234 and press the enter button as shown in the below image.

Once we type the old password and press the enter button, then it will be asked to enter the new password. Enter the new password as Anurag123456 and press the enter key as shown in the below image.

Once we type the new password and press the enter button, then it will be asked to retype the new password. Retype the new password as Anurag123456 and press the enter key as shown in the below image.

Once you retype the new password and press the enter button, then you will get the “Password changed” message.

Now, if you try to log in using the old password (Anurag1234), then you will get a logon denied error message as shown in the below image.

Now, if you try to log in using the new password (Anurag123456), then you will get a login successful message as shown in the below image.

How to create a new password for the user (when we forgot the password) in Oracle?

To do so, first, login to the database using the System user i.e. administrative user as shown in the below image.

Then alter the password of the user ANURAG by executing the ALTER USER ANURAG IDENTIFIED BY Anurag123; command as shown in the below image.

Once you execute the command, you will get a User altered message that means the password is changed of the user ANURAG to Anurag123.

How to Lock and Unlock users in the Oracle database?

When we connect to the oracle database sometimes, we will face a problem i.e. Error: ora-28000: the account is locked.

Let us understand this with example. So, what we are going to do is, first, we will lock the ANURAG account, and then we will try to log in using ANURAG Credentials. Then we will see, how to unlock the ANURAG account. The syntax to lock and unlock users in the oracle database is given below.

Syntax: alter user <user name> account unlock / lock;

Locking ANURAG account:

First, log in to the oracle database by using System user i.e. database admin as shown in the below image.

Once you login to the database using Admin credentials, then execute the alter user ANURAG account lock; command to lock the user ANURAG as shown in the below image.

Now, open another instance of SQL Plus and try to log in using ANURAG credentials and you will get an account lock message as shown in the below image.

Now, from the Admin SQL Plus instance, execute alter user ANURAG account unlock; command to unlock the user ANURAG as shown in the below image.

Now, try to log in using ANURAG credentials and the login should be successful as shown in the below image.

To view all list of tables in the oracle database: select * from tab;

To view data of a particular table: Syntax: select * from <table name>;

Example: select * from emp;

When we want to display the information/data of a particular table proper systematically then we need to set the following two properties are,

1) PAGESIZE n:

The number of rows displayed per page. Here “n” is represented by no. of rows. By default, a single page is displayed 14 rows.
Syntax: set pagesize n;
Example: set pagesize 100;

2) LINES n:

The number of characters in a single line. Here “n” is representing no. of characters.
Syntax: set lines n;
Example: set lines 100;

To clear SQL Plus editor screen:

Syntax: cl scr;
Syntax: clear screen;
(or)
Shift+delete (from keyboard)

To disconnect/exit from the oracle database:

Exit;

Structure Query Language (SQL):

The language which was used to communicate with the database is called Structure Query Language (SQL). The Structure Query Language (SQL) was introduced by “IBM” and the initial name of this language was “sequel” and later renamed with “SQL”.

SQL is also called “CLI” (Common Language Interface) because this is the language that is used to communicate with any RDBMS products such as Oracle, SQL Server, MySQL, DB2, etc.

SQL pre-defined queries are not a case – sensitive (write queries in either upper & lower case characters) but every SQL query should end with a “; ” semicolon.

Examples:

SELECT * FROM EMPLOYEE——error
select * from employee;
SeleCt * From employee;
Note: Every SQL query should end with a “;” semicolon.

Types of SQL Languages:

We have 5 sub-languages are there:

1. Data Definition Language (DDL):

1. Create
2. Alter
3. Rename
4. Truncate
5. Drop

Latest Features in DDL:

1. Recyclebin
2. Flashback
3. Purge

2. Data Manipulation Language (DML):

1. Insert
2. Update
3. Delete

Latest Commands in DML:

1. Insert all
2. Merge

3. Data Query / Retrieval Language (DQL / DRL):

1. Select

4. Transaction Control Language (TCL):

1. Commit
2. Rollback
3. Savepoint

5. Data Control Language (DCL):

1. Grant
2. Revoke

In the next article, I am going to discuss Datatypes in Oracle with Examples. Here, in this article, I try to explain how to work with the Oracle database and I hope you enjoy this working with the Oracle article.