Transcript Logical Database Layouts - University of South Alabama

Database
Chapter Processing
3 - OFA
Chapter 3
Logical Database Layouts
Optimal Flexible
Architecture
Chapter 3 - OFA
image source: http://www.iprimus.ca/~mariolam/flexibility.html
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
OFA
Chapter 3 - OFA
• The OFA standard is a set of configuration
guidelines for fast, reliable Oracle
databases that require little maintenance.
• At the highest level, it is designed to
logically separate objects by object type
and activity type.
• It is the structure you get if you create a
default database
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
System tablespace
Chapter 3 - OFA
• At a minimum you have to have a SYSTEM
tablespace
• It would be a very poor design to put all
objects into one tablespace.
– Data dictionary should be isolated
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
Data tablespaces
Chapter 3 - OFA
• Our author suggests that data tablespaces
should be organized by application
• The data for each application should
isolated from the system tables and other
tablespaces
– For example, human resources, accounting,
sales, inventory management
– One database, many applications
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
Index tablespaces
Chapter 3 - OFA
• Because of concurrent I/O, indexes should
be stored separately from their associated
tables.
• In my installation, my assumption was that
the indexes would be in the indx01.dbf file
• I queried the dba_indexes view and found...
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
Index tablespaces
Chapter 3 - OFA
Primary Key
indexes in User
tablespace
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
Index tablespaces
Chapter 3 - OFA
USER tablespace is in the USERS01.DBF file.
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
Index tablespaces
Chapter 3 - OFA
So I moved it...
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
Index tablespaces
Chapter 3 - OFA
And now it’s in the index data file
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
Tools tablespaces
Chapter 3 - OFA
• Used to store tables created by Oracle or
3rd party tools (applications) that create
tables owned by the SYSTEM account
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
User tablespaces
Chapter 3 - OFA
• For development projects
• All other tables should be created by the
DBA
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
Other tablespaces
Chapter 3 - OFA
• RBS
• Temp
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts
OFA
Chapter 3 - OFA
• Further divide the previous tablespaces
into pairs of high/low usage tablespaces
• Common-sense Logical Layouts
– Segment types that are used in the same way
should be stored together
– The system should be designed for standard
usage
– Separate areas should exist for exceptions
– Contention among tablespaces should be
minimized
– The data dictionary should be isolated
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 3 - Logical Database Layouts