ALTER TABLE

The ALTER TABLE statement changes an existing table definition.

Required Privilege

No privilege is required for the table owner.

ALTER ANY TABLE for another user's table.

For ALTER TABLE...ADD FOREIGN KEY, the owner of the altered table must have the REFERENCES privilege on the table referenced by the foreign key clause.

ALTER TABLE

See:

SQL Syntax for ALTER TABLE

To add one column:

ALTER TABLE [Owner.]TableName ADD [COLUMN] ColumnName ColumnDataType
  [DEFAULT DefaultVal] [[NOT] INLINE] [UNIQUE] [NULL]
  [COMPRESS (CompressColumns [,...])];

To add multiple columns:

ALTER TABLE [Owner.]TableName ADD (ColumnName ColumnDataType 
  [DEFAULT DefaultVal] [[NOT] INLINE] [UNIQUE] [NULL] [,... ] )
  [COMPRESS (CompressColumns [,...])];

To add a NOT NULL column (note that the DEFAULT clause is required):

ALTER TABLE [Owner.]TableName
  ADD [COLUMN] ColumnName ColumnDataType
    NOT NULL [ENABLE] DEFAULT DefaultVal [[NOT] INLINE] [UNIQUE]
  [COMPRESS (CompressColumns [,...])]

To add multiple NOT NULL columns (note that the DEFAULT clause is required):

ALTER TABLE [Owner.]TableName
  ADD (ColumnName ColumnDataType
       NOT NULL [ENABLE] DEFAULT DefaultVal [[NOT] INLINE] [UNIQUE] [,...])
  [COMPRESS (CompressColumns [,...])]

The CompressColumns syntax is as follows:

{ColumnDefinition | (ColumnDefinition [,...])} BY DICTIONARY 
   [MAXVALUES = CompressMax]

To remove columns.

ALTER TABLE [Owner.]TableName 
  DROP {[COLUMN] ColumnName | (ColumnName [,... ] )}

Note:

If removing columns in a compressed column group, all columns in the compressed column group must be specified.

To add a primary key constraint using a range index:

ALTER TABLE [Owner.]TableName ADD CONSTRAINT ConstraintName
  PRIMARY KEY (ColumnName [,... ])

To add a primary key constraint using a hash index:

ALTER TABLE [Owner.]TableName ADD CONSTRAINT ConstraintName
  PRIMARY KEY (ColumnName [,... ])
  USE HASH INDEX PAGES = RowPages | CURRENT

To add a foreign key and optionally add ON DELETE CASCADE:

ALTER TABLE [Owner.]TableName 
ADD [CONSTRAINT ForeignKeyName] FOREIGN KEY
    (ColumnName [,...]) REFERENCES RefTableName
       [(ColumnName [,...])] [ON DELETE CASCADE]

To remove a foreign key:

ALTER TABLE [Owner.]TableName 
DROP CONSTRAINT ForeignKeyName

Note:

You cannot use ALTER TABLE to drop a primary key constraint. To drop the constraint, drop and recreate the table.

To resize a hash index:

ALTER TABLE [Owner.]TableName
SET PAGES = RowPages | CURRENT

To change the primary key to use a hash index:

ALTER TABLE [Owner.]TableName
USE HASH INDEX PAGES = RowPages | CURRENT

To change the primary key to use a range index with the USE RANGE INDEX clause:

ALTER TABLE [Owner.]TableName
USE RANGE INDEX

To change the default value of a column:

ALTER TABLE [Owner.]TableName
MODIFY (ColumnName DEFAULT DefaultVal)

To add or drop a unique constraint on a column:

ALTER TABLE Owner.]TableName
{ADD | DROP} UNIQUE (ColumnName)

To remove the default value of a column that is nullable, by changing it to NULL:

ALTER TABLE [Owner.]TableName
MODIFY (ColumnName DEFAULT NULL)

To add LRU aging:

ALTER TABLE [Owner.]TableName
ADD AGING LRU [ON | OFF]

To add time-based aging:

ALTER TABLE [Owner.]TableName
ADD AGING USE ColumnName LIFETIME num1
    {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]}
     [CYCLE num2 {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S] }]
    [ON | OFF]

To change the aging state:

ALTER TABLE [Owner.]TableName
SET AGING {ON | OFF}

To drop aging:

ALTER TABLE [Owner.]TableName
DROP AGING

To change the lifetime for time-based aging:

ALTER TABLE [Owner.]TableName
SET AGING LIFETIME num1 {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]}

To change the cycle for time-based aging:

ALTER TABLE [Owner.]TableName
SET AGING CYCLE num2 {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]}

Parameters for ALTER TABLE

