For more references Please Visit :

https://easy2learncode.in

RDBMS Using Oracle

unit-5
(Unit-5: Oracle Database Structure and Storage
Database, Resource Management and Task
Scheduling)
- Sojitra Dhruvi H.

22
 RDBMS Using Oracle unit-5

Oracle Instance Architecture

 When a database is started on a database server,
oracle allocates a memory area that called the system
global area(SGA).

 SGA starts one or more oracle processes.

 The combination of the SGA and the oracle processes

is called an oracle instance

Instance architecture :=
1. Database processes
2. Memory structure
3. Data files

https://easy2learncode.in Page 2
 RDBMS Using Oracle unit-5

 Processes
Process is a job or task.
An oracle server has three types of processes.
1. User/client process
 This process is the user’s connections to the
RDBMS system.
 When the user name and password is valid,
session is established and a user process is
created on the client side to act on behalf of
the user.

2. Server process

https://easy2learncode.in Page 3
 RDBMS Using Oracle unit-5

 Server process communicates between the

user process and database and according to
that it returns back the data.

3. Background process
 Background process are :
 DBWR(database writer)
 LGWR(log writer)
 SMON(system monitor)
 PMON(process monitor)
 CKPT(check point)
 ARCH(Archive Process)
 RECO(Recover Process)
 LCKN(Locking)
 SNP(snapshot/job process)
 DN(Dispatcher Processes)

 Memory Structure.
1)SGA (SYSTEM GLOBAL AREA)
 Shared pool
 Database buffer cashe
 Redo log buffer
 Java pool
 Large pool
2) PGA(PROGRAM GLOBAL AREA)

https://easy2learncode.in Page 4
 RDBMS Using Oracle unit-5

Data files

 Control files
 The control file resides on the operating system file
system.
 This files contain the location of data files, location of
redo log files.

 Redo log files

 The REDO log files are used to store redo information.
 The redo log file is necessary for proper recovery.
 If any file is lost in your memory , you will not be able to
recover in the event of a system failure.
 Therefore you must protect the redo log file against any
kind of failure.
 Ex. Rollback

 Initialization Parameter(parameter file)

 The parameter file which contains a number of
parameter. That can be used to configure the oracle
instance
 Where is the initialization parameter file
located?
 C:\oraclexe\app\oracle\product\10.2.0\server\da
tabase
 The initialization parameter file is an ASCII text file
containing a list of parameters.

https://easy2learncode.in Page 5
 RDBMS Using Oracle unit-5

 Every database instance has a corresponding

initialization parameter file & ORACLE_SID
parameter.

 Every database have its own & unique initialization

parameter file.

Creating & Altering Database

 Database creation occurs in two separate phases: the first
phase involves the actual database creation. The redo log
files, the control files and the data files necessary to create
the SYSTEM tablespace.

 The second phase involves adding tablespaces, table,indexes

and other oracle objects that are used to store the specific
data.

o The maximum size of a data file is 32 GB.

o The maximum no. of data files per tablespace is 1022.
o The maximum size of tablespace is 32 TB.

 This SYSTEM tablespace is where important structure such

as the data dictionary are kept.

Syntax :

CREATE DATABASE DB_NAME

[datafile [Autoextend on/off maxsize n/UNLIMITED] ]
[MAXLOGFILES n]
[MAXDATAFILES n]
[ARCHIVELOG/ NOARCHIVELOG]
[MAXLOGMEMBERS n];

https://easy2learncode.in Page 6
 RDBMS Using Oracle unit-5

Example :
Create database firstdb
datafile '/disc03/firstdb/system1.dbf ' SIZE 20M
LOGFILE '/disc04/firstdb/log1.log '
'/disc05/firstdb/log2.log ' SIZE 512K;

 ALTERING Database

ALTER DATABASE ADD LOGFILE ‘disk03/firstdb/log3.rdo’

SIZE 512K;
ALTER DATABASE RENAME FILE ‘disk03/firstdb/log3.rdo’
TO ‘disk04/firstdb/log4.rdo’ ;

Opening & shutdown Database

(1)Startup Stages
To open and use as database, the instance must be passed
through three stages of startup

Instance Creation (starting)

Mounting the database

Opening the database

 NOMOUNT Mode :
It reads only SPFILE or PFILE and allocating SGA.
EX., startup nomount

https://easy2learncode.in Page 7
 RDBMS Using Oracle unit-5

 MOUNT Mode:
It reads SPFILE or PFILE and open control files.
Ex., startup mount
Or
Alter database mount

 OPEN Mode:
 It reads SPFILE or PFILE, open control files, datafiles
and redo log files.
Ex., startup
Or
Alter database open

(2) SHUTDOWN STAGES

Shutdown modes A I T N
Allow new connections No No No No
Waits until current sessions No No No Yes
end
Waits until current Transaction No No Yes Yes
end
Forces a chechpoint and closes No Yes Yes Yes
files

Shutdown modes:

 A = ABORT
 I = IMMEDIATE
 T = TRANSACTIONAL
 N = NORMAL

https://easy2learncode.in Page 8
 RDBMS Using Oracle unit-5

Ex.,
 Shutdown
 Shutdown transactional
 Shutdown immediate
 Shutdown abort

Tablespace(Create, Alter, Drop)

 An oracle database consists of one or more logical data
storage units called tablespace.which stores the original
data.
 When you create a database the system tablespace is created
by default.
 Tablespace can hold any one of four types of segments:
 Data segments
 Index segments
 Rollback segments
 Temporary segments
