SQL Statements (continued), 2 of 11

ALTER TABLE

Syntax

column_constraint, table_constraint, column_ref_constraint, table_ref_constraint, constraint_state: See the "constraint_clause".

LOB_storage_clause::=

LOB_parameters::=

storage_clause: See "storage_clause".

varray_storage_clause::=

nested_table_storage_clause::=

object_properties::=

physical_properties::=

partition_storage_clause::=

modify_column_options::=

move_table_clause::=

segment_attributes_clause::=

physical_attributes_clause::=

index_organized_table_clause::=

compression_clause::=

index_organized_overflow_clause::=

modify_collection_retrieval_clause::=

modify_storage_clauses::=

modify_LOB_storage_clause::=

modify_LOB_storage_parameters::=

allocate_extent_clause::=

deallocate_unused_clause::=

modify_varray_storage_clause::=

drop_constraint_clause::=

drop_column_clause::=

records_per_block_clause::=

alter_overflow_clause::=

overflow_clause::=

add_overflow_clause::=

partitioning_clauses::=

modify_default_attributes_clause::=

modify_partition_clause::=

partition_attributes::=

add_subpartition_clause::=

subpartition_description::=

modify_subpartition_clause::=

move_partition_clause::=

partition_description::=

partition_level_subpartitioning::=

hash_partitioning_storage_clause::=

move_subpartition_clause::=

add_range_partition_clause::=

add_hash_partition_clause::=

coalesce_partition_clause::=

drop_partition_clause::=

rename_partition/ subpartition_clause::=

truncate_partition_clause/truncate_subpartition_clause::=

split_partition_clause::=

merge_partitions_clause::=

exchange_partition_clause/exchange_subpartition_clause::=

row_movement_clause::=

parallel_clause::=

enable_disable_clause::=

using_index_clause::=

Purpose

To alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition.

Prerequisites

The table must be in your own schema, or you must have ALTER privilege on the table, or you must have ALTER ANY TABLE system privilege. For some operations you may also need the CREATE ANY INDEX privilege.

In addition, if you are not the owner of the table, you need the DROP ANY TABLE privilege in order to use the drop_partition_clause or truncate_partition_clause.

You must also have space quota in the tablespace in which space is to be acquired in order to use the add_partition_clause, modify_partition_clause, move_partition_clause, and split_partition_clause.

To enable a UNIQUE or PRIMARY KEY constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle creates an index on the columns of the unique or primary key in the schema containing the table. See "CREATE INDEX".

To enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER 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 privilege or the EXECUTE schema object privilege for the object type.

Keywords and Parameters

Examples

Nested Table Example

The following statement modifies the storage characteristics of a nested table column PROJECTS in table EMP so that when queried it returns actual values instead of locators:

ALTER TABLE emp MODIFY NESTED TABLE projects RETURN AS VALUE;

PARALLEL Example

The following statement specifies parallel processing for queries to the EMP table:

ALTER TABLE emp
   PARALLEL;

ENABLE VALIDATE Example

The following statement places in ENABLE VALIDATE state an integrity constraint named FK_DEPTNO in the EMP table:

ALTER TABLE emp
   ENABLE VALIDATE CONSTRAINT fk_deptno
   EXCEPTIONS INTO except_table;

Each row of the EMP table must satisfy the constraint for Oracle to enable the constraint. If any row violates the constraint, the constraint remains disabled. Oracle lists any exceptions in the table EXCEPT_TABLE. You can also identify the exceptions in the EMP table with the following statement:

SELECT emp.*
   FROM emp e, except_table ex
   WHERE e.row_id = ex.row_id
      AND ex.table_name = 'EMP'
      AND ex.constraint = 'FK_DEPTNO';

ENABLE NOVALIDATE Example

The following statement tries to place in ENABLE NOVALIDATE state two constraints on the EMP table:

ALTER TABLE emp
   ENABLE NOVALIDATE UNIQUE (ename)
   ENABLE NOVALIDATE CONSTRAINT nn_ename;

This statement has two ENABLE clauses:

The first places a unique constraint on the ENAME column in ENABLE NOVALIDATE state.
The second places the constraint named NN_ENAME in ENABLE NOVALIDATE state.

In this case, Oracle enables the constraints only if both are satisfied by each row in the table. If any row violates either constraint, Oracle returns an error and both constraints remain disabled.

DISABLE Example

Consider a referential integrity constraint involving a foreign key on the combination of the AREACO and PHONENO columns of the PHONE_CALLS table. The foreign key references a unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table. The following statement disables the unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table:

