📄 ch07.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself Oracle 8 In 21 Days -- Ch 7 -- Administering Tablespaces</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself Oracle 8 In 21 Days</FONT></H1>
</CENTER>
<CENTER>
<P><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>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 7 -<BR>
Administering Tablespaces</FONT></H1>
</CENTER>
<P>Yesterday you learned the first part of how to create a database. Using the <TT>CREATE
DATABASE</TT> statement, you will create by default the <TT>SYSTEM</TT> tablespace.
This tablespace will be created on the datafiles that you specify in the <TT>CREATE
DATABASE</TT> statement. This <TT>SYSTEM</TT> tablespace is used to store important
internal structures such as the data dictionary, the system stored procedures, and
the system rollback segments.</P>
<P>Unless you specify otherwise, the <TT>SYSTEM</TT> 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.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>The <TT>SYSTEM</TT> tablespace
is used to hold important system schema objects. It is a good idea not to create
user objects on the <TT>SYSTEM</TT> tablespace. If the <TT>SYSTEM</TT> tablespace
runs out of space, this could disrupt the entire operation of the RDBMS.
<HR>
</BLOCKQUOTE>
<P>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.
<H2><FONT COLOR="#000077"><B>Review of Tablespaces</B></FONT></H2>
<P>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.</P>
<P>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.</P>
<P>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.</P>
<P>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.</P>
<P>Every Oracle database must have at least one tablespace. When you create a database,
the <TT>SYSTEM</TT> tablespace is created by default. This is where the data dictionary
is kept. By default, a user is assigned to the <TT>SYSTEM</TT> 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.</P>
<P>Tablespaces can hold any one of four types of segments:
<UL>
<LI>Data segments--This type of segment, which is the basic type, can be used to
hold tables and clusters.
<P>
<LI>Index segments--This type of segment is used to hold indexes.
<P>
<LI>Rollback segments--This special type of segment is used to store undo information.
Rollback segments are covered in more detail tomorrow.
<P>
<LI>Temporary segments--This type of segment is used for storing temporary data.
</UL>
<P>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.
<H2><FONT COLOR="#000077"><B>Creating Tablespaces</B></FONT></H2>
<P>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.</P>
<P>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.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>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).
<HR>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077"><B>Creating Tablespaces with Enterprise Manager</B></FONT></H3>
<P>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.</P>
<P>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.</P>
<P>Drill down to the <TT>DOGS.world</TT> database by clicking the Database option
and then clicking the <TT>DOGS.world</TT> option.</P>
<P>If you click the Tablespace option, you will see that there is already (by default)
one tablespace present in the system: the <TT>SYSTEM</TT> tablespace.</P>
<P>By drilling down into the <TT>SYSTEM</TT> tablespace option, you can see that
it is also possible to administer datafiles and rollback segments from here.</P>
<P>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.</P>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 7.1.</B></A></P>
<P><I>Tablespace options.</I></P>
<P>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.</P>
<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 7.2.</B></A></P>
<P><I>The Create Tablespace dialog box.</I></P>
<P>Available options include
<UL>
<LI>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.
<P>
<LI>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.
<P>
<LI>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).
<P>
<LI>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.
<P>
<LI>Temporary--This specifies that the tablespace is for temporary objects.
</UL>
<P>If you click the Add button, a new datafile will be created. The Create Datafile
dialog box is shown in Figure 7.3.</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 7.3.</B></A></P>
<P><I>The Create Datafile dialog box.</I></P>
<P>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.</P>
<P>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.</P>
<P><A NAME="04"></A><A HREF="04.htm"><B>Figure 7.4.</B></A></P>
<P><I>Option to view the SQL statement.</I></P>
<P>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.
<H3><FONT COLOR="#000077"><B>Creating Tablespaces with Storage Manager</B></FONT></H3>
<P>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).</P>
<P>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).</P>
<P>Again you see the <TT>SYSTEM</TT> option, but with Storage Manager, you see a
detail screen on the right-hand side that shows the following:
<UL>
<LI>Name--The tablespace name.
<P>
<LI>Status--Whether the tablespace is offline or online.
<P>
<LI>Size--The allocated size of the tablespace.
<P>
<LI>Used--How much of the tablespace is currently being used. The display also shows
the approximate percentage of space that is used.
</UL>
<P>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).</P>
<P><A NAME="05"></A><A HREF="05.htm"><B>Figure 7.5.</B></A></P>
<P><I>The Storage Manager main screen.</I></P>
<P><A NAME="06"></A><A HREF="06.htm"><B>Figure 7.6.</B></A></P>
<P><I>The Tablespace view.</I></P>
<P>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.
<H3><FONT COLOR="#000077"><B>Creating Tablespaces with the </B>CREATE TABLESPACE<B>
Command</B></FONT></H3>
<P>You can create a tablespace with the Server Manager using the <TT>CREATE TABLESPACE</TT>
command. This command can be run either interactively within Server Manager or from
a SQL script. As with the <TT>CREATE DATABASE</TT> 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:</P>
<PRE><FONT COLOR="#0066FF">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]
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -