Teach Yourself Oracle 8 In 21 Days
  
- Day 7 -
Administering Tablespaces
Yesterday you learned the first part of how to create a database. Using the CREATE
DATABASE statement, you will create by default the SYSTEM tablespace.
This tablespace will be created on the datafiles that you specify in the CREATE
DATABASE statement. This SYSTEM tablespace is used to store important
internal structures such as the data dictionary, the system stored procedures, and
the system rollback segments.
Unless you specify otherwise, the SYSTEM tablespace will be used as the
default for all database users. This is not desirable, so it is important to create
additional tablespaces as soon as possible. The creation of the additional tablespaces,
where you place your own tables, indexes, and so on, is really the second part of
creating your own database.
WARNING: The SYSTEM tablespace
is used to hold important system schema objects. It is a good idea not to create
user objects on the SYSTEM tablespace. If the SYSTEM tablespace
runs out of space, this could disrupt the entire operation of the RDBMS.
Today you will learn how to create tablespaces, what they are used for, and how
to properly configure the tablespaces for optimal performance using Oracle8 partitioning.
Review of Tablespaces
Day 2, "Exploring the Oracle Architecture," presented you with an overview
of the Oracle architecture and briefly outlined Oracle tablespaces. This section
reviews the Oracle tablespace in more detail.
The Oracle tablespace is the lowest logical layer of the Oracle data structure.
The tablespace consists of one or more datafiles; these can be files on the operating
system filesystem or raw devices. Until recently the size of a datafile was fixed,
but now datafiles can be extended automatically or manually.
The tablespace is important in that it provides the finest granularity for laying
out data across datafiles. After the tablespace is created, you don't have much control
over how the actual tables are distributed within the tablespace. By carefully configuring
the tablespace, you have some coarse configuration options (you will see these later
today), but for the most part, the internal layout of schema objects on tablespaces
is done automatically.
As you will see later, if you take advantage of the range partitioning feature
of Oracle8, the partition configuration is very important. In fact, how and where
your tablespaces are created is crucial to the optimization of range partitioning.
Every Oracle database must have at least one tablespace. When you create a database,
the SYSTEM tablespace is created by default. This is where the data dictionary
is kept. By default, a user is assigned to the SYSTEM tablespace, but this
is not recommended. As you will see on Day 10, "Administering User Accounts,"
users can and should be assigned tablespaces based on the function they are to perform.
Quotas can also be placed on these users, specifying how much space they can use.
Tablespaces can hold any one of four types of segments:
- Data segments--This type of segment, which is the basic type, can be used to
hold tables and clusters.
- Index segments--This type of segment is used to hold indexes.
- Rollback segments--This special type of segment is used to store undo information.
Rollback segments are covered in more detail tomorrow.
- Temporary segments--This type of segment is used for storing temporary data.
Tablespaces are made read-write by default, but can be altered to become read-only
as shown later today. Read-only tablespaces can be very useful in a number of situations.
Creating Tablespaces
Creating a tablespace consists of specifying one or more datafiles and storage
parameters. The datafiles can be either filesystem files or raw devices. The storage
parameters specify how the tablespace is used. The storage parameters are important
and are discussed later today.
As with most of the operations you have seen so far, the tablespace can be created
both graphically with the Oracle Enterprise Manager or with a SQL script using the
Server Manager. Both methods are described here.
NOTE: Under Windows NT, each datafile
can be 4MB blocks in size. For a 2KB block size, this is 8GB; for an 8KB block size,
this is 32GB in size. A tablespace can consist of a maximum of 1,022 datafiles. Therefore,
a tablespace under Windows NT can be a maximum size of 32TB (terabytes).
Creating Tablespaces with Enterprise Manager
You can create tablespaces using either Enterprise Manager or Storage Manager.
Either method performs essentially the same task; in fact, the screens used to perform
the creation are identical.
For this example, I have shut down all Enterprise Manager panes except the Navigator
pane. I did this by unchecking all panes in the View drop-down menu that I did not
want displayed. By unchecking the Map, Job, and Event panes, I removed them from
the screen. From here you will work through an example of how to create a tablespace
using Enterprise Manager.
Drill down to the DOGS.world database by clicking the Database option
and then clicking the DOGS.world option.
If you click the Tablespace option, you will see that there is already (by default)
one tablespace present in the system: the SYSTEM tablespace.
By drilling down into the SYSTEM tablespace option, you can see that
it is also possible to administer datafiles and rollback segments from here.
With Enterprise Manager, it is simply necessary to right-click the Tablespace
option in the tree structure, as shown in Figure 7.1. This will present you with
a number of tasks that you can perform on tablespaces. From here you can create a
tablespace.
Figure 7.1.
Tablespace options.
As you can see, the first option available is the Create option. Clicking this
option will invoke the Create Tablespace dialog box, shown in Figure 7.2. From this
dialog box, you can create additional tablespaces.
Figure 7.2.
The Create Tablespace dialog box.
Available options include
- Online--This option specifies that the tablespace is brought online after creation.
This is the normal operation, so you can have immediate access to this tablespace
after creation.
- Offline--This specifies that the tablespace is left offline after creation. If
you want to leave the tablespace unavailable until you perform other operations on
it, you should select this option.
- Read Only--This specifies that the tablespace is read-only. There is not much
point in making a tablespace read-only at creation time. Typically you will create
tables and populate them, then make the tablespace read-only (if desired).
- Permanent--This specifies that the tablespace is for permanent objects. This
is the default parameter. This option is used for all schema objects except for temporary
tablespaces.
- Temporary--This specifies that the tablespace is for temporary objects.
If you click the Add button, a new datafile will be created. The Create Datafile
dialog box is shown in Figure 7.3.
Figure 7.3.
The Create Datafile dialog box.
In the Create Datafile dialog box, provide a datafile name and a size. Click M
if you want the size to appear in megabytes; K (kilobytes) is the default. After
you have filled in the Create Datafile dialog box, you are returned to the Create
Tablespace dialog box. To finish, click the OK button.
Enterprise Manager provides you with the ability to view the SQL used to create
the tablespace. If you click the Show SQL button, you will see the SQL statement
used to create the tablespace (see Figure 7.4). As you can see, after you click the
Show SQL button, that button changes to a Hide SQL button. If you click the Hide
SQL button, the SQL statement will no longer be displayed.
Figure 7.4.
Option to view the SQL statement.
Viewing the SQL statements created by the graphical tools can be a very nice learning
tool. By trying various options, you can see the different SQL statements that are
created and how they change. Following these procedures, you can create new tablespaces
directly from the Oracle Enterprise Manager.
Creating Tablespaces with Storage Manager
Tablespaces can also be created through the use of the Storage Manager application
that is part of Enterprise Manager. Storage Manager is used to manage storage objects
such as tablespaces and datafiles on a particular instance. Unlike Enterprise Manager,
where multiple databases are displayed, Storage Manager is concerned only with the
instance you log in to it with. After you log in to Storage Manager, you will see
the main dialog box (see Figure 7.5).
As with Enterprise Manager, you can drill down to the tablespace level in Storage
Manager by clicking the Tablespace option (shown in Figure 7.6).
Again you see the SYSTEM option, but with Storage Manager, you see a
detail screen on the right-hand side that shows the following:
- Name--The tablespace name.
- Status--Whether the tablespace is offline or online.
- Size--The allocated size of the tablespace.
- Used--How much of the tablespace is currently being used. The display also shows
the approximate percentage of space that is used.
From this point, you can look at the tablespaces and datafiles that are currently
in use, or you can create new tablespaces. If you right-click the Tablespace option,
you will see a menu similar to the one shown in Figure 7.1. From this menu you can
click the Create button to invoke the Create Tablespace dialog box (refer to Figure
7.2).
Figure 7.5.
The Storage Manager main screen.
Figure 7.6.
The Tablespace view.
If you use Storage Manager, you will see that the screens and operation are identical
to those used in Enterprise Manager. The basic differences are in the main screen
(refer to Figure 7.5), which gives you more information than Enterprise Manager.
The same options are used and the same procedures are followed to create a tablespace
using Storage Manager.
Creating Tablespaces with the CREATE TABLESPACE
Command
You can create a tablespace with the Server Manager using the CREATE TABLESPACE
command. This command can be run either interactively within Server Manager or from
a SQL script. As with the CREATE DATABASE command, I prefer to use a SQL
script because it can be reused or used as a template for further tablespace creations.
This SQL script is also a good record of the operations necessary to re-create the
database in case of a catastrophic failure. The following command is used to create
a tablespace:
CREATE TABLESPACE
DATAFILE file_specification
[AUTOEXTEND OFF]
or [AUTOEXTEND ON [NEXT number K or M]
[MAXSIZE UNLIMITED or MAXSIZE number K or M]
[NOLOGGING or LOGGING]
[, file_specification
[AUTOEXTEND OFF]
or [AUTOEXTEND ON [NEXT number K or M]
[MAXSIZE UNLIMITED or MAXSIZE number K or M]
[NOLOGGING or LOGGING]]
[MINIMUM EXTENT number K or M]
[DEFAULT STORAGE storage_clause]
[ONLINE or OFFLINE]
[PERMANENT or TEMPORARY]
DATAFILE
DATAFILE file_specification specifies the filename or filenames of the
datafiles that make up the tablespace. File specification is made up of the `filename'
SIZE number (K or M) [REUSE] component. The file specification is used
to define the name and the initial size in kilobytes (K) or megabytes (M) of the
datafile. The REUSE parameter allows you to use the name of an existing
file.
Additional qualifiers to the DATAFILE parameter are
- AUTOEXTEND OFF--This parameter specifies that the autoextend feature
is disabled on this datafile.
- AUTOEXTEND ON--This parameter specifies that that autoextend feature
is enabled. Additional qualifiers to the AUTOEXTEND ON parameter are
- NEXTnumber K or M--When a datafile autoextends itself, it extends
by number K (kilobytes) or number M (megabytes).
- MAXSIZE UNLIMITED--This specifies that the maximum size of the datafile
is limited only by disk space and OS-specific maximum sizes.
- MAXSIZEnumber K or M--This specifies that the maximum size
that autoextend will grow this datafile to is number. This size is either specified
in K (kilobytes) or M (megabytes).
Other parameters available with the CREATE TABLESPACE command are
- LOGGING--This specifies that redo log information is kept on table,
index, and partition operations. This is the default. Logging can be disabled by
using the NOLOGGING option on these operations.
- NOLOGGING--This specifies that logging is not done on operations that
support the NOLOGGING option.
- MINIMUM EXTENTnumber K or M--This specifies the minimum extent
size for extents created in this tablespace.
- DEFAULT STORAG storage_clause--Specifies the default storage parameters
for this tablespace.
- ONLINE--Specifies that the state of the tablespace is available immediately
after creation.
- OFFLINE--Specifies that the state of the tablespace is unavailable immediately
after creation.
- TEMPORARY--Specifies that this tablespace will be used only to hold
temporary objects.
- PERMANENT--Specifies that this tablespace will be used to hold permanent
objects. This is the default.
As you can see, there are many options available in the creation of the tablespace.
Among these are the storage parameters that are shown later in this lesson. These
storage parameters define the characteristics of the tablespace and how it grows.
Modifying Tablespaces
It is often necessary to modify a tablespace that has already been created. This
can be accomplished in several ways, either using Enterprise Manager or Server Manager.
Before I go into the syntax of operations that are available to modify the tablespace
parameters and state, I would like to go through the various options and why you
would perform these operations. At the end of this section you will see how to operationally
change the tablespace properties and state.
Bring a Tablespace Offline
Bringing the tablespace offline can occur in several different ways. These methods
normally take the tablespace offline either with the Temporary option or with the
Immediate option. Each of these options has different purposes and consequences.
Normal Tablespace Offline
Bringing a tablespace offline normally involves Oracle checkpointing all the datafiles
used by the tablespace and then bringing the tablespace offline. Bringing a tablespace
offline normally requires all the datafiles to be available and functioning properly.
Bringing a tablespace back online from a normal offline operation does not require
recovery.
Temporary Tablespace Offline
Bringing a tablespace offline with the Temporary option will allow the tablespace
to be brought offline even if a datafile is unavailable. So even if you are having
a problem with a datafile, you can bring the tablespace offline with the Temporary
option. All the available datafiles will be checkpointed. Bringing the tablespace
back online might require some recovery.
Immediate Tablespace Offline
Using the Immediate option to take a tablespace offline does just what you'd think:
The tablespace is immediately taken offline without the datafiles being checkpointed,
so recovery is required when the tablespace is brought back online. This should be
done only as a last resort under emergency conditions.
Bring a Tablespace Online
Any tablespace that has been taken offline for any reason can be brought back
online either through Enterprise Manager or Server Manager. Bringing a tablespace
online alters the state of that tablespace so that it can be accessed by users. Depending
on how the tablespace was taken offline, there might be recovery involved in bringing
it back online.
Modifying the Tablespace for Backup
When performing an online backup operation using traditional methods not involving
the new Recovery Manager, use the following procedure:
- 1. The tablespace is altered for backup. This guarantees that the datafile
will not be written to during the backup.
2. The datafiles are backed up using OS or third-party utilities.
3. The tablespace is brought back online and any unwritten changes are
updated on the datafiles.
This has been the traditional method for the Oracle backup operation. Backup and
recovery are covered in detail on Days 16, "Understanding Effective Backup Techniques,"
and 17, "Recovering the Database."
Coalescing the Tablespace
Because the tablespace allocates space to schema objects in extents of various
sizes, it is possible that over time this space will become fragmented. As extents
are allocated, Oracle looks for free space that is closest in size to the space needed
for the new extent. As the tablespace ages and extents are added and freed, you might
find many small free extents located next to each other, as illustrated in Figure
7.7.
Figure 7.7.
A fragmented tablespace.
By coalescing the tablespace, you coalesce the adjacent free extents into larger
free extents, thus making the free space more flexible to new extent allocations.
Compare the fragmented tablespace shown in Figure 7.7 with the same tablespace after
it is coalesced (see Figure 7.8).
Figure 7.8.
A coalesced tablespace.
The SMON process automatically coalesces tablespaces unless the process has been
disabled. It is rarely necessary to coalesce a tablespace by hand, but it is necessary
to know how and why this operation is performed.
Adding Datafiles
It is often necessary to add more datafiles to a tablespace if you need more space
or want to spread out I/Os among more disk drives. It is also faster to add datafiles
to a tablespace than to create them at database-creation time. The CREATE TABLESPACE
command works serially. That is, it creates one datafile at a time. By adding datafiles,
this operation can be parallelized, so multiple datafiles can be added to a tablespace
at once.
TIP: It can be faster to add datafiles
to a tablespace than to specify them at tablespace creation because adding datafiles
can be done in parallel. Datafile creation at tablespace-creation time is done in
serial, one at a time. For creation of a large tablespace, this can save significant
time.
Altering Tablespace Properties
The tablespace can be altered with Storage Manager and with the ALTER TABLESPACE
command that can be run from Server Manager.
Modifying Tablespaces with Enterprise Manger
There is some limited ability to modify the tablespace from within Enterprise
Manager itself. Drill down to the Tablespace option and right-click the tablespace
you want to modify. In this case, I have right-clicked the DOGS tablespace.
Selecting Quick Edit will invoke the Quick Edit Tablespace dialog box, shown in Figure
7.9.
Figure 7.9.
The Quick Edit Tablespace dialog box.
As you can see, the options available from the Quick Edit Tablespace dialog box
are quite limited:
- Online--Enabling this radio button puts the tablespace online.
- Offline--Enabling this radio button takes the tablespace offline.
- Read Only--Checking this option makes the tablespace read-only.
- Add--Clicking this invokes the Create Datafile dialog box, where you can add
a datafile to the tablespace.
- Permanent--Enabling this radio button changes a temporary tablespace to a permanent
one.
- Temporary--Enabling this radio button changes a permanent tablespace to a temporary
one.
The options to modify a tablespace via Enterprise Manager are quite limited compared
to the various tablespace options that are available.
Modifying Tablespaces with Storage Manger
To modify the database through a graphical utility, you can use Enterprise Manager
or Enterprise Manager's application, Storage Manager. Storage Manager is invoked
either through Enterprise Manager or from the Oracle Administrators toolbar.
On the left side of the Enterprise Manager screen, you see the Navigator pane,
where a tree-like structure displays the SID as the top level and branches out to
tablespaces, datafiles, and rollback segments. If you drill down to the tablespace
level, you will see the detail screen of the tablespaces, as shown in Figure 7.10.
Figure 7.10.
The Storage Manager Tablespace view.
An extremely useful feature of this screen is the capability to show how much
of your space is used in the tablespaces. On the far-right side, you can see both
the size of the tablespaces and the amount of space that each tablespace has used.
You can drill down to the Tablespace screen and right-click to invoke a list of
options, as shown in Figure 7.11. As you can see, the options available to modify
the tablespaces are as limited as within Enterprise Manager. You do, however, get
a few more features within Storage Manager.
An extremely useful feature of this screen is the capability to show how much
of your space is used within each object. If you click the Datafile option, you can
view each of the datafiles and the space available in each, as shown in Figure 7.12.
As with the tablespace and datafile screens, you can view the space used in the
rollback segments by clicking the Rollback Segments option. You will see more on
how to use Storage Manager with rollback segments tomorrow.
Figure 7.11.
The Storage Manager Tablespace options.
Figure 7.12.
Datafile view.
Modifying the Tablespace with the ALTER TABLESPACE
Command
As you can see, Enterprise Manager and Storage Manager are quite limited when
it comes to modifying tablespaces. Only a few options are available. All the tablespace
options can be modified with the ALTER TABLESPACE command. This command
can be used to modify the tablespace parameters that are set when the tablespace
is created, to alter the state of the tablespace, or to add datafiles.
The Syntax for ALTER TABLESPACE
The ALTER TABLESPACE command is run with the following syntax:
SYNTAX:
ALTER TABLESPACE tablespace
[LOGGING or NOLOGGING]
[ADD DATAFILE file_specification
[AUTOEXTEND OFF]
or [AUTOEXTEND ON [NEXT number K or M]
[MAXSIZE UNLIMITED or MAXSIZE number K or M]]
[, file_specification
[AUTOEXTEND OFF]
or [AUTOEXTEND ON [NEXT number K or M]
[MAXSIZE UNLIMITED or MAXSIZE number K or M]]
[RENAME DATAFILE `filename' [, `filename]...
TO `filename' [, `filename']...]
[COALESCE]
[DEFAULT STORAGE storage_clause]
[MINIMUM EXTENT number [K or M]]
[ONLINE]
[OFFLINE NORMAL or OFFLINE TEMPORARY or OFFLINE IMMEDIATE]
[BEGIN BACKUP or END BACKUP]
[READ ONLY or READ WRITE]
[PERMANENT or TEMPORARY]
The parameters used to alter the tablespace are defined as follows:
- LOGGING--This parameter specifies that redo log information is kept
on table, index, and partition operations. This is the default. You can disable logging
by using the NOLOGGING option on these operations.
- NOLOGGING--This parameter specifies that logging not be done on operations
that support the NOLOGGING option.
- ADD DATAFILEfile_specification--This parameter specifies that a datafile
or datafiles specified by file_specification will be added to the tablespace.
file_specification consists of the `filename' SIZE number (K or M)
[REUSE] component. file_specification is used to define the name, and the initial
size in kilobytes (K) or megabytes (M) of the datafile. The REUSE parameter
allows you to use the name of an existing file.
Additional qualifiers to the ADD DATAFILE parameter are
- AUTOEXTEND OFF--This parameter specifies that the autoextend feature
is disabled on this datafile.
- AUTOEXTEND O--This parameter specifies that that autoextend feature
is enabled. Additional qualifiers to the AUTOEXTEND ON parameter are
- NEXTnumber K or M--When a datafile autoextends itself, it extends
by number K (kilobytes) or number M (megabytes).
- MAXSIZE UNLIMITE--This specifies that the maximum size of the datafile
is limited only by disk space and OS-specific maximum sizes.
- MAXSIZEnumber K or M--This specifies that the maximum size
autoextend will grow this datafile to is number. This size is either specified in
kilobytes (K) or megabytes (M).
Other parameters available with the ALTER TABLESPACE command are
- RENAME DATAFILE `filename' [, `filename]... TO `filename'--This
command is used to rename one or more datafiles in the [, `filename']...
tablespace.
- COALESC--This command is used to coalesce the tablespace as described
previously.
- DEFAULT STORAGE storage_claus--This command is used to change the default
storage parameters for the tablespace. These default storage parameters are used
when schema objects are created unless a storage parameter is specified.
- MINIMUM EXTENTnumber [K or M]--This specifies the minimum size
of an extent that is created on the tablespace. The minimum extent size will be number
K (kilobytes) or number M (megabytes).
- ONLINE--This is used to bring the tablespace online.
- OFFLINE NORMAL--This is used to bring the tablespace offline normally
as described previously.
- OFFLINE TEMPORARY--This is used to bring the tablespace offline temporarily,
as described previously.
- OFFLINE IMMEDIATE--This is used to force the tablespace offline with
the IMMEDIATE option.
- BEGIN BACKUP--This takes the tablespace offline and defers all writes
to the datafiles while the backup is taking place.
- END BACKUP--This brings the tablespace back online and writes out all
changes that have occurred since the BEGIN BACKUP.
- READ ONLY--This modifies the tablespace to be read-only. The read-only
tablespace is described later today.
- READ WRITE--This modifies a read-only tablespace to be read-write.
- PERMANENT--This converts the tablespace from temporary to permanent
status.
- TEMPORARY--This converts the tablespace from permanent to temporary
status.
As you have seen, the ALTER TABLESPACE command allows many changes to
be made to the tablespace. Changes to tablespaces should always be logged when they
occur. If the database ever needs to be re-created, this information is critical.
The STORAGE Clause
The STORAGE clause is very important because it is used to specify the
initial size and characteristics of the tablespace as well as the future growth of
that tablespace.
The Syntax for the STORAGE Clause
The STORAGE clause has the following syntax:
SYNTAX:
STORAGE
(
[INITIAL number K or M]
[NEXT number K or M]
[MINEXTENTS number]
[MAXEXTENTS number or MAXEXTENTS UNLIMITED]
[PCTINCREASE number]
[FREELISTS number]
[FREELIST GROUPS number]
[OPTIMAL [number K or M] or [NULL]]
)
The parameters used in the STORAGE clause are defined as follows:
- INITIAL number K or M--This parameter specifies the initial
size of the extents. These extents are created when the schema object is created.
This parameter specifies the size to be number K (kilobytes) or number M
(megabytes). The default is 5 data blocks. The size is rounded up to the nearest
multiple of 5 blocks.
- NEXTnumber K or M--The NEXT parameter specifies the
size of subsequent extents to be number K (kilobytes) or number M
(megabytes). This number is also rounded up to the nearest multiple of 5 data blocks,
and defaults to 5 data blocks.
- MINEXTENTSnumber--This specifies the minimum number of extents created
when the schema object is created. Each of these extents is the size of the INITIAL
extent, and Oracle uses NEXT and PCTINCREASE to calculate the size
of subsequent extents. The default value is 1 except for rollback segments,
where the default is 2.
- MAXEXTENTSnumber--This specifies the maximum number of extents that
can be created for a schema object. This includes the first extent.
- MAXEXTENTS UNLIMITE--This specifies that the maximum number of extents
that can be created for a schema object is unlimited. Oracle does not recommend that
you use this option with any schema objects except for rollback segments.
- PCTINCREASEnumber--This specifies the size of extents after the second
extent (that is, from the third extent on). The initial extents are sized via the
INITIAL parameter. The next extent is sized via the NEXT parameter.
If the PCTINCREASE parameter is nonzero, all subsequent extents are sized
as NEXT multiplied by PCTINCREASE number. This product is a percentage,
so 40 means 40% larger, and so on. A value of 0 specifies that
all subsequent extents are the same size as specified in the NEXT parameter.
The default value is 50, except for rollback segments, which can only have
a PCTINCREASE of 0.
- FREELISTSnumber--The FREELISTS parameter specifies the number
of sets of freelists for each of the freelist groups of tables, indexes, partitions
and clusters. A freelist is a linked list of available data blocks in the extent
that have free space greater than PCTFREE. These are essentially lists of
blocks that are available for inserts. By having more than one freelist, you can
reduce contention on inserts.
- FREELIST GROUP number--FREELISTS GROUPS specifies the number
of groups of freelists in a parallel-server environment. This allows each instance
to have its own set of freelists. FREELIST GROUPS is a parallel-server-only
parameter.
- OPTIMAL number K or M]--This parameter applies only to rollback
segments. It specifies the ideal size of the rollback segment. Because the rollback
segment grows as described tomorrow, this parameter describes what size Oracle should
attempt to keep them.
- OPTIMAL [NULL--This parameter specifies that the rollback segments never
shrink, as they can with the OPTIMAL parameter set to a value. This is described
in detail tomorrow.
These storage parameters can be used not only in the creation of tablespaces,
but also in the creation of schema objects, as you will see later in the book. The
size and characteristics of the tablespaces can be very important to the performance
of the system.
NOTE: For tablespaces, you specify the
DEFAULT STORAGE options. These are used as the default values for the schema
objects that you will be creating. Your schema object creation options override the
default storage parameters.
Using the STORAGE Clause
By using the STORAGE clause, you can be very efficient with how the schema
objects are stored. If you know you will be loading a large amount of data that will
be stored in a certain table, it is much more efficient to have a few large extents
rather than many small extents. This is typically be done using the STORAGE
clause on the schema objects like so:
CREATE TABLESPACE ts_1
DATAFILE `D:databasets_1_a.dbf' SIZE 20M,
E:databasets_1_b.dbf SIZE 20M
DEFAULT STORAGE
(
INITIAL 2M NEXT 2M PCTINCREASE 0 MINEXTENTS 2
);
This creates the tablespace ts_1 with two datafiles and two initial extents.
To create the same tablespace but allow the second datafile to autoextend, you can
affix the additional parameters as follows:
CREATE TABLESPACE ts_1
DATAFILE `D:databasets_1_a.dbf' SIZE 20M,
E:databasets_1_b.dbf SIZE 20M AUTOEXTEND ON NEXT 1M MAXSIZE 30M
DEFAULT STORAGE
(
INITIAL 2M NEXT 2M PCTINCREASE 0 MINEXTENTS 2
);
Remember that the DEFAULT STORAGE clause is used for the creation of
extents. Extents are used to hold schema objects. When the schema objects are created
and grow, the default storage parameters are used. These parameters are simply defaults
for the schema objects that are created on these tablespaces. Schema objects created
with their own storage parameters override the tablespace defaults.
Read-Only Tablespaces
As described previously, it is possible to alter a tablespace to make it read-only.
Read-only tablespaces are similar to read-write tablespaces except that no changes
can be made to the schema objects residing on those tablespaces. When a tablespace
is read-only, the need to back up this tablespace is eliminated.
Because the data is guaranteed not to change, it is unnecessary to perform regular
backups on the read-only data. If a backup has been performed at some time, that
backup should be good for the life of the tablespace.
Because the read-only tablespace is not modified by Oracle, it is possible to
place the tablespace on a read-only medium such as a CD-ROM or a WORM drive. If this
data is archival in nature but must be available, a CD-ROM is an excellent choice.
Creating Read-Only Tablespaces
All tablespaces are created as read-write tablespaces and must be populated with
data before they are useful. After the data and indexes have been created to your
specifications, the tablespace can be made read-only. This can happen in several
ways.
Enterprise Manager or Storage Manager can be used to modify a tablespace to be
read-only, as described in the previous section. Simply go to the Tablespace Modification
screen, shown in Figure 7.13, and click the Read Only box.
Figure 7.13.
Making a tablespace read-only.
I enjoy Enterprise Manager's capability to show the SQL used to perform the command.
I encourage you to use this feature to become familiar with the DDL statements that
are used in these operations.
The tablespace can also be made read-only through the use of the ALTER TABLESPACE
command. The syntax looks something like this:
ALTER TABLESPACE DOGS READONLY;
There are several uses for the read-only tablespace, but they are fairly specific.
Whether you can take advantage of it depends on your applications.
If you have a large amount of static data that can be put on slower media, the
read-only tablespace might be advantageous for you. A read-only tablespace might
also be advantageous if you want to guarantee that archival data is not modified.
Temporary Tablespaces
Temporary tablespaces are used to perform sort operations that cannot fit into
memory. If you allocate a tablespace specifically for sorting, it is unnecessary
to allocate and deallocate space in tablespaces that are used for other purposes
(doing so causes fragmentation).
When a sort operation cannot fit in memory, it must create and use a temporary
segment. This temporary segment allocates extents and continues to do so until it
has enough room to perform the sort. With large DSS queries, these temporary segments
can become quite large. By having tablespaces specifically for this type of operation,
not only will the sorts be more efficient, there will be less temporary usage on
your data tablespaces.
Creating Temporary Tablespaces
A tablespace can be made temporary when it is created with Enterprise Manager,
with Storage Manager, or with the CREATE TABLESPACE command. You can change
an existing tablespace from a permanent one to a temporary one using the GUI tools
or the ALTER TABLESPACE command. This syntax looks something like this:
ALTER TABLESPACE DOGS TEMPORARY;
It is rare that you will change a permanent tablespace to a temporary one, or
vice versa. A temporary tablespace is typically created as such, and will remain
so for the duration of its existence.
Tablespace Tricks and Tips
The tablespace is the resource from which the schema objects obtain their space.
Think of a tablespace as a filesystem on a set of disk drives. The space is there
and allocated, but is not used until somebody creates a file or saves some data.
This is also true of the Oracle tablespace.
As schema objects are created, extents are allocated from the tablespace. These
extents are allocated based on the storage parameters of the schema creation or the
tablespace's default storage parameters.
As objects are created, the space is taken from the front of the tablespace; as
more and more objects are created, the space is allocated from where the last object
was created. This can cause some problems.
NOTE: Here is an example of where the
tablespaces can work counter to your intent: The system has two disk drives, each
4GB in size. You create a tablespace that uses two datafiles, one on each drive of
4GB each.
In this case, only the first disk drive will be used until you use up 4GB of space
in the tablespace, as shown in Figure 7.14. Only then it will start using space on
the second datafile. Unfortunately, this does little to balance the I/O load.
Instead, create eight, 1GB datafiles, four on each disk drive in an alternating pattern
(see Figure 7.15). This will more evenly spread the load.
Figure 7.14.
Unbalanced tablespaces.
Figure 7.15.
Balanced tablespaces.
Other ways to balance the I/O load are to use hardware features or OS features
such as disk striping. If you stripe your disk drives with a disk array or with software
striping, the load will be fairly well balanced among all the disk drives in the
stripe.
TIP: Disk striping can be an easy way
to balance the I/O load between disk drives. A hardware or software disk array will
evenly distribute the data among all the disk drives in the stripe.
Configuring and managing the tablespaces can be a time-consuming task requiring
much up-front planning. This planning is well worth it. More time spent planning
now means less time spent fixing problems later.
Followup
Even after the tablespaces have been created, your job is not over. You must monitor
the space used and the load balancing of your tablespaces. As you recall from Day
4, "Properly Sizing Your Database and Planning for Growth," capacity planning
and sizing are very important duties. By anticipating problems and solving them before
they become critical, you can avoid costly mishaps. By monitoring the system and
planning for the future, you can avoid costly downtime.
Monitoring the Tablespaces
As described previously, you can use Storage Manager to view both the tablespaces
and the datafiles for space utilization. The information provided by Storage Manager
is quite useful. From the Tablespace view, you can see both the space allocated to
the tablespace and the amount of space used in the tablespace (refer to Figure 7.6).
The SYSTEM tablespace has used approximately 11.7MB of its available 25MB.
The colored bar shows this to be about 45% used.
This feature is also available from the Datafiles option (refer to Figure 7.12).
This shows approximately the same view as in the Tablespace view since both tablespaces,
SYSTEM and DOGS, have one associated datafile. In situations where
more datafiles exist, you might be able to determine whether balancing of space is
an issue. You must use the NT Performance Monitor to determine whether I/O balancing
is a problem.
Load Balancing
As detailed yesterday, you should use diskperf to determine whether you are overloading
your I/O system. Periodically look at the physical disk statistics during peak and
nonpeak usage periods. If you see one or two disk drives that have more activity
than others, you may have an I/O problem. Don't rely on things staying the same.
Performance characteristics of your system are constantly changing and must be monitored.
TIP: Try scheduling several days during
the month to monitor system performance. Put it on your calendar at the beginning
of the month. Monitor several different days of the week and times of day to get
some different samples.
Summary
Yesterday you looked at the database-creation operation that creates the database,
the redo log files, the control files, and the SYSTEM tablespace. This tablespace
is populated with stored procedures and structures necessary to run the Oracle RDBMS.
But this does not leave room for your data.
Today you looked at the second step of creating your own database: creating tablespaces.
After the database has been created, you must create additional tablespaces for your
own data. These tablespaces are where your specific schema objects will reside, and
where your data will be loaded. This is why I refer to tablespace creation as the
second step in creating your database.
These tablespaces are very important because your data and the performance of
your system rely on how well you design these tablespaces. The tablespaces are the
key to data partitioning and load balancing. It is very important to plan the layout
of your tablespaces and datafiles with performance and functionality in mind. In
subsequent lessons you will see how important this is.
What's Next?
Tomorrow you will see the importance of the redo log files, the control files,
and the rollback segments. After finishing tomorrow's lesson, you will be familiar
with all operations that involve physical datafiles. The four types of files that
exist in the Oracle database are datafiles, control files, redo log files, and parameter
files. All other Oracle structures reside within the datafiles.
As you learned on Day 2, "Exploring the Oracle Architecture," the Oracle
instance consists of the Oracle datafiles (all types), the Oracle memory structures,
and the Oracle processes. By the end of Day 8, "Administering Redo Logs, Control
Files, and Rollback Segments," you will have seen all the physical files used
in normal operations (don't forget backups and archiving, which you will see later).
Q&A
- Q What utilities can be used to create a tablespace?
A You can use Enterprise Manager, Storage Manager, or the CREATE TABLESPACE
command to create a tablespace.
Q What is a tablespace used for?
A The tablespace is used to create schema objects. Tables, indexes, views,
and clusters all are created within tablespaces.
Q Is using Enterprise Manger better than using SQL commands?
A Neither is better or worse. You should learn how to use both of them.
I really like Enterprise Manager, but there are still lots of things I use Server
Manager for.
Workshop
The workshop provides quiz questions to help you solidify your understanding of
the material covered and exercises to provide you with experience in using what you've
learned. For answers to quiz questions, see Appendix A, "Answers."
Quiz
- 1. What is an extent?
2. How big can a tablespace be?
3. Is the size of a tablespace permanently fixed?
4. What states can a tablespace be in?
5. How many tablespaces can you have in a database?
6. Name the four types of segments.
Exercises
- 1. Create a tablespace using Storage Manager.
2. Create a tablespace using the CREATE TABLESPACE command.
3. Take the tablespace offline.
4. Alter the tablespace to be read-only using Storage Manger and the ALTER
TABLESPACE command.
© Copyright, Macmillan Computer Publishing. All
rights reserved.
| 
