Oracle - SPLessons

Chapter 14

Oracle Schema

Oracle Schema

Oracle Schema is a relevant capsule for data structures. A database client account has a user name and password and having exact database privileges. Each account of a client reserves a individual schema, and contain similar term as of the client. Tables and indexes are the examples of Oracle Schema objects. Schema objects manipulate and creates schema through SQL.

The Oracle Schema holds the information for the client containing the schema. For instance, the manager account reserves the manager schema, in these schema items are present in the manner to employee table. Each schema object has a unique name. i.e, manager employee refers to the table employee in the manager schema.

Oracle Database schemas

A Oracle database schema  of a database framework is its structure portrayed in a formal dialect upheld by the database administration framework. The expression”schema” alludes to the association of information as a plan of how the database is constructed. The formal explanation of a database schema is an arrangement of equations called integrity constraints implemented on a Oracle database.

These integrity constraints guarantee the similarity between parts of the Oracle Schema. A database can be viewed as a structure in acknowledgement of the database dialect. All requirements constraints are expressible in the same dialect. The conditions of a made reasonable outline are changed into an unequivocal mapping, the database schema. “A database schema determines, in the light of the database administrator’s knowledge of possible applications, important to the conceivable end-clients, the facts that can enter the database.

In data dictionary, the database will stores it schemas. Although in text database language a schema is defined, the term is often used to refer to a graphical depiction of the database structure. In other words, schema is the structure of the database that defines the objects in the database. Schemas and table space doesn’t contain any relationship. The objects for a schema can be contained in different table spaces and table space can contain objects from different schemas.

Common Oracle Database Objects

Oracle Database schema object contain the following types 

Schema object types

Oracle database allows to generate and mould different forms of schema items. The succeeding are the sorts of schema items.

  • Tables
  • Partitions
  • Index
  • Sequences
  • Views
  • Dimensions


Tables are the most crucial schema items in the comparative database. A table holds the information values in a row.


Partitions are data items of huge set of indexes and tables. Every allotment has a unique term name and contain individual repository components.


These are schema items that consists of an entry for every indexed row of the table.

  • Index gives explicit executions to rows.
  • Database backing with multiple sorts of index.
  • Organised table data is stored in the index structure.


Is a client-oriented item, so can be manually distributed to different number of clients to develop integers.


Views is a personalized presentation of data in many tables. Views doesn’t  contain any data.


The dimensions describes, parent and child affairs among combination of column arrangements. Position of all the columns in the column arrangements will be defined within the equivalent table. The measures are generally used to describe information like purchaser, item and accessions.

Schema object dependencies

Few sorts of schema items can be referred through different items. For instance, a view can be termed as a query and refers to tables, contain the body of sub-program that includes SQL statements and can refer to other objects.

Oracle database contributes an automated tool to assure that a inferior item is constantly updated with regard to its specified items. While creating a specified item, the database marks the dependency among the referred objects and the dependent object. While a referred object alters the way that control the constantly item, the database imprints the constantly item as baseless.

create table <test_table>(column_name1 int,column_name2 interger);
create or replace procedure test_proc
for x in(select column_name1,column_name2 from test_table)
–process data
end loop;
Test_table => The accurate table name in the database
Test_proc => The accurate table name in the database
Column_name => The names inserted in the column

Sample Schema

Oracle database contain a set of schema called sample schemas, that are interlaced schemas and implements Oracle authentication and didactic components to explain similar database operations. The manager schema contain a sample schema and consists data about an employee, administration and area. Employee contain employee-id, first-name, last-name, email-id. Administration contain dept_id,dept_name and area contains city, street and postal code.

Schema object storage

Schema objects storage is a type of logical storage structure called a segment. Database saves a schema item inside table space and no communication exits among the table spaces and schemas. A table space contains items against distant schemas, and the objects for a schema can be enclosed in various table spaces.

For example, a non_partition heap-organize table are an index created segments. Further schema items, functioning as views and sequences, consists of only metadata.

