ch07.htm

Teach yourself Oracle8 in 21 day

is read-only, the need to back up this tablespace is eliminated.</P>
<P>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.</P>
<P>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.
<H3><FONT COLOR="#000077"><B>Creating Read-Only Tablespaces</B></FONT></H3>
<P>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.</P>
<P>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.</P>
<P><A NAME="13"></A><A HREF="13.htm"><B>Figure 7.13.</B></A></P>
<P><I>Making a tablespace read-only.</I></P>
<P>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.</P>
<P>The tablespace can also be made read-only through the use of the <TT>ALTER TABLESPACE</TT>
command. The syntax looks something like this:</P>
<PRE><FONT COLOR="#0066FF">ALTER TABLESPACE DOGS READONLY;
</FONT></PRE>
<P>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.</P>
<P>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.
<H2><FONT COLOR="#000077"><B>Temporary Tablespaces</B></FONT></H2>
<P>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).</P>
<P>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.
<H3><FONT COLOR="#000077"><B>Creating Temporary Tablespaces</B></FONT></H3>
<P>A tablespace can be made temporary when it is created with Enterprise Manager,
with Storage Manager, or with the <TT>CREATE TABLESPACE</TT> command. You can change
an existing tablespace from a permanent one to a temporary one using the GUI tools
or the <TT>ALTER TABLESPACE</TT> command. This syntax looks something like this:</P>
<PRE><FONT COLOR="#0066FF">ALTER TABLESPACE DOGS TEMPORARY;
</FONT></PRE>
<P>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.
<H2><FONT COLOR="#000077"><B>Tablespace Tricks and Tips</B></FONT></H2>
<P>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.</P>
<P>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.</P>
<P>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.


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


</BLOCKQUOTE>

<P><A NAME="14"></A><A HREF="14.htm"><B>Figure 7.14.</B></A></P>
<P><I>Unbalanced tablespaces.</I></P>
<P><A NAME="15"></A><A HREF="15.htm"><B>Figure 7.15.</B></A></P>
<P><I>Balanced tablespaces.</I></P>
<P>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.


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


</BLOCKQUOTE>

<P>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.
<H2><FONT COLOR="#000077"><B>Followup</B></FONT></H2>
<P>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, &quot;Properly Sizing Your Database and Planning for Growth,&quot; 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.
<H3><FONT COLOR="#000077"><B>Monitoring the Tablespaces</B></FONT></H3>
<P>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 <TT>SYSTEM</TT> tablespace has used approximately 11.7MB of its available 25MB.
The colored bar shows this to be about 45% used.</P>
<P>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,
<TT>SYSTEM</TT> and <TT>DOGS</TT>, 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.
<H3><FONT COLOR="#000077"><B>Load Balancing</B></FONT></H3>
<P>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.


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


</BLOCKQUOTE>

<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Yesterday you looked at the database-creation operation that creates the database,
the redo log files, the control files, and the <TT>SYSTEM</TT> 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.</P>
<P>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.</P>
<P>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.
<H2><FONT COLOR="#000077"><B>What's Next?</B></FONT></H2>
<P>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.</P>
<P>As you learned on Day 2, &quot;Exploring the Oracle Architecture,&quot; the Oracle
instance consists of the Oracle datafiles (all types), the Oracle memory structures,
and the Oracle processes. By the end of Day 8, &quot;Administering Redo Logs, Control
Files, and Rollback Segments,&quot; you will have seen all the physical files used
in normal operations (don't forget backups and archiving, which you will see later).
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q What utilities can be used to create a tablespace?</B>
	<P><B>A</B> You can use Enterprise Manager, Storage Manager, or the <TT>CREATE TABLESPACE</TT>
	command to create a tablespace.</P>
	<P><B>Q What is a tablespace used for?</B></P>
	<P><B>A</B> The tablespace is used to create schema objects. Tables, indexes, views,
	and clusters all are created within tablespaces.</P>
	<P><B>Q Is using Enterprise Manger better than using SQL commands?</B></P>
	<P><B>A</B> 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.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>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, &quot;Answers.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1. </B>What is an extent?
	<P><B>2. </B>How big can a tablespace be?</P>
	<P><B>3.</B> Is the size of a tablespace permanently fixed?</P>
	<P><B>4.</B> What states can a tablespace be in?</P>
	<P><B>5.</B> How many tablespaces can you have in a database?</P>
	<P><B>6.</B> Name the four types of segments.
</DL>

<H3><FONT COLOR="#000077"><B>Exercises</B></FONT></H3>

<DL>
	<DD><B>1.</B> Create a tablespace using Storage Manager.
	<P><B>2.</B> Create a tablespace using the <TT>CREATE TABLESPACE</TT> command.</P>
	<P><B>3.</B> Take the tablespace offline.</P>
	<P><B>4.</B> Alter the tablespace to be read-only using Storage Manger and the <TT>ALTER
	TABLESPACE</TT> command.
</DL>

<CENTER>
<P>
<HR>
<A HREF="../ch06/ch06.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../rev1/rev1.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>