TIDY UP BY DROPPING THE TABLESPACE, FIRST WITH THIS COMMAND

10. Tidy up by dropping the tablespace, first with this command:

drop tablespace manualsegs;

Note the error caused by the tablespace not being empty, and fix it:

drop tablespace manualsegs including contents and datafiles;

Two-Minute Drill

Overview of Tablespaces and Datafiles

• One tablespace can be physically represented by many datafiles.• One tablespace can contain many segments.• One segment comprises one or more extents.• One extent is many consecutive blocks, in one datafile.• One Oracle block should be one or more operating system blocks.• The Oracle block is the granularity of database I/O.

Create and Manage Tablespaces

• A SMALLFILE tablespace can have many datafiles, but a BIGFILE tablespace can have only one.• Tablespaces default to local extent management, automatic segment space management, but not to a uniform extent size.• OMF datafiles are automatically named, initially 100MB, and can autoextend without limit.• A tablespace that contains segments cannot be dropped—unless an INCLUDING CONTENTS clause is specified.• Tablespaces can be online or offline, read-write or read only.• Tablespaces can store one of three types of objects: permanent objects, temporary objects, or undo segments.

Space Management in Tablespaces

• Local extent management tracks extent allocation with bitmaps in each datafile.• The UNIFORM SIZE clause when creating a tablespace forces all extents to be the same size.• The AUTOALLOCATE clause lets Oracle determine the next extent size, which is based on how many extents are being allocated to a segment.• Automatic segment space management tracks the free space in each block of an extent using bitmaps.• It is possible to convert a tablespace from dictionary extent management to local extent management, but not from freelist segment management to automatic management.

Self Test

Tablespaces

D