You are here:

Oracle/diff between instance,schema,database

Advertisement


Question
Hi Peter Choi,
     i am using oracle 10g(rdbms). i am beginer in plsql,mostly iam confusing with this words schema,database,context could you please explain me the diff between those three briefly?

thank you,
santhosh.

Answer
Hi Santhosh,

In Oracle (since inception), a schema is a collection of objects managed under a user account.

In a default Oracle installation, you can choose to install the sample user SCOTT which owns a few tables (EMP, DEPT, etc.). SCOTT is a SCHEMA.

There are other administrator type schemas that are installed that is used to manage the database instance.

(reference:
a. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6162110256950
b. https://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm)

A database is made of the "database repository", the structures that contain data, and an "instance". The instance represents all the background processes that support and make up the database. Thus to create a database, you first need to create the instance. Then that will allow you to create your database. Once your database is created, you can create your schema, which will then allow you to create the structures and objects to store (e.g. tables) and process (e.g. PL/SQL stored procedures) data.


(reference:
a. https://docs.oracle.com/cd/B19306_01/server.102/b14220/intro.htm)

I hope this clarifies the terminologies.

Peter

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Peter Choi

Expertise

I am a senior Oracle DBA, PeopleSoft HCM specialist and Project Manager with 15+ years experience. I have been working with PeopleSoft (HRMS/HCM 5, 7, 7.x and 8.9, 9.x), Oracle RDBMS (7.3 - 11gR2) on various Unix and MS-Windows platforms, and some Oracle Application Server (9i - 11gR1). I also have experience with the configuration and administration of PeopleSoft's Internet Architecture (PIA) and the Oracle 11g Fusion Middleware and Oracle Business Intelligence (OBI).

©2016 About.com. All rights reserved.