Edit D:\rfid\database\database\doc\server.112\e10897\schema.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html lang="en" xml:lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Language" content="en" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <meta name="robots" content="all" scheme="http://www.robotstxt.org/" /> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = document) - Version 5.1" /> <meta name="Date" content="2009-09-09T22:35:14Z" /> <meta name="doctitle" content="Oracle® Database 2 Day DBA 11g Release 2 (11.2)" /> <meta name="partno" content="E10897-02" /> <meta name="docid" content="ADMQS" /> <link rel="Start" href="../../index.htm" title="Home" type="text/html" /> <link rel="Copyright" href="../../dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="Stylesheet" href="../../dcommon/css/blafdoc.css" title="Default" type="text/css" /> <script type="text/javascript" src="../../dcommon/js/doccd.js"> </script> <link rel="Contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="Index" href="index.htm" title="Index" type="text/html" /> <link rel="Prev" href="users_secure.htm" title="Previous" type="text/html" /> <link rel="Next" href="backrest.htm" title="Next" type="text/html" /> <link rel="alternate" href="../e10897.pdf" title="PDF version" type="application/pdf" /> <title>Managing Schema Objects</title> </head> <body> <div class="header"> <div class="zz-skip-header"><a name="top" id="top" href="#BEGIN">Skip Headers</a></div> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"><b>Oracle® Database 2 Day DBA<br /> 11<i>g</i> Release 2 (11.2)</b><br /> Part Number E10897-02</td> <td valign="bottom" align="right"> <table class="icons oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="245"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img width="24" height="24" src="../../dcommon/gifs/doclib.gif" alt="Go to Documentation Home" /><br /> <span class="icon">Home</span></a></td> <td align="center" valign="top"><a href="../../nav/portal_booklist.htm"><img width="24" height="24" src="../../dcommon/gifs/booklist.gif" alt="Go to Book List" /><br /> <span class="icon">Book List</span></a></td> <td align="center" valign="top"><a href="toc.htm"><img width="24" height="24" src="../../dcommon/gifs/toc.gif" alt="Go to Table of Contents" /><br /> <span class="icon">Contents</span></a></td> <td align="center" valign="top"><a href="index.htm"><img width="24" height="24" src="../../dcommon/gifs/index.gif" alt="Go to Index" /><br /> <span class="icon">Index</span></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><img width="24" height="24" src="../../dcommon/gifs/feedbck2.gif" alt="Go to Feedback page" /><br /> <span class="icon">Contact Us</span></a></td> </tr> </table> </td> </tr> </table> <hr /> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="98"> <tr> <td align="center" valign="top"><a href="users_secure.htm"><img width="24" height="24" src="../../dcommon/gifs/leftnav.gif" alt="Go to previous page" /><br /> <span class="icon">Previous</span></a></td> <td align="center" valign="top"><a href="backrest.htm"><img width="24" height="24" src="../../dcommon/gifs/rightnav.gif" alt="Go to next page" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </td> <td align="right" valign="top" style="font-size: 90%"><a href="../e10897.pdf">View PDF</a></td> </tr> </table> <a name="BEGIN" id="BEGIN"></a></div> <div class="IND"><!-- End Header --><a id="CFHJAHAA" name="CFHJAHAA"></a><a id="ADMQS008" name="ADMQS008"></a> <h1 class="chapter"><span class="secnum">8</span> Managing Schema Objects</h1> <p>This chapter discusses the creation and management of schema objects. It contains the following sections:</p> <ul> <li> <p><a href="#CFHHBEGH">About Schema Objects</a></p> </li> <li> <p><a href="#CBBCIEEJ">About Schema Object Management Privileges</a></p> </li> <li> <p><a href="#CFHEIBFG">Managing Tables</a></p> </li> <li> <p><a href="#BGBDBDFB">Managing Indexes</a></p> </li> <li> <p><a href="#BGBICDFE">Managing Views</a></p> </li> <li> <p><a href="#CHDHDBID">Managing Program Code Stored in the Database</a></p> </li> <li> <p><a href="#BGBBGEBF">Working with Other Schema Objects</a></p> </li> <li> <p><a href="#CHDJGIIH">Schemas: Oracle By Example Series</a></p> </li> </ul> <a id="CFHHBEGH" name="CFHHBEGH"></a><a id="ADMQS081" name="ADMQS081"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">About Schema Objects</h2> <p>A <a id="sthref453" name="sthref453"></a><span class="bold">schema</span> is a collection of database objects. A schema is owned by a database user and shares the same name as the user. <span class="bold">Schema objects</span> are logical structures created by users. Some objects, such as tables or indexes, hold data. Other objects, such as views or synonyms, consist of a definition only.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> There is no relationship between a tablespace and a schema. Objects in the same schema can use storage in different tablespaces, and a tablespace can contain data from different schemas.</div> <a id="ADMQS0812" name="ADMQS0812"></a> <p class="subhead2">Naming Schema Objects</p> <p>Every object in the database belongs to one schema and has a unique name within that schema. Multiple database objects can share the same name, if they are in different schemas. You can use the schema name to unambiguously refer to objects. For example, <code>hr.employees</code> refers to the table named <code>employees</code> in the <code>hr</code> schema. (The <code>employees</code> table is owned by <code>hr</code>.) The terms <span class="italic">database object</span> and <span class="italic">schema object</span> are used interchangeably.</p> <p>When you create a database object, you must ensure that you create it in the intended schema. One method is to log in to the database as the user who owns the schema and then create the object. Generally, you place all the objects that belong to a single application in the same schema.</p> <p><a id="sthref454" name="sthref454"></a>A schema object name must abide by certain rules. In addition to being unique within a schema, a schema object name cannot be longer than 30 bytes and must begin with a letter. If you attempt to create an object with a name that violates any of these rules, then the database raises an error.</p> <a id="CBBBGIJB" name="CBBBGIJB"></a><a id="ADMQS12093" name="ADMQS12093"></a> <p class="subhead2">The Show SQL Button</p> <p>You can create and manipulate schema objects with SQL or with Oracle Enterprise Manager Database Control (Database Control).</p> <p>When creating schema objects using Database Control, you can click the <span class="bold">Show SQL</span> button to display the SQL statement that is the equivalent of the schema object properties that you specified with the graphical user interface. Database Control submits this SQL statement to create the schema object. This option shows the statement even if it is incomplete, so you must enter all specifications for the schema object to see the complete SQL statement that Database Control submits.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink CNCPT" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT"><span class="italic">Oracle Database Concepts</span></a> for more detailed information about schema objects, object names, and data types</p> </li> </ul> </div> </div> <!-- class="sect1" --> <a id="CBBCIEEJ" name="CBBCIEEJ"></a><a id="ADMQS12092" name="ADMQS12092"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">About Schema Object Management Privileges</h2> <p><a id="sthref455" name="sthref455"></a><a id="sthref456" name="sthref456"></a>As a database administrator (DBA), you can create, modify, and delete schema objects in your own schema and in any other schema. For purposes of this discussion, a database administrator is defined as any user who is granted the <a id="sthref457" name="sthref457"></a><a id="sthref458" name="sthref458"></a><code>DBA</code> role. This includes the <code>SYS</code> and <code>SYSTEM</code> users by default. Oracle recommends granting the <code>DBA</code> role only to those user who require administrative type access.</p> <p>You can enable other users to manage schema objects without necessarily granting them DBA privileges. For example, a common scenario is to enable an application developer to create, modify, and delete schema objects in his or her own schema. To do so, you grant the <a id="sthref459" name="sthref459"></a><a id="sthref460" name="sthref460"></a><code>RESOURCE</code> role to the application developer.</p> <p>As described in <a href="em_manage.htm#BABHCDGA">"Granting Access to Database Control for Nonadministrative Users"</a>, you must also grant the developer access to Database Control before he or she can log in to Database Control to manage schema objects. If you do not grant access to Database Control, then the developer must manage schema objects with SQL*Plus or SQL Developer.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="users_secure.htm#CHDBDBGI">"Example: Granting Privileges and Roles to a User Account"</a></p> </li> <li> <p><a href="em_manage.htm#BABGJCIB">"Administering the Database with SQL-Based Management Tools"</a></p> </li> </ul> </div> </div> <!-- class="sect1" --> <a id="CFHEIBFG" name="CFHEIBFG"></a><a id="ADMQS082" name="ADMQS082"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Managing Tables</h2> <p>The following topics discuss database tables and how to create and modify them:</p> <ul> <li> <p><a href="#CBBGHBEG">About Tables</a></p> </li> <li> <p><a href="#BGBBAHAG">Viewing Tables</a></p> </li> <li> <p><a href="#CBBJDBGB">Viewing Table Data</a></p> </li> <li> <p><a href="#BEABGHHE">Example: Creating a Table</a></p> </li> <li> <p><a href="#CBBJGEIJ">Modifying Table Attributes</a></p> </li> <li> <p><a href="#CFHGBGCB">Example: Loading Data into a Table</a></p> </li> <li> <p><a href="#CBBIAAFF">Deleting a Table</a></p> </li> </ul> <a id="CBBGHBEG" name="CBBGHBEG"></a><a id="ADMQS0821" name="ADMQS0821"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">About Tables</h3> <p><a id="sthref461" name="sthref461"></a><a id="sthref462" name="sthref462"></a>The table is the basic unit of data storage in an Oracle database. It holds all user-accessible data. Each table is made up of columns and rows. In the <code>employees</code> table, for example, there are columns called <code>last_name</code> and <code>employee_id</code>. Each row in the table represents a different employee, and contains a value for <code>last_name</code> and <code>employee_id</code>.</p> <p>When you create a table, you specify the table type, and define its columns and constraints. <span class="bold">Constraints</span> are rules that help preserve data integrity.</p> <p>This section contains the following topics:</p> <ul> <li> <p><a href="#CBBHFAJH">About Table Types</a></p> </li> <li> <p><a href="#CBBICGAD">About Table Column Attributes</a></p> </li> <li> <p><a href="#BGBDDJDG">About Table-Level Constraints</a></p> </li> <li> <p><a href="#CBBDGEJE">Other Table Creation Considerations</a></p> </li> </ul> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <a id="CBBHFAJH" name="CBBHFAJH"></a><a id="ADMQS08211" name="ADMQS08211"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">About Table Types</h4> <p>The most common type of table in an Oracle database is a <span class="italic">relational</span> table, which is structured with simple columns similar to the <code>employees</code> table. Two other table types are supported: <span class="italic">object</span> tables and <code><span class="codeinlineitalic">XMLType</span></code> tables. Any of the three table types can be defined as <span class="italic">permanent</span> or <span class="italic">temporary</span>. Temporary tables hold session-private data that exists only for the duration of a transaction or session. They are useful in applications where a results set must be held temporarily in memory, perhaps because the results set is constructed by running multiple operations.</p> <p>You can build relational tables in either <span class="italic">heap</span> or <span class="italic">index-organized</span> structures. In heap structures, the rows are not stored in any particular order. In index-organized tables, the row order is determined by the values in one or more selected columns. For some applications, index-organized tables provide enhanced performance and more efficient use of disk space.</p> <p>This section describes permanent, heap-organized tables. For information about other table types and when to use them, see <a class="olink ADMIN01502" href="http://www.oracle.com/pls/db112/lookup?id=ADMIN01502"><span class="italic">Oracle Database Administrator's Guide</span></a>, <a class="olink CNCPT211" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT211"><span class="italic">Oracle Database Concepts</span></a>, and <a class="olink PFGRF030" href="http://www.oracle.com/pls/db112/lookup?id=PFGRF030"><span class="italic">Oracle Database Performance Tuning Guide</span></a>. For the syntax required to create and alter tables with SQL, see <a class="olink SQLRF" href="http://www.oracle.com/pls/db112/lookup?id=SQLRF"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p> </div> <!-- class="sect3" --> <a id="CBBICGAD" name="CBBICGAD"></a><a id="ADMQS08212" name="ADMQS08212"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">About Table Column Attributes</h4> <p>You define table columns to hold your data. When you create a column, you specify the following attributes:</p> <ul> <li> <p><a href="#CHDGCAJH">Data Type</a></p> </li> <li> <p><a href="#CHDDAAGI">NOT NULL Column Constraint</a></p> </li> <li> <p><a href="#CHDEDJEB">Default Value</a></p> </li> <li> <p><a href="#CBBGHADA">Encryption</a></p> </li> </ul> <a id="CHDGCAJH" name="CHDGCAJH"></a><a id="ADMQS08216" name="ADMQS08216"></a> <div class="sect4"> <h5 class="sect4">Data Type</h5> <p>The data type attribute defines the kind of data to be stored in the column.<a id="sthref463" name="sthref463"></a> When you create a table, you must specify a data type for each of its columns.</p> <p>Data types define the domain of values that each column can contain. For example, <code>DATE</code> columns cannot accept the value <code>February 29</code> (except for a leap year) or the values <code>2</code> or <code>SHOE</code>. Each value subsequently inserted in a column assumes the column data type. For example, if you insert <code>17-JAN-2004</code> into a date column, then Oracle Database treats that character string as a date value after verifying that it converts to a valid date.</p> <p><a href="#BGBIJHDB">Table 8-1</a> lists some common Oracle Database built-in data types.</p> <div class="tblhruleformalwide"><a id="ADMQS12344" name="ADMQS12344"></a><a id="sthref464" name="sthref464"></a><a id="BGBIJHDB" name="BGBIJHDB"></a> <p class="titleintable">Table 8-1 Common Data Types</p> <table class="HRuleFormalWide" title="Common Data Types" summary="This table describes Oracle's built-in datatypes." dir="ltr" border="1" width="100%" frame="hsides" rules="rows" cellpadding="3" cellspacing="0"> <col width="34%" /> <col width="*" /> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t5">Data Type</th> <th align="left" valign="bottom" id="r1c2-t5">Description</th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t5" headers="r1c1-t5"> <p><code>VARCHAR<a id="sthref465" name="sthref465"></a><a id="sthref466" name="sthref466"></a>2(</code><code><span class="codeinlineitalic">size</span></code> <code>[BYTE|CHAR]</code>)</p> </td> <td align="left" headers="r2c1-t5 r1c2-t5"> <p>Variable-length character string having a maximum length of <code><span class="codeinlineitalic">size</span></code> bytes or characters. A column to hold postal codes for different countries, for example, might be restricted to 12 bytes by defining it as <code>VARCHAR2(12)</code>.</p> <p>You can use the <code>CHAR</code> qualifier, for example <code>VARCHAR2(10 CHAR)</code>, to indicate the maximum length in characters, without regard for the number of bytes required. This is especially useful for languages that use characters with double-byte and triple-byte lengths. The <code>BYTE</code> and <code>CHAR</code> qualifiers override the setting of the <code>NLS_LENGTH_SEMANTICS</code> parameter, which has a default of bytes. The maximum size is 4000 bytes or characters. The minimum is 1 byte or 1 character. You must specify <code><span class="codeinlineitalic">size</span></code> for <code>VARCHAR2</code>.</p> <p>See <a class="olink NLSPG" href="http://www.oracle.com/pls/db112/lookup?id=NLSPG"><span class="italic">Oracle Database Globalization Support Guide</span></a> for more information.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t5" headers="r1c1-t5"> <p><code>NUMBE<a id="sthref467" name="sthref467"></a><a id="sthref468" name="sthref468"></a>R (</code><code><span class="codeinlineitalic">p,s</span></code><code>)</code></p> </td> <td align="left" headers="r3c1-t5 r1c2-t5"> <p>Number having precision <code><span class="codeinlineitalic">p</span></code> and scale <code><span class="codeinlineitalic">s</span></code>. Precision sets the maximum number of digits in the number, and scale defines how many of the digits are to the right of the decimal point. For example, a field to hold monetary values might be defined as <code>NUMBER(12,2)</code>, providing 10 digits for the primary unit of currency (dollars, pounds, marks, and so on) and two digits for the secondary unit (cents, pennies, pfennigs, and so on). The precision <code><span class="codeinlineitalic">p</span></code> can range from 1 to 38. The scale <code><span class="codeinlineitalic">s</span></code> can range from -84 to 127.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t5" headers="r1c1-t5"> <p><code>DA<a id="sthref469" name="sthref469"></a><a id="sthref470" name="sthref470"></a>TE</code></p> </td> <td align="left" headers="r4c1-t5 r1c2-t5"> <p>A composite value that includes both a date and time component. For each <code>DATE</code> value, the database stores the following information: century, year, month, day, hour, minute, and second. When entering a date into a table column of type <code>DATE</code>, you must use the format specified by the <code>NLS_DATE_FORMAT</code> initialization parameter. The <code>NLS_TERRITORY</code> initialization parameter determines the default value of the <code>NLS_DATE_FORMAT</code> parameter. For example, in the United States, the <code>NLS_DATE_FORMAT</code> parameter defaults to '<code>DD-MON-RR</code>'. You must therefore enter a date in the format '<code>11-JAN-06</code>'. Because this format does not include a time component, the time defaults to 12:00:00 a.m. (midnight). You can also use the <code>TO_DATE</code> function, which converts a character string to a date, to include a time component or to enter a date in another format. The valid date range is from January 1, 4712 BC to December 31, 9999 AD.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t5" headers="r1c1-t5"> <p><code>CLO<a id="sthref471" name="sthref471"></a><a id="sthref472" name="sthref472"></a>B</code></p> </td> <td align="left" headers="r5c1-t5 r1c2-t5"> <p>A character large object (CLOB) containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. The maximum size is (4 gigabytes - 1) * (database block size). For example, for a block size of 32K, the maximum CLOB size is 128 terabytes.</p> </td> </tr> </tbody> </table> <br /></div> <!-- class="tblhruleformalwide" --></div> <!-- class="sect4" --> <a id="CHDDAAGI" name="CHDDAAGI"></a><a id="ADMQS08217" name="ADMQS08217"></a> <div class="sect4"> <h5 class="sect4">NOT NULL Column Constraint</h5> <p>Constraints determine valid values for the column. In Oracle Enterprise Manager Database Control (Database Control), the only constraint you can define at the column level on the Create Table page is the <code>NOT NULL</code> constraint, which requires that a value be included in the column whenever a row is inserted or updated. Unlike other constraints described in <a href="#BGBDDJDG">"About Table-Level Constraints"</a>, which can be defined as part of the column definition or part of the table definition, the <code>NOT NULL</code> constraint must be defined as part of the column definition.</p> <p>Use a <code>NOT NULL</code> constraint when data must be supplied for a column for the integrity of the database. For example, if all employees must belong to a specific department, then the column that contains the department identifier must be defined with a <code>NOT NULL</code> constraint. However, do not define a column as <code>NOT NULL</code> if the data can be unknown or may not exist when rows are added or changed. An example of a column for which you must not use a <code>NOT</code> <code>NULL</code> constraint is the second, optional line in a mailing address.</p> <p>The database automatically adds a <code>NOT NULL</code> constraint to the column or columns included in the primary key of a table.</p> </div> <!-- class="sect4" --> <a id="CHDEDJEB" name="CHDEDJEB"></a><a id="ADMQS08218" name="ADMQS08218"></a> <div class="sect4"> <h5 class="sect4">Default Value</h5> <p>This value is automatically stored in the column whenever a new row is inserted without a value being provided for the column. You can specify a default value as a literal or as an expression. However, there are limitations on how you construct the expression. See <a class="olink SQLRF01402" href="http://www.oracle.com/pls/db112/lookup?id=SQLRF01402"><span class="italic">Oracle Database SQL Language Reference</span></a> for details.</p> </div> <!-- class="sect4" --> <a id="CBBGHADA" name="CBBGHADA"></a><a id="ADMQS12077" name="ADMQS12077"></a> <div class="sect4"><!-- infolevel="all" infotype="General" --> <h5 class="sect4">Encryption</h5> <p>You can enable automatic encryption for column data. See the discussion of transparent data encryption in <a class="olink TDPSG40043" href="http://www.oracle.com/pls/db112/lookup?id=TDPSG40043"><span class="italic">Oracle Database 2 Day + Security Guide</span></a> for more information.</p> </div> <!-- class="sect4" --></div> <!-- class="sect3" --> <a id="BGBDDJDG" name="BGBDDJDG"></a><a id="ADMQS08213" name="ADMQS08213"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">About Table-Level Constraints</h4> <p><a id="sthref473" name="sthref473"></a>In an Oracle database, you can apply rules to preserve the integrity of your data. For example, in a table that contains employee data, the employee name column cannot accept <code>NULL</code> as a value. Similarly, in this table, you cannot have two employees with the same ID.</p> <p><a id="sthref474" name="sthref474"></a>Oracle Database enables you to apply data integrity rules called <span class="bold">constraints</span>, both at the table level and at the column level. Any SQL statement that attempts to insert or update a row that violates a constraint results in an error, and the statement is rolled back. Likewise, any attempt to apply a new constraint to a populated table also results in an error if any existing rows violate the new constraint.</p> <p><a id="sthref475" name="sthref475"></a>The types of constraints that you can apply at the table level are as follows:</p> <ul> <li> <p><span class="bold">Primary Key</span>—Requires that a column (or combination of columns) be the unique identifier of the row. A primary key column does not allow <code>NULL</code> values.</p> </li> <li> <p><span class="bold">Unique Key</span>—Requires that no two rows can have duplicate values in a specified column or combination of columns. The set of columns is considered to be a unique key.</p> </li> <li> <p><span class="bold">Check</span>—Requires that a column (or combination of columns) satisfy a condition for every row in the table. A check constraint must be a Boolean expression. It is evaluated each time that a row is inserted or updated. An example of a check constraint is: <code>SALARY > 0</code>.</p> </li> <li> <p><span class="bold">Foreign Key</span>—Requires that for a particular column (or combination of columns), all column values in the child table exist in the parent table. The table that includes the foreign key is called the dependent or <span class="bold">child</span> table. The table that is referenced by the foreign key is called the <span class="bold">parent</span> table. An example of a foreign key constraint is where the department column of the employees table must contain a department ID that exists in the parent department table.</p> </li> </ul> <p>Constraints can be created and usually modified with different statuses. The options include enabled or disabled, which determine if the constraint is checked when rows are added or modified, and deferred or immediate, which cause constraint validation to occur at the end of a transaction or at the end of a statement, respectively.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink CNCPT021" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT021"><span class="italic">Oracle Database Concepts</span></a> for more information about constraints</p> </li> </ul> </div> </div> <!-- class="sect3" --> <a id="CBBDGEJE" name="CBBDGEJE"></a><a id="ADMQS08215" name="ADMQS08215"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">Other Table Creation Considerations</h4> <p>This section describes some additional considerations for creating tables. It contains the following topics:</p> <ul> <li> <p><a href="#CBBIDBDF">User-Defined Types and Large Objects (LOBs)</a></p> </li> <li> <p><a href="#CBBJJAGE">Partitioned Tables and Indexes</a></p> </li> <li> <p><a href="#CBBEFJCC">Physical Storage Attributes</a></p> </li> <li> <p><a href="#CBBCCEGG">Compressed Tables</a></p> </li> </ul> <a id="CBBIDBDF" name="CBBIDBDF"></a><a id="ADMQS12094" name="ADMQS12094"></a> <div class="sect4"><!-- infolevel="all" infotype="General" --> <h5 class="sect4">User-Defined Types and Large Objects (LOBs)</h5> <p><a id="sthref476" name="sthref476"></a><a id="sthref477" name="sthref477"></a>Your new table can include one or more columns defined with user-defined types. <span class="bold">User-defined types</span> enable a single column in a single row to contain multiple values. The multiple values can be represented as arrays, nested tables, or objects, where an object type represents a real-world entity such as a purchase order. (Retrieving a purchase order–type column value could return a <span class="italic">record</span> that contains purchase order number, customer number, amount, and so on.) User-defined types are created with the <code>CREATE TYPE</code> statement and are described in detail in <a class="olink SQLRF01506" href="http://www.oracle.com/pls/db112/lookup?id=SQLRF01506"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p> <p><span class="bold">Large object</span> (<a id="sthref478" name="sthref478"></a><a id="sthref479" name="sthref479"></a><a id="sthref480" name="sthref480"></a><a id="sthref481" name="sthref481"></a>LOB) columns are used to contain unstructured data (such as text or streaming video), and can hold terabytes of information. In Oracle Database 11<span class="italic">g</span>, you can use SecureFiles, the next generation LOB data type, which provide high performance, easier manageability, and full backward compatibility with existing LOB interfaces. SecureFiles also offer advanced features such as intelligent data compression, deduplication and transparent encryption. The LOB implementation available in Oracle Database 10<span class="italic">g</span> Release 2 and prior releases is still supported for backward-compatibility reasons and is now referred to as BasicFiles. If you add a LOB column to a table, then you can specify whether it should be created as a SecureFile or a BasicFile. If you do not specify the storage type, then the LOB is created as a BasicFile to ensure backward compatibility.</p> <p>When creating a table that contains one or more LOB columns, select the <span class="bold">LOB</span> column, then click <span class="bold">Advanced Attributes</span> on the General subpage of the Create Table page to specify the storage type (BasicFile or SecureFile) and the storage options for the LOB column. To specify the same storage type and storage options for all LOB columns in a table, click <span class="bold">Set Default LOB Attributes</span>.</p> </div> <!-- class="sect4" --> <a id="CBBJJAGE" name="CBBJJAGE"></a><a id="ADMQS12095" name="ADMQS12095"></a> <div class="sect4"><!-- infolevel="all" infotype="General" --> <h5 class="sect4">Partitioned Tables and Indexes</h5> <p>You can <a id="sthref482" name="sthref482"></a><a id="sthref483" name="sthref483"></a><span class="italic">partition</span> tables and indexes. Partitioning helps to support very large tables and indexes by enabling you to divide the tables and indexes into smaller and more manageable pieces called <span class="bold">partitions</span>. SQL queries and DML statements do not have to be modified to access partitioned tables and indexes. Partitioning is transparent to the application.</p> <p>After partitions are defined, certain operations become more efficient. For example, for some queries, the database can generate query results by accessing only a subset of partitions, rather than the entire table. This technique (called <span class="bold">partition pruning</span>) can provide order-of-magnitude gains in improved performance. In addition, data management operations can take place at the partition level, rather than on the entire table. This results in reduced times for operations such as data loads; index creation and rebuilding; and backup and recovery.</p> <p>Each partition can be stored in its own tablespace, independent of other partitions. Because different tablespaces can be on different disks, this provides a table structure that can be better tuned for availability and performance. Storing partitions in different tablespaces on separate disks can also optimize available storage usage, because frequently accessed data can be placed on high-performance disks, and infrequently retrieved data can be placed on less expensive storage.</p> <p>Partitioning is useful for many types of applications that manage large volumes of data. Online transaction processing (OLTP) systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from increased performance and manageability.</p> </div> <!-- class="sect4" --> <a id="CBBEFJCC" name="CBBEFJCC"></a><a id="ADMQS12320" name="ADMQS12320"></a> <div class="sect4"><!-- infolevel="all" infotype="General" --> <h5 class="sect4">Physical Storage Attributes</h5> <p>You can specify several storage attributes for a table. For example, you can specify the initial size of the table on disk. For more information about setting storage attributes for a table, see <a class="olink ADMIN014" href="http://www.oracle.com/pls/db112/lookup?id=ADMIN014"><span class="italic">Oracle Database Administrator's Guide</span></a> and <a class="olink SQLRF01402" href="http://www.oracle.com/pls/db112/lookup?id=SQLRF01402"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p> </div> <!-- class="sect4" --> <a id="CBBCCEGG" name="CBBCCEGG"></a><a id="ADMQS12096" name="ADMQS12096"></a> <div class="sect4"><!-- infolevel="all" infotype="General" --> <h5 class="sect4">Compressed Tables</h5> <p>Table Compression is suitable for both OLTP applications and data warehousing applications. <a id="sthref484" name="sthref484"></a><a id="sthref485" name="sthref485"></a>Compressed tables require less disk storage and result in improved query performance due to reduced I/O and buffer cache requirements. Compression is transparent to applications and incurs minimal overhead during bulk loading or regular DML operations such as INSERT, UPDATE or DELETE. You can configure table compression on the Storage subpage of the Create Table page.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink ADMIN01502" href="http://www.oracle.com/pls/db112/lookup?id=ADMIN01502"><span class="italic">Oracle Database Administrator's Guide</span></a> for design and management considerations for different table types</p> </li> <li> <p><a class="olink CNCPT1136" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT1136"><span class="italic">Oracle Database Concepts</span></a> and <a class="olink VLDBG" href="http://www.oracle.com/pls/db112/lookup?id=VLDBG"><span class="italic">Oracle Database VLDB and Partitioning Guide</span></a> for more information about partitioned tables and indexes</p> </li> <li> <p><a class="olink CNCPT1536" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT1536"><span class="italic">Oracle Database Concepts</span></a> for more information about LOBs, SecureFiles and BasicFiles</p> </li> <li> <p><a href="#BEABGHHE">"Example: Creating a Table"</a></p> </li> </ul> </div> </div> <!-- class="sect4" --></div> <!-- class="sect3" --> <a id="BGBBAHAG" name="BGBBAHAG"></a><a id="ADMQS0822" name="ADMQS0822"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Viewing Tables</h3> <p><a id="sthref486" name="sthref486"></a><a id="sthref487" name="sthref487"></a>You can use Database Control to list all the tables in a specified schema, and to view the definitions of individual tables.</p> <a id="ADMQS12287" name="ADMQS12287"></a> <p class="subhead2">To view tables:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Tables</span>.</p> <p>The Tables page appears.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter the name of a schema. Alternatively, click the flashlight icon adjacent to the <span class="bold">Schema</span> field to search for a schema.</p> <p>Examples of schema names include <code>SYS</code> and <code>hr</code>.</p> </li> <li> <p>Leave the Object Name field blank to search for and display all tables in the schema. Alternatively, enter a table name or partial table name to limit the search.</p> <p>If you enter a search string in the <span class="bold">Object Name</span> field, then all tables that have names that start with the search string are displayed. If you precede the search string with an asterisk (*), then all tables that have the search string anywhere in the table name are displayed.</p> </li> <li> <p>Click <span class="bold">Go</span>.</p> <p>The tables in the specified schema are displayed.</p> <img width="802" height="533" src="img/hr_tables.gif" alt="This image shows the Tables page." title="This image shows the Tables page." longdesc="img_text/hr_tables.htm" /><br /> <a id="sthref488" name="sthref488" href="img_text/hr_tables.htm">Description of the illustration hr_tables.gif</a><br /> <br /></li> <li> <p>To view the definition of a particular table, select the table and then click <span class="bold">View</span>. Alternatively, click the table name.</p> <p>The View Table page appears.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBJDBGB" name="CBBJDBGB"></a><a id="ADMQS0823" name="ADMQS0823"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Viewing Table Data</h3> <p><a id="sthref489" name="sthref489"></a><a id="sthref490" name="sthref490"></a>Besides viewing table names and table definitions, you can view the data stored in the table, and the SQL statement used to display the data. You can also change the SQL statement to alter the results set.</p> <a id="ADMQS12288" name="ADMQS12288"></a> <p class="subhead2">To view table data:</p> <ol> <li> <p>Search for a table as described in <a href="#BGBBAHAG">"Viewing Tables"</a>. For example, search for the tables in the <code>hr</code> schema.</p> </li> <li> <p>Select the table that contains the data.</p> <p>For example, select <code><span class="codeinlinebold">employees</span></code>.</p> </li> <li> <p>In the Actions list, select <span class="bold">View Data</span>, and then click <span class="bold">Go</span>.</p> <p>The View Data for Table page appears.</p> <img width="727" height="439" src="img/view_employees.gif" alt="This image is described in the text." title="This image is described in the text." longdesc="img_text/view_employees.htm" /><br /> <a id="sthref491" name="sthref491" href="img_text/view_employees.htm">Description of the illustration view_employees.gif</a><br /> <br /> <p>The <span class="bold">Query</span> field displays the SQL query that was run to view the data for the table. The Result section shows the data in the table. You may have to use the horizontal scroll bar at the bottom of the page to view all columns.</p> </li> <li> <p>(Optional) Click a column name to sort the data by that column.</p> </li> <li> <p>(Optional) Click <span class="bold">Refine Query</span> to change the query and redisplay the data.</p> <p>The Refine Query for Table page appears. This page enables you to select the columns to display. It also enables you to specify a <code>WHERE</code> clause for the SQL <code>SELECT</code> statement to limit the results.</p> </li> </ol> <p>You can also write and submit your own SQL <code>SELECT</code> statement to see the contents of a table. You can run SQL statements by starting a SQL Worksheet session in Database Control. To do so, click <span class="bold">SQL Worksheet</span> in the Related Links section of the Database Home page.</p> <p>A detailed description of the <code>SELECT</code> statement is in <a class="olink SQLRF01702" href="http://www.oracle.com/pls/db112/lookup?id=SQLRF01702"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="BEABGHHE" name="BEABGHHE"></a><a id="ADMQS0824" name="ADMQS0824"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Creating a Table</h3> <p><a id="sthref492" name="sthref492"></a><a id="sthref493" name="sthref493"></a>You can use Database Control to create a table. Before you create and populate a table, you can estimate its size to ensure that you have sufficient space to hold its data.</p> <p>In the following example, you create a table called <code>purchase_orders</code> in the <code>nick</code> schema that you created in <a href="users_secure.htm#CHDJBHHI">Chapter 7, "Administering User Accounts and Security"</a>. The table has the following columns:</p> <div class="inftblinformal"> <table class="Informal" title="Columns of the PURCHASE_ORDER Table" summary="This table descirbes the columns to be created for the sample table purchase_orders. It has 4 rows and 4 columns. Each row represents a column to add to the purchase_orders table. The columns are the following: Column Name, Data Type, Size, Not NULL." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <col width="*" /> <col width="27%" /> <col width="15%" /> <col width="21%" /> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t10">Column Name</th> <th align="left" valign="bottom" id="r1c2-t10">Data Type</th> <th align="left" valign="bottom" id="r1c3-t10">Size</th> <th align="left" valign="bottom" id="r1c4-t10">Not NULL</th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t10" headers="r1c1-t10"><code>PO_NUMBER</code></td> <td align="left" headers="r2c1-t10 r1c2-t10"><code>NUMBER</code></td> <td align="left" headers="r2c1-t10 r1c3-t10"> </td> <td align="left" headers="r2c1-t10 r1c4-t10">Yes</td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t10" headers="r1c1-t10"><code>PO_DESCRIPTION</code></td> <td align="left" headers="r3c1-t10 r1c2-t10"><code>VARCHAR2</code></td> <td align="left" headers="r3c1-t10 r1c3-t10">200</td> <td align="left" headers="r3c1-t10 r1c4-t10">No</td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t10" headers="r1c1-t10"><code>PO_DATE</code></td> <td align="left" headers="r4c1-t10 r1c2-t10"><code>DATE</code></td> <td align="left" headers="r4c1-t10 r1c3-t10"> </td> <td align="left" headers="r4c1-t10 r1c4-t10">Yes</td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t10" headers="r1c1-t10"><code>PO_VENDOR</code></td> <td align="left" headers="r5c1-t10 r1c2-t10"><code>NUMBER</code></td> <td align="left" headers="r5c1-t10 r1c3-t10"> </td> <td align="left" headers="r5c1-t10 r1c4-t10">Yes</td> </tr> </tbody> </table> <br /></div> <!-- class="inftblinformal" --> <a id="ADMQS12289" name="ADMQS12289"></a> <p class="subhead2">To create the PURCHASE_ORDERS table in the NICK schema:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>nick</code> or as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Tables</span>.</p> <p>The Tables page appears.</p> </li> <li> <p>Click <span class="bold">Create</span>.</p> <p>The Create Table: Table Organization page appears.</p> </li> <li> <p>Select <span class="bold">Standard, Heap Organized</span>, and then click <span class="bold">Continue</span>.</p> <p>The Create Table page appears.</p> </li> <li> <p>In the <span class="bold">Name</span> field, enter <code><span class="codeinlinebold">purchase_orders</span></code> as the table name, and in the <span class="bold">Schema</span> field, enter <code><span class="codeinlinebold">nick</span></code>.</p> <p>Because a default tablespace was specified when creating the user <code>nick</code> in the section <a href="users_secure.htm#CHDDCIGA">"Example: Creating a User Account"</a>, accept the default tablespace setting for the table.</p> </li> <li> <p>In the Columns section, enter column information for the <code>purchase_orders</code> table as specified in the table in the introduction to this topic. For example, for the first column in the <code>purchase_orders</code> table, enter the name <code><span class="codeinlinebold">PO_NUMBER</span></code> and the data type <code><span class="codeinlinebold">NUMBER</span></code>, and select the <span class="bold">Not NULL</span> check box.</p> <p>For all <code>purchase_orders</code> columns, you can leave Scale and Default Value blank.</p> <img width="778" height="534" src="img/purchase_orders2.gif" alt="Description of purchase_orders2.gif follows" title="Description of purchase_orders2.gif follows" longdesc="img_text/purchase_orders2.htm" /><br /> <a id="sthref494" name="sthref494" href="img_text/purchase_orders2.htm">Description of the illustration purchase_orders2.gif</a><br /> <br /> <div class="infoboxnote"> <p class="notep1">Note:</p> To create the table with partitions, click <span class="bold">Partitions</span> at the top of the page during this step.</div> </li> <li> <p>(Optional) Obtain an estimate of the table size by completing the following steps:</p> <ol> <li> <p>Click <span class="bold">Estimate Table Size</span>.</p> <p>The Estimate Table Size page appears.</p> </li> <li> <p>In the <span class="bold">Projected Row Count</span> field, enter <code><span class="codeinlinebold">400000</span></code> (four hundred thousand), and then click <span class="bold">Estimate Table Size</span>.</p> <p>The estimated results are calculated and displayed.</p> </li> <li> <p>Click <span class="bold">OK</span> to return to the Create Table page.</p> </li> </ol> <p>The estimate of the table size can help you determine what values to use when specifying the storage parameters for the table.</p> </li> <li> <p>Click <span class="bold">Constraints</span> to view the Constraints subpage, where you can designate a primary key for the table.</p> </li> <li> <p>In the Constraints list, select <code><span class="codeinlinebold">PRIMARY</span></code>, then click <span class="bold">Add</span>.</p> <p>The Add PRIMARY Constraint page appears.</p> <img width="738" height="566" src="img/primary_constraint.gif" alt="Description of primary_constraint.gif follows" title="Description of primary_constraint.gif follows" longdesc="img_text/primary_constraint.htm" /><br /> <a id="sthref495" name="sthref495" href="img_text/primary_constraint.htm">Description of the illustration primary_constraint.gif</a><br /> <br /></li> <li> <p>In the Available Columns list, select <code><span class="codeinlinebold">PO_NUMBER</span></code>, and then click <span class="bold">Move</span>.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> You can also double-click <code><span class="codeinlinebold">PO_NUMBER</span></code>.</div> <p>The <code>po_number</code> column moves to the Selected Columns list.</p> </li> <li> <p>Click <span class="bold">Continue</span> to return to the Constraints subpage of the Create Table page.</p> </li> <li> <p>Click <span class="bold">OK</span>.</p> <p>The Tables page returns, showing a confirmation message and listing the new table in the tables list. The <code>purchase_orders</code> table is now created with <code>po_number</code> as its primary key.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBJGEIJ" name="CBBJGEIJ"></a><a id="ADMQS0825" name="ADMQS0825"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Modifying Table Attributes</h3> <p><a id="sthref496" name="sthref496"></a><a id="sthref497" name="sthref497"></a><a id="sthref498" name="sthref498"></a>You can use Database Control to add and delete table columns and to manage table constraints. This section contains the following topics:</p> <ul> <li> <p><a href="#BGBCIIJE">Example: Adding Table Columns</a></p> </li> <li> <p><a href="#CBBBHAFJ">Example: Deleting a Table Column</a></p> </li> <li> <p><a href="#CBBEEEAJ">Example: Adding a New Table Constraint</a></p> </li> <li> <p><a href="#CBBJAFAA">Example: Modifying an Existing Table Constraint</a></p> </li> <li> <p><a href="#CBBCDICH">Example: Deleting a Table Constraint</a></p> </li> </ul> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> </ul> </div> <a id="BGBCIIJE" name="BGBCIIJE"></a><a id="ADMQS08251" name="ADMQS08251"></a> <div class="sect3"> <h4 class="sect3">Example: Adding Table Columns</h4> <p><a id="sthref499" name="sthref499"></a><a id="sthref500" name="sthref500"></a><a id="sthref501" name="sthref501"></a>In this example, you add columns to the <code>purchase_orders</code> table that you created previously in <a href="#BEABGHHE">"Example: Creating a Table"</a>. The two new columns are named <code>po_date_received</code> and <code>po_requestor_name</code>.</p> <a id="ADMQS12290" name="ADMQS12290"></a> <p class="subhead2">To add columns to the PURCHASE_ORDERS table:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>nick</code> or as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Tables</span>.</p> <p>The Tables page appears.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter <code><span class="codeinlinebold">nick</span></code>, and then click <span class="bold">Go</span>.</p> <p>All tables owned by user <code>nick</code> are displayed.</p> </li> <li> <p>Select the <code><span class="codeinlinebold">PURCHASE_ORDERS</span></code> table, and then click <span class="bold">Edit</span>.</p> <p>The Edit Table page appears.</p> </li> <li> <p>In the Columns section, in the first available row, enter the following information about the new <code>po_date_received</code> column:</p> <div class="inftblinformal"> <table class="Informal" title="Attributes of the New PURCHASE_ORDERS Column" summary="This table includes four rows and two columns. The columns are entitled Field Name and Value. Each of the four rows represents a column attribute to specify." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <col width="40%" /> <col width="*" /> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t15">Field Name</th> <th align="left" valign="bottom" id="r1c2-t15">Value</th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t15" headers="r1c1-t15"><code>Name</code></td> <td align="left" headers="r2c1-t15 r1c2-t15"><code>PO_DATE_RECEIVED</code></td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t15" headers="r1c1-t15"><code>Data</code> <code>Type</code></td> <td align="left" headers="r3c1-t15 r1c2-t15"><code>DATE</code></td> </tr> </tbody> </table> <br /></div> <!-- class="inftblinformal" --> <p>You can leave Size, Scale, Not NULL, and Default Value blank.</p> </li> <li> <p>In the next available row, enter the following information about the new <code>po_requestor_name</code> column:</p> <div class="inftblinformal"> <table class="Informal" title="Attributes of the New PURCHASE_ORDERS Column" summary="This table includes four rows and two columns. The columns are entitled Field Name and Value. Each of the four rows represents a column attribute to specify." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <col width="40%" /> <col width="*" /> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t16">Field Name</th> <th align="left" valign="bottom" id="r1c2-t16">Value</th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t16" headers="r1c1-t16"><code>Name</code></td> <td align="left" headers="r2c1-t16 r1c2-t16"><code>PO_REQUESTOR_NAME</code></td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t16" headers="r1c1-t16"><code>Data</code> <code>Type</code></td> <td align="left" headers="r3c1-t16 r1c2-t16"><code>VARCHAR2</code></td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t16" headers="r1c1-t16"><code>Size</code></td> <td align="left" headers="r4c1-t16 r1c2-t16"><code>40</code></td> </tr> </tbody> </table> <br /></div> <!-- class="inftblinformal" --> <p>You can leave Scale, Not NULL, and Default Value blank.</p> </li> <li> <p>Click <span class="bold">Apply</span>.</p> <p>An update message appears indicating that the table has been modified successfully.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> </ul> </div> </div> <!-- class="sect3" --> <a id="CBBBHAFJ" name="CBBBHAFJ"></a><a id="ADMQS08252" name="ADMQS08252"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">Example: Deleting a Table Column</h4> <p><a id="sthref502" name="sthref502"></a><a id="sthref503" name="sthref503"></a><a id="sthref504" name="sthref504"></a>In this example, you delete the <code>po_requestor_name</code> column that you added to the <code>purchase_orders</code> table in <a href="#BGBCIIJE">"Example: Adding Table Columns"</a>.</p> <a id="ADMQS12291" name="ADMQS12291"></a> <p class="subhead2">To delete the PO_REQUESTOR_NAME column:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>nick</code> or as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Tables</span>.</p> <p>The Tables page appears.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter <code><span class="codeinlinebold">nick</span></code> and then click <span class="bold">Go</span>.</p> <p>All tables owned by user <code>nick</code> are displayed.</p> </li> <li> <p>Select the <code><span class="codeinlinebold">PURCHASE_ORDERS</span></code> table, and then click <span class="bold">Edit</span>.</p> <p>The Edit Table page appears.</p> </li> <li> <p>In the Columns section, select the <code><span class="codeinlinebold">PO_REQUESTOR_NAME</span></code> column, and then click <span class="bold">Delete</span>.</p> <p>The row that contained the information for the deleted column becomes blank.</p> </li> <li> <p>Click <span class="bold">Apply</span>.</p> <p>An update message appears indicating that the table has been modified successfully.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> </ul> </div> </div> <!-- class="sect3" --> <a id="CBBEEEAJ" name="CBBEEEAJ"></a><a id="ADMQS08253" name="ADMQS08253"></a> <div class="sect3"> <h4 class="sect3">Example: Adding a New Table Constraint</h4> <p><a id="sthref505" name="sthref505"></a><a id="sthref506" name="sthref506"></a><a id="sthref507" name="sthref507"></a>In this example, you add a table constraint to the <code>purchase_orders</code> table that you created in <a href="#BEABGHHE">"Example: Creating a Table"</a>. To enforce the rule that the <code>po_date_received</code> value must be either the same day as, or later than, the value of <code>po_date</code>, you add a check constraint.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> You can also add constraints during table creation, as shown in <a href="#BEABGHHE">"Example: Creating a Table"</a>. In that example, you added a primary key constraint.</div> <a id="ADMQS12292" name="ADMQS12292"></a> <p class="subhead2">To add a table constraint to the PURCHASE_ORDERS table:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>nick</code> or as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Tables</span>.</p> <p>The Tables page appears.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter <code><span class="codeinlinebold">nick</span></code> and then click <span class="bold">Go</span>.</p> <p>All tables owned by user <code>nick</code> are displayed.</p> </li> <li> <p>Select the <code><span class="codeinlinebold">PURCHASE_ORDERS</span></code> table, and then click <span class="bold">Edit</span>.</p> <p>The Edit Table page appears.</p> </li> <li> <p>Click <span class="bold">Constraints</span> to display the Constraints subpage.</p> <img width="820" height="372" src="img/constraints.gif" alt="Description of constraints.gif follows" title="Description of constraints.gif follows" longdesc="img_text/constraints.htm" /><br /> <a id="sthref508" name="sthref508" href="img_text/constraints.htm">Description of the illustration constraints.gif</a><br /> <br /></li> <li> <p>In the list adjacent to the <span class="bold">Add</span> button, select <code><span class="codeinlinebold">CHECK</span></code>, and then click <span class="bold">Add</span>.</p> <p>The Add CHECK Constraint page appears.</p> </li> <li> <p>In the <span class="bold">Name</span> field, enter <code><span class="codeinlinebold">po_check_rcvd_date</span></code>, overwriting the system-assigned default name.</p> </li> <li> <p>In the <span class="bold">Check Condition</span> field, enter the following:</p> <pre xml:space="preserve" class="oac_no_warn">po_date_received >= po_date </pre> <p>This expression indicates that <code>po_date_received</code> must be greater than or equal to <code>po_date</code>. For date columns, this is equivalent to stating that <code>po_date_received</code> must be on the same day as, or later than, <code>po_date</code>.</p> </li> <li> <p>Click <span class="bold">Continue</span></p> <p>The new constraint appears on the Constraints subpage.</p> </li> <li> <p>Click <span class="bold">Apply</span>.</p> <p>A confirmation message appears.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> <li> <p><a href="#BGBDDJDG">"About Table-Level Constraints"</a></p> </li> </ul> </div> </div> <!-- class="sect3" --> <a id="CBBJAFAA" name="CBBJAFAA"></a><a id="ADMQS08254" name="ADMQS08254"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">Example: Modifying an Existing Table Constraint</h4> <p><a id="sthref509" name="sthref509"></a><a id="sthref510" name="sthref510"></a><a id="sthref511" name="sthref511"></a>There are a few ways in which you can modify a table constraint. You can change the status of an existing table constraint, for example, from an enabled state to a disabled state. In this example, you disable the check constraint that you created for the <code>purchase_orders</code> table in <a href="#CBBEEEAJ">"Example: Adding a New Table Constraint"</a>.</p> <a id="ADMQS12293" name="ADMQS12293"></a> <p class="subhead2">To disable a constraint for the PURCHASE_ORDERS table:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>nick</code> or as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Tables</span>.</p> <p>The Tables page appears.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter <code><span class="codeinlinebold">nick</span></code> and then click <span class="bold">Go</span>.</p> <p>All tables owned by user <code>nick</code> are displayed.</p> </li> <li> <p>Select the <code><span class="codeinlinebold">purchase_orders</span></code> table, and then click <span class="bold">Edit</span>.</p> <p>The Edit Table page appears.</p> </li> <li> <p>Click <span class="bold">Constraints</span> to display the Constraints subpage.</p> </li> <li> <p>Select the constraint named <code><span class="codeinlinebold">PO_CHECK_RCVD_DATE</span></code>, and then click <span class="bold">Edit</span>.</p> <p>The Edit CHECK Constraint page appears.</p> </li> <li> <p>In the Attributes section, select <span class="bold">Disabled</span>, and then click <span class="bold">Continue</span>.</p> </li> <li> <p>Click <span class="bold">Apply</span>.</p> <p>A confirmation message appears. The Disabled column shows that the check constraint has been disabled.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> <li> <p><a href="#BGBDDJDG">"About Table-Level Constraints"</a></p> </li> </ul> </div> </div> <!-- class="sect3" --> <a id="CBBCDICH" name="CBBCDICH"></a><a id="ADMQS08255" name="ADMQS08255"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">Example: Deleting a Table Constraint</h4> <p><a id="sthref512" name="sthref512"></a><a id="sthref513" name="sthref513"></a><a id="sthref514" name="sthref514"></a>You can delete constraints from a table with Database Control. Deleting a table constraint may cause the deletion of other constraints. For example, if you delete the primary key constraint from a table (the parent table) that is referenced in a foreign key constraint in another table (the child table), then the foreign key constraint in the child table is also deleted through a cascading delete mechanism.</p> <p>In this example, you delete the check constraint that you created for the <code>purchase_orders</code> table in <a href="#CBBEEEAJ">"Example: Adding a New Table Constraint"</a>.</p> <a id="ADMQS12294" name="ADMQS12294"></a> <p class="subhead2">To delete a constraint from the PURCHASE_ORDERS table:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>nick</code> or as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Tables</span></p> <p>The Tables page appears.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter <code>NICK</code> and then click <span class="bold">Go</span>.</p> <p>All tables owned by user <code>NICK</code> are displayed.</p> </li> <li> <p>Select the <code><span class="codeinlinebold">PURCHASE_ORDERS</span></code> table, and then click <span class="bold">Edit</span>.</p> <p>The Edit Table page appears.</p> </li> <li> <p>Click <span class="bold">Constraints</span> to display the Constraints subpage.</p> </li> <li> <p>Select the constraint named <code><span class="codeinlinebold">PO_CHECK_RCVD_DATE</span></code>, and then click <span class="bold">Delete</span>.</p> <p>The check constraint is removed from the list.</p> </li> <li> <p>Click <span class="bold">Apply</span>.</p> <p>A confirmation message appears.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink CNCPT33336" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT33336"><span class="italic">Oracle Database Concepts</span></a> for more information about the cascading delete mechanism</p> </li> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> <li> <p><a href="#BGBDDJDG">"About Table-Level Constraints"</a></p> </li> </ul> </div> </div> <!-- class="sect3" --></div> <!-- class="sect2" --> <a id="CFHGBGCB" name="CFHGBGCB"></a><a id="ADMQS0826" name="ADMQS0826"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Loading Data into a Table</h3> <p><a id="sthref515" name="sthref515"></a><a id="sthref516" name="sthref516"></a><a id="sthref517" name="sthref517"></a><a id="sthref518" name="sthref518"></a>You can use Database Control to load data into a table. You can load data from a source file that is on your local computer—the one where your browser is running—or from a source file that is on the database host computer—the computer on which the Oracle instance is running. Because Database Control invokes the Oracle SQL*Loader utility to load the data, the format of the data in the source file can be of any format that is supported by SQL*Loader. In this example, you use a comma-delimited text file as the source file. In SQL*Loader terminology, the source file is referred to as the <span class="italic">data file</span>.</p> <p><a id="sthref519" name="sthref519"></a><a id="sthref520" name="sthref520"></a><a id="sthref521" name="sthref521"></a>SQL*Loader also uses a control file to control the loading of data from the data file. The <span class="bold">control file</span> is a text file that contains statements written in the SQL*Loader command language. These statements specify where to find the data, how to parse and interpret the data, where to insert the data, and more. Database Control contains a Load Data <span class="italic">wizard</span> that takes you through the steps of preparing and running a data load job with SQL*Loader. (A <span class="bold">wizard</span> is an online, guided workflow.) The Load Data wizard can automatically create the SQL*Loader control file for you.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> The SQL*Loader control file is unrelated to the database control files described in <a href="storage.htm#CACBFEHH">"About Control Files"</a>.</div> <p>In this example, you load data into the <code>PURCHASE_ORDERS</code> table that you created in <a href="#BEABGHHE">"Example: Creating a Table"</a>. For simplicity, this example loads only three rows.</p> <p>To prepare for this example, you must create a text file named <code>load.dat</code> on the file system of the database host computer or on the file system of your local computer. The contents of the file should be as follows:</p> <pre xml:space="preserve" class="oac_no_warn">1, Office Equipment, 25-MAY-2006, 1201, 13-JUN-2006 2, Computer System, 18-JUN-2006, 1201, 27-JUN-2006 3, Travel Expense, 26-JUN-2006, 1340, 11-JUL-2006 </pre> <div class="infoboxnote"> <p class="notep1">Note:</p> This example assumes that the columns in the <code>PURCHASE_ORDERS</code> table are the following: <code>PO_NUMBER</code>, <code>PO_DESCRIPTION</code>, <code>PO_DATE</code>, <code>PO_VENDOR</code>, and <code>PO_DATE_RECEIVED</code>. If your <code>PURCHASE_ORDERS</code> table does not have all these columns (or has additional columns), then modify the data in the text file accordingly.</div> <a id="ADMQS12295" name="ADMQS12295"></a> <p class="subhead2"><a id="sthref522" name="sthref522"></a>To load data into the PURCHASE_ORDERS table:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Data Movement</span>.</p> <p>The Data Movement subpage appears.</p> </li> <li> <p>In the Move Row Data section, click <span class="bold">Load Data from User Files</span>.</p> <p>The Load Data: Generate or Use Existing Control File page appears.</p> </li> <li> <p>Select <span class="bold">Automatically Generate Control File</span>, and enter host computer credentials (user name and password) for the database host computer.</p> </li> <li> <p>Click <span class="bold">Continue</span>.</p> <p>The first page of the Load Data wizard appears. Its title is Load Data: Data Files.</p> </li> <li> <p>Follow the steps in the wizard, clicking <span class="bold">Next</span> to proceed to each new step.</p> <p>For information about using any of the wizard pages, click <span class="bold">Help</span> on that page. At the conclusion of the wizard, you submit a job that runs SQL*Loader. A job status page is then displayed. If necessary, refresh the status page until you see a succeeded (or failed) status.</p> </li> <li> <p>If the job succeeded, then confirm that the data was successfully loaded by doing one of the following:</p> <ul> <li> <p>View the table data.</p> <p>See <a href="#CBBJDBGB">"Viewing Table Data"</a>.</p> </li> <li> <p>Examine the SQL*Loader log file, which is written to the host computer directory that you designated for the SQL*Loader data file.</p> </li> </ul> <div class="infoboxnote"> <p class="notep1">Note:</p> If the job succeeds, then it means only that Database Control was able to run the SQL*Loader utility. It does not necessarily mean that SQL*Loader ran without errors. For this reason, you must confirm that the data loaded successfully.</div> </li> <li> <p>If the job failed, then examine the SQL*Loader log file, correct any errors, and try again.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink SUTIL" href="http://www.oracle.com/pls/db112/lookup?id=SUTIL"><span class="italic">Oracle Database Utilities</span></a> for more information about SQL*Loader</p> </li> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBIAAFF" name="CBBIAAFF"></a><a id="ADMQS0827" name="ADMQS0827"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Deleting a Table</h3> <p><a id="sthref523" name="sthref523"></a><a id="sthref524" name="sthref524"></a>If you no longer need a table, then you can delete it using Database Control. When you delete a table, the database deletes the data and dependent objects of the table (such as indexes), and removes the table from the data dictionary.</p> <p>When you delete a table from a locally managed tablespace that is not the <code>SYSTEM</code> tablespace, the database does not immediately reclaim the space associated with the table. Instead, it places the table and any dependent objects in the <a id="sthref525" name="sthref525"></a><span class="italic">recycle bin</span>. You can then restore the table, its data, and its dependent objects from the recycle bin if necessary. You can view the contents of the recycle bin by clicking <span class="bold">Recycle Bin</span> on the Tables page. Note that users can see only tables that they own in the recycle bin. See <a class="olink ADMIN01511" href="http://www.oracle.com/pls/db112/lookup?id=ADMIN01511"><span class="italic">Oracle Database Administrator's Guide</span></a> for more information about the recycle bin, including how to view, purge, and recover tables for which you are not the owner.</p> <a id="ADMQS12296" name="ADMQS12296"></a> <p class="subhead2">To delete a table:</p> <ol> <li> <p>Search for the table to delete, as explained in <a href="#BGBBAHAG">"Viewing Tables"</a>.</p> </li> <li> <p>Select the table, and then click <span class="bold">Delete With Options</span>.</p> <p>The Delete With Options page appears.</p> </li> <li> <p>Select <span class="bold">Delete the table definition, all its data, and dependent objects (DROP)</span>.</p> </li> <li> <p>Select <span class="bold">Delete all referential integrity constraints (CASCADE CONTRAINTS)</span>.</p> </li> <li> <p>Click <span class="bold">Yes</span>.</p> <p>The Tables page returns and displays a confirmation message.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBGHBEG">"About Tables"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="BGBDBDFB" name="BGBDBDFB"></a><a id="ADMQS083" name="ADMQS083"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Managing Indexes</h2> <p>The following topics describe how to create and manage indexes:</p> <ul> <li> <p><a href="#CBBCBFJA">About Indexes</a></p> </li> <li> <p><a href="#CBBDDCBC">Viewing Indexes</a></p> </li> <li> <p><a href="#CBBHCDHB">Example: Creating an Index</a></p> </li> <li> <p><a href="#CBBGHDBG">Example: Deleting an Index</a></p> </li> </ul> <a id="CBBCBFJA" name="CBBCBFJA"></a><a id="ADMQS0831" name="ADMQS0831"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">About Indexes</h3> <p><a id="sthref526" name="sthref526"></a><a id="sthref527" name="sthref527"></a>Indexes are optional schema objects that are associated with tables. You create indexes on tables to improve query performance. Just as the index in a guide helps you to quickly locate specific information, an Oracle Database index provides quick access to table data.</p> <p>You can create as many indexes on a table as you need. You create each index on one or more columns of a table. For example, in a purchase orders table, if you create an index on the vendor number column, then you can then sequentially access the rows of the table in vendor number order, without having to actually sort the rows. Additionally, you can directly access all purchase orders issued to a particular vendor without having to scan the entire table.</p> <p>After an index is created, it is automatically maintained and used by the database. Changes to the data or structure of a table, such as adding new rows, updating rows, or deleting rows, are automatically incorporated into all relevant indexes. This is transparent to the user.</p> <p>Some indexes are created implicitly through constraints that are placed on a table. For example, the database automatically creates an index on the columns of a primary key constraint or unique key constraint.</p> <p>The following topics provide more background information about indexes:</p> <ul> <li> <p><a href="#CBBFBHJG">Indexes and Performance</a></p> </li> <li> <p><a href="#CBBEBGAD">Index Attributes</a></p> </li> </ul> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBDDCBC">"Viewing Indexes"</a></p> </li> <li> <p><a href="#CBBHCDHB">"Example: Creating an Index"</a></p> </li> <li> <p><a class="olink CNCPT721" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT721"><span class="italic">Oracle Database Concepts</span></a></p> </li> </ul> </div> <a id="CBBFBHJG" name="CBBFBHJG"></a><a id="ADMQS08311" name="ADMQS08311"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">Indexes and Performance</h4> <p>Indexes generally improve the performance of queries and DML statements that operate on a single, existing row or a small number of existing rows. However, too many indexes can increase the processing overhead for statements that add, modify, or delete rows.</p> <p>To determine if you can improve application performance with more indexes, you can run the SQL Access Advisor in Oracle Enterprise Manager Database Control (Database Control). See <a href="montune.htm#CHDEAJCC">"Running the SQL Access Advisor"</a>.</p> <p>Before you add additional indexes, examine the performance of your database for queries and DML. You can then compare performance after the new indexes are added.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink RATUG166" href="http://www.oracle.com/pls/db112/lookup?id=RATUG166"><span class="italic">Oracle Database Real Application Testing User's Guide</span></a> for information about using the SQL Performance Analyzer to analyze the SQL performance impact of any type of schema or system changes</p> </li> </ul> </div> </div> <!-- class="sect3" --> <a id="CBBEBGAD" name="CBBEBGAD"></a><a id="ADMQS08312" name="ADMQS08312"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4 class="sect3">Index Attributes</h4> <p>Indexes can be created in several ways, using various combinations of index attributes. The primary index attributes are the following:</p> <a id="ADMQS12359" name="ADMQS12359"></a> <p class="subhead2">Standard (B-tree) and Bitmap</p> <p>A standard, B-tree index contains an entry for each value in the index key along with a disk address of the row where the value is stored. A B-tree index is the default and most common type of index in an Oracle database.</p> <p>A bitmap index uses strings of bits to encapsulate values and potential row addresses. It is more compact than a B-tree index and can perform some types of retrieval more efficiently. For general use, however, a bitmap index requires more overhead during row operations on the table and should be used primarily for data warehouse environments, as described in <a class="olink DWHSG006" href="http://www.oracle.com/pls/db112/lookup?id=DWHSG006"><span class="italic">Oracle Database Data Warehousing Guide</span></a>.</p> <a id="ADMQS12360" name="ADMQS12360"></a> <p class="subhead2">Ascending and Descending</p> <p>The default search through an index is from lowest to highest value, where character data is sorted by ASCII values, numeric data from smallest to largest number, and date from the earliest to the latest value. This default search method is performed in indexes created as ascending indexes. You can cause index searches to reverse the search order by creating the related index with the descending option.</p> <a id="ADMQS12361" name="ADMQS12361"></a> <p class="subhead2">Column and Functional</p> <p>Typically, an index entry is based on the value or values found in the column or columns of a table. This is a column index. Alternatively, you can create a function-based index in which the indexed value is derived from the table data. For example, to find character data that can be in various combinations of upper and lowercase letters, you can use a function-based index based on the <code>UPPER()</code> function to look for the values as if they were all in uppercase characters.</p> <a id="ADMQS12362" name="ADMQS12362"></a> <p class="subhead2">Single-Column and Concatenated</p> <p>You can create an index on just one column, which is called a <span class="bold">single-column index</span>, or on multiple columns, which is called a <span class="bold">concatenated index</span>. Concatenated indexes are useful when all the index columns are likely to be included in the <code>WHERE</code> clause of frequently executed SQL statements.</p> <p>For concatenated indexes, you must define the columns used in the index carefully so that the column with the fewest duplicate values is named first, the column with next fewest duplicate values is named second, and so on. Columns with many duplicate values or many rows with <code>NULL</code> values should not be included or should be the last-named columns in the index definition.</p> <a id="ADMQS12363" name="ADMQS12363"></a> <p class="subhead2">Nonpartitioned and Partitioned</p> <p>As with tables, you can partition an index. In most situations, it is useful to partition an index when the associated table is partitioned, and to partition the index using the same partitioning scheme as the table. (For example, if the table is range-partitioned by sales date, then you create an index on sales date and partition the index using the same ranges as the table partitions.) This is known as a <span class="bold">local</span> partitioned index. However, you do not have to partition an index using the same partitioning scheme as its table. You can also create a nonpartitioned, or <a id="sthref528" name="sthref528"></a><span class="bold">global</span>, <a id="sthref529" name="sthref529"></a>index on a partitioned table.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink CNCPT721" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT721"><span class="italic">Oracle Database Concepts</span></a> for design and management considerations of different index types</p> </li> <li> <p><a class="olink SQLRF01209" href="http://www.oracle.com/pls/db112/lookup?id=SQLRF01209"><span class="italic">Oracle Database SQL Language Reference</span></a> for the syntax to create indexes</p> </li> <li> <p><a class="olink VLDBG" href="http://www.oracle.com/pls/db112/lookup?id=VLDBG"><span class="italic">Oracle Database VLDB and Partitioning Guide</span></a> for more information about partitioned tables and indexes</p> </li> </ul> </div> </div> <!-- class="sect3" --></div> <!-- class="sect2" --> <a id="CBBDDCBC" name="CBBDDCBC"></a><a id="ADMQS0832" name="ADMQS0832"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Viewing Indexes</h3> <p><a id="sthref530" name="sthref530"></a><a id="sthref531" name="sthref531"></a><a id="sthref532" name="sthref532"></a>You use the Indexes page of Database Control to view the indexes in your database.</p> <a id="ADMQS12297" name="ADMQS12297"></a> <p class="subhead2">To view indexes:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Indexes</span>.</p> <p>The Indexes page appears.</p> </li> <li> <p>In the Search By list, do one of the following:</p> <ul> <li> <p>Select <span class="bold">Index Name</span> to search for indexes by name.</p> <p>Every index has a system-assigned or user-assigned name.</p> </li> <li> <p>Select <span class="bold">Table Name</span> to search for indexes that belong to a particular table.</p> </li> </ul> </li> <li> <p>In the <span class="bold">Schema</span> field, enter the name of a schema. Alternatively, click the flashlight icon adjacent to the <span class="bold">Schema</span> field to search for a schema.</p> </li> <li> <p>Do one of the following:</p> <ul> <li> <p>If you are searching by index name, then leave the Object Name field blank to search for and display all indexes in the schema. Alternatively, enter an index name or partial index name as a search string.</p> <p>If you enter a search string in the <span class="bold">Object Name</span> field, then all indexes that have names that start with the search string are displayed. If you precede the search string with an asterisk (*), then all indexes that have the search string anywhere in the index name are displayed.</p> </li> <li> <p>If you are searching by table name, then enter a table name or partial table name in the <span class="bold">Object Name</span> field.</p> <p>If you enter a partial table name as a search string, then indexes are displayed for all tables that have names that start with the search string. If you precede the search string with an asterisk (*), then indexes are displayed for all tables that have the search string anywhere in the table name.</p> </li> </ul> </li> <li> <p>Click <span class="bold">Go</span>.</p> <p>The indexes in the specified schema are displayed.</p> </li> <li> <p>To view the definition of a particular index, select the index and then click <span class="bold">View</span>. Alternatively, double-click the index name.</p> <p>The View Index page appears. This page includes basic information about the index, including its status, the table and column or columns on which it is built, the space used by the index, the options used in its definition, and index statistics.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBCBFJA">"About Indexes"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBHCDHB" name="CBBHCDHB"></a><a id="ADMQS0833" name="ADMQS0833"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Creating an Index</h3> <p><a id="sthref533" name="sthref533"></a><a id="sthref534" name="sthref534"></a><a id="sthref535" name="sthref535"></a><a id="sthref536" name="sthref536"></a>When you create an index, you specify one or more table columns to be indexed and the type of index to create.</p> <p>In this example, you create a standard B-tree index on the <code>SUPPLIER_ID</code> column in the <code>SH.PRODUCTS</code> table. (The <code>SH</code> schema is part of the sample schemas.)</p> <a id="ADMQS12298" name="ADMQS12298"></a> <p class="subhead2">To create a supplier index on the SH.PRODUCTS table:</p> <ol> <li> <p>View the tables in the <code>SH</code> schema, by following the instructions in the section <a href="#BGBBAHAG">"Viewing Tables"</a>.</p> </li> <li> <p>Select the <code><span class="codeinlinebold">PRODUCTS</span></code> table.</p> </li> <li> <p>In the Actions list, select <span class="bold">Create Index</span>, and then click <span class="bold">Go</span>.</p> <p>The Create Index page appears.</p> <img width="702" height="488" src="img/create_index.gif" alt="Description of create_index.gif follows" title="Description of create_index.gif follows" longdesc="img_text/create_index.htm" /><br /> <a id="sthref537" name="sthref537" href="img_text/create_index.htm">Description of the illustration create_index.gif</a><br /> <br /></li> <li> <p>Enter the following information:</p> <ul> <li> <p>In the <span class="bold">Name</span> field, enter <code><span class="codeinlinebold">PRODUCTS_SUPPLIER_IDX</span></code>.</p> </li> <li> <p>For the <span class="bold">Tablespace</span> field, accept the default value.</p> </li> <li> <p>For Index Type, select <span class="bold">Standard - B-tree</span>.</p> </li> <li> <p>In the Table Columns list, select the <code>SUPPLIER_ID</code> column by entering <code><span class="codeinlinebold">1</span></code> in the <span class="bold">Order</span> column.</p> <p>If your index were to consist of multiple columns (a concatenated index), then you would enter <code>2</code> in the next column to include, and so on. These numbers indicate the order in which the columns are to be concatenated, from left to right, or from most significant in the sort order to least significant.</p> </li> <li> <p>For <span class="bold">Sorting Order</span>, accept the default selection of <span class="bold">ASC</span> (ascending).</p> </li> </ul> </li> <li> <p>Click <span class="bold">OK</span> to create the index.</p> <p>The Indexes page returns and displays a confirmation message. The new index is listed in the table of indexes.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBCBFJA">"About Indexes"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBGHDBG" name="CBBGHDBG"></a><a id="ADMQS0834" name="ADMQS0834"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Deleting an Index</h3> <p>If you no longer need an index, then you can delete it using Database Control.</p> <p>In this example, you delete the <code>PRODUCTS_SUPPLIER_IDX</code> index that you created previously on the <code>SH.PRODUCTS</code> table in <a href="#CBBHCDHB">"Example: Creating an Index"</a>.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> You cannot delete an index that is currently used to enforce a constraint. You must disable or delete the constraint and then, if the index is not deleted as a result of that action, delete the index.</div> <a id="ADMQS12299" name="ADMQS12299"></a> <p class="subhead2">To delete the supplier index on the SH.PRODUCTS table:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Indexes</span>.</p> <p>The Indexes page appears.</p> </li> <li> <p>In the Search By list, select <span class="bold">Table Name</span>.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter <code><span class="codeinlinebold">SH</span></code>.</p> </li> <li> <p>In the <span class="bold">Object Name</span> field, enter <code><span class="codeinlinebold">PROD</span></code>.</p> <p>You can enter only the first few letters of the table name.</p> </li> <li> <p>Click <span class="bold">Go</span>.</p> <p>All indexes on the <code>PRODUCTS</code> table are displayed.</p> </li> <li> <p>Select the <code><span class="codeinlinebold">PRODUCTS_SUPPLIER_IDX</span></code> index, and then click <span class="bold">Delete</span>.</p> <p>A confirmation page appears.</p> </li> <li> <p>Click <span class="bold">Yes</span> to delete the index.</p> <p>The Indexes page returns and displays a confirmation message.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBCBFJA">"About Indexes"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <a id="BGBICDFE" name="BGBICDFE"></a><a id="ADMQS084" name="ADMQS084"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Managing Views</h2> <p>The following topics describe how to create and manage views:</p> <ul> <li> <p><a href="#CBBCIEFC">About Views</a></p> </li> <li> <p><a href="#CBBCGCAJ">Displaying Views</a></p> </li> <li> <p><a href="#CBBFFJGA">Example: Creating a View</a></p> </li> <li> <p><a href="#CBBBCJGH">Example: Deleting a View</a></p> </li> </ul> <a id="CBBCIEFC" name="CBBCIEFC"></a><a id="ADMQS0841" name="ADMQS0841"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">About Views</h3> <p><a id="sthref538" name="sthref538"></a><a id="sthref539" name="sthref539"></a><span class="bold">Views</span> are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data, but instead derive their data from the tables upon which they are based. These tables are referred to as the <span class="bold">base tables</span> of the view.</p> <p>Similar to tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view. Views can provide an additional level of security by restricting access to a predetermined set of rows and columns of a table. They can also hide data complexity and store complex queries.</p> <p>Many important views are in the <code>SYS</code> schema. There are two types: <span class="italic">static data dictionary views</span> and <span class="italic">dynamic performance views</span>. Complete descriptions of the views in the <code>SYS</code> schema are in <a class="olink REFRN" href="http://www.oracle.com/pls/db112/lookup?id=REFRN"><span class="italic">Oracle Database Reference</span></a>.</p> <a id="ADMQS12300" name="ADMQS12300"></a> <p class="subhead2">Static Data Dictionary Views</p> <p>The data dictionary views are called <span class="bold">static views</span> because they change infrequently, only when a change is made to the data dictionary. Examples of data dictionary changes include creating a new table or granting a privilege to a user.</p> <p>Many data dictionary tables have three corresponding views:</p> <ul> <li> <p>A <code>DBA_</code> view displays all relevant information in the entire database. <code>DBA_</code> views are intended only for administrators.</p> <p>An example of a <code>DBA_</code> view is <code>DBA_TABLESPACES</code>, which contains one row for each tablespace in the database.</p> </li> <li> <p>An <code>ALL_</code> view displays all the information accessible to the current user, including information from the schema of the current user, and information from objects in other schemas, if the current user has access to those objects through privileges or roles.</p> <p>An example of an <code>ALL_</code> view is <code>ALL_TABLES</code>, which contains one row for every table for which the user has object privileges.</p> </li> <li> <p>A <code>USER_</code> view displays all the information from the schema of the current user. No special privileges are required to query these views.</p> <p>An example of a <code>USER_</code> view is <code>USER_TABLES</code>, which contains one row for every table owned by the user.</p> </li> </ul> <p>The columns in the <code>DBA_</code>, <code>ALL_</code>, and <code>USER_</code> views are usually nearly identical.</p> <a id="ADMQS12301" name="ADMQS12301"></a> <p class="subhead2">Dynamic Performance Views</p> <p><span class="bold">Dynamic performance views</span> monitor ongoing database activity. They are available only to administrators. The names of dynamic performance views start with the characters <code>V$</code>. For this reason, these views are often referred to as <code>V$</code> views.</p> <p>An example of a <code>V$</code> view is <code>V$SGA</code>, which returns the current sizes of various System Global Area (SGA) memory components.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBCGCAJ">"Displaying Views"</a></p> </li> <li> <p><a href="#CBBFFJGA">"Example: Creating a View"</a></p> </li> <li> <p><a href="#CBBBCJGH">"Example: Deleting a View"</a></p> </li> <li> <p><a class="olink CNCPT311" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT311"><span class="italic">Oracle Database Concepts</span></a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBCGCAJ" name="CBBCGCAJ"></a><a id="ADMQS0842" name="ADMQS0842"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Displaying Views</h3> <p><a id="sthref540" name="sthref540"></a><a id="sthref541" name="sthref541"></a>You can use Oracle Enterprise Manager Database Control (Database Control) to list the views in a specified schema. You can also display the view definitions.</p> <a id="ADMQS12302" name="ADMQS12302"></a> <p class="subhead2">To display views:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Views</span>.</p> <p>The Views page appears.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter the name of a schema. Alternatively, click the flashlight icon adjacent to the <span class="bold">Schema</span> field to search for a schema.</p> <p>Examples of schema names include <code>SYS</code> and <code>hr</code>.</p> </li> <li> <p>Leave the Object Name field blank to search for and display all views in the schema. Alternatively, enter a view name or partial view name to limit the search.</p> <p>If you enter a search string in the <span class="bold">Object Name</span> field, then all views that have names that start with the search string are displayed. If you precede the search string with an asterisk (*), then all views that have the search string anywhere in the view name are displayed.</p> </li> <li> <p>Click <span class="bold">Go</span>.</p> <p>The views in the specified schema are displayed.</p> </li> <li> <p>To view the definition of a particular view, select the view and then click <span class="bold">View</span>. Alternatively, double-click the view name.</p> <p>The View page appears.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBCIEFC">"About Views"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBFFJGA" name="CBBFFJGA"></a><a id="ADMQS0843" name="ADMQS0843"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Creating a View</h3> <p>In this example, you create a view named <code>king_view</code>, which uses the <code>hr.employees</code> table as its base table. (The <code>hr</code> schema is part of the sample schemas.) This view filters the table data so that only employees who report directly to the manager King, whose employee ID is 100, are returned in queries. In an application scenario, this view adds an additional level of security to the <code>hr.employees</code> table while providing a suitable presentation of relevant information for manager King.</p> <a id="ADMQS12303" name="ADMQS12303"></a> <p class="subhead2">To create the KING_VIEW view on the HR.EMPLOYEES table:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>hr</code> or as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Views</span>.</p> <p>The Views page appears.</p> </li> <li> <p>Click <span class="bold">Create</span>.</p> <p>The Create View page appears.</p> </li> <li> <p>Enter the following information:</p> <ul> <li> <p>In the <span class="bold">Name</span> field, enter <code><span class="codeinlinebold">king_view</span></code>.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter <code><span class="codeinlinebold">hr</span></code>.</p> </li> <li> <p>In the <span class="bold">Query Text</span> field, enter the following SQL statement:</p> <pre xml:space="preserve" class="oac_no_warn">SELECT * FROM hr.employees WHERE manager_id = 100 </pre></li> </ul> </li> <li> <p>Click <span class="bold">OK</span>.</p> <p>The Views page returns and displays a confirmation message. The new view appears in the list of views.</p> </li> </ol> <a id="ADMQS12304" name="ADMQS12304"></a> <p class="subhead2">To test the new KING_VIEW view:</p> <ol> <li> <p>On the Views page, select <code><span class="codeinlinebold">king_view</span></code> and then select <span class="bold">View Data</span> from the Actions list.</p> </li> <li> <p>Click <span class="bold">Go</span>.</p> <p>The View Data for View page appears. The data selected by the view appears in the Result section.</p> </li> <li> <p>(Optional) You can also test the view by submitting the following SQL statement in SQL*Plus or SQL Developer:</p> <pre xml:space="preserve" class="oac_no_warn">SELECT * FROM king_view </pre></li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBCIEFC">"About Views"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBBCJGH" name="CBBBCJGH"></a><a id="ADMQS0844" name="ADMQS0844"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Deleting a View</h3> <p><a id="sthref542" name="sthref542"></a><a id="sthref543" name="sthref543"></a>If you no longer need a view, then you can delete it using Database Control.</p> <p>In this example, you delete the <code>hr.king_view</code> view that you created previously in <a href="#CBBFFJGA">"Example: Creating a View"</a>.</p> <a id="ADMQS12305" name="ADMQS12305"></a> <p class="subhead2">To delete the HR.KING_VIEW view:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click <span class="bold">Views</span>.</p> <p>The Views page appears.</p> </li> <li> <p>In the <span class="bold">Schema</span> field, enter <code><span class="codeinlinebold">hr</span></code>.</p> </li> <li> <p>In the <span class="bold">Object Name</span> field, enter <code><span class="codeinlinebold">king</span></code>.</p> <p>You can enter just the first few letters of the view name.</p> </li> <li> <p>Click <span class="bold">Go</span>.</p> <p><code>KING_VIEW</code> is displayed in the list of views.</p> </li> <li> <p>Select <code><span class="codeinlinebold">KING_VIEW</span></code>, and then click <span class="bold">Delete</span>.</p> <p>A Confirmation page appears.</p> </li> <li> <p>Click <span class="bold">Yes</span> to delete the view.</p> <p>The Views page returns and displays a confirmation message.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CBBCIEFC">"About Views"</a></p> </li> </ul> </div> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <a id="CHDHDBID" name="CHDHDBID"></a><a id="ADMQS085" name="ADMQS085"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Managing Program Code Stored in the Database</h2> <p>This section describes your responsibilities as a database administrator (DBA) for program code that is stored in the database. It contains the following topics:</p> <ul> <li> <p><a href="#CBBBDEBH">About Program Code Stored in the Database</a></p> </li> <li> <p><a href="#CBBGDCEF">Validating (Compiling) Invalid Schema Objects</a></p> </li> </ul> <a id="CBBBDEBH" name="CBBBDEBH"></a><a id="ADMQS12097" name="ADMQS12097"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">About Program Code Stored in the Database</h3> <p><a id="sthref544" name="sthref544"></a><a id="sthref545" name="sthref545"></a><a id="sthref546" name="sthref546"></a><a id="sthref547" name="sthref547"></a><a id="sthref548" name="sthref548"></a><a id="sthref549" name="sthref549"></a><a id="sthref550" name="sthref550"></a><a id="sthref551" name="sthref551"></a><a id="sthref552" name="sthref552"></a><a id="sthref553" name="sthref553"></a><a id="sthref554" name="sthref554"></a><a id="sthref555" name="sthref555"></a>Oracle Database offers the ability to store program code in the database. Developers write program code in PL/SQL or Java, and store the code in schema objects. You, as the DBA, can use Oracle Enterprise Manager Database Control (Database Control) to manage program code objects such as:</p> <ul> <li> <p>PL/SQL packages, procedures, functions, and triggers</p> </li> <li> <p>Java source code (Java sources) and compiled Java classes</p> </li> </ul> <p>The actions that you can perform include creating, compiling, creating synonyms for, granting privileges on, and showing dependencies for these code objects. You access administration pages for these objects by clicking links in the Programs section of the Schema subpage.</p> <p>Note that creating and managing program code objects is primarily the responsibility of application developers. However, as a DBA you might have to assist in managing these objects. Your most frequent task for program code objects might be to revalidate (compile) them, because they can become invalidated if the schema objects on which they depend change or are deleted.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> Other types of schema objects besides program code objects can become invalid. For example, if you delete a table, then any views that reference that table become invalid.</div> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink CNCPT036" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT036"><span class="italic">Oracle Database Concepts</span></a> for an overview of using PL/SQL and Java for server-side programming</p> </li> <li> <p><a class="olink TDDDG40000" href="http://www.oracle.com/pls/db112/lookup?id=TDDDG40000"><span class="italic">Oracle Database 2 Day Developer's Guide</span></a> for more information about PL/SQL procedures, functions, packages, and triggers</p> </li> <li> <p><a class="olink TDPJD" href="http://www.oracle.com/pls/db112/lookup?id=TDPJD"><span class="italic">Oracle Database 2 Day + Java Developer's Guide</span></a> for more information about Java sources and Java classes</p> </li> <li> <p><a class="olink LNPLS" href="http://www.oracle.com/pls/db112/lookup?id=LNPLS"><span class="italic">Oracle Database PL/SQL Language Reference</span></a> to learn about PL/SQL code</p> </li> <li> <p><a class="olink ADMIN11556" href="http://www.oracle.com/pls/db112/lookup?id=ADMIN11556"><span class="italic">Oracle Database Administrator's Guide</span></a> for more information about object invalidation</p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CBBGDCEF" name="CBBGDCEF"></a><a id="ADMQS12098" name="ADMQS12098"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Validating (Compiling) Invalid Schema Objects</h3> <p><a id="sthref556" name="sthref556"></a><a id="sthref557" name="sthref557"></a><a id="sthref558" name="sthref558"></a><a id="sthref559" name="sthref559"></a><a id="sthref560" name="sthref560"></a>As a database administrator (DBA), you may be asked to revalidate schema objects that have become invalid. Schema objects (such as triggers, procedures, or views) might be invalidated when changes are made to objects on which they depend. For example, if a PL/SQL procedure contains a query on a table and you modify table columns that are referenced in the query, then the PL/SQL procedure becomes invalid. You revalidate schema objects by compiling them.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> It is not always possible to revalidate a schema object that stores program code by compiling it. You may have to take remedial actions first. For example, if a view becomes invalid because a table that it references is deleted, then compiling the view produces an error message that indicates that the table does not exist. You cannot validate the view until you re-create the table or retrieve it from the recycle bin.</div> <p>Database Control notifies you when schema objects become invalid by displaying an alert in the Alerts section of the Database Home page.</p> <p>There are two ways to display schema objects that require validation: by following an alert on the Database Home page, or by viewing the appropriate object page (Views page, Procedures page, and so on) and searching for the objects.</p> <a id="ADMQS12306" name="ADMQS12306"></a> <p class="subhead2">To validate schema objects starting from an alert:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>In the Alerts section, search for alerts with the following message:</p> <pre xml:space="preserve" class="oac_no_warn"><span class="italic">n</span> object(s) are invalid in the <span class="italic">schema_name</span> schema. </pre> <p>An example of such a message is the following:</p> <pre xml:space="preserve" class="oac_no_warn">4 object(s) are invalid in the HR schema. </pre> <p>There is a separate message for each schema that contains invalid objects.</p> <img width="944" height="157" src="img/invalid_object_alerts.gif" alt="Description of invalid_object_alerts.gif follows" title="Description of invalid_object_alerts.gif follows" longdesc="img_text/invalid_object_alerts.htm" /><br /> <a id="sthref561" name="sthref561" href="img_text/invalid_object_alerts.htm">Description of the illustration invalid_object_alerts.gif</a><br /> <br /></li> <li> <p>In the <span class="bold">Message</span> column, click an invalid object message.</p> <p>The Owner's Invalid Object Count page appears.</p> </li> <li> <p>Under Related Links, click <span class="bold">Invalid Objects Details</span>.</p> <p>The Invalid Object Details page appears, showing a list of invalid objects.</p> <img width="682" height="342" src="img/invalid_object_details.gif" alt="Description of invalid_object_details.gif follows" title="Description of invalid_object_details.gif follows" longdesc="img_text/invalid_object_details.htm" /><br /> <a id="sthref562" name="sthref562" href="img_text/invalid_object_details.htm">Description of the illustration invalid_object_details.gif</a><br /> <br /></li> <li> <p>Select one or more objects, and then click <span class="bold">Recompile</span>.</p> <p>A confirmation message appears, and the newly validated objects are removed from the list.</p> </li> </ol> <a id="ADMQS12307" name="ADMQS12307"></a> <p class="subhead2">To validate a schema object starting from an object page:</p> <ol> <li> <p>Go to the Database Home page, logging in as user <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Schema</span> to view the Schema subpage.</p> </li> <li> <p>In the Database Objects section, click the link for the object type to validate.</p> <p>For example, to validate a view, click <span class="bold">Views</span>.</p> </li> <li> <p>On the object page (for example, the Views page), enter a schema name and, optionally, an object name or partial object name, and then click <span class="bold">Go</span>.</p> <p>The schema objects are displayed.</p> </li> <li> <p>Select the schema object to validate.</p> </li> <li> <p>In the Actions list, select <span class="bold">Compile</span>, and then click <span class="bold">Go</span>.</p> <p>A confirmation message appears.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink CNCPT1859" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT1859"><span class="italic">Oracle Database Concepts</span></a> for more information about schema object dependencies</p> </li> <li> <p><a class="olink ADMIN11556" href="http://www.oracle.com/pls/db112/lookup?id=ADMIN11556"><span class="italic">Oracle Database Administrator's Guide</span></a> for information about managing object dependencies</p> </li> </ul> </div> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <a id="BGBBGEBF" name="BGBBGEBF"></a><a id="ADMQS086" name="ADMQS086"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Working with Other Schema Objects</h2> <p>You can manage other schema objects with Oracle Enterprise Manager Database Control (Database Control), including the following:</p> <ul> <li> <p>Seq<a id="sthref563" name="sthref563"></a><a id="sthref564" name="sthref564"></a>uences</p> <p>A <span class="bold">sequence</span> is a database object that generates unique integers. Each time that you query the sequence, it increments its current value by a designated amount and returns the resulting integer. Sequences can be simultaneously queried by multiple users, and each user receives a unique value. For this reason, using a sequence to provide the value for a primary key in a table is an easy way to guarantee that the key value is unique, regardless of the number of users inserting data into the table.</p> </li> <li> <p>Syn<a id="sthref565" name="sthref565"></a><a id="sthref566" name="sthref566"></a>onyms</p> <p>A <span class="bold">synonym</span> is an alias for any schema object, such as a table or view. Synonyms provide an easy way to hide the underlying database structure from an application or a user. Synonyms can be private or public. A public synonym does not have to be qualified with a schema name, whereas a private synonym does, if the user referencing the private synonym is not the synonym owner. For example, consider the following query, issued by a user who has been granted the <code>SELECT</code> object privilege on the <code>hr.employees</code> table:</p> <pre xml:space="preserve" class="oac_no_warn">SELECT employee_id, salary FROM hr.employees ORDER BY salary </pre> <p>Now suppose you create a public synonym named <code>personnel</code> as an alias for the <code>hr.employees</code> table, and you grant the <code>SELECT</code> privilege on the <code>hr.employees</code> table to <code>PUBLIC</code> (all database users). With the public synonym in place, any user can issue the following simpler query:</p> <pre xml:space="preserve" class="oac_no_warn">SELECT employee_id, salary FROM personnel ORDER BY salary </pre> <p>The user who created this query did not need to know the name of the schema that contains the personnel data.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> If a user owns a table named <code>personnel</code>, then that table is used in the query. If no such table exists, then the database then resolves the public synonym and uses the <code>hr.employees</code> table.</div> <p>An additional benefit of synonyms is that you can use the same synonym in a development database as in the production database, even if the schema names are different. This technique enables application code to run unmodified in both environments. For example, the preceding query would run without errors in a development database that had the <code>employees</code> table in the <code>dev1</code> schema, if the <code>personnel</code> synonym is defined in the development database to point to the <code>dev1</code> schema.</p> <p><a id="sthref567" name="sthref567"></a><a id="sthref568" name="sthref568"></a>Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary. To reference a synonym in a query, you must have privileges on the object to which it points. Synonyms themselves cannot be secured. If you grant object privileges on a synonym to a user, then you are granting privileges on the object to which the synonym points.</p> </li> <li> <p>Dat<a id="sthref569" name="sthref569"></a><a id="sthref570" name="sthref570"></a>abase links</p> <p>A <span class="bold">database link</span> is a schema object that points to another Oracle database. You use a database link to query or update objects in a remote database. Database links are used in distributed database environments, which are described in <a class="olink ADMIN028" href="http://www.oracle.com/pls/db112/lookup?id=ADMIN028"><span class="italic">Oracle Database Administrator's Guide</span></a>.</p> </li> </ul> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink TDDDG34000" href="http://www.oracle.com/pls/db112/lookup?id=TDDDG34000"><span class="italic">Oracle Database 2 Day Developer's Guide</span></a> for more information about sequences</p> </li> <li> <p><a class="olink SQLRF" href="http://www.oracle.com/pls/db112/lookup?id=SQLRF"><span class="italic">Oracle Database SQL Language Reference</span></a></p> </li> </ul> </div> </div> <!-- class="sect1" --> <a id="CHDJGIIH" name="CHDJGIIH"></a><a id="ADMQS087" name="ADMQS087"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Schemas: Oracle By Example Series</h2> <p>Oracle By Example (OBE) has a series on the <span class="italic">Oracle Database 2 Day DBA</span> guide. This OBE steps you through the tasks in this chapter and includes annotated screenshots.</p> <p>To view the Schemas OBE, in your browser, enter the following URL:</p> <p><code><a href="http://www.oracle.com/technology/obe/11gr2_2day_dba/schema/schema.htm">http://www.oracle.com/technology/obe/11gr2_2day_dba/schema/schema.htm</a></code></p> </div> <!-- class="sect1" --></div> <!-- class="ind" --> <div class="footer"> <hr /> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="33%" /> <col width="*" /> <col width="33%" /> <tr> <td align="left"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="98"> <tr> <td align="center" valign="top"><a href="users_secure.htm"><img width="24" height="24" src="../../dcommon/gifs/leftnav.gif" alt="Go to previous page" /><br /> <span class="icon">Previous</span></a></td> <td align="center" valign="top"><a href="backrest.htm"><img width="24" height="24" src="../../dcommon/gifs/rightnav.gif" alt="Go to next page" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </td> <td style="font-size: 90%" align="center" class="copyrightlogo"><img width="144" height="18" src="../../dcommon/gifs/oracle.gif" alt="Oracle" /><br /> Copyright © 2004, 2009, Oracle and/or its affiliates. All rights reserved.<br /> <a href="../../dcommon/html/cpyr.htm">Legal Notices</a></td> <td align="right"> <table class="icons oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="245"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img width="24" height="24" src="../../dcommon/gifs/doclib.gif" alt="Go to Documentation Home" /><br /> <span class="icon">Home</span></a></td> <td align="center" valign="top"><a href="../../nav/portal_booklist.htm"><img width="24" height="24" src="../../dcommon/gifs/booklist.gif" alt="Go to Book List" /><br /> <span class="icon">Book List</span></a></td> <td align="center" valign="top"><a href="toc.htm"><img width="24" height="24" src="../../dcommon/gifs/toc.gif" alt="Go to Table of Contents" /><br /> <span class="icon">Contents</span></a></td> <td align="center" valign="top"><a href="index.htm"><img width="24" height="24" src="../../dcommon/gifs/index.gif" alt="Go to Index" /><br /> <span class="icon">Index</span></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><img width="24" height="24" src="../../dcommon/gifs/feedbck2.gif" alt="Go to Feedback page" /><br /> <span class="icon">Contact Us</span></a></td> </tr> </table> </td> </tr> </table> </div> <noscript> <p>Scripting on this page enhances content navigation, but does not change the content in any way.</p> </noscript> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de