Parameter	Description
`[Owner.]` `TableName`	Identifies the table to be altered.
`UNIQUE`	Specifies that in the column `ColumnName` each row must contain a unique value.
`MODIFY`	Specifies that an attribute of a given column is to be changed to a new value.
`DEFAULT [DefaultVal` `\|NULL]`	Specifies that the column has a default value, `DefaultVal`. If `NULL`, specifies that the default value of the columns is to be dropped. If a column with a default value of `SYSDATE` is added, the value of the column of the existing rows only is the system date at the time the column was added. If the default value is one of the `USER` functions the column value is the user value of the session that executed the `ALTER TABLE` statement. Currently, you cannot assign a default value for the `ROWID` data type. Altering the default value of a column has no impact on existing rows. Note: To add a `NOT NULL` column to a table that is part of a replication scheme, `DDL_REPLICATON_LEVEL` must be 3 or greater.
`ColumnName`	Name of the column participating in the `ALTER` `TABLE` statement. A new column cannot have the same name as an existing column or another new column. If you add a `NOT` `NULL` column, you must include the `DEFAULT` clause.
`ColumnDataType`	Type of the column to be added. Some types require additional parameters. See Data Types for the data types that can be specified.
`NOT NULL [ENABLE]`	If you add a column, you can specify `NOT` `NULL`. If you specify `NOT` `NULL`, then you must include the `DEFAULT` clause. Optionally, you can specify `ENABLE` after the `NOT` `NULL` clause. Because `NOT` `NULL` constraints are always enabled, you are not required to specify `ENABLE`.
`INLINE\|NOT INLINE`	By default, variable-length columns whose declared column length is > 128 bytes are stored out of line. Variable-length columns whose declared column length is <= 128 bytes are stored inline. The default behavior can be overridden during table creation through the use of the `INLINE` and `NOT INLINE` keywords.
`COMPRESS (CompressColumns` `[,...])`	Defines a compressed column group for a table that is enabled for compression. This can include one or more columns in the table. If you define multiple columns for a compression group, you must specify the columns as `INLINE`. An out-of-line column cannot be in a multi-column compression group. Each compressed column group is limited to a maximum of 16 columns. See Column-Based Compression of Tables for details on compression columns.
`BY DICTIONARY`	Defines a compression dictionary for each compressed column group.
`MAXVALUES` = `CompressMax`	`CompressMax` is the total number of distinct values in the table and sets the size for the compressed column group pointer column to 1, 2, or 4 bytes and sets the size for the maximum number of entries in the dictionary table. For the dictionary table, `NULL` is counted as one unique value. `CompressMax` takes an integer between 1 and 2³²-1. The maximum size defaults to size of 2³²-1 if the `MAXVALUES` clause is omitted, which uses 4 bytes for the pointer column. An error is thrown if the value is greater than 2³²-1. See Column-Based Compression of Tables for details on maximum sizing for compression dictionaries.
`ADD CONSTRAINT` `ConstraintName` `PRIMARY KEY` `(ColumnName` `[,... ] ) [USE HASH INDEX PAGES =` `RowPages` `\| CURRENT]`	Adds a primary key constraint to the table. Columns of the primary key must be defined as `NOT NULL`. Specify `ConstraintName` as the name of the index used to enforce the primary key constraint. Specify `ColumnName` as the name(s) of the `NOT NULL` column(s) used for the primary key. Specify the `USE HASH INDEX` clause to use a hash index for the primary key. If not specified, a range index is used for the primary key constraint. If you specify `CURRENT`, the current number of rows in the table is used to calculate the page count value. If you specify `RowPages`, the number of pages is used. To determine the value for `RowPages`, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for RowPages (256000/256=1000). The value for `RowPages` must be a positive constant and must be greater than 0. TimesTen recommends that you do not specify `PAGES=CURRENT` if there are no rows in your table. If your estimate is too small, performance may be degraded. See Column Definition for more information on hash indexes. Note: Before you use `ADD CONSTRAINT` to add a named primary key constraint, be aware that you cannot use `ALTER TABLE` to drop a primary key constraint. You would have to drop and recreate the table in order to drop the constraint.
`CONSTRAINT`	Specifies that a foreign key is to be dropped. Optionally specifies that an added foreign key is named by the user.
`ForeignKeyName`	Name of the foreign key to be added or dropped. All foreign keys are assigned a default name by the system if the name was not specified by the user. Either the user-provided name or system name can be specified in the `DROP FOREIGN KEY` clause.
`FOREIGN KEY`	Specifies that a foreign key is to be added.
`REFERENCES`	Specifies that the foreign key references another table.
`RefTableName`	The name of the table that the foreign key references.
`[ON DELETE` `CASCADE]`	Enables the `ON DELETE CASCADE` referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.
`USE` `HASH` `INDEX` `PAGES` = `RowPages` \| `CURRENT`	Changes primary key to use a hash index. If the primary key already uses a hash index, then this clause is equivalent to the `SET PAGES` clause.
`USE RANGE INDEX`	Changes primary key to use a range index. If the primary key already uses a range index, TimesTen ignores this clause.
`SET PAGES =` `RowPages` `\| CURRENT`	Resizes the hash index to reflect the expected number of pages in the table. If you specify `CURRENT`, the current number of rows in the table is used to calculate the page count value. If you specify `RowPages`, the number of pages is used. To determine the value for `RowPages`, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for `RowPages` (256000/256=1000). The value for `RowPages` must be a positive constant and must be greater than 0. TimesTen recommends that you do not specify `PAGES=CURRENT` if there are no rows in your table. If your estimate is too small, performance may be degraded. See Column Definition for more information on hash indexes.
`ADD AGING LRU [ON \| OFF]`	Adds least recently used (LRU) aging to an existing table that has no aging policy defined. The LRU aging policy defines the type of aging (least recently used (LRU)), the aging state (`ON` or `OFF`) and the LRU aging attributes. Set the aging state to either `ON` or `OFF`. `ON` indicates that the aging state is enabled and aging is done automatically. `OFF` indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is `ON`. LRU attributes are defined by calling the `ttAgingLRUConfig` and/or the `ttAgingTableLRUConfig` built-in procedures. LRU attributes are not defined at the SQL level. See ttAgingLRUConfig and ttAgingTableLRUConfig in the Oracle TimesTen In-Memory Database Reference and Implementing an Aging Policy in Your Tables in the Oracle TimesTen In-Memory Database Operations Guide for more information.
`ADD AGING USE` `ColumnName...[ON\| OFF]`	Adds time-based aging to an existing table that has no aging policy defined. The time-based aging policy defines the type of aging (time-based), the aging state (`ON` or `OFF`) and the time-based aging attributes. Set the aging state to either `ON` or `OFF`. `ON` indicates that the aging state is enabled and aging is done automatically. `OFF` indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is `ON`. Time-based aging attributes are defined at the SQL level and are specified by the `LIFETIME` and `CYCLE` clauses. Specify `ColumnName` as the name of the column used for time-based aging. Define the column as `NOT NULL` and of data type `TIMESTAMP` or `DATE`. The value of this column is subtracted from `SYSDATE`, truncated using the specified unit (minute, hour, day) and then compared to the `LIFETIME` value. If the result is greater than the `LIFETIME` value, then the row is a candidate for aging. The values of the column used for aging are updated by your applications. If the value of this column is unknown for some rows, and you do not want the rows to be aged, define the column with a large default value (the column cannot be `NULL`). You can define your aging column with a data type of `TT_TIMESTAMP` or `TT_DATE`. If you choose data type `TT_DATE`, then you must specify the `LIFETIME` unit as days. For more information about time-based aging, see Implementing an Aging Policy in Your Tables in Oracle TimesTen In-Memory Database Operations Guide.
`LIFETIME` `Num1` `{SECOND[S] \| MINUTE[S] \| HOUR[S] \| DAY[S]`	Specify the `LIFETIME` clause after the `ADD AGING USE` `ColumnName` clause if you are adding the time-based aging policy to an existing table. Specify the `LIFETIME` clause after the `SET AGING` clause to change the `LIFETIME` setting. The `LIFETIME` clause specifies the minimum amount of time data is kept in cache. Specify `Num1` as a positive integer constant to indicate the unit of time expressed in seconds, minutes, hours or days that rows should be kept in cache. Rows that exceed the `LIFETIME` value are aged out (deleted from the table). If you define your aging column with data type `TT_DATE`, then you must specify `DAYS` as the `LIFETIME` unit. The concept of time resolution is supported. If `DAYS` is specified as the time resolution, then all rows whose timestamp belongs to the same day are aged out at the same time. If `HOURS` is specified as the time resolution, then all rows with timestamp values within that hour are aged at the same time. A `LIFETIME` of 3 days is different than a `LIFETIME` of 72 hours (324) or a `LIFETIME` of 432 minutes (324*60).
`CYCLE` `Num2` `{SECOND[S]\| MINUTE[S]\| HOUR[S]\|DAY[S]}`	Specify the optional `CYCLE` clause after the `LIFETIME` clause if you are adding the time-based aging policy to an existing table. `CYCLE` is a time-based aging attribute. The `CYCLE` clause indicates how often the system should examine rows to see if data exceeds the specified `LIFETIME` value and should be aged out (deleted). Specify `Num2` as a positive integer constant. If you do not specify the `CYCLE` clause, then the default value is 5 minutes. If you specify 0 for `Num2,` then the aging thread wakes up every second. If the aging state is `OFF`, then aging is not done automatically and the `CYCLE` clause is ignored. Specify the `CYCLE` clause after the `SET AGING` clause to change the `CYCLE` setting.
`SET AGING {ON\|OFF}`	Changes the aging state. The aging policy must be previously defined. `ON` enables automatic aging. `OFF` disables automatic aging. To control aging with an external scheduler, then disable aging and invoke the `ttAgingScheduleNow` built-in procedure.
`DROP AGING`	Drops the aging policy from the table. After you define an aging policy, you cannot alter it. Drop aging, then redefine.
`SET AGING LIFETIME` `Num1` `{SECOND[S]\| MINUTE[S]\|HOUR[S] \|DAY[S]}`	Use this clause to change the lifetime for time-based aging. `Num1` must be a positive integer constant. If you defined your aging column with data type `TT_DATE`, then you must specify `DAYS` as the `LIFETIME` unit.
`SET AGING CYCLE` `Num2` `{SECOND[S]\| MINUTE[S]\| HOUR[S]\|DAY[S]}`	Use this clause to change the cycle for time-based aging. `Num2` must be a positive integer constant.

