learn-oracle


Learn Oracle



PREVIOUS CHAPTER | NEXT CHAPTER

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 - Modifying Table Attributes

Learn Oracle - Modifying Table Attributes

Oracle DBA can use Enterprise Manager to modify tables, such as adding and deleting columns or adding constraints. The following sections show how to add and delete a column, and how to add, change the status of, and drop a table constraint.

Adding a Column

In this example, we modify the PURCHASE_ORDERS table by adding a new column called po_item2. To add the new column:

1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.

2. In the Edit Table page, click Add 5 Table Columns. An editable columns list appears.

3. Enter po_item2 as VARCHAR2(100) as the new column. Oracle DBA can leave the Scale, Not Null, and Default Value blank. Click Apply.

4. An Update Message appears indicating that the table has been modified successfully.



Dropping a Column

This example shows Oracle DBA how to drop column, in this case, the po_item2 column that was added to the PURCHASE_ORDERS table in the previous section ("Adding a Column"). To delete the column:

1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.

2. In the Columns table on the Edit Table page, select the column Oracle DBA want to delete, po_item2.

3. Click Delete.

4. The row for that contained the information for the deleted row, po_item2, becomes blank to indicate that the table has been modified successfully.

Creating a New Table Constraint

Oracle DBA can add constraints to a table with Enterprise Manager. In our current example, we want to enforce that our purchase orders always be entered with an ID and that this column (po_id) be the primary key of the table. We will add the not null and primary key constraints to this column.

To add these constraints to the purchase_orders table, do the following:

1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.

2. To apply the Not Null constraint, check the Not Null box next to the po_id column. Click Apply.

3. To apply the primary key constraint, click the Constraints property page.

4. On the Constraints page, select PRIMARY from the drop-down menu, and click Add. The Add PRIMARY Constraint page appears.

5. In the available columns list, select po_id and move it to the selected columns list. Do not change the check boxes in the Attributes area of the page because so that the default settings for the constraint are retained.

6. Click OK. On the Edit Table page, click Apply. A confirmation message appears. Oracle DBA can view the new constraints by navigating to the View Table page.

Modifying an Existing Constraint

Oracle DBA can change the status of an existing constraint, for example, from an enabled to a disabled state. Using the primary key constraint created in the previous section, "Creating a New Table Constraint", here is an example of how to disable a constraint:

1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.

2. To locate the constraint to be disabled, click the Constraints property page.

3. On the Constraints page, select the row containing the PRIMARY constraint from the Constraints table, and click Edit.

4. Under the Attributes section of Edit PRIMARY Constraint window, select the Disabled box.

5. Click OK. On the Edit table page, click Apply. A confirmation message appears and the value in the Disabled column for the primary key constraint should be Yes.

Dropping a Constraint

Oracle DBA can drop constraints to a table with Enterprise Manager. Although Oracle DBA do not have to disable a constraint before dropping it, Oracle DBA can determine whether the constraint can be dropped by attempting to disable it first. When a constraint in a parent table is used to enforce a foreign key constraint in a child table, the constraint cannot always be disabled or dropped if the child table contains dependent rows. Continuing the current example, we drop the primary key constraint that we created earlier in the section, "Creating a New Table Constraint", by completing the following steps:

1. In the Tables page, in the MYUSER schema, select the PURCHASE_ORDERS table in the results list and click Edit.

2. To locate the constraint to be disabled, click the Constraints property page.

3. On the Constraints page, select the row containing the PRIMARY constraint from the Constraints table, and click Delete.

4. On the Edit table page, click Apply. A confirmation message appears.
Oracle Tutorial - BLOGS
PREVIOUS CHAPTER | NEXT CHAPTER

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 AttributesModifying Tables
Managing Indexes
ORACLE DBA - DISCUSSION FORUMS
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 | Oracle DBA

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