ch07.htm

Teach yourself Oracle8 in 21 day

     [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]
</FONT></PRE>
<P>The parameters used to alter the tablespace are defined as follows:

<UL>
	<LI><TT>LOGGING</TT>--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 <TT>NOLOGGING</TT> option on these operations.
	<P>
	<LI><TT>NOLOGGING</TT>--This parameter specifies that logging not be done on operations
	that support the <TT>NOLOGGING</TT> option.
	<P>
	<LI><TT>ADD DATAFILE</TT>file_specification--This parameter specifies that a datafile
	or datafiles specified by file_specification will be added to the tablespace.
</UL>

<P>file_specification consists of the <TT>`filename' SIZE </TT>number<TT> (K or M)
[REUSE]</TT> component. file_specification is used to define the name, and the initial
size in kilobytes (K) or megabytes (M) of the datafile. The <TT>REUSE</TT> parameter
allows you to use the name of an existing file.</P>
<P>Additional qualifiers to the <TT>ADD DATAFILE</TT> parameter are

<UL>
	<LI><TT>AUTOEXTEND OFF</TT>--This parameter specifies that the autoextend feature
	is disabled on this datafile.
	<P>
	<LI><TT>AUTOEXTEND O</TT>--This parameter specifies that that autoextend feature
	is enabled. Additional qualifiers to the <TT>AUTOEXTEND ON</TT> parameter are
	<P>
	<LI><TT>NEXT</TT>number<TT> K or M</TT>--When a datafile autoextends itself, it extends
	by number<TT> K</TT> (kilobytes) or number<TT> M</TT> (megabytes).
	<P>
	<LI><TT>MAXSIZE UNLIMITE</TT>--This specifies that the maximum size of the datafile
	is limited only by disk space and OS-specific maximum sizes.
	<P>
	<LI><TT>MAXSIZE</TT>number<TT> K or M</TT>--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).
</UL>

<P>Other parameters available with the <TT>ALTER TABLESPACE</TT> command are

<UL>
	<LI><TT>RENAME DATAFILE `filename' [, `filename]</TT>...<TT> TO `filename'</TT>--This
	command is used to rename one or more datafiles in the <TT>[, `filename']</TT>...
	tablespace.
	<P>
	<LI><TT>COALESC</TT>--This command is used to coalesce the tablespace as described
	previously.
	<P>
	<LI><TT>DEFAULT STORAGE storage_claus</TT>--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.
	<P>
	<LI><TT>MINIMUM EXTENT</TT>number<TT> [K or M]</TT>--This specifies the minimum size
	of an extent that is created on the tablespace. The minimum extent size will be number<TT>
	K</TT> (kilobytes) <TT>or </TT>number<TT> M</TT> (megabytes).
	<P>
	<LI><TT>ONLINE</TT>--This is used to bring the tablespace online.
	<P>
	<LI><TT>OFFLINE NORMAL</TT>--This is used to bring the tablespace offline normally
	as described previously.
	<P>
	<LI><TT>OFFLINE TEMPORARY</TT>--This is used to bring the tablespace offline temporarily,
	as described previously.
	<P>
	<LI><TT>OFFLINE IMMEDIATE</TT>--This is used to force the tablespace offline with
	the <TT>IMMEDIATE</TT> option.
	<P>
	<LI><TT>BEGIN BACKUP</TT>--This takes the tablespace offline and defers all writes
	to the datafiles while the backup is taking place.
	<P>
	<LI><TT>END BACKUP</TT>--This brings the tablespace back online and writes out all
	changes that have occurred since the <TT>BEGIN BACKUP</TT>.
	<P>
	<LI><TT>READ ONLY</TT>--This modifies the tablespace to be read-only. The read-only
	tablespace is described later today.
	<P>
	<LI><TT>READ WRITE</TT>--This modifies a read-only tablespace to be read-write.
	<P>
	<LI><TT>PERMANENT</TT>--This converts the tablespace from temporary to permanent
	status.
	<P>
	<LI><TT>TEMPORARY</TT>--This converts the tablespace from permanent to temporary
	status.
</UL>

<P>As you have seen, the <TT>ALTER TABLESPACE</TT> 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.
<H2><FONT COLOR="#000077"><B>The </B>STORAGE<B> Clause</B></FONT></H2>
<P>The <TT>STORAGE</TT> 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.
<H4><FONT COLOR="#000077"><B>The Syntax for the </B>STORAGE<B> Clause</B></FONT></H4>
<P>The <TT>STORAGE</TT> clause has the following syntax:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">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]]
)
</FONT></PRE>
<P>The parameters used in the <TT>STORAGE</TT> clause are defined as follows:

<UL>
	<LI><TT>INITIAL </TT>number<TT> K or M</TT>--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<TT> K</TT> (kilobytes) or number <TT>M</TT>
	(megabytes). The default is 5 data blocks. The size is rounded up to the nearest
	multiple of 5 blocks.
	<P>
	<LI><TT>NEXT</TT>number<TT> K or M</TT>--The <TT>NEXT</TT> parameter specifies the
	size of subsequent extents to be number<TT> K</TT> (kilobytes) or number<TT> M</TT>
	(megabytes). This number is also rounded up to the nearest multiple of 5 data blocks,
	and defaults to 5 data blocks.
	<P>
	<LI><TT>MINEXTENTS</TT>number--This specifies the minimum number of extents created
	when the schema object is created. Each of these extents is the size of the <TT>INITIAL</TT>
	extent, and Oracle uses <TT>NEXT</TT> and <TT>PCTINCREASE</TT> to calculate the size
	of subsequent extents. The default value is <TT>1</TT> except for rollback segments,
	where the default is <TT>2</TT>.
	<P>
	<LI><TT>MAXEXTENTS</TT>number--This specifies the maximum number of extents that
	can be created for a schema object. This includes the first extent.
	<P>
	<LI><TT>MAXEXTENTS UNLIMITE</TT>--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.
	<P>
	<LI><TT>PCTINCREASE</TT>number--This specifies the size of extents after the second
	extent (that is, from the third extent on). The initial extents are sized via the
	<TT>INITIAL</TT> parameter. The next extent is sized via the <TT>NEXT</TT> parameter.
	If the <TT>PCTINCREASE</TT> parameter is nonzero, all subsequent extents are sized
	as <TT>NEXT</TT> multiplied by <TT>PCTINCREASE </TT>number. This product is a percentage,
	so <TT>40</TT> means 40% larger, and so on. A value of <TT>0</TT> specifies that
	all subsequent extents are the same size as specified in the <TT>NEXT</TT> parameter.
	The default value is <TT>50</TT>, except for rollback segments, which can only have
	a <TT>PCTINCREASE</TT> of <TT>0</TT>.
	<P>
	<LI><TT>FREELISTS</TT>number--The <TT>FREELISTS</TT> 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 <TT>PCTFREE</TT>. These are essentially lists of
	blocks that are available for inserts. By having more than one freelist, you can
	reduce contention on inserts.
	<P>
	<LI><TT>FREELIST GROUP</TT> number--<TT>FREELISTS GROUPS</TT> specifies the number
	of groups of freelists in a parallel-server environment. This allows each instance
	to have its own set of freelists. <TT>FREELIST GROUPS</TT> is a parallel-server-only
	parameter.
	<P>
	<LI><TT>OPTIMAL </TT>number<TT> K or M]</TT>--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.
	<P>
	<LI><TT>OPTIMAL [NULL</TT>--This parameter specifies that the rollback segments never
	shrink, as they can with the <TT>OPTIMAL</TT> parameter set to a value. This is described
	in detail tomorrow.
</UL>

<P>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.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>For tablespaces, you specify the
	<TT>DEFAULT STORAGE</TT> 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. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Using the </B>STORAGE<B> Clause</B></FONT></H3>
<P>By using the <TT>STORAGE</TT> 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 <TT>STORAGE</TT>
clause on the schema objects like so:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLESPACE ts_1
DATAFILE `D:\database\ts_1_a.dbf' SIZE 20M,
E:\database\ts_1_b.dbf SIZE 20M
DEFAULT STORAGE
(
INITIAL 2M NEXT 2M PCTINCREASE 0 MINEXTENTS 2
);
</FONT></PRE>
<P>This creates the tablespace <TT>ts_1</TT> 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:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLESPACE ts_1
DATAFILE `D:\database\ts_1_a.dbf' SIZE 20M,
E:\database\ts_1_b.dbf SIZE 20M AUTOEXTEND ON NEXT 1M MAXSIZE 30M
DEFAULT STORAGE
(
INITIAL 2M NEXT 2M PCTINCREASE 0 MINEXTENTS 2
);
</FONT></PRE>
<P>Remember that the <TT>DEFAULT STORAGE</TT> 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.
<H2><FONT COLOR="#000077"><B>Read-Only Tablespaces</B></FONT></H2>
<P>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