Description for ALTER TABLE

The ALTER TABLE statement cannot be used to alter a temporary table.
The ALTER TABLE ADD [COLUMN] ColumnName statement adds one or more new columns to an existing table. When you add one or more columns, the new columns are added to the end of all existing rows of the table in one new partition.
The ALTER TABLE ADD or DROP COLUMN statement can be used to add or drop columns from replicated tables.

Do not use ALTER TABLE to alter a replicated table that is part of a TWOSAFE BY REQUEST transaction.
Columns referenced by materialized views cannot be dropped.
You cannot use the ALTER TABLE statement to add a column, drop a column, or add a constraint for cache group tables.
Only one partition is added to the table per statement regardless of the number of columns added.
You can ALTER a table to add a NOT NULL column with a default value. The DEFAULT clause is required. Restrictions include:
- You cannot use the column as a primary key column. Specifically, you cannot specify the column in the statement: ALTER TABLE ADD ConstraintName PRIMARY KEY (ColumnName [,...]).
- You cannot use the column for time-based aging. Specifically, you cannot specify the column in the statement ALTER TABLE ADD AGING USE ColumnName.
  
  Note:
  To add a NOT NULL column to a table that is part of a replication scheme, DDL_REPLICATON_LEVEL must be 3 or greater.
NULL is the initial value for all added columns, unless a default value is specified for the new column.
The total number of columns in the table cannot exceed 1000. In addition, the total number of partitions in a table cannot exceed 1000, one of which is used by TimesTen.
Use the ADD CONSTRAINT ... PRIMARY KEY clause to add a primary key constraint to a regular table or to a detailed or materialized view table. Do not use this clause on a table that already has a primary key.
If you use the ADD CONSTRAINT... PRIMARY KEY clause to add a primary key constraint, and you do not specify the USE HASH INDEX clause, then a range index is used for the primary key constraint.
If a table is replicated and the replication agent is active, you cannot use the ADD CONSTRAINT ... PRIMARY KEY clause. Stop the replication agent first.
Do not specify the ADD CONSTRAINT ... PRIMARY KEY clause on a global temporary table.
Do not specify the ADD CONSTRAINT ... PRIMARY KEY clause on a cache group table because cache group tables defined with a primary key must be defined in the CREATE CACHE GROUP statement.
As the result of an ALTER TABLE ADD statement, an additional read occurs for each new partition during queries. Therefore, altered tables may have slightly degraded performance. The performance can only by restored by dropping and recreating the table, or by using the ttMigrate create -c -relaxedUpgrade command, and restoring the table using the ttRestore -r -relaxedUpgrade command. Dropping the added column does not recover the lost performance or decrease the number of partitions.
When you use the ALTER TABLE DROP statement to remove one or more columns from an existing table, dropped columns are removed from all current rows of the table. Subsequent SQL statements must not attempt to make any use of the dropped columns. You cannot drop columns that are in the table's primary key. You cannot drop columns that are in any of the table's foreign keys until you have dropped all foreign keys. You cannot drop columns that are indexed until all indexes on the column have been dropped. ALTER TABLE cannot be used to drop all of the columns of a table. Use DROP TABLE instead.
When a column is dropped from a table, all commands referencing that table need to be recompiled. An error may result at recompilation time if a dropped column was referenced. The application must re-prepare those commands, and rebuild any parameters and result columns. When a column is added to a table, the commands that contain a SELECT * statement are invalidated. Only these commands must be re-prepared. All other commands continue to work as expected.
When you drop a column, the column space is not freed.
When you add a UNIQUE constraint, there is overhead incurred (in terms of additional space and additional time). This is because an index is created to maintain the UNIQUE constraint. You cannot use the DROP INDEX statement to drop an index used to maintain the UNIQUE constraint.
A UNIQUE constraint and its associated index cannot be dropped if it is being used as a unique index on a replicated table.
Use ALTER TABLE...USE RANGE INDEX if your application performs range queries over a table's primary key.
Use ALTER TABLE...USE HASH INDEX if your application performs exact match lookups on a table's primary key.
An error is generated if a table has no primary key and either the USE HASH INDEX clause or the USE RANGE INDEX clause is specified.
Make sure to stop the replication agent before adding or dropping a foreign key on a replicated table.
If ON DELETE CASCADE is specified on a foreign key constraint for a child table, a user can delete rows from a parent table for which the user has the DELETE privilege without requiring explicit DELETE privilege on the child table.
To change the ON DELETE CASCADE triggered action, drop then redefine the foreign key constraint.
ON DELETE CASCADE is supported on detail tables of a materialized view. If you have a materialized view defined over a child table, a deletion from the parent table causes cascaded deletes in the child table. This, in turn, triggers changes in the materialized view.
The total number of rows reported by the DELETE statement does not include rows deleted from child tables as a result of the ON DELETE CASCADE action.
For ON DELETE CASCADE, since different paths may lead from a parent table to a child table, the following rule is enforced:
Either all paths from a parent table to a child table are "delete" paths or all paths from a parent table to a child table are "do not delete" paths.
- Specify ON DELETE CASCADE on all child tables on the "delete" path.
- This rule does not apply to paths from one parent to different children or from different parents to the same child.
For ON DELETE CASCADE, a second rule is also enforced:
If a table is reached by a "delete" path, then all its children are also reached by a "delete" path.
For ON DELETE CASCADE with replication, the following restrictions apply:
- The foreign keys specified with ON DELETE CASCADE must match between the Master and subscriber for replicated tables. Checking is done at runtime. If there is an error, the receiver thread stops working.
- All tables in the delete cascade tree have to be replicated if any table in the tree is replicated. This restriction is checked when the replication scheme is created or when a foreign key with ON DELETE CASCADE is added to one of the replication tables. If an error is found, the operation is aborted. You may be required to drop the replication scheme first before trying to change the foreign key constraint.
The ALTER TABLE ADD/DROP CONSTRAINT statement has the following restrictions:
- When a foreign key is dropped, TimesTen also drops the index associated with the foreign key. Attempting to drop an index associated with a foreign key using the regular DROP INDEX statement results in an error.
- Foreign keys cannot be added or dropped on tables in a cache group.
- Foreign keys cannot be added or dropped on views or temporary tables.
- You cannot use ALTER TABLE to drop a primary key constraint. You would have to drop and recreate the table in order to drop the constraint.
After you have defined an aging policy for the table, you cannot change the policy from LRU to time-based or from time-based to LRU. You must first drop aging and then alter the table to add a new aging policy.
The aging policy must be defined to change the aging state.
The following rules determine if a row is accessed or referenced for LRU aging:
- Any rows used to build the result set of a SELECT statement.
- Any rows used to build the result set of an INSERT ... SELECT statement.
- Any rows that are about to be updated or deleted.
Compiled commands are marked invalid and need recompilation when you either drop LRU aging from or add LRU aging to tables that are referenced in the commands.
Call the ttAgingScheduleNow procedure to schedule the aging process right away regardless if the aging state is ON or OFF.
For the time-based aging policy, you cannot add or modify the aging column. This is because you cannot add or modify a NOT NULL column.
Aging restrictions:
- You cannot drop the column that is used for time-based aging.
- Tables that are related by foreign keys must have the same aging policy.
- For LRU aging, if a child row is not a candidate for aging, neither this child row nor its parent row are deleted. ON DELETE CASCADE settings are ignored.
- For time-based aging, if a parent row is a candidate for aging, then all child rows are deleted. ON DELETE CASCADE (whether specified or not) is ignored.
Restrictions for column-based compression of tables:
- You can add compressed column groups with the ALTER TABLE statement only if the table was enabled for compression at table creation. You can add uncompressed columns to any table, including tables enabled for compression. Refer to Column-Based Compression of Tables for more details on adding compressed column groups to a table.
- You cannot modify columns of a compressed column group.
- You can drop all columns within a compressed column group with the ALTER TABLE command; when removing columns in a compressed column group, all columns in the compressed column group must be specified for removal.
- You cannot use ALTER TABLE to modify an existing uncompressed column to make it compressed. For example:
```
Command> create table mytab (a varchar2 (30), b int, c int) compress ((a,b)
           by dictionary);
Command> alter table mytab add (d int) compress (c by dictionary);
2246: Cannot change compression clause for already defined column C 
The command failed.
```

