ch07.htm

Teach yourself Oracle8 in 21 day

[, 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]
</FONT></PRE>
<H4><FONT COLOR="#000077">DATAFILE</FONT></H4>
<P><TT>DATAFILE </TT>file_specification specifies the filename or filenames of the
datafiles that make up the tablespace. File specification is made up of the <TT>`</TT>filename<TT>'
SIZE </TT>number <TT>(K or M) [REUSE]</TT> component. The 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>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 ON</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 UNLIMITED</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
	that autoextend will grow this datafile to is number. This size is either specified
	in K (kilobytes) or M (megabytes).
</UL>

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

<UL>
	<LI><TT>LOGGING</TT>--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 <TT>NOLOGGING</TT> option on these operations.
	<P>
	<LI><TT>NOLOGGING</TT>--This specifies that logging is not done on operations that
	support the <TT>NOLOGGING</TT> option.
	<P>
	<LI><TT>MINIMUM EXTENT</TT>number<TT> K or M</TT>--This specifies the minimum extent
	size for extents created in this tablespace.
	<P>
	<LI><TT>DEFAULT STORAG</TT> storage_clause--Specifies the default storage parameters
	for this tablespace.
	<P>
	<LI><TT>ONLINE</TT>--Specifies that the state of the tablespace is available immediately
	after creation.
	<P>
	<LI><TT>OFFLINE</TT>--Specifies that the state of the tablespace is unavailable immediately
	after creation.
	<P>
	<LI><TT>TEMPORARY</TT>--Specifies that this tablespace will be used only to hold
	temporary objects.
	<P>
	<LI><TT>PERMANENT</TT>--Specifies that this tablespace will be used to hold permanent
	objects. This is the default.
</UL>

<P>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.
<H2><FONT COLOR="#000077"><B>Modifying Tablespaces</B></FONT></H2>
<P>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.
<H3><FONT COLOR="#000077"><B>Bring a Tablespace Offline</B></FONT></H3>
<P>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.
<H4><FONT COLOR="#000077"><B>Normal Tablespace Offline</B></FONT></H4>
<P>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.
<H4><FONT COLOR="#000077"><B>Temporary Tablespace Offline</B></FONT></H4>
<P>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.
<H4><FONT COLOR="#000077"><B>Immediate Tablespace Offline</B></FONT></H4>
<P>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.
<H3><FONT COLOR="#000077"><B>Bring a Tablespace Online</B></FONT></H3>
<P>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.
<H3><FONT COLOR="#000077"><B>Modifying the Tablespace for Backup</B></FONT></H3>
<P>When performing an online backup operation using traditional methods not involving
the new Recovery Manager, use the following procedure:

<DL>
	<DD><B>1.</B> The tablespace is altered for backup. This guarantees that the datafile
	will not be written to during the backup.
	<P><B>2. </B>The datafiles are backed up using OS or third-party utilities.</P>
	<P><B>3. </B>The tablespace is brought back online and any unwritten changes are
	updated on the datafiles.
</DL>

<P>This has been the traditional method for the Oracle backup operation. Backup and
recovery are covered in detail on Days 16, &quot;Understanding Effective Backup Techniques,&quot;
and 17, &quot;Recovering the Database.&quot;
<H3><FONT COLOR="#000077"><B>Coalescing the Tablespace</B></FONT></H3>
<P>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.</P>
<P><A NAME="07"></A><A HREF="07.htm"><B>Figure 7.7.</B></A></P>
<P><I>A fragmented tablespace.</I></P>
<P>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).</P>
<P><A NAME="08"></A><A HREF="08.htm"><B>Figure 7.8.</B></A></P>
<P><I>A coalesced tablespace.</I></P>
<P>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.
<H3><FONT COLOR="#000077"><B>Adding Datafiles</B></FONT></H3>
<P>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 <TT>CREATE TABLESPACE</TT>
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.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>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. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Altering Tablespace Properties</B></FONT></H3>
<P>The tablespace can be altered with Storage Manager and with the <TT>ALTER TABLESPACE</TT>
command that can be run from Server Manager.
<H4><FONT COLOR="#000077"><B>Modifying Tablespaces with Enterprise Manger</B></FONT></H4>
<P>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 <TT>DOGS</TT> tablespace.
Selecting Quick Edit will invoke the Quick Edit Tablespace dialog box, shown in Figure
7.9.</P>
<P><A NAME="09"></A><A HREF="09.htm"><B>Figure 7.9.</B></A></P>
<P><I>The Quick Edit Tablespace dialog box.</I></P>
<P>As you can see, the options available from the Quick Edit Tablespace dialog box
are quite limited:

<UL>
	<LI>Online--Enabling this radio button puts the tablespace online.
	<P>
	<LI>Offline--Enabling this radio button takes the tablespace offline.
	<P>
	<LI>Read Only--Checking this option makes the tablespace read-only.
	<P>
	<LI>Add--Clicking this invokes the Create Datafile dialog box, where you can add
	a datafile to the tablespace.
	<P>
	<LI>Permanent--Enabling this radio button changes a temporary tablespace to a permanent
	one.
	<P>
	<LI>Temporary--Enabling this radio button changes a permanent tablespace to a temporary
	one.
</UL>

<P>The options to modify a tablespace via Enterprise Manager are quite limited compared
to the various tablespace options that are available.
<H4><FONT COLOR="#000077"><B>Modifying Tablespaces with Storage Manger</B></FONT></H4>
<P>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.</P>
<P>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.</P>
<P><A NAME="10"></A><A HREF="10.htm"><B>Figure 7.10.</B></A></P>
<P><I>The Storage Manager Tablespace view.</I></P>
<P>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.</P>
<P>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.</P>
<P>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.</P>
<P>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.</P>
<P><A NAME="11"></A><A HREF="11.htm"><B>Figure 7.11.</B></A></P>
<P><I>The Storage Manager Tablespace options.</I></P>
<P><A NAME="12"></A><A HREF="12.htm"><B>Figure 7.12.</B></A></P>
<P><I>Datafile view.</I></P>
<P>
<H4><FONT COLOR="#000077"><B>Modifying the Tablespace with the </B>ALTER TABLESPACE<B>
Command</B></FONT></H4>
<P>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 <TT>ALTER TABLESPACE</TT> 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.
<H4><FONT COLOR="#000077"><B>The Syntax for </B>ALTER TABLESPACE</FONT></H4>
<P>The <TT>ALTER TABLESPACE</TT> command is run with the following syntax:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">ALTER TABLESPACE tablespace
[LOGGING or NOLOGGING]
[ADD DATAFILE file_specification
[AUTOEXTEND OFF]
or [AUTOEXTEND ON [NEXT number K or M]