Prepared By : Uday Shah (HOD-IT)
Contact No : 7600044051
E-Mail : rupareleducation@gmail.com
Unit No : 3
Other Oracle Database
Management
Oracle
provides a facility to create different types of object inside the user schema
table is one such a kind of object used to store in the data.
With the different table user can
create and maintain relationship oracle introduce other data base object like,
view, sequence, synonyms, data base link etc…
1. View
·
A view can be considered as a store query or a virtual
table.
·
It is a customize representation of data from one or more
tables.
·
Only the query is store in the oracle data dietary.
·
The actual data is not copy anywhere.
·
So creating view does not tack any storage space.
·
A view can also hide query complexity there may be multiple
table join in the query but user access it using a view.
·
A view can have different column name then base table.
·
You can be created to limit the data accessibility to other
user.
·
All the operation performs on the view directly reflected to
their actual table.
·
A maximum number of column that we can define in a view
·
View is does not require any refresh method because when a
new record.
·
Is inserted deleted and updated in a base table
automatically reflect in a view
·
There are two types of view
·
Read only view
·
Updatable view
·
Read only view is used to just display to data can not
update the data where has an updatable view allow user to modify the data.
Syntax..
Crate or
replace view view_name as select * from table name where condition group by having.
Example
·
Create view v1 as select * from emp;
·
Create view v2 as select * emp no, ename from emp;
·
Create view v1(no,name) as select emp no, ename from emp;
According to
first example is include all rows and columns second example contain two
columns EMP no and ename to table v2 and third example contain two column and
table v3
View can be
update for drop using SQL query.
2.
Sequence
·
An oracle sequence is a name to generate sequential number.
·
The sequence is an object set generate numeric value.
·
It is an independent object and can be used with any table that
require is output.
·
Usually the sequence is used to enter data in a primary key
column to maintain unique vale.
·
In a multi user environment when many user to entry
simuantenosly there is a possibility of use of same number by the user and this
can be avoid using sequential.
·
Like constraint sequence exist only in the data directory.
·
Sequence can be configure to increases or decreases without
bound or to repeat a cycle upon reaching a bounding value.
Syntax
Create sequence sequence_name
Increment by number
Start with number
Max value name/no-max value
Min value name/no-min value
Cycle/no cycle
Check /no check
·
According to above syntax sequence is a object of an oracle
increment by define the increases or decrease value in a sequence.
·
Start with define the first number of a sequence default is
one min value define the lowest number of the sequence.
·
Max value define the largest number of sequence
·
Default is one min value define the lowest number of the
sequence.
·
Max value define the largest number of sequence cycle repeat the number after
reaching the handling value check define
the size of the block of sequence number help in a memory the default is a 20.
·
Start value is must be equal to or grater then min value.
For example
Create sequence
s1
Create sequence
s2
Start with 1
Increment by 2
Max value 20
Cycle check 10
·
According to above example we can define sequence by using
list name only or we can configure it as per our requirement oracle also allow
to alter using alter sequence command and dropped using drop sequence command.
·
Alter sequence sequence_name max value 100
3. Synonyms
·
A synonym is an alternative name for object with such as
table view, sequence, store procurer and other data base object.
·
A public synonyms is available to all user while a private synonyms
is to available only to the owner.
·
Synonyms can point to table view, sequence, procedure
function or package in a local data base.
·
Synonyms are frequently used to simplify SQL by given a
universal name to local or remote object.
Following is syntax
of synonyms
Create or
replace synonyms synonyms_name for object_name;
·
According to above syntax the default synonyms is a public synonyms
and it is acceptable call by the user synonyms name must be a unique name and
name of the object is must be a oracle object with is define under oracle data
dictionary.
Example
Create synonyms
department for dept;
·
According to above example department is alternative names
for dept table in an oracle synonyms not allow to alter command but it allow
dropping an existing synonyms using drop synonyms name command.
4. Database Link
·
As your data base grow in a size and number you will very like
a need to share data among them nearing data require a method of locating and
accessing the data in oracle remote data access such as query and update are
enable through the use of data base name.
·
Data base link dell oracle how to get from one data base
another if you will be frequently use the same connection to a remote data base
access.
·
Net 8 is usually started by the data base administrator or
the system manger.
·
A data base link established connection from the first data
base to second data base following an diagram that define how to data base link
word.
·
Diagram Here….
·
According to above diagram data base link specified the
following connection information that is the communication protocol such as
DCIPT use during the connection.
·
The host on which the remote database reside the name of the
name of the data base and the remote host
·
The name of the valid account in the remote data base.
5. Index
·
Index is data structure that helps to improve speed in rows from
a table.
·
It contains an entry for each value of index column of the
table and provides direct fast access to row to speed up query.
·
Index should be created on a column in the following
situation.
·
The column is requiring frequently.
·
A referential integrity constraint exists in the column.
·
A unique key integration constraint exists on the column.
·
Following are list of indexes
1.
Simple Index
2.
Composite Index
3.
Unique Index
4.
Bitmap Index
5.
Function Based Index
6.
B-Tree Index
·
Simple Index:
·
This is a default index in oracle that supports to arrange
data in one sequence logically.
·
This is work on based on Performance.
·
It is all ready created when any in
·
Indexes are used to search the rows in the oracle table quickly.
If the index is not present the select query has to read the whole table and
returns the rows. With Index, the rows can be retrieved quickly
·
Indexes are logically and physically independent of the data
in the associate table.
·
Indexes are optional structures associated with tables and
clusters. You can create indexes on one or more columns of a table to
speed SQL statement execution on that table.
·
Composite Indexes
·
You can create an index on multiple columns in a table. If
you want to create an index on the EMPLOYEE_ID
and DEPARTMENT_ID columns in the employees table.
·
For example, you can do so, and the result is called a
composite or concatenated index. Here's an example:
·
SQL> create index test_idx1 on employees(employee_id, department_id);
·
SQL> Index created
·
You can create composite B-tree indexes as well bitmap
indexes.
·
Unique Index :
·
Unique Index maintain on Unique Constraint Column index
which means that it require unique constraint.
·
Unique Index does not contain duplicate vales in it.
·
It user try to input duplicate value then it reject entire
record as well unique index does not maintain if duplicate records are found in
a table.
·
Unique index maintain both Unique Constrain and Unique Index
as well.
·
For example.
·
SQL> create unique index u1 on emp(empno);
·
According to above example Unique index create on Employee
Table employee No Column.
·
Bitmap Index :
·
Bitmap index are mainly use for distinct support system
or static data.
·
They do not support row level locking bitmap index can be
simple or concertinaed.
·
Bitmap index should be used when the update frequently of
index column is a less and the column has almost similar entry.
·
The entry in the index is bits ( 0 to 1) that’s why it’s
called bitmap index.
·
Bitmap Indexing is a special type of database indexing that
uses bitmaps.
·
This technique is used for huge databases, when column is of
low cardinality and these columns are most frequently used in the query.
·
Function Based Index
·
When we create index on a function and expiration that
invoke one or more column in the table bin index
·
A function based index complete the value of a function or
expiration invoke one or more columns and store in the index.
·
A function based can be either b-tree on bitmap.
·
Advantages :
·
A function is use for building the index can be an
arithmetic expiration or an expiration that contain SQL function.
·
A function based index increases the number of situation
where the data base can perform and index.
·
A function based index pre-compute and store the value of an
object column reference column.
·
You can create function based index on an object column or
reference column.
·
A function based index perform a powerful sorts incase of
upper and lower case.
·
Disadvantages :
·
The optimizer can use function based index only for cost
based optimization not for rule base optimization.
·
The data base does not use function based index.
·
We must ensure that for schema level or package level.
·
The index expirations cannot invoke and aggregate function.
·
B* tree index
·
This is a default index type and most commonly use index.
·
The index can be either unique or non-unique and it can be
simple or concatenated.
·
This type of index performs the best when a column has much
distinct value as the name implies b*tree index is a based on binary tree.
·
Construct with branch blogs and leaf blogs.
·
Branch blog contain the index column and address to another
index while Leaf blog contain key and row id for each matching row in the
table.
·
b*tree index contain parent child relationships and when the
reverse keyword is use each entry inside the index key column I reserve before
creation of index.
6.
Cluster :
·
Cluster is a schema object that contains data from one or
more tables.
·
In a cluster oracle data base store to gather all the rows
from all the tables that share the same cluster key from example.
·
A table cluster is a group of table at shear common columns
and store related data in the same blog.
·
When table are cluster a single data blog can contain rows
from multiple table for example.
·
A blog can store rows from both the table’s employee and
department.
·
The cluster key is a
column or columns that are cluster a single data blog can contain rows from
multiple tables for example.
·
Employee and department table have common column deptno that
is
·
We may specify the cluster key while creating the table
cluster.
·
The cluster key value is the value of the cluster key
column.
7.
Snapshot
·
A snapshot is a one kind of view to deliver the data to the
remote site in the oracle environment where the query execution is expensive in
trams of time.
·
Snapshot are update from one or more master table through
individual or in a query based it is also known as reface snapshot can contain
a where clause it means it can contain customized set of data.
·
Such a snapshot can be helpful for regional office or sales
target that or not require any corporate data we can achieve following also
using snapshot.
·
Easy network nodes
·
Mask deployment
·
Data sub setting
·
Disconnect component
·
Oracle offer several types of snapshot to meet the
requirement of different application in different situation.
What is locking?
·
Locking is a
mechanism to ensure data integrity while allowing maximum concurrent access to
data.
·
It is used to
implement concurrent access to data. It is used to implement concurrency
control when multiple user access table to manipulate its data at the same
time.
·
A lock is used to
access a database concurrency for multiple users this prevent data from being
corrupted or invalidate when multiple users try to write to the database.
·
Any single user
can only modify those database records(that is, items in the database to which
they have applied a lock that gives them exclusive access to the record until
the lock is released.
Locking issue
·
There are a many
issues that while locking in database. Locking is strategies that prevent data
lose and data corrupt locking issue describe as under.
Shared
Lock:
- also
known as Read-only lock. As the name suggests it can be shared between
transactions because while holding this lock the transaction does not have
the permission to update data on the data item.
- Shared
lock is requested using lock Shared instruction.
Exclusive Lock (X):
- Data
item can be both read as well as written.
- This
is Exclusive and cannot be held simultaneously on the same data item.
X-lock is requested using lock-X instruction.
DML locks
·
A DML lock, also
called a data lock, guarantees the integrity of data accessed concurrently by
multiple users.
·
For example, a
DML lock prevents two customers from buying the last copy of a block available
from an online bookseller.
·
DML locks prevent
destructive interface of simultaneous conflicting DML or DDL operations.
·
DML statements
automatically acquire the following types of locks.
·
Row locks
·
Page locks
·
Table locks
DDL Locks
·
The two basic
type of locks are DML (table) locks, and DDL(dictionary) locks.
·
A DDL lock
projects the definition of a schema object while the object is referenced in a
DDL operation.
·
Oracle
automatically acquire a DDL lock to prevent other DDL operations from
referencing or altering the same object.
·
If a DDL lock is
requested on an object that already has a lock on it, the lock request will
wait.
·
DDL lock
contention is rare because they are usually held only briefly.
·
There are a three
type of DDL locks: exclusive, shared, and breakable pares locks.
What are Latches?
·
Latches are a
close cousin to lock mechanisms in oracle that provide concurrency functions
and protect data integrity for oracle data structures.
·
Latches are a
type of locking device that are quickly acquired and released.
·
They prevent
multiple processes for running the same application code in the database more
then once at the same time, thus making available a serialization mechanism to
provide data integrity for transaction management with the oracle database
environments.
·
Latches are the
lightweight serialization mechanisms that are held and quickly released for
shorter durations of time as opposed to locks which are held for longer
duration of time within oracle.
·
Just as one would
lock a door with a deadbolt heavy-duty lock to protect long running
transaction. Conversely, when one just wants to close the door for a short
period of time, latch a transaction to keep it protected for a briefer period
of time than a lock would do.
Levels of Locks
·
Locks can be of
several levels. The level of a lock refers to the scope of the resource on
which the lock is requested or used, for example, whether the lock affects:
·
A single row
·
A single page
·
A table as a whole
·
An entire database
·
Row, page, and table lock levels are subject to
user control, whereas database and table "control" locks are taken by
commands and utilities.
Row
·
The most frequently lock level. Individual rows
are locked as they are encountered by a query.
·
Before a row lock is acquired, its page is
intent locked.
·
A transaction using a lock level of ROW will
hold intent table locks, intent page locks, and shared or exclusive row locks.
·
Row-level locking is supported for tables with a
page size greater than 2 KB.
·
Row-level locking does not work for the Database.
Page
·
Locks an entire page.
·
A transaction using a lock level of PAGE will
hold intent table locks and shared and/or exclusive page locks.
Table
·
Locks an entire table.
·
A transaction using a lock level of TABLE will
hold shared and/or exclusive table locks.
Lost update
·
A lost update
occurs when two different transaction are trying to update the same column on
the same row within a database at the same time.
·
Typically, one
transaction updates a particular column in a particular row, while another that
began very shortly afterward did not see this update before updating the same
value itself.
·
The result of the
first transaction is then “lost”, as it is simply overwritten by the second
transaction.
Deadlocks
·
A deadlock occurs
when two or more threads of control are blocked. Each waiting on a resource
held by the other threads.
·
When this
happens, there is no possibility of the threads ever making forward progress
unless some outside agent takes action to break the deadlocks.
Blocks
·
Simply put a
thread of control is blocked
·
When it attempts
to obtain a lock, but that attempt is denied because some other thread of
control is temporarily unable to make any forward progress until the requested
lock is obtained or the operation requesting the lock is abandoned. It’s called
blocks.
:: Best Of Luck ::