Understanding Partitions When Using ALTER TABLE in TimesTen

When you create a table, an initial partition is created. If you ALTER the table, and add additional columns, secondary partitions are created. There is one secondary partition created for each ALTER TABLE statement. For a column in secondary partitions, you cannot create a primary key constraint on the column or use the column for time-based aging.

You can use ttMigrate -r -relaxedUpgrade to condense multiple partitions. This means the initial partition plus one or more secondary partitions are condensed into a single partition called the initial partition. Once you condense the partitions, you can then ALTER the table and add a primary key constraint on the column or use the column for time-based aging. This is because the columns are no longer in secondary partitions but are now in the initial partition.

If your database is involved in replication and you want to condense multiple partitions, you must use the StoreAttribute TABLE DEFINITION CHECKING RELAXED (of the CREATE REPLICATION statement). Run ttMigrate -r -relaxedUpgrade on both the master and subscriber or on either the master or subscriber by using -duplicate.

Use ttSchema to view partition numbers for columns. ttSchema displays secondary partition number 1 as partition 1, secondary partition number 2 as partition 2 and so on.

As an example, create a table MyTab with 2 columns. Then ALTER the table adding 2 columns (Col3 and Col4) with the NOT NULL DEFAULT clause.

Command> CREATE TABLE MyTab (Col1 NUMBER, Col2 VARCHAR2 (30));
Command> ALTER TABLE MyTab ADD (Col3 NUMBER NOT NULL DEFAULT 10, Col4 TIMESTAMP
            NOT NULL DEFAULT TIMESTAMP '2012-09-03 12:00:00');

