Top 9 오라클 Alter Table 5795 Votes This Answer

You are looking for information, articles, knowledge about the topic nail salons open on sunday near me 오라클 alter table on Google, you do not find the information you need! Here are the best content compiled and compiled by the https://toplist.maxfit.vn team, along with other related topics such as: 오라클 alter table Oracle alter table add column default, 오라클 컬럼 코멘트 변경, 오라클 컬럼 추가, 오라클 컬럼 순서 변경, 오라클 코멘트 추가, Oracle ALTER TABLE MODIFY column type, 오라클 컬럼명 변경, 오라클 컬럼 삭제

오라클 데이터베이스 SQL 강의 9강 – 테이블 수정하기(ALTER TABLE)

[ORACLE] 오라클 테이블 컬럼 추가/수정/삭제/이름변경 하는 방법(ALTER 테이블 ADD/MODIFY/DROP/RENAME) :: 인 생

Article author: jwklife.tistory.com
Reviews from users: 41941 Ratings
Top rated: 4.2
Lowest rated: 1
Summary of article content: Articles about [ORACLE] 오라클 테이블 컬럼 추가/수정/삭제/이름변경 하는 방법(ALTER 테이블 ADD/MODIFY/DROP/RENAME) :: 인 생 [ORACLE] 오라클 테이블 컬럼 추가/수정/삭제/이름변경 하는 방법(ALTER 테이블 ADD/MODIFY/DROP/RENAME) … -> ALTER TABLE USER RENAME COLUMN USER_NAME … …
Most searched keywords: Whether you are looking for [ORACLE] 오라클 테이블 컬럼 추가/수정/삭제/이름변경 하는 방법(ALTER 테이블 ADD/MODIFY/DROP/RENAME) :: 인 생 [ORACLE] 오라클 테이블 컬럼 추가/수정/삭제/이름변경 하는 방법(ALTER 테이블 ADD/MODIFY/DROP/RENAME) … -> ALTER TABLE USER RENAME COLUMN USER_NAME … 1. 테이블 컬럼 추가하기(ALTER TABLE ADD) [문법] ALTER TABLE 테이블명 ADD(컬럼명 데이타타입(사이즈)); ( EX ) USER라는 테이블에 USER_NAME이라는 컬럼을 VARCHAR2(13) 타입으로 추가할 때 -> ALTER TABLE..
Table of Contents:

Article author: www.techonthenet.com
Reviews from users: 34236 Ratings
Top rated: 3.8
Lowest rated: 1
Summary of article content: Articles about Oracle / PLSQL: ALTER TABLE Statement The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The Oracle ALTER TABLE statement is also used to rename a table. Add … …
Most searched keywords: Whether you are looking for Oracle / PLSQL: ALTER TABLE Statement The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The Oracle ALTER TABLE statement is also used to rename a table. Add … This Oracle tutorial explains how to use the Oracle ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax, examples and practice exercises).techonthenet, tech on the net, totn, oracle, plsql, alter table statement, add column, modify column, drop column, rename column, rename table, syntax, example, tutorial
Table of Contents:

Description

Add column in table

Add multiple columns in table

Modify column in table

Modify Multiple columns in table

Drop column in table

Rename column in table
(NEW in Oracle 9i Release 2)

Rename table

Practice Exercise #1

Practice Exercise #2

Practice Exercise #3

Practice Exercise #4

Practice Exercise #5

Practice Exercise #6

Practice Exercise #7

Oracle / PLSQL: ALTER TABLE Statement

Article author: docs.oracle.com
Reviews from users: 26779 Ratings
Top rated: 3.3
Lowest rated: 1
Summary of article content: Articles about ALTER TABLE Operations performed by the ALTER TABLE statement can cause Oracle Database to invalate procedures and stored functions that access the table. For information … …
Most searched keywords: Whether you are looking for ALTER TABLE Operations performed by the ALTER TABLE statement can cause Oracle Database to invalate procedures and stored functions that access the table. For information …
Table of Contents:

ALTER TABLE

Article author: ssunsband.tistory.com
Reviews from users: 49732 Ratings
Top rated: 3.8
Lowest rated: 1
Summary of article content: Articles about 오라클 테이블 컬럼 추가, 수정, 삭제, 이름변경 하기(ALTER 테이블) 테이블에 컬럼을 추가합니다.(ADD). 문법) ALTER TABLE 테이블명 ADD(컬럼명 데이터타입(데이터크기));. EX1) ALTER TABLE EMP ADD(EMP_ADDR … …
Most searched keywords: Whether you are looking for 오라클 테이블 컬럼 추가, 수정, 삭제, 이름변경 하기(ALTER 테이블) 테이블에 컬럼을 추가합니다.(ADD). 문법) ALTER TABLE 테이블명 ADD(컬럼명 데이터타입(데이터크기));. EX1) ALTER TABLE EMP ADD(EMP_ADDR … 1. 테이블에 컬럼을 추가합니다.(ADD) 문법) ALTER TABLE 테이블명 ADD(컬럼명 데이터타입(데이터크기)); EX1) ALTER TABLE EMP ADD(EMP_ADDR VARCHAR2(10)); — EMP 테이블에 EMP_ADDR이라는 컬럼을 VARCHA..
Table of Contents:

오라클 테이블 컬럼 추가, 수정, 삭제, 이름변경 하기(ALTER 테이블)

