chap1_6.html

Oracle资料大集合

</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>
<p>
<b><font size="+1">1.6 How do I匔reate new tablespaces?</font></b>

<p>

<B>Problem</B>

<p>

I have created a new Oracle instance and want to prepare it for user account creation. What tablespaces should I add for a minimally sufficient production instance, and how do I add them?

<p>

<B>Technique</B>

<p>

To determine the tablespaces and data files that currently comprise the instance, query the data dictionary views DBA_TABLESPACES and DBA_DATA_FILES. The structures of these views appear in <A HREF="image/01oht09.gif">Figure 1.9</A>.

<p>

The Oracle server will enable you to open a database for production work and add users, even if the only tablespace in the instance is SYSTEM. Unfortunately, this architecture is likely to introduce fragmentation in the SYSTEM tablespace. This situation is certain to adversely effect database performance. A good rule of thumb is to add three additional tablespaces prior to adding user accounts:

<p>

A tablespace (suggested name: TEMP) to contain temporary segments, such as those produced by a
sort or an index creation

<p>

A tablespace (suggested name: USERS) to act as the default tablespace for all users

<p>

A tablespace (suggested name: RBS) to contain rollback segments

<p>

Use the CREATE TABLESPACE statement to create new tablespaces and data files.\

<p>

<B>Steps</B>

<p>

<B>1.</B>	Run SQL*Plus and connect as the SYSTEM user account. The script called CHP1_1.SQL queries the DBA_TABLESPACES and DBA_DATA_FILES data dictionary views to summarize the current tablespaces in the instance. The results of this script appear in <A HREF="image/01oht10.gif" target="_blank">Figure 1.10</A>.

<p>

<B>2.</B>	Run the script named CHP1_2.SQL to create the USERS, TEMP, and RBS tablespaces. This script and its results appear in <A HREF="image/01oht11.gif" target="_blank">Figure 1.11</A>.

<p>

<B>3.</B>	Run the CHP1_1.SQL script again to verify that the new tablespaces exist, reside in the intended location, and are online. <A HREF="image/01oht12.gif" TARGET="_blank">Figure 1.12</A> shows that the CHP1_2.SQL script successfully created three new tablespaces.

<p>

<B>How It Works</B>

<p>

The script in Step 1 creates a report of the current tablespaces and data files that comprise the database. Step 2 creates three tablespaces commonly found in Oracle instances, and Step 3 verifies the correct operation of the script in Step 2.

<p>

<B>Comments</B>

<p>

Don抰 attempt the reverse operation, dropping tablespaces and data files, at the operating system level without using the DROP TABLESPACE command first. Failure to use this Server Manager command prior to physically dropping a data file will cause Oracle to report that it cannot find a data file when you attempt to open the database. This error indicates a conflict between the physical structure of the database and the structure reflected in the control file.

<p>

After completion of this How-To, connect to Oracle as SYSTEM or as any user with DBA privilege, and run the INSTALL.SQL script to create the WAITE account. This CREATE USER script names the TEMP and USER tablespaces created in Step 2 as the temporary and default tablespaces, respectively, for the WAITE account.

<p>

<code><b>
SQL> start install

<p>
SQL>

<p>
SQL> create user waite identified by waite

<p>

2  temporary tablespace temp

<p>

3  default tablespace users;

<p>

User created.

<p>

SQL>

<p>

SQL> grant dba to waite;

<p>
Grant succeeded.
</B></code>
<p>

<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">