Oracle database 12c-2

What is Oracle Database 12c?

Oracle database server

Oracle Instance

Starting Database

Working with SQL*Plus

WHAT IS ORACLE DATABASE 12C? Oracle database is the name of the database management system that comes from Oracle Corporation. Oracle Database 12c is latest version which is claimed to be the first database designed for Cloud computing. So c in 12c stands for Cloud computing.

Oracle12c is the latest release by Oracle Corporation. Oracle Database 12c is Database Management System that is used to store and access data. Oracle Database is by far the most widely used relational database management system (RDBMS).

This book is about Oracle Database. It doesn’t discuss about other products of Oracle Inc.

Oracle Corporation is also into Enterprise Resource Planning (ERP). It has Oracle Applications product that includes Oracle Financials etc.

One of the biggest advantages of Oracle Database has been its presence on around 100 different platforms. Oracle is quite

scalable, which means it can scale up and down very easily as the requirements change.

Oracle also provides Java Virtual Machine (JVM) as part of database. This enables oracle to run java programs. Starting from Oracle8i, oracle can run programs written either in PL/SQL or Java.

ORACLE INSTANCE Oracle instance is a collection of memory structures and processes that are used to manage oracle database. Each oracle database is to be accessed by one or more Oracle instances. If two or more instances are accessing the same database, it is called as parallel server architecture.

Starting from Oracle Database 12c, it is possible for one Container Database (CDB) to contain many Pluggable Databases (PDB). It is also known as Multitenant architecture.

In order to start using an oracle database, we must first start Oracle instance. Oracle instance will then open the database and make it available to users. By default, Oracle instance automatically starts during booting process.

It is beyond the scope of this book to discuss what Oracle instance actually contains. Please read “Oracle Concepts” manual for complete information about oracle instance.

In nutshell every oracle installation contains at least one Oracle Instance and one oracle database.

Oracle Database provides menu options Start Database and Stop Database to start and stop Oracle instance respectively.

Note: Starting and shutting down the database is the job of Database Administrator. As this book assumes that you are an application developer, it doesn’t get into those details.

STARTING SQL*PLUS SQL*plus is a tool that comes along with Oracle. It is used to issue SQL and SQL*PLUS commands. It provides command line interface through which we can enter SQL and SQL*PLUS commands.

To start SQL*PLUS, take the steps given below:

1. Select Oracle – OracleDB12Home from the list of programs

installed in your system.

2. Then select SQL Plus in that menu.

3. When you are prompted to enter username and password, enter system as username and password of system that is provided at the time of installation. 4. Then you get SQL> prompt, where you can give SQL

commands.

Username and Password 
Oracle is a multi-user database. Whoever is to access the database must logon to database. To logon we have to supply username and password. When the given username and password are recognized by Oracle, it will allow us to access data. A user can access only the data that belongs to him/her and not the data of others. However, it is possible for a user to grant privileges to others so that others can access his/her data.

Creation of users and management of overall security is the responsibility of Database Administrator (DBA). DBA is the person who makes sure that database is functioning smoothly. He is responsible for operations such as taking backup of the database, recovering the database in the event of failure, fine tuning database to get best performance. So, if you want to have a new account under your name, please consult administrator of your database.

Every user who wants to access oracle database must have an account in the database. These accounts are created by DBA. Each account is associated with username and password.

Oracle comes with a set of predefined accounts. The most important account is SYSTEM as any user logging into Oracle with this account will get administrative privileges.

Password for SYSTEM account is provided at the time of installation.

Note: When you enter into oracle using either system or sys then you become DBA. That means you get special privileges to perform major operations such as creating users etc.

How to enter SQL statements? SQL*PLUS allows two types of commands - SQL and SQL*PLUS.

SQL commands include commands of ANSI/ISO SQL and extra commands added to ANSI SQL by oracle.

The following are the rules to be followed while entering SQL commands.

