Logical Database Architecture Exercises

Often times, the job of a DBA involves making assessments and decisions about the database, using data that is accumulated through testing and monitoring the database activity. There are all sorts of things that can affect the performance of a database, and most of them are not restricted to the number of people logged on and using the database.
Obviously, the rate at which the database grows can have a direct effect on how the database reacts to use and traffic. The things that can cause the database to start using excessive amounts of storage space are not always so obvious, but many of them are logical. Oracle's dictionary managed files cause the most administrative overhead, and knowing or being aware of some of the more common things can help eliminate obvious problems more easily. The first part of this exercise addresses some of these problem areas.
The second part of the exercise deals with the storage makeup of the Oracle database. Oracle's architecture allows for storage management and configuration in multiple areas. Understanding how the different parts of the storage architecture work together and relate to each other is an integral part of understanding how to configure a database at creation. In this part of the exercise, you will need to relate your understanding of Oracle's storage structures and how they relate to and work with one another.
Now let's get started.

I N D I V I D U A L     E X E R C I S E S

HANDS-ON #1: Storage space not adequate

Your current database uses dictionary-managed tablespaces. In running various performance tuning scripts, you have discovered that one of these tablespaces seems to have run out of space long before you calculated that it would. In tracking activity in the tablespace, you discover that the process of tables being created, then dropped and re-created, with different storage settings, has been excessive. Give an explanation for why your tablespace might have a problem, and list two actions you can take to lessen the problem.
Place and save your answers in a Word document named week4_exercise.doc.

HANDS-ON #2: Data blocks, extents, segments and tablespaces

Understanding the various parts of data storage and allocation in Oracle can help identify problems more easily.

• In your own words, provide an explanation of the difference between a data block, an extent, a segment, and a tablespace.
• Give an example of how the four are related.
• What is the relationship between a segment and an object created in a tablespace?