ALTER TABLE customers
   DISABLE UNIQUE (areaco, phoneno) CASCADE;

The unique key in the CUSTOMERS table is referenced by the foreign key in the PHONE_CALLS table, so you must use the CASCADE clause to disable the unique key. This clause disables the foreign key as well.

CHECK Example

The following statement defines and disables a CHECK constraint on the EMP table:

ALTER TABLE emp
   ADD (CONSTRAINT check_comp CHECK (sal + comm <= 5000) )
   DISABLE CONSTRAINT check_comp;

The constraint CHECK_COMP ensures that no employee's total compensation exceeds $5000. The constraint is disabled, so you can increase an employee's compensation above this limit.

Triggers Example

The following statement enables all triggers associated with the EMP table:

ALTER TABLE emp
   ENABLE ALL TRIGGERS;

DEALLOCATE UNUSED Example

The following statement frees all unused space for reuse in table EMP, where the high water mark is above MINEXTENTS:

ALTER TABLE emp
    DEALLOCATE UNUSED;

DROP COLUMN Example

This statement illustrates the drop_column_clause with CASCADE CONSTRAINTS. Assume table T1 is created as follows:

CREATE TABLE t1 (
   pk NUMBER PRIMARY KEY,
   fk NUMBER,
   c1 NUMBER,
   c2 NUMBER,
   CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1,
   CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0),
   CONSTRAINT ck2 CHECK (c2 > 0)
);

An error will be returned for the following statements:

ALTER TABLE t1 DROP (pk); -- pk is a parent key
ALTER TABLE t1 DROP (c1);  -- c1 is referenced by multicolumn
                           constraint ck1

Submitting the following statement drops column PK, the primary key constraint, the foreign key constraint, RI, and the check constraint, CK1:

ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;

If all columns referenced by the constraints defined on the dropped columns are also dropped, then CASCADE CONSTRAINTS is not required. For example, assuming that no other referential constraints from other tables refer to column PK, then it is valid to submit the following statement without the CASCADE CONSTRAINTS clause:

ALTER TABLE t1 DROP (pk, fk, c1);

Index-Organized Table Examples

This statement modifies the INITRANS parameter for the index segment of index-organized table DOCINDEX:

ALTER TABLE docindex INITRANS 4;

The following statement adds an overflow data segment to index-organized table DOCINDEX:

ALTER TABLE docindex ADD OVERFLOW;

This statement modifies the INITRANS parameter for the overflow data segment of index-organized table DOCINDEX:

ALTER TABLE docindex OVERFLOW INITRANS 4;

ADD PARTITION Example

The following statement adds a partition P3 and specifies storage characteristics for three of the table's LOB columns (B, C, and D):

ALTER TABLE pt ADD PARTITION p3 VALUES LESS THAN (30)
   LOB (b, d) STORE AS (TABLESPACE tsz)
   LOB (c) STORE AS mylobseg;

The LOB data and LOB index segments for columns B and D in partition P3 will reside in tablespace TSZ. The remaining attributes for these LOB columns will be inherited first from the table-level defaults, and then from the tablespace defaults.

The LOB data segments for column C will reside in the MYLOBSEG segment, and will inherit all other attributes from the table-level defaults and then from the tablespace defaults.

SPLIT PARTITION Example

The following statement splits partition P3 into partitions P3_1 and P3_2:

