2

Managing Schema Objects

Copyright 2006, Oracle. All rights reserved.

 Objectives

After completing this lesson, you should be able to do

the following:
Add constraints
Create indexes
Create indexes using the CREATE TABLE statement
Creating function-based indexes
Drop columns and set column UNUSED
Perform FLASHBACK operations
Create and use external tables

2-2 Copyright 2006, Oracle. All rights reserved.

 ALTER TABLE Statement

Use the ALTER TABLE statement to:

Add a new column
Modify an existing column
Define a default value for the new column
Drop a column

2-3 Copyright 2006, Oracle. All rights reserved.

 ALTER TABLE Statement

Use the ALTER TABLE statement to add, modify, or drop

columns:
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

DROP (column);

2-4 Copyright 2006, Oracle. All rights reserved.

 Adding a Column

You use the ADD clause to add columns:

ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
Table altered.
The new column becomes the last column:

2-5 Copyright 2006, Oracle. All rights reserved.

 Modifying a Column

You can change a columns data type, size, and

default value.
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
Table altered.
A change to the default value affects only
subsequent insertions to the table.

2-6 Copyright 2006, Oracle. All rights reserved.

 Dropping a Column

Use the DROP COLUMN clause to drop columns you no

longer need from the table:

ALTER TABLE dept80

DROP COLUMN job_id;
Table altered.

2-7 Copyright 2006, Oracle. All rights reserved.

 SET UNUSED Option

You use the SET UNUSED option to mark one or

more columns as unused.
You use the DROP UNUSED COLUMNS option to
remove the columns that are marked as unused.
ALTER TABLE <table_name>
SET UNUSED(<column_name>);
OR
ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name>;

ALTER TABLE <table_name>

DROP UNUSED COLUMNS;

2-8 Copyright 2006, Oracle. All rights reserved.

 Adding a Constraint Syntax

Use the ALTER TABLE statement to:

Add or drop a constraint, but not modify its
structure
Enable or disable constraints
Add a NOT NULL constraint by using the MODIFY
clause
ALTER TABLE <table_name>
ADD [CONSTRAINT <constraint_name>]
type (<column_name>);

2-10 Copyright 2006, Oracle. All rights reserved.

 Adding a Constraint

Add a FOREIGN KEY constraint to the EMP2 table

indicating that a manager must already exist as a valid
employee in the EMP2 table.
ALTER TABLE emp2
modify employee_id Primary Key;
Table altered.

ALTER TABLE emp2

ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id)
REFERENCES emp2(employee_id);
Table altered.

2-11 Copyright 2006, Oracle. All rights reserved.

 ON DELETE CASCADE

Delete child rows when a parent key is deleted:

ALTER TABLE Emp2 ADD CONSTRAINT emp_dt_fk

FOREIGN KEY (Department_id)
REFERENCES departments ON DELETE CASCADE);
Table altered.

2-12 Copyright 2006, Oracle. All rights reserved.

 Deferring Constraints

Constraints can have the following attributes:

DEFERRABLE or NOT DEFERRABLE
INITIALLY DEFERRED or INITIALLY IMMEDIATE
Deferring constraint on
ALTER TABLE dept2 creation
ADD CONSTRAINT dept2_id_pk
PRIMARY KEY (department_id)
DEFERRABLE INITIALLY DEFERRED

Changing a specific
SET CONSTRAINTS dept2_id_pk IMMEDIATE constraint attribute

ALTER SESSION Changing all constraints for a

session
SET CONSTRAINTS= IMMEDIATE

2-13 Copyright 2006, Oracle. All rights reserved.

 Dropping a Constraint

Remove the manager constraint from the EMP2

table:
ALTER TABLE emp2
DROP CONSTRAINT emp_mgr_fk;
Table altered.

Remove the PRIMARY KEY constraint on the DEPT2

table and drop the associated FOREIGN KEY
constraint on the EMP2.DEPARTMENT_ID column:

ALTER TABLE dept2

DROP PRIMARY KEY CASCADE;
Table altered.

2-14 Copyright 2006, Oracle. All rights reserved.

 Disabling Constraints

Execute the DISABLE clause of the ALTER TABLE

statement to deactivate an integrity constraint.
Apply the CASCADE option to disable dependent
integrity constraints.

ALTER TABLE emp2

DISABLE CONSTRAINT emp_dt_fk;
Table altered.

2-15 Copyright 2006, Oracle. All rights reserved.

 Enabling Constraints

Activate an integrity constraint currently disabled

in the table definition by using the ENABLE clause.
ALTER TABLE emp2
ENABLE CONSTRAINT emp_dt_fk;
Table altered.

A UNIQUE index is automatically created if you

enable a UNIQUE key or a PRIMARY KEY constraint.

2-16 Copyright 2006, Oracle. All rights reserved.

 Cascading Constraints

The CASCADE CONSTRAINTS clause is used along

