8 Managing Schema Objects

About Tables

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 employees table, for example, there are columns called last_name and employee_id. Each row in the table represents a different employee, and contains a value for last_name and employee_id.

When you create a table, you specify the table type, and define its columns and constraints. Constraints are rules that help preserve data integrity.

This section contains the following topics:

• About Table Types

• About Table Column Attributes

• About Table-Level Constraints

• Other Table Creation Considerations

Data Type

The data type attribute defines the kind of data to be stored in the column. When you create a table, you must specify a data type for each of its columns.

Data types define the domain of values that each column can contain. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or SHOE. Each value subsequently inserted in a column assumes the column data type. For example, if you insert 17-JAN-2004 into a date column, then Oracle Database treats that character string as a date value after verifying that it converts to a valid date.

Table 8-1 lists some common Oracle Database built-in data types.

NOT NULL Column Constraint

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 NOT NULL constraint, which requires that a value be included in the column whenever a row is inserted or updated. Unlike other constraints described in "About Table-Level Constraints", which can be defined as part of the column definition or part of the table definition, the NOT NULL constraint must be defined as part of the column definition.

Use a NOT NULL 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 NOT NULL constraint. However, do not define a column as NOT NULL 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 NOT NULL constraint is the second, optional line in a mailing address.

The database automatically adds a NOT NULL constraint to the column or columns included in the primary key of a table.

Default Value

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 Oracle Database SQL Language Reference for details.

Encryption

You can enable automatic encryption for column data. See the discussion of transparent data encryption in Oracle Database 2 Day + Security Guide for more information.

User-Defined Types and Large Objects (LOBs)

Your new table can include one or more columns defined with user-defined types. User-defined types 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 record that contains purchase order number, customer number, amount, and so on.) User-defined types are created with the CREATE TYPE statement and are described in detail in Oracle Database SQL Language Reference.

Large object (LOB) columns are used to contain unstructured data (such as text or streaming video), and can hold terabytes of information. In Oracle Database 11g, 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 10g 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.

When creating a table that contains one or more LOB columns, select the LOB column, then click Advanced Attributes 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 Set Default LOB Attributes.

Partitioned Tables and Indexes

You can partition 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 partitions. SQL queries and DML statements do not have to be modified to access partitioned tables and indexes. Partitioning is transparent to the application.

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 partition pruning) 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.

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.

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.

Physical Storage Attributes

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 Oracle Database Administrator's Guide and Oracle Database SQL Language Reference.

Compressed Tables

Table Compression is suitable for both OLTP applications and data warehousing applications. 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.

Example: Adding Table Columns

In this example, you add columns to the purchase_orders table that you created previously in "Example: Creating a Table". The two new columns are named po_date_received and po_requestor_name.

To add columns to the PURCHASE_ORDERS table:

1. Go to the Database Home page, logging in as user nick or as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Tables.

   The Tables page appears.

4. In the Schema field, enter nick, and then click Go.

   All tables owned by user nick are displayed.

5. Select the PURCHASE_ORDERS table, and then click Edit.

   The Edit Table page appears.

6. In the Columns section, in the first available row, enter the following information about the new po_date_received column:

   Field Name	Value
   Name	PO_DATE_RECEIVED
   Data Type	DATE

   
   

   You can leave Size, Scale, Not NULL, and Default Value blank.

7. In the next available row, enter the following information about the new po_requestor_name column:

   Field Name	Value
   Name	PO_REQUESTOR_NAME
   Data Type	VARCHAR2
   Size	40

   
   

   You can leave Scale, Not NULL, and Default Value blank.

8. Click Apply.

   An update message appears indicating that the table has been modified successfully.

Example: Deleting a Table Column

In this example, you delete the po_requestor_name column that you added to the purchase_orders table in "Example: Adding Table Columns".

To delete the PO_REQUESTOR_NAME column:

1. Go to the Database Home page, logging in as user nick or as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Tables.

   The Tables page appears.

4. In the Schema field, enter nick and then click Go.

   All tables owned by user nick are displayed.

5. Select the PURCHASE_ORDERS table, and then click Edit.

   The Edit Table page appears.

6. In the Columns section, select the PO_REQUESTOR_NAME column, and then click Delete.

   The row that contained the information for the deleted column becomes blank.

7. Click Apply.

   An update message appears indicating that the table has been modified successfully.

Example: Adding a New Table Constraint

In this example, you add a table constraint to the purchase_orders table that you created in "Example: Creating a Table". To enforce the rule that the po_date_received value must be either the same day as, or later than, the value of po_date, you add a check constraint.

To add a table constraint to the PURCHASE_ORDERS table:

1. Go to the Database Home page, logging in as user nick or as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Tables.

   The Tables page appears.

4. In the Schema field, enter nick and then click Go.

   All tables owned by user nick are displayed.

