Summary

In this chapter, you learned about the purpose of tablespaces and datafiles. You found out how to create and manage tablespaces as well as how Oracle stores some schema objects as segments in extents and data blocks.

You learned how to create and modify tables, indexes, constraints, views, and sequences. We covered deferred constraint checking and how to configure foreign key constraints to supporteither deferrable or not deferrable implementations.

Exam Essentials

Know the relationship of datafiles to tablespaces. Tablespaces are built on one or more datafiles—bigfile tablespaces on a single datafile and smallfile tablespaces on one or more datafiles.

Understand the statements needed to create, modify, and drop tablespaces. Use a CREATE TABLESPACE, ALTER TABLESPACE, or DROP TABLESPACE statement to create, modify, and drop a tablespace.

Know how to take tablespaces offline and what consequences the offline immediate option poses. Use an ALTER TABLESPACE statement to take a tablespace offline or bring it online. If you use the offline immediate option, you must perform media recovery when you bring it back online.

Know how to use the EM Database Control to view tablespace information. The EM Database Control can be used to view tablespace information as well as perform various administrative tasks. A working knowledge of this tool is required.

Know the difference between segment space management and extent management. Extent management deals with segment-level space allocations, and segment space management deals with data block–level space allocations.

Know what initialization parameter controls Oracle Managed Files (OMF) placement. The DB_CREATE_FILE_DEST parameter tells the database where to place Oracle Managed Files.

Know what constitutes a valid name for a database object. Database object names can be a maximum of 30 bytes, must begin with a letter, and can be composed of letters, numbers, and the special characters underscore, dollar sign, or pound sign. If the database name is enclosed in double quotes, it can be mixed case and contain other punctuation marks.

Know what a namespace is and how it determines which objects can have the same name.

Database object names must be unique within a namespace. One namespace is shared by tables, views, sequences, private synonyms, procedures, functions, packages, materialized views, and user-defined types. There are separate namespaces for each of the following: indexes, constraints, clusters, database triggers, private database links, and dimensions.

Know the different types of constraints and which have dependencies with others. There are CHECK, NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. A PRIMARY KEY constraint implicitly includes NOT NULL and UNIQUE constraints. A FOREIGN KEY constraint must refer to a PRIMARY KEY or UNIQUE constraint.

Know the types of indexes and when they are appropriate. There are Btree and bitmap indexes. Btree indexes are medium- to high-cardinality columns in applications in which data can change frequently. Bitmap indexes are best for low- to medium-cardinality(表间关系) columns in applications that control data changes, usually in batches.