Article author: amaze9001.tistory.com
Reviews from users: 29934 Ratings
Top rated: 4.8
Lowest rated: 1
Summary of article content: Articles about [Oracle] 오라클 테이블 변경 (ADD/MODIFY/DROP/RENAME) 오라클 테이블 구조 변경하기 ALTER TABLE 1. 컬럼 추가하기 문법 : ALTER TABLE 테이블명 ADD ( 컬럼명1 데이터타입, 컬럼명2 데이터타입, . …
Most searched keywords: Whether you are looking for [Oracle] 오라클 테이블 변경 (ADD/MODIFY/DROP/RENAME) 오라클 테이블 구조 변경하기 ALTER TABLE 1. 컬럼 추가하기 문법 : ALTER TABLE 테이블명 ADD ( 컬럼명1 데이터타입, 컬럼명2 데이터타입, . 오라클 테이블 구조 변경하기 ALTER TABLE 1. 컬럼 추가하기 문법 : ALTER TABLE 테이블명 ADD ( 컬럼명1 데이터타입, 컬럼명2 데이터타입, …. ); 예시 : EMP20 테이블에 숫자타입 급여 컬럼과 문자타입 업무..Amazing한 IT 이야기
Table of Contents:

[Oracle] 오라클 테이블 변경 (ADDMODIFYDROPRENAME)

오라클 테이블 구조 변경하기 ALTER TABLE

Article author: dpdpwl.tistory.com
Reviews from users: 354 Ratings
Top rated: 4.2
Lowest rated: 1
Summary of article content: Articles about 코딩벌레 :: [Oracle] 오라클 컬럼추가,수정,삭제,코멘트달기 오라클에서 테이블컬럼 추가,수정,삭제,코멘트를 다는법을 알아보겠습니다. A,B,C 세개의 컬럼이 있는 테이블이 있습니다. 추가 ALTER TABLE 테이블명 ADD(컬럼명 … …
Most searched keywords: Whether you are looking for 코딩벌레 :: [Oracle] 오라클 컬럼추가,수정,삭제,코멘트달기 오라클에서 테이블컬럼 추가,수정,삭제,코멘트를 다는법을 알아보겠습니다. A,B,C 세개의 컬럼이 있는 테이블이 있습니다. 추가 ALTER TABLE 테이블명 ADD(컬럼명 … 오라클에서 테이블컬럼 추가,수정,삭제,코멘트를 다는법을 알아보겠습니다. A,B,C 세개의 컬럼이 있는 테이블이 있습니다. 추가 ALTER TABLE 테이블명 ADD(컬럼명 타입(사이즈)); ALTER TABLE TEST_TABLE ADD(D..
Table of Contents:

네비게이션

[Oracle] 오라클 컬럼추가수정삭제코멘트달기

사이드바

검색

티스토리툴바

Article author: offbyone.tistory.com
Reviews from users: 44890 Ratings
Top rated: 5.0
Lowest rated: 1
Summary of article content: Articles about 오라클 ALTER 명령을 사용해서 테이블 변경하기 오라클 ALTER 명령을 사용해서 테이블 변경하기 · 1. 테스트용 테이블 생성 스크립트 입니다. · 2. 주키를 추가 / 제거 합니다. · 3. 외래키를 추가/ 제거 … …
Most searched keywords: Whether you are looking for 오라클 ALTER 명령을 사용해서 테이블 변경하기 오라클 ALTER 명령을 사용해서 테이블 변경하기 · 1. 테스트용 테이블 생성 스크립트 입니다. · 2. 주키를 추가 / 제거 합니다. · 3. 외래키를 추가/ 제거 … ALTER 명령어를 사용해서 오라클 테이블 구조를 변경하는 방법에 대해 알아봅니다. 1. 테스트용 테이블 생성 스크립트 입니다. CREATE TABLE TB_BOARD ( NUM NUMBER(20,0) NOT NULL, NAME VARCHAR2(20) NOT NULL,..
Table of Contents:

태그

공지사항

최근글

[ORACLE] 오라클 테이블 컬럼 추가/수정/삭제/이름변경 하는 방법(ALTER 테이블 ADD/MODIFY/DROP/RENAME)

1. 테이블 컬럼 추가하기 (ALTER TABLE ADD)
[문법] ALTER TABLE 테이블명 ADD(컬럼명 데이타타입(사이즈));

( EX ) USER라는 테이블에 USER_NAME이라는 컬럼을 VARCHAR2(13) 타입으로 추가할 때

-> ALTER TABLE USER ADD(USER_NAME VARCHAR2(13));

2. 테이블 컬럼 수정하기 (ALTER TABLE MODIFY)
[문법] ALTER TABLE 테이블명 MODIFY(컬럼명 테이타타입(사이즈));

( EX ) USER라는 테이블에 USER_AGE 라는 컬럼을 NUMBER(3) 타입으로 수정할 때

-> ALTER TABLE USER MODIFY(USER_AGE NUMBER(3));

3. 테이블 컬럼 삭제하기 (ALTER TABLE DROP)
[문법] ALTER TABLE 테이블명 DROP COLUMN 컬럼명

( EX ) USER라는 테이블에 USER_NAME 이라는 컬럼을 삭제할 때

-> ALTER TABLE USER DROP COLUMN USER_NAME;

4. 테이블 컬럼 이름 변경하기 (ALTER TABLE RENAME)
[문법] ALTER TABLE 테이블명 RENAME COLUMN 원래컬럼명 TO 바꿀컬럼명;

( EX ) USER라는 테이블에 USER_NAME 이라는 컬럼을 USER_FIRST_NAME으로 변경할 때

-> ALTER TABLE USER RENAME COLUMN USER_NAME TO USER_FIRST_NAME;

컬럼 수정 작업은 테이터가 별로 없는 테이블이나 시작 단계에서 작업을 하는 것을 추천합니다.

1. 데이터가 많으면 수정하는데 시간이 많이 걸릴 것입니다.

2. 컬럼 수정으로 인해 컬럼명이 매칭이 되지 않아서 프로그램 오류가 발생할 수도 있습니다.

ALTER TABLE

Oracle recommends that you use thestatement, rather than, whenever possible for operations on materialized view log tables.

Use the ALTER TABLE statement to alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition. For object tables or relational tables with object columns, use ALTER TABLE to convert the table to the latest definition of its referenced type after the type has been altered.

Additional Prerequisite for Referring to Editioned Objects To specify an edition in the evaluation_edition_clause or the unusable_editions_clause , you must have the USE privilege on the edition.

Additional Prerequisites for Flashback Data Archive Operations To use the flashback_archive_clause to enable historical tracking for the table, you must have the FLASHBACK ARCHIVE object privilege on the flashback data archive that will contain the historical data. To use the flashback_archive_clause to disable historical tracking for the table, you must have the FLASHBACK ARCHIVE ADMINSTER system privilege or you must be logged in as SYSDBA .

Additional Prerequisites When Using Object Types 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 object privilege for the object type.

To enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER system privilege.

Additional Prerequisites for Constraints and Triggers 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 Database creates an index on the columns of the unique or primary key in the schema containing the table.

When using the exchange_partition_subpart clause, if the table data being exchanged contains an identity column and you are not the owner of both tables involved in the exchange, then you must have the ALTER ANY SEQUENCE system privilege.

When a partitioning operation cascades to reference-partitioned child tables, privileges are not required on the reference-partitioned child tables.

You must also have space quota in the tablespace in which space is to be acquired in order to use the add_table_partition , modify_table_partition , move_table_partition , and split_table_partition clauses.

Additional Prerequisites for Partitioning Operations If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the drop_table_partition or truncate_table_partition clause.

The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.

If you specify theclause, then you must specify at least one of the clausesor

After each clause you will find links to its component subclauses.

You must specify some clause after. None of the clauses afterare required, but you must specify at least one of them.

Semantics

Many clauses of the ALTER TABLE statement have the same functionality they have in a CREATE TABLE statement. For more information on such clauses, see CREATE TABLE.

Note: Operations performed by the ALTER TABLE statement can cause Oracle Database to invalidate procedures and stored functions that access the table. For information on how and when the database invalidates such objects, see Operations performed by thestatement can cause Oracle Database to invalidate procedures and stored functions that access the table. For information on how and when the database invalidates such objects, see Oracle Database Development Guide

schema

Specify the schema containing the table. If you omit schema , then Oracle Database assumes the table is in your own schema.

table

Specify the name of the table to be altered.

Note: If you alter a table that is a master table for one or more materialized views, then Oracle Database marks the materialized views INVALID . Invalid materialized views cannot be used by query rewrite and cannot be refreshed. For information on revalidating a materialized view, see If you alter a table that is a master table for one or more materialized views, then Oracle Database marks the materialized views. Invalid materialized views cannot be used by query rewrite and cannot be refreshed. For information on revalidating a materialized view, see ALTER MATERIALIZED VIEW

Restrictions on Altering Temporary Tables You can modify, drop columns from, or rename a temporary table. However, for a temporary table you cannot:

Add columns of nested table type. You can add columns of other types.

Specify referential integrity (foreign key) constraints for an added or modified column.

Specify the following clauses of the LOB_storage_clause for an added or modified LOB column: TABLESPACE , storage_clause , logging_clause , allocate_extent_clause , or deallocate_unused_clause .

Specify the physical_attributes_clause , nested_table_col_properties , parallel_clause , allocate_extent_clause , deallocate_unused_clause , or any of the index-organized table clauses.

Exchange partitions between a partition and a temporary table.

Specify the logging_clause .

Specify MOVE.

Add an INVISIBLE column or modify an existing column to be INVISIBLE .

Restrictions on Altering External Tables You can add, drop, or modify the columns of an external table. However, for an external table you cannot:

Add a LONG , LOB, or object type column or change the data type of an external table column to any of these data types.

Add a constraint to an external table.

Modify the storage parameters of an external table.

Specify the logging_clause .

Specify MOVE .

Add an INVISIBLE column or modify an existing column to be INVISIBLE .

alter_table_properties

Use the alter_table_clauses to modify a database table.

physical_attributes_clause

The physical_attributes_clause lets you change the value of the PCTFREE , PCTUSED , and INITRANS parameters and storage characteristics. Refer to physical_attributes_clause and storage_clause for a full description of these parameters and characteristics.

Restrictions on Altering Table Physical Attributes Altering physical attributes is subject to the following restrictions:

You cannot specify the PCTUSED parameter for the index segment of an index-organized table.

If you attempt to alter the storage attributes of tables in locally managed tablespaces, then Oracle Database raises an error. However, if some segments of a partitioned table reside in a locally managed tablespace and other segments reside in a dictionary-managed tablespace, then the database alters the storage attributes of the segments in the dictionary-managed tablespace but does not alter the attributes of the segments in the locally managed tablespace, and does not raise an error.

For segments with automatic segment-space management, the database ignores attempts to change the PCTUSED setting. If you alter the PCTFREE setting, then you must subsequently run the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure to implement the new setting on blocks already allocated to the segment.

Cautions on Altering Tables Physical Attributes The values you specify in this clause affect the table as follows:

For a nonpartitioned table, the values you specify override any values specified for the table at create time.

For a range-, list-, or hash-partitioned table, the values you specify are the default values for the table and the actual values for every existing partition, overriding any values already set for the partitions. To change default table attributes without overriding existing partition values, use the modify_table_default_attrs clause.

For a composite-partitioned table, the values you specify are the default values for the table and all partitions of the table and the actual values for all subpartitions of the table, overriding any values already set for the subpartitions. To change default partition attributes without overriding existing subpartition values, use the modify_table_default_attrs clause with the FOR PARTITION clause.

logging_clause

Use the logging_clause to change the logging attribute of the table. The logging_clause specifies whether subsequent ALTER TABLE … MOVE and ALTER TABLE … SPLIT operations will be logged or not logged.

When used with the modify_table_default_attrs clause, this clause affects the logging attribute of a partitioned table.

See Also: logging_clause for a full description of this clause

Oracle Database VLDB and Partitioning Guide for more information about the logging_clause and parallel DML

table_compression

The table_compression clause is valid only for heap-organized tables. Use this clause to instruct Oracle Database whether to compress data segments to reduce disk and memory use. Refer to the CREATE TABLE table_compression for the full semantics of this clause and for information on creating objects with table compression.

Note: The first time a table is altered in such a way that compressed data will be added, all bitmap indexes and bitmap index partitions on that table must be marked UNUSABLE . The first time a table is altered in such a way that compressed data will be added, all bitmap indexes and bitmap index partitions on that table must be marked

inmemory_alter_table_clause

Use this clause to enable or disable a table or table column for the In-Memory Column Store (IM column store), or to change the in-memory parameters for a table or table column.

Specify INMEMORY to enable a table for the IM column store, or to change the inmemory_parameters for a table that is already enabled for the IM column store.

Specify the inmemory_column_clause to enable or disable a table column for the IM column store, or to change the inmemory_memcompress setting for a table column.

Specify NO INMEMORY to disable a table for the IM column store.

This inmemory_alter_table_clause has the same semantics as the inmemory_table_clause of CREATE TABLE with the following additions:

When you change the data compression method for a table that is already enabled for the IM column store, any columns that were previously assigned a specific data compression method will retain their data compression method.

When you disable a table for the IM column store, any column-level data compression method settings are lost. If you subsequently enable the table for the IM column store, all columns will use the table’s data compression method unless you specify otherwise when enabling the table.

The clauses inmemory_parameters and inmemory_column_clause have the same semantics in CREATE TABLE and ALTER TABLE . Refer to inmemory_parameters and inmemory_column_clause in the documentation on CREATE TABLE for the full semantics of these clauses.

inmemory_clause

Use this clause to enable or disable a table partition for the IM column store, or to change the in-memory parameters for a table partition. This clause has the same semantics in CREATE TABLE and ALTER TABLE . Refer to the inmemory_clause in the documentation on CREATE TABLE for the full semantics of this clause.

ilm_clause

Use this clause to add, delete, enable, or disable Automatic Data Optimization policies for the table.

ADD POLICY Specify this clause to add a policy for the table.

Use the ilm_policy_clause to specify the policy. Refer to the ilm_policy_clause for the full semantics of this clause.

Oracle Database assigns a name to the policy of the form P n , where n is an integer value.

{ DELETE | ENABLE | DISABLE } POLICY Specify these clauses to delete a policy for the table, enable a policy for the table, or disable a policy for the table, respectively.

For ilm_policy_name , specify the name of the policy. You can view policy names by querying the POLICY_NAME column of the DBA_ILMPOLICIES view.

{ DELETE_ALL, ENABLE_ALL, DISABLE_ALL } Specify these clauses to delete all policies for the table, enable all policies for the table, or disable all policies for the table, respectively.

ilm_policy_clause

This clause lets you specify an Automatic Data Optimization policy. You can use the ilm_compression_policy clause to specify a compression policy or the ilm_tiering_policy clause to specify a storage tiering policy.

ilm_compression_policy

Use this clause to specify a compression policy. This type of policy instructs the database to compress data when a specified condition is met. Use the SEGMENT , GROUP , or ROW clause to specify a segment-level, group-level, or row-level compression policy.

table_compression Use the table_compression clause to specify the compression type. This clause applies to segment-level and group-level compression policies.

You must specify a compression type that is higher than the current compression type. The order of compression types from lowest to highest is:

NOCOMPRESS

ROW

STORE

COMPRESS

BASIC

ROW

STORE

COMPRESS

ADVANCED

COLUMN

STORE

COMPRESS

FOR

QUERY

LOW

COLUMN

STORE

COMPRESS

FOR

QUERY

HIGH

COLUMN

STORE

COMPRESS

FOR

ARCHIVE

LOW

COLUMN

STORE

COMPRESS

FOR

ARCHIVE

HIGH

Refer to table_compression for the full semantics of this clause.

SEGMENT Specify SEGMENT to create a segment-level compression policy. This type of policy instructs the database to compress table segments when the condition specified in the AFTER clause is met or when the PL/SQL function specified in the ON clause returns TRUE .

GROUP Specify GROUP to create a group-level compression policy. This type of policy instructs the database to compress the table and its dependent objects, such as indexes and SecureFiles LOBs, when the condition specified in the AFTER clause is met or when the PL/SQL function specified in the ON clause returns TRUE .

ROW Specify ROW to create a row-level compression policy. This type of policy instructs the database to compress database blocks in which all the rows have not been modified for a specified period of time. When creating a row-level policy, you must specify ROW STORE COMPRESS ADVANCED compression and you must specify AFTER ilm_time_period OF NO MODIFICATION . Refer to table_compression for the full semantics of the ROW STORE COMPRESS ADVANCED clause.

AFTER Use this clause to describe the condition that must be met in order for the policy to take effect. The condition consists of a length of time, specified with the ilm_time_period clause, and one of the following condition types:

OF NO ACCESS : The policy will take effect after table has not been accessed for the specified length of time.

OF NO MODIFICATION : The policy will take effect after table has not been modified for the specified length of time.

OF CREATION : The policy will take effect when the specified length of time has passed since table was created.

ilm_time_period Specify a length of time in days, months, or years after which the condition must be met. For integer , specify a positive integer. The DAY and DAYS keywords can be used interchangeably and are provided for semantic clarity. This is also the case for the MONTH and MONTHS keywords, and the YEAR and YEARS keywords.

ON Use this clause to specify a PL/SQL function that returns a boolean value. For function_name , specify the name of the function. The policy will take effect when the function returns TRUE .

ilm_tiering_policy

Use this clause to specify a storage tiering policy. This type of policy instructs the database to migrate data to a specified tablespace, either when a specified condition is met or when data usage reaches a specified limit. Use the SEGMENT or GROUP clause to specify a segment-level or group-level policy. You can migrate data to a read/write tablespace or a read-only tablespace.

TIER TO tablespace Use this clause to migrate data to a read/write tablespace .

If you specify the ON function clause, then data will be migrated when function returns TRUE . Refer to the ON clause for the full semantics of this clause.

If you omit the ON function clause, then data will be migrated when data usage of the tablespace quota reaches the percentage defined by TBS_PERCENT_USED . The database will make a best effort to migrate enough data so that the amount of free space within the tablespace quota reaches the percentage defined by TBS_PERCENT_FREE . Refer to Oracle Database PL/SQL Packages and Types Reference for more information on TBS_PERCENT_USED and TBS_PERCENT_FREE , which are constants in the DBMS_ILM_ADMIN package.

TIER TO tablespace READ ONLY Use this clause to migrate data to a read-only tablespace . When migrating data to the tablespace, the database temporarily places the tablespace in read/write mode, migrates the data, and then places the tablespace back in read-only mode.

If you specify the AFTER clause, then data will be migrated when the specified condition is met. Refer to the AFTER clause for the full semantics of this clause

If you specify the ON function clause, then data will be migrated when function returns TRUE . Refer to the ON clause for the full semantics of this clause.

SEGMENT | GROUP Specify SEGMENT to create a segment-level storage tiering policy. This type of policy instructs the database to migrate table segments to tablespace . Specify GROUP to create a group-level storage tiering policy. This type of policy instructs the database to migrate the table and its dependent objects, such as indexes and SecureFiles LOBs, to tablespace . The default is SEGMENT .

supplemental_table_logging

Use the supplemental_table_logging clause to add or drop a redo log group or one or more supplementally logged columns in a redo log group.

In the ADD clause, use supplemental_log_grp_clause to create named supplemental log group. Use the supplemental_id_key_clause to create a system-generated log group.

On the DROP clause, use GROUP log_group syntax to drop a named supplemental log group and use the supplemental_id_key_clause to drop a system-generated log group.

The supplemental_log_grp_clause and the supplemental_id_key_clause have the same semantics in CREATE TABLE and ALTER TABLE statements. For full information on these clauses, refer to supplemental_log_grp_clause and supplemental_id_key_clause in the documentation on CREATE TABLE .

allocate_extent_clause

Use the allocate_extent_clause to explicitly allocate a new extent for the table, the partition or subpartition, the overflow data segment, the LOB data segment, or the LOB index.

Restriction on Allocating Table Extents You cannot allocate an extent for a temporary table or for a range- or composite-partitioned table.

deallocate_unused_clause

Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and make the space available for other segments in the tablespace.

CACHE | NOCACHE

The CACHE and NOCACHE clauses have the same semantics in CREATE TABLE and ALTER TABLE statements. For complete information on these clauses, refer to “CACHE | NOCACHE | CACHE READS” in the documentation on CREATE TABLE . If you omit both of these clauses in an ALTER TABLE statement, then the existing value is unchanged.

RESULT_CACHE

The RESULT_CACHE clause has the same semantics in CREATE TABLE and ALTER TABLE statements. For complete information on this clause, refer to “RESULT_CACHE Clause” in the documentation on CREATE TABLE . If you omit this clause in an ALTER TABLE statement, then the existing setting is unchanged.

upgrade_table_clause

The upgrade_table_clause is relevant for object tables and for relational tables with object columns. It lets you instruct Oracle Database to convert the metadata of the target table to conform with the latest version of each referenced type. If table is already valid, then the table metadata remains unchanged.

Restriction on Upgrading Object Tables and Columns Within this clause, you cannot specify object_type_col_properties as a clause of column_properties .

INCLUDING DATA Specify INCLUDING DATA if you want Oracle Database to convert the data in the table to the latest type version format. You can define the storage for any new column while upgrading the table by using the column_properties and the LOB_partition_storage . This is the default.

You can convert data in the table at the time you upgrade the type by specifying CASCADE INCLUDING TABLE DATA in the dependent_handling_clause of the ALTER TYPE statement. See Oracle Database PL/SQL Language Reference for information on this clause. For information on whether a table contains data based on an older type version, refer to the DATA_UPGRADED column of the USER_TAB_COLUMNS data dictionary view.

NOT INCLUDING DATA Specify NOT INCLUDING DATA if you want Oracle Database to leave column data unchanged.

Restriction on NOT INCLUDING DATA You cannot specify NOT INCLUDING DATA if the table contains columns in Oracle8 release 8.0.x image format. To determine whether the table contains such columns, refer to the V80_FMT_IMAGE column of the USER_TAB_COLUMNS data dictionary view.

See Also: Oracle Database Reference for information on the data dictionary views

ALTER TYPE for information on converting dependent table data when modifying a type upon which the table depends

records_per_block_clause

The records_per_block_clause lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.

Restrictions on Records in a Block The record_per_block_clause is subject to the following restrictions:

You cannot specify either MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. You must first drop the bitmap index.

You cannot specify this clause for an index-organized table or a nested table.

MINIMIZE Specify MINIMIZE to instruct Oracle Database to calculate the largest number of records in any block in the table and to limit future inserts so that no block can contain more than that number of records.

Oracle recommends that a representative set of data already exist in the table before you specify MINIMIZE . If you are using table compression (see table_compression ), then a representative set of compressed data should already exist in the table.

Restriction on MINIMIZE You cannot specify MINIMIZE for an empty table.

NOMINIMIZE Specify NOMINIMIZE to disable the MINIMIZE feature. This is the default.

row_movement_clause

You cannot disable row movement in a reference-partitioned table unless row movement is also disabled in the parent table. Otherwise, this clause has the same semantics in CREATE TABLE and ALTER TABLE statements. For complete information on these clauses, refer to row_movement_clause in the documentation on CREATE TABLE .

flashback_archive_clause

You must have the FLASHBACK ARCHIVE object privilege on the specified flashback data archive to specify this clause. Use this clause to enable or disable historical tracking for the table.

Specify FLASHBACK ARCHIVE to enable tracking for the table. You can specify flashback_archive to designate a particular flashback data archive for this table. The flashback data archive you specify much already exist. If you omit the archive name, then the database uses the default flashback data archive designated for the system. If no default flashback data archive has been designated for the system, then you must specify flashback_archive . You cannot specify FLASHBACK ARCHIVE to change the flashback data archive for this table. Instead you must first issue an ALTER TABLE statement with the NO FLASHBACK ARCHIVE clause and then issue an ALTER TABLE statement with the FLASHBACK ARCHIVE clause.

Specify NO FLASHBACK ARCHIVE to disable tracking for the table.

See Also: The CREATE TABLE The flashback_archive_clause for information on creating a table with tracking enabled and CREATE FLASHBACK ARCHIVE for information on creating default flashback data archives

RENAME TO

Use the RENAME clause to rename table to new_table_name .

Using this clause invalidates any dependent materialized views. For more information on materialized views, see CREATE MATERIALIZED VIEW and Oracle Database Data Warehousing Guide.

If a domain index is defined on the table, then the database invokes the ODCIIndexAlter() method with the RENAME option. This operation establishes correspondence between the indextype metadata and the base table.

shrink_clause

The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

Note: Do not attempt to enable row movement for an index-organized table before specifying the shrink_clause . The ROWID of an index-organized table is its primary key, which never changes. Therefore, row movement is neither relevant nor valid for such tables. Do not attempt to enable row movement for an index-organized table before specifying the. Theof an index-organized table is its primary key, which never changes. Therefore, row movement is neither relevant nor valid for such tables.

COMPACT If you specify COMPACT , then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE … SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

For an index or index-organized table, specifying ALTER [ INDEX | TABLE ] … SHRINK SPACE COMPACT is equivalent to specifying ALTER [ INDEX | TABLE … COALESCE . The shrink_clause can be cascaded (refer to the CASCADE clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not want to release the unused space, then you can use the appropriate COALESCE clause.

CASCADE If you specify CASCADE , then Oracle Database performs the same operations on all dependent objects of table , including secondary indexes on index-organized tables.

Restrictions on the shrink_clause The shrink_clause is subject to the following restrictions:

You cannot combine this clause with any other clauses in the same ALTER TABLE statement. You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.

Segment shrink is not supported for tables with function-based indexes, domain indexes, or bitmap join indexes.

This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE .

You can specify this clause for a table with Advanced Row Compression enabled ( ROW STORE COMPRESS ADVANCED ). You cannot specify this clause for a table with any other type of table compression enabled.

You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

READ ONLY | READ WRITE

Specify READ ONLY to put the table in read-only mode. When the table is in READ ONLY mode, you cannot issue any DML statements that affect the table or any SELECT … FOR UPDATE statements. You can issue DDL statements as long as they do not modify any table data. Operations on indexes associated with the table are allowed when the table is in READ ONLY mode.

Specify READ WRITE to return a read-only table to read/write mode.

REKEY encryption_spec

Use the REKEY clause to generate a new encryption key or to switch between different algorithms. This operation returns only after all encrypted columns in the table, including LOB columns, have been reencrypted.
[NO] ROW ARCHIVAL

Specify this clause to enable or disable table for row archival.

Specify ROW ARCHIVAL to enable table for row archival. A hidden column ORA_ARCHIVE_STATE is created in the table. If the table is already populated with data, then the value of ORA_ARCHIVE_STATE is set to 0 for each existing row in the table. You can subsequently use the UPDATE statement to set the value of ORA_ARCHIVE_STATE to 1 for rows you want to archive.

Specify NO ROW ARCHIVAL to disable table for row archival. The hidden column ORA_ARCHIVE_STATE is dropped from the table.

Restrictions on [NO] ROW ARCHIVAL The following restrictions apply to this clause:

You cannot specify the ROW ARCHIVAL clause for a table that already contains a column named ORA_ARCHIVE_STATE .

You cannot specify the NO ROW ARCHIVAL clause for tables owned by SYS .

See Also: The CREATE TABLE ROW ARCHIVAL clause for the full semantics of this clause

Oracle Database VLDB and Partitioning Guide for more information on In-Database Archiving

attribute_clustering_clause

Use the ADD attribute_clustering_clause to enable the table for attribute clustering. The attribute_clustering_clause has the same semantics for ALTER TABLE and CREATE TABLE . Refer to the attribute_clustering_clause in the documentation on CREATE TABLE .

MODIFY CLUSTERING

Use this clause to allow or disallow attribute clustering for the table during direct-path insert operations or data movement operations. The table must be enabled for attribute clustering. The clustering_when clause and the zonemap_clause have the same semantics for ALTER TABLE and CREATE TABLE . Refer to the clustering_when clause and the zonemap_clause in the documentation on CREATE TABLE .

DROP CLUSTERING

Use this clause to disable the table for attribute clustering.

If a zone map on the table was created using the WITH MATERIALIZED ZONEMAP clause of CREATE TABLE or ALTER TABLE , then the zone map will be dropped. If a zone map on the table was created using the CREATE MATERIALIZED ZONEMAP statement, then the zone map will not be dropped.

alter_iot_clauses

index_org_table_clause

This clause lets you alter some of the characteristics of an existing index-organized table. Index-organized tables keep data sorted on the primary key and are therefore best suited for primary-key-based access and manipulation. See index_org_table_clause in the context of CREATE TABLE .

prefix_compression

Use the prefix_compression clause to enable prefix compression for the table. Specify COMPRESS to instruct Oracle Database to combine the primary key index blocks of the index-organized table where possible to free blocks for reuse. You can specify this clause with the parallel_clause . Specify NOCOMPRESS to disable prefix compression for the table.

PCTTHRESHOLD integer Refer to “PCTTHRESHOLD integer” in the documentation on CREATE TABLE .

INCLUDING column_name Refer to “INCLUDING column_name” in the documentation on CREATE TABLE .

overflow_attributes

The overflow_attributes let you specify the overflow data segment physical storage and logging attributes to be modified for the index-organized table. Parameter values specified in this clause apply only to the overflow data segment.

See Also: CREATE TABLE

add_overflow_clause

The add_overflow_clause lets you add an overflow data segment to the specified index-organized table. You can also use this clause to explicitly allocate an extent to or deallocate unused space from an existing overflow segment.

Use the STORE IN tablespace clause to specify tablespace storage for the entire overflow segment. Use the PARTITION clause to specify tablespace storage for the segment by partition.

For a partitioned index-organized table:

If you do not specify PARTITION , then Oracle Database automatically allocates an overflow segment for each partition. The physical attributes of these segments are inherited from the table level.

If you want to specify separate physical attributes for one or more partitions, then you must specify such attributes for every partition in the table. You need not specify the name of the partitions, but you must specify their attributes in the order in which they were created.

You can find the order of the partitions by querying the PARTITION_NAME and PARTITION_POSITION columns of the USER_IND_PARTITIONS view.

If you do not specify TABLESPACE for a particular partition, then the database uses the tablespace specified for the table. If you do not specify TABLESPACE at the table level, then the database uses the tablespace of the partition primary key index segment.

Restrictions on Overflow Attributes Within the segment_attributes_clause :

You cannot specify the OPTIMAL parameter of the physical_attributes_clause .

You cannot specify tablespace storage for the overflow segment using this clause. For a nonpartitioned table, you can use ALTER TABLE … MOVE … OVERFLOW to move the segment to a different tablespace. For a partitioned table, use ALTER TABLE … MODIFY DEFAULT ATTRIBUTES … OVERFLOW to change the default tablespace of the overflow segment.

Additional restrictions apply if table is in a locally managed tablespace, because in such tablespaces several segment attributes are managed automatically by the database.

See Also: add_overflow_clause allocate_extent_clause and deallocate_unused_clause for full descriptions of these clauses of the

alter_overflow_clause

The alter_overflow_clause lets you change the definition of the overflow segment of an existing index-organized table.

The restrictions that apply to the add_overflow_clause also apply to the alter_overflow_clause .

Note: When you add a column to an index-organized table, Oracle Database evaluates the maximum size of each column to estimate the largest possible row. If an overflow segment is needed but you have not specified OVERFLOW , then the database raises an error and does not execute the ALTER TABLE statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking. When you add a column to an index-organized table, Oracle Database evaluates the maximum size of each column to estimate the largest possible row. If an overflow segment is needed but you have not specified, then the database raises an error and does not execute thestatement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking.

alter_mapping_table_clauses

The alter_mapping_table_clauses is valid only if table is index organized and has a mapping table.

allocate_extent_clause Use the allocate_extent_clause to allocate a new extent at the end of the mapping table for the index-organized table. Refer to allocate_extent_clause for a full description of this clause.

deallocate_unused_clause Specify the deallocate_unused_clause to deallocate unused space at the end of the mapping table of the index-organized table. Refer to deallocate_unused_clause for a full description of this clause.

Oracle Database automatically maintains all other attributes of the mapping table or its partitions.

COALESCE Clause

Specify COALESCE to instruct Oracle Database to merge the contents of index blocks of the index the database uses to maintain the index-organized table where possible to free blocks for reuse. Refer to the shrink_clause for information on the relationship between these two clauses.

alter_XMLSchema_clause

This clause is valid as part of alter_table_properties only if you are modifying an XMLType table with BINARY XML storage. Refer to XMLSchema_spec in the documentation on CREATE TABLE for more information on the ALLOW and DISALLOW clauses.

column_clauses

Use these clauses to add, drop, or otherwise modify a column.

add_column_clause

The add_column_clause lets you add a column to a table.

column_definition

Unless otherwise noted in this section, the elements of column_definition have the same behavior when adding a column to an existing table as they do when creating a new table. Refer to column_definition for information.

Restriction on column_definition The SORT parameter is valid only when creating a new table. You cannot specify SORT in the column_definition of an ALTER TABLE … ADD statement.

When you add a column, the initial value of each row for the new column is null, unless you specify the DEFAULT clause.

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

You can add LOB columns to nonpartitioned and partitioned tables. You can specify LOB storage at the table and at the partition or subpartition level.

If you previously created a view with a query that used the SELECT * syntax to select all columns from table, and you now add a column to table , then the database does not automatically add the new column to the view. To add the new column to the view, re-create the view using the CREATE VIEW statement with the OR REPLACE clause. Refer to CREATE VIEW for more information.

Restrictions on Adding Columns The addition of columns is subject to the following restrictions:

You cannot add a LOB column or an INVISIBLE column to a cluster table.

If you add a LOB column to a hash-partitioned table, then the only attribute you can specify for the new partition is TABLESPACE .

You cannot add a column with a NOT NULL constraint if table has any rows unless you also specify the DEFAULT clause.

If you specify this clause for an index-organized table, then you cannot specify any other clauses in the same statement.

DEFAULT

Use the DEFAULT clause to specify a default for a new column or a new default for an existing column. Oracle Database assigns this value to the column if a subsequent INSERT statement omits a value for the column.

The data type of the expression must match the data type specified for the column. The column must also be large enough to hold this expression.

The DEFAULT expression can include any SQL function as long as the function does not return a literal argument, a column reference, or a nested function invocation.

The DEFAULT expression can include the sequence pseudocolumns CURRVAL and NEXTVAL , as long as the sequence exists and you have the privileges necessary to access it. Users who perform subsequent inserts that use the DEFAULT expression must have the INSERT privilege on the table and the SELECT privilege on the sequence. If the sequence is later dropped, then subsequent insert statements where the DEFAULT expression is used will result in an error. If you are adding a new column to a table, then the order in which NEXTVAL is assigned to each existing row is nondeterministic. If you do not fully qualify the sequence by specifying the sequence owner, for example, SCOTT . SEQ1 , then Oracle Database will default the sequence owner to be the user who issues the ALTER TABLE statement. For example, if user MARY adds a column to SCOTT . TABLE and refers to a sequence that is not fully qualified, such as SEQ2 , then the column will use sequence MARY . SEQ2 . Synonyms on sequences undergo a full name resolution and are stored as the fully qualified sequence in the data dictionary; this is true for public and private synonyms. For example, if user BETH adds a column referring to public or private synonym SYN1 and the synonym refers to PETER . SEQ7 , then the column will store PETER . SEQ7 as the default.

If you specify the DEFAULT clause for a column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set. This optimized behavior is subject to the following restrictions:

The table cannot have any LOB columns. It cannot be index-organized, temporary, or part of a cluster. It also cannot be a queue table, an object table, or the container table of a materialized view.

If the table has a Virtual Private Database (VPD) policy on it, then the optimized behavior will not take place unless the user who issues the ALTER TABLE … ADD statement has the EXEMPT ACCESS POLICY system privilege.

The column being added cannot be encrypted, and cannot be an object column, nested table column, or a LOB column.

The DEFAULT expression cannot include the sequence pseudocolumns CURRVAL or NEXTVAL .

If the optimized behavior cannot take place due to the preceding restrictions, then Oracle Database updates each row in the newly created column with the default value. In this case, the database does not fire any UPDATE triggers that are defined on the table.

Restrictions on Default Column Values Default column values are subject to the following restrictions:

A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL , PRIOR , and ROWNUM , or date constants that are not fully specified.

The expression can be of any form except a scalar subquery expression.

ON NULL If you specify the ON NULL clause, then Oracle Database assigns the DEFAULT column value when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

When you specify ON NULL , the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified. If you specify an inline constraint that conflicts with NOT NULL and NOT DEFERRABLE , then an error is raised.

identity_clause

The identity_clause has the same semantics when you add an identity column that it has when you create an identity column. Refer to CREATE TABLE identity_clause for more information.

When you add a new identity column to a table, all existing rows are updated using the sequence generator. The order in which a value is assigned to each existing row is nondeterministic.

identity_options

Use the identity_options clause to configure the sequence generator. The identity_options clause has the same parameters as the CREATE SEQUENCE statement. Refer to CREATE SEQUENCE for a full description of these parameters and characteristics. The exception is START WITH LIMIT VALUE , which is specific to identity_options and can only be used with ALTER TABLE MODIFY . Refer to identity_options for more information.

inline_constraint

Use inline_constraint to add a constraint to the new column.

inline_ref_constraint

This clause lets you describe a new column of type REF . Refer to constraint for syntax and description of this type of constraint, including restrictions.

virtual_column_definition

The virtual_column_definition has the same semantics when you add a column that it has when you create a column.

Restriction on Adding a Virtual Column You cannot add a virtual column when the SQL expression for the virtual column involves a column on which an Oracle Data Redaction policy is defined.

column_properties

The clauses of column_properties determine the storage characteristics of an object type, nested table, varray, or LOB column.

object_type_col_properties This clause is valid only when you are adding a new object type column or attribute. To modify the properties of an existing object type column, use the modify_column_clauses . The semantics of this clause are the same as for CREATE TABLE unless otherwise noted.

Use the object_type_col_properties clause to specify storage characteristics for a new object column or attribute or an element of a collection column or attribute.

For complete information on this clause, refer to object_type_col_properties in the documentation on CREATE TABLE .

nested_table_col_properties The nested_table_col_properties clause lets you specify separate storage characteristics for a nested table, which in turn lets you to define the nested table as an index-organized table. You must include this clause when creating a table with columns or column attributes whose type is a nested table. (Clauses within this clause that function the same way they function for parent object tables are not repeated here. See the CREATE TABLE clause nested_table_col_properties for more information about these clauses.)

For nested_item , specify the name of a column (or a top-level attribute of the nested table object type) whose type is a nested table. If the nested table is a multilevel collection, and the inner nested table does not have a name, then specify COLUMN_VALUE in place of the nested_item name.

For storage_table , specify the name of the table where the rows of nested_item reside. The storage table is created in the same schema and the same tablespace as the parent table.

Restrictions on Nested Table Column Properties Nested table column properties are subject to the following restrictions:

You cannot specify the parallel_clause .

You cannot specify CLUSTER as part of the physical_properties clause.

varray_col_properties The varray_col_properties clause lets you specify separate storage characteristics for the LOB in which a varray will be stored. If you specify this clause, then Oracle Database will always store the varray in a LOB, even if it is small enough to be stored inline. If varray_item is a multilevel collection, then the database stores all collection items nested within varray_item in the same LOB in which varray_item is stored.

Restriction on Varray Column Properties You cannot specify TABLESPACE as part of LOB_parameters for a varray column. The LOB tablespace for a varray defaults to the tablespace of the containing table.

out_of_line_part_storage

This clause lets you specify storage attributes the newly added column for each partition or subpartition in a partitioned table. For any partition or subpartition you do not name in this clause, the storage attributes for the new column are the same as those specified in the nested_table_col_properties at the table level.

LOB_storage_clause

Use the LOB_storage_clause to specify the LOB storage characteristics for a newly added LOB column, LOB partition, or LOB subpartition, or when you are converting a LONG column into a LOB column. You cannot use this clause to modify an existing LOB. Instead, you must use the modify_LOB_storage_clause.

Unless otherwise noted in this section, all LOB parameters, in both the LOB_storage_clause and the modify_LOB_storage_clause , have the same semantics in an ALTER TABLE statement that they have in a CREATE TABLE statement. Refer to the CREATE TABLE LOB_storage_clause for complete information on this clause.

Restriction on LOB Parameters The only parameter of LOB_parameters you can specify for a hash partition or hash subpartition is TABLESPACE .

CACHE READS Clause When you add a new LOB column, you can specify the logging attribute with CACHE READS , as you can when defining a LOB column at create time. Refer to the CREATE TABLE clause CACHE READS for full information on this clause.

ENABLE | DISABLE STORAGE IN ROW You cannot change STORAGE IN ROW once it is set. Therefore, you cannot specify this clause as part of the modify_col_properties clause. However, you can change this setting when adding a new column (add_column_clause ) or when moving the table (move_table_clause ). Refer to the CREATE TABLE clause ENABLE STORAGE IN ROW for complete information on this clause.

CHUNK integer You use cannot use the modify_col_properties clause to change the value of CHUNK after it has been set. If you require a different CHUNK value for a column after it has been created, use ALTER TABLE … MOVE . Refer to the CREATE TABLE clause CHUNK integer for more information.

RETENTION For BasicFiles LOBs, if the database is in automatic undo mode, then you can specify RETENTION instead of PCTVERSION to instruct Oracle Database to retain old versions of this LOB. This clause overrides any prior setting of PCTVERSION . Refer to the CREATE TABLE clause LOB_retention_clause for a full description of this parameter.

FREEPOOLS integer For BasicFiles LOBs, if the database is in automatic undo mode, then you can use this clause to specify the number of freelist groups for this LOB. This clause overrides any prior setting of FREELIST GROUPS . Refer to the CREATE TABLE clause FREEPOOLS integer for a full description of this parameter. The database ignores this parameter for SecureFiles LOBs.

LOB_partition_storage

You can specify only one list of LOB_partition_storage clauses in a single ALTER TABLE statement, and all LOB_storage_clauses and varray_col_properties clause must precede the list of LOB_partition_storage clauses. Refer to the CREATE TABLE clause LOB_partition_storage for full information on this clause, including restrictions.

XMLType_column_properties Refer to the CREATE TABLE clause XMLType_column_properties for a full description of this clause.

modify_column_clauses

Use the modify_column_clauses to modify the properties of an existing column, the visibility of an existing column, or the substitutability of an existing object type column.

modify_col_properties

Use this clause to modify the properties of the column. Any of the optional parts of the column definition (data type, default value, or constraint) that you omit from this clause remain unchanged.

datatype You can change the data type of any column if all rows of the column contain nulls. However, if you change the data type of a column in a materialized view container table, then Oracle Database invalidates the corresponding materialized view.

You can omit the data type only if the statement also designates the column as part of the foreign key of a referential integrity constraint. The database automatically assigns the column the same data type as the corresponding column of the referenced key of the referential integrity constraint.

You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the rows contain nulls. You can reduce the size of a data type of a column as long as the change does not require data to be modified. The database scans existing data and returns an error if data exists that exceeds the new length limit.

When you increase the size of a VARCHAR2 , NVARCHAR2 , or RAW column to exceed 4000 bytes, Oracle Database performs an in-place length extension and does not migrate the inline storage to external LOB storage. This enables uninterrupted migration of large tables, especially after migration, to leverage extended data types. However, the inline storage of the column will not be preserved during table reorganization operations, such as CREATE TABLE … AS SELECT , export, import, or online redefinition. To migrate to the new out-of-line storage of extended data type columns, you must recreate the table using one of the aforementioned methods. The inline storage of the column will be preserved during table or partition movement operations, such as ALTER TABLE MOVE [[SUB]PARTITION] , and partition maintenance operations, such as ALTER TABLE SPLIT [SUB]PARTITION , ALTER TABLE MERGE [SUB]PARTITIONS , and ALTER TABLE COALESCE [SUB]PARTITIONS .

Note: Oracle recommends against excessively increasing the size of a VARCHAR2 , NVARCHAR2 , or RAW column beyond 4000 bytes for the following reasons: Row chaining may occur.

Data that is stored inline must be read in its entirety, whether a column is selected or not. Therefore, extended data type columns that are stored inline can have a negative impact on performance. Oracle recommends against excessively increasing the size of a, orcolumn beyond 4000 bytes for the following reasons:

You can reduce the size of a data type of a column as long as the change does not require data to be modified. The database scans existing data and returns an error if data exists that exceeds the new length limit.

You can change a DATE column to a TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE column, and you can change a TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE column to a DATE column. The following rules apply:

When you change a TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE column to a DATE column, Oracle Database updates each column value that has non-zero fractional seconds by rounding the value to the nearest second. If, while updating such a value, Oracle Database encounters a minute field greater than or equal to 60 (which can occur in a boundary case when the daylight saving rule switches), then it updates the minute field by subtracting 60 from it.

After you change a TIMESTAMP WITH LOCAL TIME ZONE column to a DATE column, the values in the column still represent the local time that they represented in the database time zone. However, the database time zone is no longer associated with the values. When queried in SQL*Plus, the values are no longer automatically adjusted to the session time zone. It is now the responsibility of applications processing the column values to interpret them in a particular time zone.

If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.

You can use the TO_LOB function to change a LONG column to a CLOB or NCLOB column, and a LONG RAW column to a BLOB column. However, you cannot use the TO_LOB function from within a PL/SQL package. Instead use the TO_CLOB or TO_BLOB functions.

The modified LOB column inherits all constraints and triggers that were defined on the original LONG column. If you want to change any constraints, then you must do so in a subsequent ALTER TABLE statement.

If any domain indexes are defined on the LONG column, then you must drop them before modifying the column to a LOB.

After the modification, you will have to rebuild all other indexes on all columns of the table.

You can use the TO_CLOB function to convert NCLOB columns CLOB columns.

See Also: Oracle Database SecureFiles and Large Objects Developer’s Guide for information on LONG to LOB migration

ALTER INDEX for information on dropping and rebuilding indexes

For CHAR and VARCHAR2 columns, you can change the length semantics by specifying CHAR (to indicate character semantics for a column that was originally specified in bytes) or BYTE (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the CHAR_USED column of the ALL_ , USER_ , or DBA_TAB_COLUMNS data dictionary view.

See Also: Oracle Database Globalization Support Guide for information on byte and character semantics

Oracle Database Reference for information on the data dictionary views

identity_clause Use identity_clause to modify the properties of an identity column. You cannot specify this clause on a column that is not an identity column. If you do not specify ALWAYS or BY DEFAULT , then the current generation type is retained. Refer to CREATE TABLE identity_clause for more information on ALWAYS and BY DEFAULT .

identity_options Use the identity_options clause to configure the sequence generator. The identity_options clause has the same parameters as the CREATE SEQUENCE statement. Refer to CREATE SEQUENCE for a full description of these parameters and characteristics. The exceptions are:

START WITH LIMIT VALUE , which is specific to identity_options , can only be used with ALTER TABLE MODIFY . If you specify START WITH LIMIT VALUE , then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator’s high water mark. The next value returned by the sequence generator will be the high water mark + INCREMENT BY integer for increasing sequences, or the high water mark – INCREMENT BY integer for decreasing sequences.

If you change the value of START WITH , then the default values will be used for all other parameters in this clause unless you specify otherwise.

DROP IDENTITY Use this clause to remove the identity property from a column, including the sequence generator and NOT NULL and NOT DEFERRABLE constraints. Identity column values in existing rows are not affected.

ENCRYPT encryption_spec | DECRYPT Use this clause to decrypt an encrypted column, to encrypt an unencrypted column, or to change the integrity algorithm or the SALT option of an encrypted column.

When encrypting an existing column, if you specify encryption_spec , it must match the encryption specification of any other encrypted columns in the same table. Refer to the CREATE TABLE clause encryption_spec for additional information and restrictions on the encryption_spec .

If a materialized view log is defined on the table, then Oracle Database encrypts or decrypts in the materialized view log any columns you encrypt or decrypt in this clause.

Restrictions on ENCRYPT encryption_spec | DECRYPT: This clause is subject to the following restrictions:

If the new or existing column is a LOB column, then it must be stored as a SecureFiles LOB, and you cannot specify the SALT option.

You cannot encrypt or decrypt a column on which a fine-grained audit policy for the UPDATE statement is enabled. However, you can disable the fine-grained audit policy, encrypt or decrypt the column, and then enable the fine-grained audit policy.

inline_constraint This clause lets you add a constraint to a column you are modifying. To change the state of existing constraints on existing columns, use the constraint_clauses .

LOB_storage_clause The LOB_storage_clause is permitted within modify_col_properties only if you are converting a LONG column to a LOB column. In this case only, you can specify LOB storage for the column using the LOB_storage_clause . However, you can specify only the single column as a LOB_item . Default LOB storage attributes are used for any attributes you omit in the LOB_storage_clause .

alter_XMLSchema_clause This clause is valid within modify_col_properties only for XMLType tables with BINARY XML storage. Refer to XMLSchema_spec in the documentation on CREATE TABLE for more information on the ALLOW and DISALLOW clauses.

Restrictions on Modifying Column Properties The modification of column properties is subject to the following restrictions:

You cannot change the data type of a LOB column.

You cannot modify a column of a table if a domain index is defined on the column. You must first drop the domain index and then modify the column.

You cannot modify the data type or length of a column that is part of the partitioning or subpartitioning key of a table or index.

You can change a CHAR column to VARCHAR2 (or VARCHAR ) and a VARCHAR2 (or VARCHAR ) column to CHAR only if the BLANK_TRIMMING initialization parameter is set to TRUE and the column size stays the same or increases. If the BLANK_TRIMMING initialization parameter is set to TRUE , then you can also reduce the column size to any size greater than or equal to the maximum trimmed data value.

You cannot change a LONG or LONG RAW column to a LOB if the table is part of a cluster. If you do change a LONG or LONG RAW column to a LOB, then the only other clauses you can specify in this ALTER TABLE statement are the DEFAULT clause and the LOB_storage_clause .

You can specify the LOB_storage_clause as part of modify_col_properties only when you are changing a LONG or LONG RAW column to a LOB.

You cannot specify a column of data type ROWID for an index-organized table, but you can specify a column of type UROWID .

You cannot change the data type of a column to REF .

See Also: ALTER MATERIALIZED VIEW for information on revalidating a materialized view

modify_virtcol_properties

This clause is valid only for virtual columns that refer to editioned PL/SQL functions. You can use this clause to modify the evaluation edition or the unusable editions for the virtual column. The evaluation_edition_clause and the unusable_editions_clause have the same semantics when you modify a virtual column that they have when you create a virtual column. For complete information, refer to evaluation_edition_clause and unusable_editions_clause in the documentation on CREATE TABLE .

Restriction on Modifying Virtual Columns

If an index is defined on a virtual column and you modify its evaluation edition or unusable editions, then the database will invalidate all indexes on the virtual column. If you attempt to modify any other properties of the virtual column, then an error occurs.

modify_col_visibility

Use this clause to change the visibility of column . For complete information, refer to “VISIBLE | INVISIBLE” in the documentation on CREATE TABLE .

Restriction on Modifying Column Visibility You cannot change a VISIBLE column to INVISIBLE in a table owned by SYS .

modify_col_substitutable

Use this clause to set or change the substitutability of an existing object type column.

The FORCE keyword drops any hidden columns containing typeid information or data for subtype attributes. You must specify FORCE if the column or any attributes of its type are not FINAL .

Restrictions on Modifying Column Substitutability The modification of column substitutability is subject to the following restrictions:

You can specify this clause only once in any ALTER TABLE statement.

You cannot modify the substitutability of a column in an object table if the substitutability of the table itself has been set.

You cannot specify this clause if the column was created or added using the IS OF TYPE syntax, which limits the range of subtypes permitted in an object column or attribute to a particular subtype. Refer to substitutable_column_clause in the documentation on CREATE TABLE for information on the IS OF TYPE syntax.

You cannot change a varray column to NOT SUBSTITUTABLE , even by specifying FORCE , if any of its attributes are nested object types that are not FINAL .

drop_column_clause

The drop_column_clause lets you free space in the database by dropping columns you no longer need or by marking them to be dropped at a future time when the demand on system resources is less.

If you drop a nested table column, then its storage table is removed.

If you drop a LOB column, then the LOB data and its corresponding LOB index segment are removed.

If you drop a BFILE column, then only the locators stored in that column are removed, not the files referenced by the locators.

If you drop or mark unused a column defined as an INCLUDING column, then the column stored immediately before this column will become the new INCLUDING column.

SET UNUSED Clause

Specify SET UNUSED to mark one or more columns as unused. For an internal heap-organized table, specifying this clause does not actually remove the target columns from each row in the table. It does not restore the disk space used by these columns. Therefore, the response time is faster than when you execute the DROP clause.

When you specify this clause for a column in an external table, the clause is transparently converted to an ALTER TABLE … DROP COLUMN statement. The reason for this is that any operation on an external table is a metadata-only operation, so there is no difference in the performance of the two commands.

You can view all tables with columns marked UNUSED in the data dictionary views USER_UNUSED_COL_TABS , DBA_UNUSED_COL_TABS , and ALL_UNUSED_COL_TABS .

See Also: Oracle Database Reference for information on the data dictionary views

Unused columns are treated as if they were dropped, even though their column data remains in the table rows. After a column has been marked UNUSED , you have no access to that column. A SELECT * query will not retrieve data from unused columns. In addition, the names and types of columns marked UNUSED will not be displayed during a DESCRIBE , and you can add to the table a new column with the same name as an unused column.

Note: Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table. However, as with all DDL statements, you cannot roll back the results of this clause. You cannot issue SET USED counterpart to retrieve a column that you have SET UNUSED . Refer to Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table. However, as with all DDL statements, you cannot roll back the results of this clause. You cannot issuecounterpart to retrieve a column that you have. Refer to CREATE TABLE for more information on the 1000-column limit. Also, if you mark a LONG column as UNUSED , then you cannot add another LONG column to the table until you actually drop the unused LONG column.

ONLINE Specify ONLINE to indicate that DML operations on the table will be allowed while marking the column or columns UNUSED .

Restrictions on Marking Columns Unused The following restrictions apply to the SET UNUSED clause:

You cannot specify the ONLINE clause when marking a column with a DEFERRABLE constraint as UNUSED .

Columns in tables owned by SYS cannot be marked as UNUSED .

DROP Clause

Specify DROP to remove the column descriptor and the data associated with the target column from each row in the table. If you explicitly drop a particular column, then all columns currently marked UNUSED in the target table are dropped at the same time.

When the column data is dropped:

All indexes defined on any of the target columns are also dropped.

All constraints that reference a target column are removed.

If any statistics types are associated with the target columns, then Oracle Database disassociates the statistics from the column with the FORCE option and drops any statistics collected using the statistics type.

Note: If the target column is a parent key of a nontarget column, or if a check constraint references both the target and nontarget columns, then Oracle Database returns an error and does not drop the column unless you have specified the CASCADE CONSTRAINTS clause. If you have specified that clause, then the database removes all constraints that reference any of the target columns. If the target column is a parent key of a nontarget column, or if a check constraint references both the target and nontarget columns, then Oracle Database returns an error and does not drop the column unless you have specified theclause. If you have specified that clause, then the database removes all constraints that reference any of the target columns.

See Also: DISASSOCIATE STATISTICS for more information on disassociating statistics types

DROP UNUSED COLUMNS Clause

Specify DROP UNUSED COLUMNS to remove from the table all columns currently marked as unused. Use this statement when you want to reclaim the extra disk space from unused columns in the table. If the table contains no unused columns, then the statement returns with no errors.

column Specify one or more columns to be set as unused or dropped. Use the COLUMN keyword only if you are specifying only one column. If you specify a column list, then it cannot contain duplicates.

CASCADE CONSTRAINTS Specify CASCADE CONSTRAINTS if you want to drop all foreign key constraints that refer to the primary and unique keys defined on the dropped columns as well as all multicolumn constraints defined on the dropped columns. If any constraint is referenced by columns from other tables or remaining columns in the target table, then you must specify CASCADE CONSTRAINTS . Otherwise, the statement aborts and an error is returned.

INVALIDATE The INVALIDATE keyword is optional. Oracle Database automatically invalidates all dependent objects, such as views, triggers, and stored program units. Object invalidation is a recursive process. Therefore, all directly dependent and indirectly dependent objects are invalidated. However, only local dependencies are invalidated, because the database manages remote dependencies differently from local dependencies.

An object invalidated by this statement is automatically revalidated when next referenced. You must then correct any errors that exist in that object before referencing it.

CHECKPOINT Specify CHECKPOINT if you want Oracle Database to apply a checkpoint for the DROP COLUMN operation after processing integer rows; integer is optional and must be greater than zero. If integer is greater than the number of rows in the table, then the database applies a checkpoint after all the rows have been processed. If you do not specify integer , then the database sets the default of 512. Checkpointing cuts down the amount of undo logs accumulated during the DROP COLUMN operation to avoid running out of undo space. However, if this statement is interrupted after a checkpoint has been applied, then the table remains in an unusable state. While the table is unusable, the only operations allowed on it are DROP TABLE , TRUNCATE TABLE , and ALTER TABLE DROP … COLUMNS CONTINUE (described in sections that follow).

You cannot use this clause with SET UNUSED , because that clause does not remove column data.

DROP COLUMNS CONTINUE Clause

Specify DROP COLUMNS CONTINUE to continue the drop column operation from the point at which it was interrupted. Submitting this statement while the table is in an invalid state results in an error.

Restrictions on Dropping Columns Dropping columns is subject to the following restrictions:

Each of the parts of this clause can be specified only once in the statement and cannot be mixed with any other ALTER TABLE clauses. For example, the following statements are not allowed: ALTER TABLE t1 DROP COLUMN f1 DROP (f2); ALTER TABLE t1 DROP COLUMN f1 SET UNUSED (f2); ALTER TABLE t1 DROP (f1) ADD (f2 NUMBER); ALTER TABLE t1 SET UNUSED (f3) ADD (CONSTRAINT ck1 CHECK (f2 > 0));

You can drop an object type column only as an entity. To drop an attribute from an object type column, use the ALTER TYPE … DROP ATTRIBUTE statement with the CASCADE INCLUDING TABLE DATA clause. Be aware that dropping an attribute affects all dependent objects. See Oracle Database PL/SQL Language Reference for more information.

You can drop a column from an index-organized table only if it is not a primary key column. The primary key constraint of an index-organized table can never be dropped, so you cannot drop a primary key column even if you have specified CASCADE CONSTRAINTS .

You can export tables with dropped or unused columns. However, you can import a table only if all the columns specified in the export files are present in the table (none of those columns has been dropped or marked unused). Otherwise, Oracle Database returns an error.

You can set unused a column from a table that uses COMPRESS BASIC , but you cannot drop the column. However, all clauses of the drop_column_clause are valid for tables that use ROW STORE COMPRESS ADVANCED . See the semantics for table_compression for more information.

You cannot drop a column on which a domain index has been built.

You cannot drop a SCOPE table constraint or a WITH ROWID constraint on a REF column.

You cannot use this clause to drop: A pseudocolumn, cluster column, or partitioning column. You can drop nonpartitioning columns from a partitioned table if all the tablespaces where the partitions were created are online and in read/write mode. A column from a nested table, an object table, or a table owned by SYS .

add_period_clause

Use the add_period_clause to add a valid time dimension to table .

The period_definition clause of ALTER TABLE has the same semantics as in CREATE TABLE , with the following exceptions and additions:

valid_time_column must not already exist in table .

If you specify start_time_column and end_time_column , then these columns must already exist in table or you must specify the add_column_clause for each of these columns.

If you specify start_time_column and end_time_column and these columns already exist in table and are populated with data, then for all rows where both columns have non-NULL values, the value of start_time_column must be earlier than the value of end_time_column .

See Also: CREATE TABLE period_definition for the full semantics of this clause

drop_period_clause

Use the drop_period_clause to drop a valid time dimension from table .

For valid_time_column , specify the name of the valid time dimension you want to drop.

This clause has the following effects:

The valid_time_column will be dropped from table .

If the start time column and end time column were automatically created by Oracle Database when the valid time dimension was created, either with CREATE TABLE … period_definition or ALTER TABLE … add_period_clause , then they will be dropped. Otherwise, these columns will remain in table and revert to regular table columns.

See Also: CREATE TABLE valid_time_column , start time column, and end time column period_definition for more information on the, start time column, and end time column

rename_column_clause

Use the rename_column_clause to rename a column of table . The new column name must not be the same as any other column name in table .

When you rename a column, Oracle Database handles dependent objects as follows:

Function-based indexes and check constraints that depend on the renamed column remain valid.

Dependent views, triggers, functions, procedures, and packages are invalidated. Oracle Database attempts to revalidate them when they are next accessed, but you may need to alter these objects with the new column name if revalidation fails.

If a domain index is defined on the column being renamed, then the database invokes the ODCIIndexAlter method with the RENAME option. This operation establishes correspondence between the indextype metadata and the base table

Restrictions on Renaming Columns Renaming columns is subject to the following restrictions:

You cannot combine this clause with any of the other column_clauses in the same statement.

You cannot rename a column that is used to define a join index. Instead you must drop the index, rename the column, and re-create the index.

modify_collection_retrieval

Use the modify_collection_retrieval clause to change what Oracle Database returns when a collection item is retrieved from the database.

collection_item Specify the name of a column-qualified attribute whose type is nested table or varray.

RETURN AS Specify what Oracle Database should return as the result of a query:

LOCATOR specifies that a unique locator for the nested table is returned.

VALUE specifies that a copy of the nested table itself is returned. See Also: “Collection Retrieval: Example”

modify_LOB_storage_clause

The modify_LOB_storage_clause lets you change the physical attributes of LOB_item . You can specify only one LOB_item for each modify_LOB_storage_clause .

The sections that follow describe the semantics of parameters specific to modify_LOB_parameters. Unless otherwise documented in this section, the remaining LOB parameters have the same semantics when altering a table that they have when you are creating a table. Refer to the restrictions at the end of this section and to the CREATE TABLE clause LOB_storage_parameters for more information.

Notes: You can modify LOB storage with an ALTER TABLE statement or with online redefinition by using the DBMS_REDEFINITION package. If you have not enabled LOB encryption, compression, or deduplication at create time, Oracle recommends that you use online redefinition to enable them after creation, as this process is more disk space efficient for changes to these three parameters. See Oracle Database PL/SQL Packages and Types Reference for more information on DBMS_REDEFINITION .

You cannot convert a LOB from one type of storage to the other. Instead you must migrate to SecureFiles or BasicFiles by using online redefinition or partition exchange.

PCTVERSION integer Refer to the CREATE TABLE clause PCTVERSION integer for information on this clause.

LOB_retention_clause If the database is in automatic undo mode, then you can specify RETENTION instead of PCTVERSION to instruct Oracle Database to retain old versions of this LOB. This clause overrides any prior setting of PCTVERSION .

FREEPOOLS integer For BasicFiles LOBs, if the database is in automatic undo mode, then you can use this clause to specify the number of freelist groups for this LOB. This clause overrides any prior setting of FREELIST GROUPS . Refer to the CREATE TABLE clause FREEPOOLS integer for a full description of this parameter. The database ignores this parameter for SecureFiles LOBs.

REBUILD FREEPOOLS This clause applies only to BasicFiles LOBs, not to SecureFiles LOBs. The REBUILD FREEPOOLS clause removes all the old versions of data from the LOB column. This clause is useful for removing all retained old version space in a LOB segment, freeing that space to be used immediately by new LOB data.

LOB_deduplicate_clause This clause is valid only for SecureFiles LOBs. KEEP_DUPLICATES disables LOB deduplication. DEDUPLICATE enables LOB deduplication. All lobs in the segment are read, and any matching LOBs are deduplicated before returning.

LOB_compression_clause This clause is valid only for SecureFiles LOBs. COMPRESS compresses all LOBs in the segment and then returns. NOCOMPRESS uncompresses all LOBs in the segment and then returns.

ENCRYPT | DECRYPT LOB encryption has the same semantics as column encryption in general. See “ENCRYPT encryption_spec | DECRYPT” for more information.

CACHE, NOCACHE, CACHE READS When you modify a LOB column from CACHE or NOCACHE to CACHE READS, or from CACHE READS to CACHE or NOCACHE , you can change the logging attribute. If you do not specify LOGGING or NOLOGGING , then this attribute defaults to the current logging attribute of the LOB column. If you do not specify CACHE , NOCACHE , or CACHE READS , then Oracle Database retains the existing values of the LOB attributes.

Restrictions on Modifying LOB Storage Modifying LOB storage is subject to the following restrictions:

You cannot modify the value of the INITIAL parameter in the storage_clause when modifying the LOB storage attributes.

You cannot specify both the allocate_extent_clause and the deallocate_unused_clause in the same statement.

You cannot specify both the PCTVERSION and RETENTION parameters.

You cannot specify the shrink_clause for SecureFiles LOBs.

alter_varray_col_properties

The alter_varray_col_properties clause lets you change the storage characteristics of an existing LOB in which a varray is stored.

Restriction on Altering Varray Column Properties You cannot specify the TABLESPACE clause of LOB_parameters as part of this clause. The LOB tablespace for a varray defaults to the tablespace of the containing table.

REKEY encryption_spec

The REKEY clause causes the database to generate a new encryption key. All encrypted columns in the table are reencrypted using the new key and, if you specify the USING clause of the encryption_spec , a new encryption algorithm. You cannot combine this clause with any other clauses in this ALTER TABLE statement.

constraint_clauses

Use the constraint_clauses to add a new constraint using out-of-line declaration, modify the state of an existing constraint, or drop a constraint. Refer to constraint for a description of all the keywords and parameters of out-of-line constraints and constraint_state .

Adding a Constraint

The ADD clause lets you add a new out-of-line constraint or out-of-line REF constraint to the table.

Modifying a Constraint

The MODIFY CONSTRAINT clause lets you change the state of an existing constraint.

The CASCADE keyword is valid only when you are disabling a unique or primary key constraint on which a foreign key constraint is defined. In this case, you must specify CASCADE so that the unique or primary key constraint and all of its dependent foreign key constraints are disabled.

Restrictions on Modifying Constraints Modifying constraints is subject to the following restrictions:

You cannot change the state of a NOT DEFERRABLE constraint to INITIALLY DEFERRED .

If you specify this clause for an index-organized table, then you cannot specify any other clauses in the same statement.

You cannot change the NOT NULL constraint on a foreign key column of a reference-partitioned table, and you cannot change the state of a partitioning referential constraint of a reference-partitioned table.

Renaming a Constraint

The RENAME CONSTRAINT clause lets you rename any existing constraint on table . The new constraint name cannot be the same as any existing constraint on any object in the same schema. All objects that are dependent on the constraint remain valid.

drop_constraint_clause

The drop_constraint_clause lets you drop an integrity constraint from the database. Oracle Database stops enforcing the constraint and removes it from the data dictionary. You can specify only one constraint for each drop_constraint_clause , but you can specify multiple drop_constraint_clause in one statement.

PRIMARY KEY Specify PRIMARY KEY to drop the primary key constraint of table .

UNIQUE Specify UNIQUE to drop the unique constraint on the specified columns.

If you drop the primary key or unique constraint from a column on which a bitmap join index is defined, then Oracle Database invalidates the index. See CREATE INDEX for information on bitmap join indexes.

CONSTRAINT Specify CONSTRAINT constraint_name to drop an integrity constraint other than a primary key or unique constraint.

CASCADE Specify CASCADE if you want all other integrity constraints that depend on the dropped integrity constraint to be dropped as well.

KEEP INDEX | DROP INDEX Specify KEEP INDEX or DROP INDEX to indicate whether Oracle Database should preserve or drop the index it has been using to enforce the PRIMARY KEY or UNIQUE constraint.

ONLINE Specify ONLINE to indicate that DML operations on the table will be allowed while dropping the constraint.

Restrictions on Dropping Constraints Dropping constraints is subject to the following restrictions:

You cannot drop a primary key or unique key constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, use the CASCADE clause. If you omit CASCADE , then Oracle Database does not drop the primary key or unique constraint if any foreign key references it.

You cannot drop a primary key constraint (even with the CASCADE clause) on a table that uses the primary key as its object identifier (OID).

If you drop a referential integrity constraint on a REF column, then the REF column remains scoped to the referenced table.

You cannot drop the scope of a REF column.

You cannot drop the NOT NULL constraint on a foreign key column of a reference-partitioned table, and you cannot drop a partitioning referential constraint of a reference-partitioned table.

You cannot drop the NOT NULL constraint on a column that is defined with a default column value using the ON NULL clause.

You cannot specify the ONLINE clause when dropping a DEFERRABLE constraint.

alter_external_table

Use the alter_external_table clauses to change the characteristics of an external table. This clause has no affect on the external data itself. The syntax and semantics of the parallel_clause , enable_disable_clause , external_data_properties , and REJECT LIMIT clause are the same as described for CREATE TABLE . See the external_table_clause (in CREATE TABLE ).

PROJECT COLUMN Clause This clause lets you determine how the access driver validates the rows of an external table in subsequent queries. The default is PROJECT COLUMN ALL , which means that the access driver processes all column values, regardless of which columns are selected, and validates only those rows with fully valid column entries. If any column value would raise an error, such as a data type conversion error, then the row is rejected even if that column was not referenced in the select list. If you specify PROJECT COLUMN REFERENCED , then the access driver processes only those columns in the select list.

The ALL setting guarantees consistent result sets. The REFERENCED setting can result in different numbers of rows returned, depending on the columns referenced in subsequent queries, but is faster than the ALL setting. If a subsequent query selects all columns of the external table, then the settings behave identically.

Restrictions on Altering External Tables Altering external tables is subject to the following restrictions:

You cannot modify an external table using any clause outside of this clause.

You cannot add a LONG , varray, or object type column to an external table, nor can you change the data type of an external table column to any of these data types.

You cannot add a constraint to an external table.

You cannot modify the storage parameters of an external table.

alter_table_partitioning

The clauses in this section apply only to partitioned tables. You cannot combine partition operations with other partition operations or with operations on the base table in the same ALTER TABLE statement.

Notes on Changing Table Partitioning The following notes apply when changing table partitioning:

If you drop, exchange, truncate, move, modify, or split a partition on a table that is a master table for one or more materialized views, then existing bulk load information about the table will be deleted. Therefore, be sure to refresh all dependent materialized views before performing any of these operations.

If a bitmap join index is defined on table , then any operation that alters a partition of table causes Oracle Database to mark the index UNUSABLE .

The only alter_table_partitioning clauses you can specify for a reference-partitioned table are modify_table_default_attrs , move_table_[sub]partition , truncate_partition_subpart , and exchange_partition_subpart . None of these operations cascade to any child table of the reference-partitioned table. No other partition maintenance operations are valid on a reference-partitioned table, but you can specify the other partition maintenance operations on the parent table of a reference-partitioned table, and the operation will cascade to the child reference-partitioned table.

When adding partitions and subpartitions, bear in mind that you can specify up to a total of 1024K-1 partitions and subpartitions for each table.

When you add a table partition or subpartition and you omit the partition name, the database generates a name using the rules described in “Notes on Partitioning in General”.

When you move, add (hash only), coalesce, drop, split, merge, rename, or truncate a table partition or subpartition, the procedures, functions, packages, package bodies, views, type bodies, and triggers that reference the table remain valid. All other dependent objects are invalidated.

Deferred segment creation is not supported for partition maintenance operations that create new segments on tables with LOB columns; segments will always be created for the involved (sub)partitions.

For additional information on partition operations on tables with an associated CONTEXT domain index, refer to Oracle Text Reference.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Refer to Oracle Database VLDB and Partitioning Guide for a discussion of these restrictions.

modify_table_default_attrs

The modify_table_default_attrs clause lets you specify new default values for the attributes of table . Only attributes named in the statement are affected. Partitions and LOB partitions you create subsequently will inherit these values unless you override them explicitly when creating the partition or LOB partition. Existing partitions and LOB partitions are not affected by this clause.

Only attributes named in the statement are affected, and the default values specified are overridden by any attributes specified at the individual partition or LOB partition level.

FOR partition_extended_name applies only to composite-partitioned tables. This clause specifies new default values for the attributes of the partition identified in partition_extended_name . Subpartitions and LOB subpartitions of that partition that you create subsequently will inherit these values unless you override them explicitly when creating the subpartition or LOB subpartition. Existing subpartitions are not affected by this clause.

PCTTHRESHOLD , prefix_compression , and the alter_overflow_clause are valid only for partitioned index-organized tables.

You can specify the prefix_compression clause only if prefix compression is already specified at the table level. Further, you cannot specify an integer after the COMPRESS keyword. Prefix length can be specified only when you create the table.

You cannot specify the PCTUSED parameter in segment_attributes for the index segment of an index-organized table.

The indexing_clause lets you modify the default indexing property for the table. The new default indexing property will be assigned to partitions or subpartitions that are subsequently added to the table, unless you override this behavior by specifying the indexing property for the new partition or subpartition. When you modify the default indexing property of a table, you do not change the indexing property of the existing partitions and subpartitions in the table. Refer to the indexing_clause of CREATE TABLE for the full semantics of this clause.

alter_interval_partitioning

Use this clause:

To convert an existing range-partitioned table to interval partitioning. The database automatically creates partitions of the specified numeric range or datetime interval as needed for data beyond the highest value allowed for the last range partition. If the table has reference-partitioned child tables, then the child tables are converted to interval reference-partitioned child tables.

To change the interval of an existing interval-partitioned table. The database first converts existing interval partitions to range partitions and determines the high value of the defined range partitions. The database then automatically creates partitions of the specified numeric range or datetime interval as needed for data that is beyond that high value.

To change the tablespace storage for an existing interval-partitioned table. If the table has interval reference-partitioned child tables, then the new tablespace storage is inherited by any child table that does not have its own table-level default tablespace.

To change an interval-partitioned table back to a range-partitioned table. Use SET INTERVAL () to disable interval partitioning. The database converts existing interval partitions to range partitions, using the higher boundaries of created interval partitions as upper boundaries for the range partitions to be created. If the table has interval reference-partitioned child tables, then the child tables are converted to ordinary reference-partitioned child tables.

For expr , specify a valid number or interval expression.

set_subpartition_template

Use the set_subpartition_template clause to create or replace existing default range, list, or hash subpartition definitions for each table partition. This clause is valid only for composite-partitioned tables. It replaces the existing subpartition template or creates a new template if you have not previously created one. Existing subpartitions are not affected, nor are existing local and global indexes. However, subsequent partitioning operations (such as add and merge operations) will use the new template.

You can drop an existing subpartition template by specifying ALTER TABLE table SET SUBPARTITION TEMPLATE () .

The set_subpartition_template clause has the same semantics as the subpartition_template clause of CREATE TABLE . Refer to the subpartition_template clause of CREATE TABLE for more information.

modify_table_partition

The modify_table_partition clause lets you change the real physical attributes of a range, hash, list partition, or system partition. This clause optionally modifies the storage attributes of one or more LOB items for the partition. You can specify new values for physical attributes (with some restrictions, as noted in the sections that follow), logging, and storage parameters.

For all types of partitions, you can also specify how Oracle Database should handle local indexes that become unusable as a result of the modification to the partition. See “UNUSABLE LOCAL INDEXES Clauses”.

For partitioned index-organized tables, you can also update the mapping table in conjunction with partition changes. See the alter_mapping_table_clauses .

indexing_clause Use the indexing_clause to modify the indexing property of a table partition. The indexing property determines whether the partition is included in partial indexes on the table. You can specify the indexing_clause in the modify_range_partition , modify_hash_partition , and modify_list_partition clauses.

Specify INDEXING ON to change the indexing property for a table partition to ON . This operation has no effect on full indexes on the table. It has the following effects on partial indexes on the table:

Local partial indexes: The table partition is included in the index. The corresponding index partition is rebuilt and marked USABLE .

Global partial indexes: The table partition is included in the index. Index entries for the table partition are added to the index as part of routine index maintenance.

Specify INDEXING OFF to change the indexing property for a table partition to OFF . This operation has no effect on full indexes on the table. It has the following effects on partial indexes on the table:

Local partial indexes: The table partition is excluded from the index. The corresponding index partition is marked UNUSABLE .

Global partial indexes: The table partition is excluded from the index. Index entries for the table partition are removed from the index. This is a metadata-only operation and the index entries will continue to be physically stored in the index. You can remove these orphaned index entries by specifying COALESCE CLEANUP in the ALTER INDEX statement or in the modify_index_partition clause.

Restriction on the indexing_clause You can specify this clause only for partitions of a simple partitioned table. For composite-partitioned tables, you can specify the indexing_clause at the table subpartition level. Refer to modify_table_subpartition for more information.

Notes on Modifying Table Partitions The following notes apply to operations on range, list, and hash table partitions:

For all types of table partition, in the partition_attributes clause, the shrink_clause lets you compact an individual partition segment. Refer to shrink_clause for additional information on this clause.

The syntax and semantics for modifying a system partition are the same as those for modifying a hash partition. Refer to modify_hash_partition.

If table is composite partitioned, then: If you specify the allocate_extent_clause , then Oracle Database allocates an extent for each subpartition of partition . If you specify the deallocate_unused_clause , then Oracle Database deallocates unused storage from each subpartition of partition . Any other attributes changed in this clause will be changed in subpartitions of partition as well, overriding existing values. To avoid changing the attributes of existing subpartitions, use the FOR PARTITION clause of modify_table_default_attrs.

When you modify the partition_attributes of a table partition with equipartitioned nested tables, the changes do not apply to the nested table partitions corresponding to the table partition being modified. However, you can modify the storage table of the nested table partition directly with an ALTER TABLE statement.

Unless otherwise documented, the remaining clauses of partition_attributes have the same behavior they have when you are creating a partitioned table. Refer to the CREATE TABLE table_partitioning_clauses for more information.

modify_range_partition

Use this clause to modify the characteristics of a range partition.

add_range_subpartition This clause is valid only for range-range composite partitions. It lets you add one or more range subpartitions to partition .

Restriction on Adding Range Subpartitions If table is an index-organized table, then you can add only one range subpartition at a time.

add_hash_subpartition This clause is valid only for range-hash composite partitions. The add_hash_subpartition clause lets you add a hash subpartition to partition . Oracle Database populates the new subpartition with rows rehashed from the other subpartition(s) of partition as determined by the hash function. For optimal load balancing, the total number of subpartitions should be a power of 2.

In the partitioning_storage_clause , the only clause you can specify for subpartitions is the TABLESPACE clause. If you do not specify TABLESPACE , then the new subpartition will reside in the default tablespace of partition .

Oracle Database adds local index partitions corresponding to the selected partition.

Oracle Database marks UNUSABLE the local index partitions corresponding to the added partitions. The database invalidates any indexes on heap-organized tables. You can update these indexes during this operation using the update_index_clauses.

add_list_subpartition This clause is valid only for range-list and list-list composite partitions. It lets you add one or more list subpartitions to partition , and only if you have not already created a DEFAULT subpartition.

The list_values_clause is required in this operation, and the values you specify in the list_values_clause cannot exist in any other subpartition of partition . However, these values can duplicate values found in subpartitions of other partitions.

In the partitioning_storage_clause , the only clauses you can specify for subpartitions are the TABLESPACE clause and table compression.

For each added subpartition, Oracle Database also adds a subpartition with the same value list to all local index partitions of the table. The status of existing local and global index partitions of table are not affected.

Restrictions on Adding List Subpartitions The following restrictions apply to adding list subpartitions:

You cannot specify this clause if you have already created a DEFAULT subpartition for this partition. Instead you must split the DEFAULT partition using the split_list_subpartition clause.

If table is an index-organized table, then you can add only one list subpartition at a time.

coalesce_table_subpartition COALESCE SUBPARTITION applies only to hash subpartitions. Use the COALESCE SUBPARTITION clause if you want Oracle Database to select the last hash subpartition, distribute its contents into one or more remaining subpartitions (determined by the hash function), and then drop the last subpartition.

Oracle Database drops local index partitions corresponding to the selected partition.

Oracle Database marks UNUSABLE the local index partitions corresponding to one or more absorbing partitions. The database invalidates any global indexes on heap-organized tables. You can update these indexes during this operation using the update_index_clauses.

modify_hash_partition

When modifying a hash partition, in the partition_attributes clause, you can specify only the allocate_extent_clause and deallocate_unused_clause . All other a

오라클 테이블 컬럼 추가, 수정, 삭제, 이름변경 하기(ALTER 테이블)

1. 테이블에 컬럼을 추가합니다.(ADD)

문법) ALTER TABLE 테이블명 ADD(컬럼명 데이터타입(데이터크기));

EX1) ALTER TABLE EMP ADD(EMP_ADDR VARCHAR2(10)); — EMP 테이블에 EMP_ADDR이라는 컬럼을 VARCHAR2 데이터타입으로 추가

EX2) ALTER TABLE EMP ADD(EMP_NUM NUMBER(8, 2)); — EMP 테이블에 EMP_NUM이라는 컬럼을 NUMBER 데이터타입으로 추가

2. 테이블에 컬럼을 수정합니다.(MODIFY)

문법) ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입(데이터크기));

EX1) ALTER TABLE EMP MODIFY(EMP_ADDR VARCHAR2(20)); — EMP 테이블에 EMP_ADDR이라는 컬럼의 데이터 크기를 20으로 수정(참고로 데이터 크기가 더 작게는 수정이 불가능 합니다)

EX2) ALTER TABLE EMP MODIFY(EMP_NUM NUMBER(10, 2)); — EMP 테이블에 EMP_NUM이라는 컬럼의 데이터 크기를 10으로 수정(참고로 데이터 크기가 더 작게는 수정이 불가능 합니다)

3. 테이블에 컬럼을 삭제합니다.(DROP)

문법) ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

EX1) ALTER TABLE EMP DROP COLUMN EMP_ADDR; — EMP 테이블에 EMP_ADDR이라는 컬럼을 삭제

