cs5614 database management systems virginiatech1 database storage structures logical structures such...

11
CS5614 Database Management Systems VirginiaTech 1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database only. It is unknown to OS. Physical structures are those that can seen and operated on from the OS, such as the physical files that store data on disk Recovery related structures such as redo logs and database backups are used to recover the database after an OS failure, Oracle Instance failure, or media failure.

Upload: eleanore-snow

Post on 14-Dec-2015

212 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 1

Database Storage Structures

Logical structures such as tablespaces are created and recognized by the database only. It is unknown to OS.

Physical structures are those that can seen and operated on from the OS, such as the physical files that store data on disk

Recovery related structures such as redo logs and database backups are used to recover the database after an OS failure, Oracle Instance failure, or media failure.

Page 2: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 2

Page 3: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 3

Tablespace

The logical structure of the database consists of tablespace, segments and extents.

Tablespace is an area where Oracle places new objects.

A tablespace is a logical group of one or more physical datafiles or tempfiles.

Database contains one or more tablespaces.

Page 4: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 4

Blocks, Extents & SegmentsThe units of database space allocation are data

blocks, extents, and segments Block:

At the finest level of granularity, Oracle stores data in data blocks.

Extent: An extent is a specified number of contiguous data

blocks allocated for storing a specific type of information Segment:

A segment is a set of extents, each of which has been allocated for a specific data structure.

Each table data is stored in its own segment.

Page 5: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 5

Blocks, Extents, and Segments

Page 6: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 6

PCTFREE & PCTUSED

Page 7: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 7

Types of tablespaces Permanent tablespaces: These tablespaces are

used to store system and user data. Eg: USERS, SYSTEM

Temporary tablespaces: These tablespaces improves the concurrency of multiple sort operations, and reduce their overhead. Eg: sorting, grouping

Undo tablespace: Oracle database creates and manages undo data in this tablespace. Undo data: Oracle makes a copy of the original data before

modifying it. The original copy is called undo data

Page 8: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 8

Datafiles: These are the OS files that hold database data. The data is written to these files in a Oracle-proprietary format that cannot be read by programs other than Oracle database.

Tempfiles:These are the special class of datafiles that are associated with temporary tablespaces.

Control file is a binary file that keeps track of all the files' Oracle needs and where they are on the host machine.

Physical structures

Page 9: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 9

Physical structures (continued)

Server parameter file: This file contains all initialization parameters that Oracle Database uses.

Password file: used to authenticate a user who is logging in remotely as user SYS.

Page 10: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 10

Page 11: CS5614 Database Management Systems VirginiaTech1 Database Storage Structures Logical structures such as tablespaces are created and recognized by the database

CS5614 Database Management Systems

VirginiaTech 11

References

Oracle® Database Express Edition 2 Day DBA 10g Release 2 (10.2)

Oracle Database Adminstrator's reference 10g Release (10.1)

http://www.oracle.com/technology/documentation/database10g.html

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/tspaces.htm#i1006157

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/onlineredo001.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm