alter table语法

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3001.htm#CIHCFDDJ

 

ALTER TABLE

Purpose

Use the ALTER TABLE statement to alterthe definition of a nonpartitioned table, a partitioned table, atable partition, or a table subpartition. For object tables orrelational tables with object columns, use ALTERTABLE to convert the table to the latest definition ofits referenced type after the type has been altered.

 

 

add_column_clause::=

Description of the illustrationadd_column_clause.gif

(column_definition::=,column_properties::=)

column_definition::=

column_definition

Unless otherwise noted in this section, the elements ofcolumn_definition have the samebehavior when adding a column to an existing table as they do whencreating a new table. Please refer to column_definition for information.

Restriction on column_definition TheSORT parameter is valid only when creating a newtable. You cannot specify SORT in thecolumn_definition of an ALTER TABLE ...ADD statement.

If you add a column, then the initial value of each row for thenew column is null unless you specify the DEFAULTclause. In this case, Oracle Database updates each row in the newcolumn with the value you specify for DEFAULT. Thisupdate operation, in turn, fires any AFTERUPDATE triggers defined on the table.

Note:

If a column has a default value, then you can use the DEFAULT clause to change the default to NULL, but you cannot remove the default valuecompletely. That is, if a column has ever had a default valueassigned to it, then the DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary view will alwaysdisplay either a default value or NULL.

You can add an overflow data segment to each partition of apartitioned index-organized table.

You can add LOB columns to nonpartitioned and partitionedtables. You can specify LOB storage at the table and at thepartition or subpartition level.

If you previously created a view with a query that used theSELECT * syntax to select all columnsfrom table, and you now add a column to table, then the database doesnot automatically add the new column to the view. To add the newcolumn to the view, re-create the view using theCREATE VIEW statement with theOR REPLACE clause. Please refer toCREATE VIEW for more information.