Use ttSchema to verify Col3 and Col4 are in secondary partition 1.

ttschema -DSN sampledb_1122
-- Database is in Oracle type mode
create table TESTUSER.MYTAB (
        COL1 NUMBER,
        COL2 VARCHAR2(30 BYTE) INLINE,
        COL3 NUMBER NOT NULL DEFAULT 10,
        COL4 TIMESTAMP(6) NOT NULL DEFAULT TIMESTAMP '2012-09-03 12:00:00');
-- column COL3 partition 1
-- column COL4 partition 1

Attempt to add a primary key constraint on Col3 and time-based aging on Col4. You see errors because you can neither add a primary key constraint nor add time-based aging to a column that is not in the initial partition.

Command> ALTER TABLE MyTab ADD CONSTRAINT PriKey PRIMARY KEY (Col3);
 2419: All columns in a primary key constraint must be in the initial partition;
column COL3 was added by ALTER TABLE
The command failed.

Command> ALTER TABLE MyTab ADD AGING USE Col4 LIFETIME 3 DAYS;
 3023: Aging column must be in the initial partition; column COL4 was added by
ALTER TABLE
The command failed.

Use ttMigrate with the -relaxedUpgrade option to condense the partitions. Then use ttSchema to verify the partitions are condensed and there are no columns in secondary partition 1.

ttMigrate -c dsn=sampledb_1122 test.migrate
 
Saving user PUBLIC
User successfully saved.
 
Saving table TESTUSER.MYTAB
  Saving rows...
  0/0 rows saved.
Table successfully saved.

ttDestroy sampledb_1122

ttMigrate -r -relaxedUpgrade
 dsn=sampledb_1122 test.migrate
 
Restoring table TESTUSER.MYTAB
  Restoring rows...
  0/0 rows restored.
Table successfully restored.

ttSchema DSN=sampledb_1122
-- Database is in Oracle type mode
create table TESTUSER.MYTAB (
        COL1 NUMBER,
        COL2 VARCHAR2(30 BYTE) INLINE,
        COL3 NUMBER NOT NULL DEFAULT 10,
        COL4 TIMESTAMP(6) NOT NULL DEFAULT TIMESTAMP '2012-09-03 12:00:00');

Now add a primary key constraint on Col3 and time-based aging on Col4. The results are successful because Col3 and Col4 are in the initial partition as a result of ttMigrate. Use ttSchema to verify results.

Command> ALTER TABLE MyTab ADD CONSTRAINT PriKey PRIMARY KEY (Col3);
Command> ALTER TABLE MyTab ADD AGING USE Col4 LIFETIME 3 DAYS;

ttschema sampledb_1122
-- Database is in Oracle type mode
create table TESTUSER.MYTAB (
        COL1 NUMBER,
        COL2 VARCHAR2(30 BYTE) INLINE,
        COL3 NUMBER NOT NULL DEFAULT 10,
        COL4 TIMESTAMP(6) NOT NULL DEFAULT TIMESTAMP '2012-09-03 12:00:00')
    AGING USE COL4 LIFETIME 3 days CYCLE 5 minutes ON;
 
    alter table TESTUSER.MYTAB add constraint PRIKEY primary key (COL3);