4. 테이블에 컬럼이름을 변경합니다.(RENAME)

문법) ALTER TABLE 테이블명 RENAME COLUMN 이전컬럼명 TO 바꿀컬럼명 ;

EX1) ALTER TABLE EMP RENAME COLUMN EMP_ADDR TO EMP_ADDR1; — EMP 테이블에 EMP_ADDR이라는 컬럼명을 EMP_ADDR1으로 변경

EX를 복사하신 다음 파란색 부분 만 원하시는 이름으로 변경 하셔서 사용하시면 됩니다.

So you have finished reading the 오라클 alter table topic article, if you find this article useful, please share it. Thank you very much. See more: Oracle alter table add column default, 오라클 컬럼 코멘트 변경, 오라클 컬럼 추가, 오라클 컬럼 순서 변경, 오라클 코멘트 추가, Oracle ALTER TABLE MODIFY column type, 오라클 컬럼명 변경, 오라클 컬럼 삭제

Top 9 오라클 Alter Table 5795 Votes This Answer

[ORACLE] 오라클 테이블 컬럼 추가/수정/삭제/이름변경 하는 방법(ALTER 테이블 ADD/MODIFY/DROP/RENAME) :: 인 생

Oracle / PLSQL: ALTER TABLE Statement

ALTER TABLE

오라클 테이블 컬럼 추가, 수정, 삭제, 이름변경 하기(ALTER 테이블)

[Oracle] 오라클 테이블 변경 (ADD/MODIFY/DROP/RENAME)

코딩벌레 :: [Oracle] 오라클 컬럼추가,수정,삭제,코멘트달기

오라클 ALTER 명령을 사용해서 테이블 변경하기

[ORACLE] 테이블 생성(CREATE), 수정(ALTER), 삭제(DROP) 하기

[ORACLE] 오라클 테이블 컬럼 추가/수정/삭제/이름변경 하는 방법(ALTER 테이블 ADD/MODIFY/DROP/RENAME)

ALTER TABLE

오라클 테이블 컬럼 추가, 수정, 삭제, 이름변경 하기(ALTER 테이블)

Leave a Comment Cancel reply