If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the table's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the table. If you omit schema, Oracle7 assumes the table is in your own schema.
table
is the name of the table to be altered.
ADD
adds a column or integrity constraint.
MODIFY
modifies the definition of an existing column. If you omit any of the optional parts of the column definition (datatype, default value, or column constraint), these parts remain unchanged.
column
is the name of the column to be added or modified.
datatype
specifies a datatype for a new column or a new datatype for an existing column.
You can only omit the datatype if the statement also designates the column as part of the foreign key of a referential integrity constraint. Oracle7 automatically assigns the column the same datatype as the corresponding column of the referenced key of the referential integrity constraint.
DEFAULT
specifies a default value for a new column or a new default for an existing column. Oracle7 assigns this value to the column if a subsequent INSERT statement omits a value for the column. The datatype of the default value must match the datatype specified for the column. The column must also be long enough to hold the default value. A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.
column_constraint
adds or removes a NOT NULL constraint to or from and existing column. See the syntax of column_constraint ![[*]](jump.gif)
.
table_constraint
adds an integrity constraint to the table. See the syntax of table_constraint .
PCTFREE PCTUSED INITRANS MAXTRANS
changes the value of specified parameters for the table. See the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters of the CREATE TABLE command .
STORAGE
changes the storage characteristics of the table. See the STORAGE clause beginning .
DROP
drops an integrity constraint. See the DROP clause .
ALLOCATE EXTENT
explicitly allocates a new extent for the table.
INSTANCE makes the new extent available to the freelist group associated with the specified instance. If the instance number exceeds the maximum number of freelist groups, the former is divided by the latter, and the remainder is used to identify the freelist group to be used. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER. If you omit this parameter, the space is allocated to the table, but is not drawn from any particular freelist group. Rather the master freelist is used, and space is allocated as needed. For more information, see Oracle7 Server Concepts. Only use this parameter if you are using Oracle7 with the Parallel Server option in parallel mode.
Explicitly allocating an extent with this clause does affect the size for the next extent to be allocated as specified by the NEXT and PCTINCREASE storage parameters.
DEALLOCATE UNUSED
explicitly deallocate unused space at the end of the table and make the freed space available for other segments. You can free only unused space above the high-water mark. If KEEP is omitted, all unused space is freed. For more information, see the deallocate_clause.
KEEP specifies the number of bytes above the high-water mark that the table will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent.
ENABLE enable_clause
enables a single integrity constraint or all triggers associated with the table. See the ENABLE clause .
ENABLE TABLE LOCK
enables DML and DDL locks on a table in a parallel server environment. For more information, see Oracle7 Parallel Server Concepts & Administration.
DISABLE disable_clause
disables a single integrity constraint or all triggers associated with the table. See the DISABLE clause .
Integrity constraints specified in DISABLE clauses must be defined in the ALTER TABLE statement or in a previously issued statement. You can also enable and disable integrity constraints with the ENABLE and DISABLE keywords of the CONSTRAINT clause. If you define an integrity constraint but do not explicitly enable or disable it, Oracle7 enables it by default.
DISABLE TABLE LOCK
disables DML and DDL locks on a table to improve performance in a parallel server environment. For more information, see Oracle7 Parallel Server Concepts & Administration.
PARALLEL
specifies the degree of parallelism for the table. See the parallel_clause .
CACHE
Specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
NOCACHE
Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior.
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, Oracle7 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 Oracle7 to invalidate procedures and stored functions that access the table. For information on how and when Oracle7 invalidates such objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
You can define a NOT NULL constraint on an existing column only if the column contains no nulls.
Example I
ALTER TABLE emp ADD (thriftplan NUMBER(7,2), loancode CHAR(1) NOT NULL)
Example II
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.
Example III
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
Example IV
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 command omits the DATAFILE parameter, Oracle7 allocates the extent in one of the data files belonging to the tablespace containing the table.
Example V
This example 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
Other Examples
For examples of defining integrity constraints with the ALTER TABLE command, see the CONSTRAINT clause beginning .
For examples of enabling, disabling, and dropping integrity constraints and triggers with the ALTER TABLE command, see the ENABLE clause , the DISABLE clause , and DROP clause .
For examples of changing the value of a table's storage parameters, see the STORAGE clause .
CONSTRAINT clause
DISABLE clause
DROP clause
ENABLE clause
STORAGE clause