Name

ALTER TABLE

Synopsis

ALTER TABLE
[schema.]table_name
  {
[ADD (column
datatype [DEFAULT
expression]
[column_constraint_clause] )]
[MODIFY (column
[datatype] [DEFAULT
expression]
[column_constraint_clause] }]
   [PCTFREE integer]
   [PCTUSED integer]
   [INITRANS integer]
   [MAXTRANS integer]
   [LOGGING | NOLOGGING]
   [STORAGE (
      [NEXT integer[K | M] ]
      [MAXEXTENTS {integer | UNLIMITED} ]
      [PCTINCREASE integer]
      [FREELISTS integer]
      [FREELIST GROUPS integer] ]
      ) ]
   [DROP {
      PRIMARY KEY [CASCADE] |
UNIQUE
(column[,column...]
[CASCADE] |
CONSTRAINT constraint_name [CASCADE]} ]
[ALLOCATE EXTENT ([DATAFILE 'filename']
[SIZE integer[K | M] ]) ]
[DEALLOCATE UNUSED [KEEP integer[K | M]]
   [CACHE | NOCACHE]
   [RENAME TO table_name]
[PARALLEL ( {DEGREE {integer | DEFAULT} |
INSTANCES {integer | DEFAULT} } ) ]
   [NOPARALLEL]
   [DROP PARTITION partition]
   [TRUNCATE PARTITION partition]
[MODIFY PARTITION partition [REBUILD]
UNUSABLE LOCAL INDEXES]
[ADD PARTITION partition VALUES LESS THAN
(valuelist)
[RENAME PARTITION partition TO
newpartition]
[EXCHANGE PARTITION partition WITH
TABLE table_name
   [ {INCLUDING | EXCLUDING} INDEXES ]
   [ {WITH | WITHOUT} VALIDATION ] ]
  }
   [ENABLE
     {
      ALL TRIGGERS |
UNIQUE (column[,
column...] [CASCADE] |
      PRIMARY KEY [CASCADE] |
      CONSTRAINT constraint_name |
     }]
   [DISABLE
     {
      ALL TRIGGERS |
UNIQUE (column[,
column...] [CASCADE] |
      PRIMARY KEY [CASCADE] |
      CONSTRAINT constraint_name
     }]
  [ENABLE TABLE LOCK]
  [DISABLE TABLE LOCK]

Modifies the column characteristics of a table (table_name), the storage characteristics of the table, or integrity constraints associated with the table or its columns.

Keywords

ADD

Adds a column or integrity constraint to the table with the specified datatype.

DEFAULT

Specifies a default value for the column. The specified expression must match the datatype of the column.

column_constraint_clause

Adds or removes a column constraint, using the syntax shown in the later column_constraint_clause section.

MODIFY

Changes the definition of an existing column in the table; you can optionally change the datatype of the column, provide a default expression, and specify column constraints.

PCTFREE

Specifies the percentage of space to be reserved in each data block for future updates to rows contained in that block. The value may be in the range - 99, and the default value is 10.

PCTUSED

Specifies the minimum percentage of space that will be maintained as used in each data block. The value may be in the range 1- 99; the default value is 40.

INITRANS

Specifies the initial number of transaction entries allocated to each block.

MAXTRANS

Specifies the maximum number of transaction entries allocated to each block.

LOGGING

Specifies that future direct path DML operations are to be logged to the redo logs.

NOLOGGING

Specifies that future direct path DML operations are not to be logged to the redo logs.

STORAGE

Specifies the physical characteristics of the table as follows:

NEXT

Specifies the size of the next extent in bytes, kilobytes, or megabytes. If the value is not a multiple of the database blocksize, it will be rounded up to a multiple of the database blocksize.

MAXEXTENTS

Specifies the maximum number of extents that may be allocated for this table. The default will vary according to the database blocksize. Specify UNLIMITED for unlimited expansion.

PCTINCREASE

Specifies the percentage by which each extent will grow over the previous extent. The default is 50, which means that each extent will be one-and-one-half times larger than the previous extent.

FREELISTS

Specifies the number of free lists contained in each freelist group in this table. The default is 1 and the maximum depends on the database blocksize.

FREELIST GROUPS

Specifies the number of groups of free lists for this table. The default is 1. This parameter should be used only with the Parallel Server option running in parallel mode.

DROP PRIMARY KEY

Specifies that the table’s primary key is to be dropped.

DROP UNIQUE

Specifies that the UNIQUE constraint on the specified columns is to be dropped.

DROP CONSTRAINT

Specifies that the named integrity constraint is to be dropped.

CASCADE

Specifies that all other integrity constraints that depend on the dropped integrity constraint should also be dropped.

ALLOCATE EXTENT

Explicitly allocates a new extent for the table; you can optionally specify a datafile and size.

DEALLOCATE UNUSED

Releases storage above the highwater mark.

KEEP

Specifies the amount of storage above the highwater mark to keep.

CACHE

Specifies that blocks retrieved from this table during full table scans are placed at the beginning of the least-recently-used (LRU) list. This allows small lookup tables to remain in the SGA.

NOCACHE

Specifies that blocks retrieved from this table during full table scans are placed at the end of the LRU list. This is the default behavior.

RENAME TO

Renames table to table_name.

PARALLEL

Specifies the level of parallelism to be supported, based on the following parameters:

DEGREE

Specifies the degree of parallelism. An integer value specifies how many slave processes can be used. Specify DEFAULT to use the default value for the tablespace.

INSTANCES

Specifies the number of instances that can be used to execute slave processes. Specify DEFAULT to use the default value specified for the tablespace.

NOPARALLEL

Specifies that no parallel operations are to be performed.

DROP PARTITION

Drops the specified partition, and all data contained in that partition, from the table.

TRUNCATE PARTITION

Truncates the specified partition.

MODIFY PARTITION

Changes the physical attributes of the specified partition.

REBUILD

When used with MODIFY PARTITION, tells Oracle to rebuild all local indexes associated with the partition.

UNUSABLE LOCAL INDEXES

When used with MODIFY PARTITION, tells Oracle to mark all local indexes associated with the partition as unusable.

ADD PARTITION

Adds a new partition at the high end of the partitions. Cannot be used if the existing top partition has the key word MAXVALUE.

VALUES LESS THAN

Specifies the upper bound for the new partition. The valuelist is a comma-separated, ordered list of literal values which must collate greater than the top boundary for the highest existing partition in the table.

RENAME PARTITION TO

Renames the specified partition TO newpartition.

EXCHANGE PARTITION

Specifies that the partition and table (specified in WITH TABLEtable_name) are to be exchanged. The segments are kept intact, and only the data dictionary is modified.

INCLUDING INDEXES

Specifies that local indexes on the partition and indexes on the table are to be exchanged as well.

EXCLUDING INDEXES

Specifies that indexes are to be ignored.

WITH VALIDATION

Specifies that Oracle is to validate that all rows in the table are capable of being included in the partition.

WITHOUT VALIDATION

Specifies that the exchange is to occur without Oracle’s verifying that all rows are legitimate.

ENABLE

Enables a single integrity constraint or all triggers associated with the table (see the definitions for the keywords under DROP). ALL TRIGGERS enables all triggers.

DISABLE

Disables a single integrity constraint or all triggers associated with the table (see the definitions for the keywords under DROP). ALL TRIGGERS disables all triggers.

ENABLE TABLE LOCK

Enables DML and DDL locks on a table in a Parallel Server environment.

DISABLE TABLE LOCK

Disables DML and DDL locks on a table in a Parallel Server environment.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset