Database Storage Structures and Tablespaces
How Table Data Is Stored
A datafile with records
EMP
record
EMP
record
DEPT
record
SELECT SUM(sal) FROM
EMP
NATURAL JOIN
DEPT
WHERE dname=’ACCOUNTING’;
Physical Database Structure
.
•
Online redo log files
•
Password file
•
Parameter file
•
Archive log files
C
o
n
t
r
o
l
f
i
l
e
s
•
Data files
•
Alert and trace log files
•
Backup files
Tablespaces and Data Files
–
Tablespaces
consist of
one or more data files
.
–
Data files
belong
to only one tablespace
.
D
a
t
a
f
i
l
e
1
D
a
t
a
f
i
l
e
2
SYSTEM
and
SYSAUX
Tablespaces
–
The
SYSTEM
and
SYSAUX
tablespaces are
mandatory
tablespaces.
–
They are created at the time of database
creation.
–
They
must be online
.
–
The
SYSTEM
tablespace is used for core
functionality (for example,
data dictionary
tables).
–
The auxiliary
SYSAUX
tablespace is used for
additional database components
(such as the
Enterprise Manager Repository).
Actions with Tablespaces
Dropping Tablespaces
Viewing Tablespace Information
Segments, Extents, and Blocks
–
Segments exist within a tablespace.
–
Segments are made up of a collection of extents.
–
Extents are a collection of data blocks.
–
Data blocks are mapped to disk blocks
.
S
e
g
m
e
n
t
E
x
t
e
n
t
s
D
a
t
a
b
l
o
c
k
s
D
i
s
k
b
l
o
c
k
s
Logical and Physical Database
Structures
D
a
t
a
b
a
s
e
L
o
g
i
c
a
l
P
h
y
s
i
c
a
l
T
a
b
l
e
s
p
a
c
e
D
a
t
a
f
i
l
e
O
S
b
l
o
c
k
S
e
g
m
e
n
t
E
x
t
e
n
t
O
r
a
c
l
e
d
a
t
a
b
l
o
c
k
S
c
h
e
m
a
Viewing Tablespace Contents
•
12061_1_sel_ts_3
How Table Data Is Stored
T
a
b
l
e
s
p
a
c
e
T
a
b
l
e
A
T
a
b
l
e
B
S
e
g
m
e
n
t
S
e
g
m
e
n
t
R
o
w
s
C
o
l
u
m
n
s
T
a
b
l
e
B
l
o
c
k
s
R
o
w
p
i
e
c
e
E
x
t
e
n
t
Anatomy of a Database Block
B
l
o
c
k
h
e
a
d
e
r
F
r
e
e
s
p
a
c
e
R
o
w
d
a
t
a
G
r
o
w
t
h
Tablespaces and Data Files
•
The Oracle database stores
data
logically in tablespaces
and
physically
in data files
.
–
Tablespaces:
•
Can belong to only one database
•
Consist of one or more data files
•
Are further divided into logical units of storage
–
Data files:
•
Can belong to only one
tablespace and one database
•
Are a repository for schema
object data
Space Management in
Tablespaces
–
Locally managed
tablespace:
•
Free extents are managed in the tablespace.
•
A bitmap is used to record free extents.
•
Each bit corresponds to a block or group of blocks.
•
The bit value indicates free or used extents.
•
The use of locally managed tablespaces is
recommended.
–
Dictionary-managed
tablespace:
•
Free extents are managed by the data dictionary.
•
Appropriate tables are updated when extents are
allocated or unallocated.
•
These tablespaces are supported only for backward
compatibility.
Tablespaces in the
Preconfigured Database
–
SYSTEM
–
SYSAUX
–
TEMP
–
UNDOTBS1
–
USERS
–
EXAMPLE
Enlarging the Database
–
You can
enlarge the database
in the following ways:
•
Creating a new tablespace
•
Adding a data file to an existing tablespace
•
Increasing the size of a data file
•
Providing for the dynamic growth of a data file
How Table Data Is Stored
When a table is created, a segment is created to hold its data. A tablespace contains a collection of segments. Logically, a table contains rows of column values. A row is ultimately stored in a database block in the form of a row piece. It is called a row piece because under some circumstances the entire row may not be stored in one place. This happens when an inserted row is too large to fit into a single block or when an update causes an existing row to outgrow its current space.
Explore the physical and logical structures of databases, including how data files are stored, the role of control files and redo log files, tablespaces and data files organization, SYSTEM and SYSAUX tablespaces, actions with tablespaces, segments, extents, blocks, and more. Learn how different elements like columns, tables, rows, segments, extents, and tablespaces are interconnected in the database storage architecture.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
How Table Data Is Stored A datafile with records EMP record 5000 10 KING EMP record 1300 10 MILLER DEPT record 10 ACCOUNTING NEW YORK SELECT SUM(sal) FROM EMP NATURAL JOIN DEPT WHERE dname= ACCOUNTING ;
Physical Database Structure . Control files Data files Online redo log files Parameter file Archive log files Backup files Alert and trace log files Password file
Tablespaces and Data Files Tablespaces consist of one or more data files. Data files belong to only one tablespace. Data file 2 Data file 1 USERS tablespace
SYSTEM and SYSAUX Tablespaces The SYSTEM and SYSAUX tablespaces are mandatory tablespaces. They are created at the time of database creation. They must be online. The SYSTEM tablespace is used for core functionality (for example, data dictionary tables). The auxiliary SYSAUX tablespace is used for additional database components (such as the Enterprise Manager Repository).
Segments, Extents, and Blocks Segments exist within a tablespace. Segments are made up of a collection of extents. Extents are a collection of data blocks. Data blocks are mapped to disk blocks. Segment Extents Data blocks Disk blocks
Logical and Physical Database Structures Logical Physical Database Data file Schema Tablespace Segment Extent Oracle data block OS block
Viewing Tablespace Contents 12061_1_sel_ts_3
How Table Data Is Stored Columns Blocks Table A Table B Rows Segment Segment Table Tablespace Extent Row piece
Anatomy of a Database Block Block header Growth Free space Row data
Tablespaces and Data Files The Oracle database stores data logically in tablespaces and physically in data files. Tablespaces: Can belong to only one database Consist of one or more data files Are further divided into logical units of storage Data files: Can belong to only one tablespace and one database Are a repository for schema object data Database Tablespace Data files
Space Management in Tablespaces Locally managed tablespace: Free extents are managed in the tablespace. A bitmap is used to record free extents. Each bit corresponds to a block or group of blocks. The bit value indicates free or used extents. The use of locally managed tablespaces is recommended. Dictionary-managed tablespace: Free extents are managed by the data dictionary. Appropriate tables are updated when extents are allocated or unallocated. These tablespaces are supported only for backward compatibility.
Tablespaces in the Preconfigured Database SYSTEM SYSAUX TEMP UNDOTBS1 USERS EXAMPLE
Enlarging the Database You can enlarge the database in the following ways: Creating a new tablespace Adding a data file to an existing tablespace Increasing the size of a data file Providing for the dynamic growth of a data file Database SYSTEM tablespace INVENTORY tablespace