System and SYS schema

All the Oracle databases contain by_default administrative accounts. This administrative accounts are exceptionally having high privileges endure designed based on DBAs to access particular operations like start and stop  database, manage the storage and memory, generating and guiding database clients.

The SYS legislative reports get generated while the database account is generated. This reports will function all database legislative resources. The SYS schema holds the common tables values in the table. These common views and tables plays a major role in Oracle Database. Tables in the SYS schema will alter or modify the database and user cannot modify the database as user will have only restricted privileges.

Table types

It is the key section of information management in the database. A table defines an entity, that describes which data should be registered in the database. For instance, the student could be an entity.

Oracle database can be classified into two types.

Rational tables

Rational tables have simple columns in the table and are typical table types.Here data can be accessed or arranged in many different ways without having to reorganize the database tables.

Rational tables can also be created in heap organised table, index organised table and external table.

Heap organised table

The create table will create heap organised table by default and heap organised table will not store data contained in rows in proper order.

Index organised table

Index organised table will arrange the data in order based on the primary key values. Index organised table will increase the performance and use disc space efficiently.

External table

External tables will only read the data, whose data is saved outside the database and it’s meta data is saved inside the database.

create table <table_name>(column_name1 datatype (size),column_name2 datatype (size));

Table_name => The accurate table in the database.

Column_name => The name inserted in the column.

By viewing the below example, creating a simple table can be easily performed.

create table student_01(stu_id int,stu_name varchar(255),course varchar(2
55),fee int);
Query OK, 0 rows affected (0.36 sec)

In the above example student_01 table has been created will all the necessary column name values.

Object table

Object table columns corresponds to top level attributes of object types that defines in the data type attributes.

It is a client characterized datatype along method, names and attributes. It is an appropriate type of table in which each row serves as an item and make it achievable to actual-sphere entities models such as seller and purchaser orders in the database as objects.

This doesn’t create any storage but creates the logical structure of a table.

create table <table_name> as object(column_name1 datatype (size),column_name2 datatype (size));

Table_name => The accurate table in the database.

Object => Is the reference to the table in the database.

Column_name => The name inserted in the column.

By viewing the below example, creating objects in the table can be easily understand.

create table department_typ AS OBJECT(DEPT_NAME VARCHAR(255),DEPT_ADDRESS VARCHAR(255));
Query OK, 0 rows affected (0.36 sec) 

In the above example department_typ table has been created will all the necessary column name values as an object reference.

Tables Rational

Table relationship contain physical and logical table relationship. On the make table page we made three tables, individual, telephone and address. These three elements have the accompanying cardinal relationship to each other.

Logical relationship

The relationship between two tables can be communicated in significant terms. In our configuration it is feasible for a man to have numerous locations. For instance, we can have a place of residence, a street number, and obviously. A man can likewise have more than one telephone number. As should be obvious our database configuration would appear to fit a “certifiable” usage of these three tables. The outline likewise gives the client adaptability in characterizing what number of locations and telephone numbers they wish to store. Coherent outlines are key and frequently overlooked from a database plan. They are crucial on the grounds that they obviously and graphically convey our table connections.

physical relationship

The physical diagram depicts the primary key and foreign key relationship between our entities. Our primary table is “person”. The foreign key column is the column “person_num” in both the address and the phone tables respectively.


  • Oracle Schema – Database schema contains a coherent capsule called information design.
  • Oracle database schemas – A Oracle database schema  of a database framework is its structure portrayed in a formal dialect upheld by the database administration framework.
  • Schema object type – Creates and manipulates the objects.
  • Schema object dependencies – Schemas that defines in the table are called object dependencies.
  • Sample schemas – This are inter linked schemas.
  • Schema object storage – Stores index and segments values.
  • System and SyS schemas – This schemas are the privileges for system administrator.
  • Table types – Contain information and regarded as a database.
  • Table relational  –  contain logical and physical relationals