Oracle Tips by Burleson
Face it, no one designs perfect applications.
This means we sometimes have to change things. For tables, this means
adding, changing, or dropping columns; adding constraints; or even
deleting all of the rows from the table. Letís look at how to
accomplish these table changes using the functions of the Oracle8
ALTER TABLE command, which are to:
* Add a column.
* Add an integrity constraint.
* Add integrity constraints to object-type
* Add or modify index-only table
* Add or modify LOB columns.
* Add or modify object type, nested table
type, or varray type column for a table.
* Add, modify, split, move, drop, or truncate
* Add, modify, split, move, drop, or truncate
* Allow or disallow writes to a table.
* Change the rows per block of storage for a
* Drop a column.
* Enable, disable, or drop an integrity
constraint or trigger.
* Explicitly allocate an extent.
* Explicitly deallocate the unused space of a
* Modification of the degree of parallelism
for a table
* Modify the LOGGING/NOLOGGING attributes.
* Modify the real storage attributes of a
nonpartitioned table or the default attributes of a partitioned table.
* Modify the storage characteristics or other
* Physically move a table.
* Redefine a column (datatype, size, default
* Rename a table or a table partition.
In order to use the ALTER TABLE command in
Oracle, the following must be true:
* The table must be in your own schema, you
must have ALTER privilege on the table, or you must have ALTER ANY
TABLE system privilege.
* To use an object type in a column definition
when modifying a table, either that object must belong to the same
schema as the table being altered or you must have either the EXECUTE
ANY TYPE system or the EXECUTE schema object privilege for the object
* If you are using Trusted Oracle in DBMS MAC
mode, your DBMS label must match the tableís creation label or you
must satisfy one of the following criteria:
If the tableís creation label is higher than your DBMS label,
you must have READUP and WRITEUP system privileges.
If the tableís creation label is lower than your DBMS label,
you must have the WRITEDOWN system privilege.
If the tableís creation label and your DBMS label are not
comparable, you must have READUP, WRITEUP, and WRITEDOWN system
The syntax of the ALTER TABLE command for
Oracle is located in the SQL manual for on the Orcle technet website.
If you use the ADD clause to add a new column
to the table, the initial value of each row for the new column is
null. You can add a column with a NOT NULL constraint only to a table
that contains no rows.
If you create a view with a query that uses
the asterisk (*) in the select list to select all columns from the
base table, and you subsequently add columns to the base table, Oracle
will not automatically add the new column to the view. To add the new
column to the view, you can re-create the view using the CREATE VIEW
command with the OR REPLACE option.
Operations performed by the ALTER TABLE
command can cause Oracle to invalidate procedures and stored functions
that access the table.
Modifying Column Definitions
You can use the MODIFY clause to change any of
the following parts of a column definition:
* Default value
* NOT NULL column constraint
The MODIFY clause need only specify the column
name and the modified part of the definition, not the entire column
Datatypes and Sizes
You can change:
* A CHAR column to VARCHAR2 (or VARCHAR).
* A VARCHAR2 (or VARCHAR) to CHAR, but only if
the column contains nulls in all rows or if you do not attempt to
change the column size.
* Any columnís datatype, or decrease any
columnís size, if all rows for the column contain nulls. You can
always increase the size of a character or raw column or the precision
of a numeric column.
A change to a columnís default value affects
only those rows subsequently inserted to the table. Such a change does
not alter default values previously inserted.
The only type of integrity constraint that you
can add to an existing column using the MODIFY clause with the column
constraint syntax is a NOT NULL constraint. However, you can define
other types of integrity constraints (UNIQUE, PRIMARY KEY, referential
integrity, and CHECK constraints) on existing columns using the ADD
clause and the table constraint syntax.
You can define a NOT NULL constraint on an
existing column only if the column contains no nulls.
Allocation of Extents
The following statement allocates an extent of
5 KB for the emp table, and makes it available to instance 4:
EXTENT (SIZE 5K INSTANCE 4);
Because this command omits the DATAFILE
parameter, Oracle allocates the extent in one of the datafiles
belonging to the tablespace containing the table.
You can add a LOB column to a table, modify a
LOB index, or modify the LOB index storage characteristics.
The following statement adds CLOB column
REFERENCES to the emp table:
emp ADD (references CLOB)
LOB (references) STORE AS references_seg (TABLESPACE references_ts);
To modify the LOB column RESUME to use
caching, enter the following statement:
emp MODIFY LOB (references) (CACHE);
Nested Table Columns
You can add a nested table type column to a
table by specifying a nested table storage clause for each column
added. The following example adds the nested table column ABILITIES to
the emp table:
emp ADD (abilities ability_list)
NESTED TABLE abilities STORE AS abilitiesv8;
You can also modify a nested tableís storage
characteristics, using the name of the storage table specified in the
nested table storage clause to make the modification. You cannot query
or perform DML statements on the storage table; you can only use the
storage table to modify the nested table column storage
The following example creates table CARSERVICE
with nested table column CLIENT and storage table CLIENTV8. Nested
table CARSERVICE is modified to specify constraints and to modify a
column length by altering nested storage table CLIENT_LIST:
carservice (mech_name VARCHAR2(30),
NESTED TABLE client STORE AS clientv8;
ALTER TABLE clientv8 ADD UNIQUE (ssn);
clientv8 MODIFY (mech_name VARCHAR2(35));
The following statement adds a UNIQUE constraint to nested table
ABILITY_LIST ADD UNIQUE (a);
A REF value is a reference to a row in an
object table. A table can have top-level REF columns or REF attributes
embedded within an object column. In general, if a table has a REF
column, each REF value in the column could reference a row in a
different object table. A SCOPE clause restricts the scope of
references to a single table. In the real world, I would say 99
percent of applications would use scoped REFs; I can envision few
applications where you would want to access multiple tables from the
same column of a single table.
Use the ALTER TABLE command to add new REF
columns or to add REF clauses to existing REF columns. You can modify
any table, including named inner nested tables (storage tables). If a
REF column is created WITH ROWID or with a scope table, you cannot
modify the column to drop these options. However, if a table is
created without any REF clauses, you can add them later with an ALTER
TIP: You can add a scope clause only
to existing REF columns of a table if the table is empty. The
scope_table_name must be in your own schema, or you must have SELECT
privilege on the table or the SELECT ANY TABLE system privilege. This
privilege is only needed while altering the table with the REF column.
In the following example, an object type
dept_t has been previously defined. Now assume that table emp is
created as follows:
dept REF dept_t) AS OBJECT;
An object table DEPARTMENTS is created as:
CREATE TABLE departments OF dept_t AS OBJECT;
If the DEPARTMENTS table contains all possible departments, the dept
column in emp can only refer to rows in the DEPARTMENTS table. This
can be expressed as a scope clause on the DEPT column as follows:
ADD (SCOPE FOR (dept) IS departments);
Note that the ALTER TABLE statement here will
succeed only if the emp table is empty.
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.