ALTER TABLE pt SPLIT PARTITION p3 AT (25)
   INTO (PARTITION p3_1 TABLESPACE ts4
            LOB (b,d) STORE AS (TABLESPACE tsz),
         PARTITION p3_2 (TABLESPACE ts5)
            LOB (c) STORE AS (TABLESPACE ts5);

In partition P3_1, Oracle creates the LOB segments for columns B and D in tablespace TSZ. In partition P3_2, Oracle creates the LOB segments for column C in tablespace TS5. The LOB segments for columns B and D in partition P3_2 and those for column C in partition P3_1 remain in original tablespace for the original partition P3. However, Oracle creates new segments for all the LOB data and LOB index segments, even though they are not moved to a new tablespace.

User-Defined Object Identifier Example

The following statements create an object type, a corresponding object table with a primary-key-based object identifier, and a table having a user-defined REF column:

CREATE TYPE emp_t AS OBJECT (empno NUMBER, address CHAR(30));

CREATE TABLE emp OF emp_t (
   empno PRIMARY KEY)
   OBJECT IDENTIFIER IS PRIMARY KEY;

CREATE TABLE dept (dno NUMBER, mgr_ref REF emp_t SCOPE is emp);

The next statements add a constraint and a user-defined REF column, both of which reference table EMP:

ALTER TABLE dept ADD CONSTRAINT mgr_cons FOREIGN KEY (mgr_ref)
   REFERENCES emp;
ALTER TABLE dept ADD sr_mgr REF emp_t REFERENCES emp;

Add Column Example

The following statement adds a column named THRIFTPLAN of datatype NUMBER with a maximum of seven digits and two decimal places and a column named LOANCODE of datatype CHAR with a size of one and a NOT NULL integrity constraint:

ALTER TABLE emp 
   ADD (thriftplan NUMBER(7,2),
        loancode CHAR(1) NOT NULL);

Modify Column Examples

The following statement increases the size of the THRIFTPLAN column to nine digits:

ALTER TABLE emp
   MODIFY (thriftplan NUMBER(9,2));

Because the MODIFY clause contains only one column definition, the parentheses around the definition are optional.

The following statement changes the values of the PCTFREE and PCTUSED parameters for the EMP table to 30 and 60, respectively:

ALTER TABLE emp 
    PCTFREE 30
    PCTUSED 60;

ALLOCATE EXTENT Example

The following statement allocates an extent of 5 kilobytes for the EMP table and makes it available to instance 4:

ALTER TABLE emp
  ALLOCATE EXTENT (SIZE 5K INSTANCE 4);

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

DEFAULT Examples

This statement modifies the BAL column of the ACCOUNTS table so that it has a default value of 0:

ALTER TABLE accounts
  MODIFY (bal DEFAULT 0);

If you subsequently add a new row to the ACCOUNTS table and do not specify a value for the BAL column, the value of the BAL column is automatically 0:

INSERT INTO accounts(accno, accname)
  VALUES (accseq.nextval, 'LEWIS'); 

SELECT * 
    FROM accounts
    WHERE accname = 'LEWIS'; 

ACCNO  ACCNAME BAL 
------ ------- ---
815234  LEWIS     0

To discontinue previously specified default values, so that they are no longer automatically inserted into newly added rows, replace the values with nulls, as shown in this statement:

ALTER TABLE accounts
   MODIFY (bal DEFAULT NULL);

The MODIFY clause need only specify the column name and the modified part of the definition, rather than the entire column definition. This statement has no effect on any existing values in existing rows.

Drop Constraint Examples

The following statement drops the primary key of the DEPT table:

ALTER TABLE dept 
    DROP PRIMARY KEY CASCADE;

If you know that the name of the PRIMARY KEY constraint is PK_DEPT, you could also drop it with the following statement:

ALTER TABLE dept
    DROP CONSTRAINT pk_dept CASCADE;

The CASCADE clause drops any foreign keys that reference the primary key.

The following statement drops the unique key on the DNAME column of the DEPT table:

ALTER TABLE dept 
    DROP UNIQUE (dname);

The DROP clause in this statement omits the CASCADE clause. Because of this omission, Oracle does not drop the unique key if any foreign key references it.

LOB Examples

The following statement adds CLOB column RESUME to the EMPLOYEE table and specifies LOB storage characteristics for the new column:

ALTER TABLE employee ADD (resume CLOB)
  LOB (resume) STORE AS resume_seg (TABLESPACE resume_ts);

To modify the LOB column RESUME to use caching, enter the following statement:

ALTER TABLE employee MODIFY LOB (resume) (CACHE);

Nested Table Examples

The following statement adds the nested table column SKILLS to the EMPLOYEE table:

ALTER TABLE employee ADD (skills skill_table_type)
    NESTED TABLE skills STORE AS nested_skill_table;

You can also modify a nested table's storage characteristics. Use 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. Use the storage table only to modify the nested table column storage characteristics.

The following statement creates table VETSERVICE with nested table column CLIENT and storage table CLIENT_TAB. Nested table VETSERVICE is modified to specify constraints:

CREATE TYPE pet_table AS OBJECT
   (pet_name VARCHAR2(10), pet_dob DATE);

CREATE TABLE vetservice (vet_name VARCHAR2(30),
                         client   pet_table)
  NESTED TABLE client STORE AS client_tab;

ALTER TABLE client_tab ADD UNIQUE (ssn);

The following statement adds a UNIQUE constraint to nested table NESTED_SKILL_TABLE:

ALTER TABLE nested_skill_table ADD UNIQUE (a);

The following statement alters the storage table for a nested table of REF values to specify that the REF is scoped:

CREATE TYPE emp_t AS OBJECT (eno number, ename char(31)); 
CREATE TYPE emps_t AS TABLE OF REF emp_t; 
CREATE TABLE emptab OF emp_t; 
CREATE TABLE dept (dno NUMBER, employees emps_t) 
   NESTED TABLE employees STORE AS deptemps; 
ALTER TABLE deptemps ADD (SCOPE FOR (column_value) IS emptab);

Similarly, to specify storing the REF with rowid:

ALTER TABLE deptemps ADD (REF(column_value) WITH ROWID);

In order to execute these ALTER TABLE statements successfully, the storage table DEPTEMPS must be empty. Also, because the nested table is defined as a table of scalars (REFs), Oracle implicitly provides the column name COLUMN_VALUE for the storage table.

See Also:

"CREATE TABLE" for more information about nested table storage.

Oracle8i Application Developer's Guide - Fundamentals for more information about nested tables.

REF Examples

In the following statement an object type DEPT_T has been previously defined. Now, create table EMP as follows:

CREATE TABLE emp 
   (name VARCHAR(100), 
    salary NUMBER,
    dept REF dept_t);

An object table DEPARTMENTS is created as:

CREATE TABLE departments OF dept_t;

The DEPT column can store references to objects of DEPT_T stored in any table. If you would like to restrict the references to point only to objects stored in the DEPARTMENTS table, you could do so by adding a scope constraint on the DEPT column as follows:

ALTER TABLE emp 
    ADD (SCOPE FOR (dept) IS departments);

The above ALTER TABLE statement will succeed only if the EMP table is empty.

If you want the REF values in the DEPT column of EMP to also store the rowids, issue the following statement:

ALTER TABLE emp 
   ADD (REF(dept) WITH ROWID);

Add Partition Example

The following statement adds partition JAN99 to tablespace TSX:

ALTER TABLE sales 
  ADD PARTITION jan99 VALUES LESS THAN( '970201' ) 
  TABLESPACE tsx;

Drop Partition Example

The following statement drops partition DEC98:

ALTER TABLE sales DROP PARTITION dec98;

Exchange Partition Example

The following statement converts partition FEB97 to table SALES_FEB97 without exchanging local index partitions with corresponding indexes on SALES_FEB97 and without verifying that data in SALES_FEB97 falls within the bounds of partition FEB97:

ALTER TABLE sales 
   EXCHANGE PARTITION feb97 WITH TABLE sales_feb97 
   WITHOUT VALIDATION;

Modify Partition Examples

The following statement marks all the local index partitions corresponding to the NOV96 partition of the SALES table UNUSABLE:

ALTER TABLE sales MODIFY PARTITION nov96 
   UNUSABLE LOCAL INDEXES;

The following statement rebuilds all the local index partitions that were marked UNUSABLE:

ALTER TABLE sales MODIFY PARTITION jan97
   REBUILD UNUSABLE LOCAL INDEXES;

The following statement changes MAXEXTENTS and logging attribute for partition BRANCH_NY:

ALTER TABLE branch MODIFY PARTITION branch_ny 
   STORAGE (MAXEXTENTS 75) LOGGING;

Move Partition Example

The following statement moves partition DEPOT2 to tablespace TS094:

ALTER TABLE parts 
  MOVE PARTITION depot2 TABLESPACE ts094 NOLOGGING;

Rename Partition Examples

The following statement renames a table:

ALTER TABLE emp RENAME TO employee;

In the following statement, partition EMP3 is renamed:

ALTER TABLE employee RENAME PARTITION emp3 TO employee3;

Split Partition Example

The following statement splits the old partition DEPOT4, creating two new partitions, naming one DEPOT9 and reusing the name of the old partition for the other:

ALTER TABLE parts
   SPLIT PARTITION depot4 AT ( '40-001' )
   INTO ( PARTITION depot4 TABLESPACE ts009 STORAGE (MINEXTENTS 2),
         PARTITION depot9 TABLESPACE ts010 )
   PARALLEL (10);

Truncate Partition Example

The following statement deletes all the data in the SYS_P017 partition and deallocates the freed space:

ALTER TABLE deliveries
   TRUNCATE PARTITION sys_p017 DROP STORAGE;

Additional Examples

For examples of defining integrity constraints with the ALTER TABLE statement, see the "constraint_clause".

For examples of changing the value of a table's storage parameters, see the "storage_clause".