| Oracle® Database 2 Day DBA 11g Release 1 (11.1) Part Number B28301-01 |
|
|
View PDF |
| Oracle® Database 2 Day DBA 11g Release 1 (11.1) Part Number B28301-01 |
|
|
View PDF |
The following sections discuss database tables and how to create and modify them:
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:
The most common type of table in an Oracle database is a relational table, which is structured with simple columns similar to the employees table. Two other table types are supported: object tables and XMLType tables. Any of the three table types can be defined as permanent or temporary. Temporary tables hold session-private data that exists only for the duration of a transaction or session. They are useful in applications where a result set must be buffered, perhaps because the result set is constructed by running multiple operations.
You can build relational tables in either heap or index-organized 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.
This section describes permanent, heap-organized tables. For information about other table types and when to use them, see Oracle Database Administrator's Guide, Oracle Database Concepts, and Oracle Database Performance Tuning Guide. For the syntax required to create and alter tables with SQL, see Oracle Database SQL Language Reference.
You define table columns to hold your data. When you create a column, you specify the following attributes:
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.
Table 8-1 Common Data Types
| Data Type | Description |
|---|---|
|
Variable-length character string having a maximum length of You can use the See Oracle Database Globalization Support Guide for more information. |
|
|
Number having precision |
|
|
A composite value that includes both a date and time component. For each |
|
|
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. Maximum size is (4 gigabytes - 1) * (database block size). For example, for a block size of 32K, the maximum CLOB size is 128 terabytes. |
See Also:
Oracle Database SQL Language Reference for a complete list of Oracle Database built-in data types, and for details about date formatting and the TO_DATE function
Oracle Database Globalization Support Guide for more information about NLS_DATE_FORMAT and NLS_TERRITORY
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. On the other hand, 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.
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.
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.
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 NULL as a value. Similarly, in this table, you cannot have two employees with the same ID.
Oracle Database enables you to apply data integrity rules called constraints, 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.
The types of constraints that you can apply at the table level are as follows:
Primary Key—Requires that a column (or combination of columns) be the unique identifier of the row. A primary key column does not allow NULL values.
Unique Key—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.
Check—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: SALARY > 0.
Foreign Key—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 child table. The table that is referenced by the foreign key is called the parent 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.
Constraints can be created and, in most cases, 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.
See Also:
Oracle Database Concepts for more information about constraints
You can specify a number of 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.
This section describes some additional considerations for creating tables. It contains the following topics:
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 functionality such as intelligent data compression, deduplication and transparent encryption. The LOB implementation available in Oracle Database 10g Release 2 and prior versions is still supported for backward compatibility reasons and is now referred as BasicFiles. If you add a LOB column to a table, you can specify whether it should be created as a SecureFile or BasicFile. If you do not specify the storage type, 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.
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 need 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 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.
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.
See Also:
Oracle Database Concepts and Oracle Database Administrator's Guide for design and management considerations for different table types
Oracle Database VLDB and Partitioning Guide for more information about partitioned tables and indexes
You can use Database Control to list all of the tables in a specified schema, and to view the definitions of individual tables.
To view tables:
Go to the Database Home page, logging in as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Tables.
The Tables page appears.
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.
Leave Object Name blank to search for and display all tables in the schema. Alternatively, enter a table name or partial table name to limit the search.
If you enter a search string in Object Name, all tables that have names that start with the search string are displayed. If you precede the search string with an asterisk (*), all tables that have the search string anywhere in the table name are displayed.
Click Go.
The tables in the specified schema are displayed.
To view the definition of a particular table, select the table and then click View. Alternatively, click the table name.
The View Table page appears.
See Also:
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 result set.
To view table data:
Search for a table as described in "Viewing Tables". For example, search for the tables in the hr schema.
Select the table that contains the data that you want to view.
For example, select employees.
In the Actions list, select View Data, and then click Go.
The View Data for Table page appears.
The Query 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.
(Optional) Click a column name to sort the data by that column.
(Optional) Click Refine Query to change the query and redisplay the data.
The Refine Query for Table page appears. This page enables you to select the columns to display. It also enables you to specify a WHERE clause for the SQL SELECT statement to limit the results.
You can also write and submit your own SQL SELECT 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 SQL Worksheet in the Related Links section of the Database Home page.
A detailed description of the SELECT statement is in Oracle Database SQL Language Reference.
See Also:
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.
In the following example, you create a table called purchase_orders in the nick schema that you created in Chapter 7, "Administering User Accounts and Security". The table has the following columns:
| Column Name | Data Type | Size | Not NULL |
|---|---|---|---|
PO_NUMBER |
NUMBER |
Yes | |
PO_DESCRIPTION |
VARCHAR2 |
200 | No |
PO_DATE |
DATE |
Yes | |
PO_VENDOR |
NUMBER |
Yes |
To create the PURCHASE_ORDERS table in the NICK schema:
Go to the Database Home page, logging in as user nick or as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Tables.
The Tables page appears.
Click Create.
The Create Table: Table Organization page appears.
Select Standard, Heap Organized, and then click Continue.
The Create Table page appears.
In the Name field, enter purchase_orders as the table name, and in the Schema field, enter nick.
Because a default tablespace was specified when creating the user nick in the section "Example: Creating a User Account", accept the default Tablespace setting for the table.
In the Columns section, enter column information for the purchase_orders table as specified in the table in the introduction to this topic. For example, for the first column in the purchase_orders table, enter the name PO_NUMBER and the data type NUMBER, and select the Not NULL check box.
For all purchase_orders columns, you can leave Scale and Default Value blank.
|
Note: If you want to create the table with partitions, you can do so during this step by clicking Partitions, at the top of the page. |
(Optional) Obtain an estimate of the table size by completing the following steps:
Click Estimate Table Size.
The Estimate Table Size page appears.
In the Projected Row Count field, enter 400000 (four hundred thousand), and then click Estimate Table Size.
The estimated results are calculated and displayed.
Click OK to return to the Create Table page.
The estimate of the table size can help you determine what values to use when specifying the storage parameters for the table.
Click Constraints to view the Constraints subpage, where you can designate a primary key for the table.
In the Constraints list, choose PRIMARY, then click Add.
The Add PRIMARY Constraint page appears.
In the Available Columns list, select PO_NUMBER, and then click Move.
Note:
You can also double-click
PO_NUMBER.The po_number column moves to the Selected Columns list.
Click Continue to return to the Constraints subpage of the Create Table page.
Click OK.
The Tables page returns, showing a confirmation message and listing the new table in the tables list. The purchase_orders table is now created with po_number as its primary key.
See Also:
You can use Database Control to add and delete table columns and to manage table constraints. This section contains the following topics:
See Also:
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:
Go to the Database Home page, logging in as user nick or as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Tables.
The Tables page appears.
In the Schema field, enter nick, and then click Go.
All tables owned by user nick are displayed.
Select the PURCHASE_ORDERS table, and then click Edit.
The Edit Table page appears.
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.
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.
Click Apply.
An update message appears indicating that the table has been modified successfully.
See Also:
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:
Go to the Database Home page, logging in as user nick or as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Tables.
The Tables page appears.
In the Schema field, enter nick and then click Go.
All tables owned by user nick are displayed.
Select the PURCHASE_ORDERS table, and then click Edit.
The Edit Table page appears.
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.
Click Apply.
An update message appears indicating that the table has been modified successfully.
See Also:
In this example, you add a table constraint to the purchase_orders table that you created in "Example: Creating a Table". Suppose you want 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. To do this, you add a check constraint.
|
Note: You can also add constraints during table creation, as shown in "Example: Creating a Table". In that example, you added a primary key constraint. |
To add a table constraint to the PURCHASE_ORDERS table:
Go to the Database Home page, logging in as user nick or as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Tables.
The Tables page appears.
In the Schema field, enter nick and then click Go.
All tables owned by user nick are displayed.
Select the PURCHASE_ORDERS table, and then click Edit.
The Edit Table page appears.
Click Constraints to display the Constraints subpage.
In the list adjacent to the Add button, select CHECK, and then click Add.
The Add CHECK Constraint page appears.
In the Name field, enter po_check_rcvd_date, overwriting the system-assigned default name.
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.
Click Continue
The new constraint appears on the Constraints subpage.
Click Apply.
A confirmation message appears.
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 in the PURCHASE_ORDERS table:
Go to the Database Home page, logging in as user nick or as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Tables.
The Tables page appears.
In the Schema field, enter nick and then click Go.
All tables owned by user nick are displayed.
Select the purchase_orders table, and then click Edit.
The Edit Table page appears.
Click Constraints to display the Constraints subpage.
Select the constraint named PO_CHECK_RCVD_DATE, and then click Edit.
The Edit CHECK Constraint page appears.
In the Attributes section, select Disabled, and then click Continue.
Click Apply.
A confirmation message appears. The Disabled column shows that the check constraint has been disabled.
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), 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:
Go to the Database Home page, logging in as user nick or as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Tables
The Tables page appears.
In the Schema field, enter NICK and then click Go.
All tables owned by user NICK are displayed.
Select the PURCHASE_ORDERS table, and then click Edit.
The Edit Table page appears.
Click Constraints to display the Constraints subpage.
Select the constraint named PO_CHECK_RCVD_DATE, and then click Delete.
The check constraint is removed from the list.
Click Apply.
A confirmation message appears.
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 data file.
SQL*Loader also uses a control file to control the loading of data from the data file. The control file 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 wizard that walks you through the steps of preparing and running a data load job with SQL*Loader. (A wizard is a multipage guided workflow.) The Load Data wizard can automatically create the SQL*Loader control file for you.
Note:
The SQL*Loader control file is unrelated to the database control files described in "About Control Files".In this example, you load data into the PURCHASE_ORDERS table that you created in "Example: Creating a Table". For simplicity, this example loads only three rows.
To prepare for this example, you must create a text file named load.dat 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:
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
Note:
This example assumes that the columns in thePURCHASE_ORDERS table are the following: PO_NUMBER, PO_DESCRIPTION, PO_DATE, PO_VENDOR, and PO_DATE_RECEIVED. If your PURCHASE_ORDERS table does not have all these columns (or has additional columns), modify the data in the text file accordingly.To load data into the PURCHASE_ORDERS table:
Go to the Database Home page, logging in as user SYSTEM.
At the top of the page, click Data Movement to display the Data Movement subpage.
In the Move Row Data section, click Load Data from User Files.
The Load Data: Generate Or Use Existing Control File page appears.
Select Automatically Generate Control File, and enter host computer credentials (user name and password) for the database host computer.
Click Continue.
The first page of the Load Data wizard appears. Its title is Load Data: Data Files.
Follow the steps in the wizard, clicking Next to proceed to each new step.
For information about using any of the wizard pages, click Help 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.
If the job succeeded, confirm that the data was successfully loaded by doing one of the following:
View the table data.
See "Viewing Table Data".
Examine the SQL*Loader log file, which is written to the host computer directory that you designated for the SQL*Loader data file.
Note:
If the job succeeds, 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.If the job failed, examine the SQL*Loader log file, correct any errors, and try again.
See Also:
Oracle Database Utilities for more information about SQL*Loader
If you no longer need a table, 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.
When you delete a table from a locally managed tablespace that is not the SYSTEM tablespace, the database does not immediately reclaim the space associated with the table. Instead, it places the table and any dependent objects in the recycle bin. 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 Recycle Bin on the Tables page. Note that users can see only tables that they own in the recycle bin. See Oracle Database Administrator's Guide for more information about the recycle bin, including how to view, purge, and recover tables for which you are not the owner.
To delete a table:
Search for the table that you want to delete, as explained in "Viewing Tables".
Select the table, and then click Delete With Options.
The Delete With Options page appears.
Select Delete the table definition, all its data, and dependent objects (DROP).
Select Delete all referential integrity constraints (CASCADE CONTRAINTS).
Click Yes.
The Tables page returns and displays a confirmation message.
See Also:
