Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Table Alteration

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 columns.

* Add or modify index-only table characteristics.

* 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 table partitions.

* Add, modify, split, move, drop, or truncate table subpartitions.

* Allow or disallow writes to a table.

* Change the rows per block of storage for a table.

* Drop a column.

* Enable, disable, or drop an integrity constraint or trigger.

* Explicitly allocate an extent.

* Explicitly deallocate the unused space of a table.

* 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 parameters.

* Physically move a table.

* Redefine a column (datatype, size, default value).

* 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 type.

* 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:

a.       If the tableís creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

b.      If the tableís creation label is lower than your DBMS label, you must have the WRITEDOWN system privilege.

c.       If the tableís creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

The syntax of the ALTER TABLE command for Oracle is located in the SQL manual for on the Orcle technet website.

Adding Columns  

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:

* Datatype

* Size

* 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 definition.

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.

Default Values  

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.

Integrity Constraints  

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:



Because this command omits the DATAFILE parameter, Oracle allocates the extent in one of the datafiles belonging to the tablespace containing the table.

LOB Columns  

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:

ALTER 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:

ALTER TABLE 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:

ALTER 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 characteristics.

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:

CREATE TABLE carservice (mech_name VARCHAR2(30),
                         client   client_list);
  NESTED TABLE client STORE AS clientv8;

ALTER TABLE clientv8 ADD UNIQUE (ssn); 

ALTER TABLE clientv8 MODIFY (mech_name VARCHAR2(35));
The following statement adds a UNIQUE constraint to nested table ABILITY_ LIST:


Scoped REFs  

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 TABLE statement.

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: 

   (name   VARCHAR(100),
    salary      NUMBER,
    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.

See 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.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter