Online Training on Oracle Databse

1
What is Oracle Database

• Oracle Storage Structure

2
DATABASE

• Why we Need a Database
• To Collect, Process and Distribute the Data.
• I can do that using Excel, Notepad a simple C,VB
  code etc. Why Database ?
• Consider the Challenges of the Above approach.
• DBAs Perspective (Developers have
  many more reasons)
• Manage 100s of files.
• Manage 100s of users.
• Just need to see One Line/record from one of the
  file.
• Changes to the Data and propagation of the
  Changes.
• Files getting Lost, Corruption due to Software,
  Hardware Issues.
• Backup Restore.

3
How Oracle DB Does IT
4
A Typical Unix Kernel
Modules
Hardware
5
Database

• Physical
• Parameter file
• Control files
• Data files
• Temp files
• Redo Log files
• Archive Log files
• Alert Trace Files
• Backup files

• Logical
• Tablespace
• Schema
• Segments
• Extents
• Oracle Data blocks

6
Oracle Storage Structure
Schema_1
Table_1
Table_2
Segment_1
Segment_2
Table_3
Segment_3
Extent_3
Extent_2
Extent_1
Block
Block
Block
Block
Block
Block
Block
Table_4
Block
Block
Block
Block
Block
Block
Block
Block
Table_5
Free Block
Free Block
Free Block
Free Block
Free Block
Free Block
Free Block
Free Block
7
A Simple Create Table Statement

• CREATE TABLESPACE TableSpace_1
• DATAFILE '\oraserv\ORADATA\Ts1_df_1.dbf'
• SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M
  
• Alter tablespace TableSpace_1 add datafile
  '\oraserv\ORADATA\Ts1_df_2.dbf Size 10GB
• Alter Database Datafile '\oraserv\ORADATA\Ts1_df_
  2.dbf resize 20GB
• CREATE TABLE Schema_1.Table_1
• ( Col_1 VARCHAR2(100), Col_2 NUMBER, col_3
  DATE )
• TABLESPACE TableSpace_1
• STORAGE ( INITIAL 16K MINEXTENTS 1
  MAXEXTENTS UNLIMITED PCTINCREASE 0 )

8
Schemas and Schema Objects

• Collection of database objects
• Tables
• Views
• Sequences
• Synonyms
• Indexes
• Procedures
• Functions
• Packages
• Triggers

9
Data Blocks

• The smallest unit of Input/Output used by Oracle
  database.
• The size of data block for any database is fixed
  at the time of creation of the database
• Some values of the data block size are 2KB, 8KB,
  16KB, and 32KB.
• Oracle recommends a size of 8KB

10
Extents

• The next level of data storage.
• One extent consists of a specific number of data
  blocks
• One or more extents in turn make up a segment.
• When the existing space in a segment is
  completely used, Oracle allocates a new extent
  for the segment.

11
Segment

• A segment consists of a set of extents
• Each tables data is stored in its own single
  segment.
• Each indexs data is stored in a single segment.
• More extents are automatically allocated by
  Oracle to a segment if its existing extents
  become full.
• The different types of segments are the data
  segments, index segments,rollback segments, and
  temporary segments.

12
Control Files

• Contain a list of all other files in the database
• Key information such as
• Name of the database
• Date created
• Current state
• Backups performed
• Time period covered by redo files

13
Redo Log Files

• Store a recording of changes made to the database
  as a result of transactions and internal Oracle
  Activities
• When Oracle fills one redo log, it automatically
  fills a second.
• Used for database recovery