| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
|
|
View PDF |
| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
|
|
View PDF |
The following sections discuss database tables and how to create and modify them:
Tables are the basic unit of data storage in an Oracle database. They hold all user-accessible data. Each table has columns and rows. In an employees table, for example, there can be columns called last_name and employee_id. Each row in the table would contain a value for employee name and number.
The most common type of table in an Oracle database is a relational table, structured like the employees table. Two other types of table are supported: object tables and XMLType tables. Any of the three table types can be defined as permanent or temporary. Temporary tables can be made available to multiple users or only to the user who creates the table.
You can build relational tables in either heap or index-organized structures. In the former, the rows are not stored in any particular order. In index-organized tables, the row order is determined by the values in one of more selected columns.
This section concentrates on permanent, heap-organized tables. For information about other table types and when to use them, see the Oracle Database Administrator's Guide, Oracle Database Concepts, and the Oracle Database Performance Tuning Guide. For the syntax required to create and alter tables, see the Oracle Database SQL Reference.
Each column in a table is defined with one or more characteristics as follows:
Data Type
This required attribute defines the nature of the data to be stored in the column and can indicate the longest value that can be placed in the column.
Constraint
This optional attribute determines valid values for the column. Some constraints can be defined at the column level or at the table level. In Enterprise Manager, the only constraint defined at the column level on the Create Table property page is the NOT NULL constraint, which requires that a value is included in the column for every row in the 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.
These column characteristics are described in more detail in the following sections.
When you create a table, you must specify a data type for each of its columns. When you create a procedure or stored function, you must specify a datatype for each of its arguments.
These data types define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or SHOE. Each value subsequently placed in a column assumes the column datatype. For example, if you insert 17-JAN-2004 into a date column, then Oracle treats that character string as a date value after verifying that it translates to a valid date.
Table 8-1 lists some common Oracle built-in datatypes.
Table 8-1 Common Datatypes
| Datatype | Description |
|---|---|
|
Variable-length character string having maximum length
|
|
|
Number having precision |
|
|
Valid date range from January 1, 4712 BC to December 31, 9999 AD. |
|
|
A character large object 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). |
In most tables, you should only need columns of NUMBER, VARCHAR2, and DATE datatypes.
When defining numeric data, you can use the precision option to set the maximum number of digits in the number, and the scale option to define 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 NUMBER(12,2), providing ten 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).
To define a VARCHAR2 field for character data, you must include the size value. Set the size to the maximum number of bytes or characters to be stored in the column. A column to hold postal codes for different countries, for example, might be restricted to 12 bytes by defining it as VARCHAR2(12).
DATE columns are automatically formatted by Oracle to include a date and time component.
The NOT NULL constraint on a column requires that the column must contain a value whenever a row is inserted or updated. Unlike other constraints described in "Table-Level Constraints", which may 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 the data is required 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 should be defined with a NOT NULL constraint. On the other hand, do not define a column as NOT NULL if the data may be unknown or may not exist when rows are added or changed, for example, the second, optional line in a mailing address.
A primary key constraint automatically adds a NOT NULL constraint to the column or columns included in the primary key.
In an Oracle database, you can apply rules to preserve the integrity of your data. For example, in a table containing 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 to tables at the column level or, with the exception of the NOT NULL constraint, at the table level. Any attempt 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 row violates the new constraint.
The types of constraints 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 every value in a column be unique. That is, no two rows can have duplicate values in a specified column or combination of columns. The set of columns is said to be the 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 that is evaluated using the column value about to be inserted or updated to the row.
Foreign—Requires that 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 is called the parent table. An example of a foreign key constraint is when 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.
When you create a table in a locally managed tablespace and use automatic segment space management, you should allow Oracle to build the table using its own storage algorithms. If you need to store a table in a dictionary-managed tablespace, then you can set the storage characteristics for the table.
The preconfigured database you installed does not contain dictionary-managed tablespaces. Therefore, these options are not discussed here but are described in the Oracle Database Administrator's Guide and Oracle Database Concepts manual if you need to use them.
When creating a table with the Create Table page, you can click the Show SQL button to display the SQL statement that has been constructed. This option shows the statement even if it is incomplete, so you need to complete your input to see the SQL statement when using this option.
You can also create a new table with the same the structure and contents as an existing table. This technique can be useful to build a test table that resembles a production table. To do this, select SQL in the Define Using list and complete the statement that is begun for you in the CREATE TABLE AS box. Your query can include all columns and rows from the original table, or you can use the SQL SELECT statement to identify which rows you want, rename columns with column aliases, or exclude certain columns.
Your new table may include a column defined with an abstract data type (ADT). ADTs are created with the CREATE TYPE statement and are described in detail in the Oracle Database SQL Reference.
If you are creating a table containing one or more LOB columns, then you may define different storage options for the LOB components of the table. In most cases, you should use the default settings. If you want to override them, click Set Default LOB Attributes and complete the entries in the Set Default LOB Attributes page. Additionally, you can set LOB attributes for a single column by selecting the column name and clicking Advanced Attributes.
You may also create tables with multiple partitions. Each partition is a separate database object with many of the characteristics of an individual table, although each partition contains the same column definitions as its parent table. Partitioning a very large table can simplify the management of the table, because each partition can be configured independently of the other partitions. Also, some operations against a partitioned table may be more efficient than against the table if it were not partitioned. This is because the partitions may be spread across more disk drives than a non-partitioned table and because the optimizer may be able to avoid processing the contents of every partition when processing certain SQL statements.
For some applications, particularly data warehousing, with large tables that are frequently queried but very rarely updated, you may create compressed tables. These require less disk storage than uncompressed tables (which are the default). To create a compressed table, you will need to write your own CREATE TABLE statement because the option is not available in Enterprise Manager.
|
See Also: Oracle Database Concepts and Oracle Database Administrator's Guide for design and management considerations of different table types and Oracle Database SQL Reference for the syntax to create tables |
You can use Enterprise Manager to view all of the tables in a specified schema. You can also view the table definitions.
To view tables:
In the Administration page, click Tables.
The Tables property page appears.
In the Schema box, enter the name of a schema. For example, enter SYS. Alternatively, click the flashlight icon adjacent to the Schema box to search for a schema.
Leave Object Name blank to search for all tables in a schema. Alternatively, enter an object name or click the flashlight icon to search for one.
Click Go.
The tables in the specified schema are displayed. Some of the tables in the SYS schema are shown in Figure 8-3.
In the results list, select a table. Click View or click the link in the Table Name column to view a table definition.
The View Table: table_name page appears.
Besides viewing table names and table definitions, you can view the data stored in the table as well as 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 explained in "Viewing Tables". For example, search for the tables in the hr schema.
In the results list, select the table whose data you want to view. For example, select EMPLOYEES.
In the Actions list, select View Data.
Click Go.
The View Data for Table: table_name page appears. Part of the page for hr.employees is shown in Figure 8-4. The Query box displays the SQL query that was executed in order to view the data for the table. The Result section shows the data in the table.
Click a column name to sort the data in a column.
Click Refine Query to change the query.
The Refine Query for Table: table_name page appears. This page enables you to select the columns to display. It also enables you to specify a WHERE clause for the SQL statement to limit the results.
You can also write your own SQL query using a SELECT statement to see the contents of a table. You can execute SQL statements by starting an iSQL*Plus session by clicking iSQL*Plus in the Related Links section of any of the Database property pages. A detailed description of the SELECT statement and its options is contained in the Oracle Database SQL Reference.
You can use Enterprise Manager to create tables. You can do so as a database administrator or user with the CONNECT role.
During table creation with Enterprise Manager, you can estimate the size of the table. This action enables you to determine whether you have sufficient room in your database or on the available disks to store the table. If you do not have room, then you can still create the table but not populate it until you have obtained the necessary storage.
To estimate the table size:
In the Database Objects section of the Administration page, click Tables.
The Tables page appears.
Click Create.
The Create Table: Table Organization page appears.
Select Standard, Heap Organized and click Continue.
The Create Table page appears.
Enter the table and schema names as well as your column names and datatypes, then click Estimate Table Size.
The Estimate Table Size page appears.
In Projected Row Count, enter the projected number of rows in the table and click Estimate Table Size
Enterprise Manager returns its estimate in MB.
In the following example, you create a table called purchase_orders in the MYUSER schema that you created in Chapter 7, "Administering Users and Security". The table will have columns po_number, po_description, po_date, and po_item.
To create a table in the MYUSER schema:
In the Database Objects section of the Administration page, click Tables.
The Tables page appears.
Click Create.
The Create Table: Table Organization page appears.
Select Standard, Heap Organized.
Click Continue.
The Create Table page appears. See Figure 8-5, "Create Table Page".
In the Name box, enter purchase_orders as the table name. In the Schema box, enter MYUSER. In the Tablespace box, accept Default.
In the Columns section, enter the following columns with the datatypes:
po_id as NUMBER
po_date as DATE
po_desc as VARCHAR2(200)
po_item as VARCHAR2(100)
You can leave Scale, Not NULL, and Default Value blank.
|
Note: If you want to the create table with partitions, you can do so at this step by clicking Partitions. |
Click OK.
An update message appears indicating that the table has been successfully created.
You can use Enterprise Manager to add and delete columns or add constraints. The following sections show how to add a column, and how to add and drop a table constraint.
In this example, you modify the purchase_orders table by adding a new column called po_item2.
To add the po_item2 column:
On the Tables page, enter MYUSER in the Schema box and click Go.
The result list shows the PURCHASE_ORDERS table that you created previously.
Select the PURCHASE_ORDERS table and click Edit.
The Edit Table: MYUSER.PURCHASE_ORDERS page appears.
In the first available Name box that is blank, enter po_item2 as the new column name. In the Data Type list, select VARCHAR2. In the Size box, enter 100. You can leave Scale, Not NULL, and Default Value blank.
Click Apply.
An Update Message appears indicating that the table has been modified successfully.
This example shows you how to drop a column, in this case, the po_item2 column that was added to the PURCHASE_ORDERS table in "Adding a Column".
To delete the po_item2 column:
On the Tables page, enter MYUSER in the Schema box and click Go.
The result list should shows the PURCHASE_ORDERS table that you created previously.
Select the PURCHASE_ORDERS table and click Edit.
The Edit Table: MYUSER.PURCHASE_ORDERS page appears.
In the Columns section, select the column you want to delete. In this case, select po_item2.
Click Delete.
The row that contained the information for the deleted column, po_item2, becomes blank to indicate that the table has been successfully modified.
You can add constraints to a table with Enterprise Manager. In the current example, assume that you want to enforce the rules that purchase orders are always entered with an ID and that the purchase order ID (po_id) is the primary key of the table. You will add the NOT NULL and primary key constraints to this column.
|
Note: You can also add constraints during table creation. |
To add these constraints to the purchase_orders table:
On the Tables page, enter MYUSER in the Schema box and click Go.
The result list shows the PURCHASE_ORDERS table that you created previously.
Select the PURCHASE_ORDERS table and click Edit.
The Edit Table: MYUSER.PURCHASE_ORDERS page appears.
Select the po_id column and check Not NULL to apply the NOT NULL constraint to the column.
Click Apply
A confirmation message appears.
Click Constraints.
The Constraints property page appears.
Select PRIMARY from the drop-down menu and click Add. This action applies the primary key constraint.
The Add Primary Constraint page appears.
In the Available Columns list, select PO_ID and move it to the Selected Columns list. To keep the default settings for the constraints, do not change the check boxes in the Attributes area of the page.
Click Continue
The new constraint appears on the Constraints page.
Click Apply.
A confirmation message appears. You can view the new constraints by navigating to the View Table page.
You can change the status of an existing constraint, for example, from an enabled to a disabled state. This example uses the primary key constraint created in the previous section, "Creating a New Table Constraint".
To disable a constraint:
On the Tables page, enter MYUSER in the Schema box and click Go.
The result list should shows the PURCHASE_ORDERS table that you created previously.
Select the PURCHASE_ORDERS table and click Edit.
The Edit Table: MYUSER.PURCHASE_ORDERS page appears.
Click Constraints.
The Constraints property page appears.
Select the row containing the PRIMARY constraint from the Constraints table and click Edit.
The Edit PRIMARY Constraint page appears.
In the Attributes section, check Disabled.
Click Continue.
Click Apply.
A confirmation message appears. The Disabled column shows that the primary key constraint has been disabled.
You can drop constraints from a table with Enterprise Manager. Although you do not have to disable a constraint before dropping it, you can determine whether the constraint can be dropped by attempting to disable it first. If a constraint in a parent table enforces a foreign key constraint in a child table, and if the child table contains dependent rows, then the constraint cannot always be disabled or dropped.
Continuing the current example, you drop the primary key constraint that you created in the section, "Creating a New Table Constraint".
To drop a constraint:
On the Tables page, enter MYUSER in the Schema box and click Go.
The result list should shows the PURCHASE_ORDERS table that you created previously.
Select the PURCHASE_ORDERS table and click Edit.
The Edit Table: MYUSER.PURCHASE_ORDERS page appears.
Click Constraints.
The Constraints property page appears.
Select the row containing the PRIMARY constraint from the Constraints table and click Delete.
Click Apply.
A confirmation message appears.
You can use Enterprise Manager to load data into tables in batch. Batch loading is useful when you have a lot of data. You can load data from the file system. You can also export data into files.
For loading data with Enterprise Manager, you can use a comma-delimited data file. Enterprise Manager can also create your control file automatically. This SQL*Loader control file tells Oracle how the data is to be loaded. It is different from a database control file.
The following example loads data into the purchase_orders table we created with MYUSER in "Creating a Table: Example". For simplicity, this example loads only three rows. Your control and data files can be different, but the idea is the same for this method of loading.
This example requires you to create a data file named load4.dat on the file system of the database host or on your client host. The contents of the file should be as follows:
1, 25-MAY-2005, Office Equipment, Optical Mouse 2, 18-JUN-2005, Computer System, Dell Laptop 3, 26-JUN-2005, Travel Expense, Car Rental
To load the data file into the myuser.purchase_orders table:
From the Move Row Data section of the Maintenance page, click Load Data from User Files.
The Load Data: Generate Or Use Existing Control File page appears.
Select Automatically Generate Control File. Also enter the Host Credentials (username and password) for the host machine. Click Continue.
The Load Data: Data Files page appears.
Select Data File is Located on Database Server Machine and enter the path to your data file. Alternatively, if you created the data file on your client host, select Data File is Located on Your Browser Machine and enter the file path. Click Next.
The Load Data: Table and File Format page appears. This page displays the contents of your data file.
In Table Name, specify the destination table for your data. Enter myuser.purchase_orders and click Next.
The Load Data: File Format Attributes page appears.
Accept all defaults. In this example, we use the comma character as the delimiter. Click Next.
The Load Data: Load Method page appears.
Accept the default Conventional Path as the loading method. If you have a lot of data, you can select Direct Path. Click Next.
The Load Data: Options page appears.
In the Optional Files section, leave Generate log file where logging information is to be stored checked. You can accept the default file name and path or enter a different one. Note that this page gives you the option of limiting the number of rows loaded. Because we are only loading three rows in this example, we leave this box unchecked. Click Next.
The Load Data: Schedule page appears.
Enter values for Job Name and Description. Select Immediately to run the job now. Click Next.
The Load Data: Review page appears. This page lets you review your file names and loading methods. You can also view your loading control file. If you want to change something, you can click the Back button.
Click Submit Job to start the loading.
The Job Activity page appears, confirming that your job was created successfully.
Search for your job name to view its status.
The Job Activity page should display your job information and show that your job succeeded. You can get more information by clicking the link under Status.
You can confirm the loaded data by navigating to the Tables page, searching for the myuser.purchase_orders table, and choosing View Data as the action. See Figure 8-6, "View Data: MYUSER.PURCHASE_ORDERS".
Figure 8-6 View Data: MYUSER.PURCHASE_ORDERS
If you no longer need a table or its contents, then you can drop the table using Enterprise Manager. Be certain that you really do not need the data in the table before you drop it. It may be difficult and time-consuming to retrieve the records, if they can be retrieved, after you execute the drop statement.
To test this procedure, follow the procedure in "Creating Tables" to create a table named test in the MYUSER schema. Create the test table with one column called col of type VARCHAR2(1).
To drop a table:
On the Tables page, enter MYUSER in the Schema box and click Go.
The result list should show the test table that you just created.
Select the TEST table and click Delete With Options.
The Delete With Options page appears.
Select Delete the table definition, all its data, and dependent objects (DROP).
Check Delete all referential integrity constraints (CASCADE CONTRAINTS).
Click Yes.
The Edit table page displays a confirmation message when the table is successfully deleted.