Examples for ALTER TABLE

Add returnrate column to parts table.

ALTER TABLE parts ADD COLUMN returnrate DOUBLE;

Add numsssign and prevdept columns to contractor table.

ALTER TABLE contractor
  ADD ( numassign INTEGER, prevdept CHAR(30) );

Remove addr1 and addr2 columns from employee table.

ALTER TABLE employee DROP ( addr1, addr2 );

Drop the UNIQUE title column of the books table.

ALTER TABLE books DROP UNIQUE (title);

Add the x1 column to the t1 table with a default value of 5:

ALTER TABLE t1 ADD (x1 INT DEFAULT 5);

Change the default value of column x1 to 2:

ALTER TABLE t1 MODIFY (x1 DEFAULT 2);

Alter table primarykeytest to add the primary key constraint c1. Use the ttIsql INDEXES command to show that the primary key constraint c1 is created and a range index is used:

Command> CREATE TABLE primarykeytest (col1 TT_INTEGER NOT NULL);
Command> ALTER TABLE primarykeytest ADD CONSTRAINT c1 
         PRIMARY KEY (col1);
Command> INDEXES primarykeytest;

Indexes on table SAMPLEUSER.PRIMARYKEYTEST:
  C1: unique range index on columns:
    COL1
  1 index found.

1 index found on 1 table.

Alter table prikeyhash to add the primary key constraint c2 using a hash index. Use the ttIsql INDEXES command to show that the primary key constraint c2 is created and a hash index is used:

Command> CREATE TABLE prikeyhash (col1 NUMBER (3,2) NOT NULL);
Command> ALTER TABLE prikeyhash ADD CONSTRAINT c2
           PRIMARY KEY (col1) USE HASH INDEX PAGES = 20;
Command> INDEXES prikeyhash;

Indexes on table SAMPLEUSER.PRIKEYHASH:
  C2: unique hash index on columns:
    COL1
  1 index found.

1 table found.

Attempt to add a primary key constraint on a table already defined with a primary key. You see an error:

Command> CREATE TABLE oneprikey (col1 VARCHAR2 (30) NOT NULL, 
         col2 TT_BIGINT NOT NULL, col3 CHAR (15) NOT NULL, 
         PRIMARY KEY (col1,col2));
Command> ALTER TABLE oneprikey ADD CONSTRAINT c2 
         PRIMARY KEY (col1,col2);
 2235: Table can have only one primary key
The command failed.

Attempt to add a primary key constraint on a column that is not defined as NOT NULL. You see an error:

Command> CREATE TABLE prikeynull (col1 CHAR (30));
Command> ALTER TABLE prikeynull ADD CONSTRAINT c3 
         PRIMARY KEY (col1);
 2236: Nullable column cannot be part of a primary key
The command failed.

This example illustrates the use of range and hash indexes. It creates the pkey table with col1 as the primary key. A range index is created by default. The table is then altered to change the index on col1 to a hash index. The table is altered again to change the index back to a range index.

Command> CREATE TABLE pkey (col1 TT_INTEGER PRIMARY KEY, col2 VARCHAR2 (20));
Command> INDEXES pkey;
Indexes on table SAMPLEUSER.PKEY:
   PKEY: unique range index on columns:
    COL1
 1 index found.
1 index found on 1 table.

Alter the pkey table to use a hash index:

Command> ALTER TABLE pkey USE HASH INDEX PAGES = CURRENT;
Command> INDEXES pkey;
Indexes on table SAMPLEUSER.PKEY:
  PKEY: unique hash index on columns:
    COL1
  1 index found.
1 table found.

Alter the pkey table to use a range index with the USE RANGE INDEX clause:

Command> ALTER TABLE pkey USE RANGE INDEX;
Command> INDEXES pkey;
Indexes on table SAMPLEUSER.PKEY:
  PKEY: unique range index on columns:
    COL1
  1 index found.
1 table found.

This example generates an error when attempting to alter a table to define either a range or hash index on a column without a primary key.

Command> CREATE TABLE myindex (Ccl1 CHAR (20));
Command> ALTER TABLE myindex USE RANGE INDEX;
 2810: The table has no primary key so cannot change its index type
The command failed.
Command> ALTER TABLE myindex USE HASH INDEX PAGES = CURRENT;
 2810: The table has no primary key so cannot change its index type
The command failed.

These examples show how time resolution works with aging. In this example, lifetime is three days.

