Learn Oracle


I have created this learn oracle "The step 10 process" Oracle Tutorial to help Database aspirants to accomplish their dream of working as Oracle Database administrator. I have tried to keep it simple and User friendly. Use NEXT and PREVIOUS Button to navaigate away and to a chapter. Since i myself had been a Database adminstrator trainer and has got real life experience of working as a Oracle DBA, I would welcome any sort of queries and doubts or modifications to webmaster@oracleonline.info.

Step 8 - Creating Table in Oracle

Learn Oracle - Creating Tables

Oracle DBA can use Enterprise Manager to create tables. Oracle DBA can do so as a system administrator or user with the CONNECT role.

Types of Table The most common type of table in an Oracle database is a relational table, structured like the employee table described at the beginning of this section, "Managing Tables" 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, the latter with the option of making the table structure available to multiple users or only to the user who creates the table.

Relational tables can be built in either heap or index organized structures. In the former, the rows are not stored in any particular order but in index organized tables, the row order is determined by the values in one of more columns which Oracle DBA select. This chapter 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, and for the syntax required to create and alter them, see the Oracle Database SQL Reference.

Column Attributes

Each column in a table is defined with one or more characteristics as follows:

Data type: a required element which defines the nature of the data to be stored in the column and may a value to indicate the longest value that can be place in the column.

Constraint: one or more optional elements that determine what values are valid in 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 this column for every row in the table.

Default value: A value that is automatically stored into 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. Data Types When Oracle DBA create a table, Oracle DBA must specify a datatype for each of its columns. When Oracle DBA create a procedure or stored function, Oracle DBA must specify a datatype for each of its arguments. These datatypes 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ís datatype. For example, if Oracle DBA insert '17-JAN-2004' into a date column, then Oracle treats the '17-JAN-2004' character string as a DATE value after verifying that it translates to a valid date.

In most tables, Oracle DBA should only need columns of NUMBER, VARCHAR2, and DATE datatypes. When defining numeric data, Oracle DBA may want to use the precision option, to set the maximum number of digits in the number, and the scale option, to define how many of them 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, pfenigs, and so on). To define a VARCHAR2 field for character data, Oracle DBA must include the size value.

Set the size to the maximum number of bytes (or, optionally, 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. Not Null Column Constraint The Not Null constraint on a column requires that column must contain a value whenever a row is inserted or updated. Unlike other constraints, described in "Table Level Constraints" on page 8-8, 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. Default Values When Oracle DBA define a column with a default value, any new rows inserted into the table will store the default value unless the row contains an alternate value for the column.

Table Level Constraints

In an Oracle database, Oracle DBA can apply rules to preserve the integrity of your data. For example, in a table containing employee data, the name column cannot accept NULL as a value. Similarly, in this table Oracle DBA cannot have two employees with the same ID. Oracle enables Oracle DBA to apply data integrity rules called constraints to tables at the column level or, with the exception of the Not Null constraint discussed earlier, 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 will also result 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 NULLs.

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 (child) must contain a department id that exists in the parent department table. Constraints can be created and, in most cases, modified with a number of different status values. The options include enabled or disabled, which determine if the constraint is checked when rows are added or modified, and deferred or immediate,

Table Creation Example

In the following example, we create a table called purchase_orders in the MYUSER schema . The table will have columns po_number, po_description, po_date, and po_item.

To create a table in MYUSERís schema, log in to Oracle Enterprise Manager as either SYS or MYUSER and follow these steps:

1. Under the Administration page, click Tables. The Tables page appears listing the tables in the MYUSER schema. Note if you logged in as SYS, Oracle DBA need to click the flashlight and select MYUSER to manage tables in this schema.

2. Click Create. The Create Table: Table Organization page appears.

3. Select the box marked Standard, Heap Organized. Click Continue.

4. The Create Table page appears.

5. In the Name field, enter purchase_orders as the table name. In the Schema field, accept the MYUSER schema, and in the Tablespace field, accept .

6. 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)

Oracle DBA can leave Scale, Not NULL, and Default Value blank.

7. Click OK. An update message appears indicating the table has been successfully created
Oracle Tutorial - BLOGS

STEP 8 - Next Topics
This step discusses managing tables, indexes, and other schema objects. Click on Oracle tuorial links below:
Managing TablesManaging Tables
Creating TablesCreating Tables
Modifying Table Attributes
Managing Indexes
Oracle Database - PODCASTS
More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Return to Learn Oracle

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info