 |
|
Tablespace
Administration
Oracle Tips by Burleson
|
Carrying through with the analogy that Oracle
is an operating system, we can say that tablespaces take the place of
disks. But with this “disk,” you, the DBA, can specify its size and
how it will create and store data (via the DEFAULT STORAGE clause) in
its files (tables).
Tablespace Creation
Let’s look at the command for creating a
tablespace.
CREATE [UNDO|TEMPORARY]
TABLESPACE tablespace
DATAFILE|TEMPFILE 'file_spec'
[MINIMUM EXTENT n [K|M]]
[AUTOEXTEND
OFF
ON NEXT n K|M MAXSIZE UNLIMITED|n [K|M]]
[LOGGING|NOLOGGING]
[ONLINE|OFFLINE]
[DEFAULT (storage_clause)] (N/A If TEMPORARY above)
[PERMANENT|TEMPORARY] (N/A If TEMPORARY above)
[EXTENT MANAGEMENT
DICTIONARY (Must be LOCAL for TEMPFILE)
LOCAL
AUTOALLOCATE
UNIFORM [SIZE n [K|M]]]
[SEGMENT SPACE MANAGEMENT MANUAL|AUTO]
Oracle creates a system-managed UNDO
(rollback, for us old-timers) tablespace (this feature is new with
this version). If a database is created in UNDO automatic management
mode, and no UNDO tablespace is specified in the CREATE DATABASE
command, the SYSTEM tablespace will be used. AN UNDO tablespace uses
AUTOALLOCATE LOCAL extent management. The database manages an UNDO
tablespace, and no other objects can be assigned to it. You can only
include the DATAFILE and EXTENT MANAGMEMENT LOCAL clauses for an UNDO
tablespace. All UNDO tablespaces are permanent, read/write, and are in
logging mode; and the values for MINIMUM EXTENT and DEFAULT STORAGE
are system-generated.
tablespace. The name of the tablespace
to be created.
DATAFILE. Specifies the datafile or
files to comprise the tablespace.
TEMPFILE. IF tablespace is TEMPORARY, must use TEMPFILE;
specifies the tempfiles to be used in the TEMPORARY tablespace.
MINIMUM EXTENT integer. Controls
freespace fragmentation in the tablespace by ensuring that every
in-use and/or free extent size in a tablespace is at least as large
as, and is a multiple of, integer.
AUTOEXTEND. Enables or disables the
automatic extension of datafile.
OFF. Disables AUTOEXTEND if it is
turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and
MAXSIZE must be respecified in later ALTER TABLESPACE AUTOEXTEND
commands if OFF is specified; they are not persistent values.
ON. Enables AUTOEXTEND.
NEXT. Disk space to allocate to the
datafile when more extents are required.
MAXSIZE. Maximum disk space allowed for
allocation to the datafile.
UNLIMITED. Set no limit on allocating
disk space to the datafile.
LOGGING, NOLOGGING. Specifies the
default logging attributes of all tables, index, and partitions within
the tablespace. LOGGING is the default. If NOLOGGING is specified, no
undo and redo logs are generated for operations that support the
NOLOGGING option on the tables, index, and partitions within the
tablespace. The tablespace-level logging attribute can be overridden
by logging specifications at the table, index, and partition levels.
DEFAULT. Specifies the default storage
parameters for all objects created in the tablespace.
ONLINE. Makes the tablespace available
immediately after creation to users who have been granted access to
the tablespace.
OFFLINE. Makes the tablespace
unavailable immediately after creation. If you omit both the ONLINE
and OFFLINE options, Oracle creates the tablespace online by default.
The data dictionary view DBA_TABLESPACES indicates whether each
tablespace is online or offline.
PERMANENT. Specifies that the
tablespace will be used to hold permanent objects. This is the
default.
TEMPORARY. Specifies that the
tablespace will only be used to hold temporary objects; for example,
segments used by implicit sorts to handle ORDER BY clauses.
EXTENT MANAGEMENT. The EXTENT
MANAGEMENT clause specifies how the extents in the tablespace will be
managed. The default extent management is DICTIONARY, which is the
standard method used by Oracle7 and Oracle8.0. The LOCAL option has
space set aside for a bitmap. LOCAL-managed extents are managed
by the tablespace itself, while DICTIONARY-managed extents are managed
by the data dictionary processes. The bitmap in a locally managed
tablespace is used to track free and used extents. LOCAL management
reduces recursive space management caused by rollback, data
dictionary, and extent management because LOCAL management requires no
dictionary actions and less rollback activity due to data dictionary
extensions. LOCAL also allows for automatic mapping of adjacent free
extents into a single large extent, eliminating the need for
coalescing of freespace. The UNIFORM or AUTOALLOCATE determines how
extents are mapped. AUTOALLOCATE, which forces system management of
extents, uses the storage clause values for extent management, while
UNIFORM uses a default of 1-MB extents. The DEFAULT STORAGE clause is
invalid for locally managed tablespaces and cannot be specified; in
addition, the MINIMUM EXTENT and TEMPORARY clauses are also prohibited
if local extent management is utilized.
SEGMENT SPACE MANAGEMENT MANUAL|AUTO.
New in 9i, this clause allows permanent, locally managed tablespaces
to specify whether Oracle should track the used and freespace in
segments using freelists or by use of bitmaps. MANUAL manages the
freespace using freelists; AUTO manages the freespace using a bitmap.
If AUTO is specified, any specification for FREELISTS or FREELIST
GROUPS is ignored for objects stored in the tablespace. You will see
this AUTO setting referred to as automatic segment-space management.
It can be determined for a tablespace by use of the
SEGMENT_SPACE_MANAGEMENT column in the DBA_ or USER_TABLESPACES view.
Under AUTO, each LOCAL UNIFORM extent must be at least five Oracle
blocks in size. If you specify LOCAL ALLOCATE, and the default
database blocksize is 16K or greater, extents sizes will automatically
be set to 1 megabyte at a minimum. The SYSTEM tablespace cannot be an
AUTO type tablespace. LOBs cannot be stored in an AUTO type
tablespace.
Say we want to create a data tablespace for
the accounts receivable (AR) application in our accounting package.
The database for our AR application is called ORACTP, short for Oracle
Accounting Production database. Let’s look at the actual command to
create a tablespace with an initial 500-MB datafile with autoextension
to a maximum size of 1 gigabyte (1,024 megabytes). The tables in our
AR application will hold a fairly large amount of data, so just in
case our developers forget to size their tables (they wouldn’t do
that, would they?) let’s size the default storage to INITIAL 10M NEXT
1M PCTINCREASE 10.
CREATE
TABLESPACE ar DATAFILE '\ORACLE1\ORACTP\data\oractp_ar01.dbf' SIZE
500M
AUTOEXTEND ON NEXT 200M MAXSIZE 1024M
DEFAULT STORAGE (INITIAL 10M NEXT 1M PCTINCREASE 10)
PERMANENT
ONLINE
LOGGING;
I’ve included the PERMANENT, ONLINE, and
LOGGING clauses for illustration only; they are the default if nothing
is specified. Why did I specify a PCTINCREASE value? If PCTINCREASE is
set to zero, the SMON process will not coalesce freespace. If
PCTINCREASE is not specified, it will default to 50 percent;
therefore, specifying it at a low value is suggested.
On the other hand, what if we wanted the
extents to be locally managed due to the level of dynamic allocation
that could happen? The CREATE TABLESPACE clause will change, in that
we no longer specify the DEFAULT STORAGE clause, and instead use the
EXTENT MANAGEMENT clause with the LOCAL clause. If we want to ensure
that uniform extents are generated, we can specify the UNIFORM clause
as well. Let’s shoot for 2-MB LOCAL UNIFORM extent management.
CREATE
TABLESPACE ar DATAFILE '\ORACLE1\ORACTP\data\oractp_ar01.dbf' SIZE
500M
AUTOEXTEND ON NEXT 200M MAXSIZE 1024M
LOCAL UNIFORM SIZE 2M
PERMANENT
ONLINE
LOGGING;
Now there are two types of temporary
tablespaces: a CREATE TEMPORARY TABLESPACE tablespace, which uses
TEMPFILES instead of DATAFILES, and a CREATE TABLESPACE tablespace
TEMPORARY, which uses DATAFILES. A CREATE TEMPORARY TABLESPACE
tablespace has to use LOCAL extent management. A CREATE TABLESPACE
tablespace TEMPORARY cannot use LOCAL extent management. TEMPFILEs
cannot be backed up using a hot backup; in fact, they don't have to
be, and will generate an error if you attempt to place them in backup
mode. Let's look at a couple of examples.
First let's create a CREATE TEMPORARY
TABLESPACE tablespace:
CREATE
TEMPORARY TABLESPACE local_temp
TEMPFILE '/oracle1/oradata/ortest1/data/local_temp01.tmp' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 1024M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
CREATE TEMPORARY TABLESPACE tablespaces (CT3s)
will not release sort segments until the database is shut down. In
tests on 8.1.7.2, this resulted in significant sort area overhead for
a busy system. For a 6-gigabyte total size database with 40 concurrent
users, 9 doing relatively heavy sorts, more than 16 gigabytes of
temporary space was required. This appears to indicate the reuse
mechanism isn't quite ready for prime time in CT3s.
Now let's create the equivalent CREATE
TABLESPACE tablespace TEMPORARY tablepace:
CREATE
TABLESPACE TEMP
DATAFILE '/ORACLE01/ORADATA/ORTEST1/DATA/TEMP01.DBF' size 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 1024M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0);
CREATE TABLESPACE tablespace TEMPORARY (CT2)
releases segments as soon as sorts end. For the same user and sort
loads, an equivalent CT2 required 7 gigabytes of space compared to the
16 gigabytes required by the CT3. This reduced space requirement was
due to the rapid release of the sort segments and reacquisition by
processes requiring sorts.
Sizing of Tablespaces
Tablespaces should be sized to contain all of
the objects they are intended to hold. This means that, in order to
size a tablespace properly, you will first need to size all of the
objects that will be placed in the tablespace. Chapters 4 through 8,
on table, cluster, and index management detail the techniques for
sizing database objects.
The autoextend capability provided since
Oracle7 can make some tablespace management easier, but it is no
replacement for properly sizing tablespaces. One problem with
autoextend is that you have no way of knowing when you will run out of
space due to a runaway process (for example, a Cartesian product of 2
million row tables filling the temporary tablespace).
Once you have the sizes of all the objects
that will reside in a tablespace, you add these size figures together;
I suggest adding 25 to 50 percent additional room to allow for growth.
The default storage parameters for a tablespace should never be used,
except where the tablespace’s purpose is to hold rollback segments.
If a tablespace is built using RAW partitions
on either UNIX or NT, I suggest selecting a standard size for the raw
partitions, such as 501 MB or 1 GB (plus 1 MB), and create all raw
segments this size.
Note: I suggest that the raw file sizes
be 101, 501, and so on, to prevent writing data into a “zero” block,
which can cause problems. Thus, a 100-megabyte datafile is mapped into
a 101-megabyte RAW area. This practice may result in small amounts of
wasted space, but it helps prevent some RAW problems from happening.
In order to minimize fragmentation issues
(Swiss cheese-type fragmentation), it is suggested that several
tablespaces (usually three), sized to hold various uniform extent
sizes (small, medium, and large, for example), be created; objects
that fit into each size model are placed into the appropriate
tablespace. This would indicate that you would have three data and
three index tablespaces to allow for the various tables and indexes in
your application. The new UNIFORM or AUTOALLOCATE tablespace options
lend themselves readily to this use of sizing models.
In Oracle, the concept of Oracle-managed
files (OMF) is introduced. Essentially, this model forces you to place
all of your tablespace datafiles in the same directory. If you use OMF,
then you must have a RAID5, a RAID0/1, or a RAID1/0 configuration.
Basically, you set the DB_CREATE_FILE_DEST and
DB_CREATE_ONLINE_LOG_DEST_X initialization parameters; then Oracle
will automatically create the datafiles, controlfiles, and log files
in that location. You do not have to specify the tablespace datafile
specification during CREATE or ALTER commands using OMF. If a filename
is not specified, any file created is placed under the OMF control.
OMF should be used only for low-end or test
databases, not for high-performance databases. OMF also should not be
used with RAW disks.
Alteration of Tablespaces
Periodically, a tablespace may need to have
its default storage changed; or require the addition of datafiles to
increase its storage volume, a name change, or being taken offline for
maintenance; or it may need to have AUTOEXTEND turned off or on; be
made temporary, or converted to permanent; or require being placed in
backup status for a hot backup. The command used for all of these
functions is the ALTER command. Let’s look at its format.
ALTER TABLESPACE tablespace
[LOGGING|NOLOGGING]
[ADD DATAFILE|TEMPFILE file_spec [autoextendclause]]
[RENAME DATAFILE 'from_file_spec' TO 'to_file_spec']
[COALESCE]
[DEFAULT STORAGE storage_clause]
[MINIMUM EXTENT n K|M]
[ONLINE|OFFLINE
NORMAL|
IMMEDIATE|
TEMPORARY]
[BEGIN|END BACKUP]
[READ ONLY|WRITE]
[PERMANENT|TEMPORARY]
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