with the DROP COLUMN clause.
The CASCADE CONSTRAINTS clause drops all
referential integrity constraints that refer to the
primary and unique keys defined on the dropped
columns.
The CASCADE CONSTRAINTS clause also drops all
multicolumn constraints defined on the dropped
columns.

2-18 Copyright 2006, Oracle. All rights reserved.

 Cascading Constraints

Example:

ALTER TABLE emp2

DROP COLUMN employee_id CASCADE CONSTRAINTS;
Table altered.

ALTER TABLE test1

DROP (pk, fk, col1) CASCADE CONSTRAINTS;
Table altered.

2-19 Copyright 2006, Oracle. All rights reserved.

 Overview of Indexes

Indexes are created:

Automatically
PRIMARY KEY creation
UNIQUE KEY creation
Manually
CREATE INDEX statement
CREATE TABLE statement

2-20 Copyright 2006, Oracle. All rights reserved.

 CREATE INDEX with the CREATE TABLE
Statement

CREATE TABLE NEW_EMP

(employee_id NUMBER(6)
PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON
NEW_EMP(employee_id)),
first_name VARCHAR2(20),
last_name VARCHAR2(25));
Table created.

SELECT INDEX_NAME, TABLE_NAME

FROM USER_INDEXES
WHERE TABLE_NAME = 'NEW_EMP';

2-21 Copyright 2006, Oracle. All rights reserved.

 Function-Based Indexes

A function-based index is based on expressions.

The index expression is built from table columns,
constants, SQL functions, and user-defined
functions.

CREATE INDEX upper_dept_name_idx

ON dept2(UPPER(department_name));

Index created.

SELECT *
FROM dept2
WHERE UPPER(department_name) = 'SALES';

2-23 Copyright 2006, Oracle. All rights reserved.

 Removing an Index

Remove an index from the data dictionary by using

the DROP INDEX command:
DROP INDEX index;

Remove the UPPER_DEPT_NAME_IDX index from

the data dictionary:
DROP INDEX upper_dept_name_idx;
Index dropped.
To drop an index, you must be the owner of the
index or have the DROP ANY INDEX privilege:

2-24 Copyright 2006, Oracle. All rights reserved.

 DROP TABLE PURGE

DROP TABLE dept80 PURGE;

2-25 Copyright 2006, Oracle. All rights reserved.

 FLASHBACK TABLE Statement

Repair tool for accidental table modifications

Restores a table to an earlier point in time
Benefits: Ease of use, availability, fast execution
Performed in place
Syntax:

FLASHBACK TABLE[schema.]table[,
[ schema.]table ]...
TO { TIMESTAMP | SCN } expr
[ { ENABLE | DISABLE } TRIGGERS ];

2-26 Copyright 2006, Oracle. All rights reserved.

 FLASHBACK TABLE Statement

DROP TABLE emp2;

Table dropped

SELECT original_name, operation, droptime,

FROM recyclebin;

FLASHBACK TABLE emp2 TO BEFORE DROP;

Flashback complete

2-27 Copyright 2006, Oracle. All rights reserved.

 External Tables

2-28 Copyright 2006, Oracle. All rights reserved.

 Creating a Directory for the External Table

Create a DIRECTORY object that corresponds to the

directory on the file system where the external data
source resides.

CREATE OR REPLACE DIRECTORY emp_dir

AS '//emp_dir';

GRANT READ ON DIRECTORY emp_dir TO hr;

2-30 Copyright 2006, Oracle. All rights reserved.

 Creating an External Table

CREATE TABLE <table_name>

( <col_name> <datatype>, )
ORGANIZATION EXTERNAL
(TYPE <access_driver_type>
DEFAULT DIRECTORY <directory_name>
ACCESS PARAMETERS
( ) )
LOCATION ('<location_specifier>') )
REJECT LIMIT [0 | <number> | UNLIMITED];

2-32 Copyright 2006, Oracle. All rights reserved.

 Creating an External Table by Using
ORACLE_LOADER
CREATE TABLE oldemp (
fname char(25), lname CHAR(25))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
FIELDS TERMINATED BY ','
(fname POSITION ( 1:20) CHAR,
lname POSITION (22:41) CHAR))
LOCATION ('emp.dat'))
PARALLEL 5
REJECT LIMIT 200;
Table created.

2-34 Copyright 2006, Oracle. All rights reserved.

 Querying External Tables

SELECT *
FROM oldemp

OLDEMP
emp.dat

2-36 Copyright 2006, Oracle. All rights reserved.

 Summary

In this lesson, you should have learned how to:

Add constraints
Create indexes
Create a primary key constraint using an index
Create indexes using the CREATE TABLE statement
Create function-based indexes
Drop columns and set column UNUSED
Perform FLASHBACK operations
Create and use external tables

2-37 Copyright 2006, Oracle. All rights reserved.

 Practice 2: Overview

This practice covers the following topics:

Altering tables
Adding columns
Dropping columns
Creating indexes
Creating external tables

2-38 Copyright 2006, Oracle. All rights reserved.