5. Select the PURCHASE_ORDERS table, and then click Edit.

   The Edit Table page appears.

6. Click Constraints to display the Constraints subpage.

   
   Description of the illustration constraints.gif
   
   

7. In the list adjacent to the Add button, select CHECK, and then click Add.

   The Add CHECK Constraint page appears.

8. In the Name field, enter po_check_rcvd_date, overwriting the system-assigned default name.

9. In the Check Condition field, enter the following:

   po_date_received >= po_date
   

   This expression indicates that po_date_received must be greater than or equal to po_date. For date columns, this is equivalent to stating that po_date_received must be on the same day as, or later than, po_date.

10. Click Continue

    The new constraint appears on the Constraints subpage.

11. Click Apply.

    A confirmation message appears.

Example: Modifying an Existing Table Constraint

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 purchase_orders table in "Example: Adding a New Table Constraint".

To disable a constraint for the PURCHASE_ORDERS table:

1. Go to the Database Home page, logging in as user nick or as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Tables.

   The Tables page appears.

4. In the Schema field, enter nick and then click Go.

   All tables owned by user nick are displayed.

5. Select the purchase_orders table, and then click Edit.

   The Edit Table page appears.

6. Click Constraints to display the Constraints subpage.

7. Select the constraint named PO_CHECK_RCVD_DATE, and then click Edit.

   The Edit CHECK Constraint page appears.

8. In the Attributes section, select Disabled, and then click Continue.

9. Click Apply.

   A confirmation message appears. The Disabled column shows that the check constraint has been disabled.

Example: Deleting a Table Constraint

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.

In this example, you delete the check constraint that you created for the purchase_orders table in "Example: Adding a New Table Constraint".

To delete a constraint from the PURCHASE_ORDERS table:

1. Go to the Database Home page, logging in as user nick or as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Tables

   The Tables page appears.

4. In the Schema field, enter NICK and then click Go.

   All tables owned by user NICK are displayed.

5. Select the PURCHASE_ORDERS table, and then click Edit.

   The Edit Table page appears.

6. Click Constraints to display the Constraints subpage.

7. Select the constraint named PO_CHECK_RCVD_DATE, and then click Delete.

   The check constraint is removed from the list.

8. Click Apply.

   A confirmation message appears.

About Indexes

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.

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.

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.

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.

The following topics provide more background information about indexes:

• Indexes and Performance

• Index Attributes

Viewing Indexes

You use the Indexes page of Database Control to view the indexes in your database.

To view indexes:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Indexes.

   The Indexes page appears.

4. In the Search By list, do one of the following:

   • Select Index Name to search for indexes by name.

     Every index has a system-assigned or user-assigned name.

   • Select Table Name to search for indexes that belong to a particular table.

5. In the Schema field, enter the name of a schema. Alternatively, click the flashlight icon adjacent to the Schema field to search for a schema.

6. Do one of the following:

   • 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.

     If you enter a search string in the Object Name 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.

   • If you are searching by table name, then enter a table name or partial table name in the Object Name field.

     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.

7. Click Go.

   The indexes in the specified schema are displayed.

8. To view the definition of a particular index, select the index and then click View. Alternatively, double-click the index name.

   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.

Example: Creating an Index

When you create an index, you specify one or more table columns to be indexed and the type of index to create.

In this example, you create a standard B-tree index on the SUPPLIER_ID column in the SH.PRODUCTS table. (The SH schema is part of the sample schemas.)

To create a supplier index on the SH.PRODUCTS table:

1. View the tables in the SH schema, by following the instructions in the section "Viewing Tables".

2. Select the PRODUCTS table.

3. In the Actions list, select Create Index, and then click Go.

   The Create Index page appears.

   
   Description of the illustration create_index.gif
   
   

4. Enter the following information:

   • In the Name field, enter PRODUCTS_SUPPLIER_IDX.

   • For the Tablespace field, accept the default value.

   • For Index Type, select Standard - B-tree.

   • In the Table Columns list, select the SUPPLIER_ID column by entering 1 in the Order column.

     If your index were to consist of multiple columns (a concatenated index), then you would enter 2 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.

   • For Sorting Order, accept the default selection of ASC (ascending).

5. Click OK to create the index.

   The Indexes page returns and displays a confirmation message. The new index is listed in the table of indexes.

Example: Deleting an Index

If you no longer need an index, then you can delete it using Database Control.

In this example, you delete the PRODUCTS_SUPPLIER_IDX index that you created previously on the SH.PRODUCTS table in "Example: Creating an Index".

To delete the supplier index on the SH.PRODUCTS table:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Indexes.

   The Indexes page appears.

4. In the Search By list, select Table Name.

5. In the Schema field, enter SH.