o An SQL statement may be entered in multiple lines. o It is not possible to break a word across lines. o SQL statement must be terminated by semicolon (;).

The following is an example of SQL command. What this command does is not important at this moment.

SQL> select *

2 from courses 3 where fee > 5000;

In the above command, we entered the command in three lines. When you enter semicolon and press enter key then SQL*PLUS will take it as the end of the command. Also note that you have to press enter key at the end of each line.

Note: Both SQL and SQL*PLUS commands are NOT case sensitive.

How to enter SQL*PLUS statements? SQL*Plus statements are available only in SQL*PLUS. They are not part of standard SQL. SQL*Plus commands are mainly used for two purposes – editing SQL commands and formatting result of query.

The following rules are to be followed while entering these commands.

The entire command must be entered on a single line. o No need to terminate command with semicolon (;). o Commands can be abbreviated. However, the amount of

abbreviation is not fixed. Some commands are abbreviated to one letter some are abbreviated to 2 and so on.

The following examples show how to use CLEAR SCREEN command of SQL*PLUS.

SQL>clear screen

Or it can be abbreviated to

SQL>cl scr

CREATING USER BOOK In order to create sample data related to examples in this book, we need to create a new user called BOOK. Here are the steps to create a new user and grant required privileges.

1. Log into Oracle as user SYSTEM 2. Enter the following commands at SQL> prompt in

SQL*Plus

create user book identified by book

default tablespace users quota unlimited on users temporary tablespace temp;

grant connect, resource to book;

connect book/book

CREATE USER command is used to create a user. In Oracle Database 12c, we must prefix username with c## to create user in container database. So user BOOK becomes C##BOOK if you are working with container database. However pluggable database and non-container database don’t need any such prefix.

GRANT is used to grant required privileges to newly created user – BOOK, to enable user to login and create objects like tables.

HOW TO USE SQL DEVELOPER SQL Developer is an Integrated Development Environment (IDE) for working with SQL in Oracle Database. It is a free product provided by Oracle. It is a Java application so you need JVM (Java Virtual Machine) to run it.

We use this product throughout this book as it is the most productive tool available. However, you can try all commands that we use in SQL Developer using SQL*PLUS also.

Follow the steps given below to download and install SQL Developer.

1. Go to following URL and select the most appropriate

version based on your operating system and click on

Download button to download the software.

http://www.oracle.com/technetwork/developer-tools/sql-

developer/downloads/index.html

2. You need to sign in with your Oracle account before

download can proceed. In case you don’t have an Oracle

account, create one now. It is free.

3. Once it is downloaded, you get a single .zip file.

4. Extract the file into whichever drive you want.

5. It creates a folder called sqldeveloper. This folder is all

that you need.

6. Move into sqldeveloper folder and double click on

sqldeveloper.exe to start it.

Creating a connection

1. Once SQL Developer starts, create a connection to Oracle

Database 12c.

2. Go to Connections tab on the left and click green plus

sign icon and provide the following details to connect to

book account (shown in the screenshot).

• Connect Name : Book

• Username : book

• Password : book

• Hostname : localhost

• Port : 1521

• SID : oracle (Instance id given during installation)

3. Turn on Save Password option.

4. Use Test button to test your connection properties. If Test

button shows Success as the status (you can see that in

the bottom left corner of the screenshot), click on

Connect to connect to Oracle with these details. It

automatically saves connection details and provides

worksheet where you can enter SQL commands.

SQL Commands in Worksheet

Worksheet allows you to enter SQL commands and execute them. Type any SQL command in the editor and click on Run Statement icon, which is green right triangle (first icon in the toolbar). It is also possible to enter multiple commands and execute one of them by selecting (marking) the command to be executed.

You need not end commands with semicolon as we do it in SQL*plus.

SUMMARY In this chapter, we connect to Oracle through SQL*PLUS using SYSTEM account.

We created a new user called BOOK, which is used to store all objects discussed in this book.

We understood how to install and use SQL Developer, which is used for all examples in this book.