Working with tables in Oracle 10g

Learning objective

After completing this topic, you should be able to create, delete, and modify tables in an Oracle 10g database.

Exercise overview

In this exercise, you're required to create and modify a table in an Oracle 10g database.

This involves the following tasks:

• creating a table
• defining columns
• modifying a table

Task 1: Creating a table

Suppose you want to create a table in the Order Entry (OE) schema.

You've navigated to the Tables page in Enterprise Manager.

Create a table called OBSOLETE_PRODUCTS in the OE schema, specifying the default table organization.

Steps list

Instructions
1. Click Create
2. Click Continue
3. Type OBSOLETE_PRODUCTS in the Name field
4. Type OE in the Schema field

Task 2: Defining columns

You scroll to the Columns section, where you want to create columns for the OBSOLETE_PRODUCTS table, and specify their attributes.

Create two columns in the new OBSOLETE_PRODUCTS table. The first should be called PRODUCT_ID and have the data type NUMBER, and the size 5. The second column should be called PRODUCT_NAME and should have the data type VARCHAR2, the size 50, and a NOT NULL constraint. Finally, confirm the changes.

Steps list

Instructions
1. Type PRODUCT_ID in the first Name field
2. Select NUMBER from the Data Type drop-down list
3. Type 5 in the Size field
4. Type PRODUCT_NAME in the 2nd name field
5. Type 50 in the Size field
6. Select the Not NULL checkbox
7. Click OK.

Task 3: Modifying a table

You now want to add a column to the OBSOLETE_PRODUCTS table.

To modify the table, you return to the Tables page, and search for the tables in the OE schema.

Add a column called OBSOLETED with data type DATE and size 8 to the OBSOLETE_PRODUCTS table.

Steps list

Instructions
1. Select the OBSOLETE_PRODUCTS option, and click Edit
2. Select Add 5 Table Columns
3. Type OBSOLETED in the Name field
4. Select DATE from the Data Type drop-down list
5. Type 8 in the Size field
6. Click Apply