Syntax:

Create tablespace <name> datafile <spec>

[minimum extend<no>]
[logging/no logging]
[ONLINE/OFFLINE]
[permanent/temporary]
[AUTOEXTENDED ON/OFF MAXSIZE <N>/
UNLIMITED]

EX.,
 Create tablespace tbs_demo1 datafile 'data_ff1' size 100k;

Alter tablespace
 Add a DATAFILE- Example.

https://easy2learncode.in Page 9
 RDBMS Using Oracle unit-5

 alter tablespace tbs_demo1 add datafile 'data_ff2' size 20M

autoextend on;

Drop a tablespace
drop tablespace tbs_demo1 including contents;

Rollback Segment
 Rollback segments are areas in database, which are
used to temporarily save the previous values when
some updates(UPDATE/INSERT/DELETE) are going
on.

 This is allows users to UNDO.

Syntax:

 CREATE[PUBLIC] ROLLBACK SEGMENT Rbs_name

option(s)
Where., option:-

 Tablespace tablespc_nm storage(initial n[k|m], NEXT

n[k|m], MAXEXTENTS n)

Alter rollback segments

 Alter ROLLBACK SEGMENT segment_nm
[STORAGE(NEXT N, MAXEXTENTS N)]
[ONLINE|OFFLINE]

TYPES OF ROLLBACK SEGMENTS:-

 SYSTEM ROLLBACK SEGMENTS
https://easy2learncode.in Page 10
 RDBMS Using Oracle unit-5

this is only used for transactions that occur inside the

system table space.
 TRANSACTION ROLLBACK SEGMENTS:-
When you commit a transaction, oracle releases the
rollback information.

https://easy2learncode.in Page 11
 RDBMS Using Oracle unit-5

Oracle Blocks
 A block is the smallest unit of storage in an oracle
database.
 Data block size is defined during the creation of a
database.
 They are the smallest unit of storage that oracle can
allocate to an object.
 They are physically stored on disk.

SEGMENT

BLOCK
BLOCK

EXTENT EXTENT

EXTENDS
 It consists of data block.
 An extends is used to minimize the amount of
wasted(empty) storage.

SEGMENT
 A segment is a set of extent used to store a particular
type of data.

https://easy2learncode.in Page 12
 RDBMS Using Oracle unit-5

 Oracle create different types of segment for different

types of database objects like, data segment, index
segment, rollback segment.

Import
 The Import utility allows you to restore the database
information held in previously created Export
files(Dump files).

 Import modes are User, table, Full Database

 Syntax:

 IMP <user name/password> file =<filename>

fromuser=<username>tables=<tablename>

Example: steps to import emp_info table.

1. IMP
2. Username
3. Address of an exported file which you want to import
on your database
4. Successfully imported

https://easy2learncode.in Page 13
 RDBMS Using Oracle unit-5

Export
 Export is a logical backup of database. This utility
copy the data and database to a binary OS file in
special format. i.e., moves data out of oracle database.

EXPORT MODES:

MODE DESCRIPTION

USER User’s Objects And The Data Within Them Is

Exported.

TABLE Specific table is exported.

FULL DATABASE Full database is exported.

Syntax: EXP username/password

Example: steps to export emp table.

1. EXP
2. GIVE USER NAME
3. GIVEN A NAME TO AN EXPORTED FILE
4. EXPORTED SUCCESSFULLY

https://easy2learncode.in Page 14
 RDBMS Using Oracle unit-5

SQL*Loader
 SQL * LOADER is a high speed data loading utility that
loads data from external files into tables in oracle
database.
 It is only way that you can load data created in
another DBMS into oracle.
 SQL * LOADER loads data using a control file.

https://easy2learncode.in Page 15
 RDBMS Using Oracle unit-5

SQL * LOADER FILES

1. CONTROL FILE
 The Control file is a text file written in a language
that SQL * Loader where to find data, how to parse
and interpret data, and where to insert data.
 It also specify how to interpret data, what column
and tables to inserts the data into, and also include
input data file management information.

2. LOG FILE
 When SQL * LOADER begins execution, it creates a
LOG FILE.
 If it can not create a log file, execution terminates.
 The LOG FILE contailns a detailed summary of the
load, including a description of any error that occur
during the load.

3. BAD FILE
 The BAD FILE contains rejected records either by
sql*loader or by oracle.
 Whenever you insert the data into a database you run
the risk of that insert failing because of some type of
error like integrity constraints represents the most
common type of error.
 Whenever sql*loader encounters a database errors
while trying to load a record it writes that records to
a file known as the bad files.

https://easy2learncode.in Page 16
 RDBMS Using Oracle unit-5

4. DISCARD FILE
 The discard file contains the records that where
filtered out of the load because they did not match
any record selection criteria specified in the control
file.
 Discard file are used to hold records that do not meet
selection criteria specified in the sql*loader control
file.
 Discard file are optional you will only gate a discard
file if you have specified a discard file name.

5. DATAFILE
 A datafile can be associated with only one tablespace
and only one database.
 Oracle creates a datafile for a tablespace by allocating
the specified amount of disk .When a datafile is
created.
 When a datafile is first created, the allocated disk
space is formatted but does not contain any user data.
However, Oracle reserves the space to hold the data
for future segments of the associated tablespace

https://easy2learncode.in Page 17
 RDBMS Using Oracle unit-5

Follow Us :

https://easy2learncode.in Page 18
 RDBMS Using Oracle unit-5

https://easy2learncode.in Page 19