6. In the Object Name field, enter PROD.

   You can enter only the first few letters of the table name.

7. Click Go.

   All indexes on the PRODUCTS table are displayed.

8. Select the PRODUCTS_SUPPLIER_IDX index, and then click Delete.

   A confirmation page appears.

9. Click Yes to delete the index.

   The Indexes page returns and displays a confirmation message.

About Views

Views 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 base tables of the view.

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.

Many important views are in the SYS schema. There are two types: static data dictionary views and dynamic performance views. Complete descriptions of the views in the SYS schema are in Oracle Database Reference.

Static Data Dictionary Views

The data dictionary views are called static views 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.

Many data dictionary tables have three corresponding views:

• A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators.

  An example of a DBA_ view is DBA_TABLESPACES, which contains one row for each tablespace in the database.

• An ALL_ 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.

  An example of an ALL_ view is ALL_TABLES, which contains one row for every table for which the user has object privileges.

• A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

  An example of a USER_ view is USER_TABLES, which contains one row for every table owned by the user.

The columns in the DBA_, ALL_, and USER_ views are usually nearly identical.

Dynamic Performance Views

Dynamic performance views monitor ongoing database activity. They are available only to administrators. The names of dynamic performance views start with the characters V$. For this reason, these views are often referred to as V$ views.

An example of a V$ view is V$SGA, which returns the current sizes of various System Global Area (SGA) memory components.

Displaying Views

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.

To display views:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Views.

   The Views page appears.

4. In the Schema field, enter the name of a schema. Alternatively, click the flashlight icon adjacent to the Schema field to search for a schema.

   Examples of schema names include SYS and hr.

5. 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.

   If you enter a search string in the Object Name 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.

6. Click Go.

   The views in the specified schema are displayed.

7. To view the definition of a particular view, select the view and then click View. Alternatively, double-click the view name.

   The View page appears.

Example: Creating a View

In this example, you create a view named king_view, which uses the hr.employees table as its base table. (The hr 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 hr.employees table while providing a suitable presentation of relevant information for manager King.

To create the KING_VIEW view on the HR.EMPLOYEES table:

1. Go to the Database Home page, logging in as user hr or as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Views.

   The Views page appears.

4. Click Create.

   The Create View page appears.

5. Enter the following information:

   • In the Name field, enter king_view.

   • In the Schema field, enter hr.

   • In the Query Text field, enter the following SQL statement:

     SELECT * FROM hr.employees
     WHERE manager_id = 100
     

6. Click OK.

   The Views page returns and displays a confirmation message. The new view appears in the list of views.

To test the new KING_VIEW view:

1. On the Views page, select king_view and then select View Data from the Actions list.

2. Click Go.

   The View Data for View page appears. The data selected by the view appears in the Result section.

3. (Optional) You can also test the view by submitting the following SQL statement in SQL*Plus or SQL Developer:

   SELECT * FROM king_view
   

Example: Deleting a View

If you no longer need a view, then you can delete it using Database Control.

In this example, you delete the hr.king_view view that you created previously in "Example: Creating a View".

To delete the HR.KING_VIEW view:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click Views.

   The Views page appears.

4. In the Schema field, enter hr.

5. In the Object Name field, enter king.

   You can enter just the first few letters of the view name.

6. Click Go.

   KING_VIEW is displayed in the list of views.

7. Select KING_VIEW, and then click Delete.

   A Confirmation page appears.

8. Click Yes to delete the view.

   The Views page returns and displays a confirmation message.

About Program Code Stored in the Database

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:

• PL/SQL packages, procedures, functions, and triggers

• Java source code (Java sources) and compiled Java classes

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.

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.

Validating (Compiling) Invalid Schema Objects

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.

Database Control notifies you when schema objects become invalid by displaying an alert in the Alerts section of the Database Home page.

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.

To validate schema objects starting from an alert:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. In the Alerts section, search for alerts with the following message:

   n object(s) are invalid in the schema_name schema.
   

   An example of such a message is the following:

   4 object(s) are invalid in the HR schema.
   

   There is a separate message for each schema that contains invalid objects.

   
   Description of the illustration invalid_object_alerts.gif
   
   

3. In the Message column, click an invalid object message.

   The Owner's Invalid Object Count page appears.

4. Under Related Links, click Invalid Objects Details.

   The Invalid Object Details page appears, showing a list of invalid objects.

   
   Description of the illustration invalid_object_details.gif
   
   

5. Select one or more objects, and then click Recompile.

   A confirmation message appears, and the newly validated objects are removed from the list.

To validate a schema object starting from an object page:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click the link for the object type to validate.

   For example, to validate a view, click Views.

4. 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 Go.

   The schema objects are displayed.

5. Select the schema object to validate.

6. In the Actions list, select Compile, and then click Go.

   A confirmation message appears.