If (SYSDATE - ColumnValue) <= 3, do not age out the row.
If (SYSDATE - ColumnValue) > 3, then the row is a candidate for aging.
If (SYSDATE - ColumnValue) = 3 days, 22 hours, then row is not aged out because lifetime was specified in days. The row would be aged out if lifetime had been specified as 72 hours.

This example alters a table by adding LRU aging. The table has no previous aging policy. The aging state is ON by default.

ALTER TABLE agingdemo3 ADD AGING LRU;
Command> DESCRIBE agingdemo3;
Table USER.AGINGDEMO3:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
  Aging lru on
1 table found.
(primary key columns are indicated with *)

This example alters a table by adding time-based aging. The table has no previous aging policy. The agingcolumn column is used for aging. LIFETIME is 2 days. CYCLE is 30 minutes.

ALTER TABLE agingdemo4
       ADD AGING USE agingcolumn LIFETIME 2 DAYS CYCLE 30 MINUTES;
Command> DESCRIBE agingdemo4;
Table USER.AGINGDEMO4:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 2 days cycle 30 minutes on

This example illustrates that after you create an aging policy, you cannot change it. You must drop aging and redefine.

CREATE TABLE agingdemo5
       (agingid NUMBER NOT NULL PRIMARY KEY
       ,name VARCHAR2 (20)
       ,agingcolumn TIMESTAMP NOT NULL
       )
       AGING USE agingcolumn LIFETIME 3 DAYS OFF;
ALTER TABLE agingdemo5
      ADD AGING LRU;
 2980: Cannot add aging policy to a table with an existing aging policy. Have to
 drop the old aging first
The command failed.

Drop aging on the table and redefine with LRU aging.

ALTER TABLE agingdemo5
      DROP AGING;
ALTER TABLE agingdemo5
       ADD AGING LRU;
Command> DESCRIBE agingdemo5;
Table USER.AGINGDEMO5:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging lru on
1 table found.
(primary key columns are indicated with *)

This example alters a table by setting the aging state to OFF. The table has been defined with a time-based aging policy. If you set the aging state to OFF, aging is not done automatically. This is useful to use an external scheduler to control the aging process. Set aging state to OFF and then call the ttAgingScheduleNow procedure to start the aging process.

Command> DESCRIBE agingdemo4;
Table USER.AGINGDEMO4:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 2 days cycle 30 minutes on

ALTER TABLE AgingDemo4
       SET AGING OFF;

Note that when you describe agingdemo4, the aging policy is defined and the aging state is set to OFF.

Command> DESCRIBE agingdemo4;
Table USER.AGINGDEMO4:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 2 days cycle 30 minutes off
1 table found.
(primary key columns are indicated with *)

Call ttAgingScheduleNow to invoke aging with an external scheduler:

Command> CALL ttAgingScheduleNow ('agingdemo4');

Attempt to alter a table adding the aging column and then use that column for time-based aging. An error is generated.

Command> DESCRIBE x;
Table USER1.X:
  Columns:
   *ID                              TT_INTEGER NOT NULL
1 table found.
(primary key columns are indicated with *)
Command> ALTER TABLE x ADD COLUMN t TIMESTAMP;
Command> ALTER TABLE x ADD AGING USE t LIFETIME 2 DAYS;
 2993: Aging column cannot be nullable
The command failed.

Attempt to alter the LIFETIME clause for a table defined with time-based aging. The aging column is defined with data type TT_DATE. An error is generated because the LIFETIME unit is not expressed in DAYS.

Command> CREATE TABLE aging1 (col1 TT_DATE NOT NULL) AGING USE 
         col1 LIFETIME 2 DAYS;
Command> ALTER TABLE aging1 SET AGING LIFETIME 2 HOURS;
 2977: Only DAY lifetime unit is allowed with a TT_DATE column
The command failed.

Alter the employees table to add a new compressed column of state, which contains the full name of the state. Note that the employees table already has a compressed column group consisting of job_id and manager_id.

Command> ALTER TABLE employees 
         ADD COLUMN state VARCHAR2(20) 
         COMPRESS (state BY DICTIONARY);

Command> DESCRIBE employees; 
Table MYSCHEMA.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)
    STATE                           VARCHAR2 (20) INLINE
  COMPRESS ( ( JOB_ID, MANAGER_ID ) BY DICTIONARY,
             STATE BY DICTIONARY )
 
1 table found.
(primary key columns are indicated with *)

The following example drops the compressed column state from the employees table:

Command> ALTER TABLE employees
 DROP state;
Command> DESCRIBE employees; 
Table MYSCHEMA.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)
 COMPRESS ( ( JOB_ID, MANAGER_ID ) BY DICTIONARY )
 
1 table found.
(primary key columns are indicated with *)