Reasons for Using the ALTER TABLE Statement

Before Alter Table operation you should keep in ming that if a view, materialized view, trigger, domain index, function-based index, check constraint, function, procedure of package depends on a base table, the alteration of the base table or its columns can affect the dependent object.

Now in which case you need ALTER TABLE operation? The cases are listed below.

•Add, drop, or rename columns, or modify an existing column definition (datatype, length, default value, NOT NULL integrity constraint, and encryption.).

•Move the table to a new segment or tablespace.

•Modify the logging attributes of the table. Either NOLOGGING or LOGGING to generate or avoid redo.

•Modify physical characteristics (INITRANS or storage parameters).

•Explicitly allocate an extent or deallocate unused space

•Modify the CACHE/NOCACHE attributes. It specifies how Oracle Database should store blocks in the buffer cache. NOCACHE is the default.

•Add, modify or drop integrity constraints associated with the table.

•Modify the degree of parallelism for the table. It is specified by PARALLEL keyword.

•To rename a table.

•Add or modify index-organized table characteristics.

•Alter the characteristics of an external table.

•Add or modify LOB columns.

•Add or modify object type, nested table, or varray columns


Related Documents:
-----------------------
Some Alter Table Operations