7.Introduction to Schema Objects

Introduction to Schema Objects

v  Each DB user owns a single schema,  which has the same  name as the user.

v   The schema contains the data for the user owning the schema. It is a logical container for data structures. E.g. mana0809

v  Within a schema, each schema object of a particular type has a unique name. E.g. mana0809. Employee_master

Schema objects are created and manipulated with SQL

Schema Object Types

Ø  Tables

Ø  Indexes

Ø  Partitions

Ø  Views

Ø  Sequences

Ø  Synonyms

Ø  PL/SQL subprograms

Tables

The most important schema objects in a relational database

—  It is a collection of rows and columns

—  We can store data into the table and retrieve the data from the table

—  We can update the data and delete the data from a table

Eg: Branch_master

Indexes

—  Optional Structure associated with a table

—  Fast Retrieval of data by specifying indexed column in the where condition of sql querry

           E.g.Transaction_history :FIRM_ID, ACCOUNT_NO, TRADT

—  Create  index for most frequently used columns.

—  An index can be dropped or created without physically affecting the table for the index.(If you drop an index, then applications still work. However, access of previously indexed data can be slower.)

—  The presence of many indexes on a table degrades  performance

Partitioning

Ø  Partitions are pieces of large tables and indexes.

Ø   Each partition has its own name and may optionally have its own storage characteristics

—  When to Partition a Table

—  Tables greater than 2 GB should always be considered as candidates for partitioning.

—  Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

—  When the contents of a table need to be distributed across different types of storage devices.

Views

Ø  Views are customized presentations of data in one or more tables or other views.

Ø  You can think of them as stored queries.

Ø   Views do not actually contain data.

Ø  Restict the view of a table i.e. can hide some of columns in the tables.

Ø   Join two or more tables and show it as one object to user.

Ø  Restict the access of a table so that nobody can insert the rows into the table.

Ø  Views have two types

        1.  Simple View  (emp_master)

        2.  Complex View(branch_detail)

Sequences

Ø  A sequence is a user-created object that can be shared by multiple users to generate integers.

Ø  Typically, sequences are used to generate primary key values.

Ø  create sequence  emp_id_SEQ
minvalue 1
maxvalue 9999999999
start with 41
increment by 1
cache 20;

Ø   emp_id   := emp_id_SEQ.nextval;

Synonyms

Ø  A synonym is an alias for another schema object. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

Ø  CREATE SYNONYM

   CREATE SYNONYM branch for mana0809.branch_master;

PL/SQL subprograms

Ø  Subprograms

Ø   Subprograms are named PL/SQL blocks that can take parameters and be invoked.

Ø  2 types

Ø   procedure and functions

Ø  procedure to perform an action

Ø  function to compute a value. (Ex, Transno)

Schema Object Dependencies

—  Some schema objects reference other objects, creating schema object dependencies.

—  For example, a view contains a query that references tables or other views, while a PL/SQL subprogram invokes other subprograms.

If the definition of object A references object B, then A is a dependent object with respect to B and B is a referenced object with respect to A.

Ex,

CREATE TABLE test_table ( col1 INTEGER, col2 INTEGER );

CREATE OR REPLACE PROCEDURE test_proc

AS BEGIN

FOR x IN ( SELECT col1, col2 FROM test_table ) LOOP

-- process data

NULL; END LOOP; END;

/

—  After adding the col3 column to test_table, the procedure is still valid because the procedure has no dependencies on this column:

—  SQL> ALTER TABLE test_table ADD col3 NUMBER; Table altered.

—  However, changing the data type of the col1 column, which the test_proc procedure depends on in, invalidates the procedure:

—  SQL> ALTER TABLE test_table MODIFY col1 VARCHAR2(20); Table altered.

—  Running or recompiling the procedure makes it valid again, as shown in the following example:

—  SQL> EXECUTE test_proc

—  PL/SQL procedure successfully completed.

SYS and SYSTEM Schemas

Ø  All Oracle databases include default administrative accounts. Administrative accounts are highly privileged and are intended only for DBAs authorized to perform tasks

Ø  such as starting and stopping the database, managing memory and storage, creating and managing database users, and so on.

SYS & SYSTEM

Ø  The administrative account SYS is automatically created when a database is created.

Ø  This account can perform all database administrative functions.

Ø   The SYS schema stores the base tables and views for the data dictionary.

Ø  These base tables and views are critical for the operation of Oracle Database.

Ø   Tables in the SYS schema are manipulated only by the database and must never be modified by any user.