6 SQL Statements

ALTER ACTIVE STANDBY PAIR

You can change an active standby pair by:

• Adding or dropping a subscriber database

• Altering store attributes. Only the PORT and TIMEOUT attributes can be set for subscribers.

• Including tables, sequences or cache groups in the replication scheme

• Excluding tables, sequences or cache groups from the replication scheme

See "Making other changes to an active standby pair" in Oracle TimesTen In-Memory Database Replication Guide.

Required privilege

ADMIN

SQL syntax

ALTER ACTIVE STANDBY PAIR { 
      SubscriberOperation | 
      StoreOperation | InclusionOperation |
      NetworkOperation } [...]

Syntax for SubscriberOperation:

{ADD | DROP } SUBSCRIBER FullStoreName

Syntax for StoreOperation:

ALTER STORE FullStoreName SET StoreAttribute

Syntax for InclusionOperation:

[{ INCLUDE | EXCLUDE }{TABLE [[Owner.]TableName [,...]]|
         CACHE GROUP [[Owner.]CacheGroupName [,...]]|
         SEQUENCE [[Owner.]SequenceName [,...]]} [,...]]

Syntax for NetworkOperation:

ADD ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName
      { { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost }
          PRIORITY Priority } [...] 
DROP ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName
      { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost } [...] 

Parameters

Description

• You must stop the replication agent before altering an active standby pair. The exceptions are for those objects and statements that are automatically replicated and included based on the values of the DDL_REPLICATION_LEVEL and DDL_REPLICATION_ACTION attributes, as described in "ALTER SESSION".

• You may only alter the active standby pair replication scheme on the active database. See "Making other changes to an active standby pair" in Oracle TimesTen In-Memory Database Replication Guide for more information.

• You may not use ALTER ACTIVE STANDBY PAIR when using Oracle Clusterware with TimesTen. See "Restricted commands and SQL statements" in Oracle TimesTen In-Memory Database Replication Guide for more information.

  Instead, perform the tasks described in "Changing the schema" section of the Oracle TimesTen In-Memory Database Replication Guide.

• Use ADD SUBSCRIBER FullStoreName to add a subscriber to the replication scheme.

• Use DROP SUBSCRIBER FullStoreName to drop a subscriber from the replication scheme.

• Use ALTER STORE FullStoreName SET StoreAttribute to change the attributes for the specified database. Only the PORT and TIMEOUT attributes can be set for subscribers.

• Use the INCLUDE or EXCLUDE clause to include the listed tables, sequences or cache groups in the replication scheme or to exclude them from the replication scheme. Use one INCLUDE or EXCLUDE clause for each object type (table, sequence or cache group). The ALTER ACTIVE STANDBY statement is not necessary for those objects and statements that are automatically replicated and included based on the values of the DDL_REPLICATION_LEVEL and DDL_REPLICATION_ACTION attributes, as described in "ALTER SESSION". However, if DDL_REPLICATION_LEVEL=2 and DDL_REPLICATION_ACTION="EXCLUDE," use the INCLUDE clause to include replicated objects into the replication scheme.

• When DDL_REPLICATION_LEVEL=2, the INCLUDE clause can only be used with empty tables on the active database. The contents of the corresponding tables on the standby and any subscribers will be truncated before the table is added to the replication scheme.

Examples

Add a subscriber to the replication scheme.

ALTER ACTIVE STANDBY PAIR
    ADD SUBSCRIBER rep4;

Drop two subscribers from the replication scheme.

ALTER ACTIVE STANDBY PAIR
    DROP SUBCRIBER rep3
    DROP SUBSCRIBER rep4;

Alter the store attributes of the rep3 and rep4 databases.

ALTER ACTIVE STANDBY PAIR
    ALTER STORE rep3 SET PORT 23000 TIMEOUT 180
    ALTER STORE rep4 SET PORT 23500 TIMEOUT 180;

Add a table, a sequence and two cache groups to the replication scheme.

ALTER ACTIVE STANDBY PAIR
    INCLUDE TABLE my.newtab
    INCLUDE SEQUENCE my.newseq
    INCLUDE CACHE GROUP my.newcg1, my.newcg2;

Add NetworkOperation clause to active standby pair:

ALTER ACTIVE STANDBY PAIR
ADD ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2"
MASTERIP "1.1.1.1" PRIORITY 1 SUBSCRIBERIP "2.2.2.2" PRIORITY 1;

See also

ALTER CACHE GROUP

The ALTER CACHE GROUP statement enables changes to the state, interval and mode of AUTOREFRESH.

Updates on Oracle tables can be propagated back to the TimesTen cache group with the use of AUTOREFRESH. AUTOREFRESH can be enabled when the cache group is a user managed cache group or is defined as READONLY with an AUTOREFRESH clause.

Any values or states set by ALTER CACHE GROUP are persistent. They are stored in the database and survive daemon and cache agent restarts.

For a description of cache group types, see "User managed and system managed cache groups".

Required privilege

No privilege is required for the cache group owner.

ALTER ANY CACHE GROUP for another user's cache group.

SQL syntax

This statement changes the AUTOREFRESH mode of the cache group, which determines which rows are updated during an autorefresh operation:

ALTER CACHE GROUP [Owner.]GroupName
        SET AUTOREFRESH MODE
        {INCREMENTAL | FULL}

This statement changes the AUTOREFRESH interval on the cache group:

ALTER CACHE GROUP [Owner.]GroupName 
        SET AUTOREFRESH INTERVAL IntervalValue 
        {MINUTE[S] | SECOND[S] | MILLISECOND[S] }

This statement alters the AUTOREFRESH state:

ALTER CACHE GROUP [Owner.]GroupName 
        SET AUTOREFRESH STATE
        {ON | OFF | PAUSED}

Parameters

Description

• A refresh does not occur immediately after issuing ALTER CACHE GROUP...SET AUTOREFRESH STATE. This statement only changes the state of AUTOREFRESH. When the transaction that contains the ALTER CACHE GROUP statement is committed, the cache agent is notified to schedule an AUTOREFRESH immediately, but the commit goes through without waiting for the completion of the refresh. The scheduling of the autorefresh operation is part of the transaction, but the refresh itself is not.

• If you issue an ALTER CACHE GROUP... SET AUTOREFRESH STATE OFF statement and there is an autorefresh operation currently running, then:

  • If LockWait interval is 0, the ALTER statement fails with a lock timeout error.

  • If LockWait interval is non-zero, then the current autorefresh transaction is rolled back, and the ALTER statement continues. This affects all cache groups with the same autorefresh interval.

• Replication cannot occur between cache groups with AUTOREFRESH and cache groups without AUTOREFRESH.

• If the ALTER CACHE GROUP statement is part of a transaction that is being replicated, and if the replication scheme has the RETURN TWOSAFE attribute, the transaction may fail.

• You cannot execute the ALTER CACHE GROUP statement when performed under the serializable isolation level. An error message is returned when attempted.

See also

ALTER FUNCTION

The ALTER FUNCTION statement recompiles a standalone stored function. Explicit recompilation eliminates the need for implicit runtime recompilation and prevents associated runtime compilation errors and performance overhead.

To recompile a function that is part of a package, recompile the package using the ALTER PACKAGE statement.

Required privilege

No privilege is required for the PL/SQL function owner.

ALTER ANY PROCEDURE for another user's function.

SQL syntax

ALTER FUNCTION [Owner.]FunctionName COMPILE
      [CompilerParametersClause [...]] 
      [REUSE SETTINGS] 

Parameters

Description

• The ALTER FUNCTION statement does not change the declaration or definition of an existing function. To redeclare or redefine a function, use the CREATE FUNCTION statement.

• TimesTen first recompiles objects upon which the function depends, if any of those objects are invalid.

• TimesTen also invalidates any objects that depend on the function, such as functions that call the recompiled function or package bodies that define functions that call the recompiled function.

• If TimesTen recompiles the function successfully, then the function becomes valid. If recompiling the function results in compilation errors, then TimesTen returns an error and the function remains invalid. Use the ttIsql command SHOW ERRORS to display compilation errors.

• During recompilation, TimesTen drops all persistent compiler settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this process, specify the REUSE SETTINGS clause.

See also

ALTER PACKAGE

The ALTER PACKAGE statement explicitly recompiles a package specification, package body, or both. Explicit recompilation eliminates the need for implicit runtime recompilation and prevents associated runtime compilation errors.

This statement recompiles all package objects together. You cannot use the ALTER PROCEDURE or ALTER FUNCTION statement to individually recompile a procedure or function that is part of a package.

Required privilege

No privilege is required for the package owner.

ALTER ANY PROCEDURE for another user's package.

SQL syntax

ALTER PACKAGE [Owner.]PackageName COMPILE
      [PACKAGE|SPECIFICATION|BODY]
      [CompilerParametersClause [...]] 
      [REUSE SETTINGS] 

Parameters

Description

• When you recompile a package specification, TimesTen invalidates local objects that depend on the specification, such as procedures that call procedures or functions in the package. The body of the package also depends on the specification. If you subsequently reference one of these dependent objects without first explicitly recompiling it, then TimesTen recompiles it implicitly at runtime.

• When you recompile a package body, TimesTen does not invalidate objects that depend on the package specification. TimesTen first recompiles objects upon which the body depends, if any of those objects are invalid. If TimesTen recompiles the body successfully, then the body become valid.

• When you recompile a package, both the specification and the body are explicitly recompiled. If there are no compilation errors, then the specification and body become valid. If there are compilation errors, then TimesTen returns an error and the package remains invalid.

See also

ALTER PROCEDURE

The ALTER PROCEDURE statement recompiles a standalone stored procedure. Explicit recompilation eliminates the need for implicit runtime recompilation and prevents associated runtime compilation errors and performance overhead.

To recompile a procedure that is part of a package, recompile the package using the ALTER PACKAGE statement.

Required privilege

No privilege is required for the procedure owner.

ALTER ANY PROCEDURE for another user's procedure.

SQL syntax

ALTER PROCEDURE [Owner.]ProcedureName COMPILE
      [CompilerParametersClause [...]] 
      [REUSE SETTINGS] 

Parameters

Description

• The ALTER PROCEDURE statement does not change the declaration or definition of an existing procedure. To redeclare or redefine a procedure, use the CREATE PROCEDURE statement.

• TimesTen first recompiles objects upon which the procedure depends, if any of those objects are invalid.

• TimesTen also invalidates any objects that depend on the procedure, such as procedures that call the recompiled procedure or package bodies that define procedures that call the recompiled procedure.

• If TimesTen recompiles the procedure successfully, then the procedure becomes valid. If recompiling the procedure results in compilation errors, then TimesTen returns an error and the procedure remains invalid. Use the ttIsql command SHOW ERRORS to display compilation errors.

• During recompilation, TimesTen drops all persistent compiler settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this process, specify the REUSE SETTINGS clause.

Examples

Query the system view USER_PLSQL_OBJECT_SETTINGS to check PLSQL_OPTIMIZE_LEVEL and PLSCOPE_SETTINGS for procedure query_emp. Alter query_emp by changing PLSQL_OPTIMIZE_LEVEL to 3. Verify results.

Command> SELECT PLSQL_OPTIMIZE_LEVEL, PLSCOPE_SETTINGS
       > FROM user_plsql_object_settings WHERE  name = 'QUERY_EMP';
< 2, IDENTIFIERS:NONE >
1 row found.

Command> ALTER PROCEDURE query_emp COMPILE PLSQL_OPTIMIZE_LEVEL = 3;
 
Procedure altered.
 
Command> SELECT PLSQL_OPTIMIZE_LEVEL, PLSCOPE_SETTINGS
       > FROM user_plsql_object_settings WHERE  name = 'QUERY_EMP';
< 3, IDENTIFIERS:NONE >
1 row found.

See also

ALTER REPLICATION

The ALTER REPLICATION statement adds, alters, or drops replication elements and changes the replication attributes of participating databases.

Most ALTER REPLICATION operations are supported only when the replication agent is stopped (ttAdmin -repStop). However, it is possible to dynamically add a subscriber database to a replication scheme while the replication agent is running. See "Altering Replication" in Oracle TimesTen In-Memory Database Replication Guide for more information.

Required privilege

ADMIN

SQL syntax

The ALTER REPLICATION statement has the syntax:

ALTER REPLICATION [Owner.]ReplicationSchemeName
  ElementOperation [...] | StoreOperation |
     NetworkOperation [...]

Specify ElementOperation one or more times:

ADD ELEMENT ElementName
   { DATASTORE | 
     { TABLE [Owner.]TableName [CheckConflicts] } | 
       SEQUENCE [Owner.]SequenceName }
   { MASTER | PROPAGATOR } FullStoreName
   { SUBSCRIBER FullStoreName [,... ] [ReturnServiceAttribute] } [ ... ] }
   { INCLUDE | EXCLUDE }
   { TABLE [[Owner.]TableName[,...]] | 
     CACHE GROUP [[Owner.]CacheGroupName[,...]] | 
     SEQUENCE [[Owner.]SequenceName[,...]] } [,...]

ALTER ELEMENT { ElementName | * IN FullStoreName ]
    ADD SUBSCRIBER FullStoreName [,...] [ReturnServiceAttribute] |
        ALTER SUBSCRIBER FullStoreName [,...]   |
            SET [ReturnServiceAttribute]   |
        DROP SUBSCRIBER FullStoreName [,... ]

ALTER ELEMENT * IN FullStoreName
    SET { MASTER | PROPAGATOR } FullStoreName

ALTER ELEMENT ElementName
    {SET NAME NewElementName | SET CheckConflicts}

ALTER ELEMENT ElementName 
    { INCLUDE | EXCLUDE } { TABLE [Owner.]TableName |
          CACHE GROUP [Owner.]CacheGroupName | 
          SEQUENCE [Owner.]SequenceName }[,...] 

DROP ELEMENT { ElementName | * IN FullStoreName }

CheckConflicts can only be set when replicating TABLE elements. The syntax is described in "CHECK CONFLICTS".

Syntax for ReturnServiceAttribute is:

{ RETURN RECEIPT [BY REQUEST] | NO RETURN }

StoreOperation clauses:

ADD STORE FullStoreName [StoreAttribute [... ]]
ALTER STORE FullStoreName SET StoreAttribute [... ]

Syntax for the StoreAttribute is:

[DISABLE RETURN {SUBSCRIBER | ALL} NumFailures]
[RETURN SERVICES {ON | OFF} WHEN [REPLICATION] STOPPED]
[DURABLE COMMIT {ON | OFF}]
[RESUME RETURN MilliSeconds ]
[LOCAL COMMIT ACTION {NO ACTION| COMMIT}]
[RETURN WAIT TIME Seconds]
[COMPRESS TRAFFIC {ON | OFF} ]
[PORT PortNumber ]
[TIMEOUT Seconds ]
[FAILTHRESHOLD Value]
[CONFLICT REPORTING SUSPEND AT Value ]
[CONFLICT REPORTING RESUME AT Value ]
[TABLE DEFINITION CHECKING {EXACT|RELAXED}]

Specify NetworkOperation one or more times:

ADD ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName
  { { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost }
      PRIORITY Priority } [...] 

DROP ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName
  { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost } [...] 

Parameters

Description

• ALTER ELEMENT DROP SUBSCRIBER deletes a subscriber for a particular replication element.

• ALTER ELEMENT SET NAME may be used to change the name of a replication element when it conflicts with one already defined at another database. SET NAME does not admit the use of * IN FullStoreName. The FullStoreName must be the database's file base name. For example, if the database file name is data.ds0, then data is the file base name.

• ALTER ELEMENT SET MASTER may be used to change the master database for replication elements. The * IN FullStoreName option must be used for the MASTER operation. That is, a master database must transfer ownership of all of its replication elements, thereby giving up its master role entirely. Typically, this option is used in ALTER REPLICATION statements requested at SUBSCRIBER databases after the failure of a (common) MASTER.

• To transfer ownership of the master elements to the subscriber:

  1. Manually drop the replicated elements by executing an ALTER REPLICATION DROP ELEMENT statement for each replicated table.

  2. Use ALTER REPLICATION ADD ELEMENT to add each table back to the replication scheme, with the newly designated MASTER / SUBSCRIBER roles.

• ALTER REPLICATION ALTER ELEMENT SET MASTER does not automatically retain the old master as a subscriber in the scheme. If this is desired, execute an ALTER REPLICATION ALTER ELEMENT ADD SUBSCRIBER statement.

  Note:

  There is no ALTER ELEMENT DROP MASTER. Each replication element must have exactly one MASTER database, and the currently designated MASTER cannot be deleted from the replication scheme.

• Stop the replication agent before you use the NetworkOperation clause.

• You cannot alter the following replication schemes with the ALTER REPLICATION statement:

  • Any active standby pair. Instead, use ALTER ACTIVE STANDBY PAIR.

  • A Clusterware-managed active standby pair. Instead, perform the tasks described in "Changing the schema" section of the Oracle TimesTen In-Memory Database Replication Guide.

Examples

This example sets up replication for an additional table westleads that is updated on database west and replicated to database east.

ALTER REPLICATION r1
   ADD ELEMENT e3 TABLE westleads
     MASTER west ON "westcoast"
     SUBSCRIBER east ON "eastcoast";

This example adds an additional subscriber (backup) to table westleads.

ALTER REPLICATION r1
   ALTER ELEMENT e3
     ADD SUBSCRIBER backup ON "backupserver";

This example changes the element name of table westleads from e3 to newelementname.

ALTER REPLICATION r1
   ALTER ELEMENT e3
     SET NAME newelementname;

This example makes newwest the master for all elements for which west currently is the master.

ALTER REPLICATION r1
   ALTER ELEMENT * IN west
     SET MASTER newwest;

This element changes the port number for east.

ALTER REPLICATION r1
   ALTER STORE east ON "eastcoast" SET PORT 22251;

This example adds my.tab1 table to the ds1 database element in my.rep1 replication scheme.

ALTER REPLICATION my.rep1
  ALTER ELEMENT ds1 DATASTORE
     INCLUDE TABLE my.tab1;

This example adds my.cg1 cache group to ds1 database in my.rep1 replication scheme.

ALTER REPLICATION my.rep1
  ALTER ELEMENT ds1 DATASTORE
     INCLUDE CACHE GROUP my.cg1;

This example adds ds1 database to my.rep1 replication scheme. Include my.tab2 table, my.cg2 cache group, and my.cg3 cache group in the database.

ALTER REPLICATION my.rep1
  ADD ELEMENT ds1 DATASTORE
     MASTER rep2
     SUBSCRIBER rep1, rep3
     INCLUDE TABLE my.tab2
     INCLUDE CACHE GROUP my.cg2, my.cg3;

This example adds ds2 database to a replication scheme but exclude my.tab1 table, my.cg0 cache group and my.cg1 cache group.

ALTER REPLICATION my.rep1
  ADD ELEMENT ds2 DATASTORE
     MASTER rep2
     SUBSCRIBER rep1
     EXCLUDE TABLE my.tab1
     EXCLUDE CACHE GROUP my.cg0, my.cg1;

Add NetworkOperation clause:

ALTER REPLICATION r
ADD ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2"
MASTERIP "1.1.1.1" PRIORITY 1 SUBSCRIBERIP "2.2.2.2"
    PRIORITY 1
MASTERIP "3.3.3.3" PRIORITY 2 SUBSCRIBERIP "4.4.4.4" PRIORITY 2;

Drop NetworkOperation clause:

ALTER REPLICATION r
DROP ROUTE MASTER repl ON "machine1" SUBSCRIBER rep2 ON "machine2"
MASTERIP "1.1.1.1" SUBSCRIBERIP "2.2.2.2"
MASTERIP "3.3.3.3" SUBSCRIBERIP "4.4.4.4";

See also

To drop a table from a database, see "Altering a replicated table" in Oracle TimesTen In-Memory Database Replication Guide.

ALTER SESSION

The ALTER SESSION statement changes session parameters dynamically.

Required privilege

None

SQL syntax

ALTER SESSION SET
  {DDL_REPLICATION_ACTION={'INCLUDE'|'EXCLUDE'} | 
   DDL_REPLICATION_LEVEL={1|2} |
   NLS_SORT = {BINARY| SortName} |
   NLS_LENGTH_SEMANTICS = {BYTE|CHAR} |
   NLS_NCHAR_CONV_EXCP = {TRUE|FALSE} |
   ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED} |
   PLSQL_TIMEOUT = n |
   PLSQL_OPTIMIZE_LEVEL = {0|1|2|3}|
   PLSCOPE_SETTINGS = {'IDENTIFIERS:ALL'|'IDENTIFIERS:NONE'} |
   PLSQL_CONN_MEM_LIMIT = n |
   REPLICATION_TRACK = TrackNumber
   } 

Parameters

Description

• The ALTER SESSION statement affects commands that are subsequently executed by the session. The new session parameters take effect immediately.

• Operations involving character comparisons support linguistic sensitive collating sequences. Case-insensitive sorts may affect DISTINCT value interpretation.

• Implicit and explicit conversions between CHAR and NCHAR are supported.

• Conversions between CHAR and NCHAR are not allowed when using the TIMESTEN8 character set.

• You can use the SQL string functions with the supported character sets. For example, UPPER and LOWER functions support non-ASCII CHAR and VARCHAR2 characters as well as NCHAR and NVARCHAR2 characters.

• Choice of character set could have an impact on memory consumption for CHAR and VARCHAR2 column data.

• The character sets of all databases involved in a replication scheme must match.

• To add an existing table to an active standby pair, set DDL_REPLICATION_LEVEL=2 and DDL_REPLICATION_ACTION to INCLUDE. Alternatively, you can use the ALTER ACTIVE STANDBY PAIR INCLUDE TABLE statement if DDL_REPLICATION_ACTION is set to EXCLUDE. In this case, the table must be empty and present on all databases before executing the ALTER ACTIVE STANDBY PAIR INCLUDE TABLE statement as the table contents will be truncated when this statement is executed.

• Objects are only replicated to TimesTen instances of release 11.2.1.8 or greater that are in a replication scheme using an active standby pair.

Examples

Use the ALTER SESSION statement to change PLSQL_TIMEOUT to 60 seconds. Use a second ALTER SESSION statement to change PLSQL_OPTIMIZE_LEVEL to 3. Then call ttConfiguration to display the new values.

Command> ALTER SESSION SET PLSQL_TIMEOUT = 60;
Session altered.Command> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
Session altered.

Command> CALL TTCONFIGURATION ();
< CkptFrequency, 600 >
< CkptLogVolume, 0 >
< CkptRate, 0 >
...
< PLSQL_OPTIMIZE_LEVEL, 3 >
< PLSQL_TIMEOUT, 60 >
...
47 rows found.

In this example, set PLSQL_TIMEOUT to 20 seconds. Attempt to execute a program that loops indefinitely. In 20 seconds, execution is terminated and an error is returned.

Command> ALTER SESSION SET PLSQL_TIMEOUT = 20;

Command> DECLARE v_timeout NUMBER;
       > BEGIN
       >   LOOP
       >     v_timeout :=0;
       >     EXIT WHEN v_timeout < 0;
       >   END LOOP;
       > END;
       > /
 8509: PL/SQL execution terminated; PLSQL_TIMEOUT exceeded

Call ttConfiguration to display the current PLSCOPE_SETTINGS value. Use the ALTER SESSION statement to change the PLSCOPE_SETTINGS value to IDENTIFIERS:ALL. Create a dummy procedure p. Query the system view SYS.USER_PLSQL_OBJECT_SETTINGS to confirm that the new setting is applied to procedure p.

Command> CALL TTCONFIGURATION ();
< CkptFrequency, 600 >
< CkptLogVolume, 0 >
< CkptRate, 0 >
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
...
47 rows found.

Command> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
Session altered.
 
Command> CREATE OR REPLACE PROCEDURE p IS
       > BEGIN
       >  NULL;
       > END;
       > /
Procedure created.
 
Command> SELECT PLSCOPE_SETTINGS FROM SYS.USER_PLSQL_OBJECT_SETTINGS WHERE
       > NAME = 'p';
< IDENTIFIERS:ALL >
1 row found.

The following example uses the ALTER SESSION statement to change the NLS_SORT setting from BINARY to BINARY_CI to BINARY_AI. The database and connection character sets are WE8ISO8859P1.

Command> connect "dsn=cs;ConnectionCharacterSet=WE8ISO8859P1";
Connection successful: DSN=cs;UID=user;DataStore=/datastore/user/cs;
DatabaseCharacterSet=WE8ISO8859P1;
ConnectionCharacterSet=WE8ISO8859P1;PermSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command>#Create the Table
Command> CREATE TABLE collatingdemo (letter VARCHAR2 (10));
Command>#Insert values
Command> INSERT INTO collatingdemo VALUES ('a');
1 row inserted.
Command> INSERT INTO collatingdemo VALUES ('A');
1 row inserted.
Command> INSERT INTO collatingdemo VALUES ('Y');
1 row inserted.
Command> INSERT INTO collatingdemo VALUES ('ä');
1 row inserted.
Command>#SELECT
Command> SELECT * FROM collatingdemo;
< a >
< A >
< Y >
< ä >
4 rows found.
Command>#SELECT with ORDER BY
Command> SELECT * FROM collatingdemo ORDER BY letter;
< A >
< Y >
< a >
< ä >
4 rows found.
Command>#set NLS_SORT to BINARY_CI and SELECT
Command> ALTER SESSION SET NLS_SORT = BINARY_CI;
Command> SELECT * FROM collatingdemo ORDER BY letter;
< a >
< A >
< Y >
< Ä >
< ä >
4 rows found.
Command>#Set NLS_SORT to BINARY_AI and SELECT
Command> ALTER SESSION SET NLS_SORT = BINARY_AI;
Command> SELECT * FROM collatingdemo ORDER BY letter;
< ä >
< a >
< A >
< Y >
4 rows found.

The following example enables parallel replication and uses the ALTER SESSION statement to change the replication track number to 5 for the current connection. To enable parallel replication for replication schemes, set ReplicationApplyOrdering to 1. Then, always set REPLICATION_TRACK to a number less than or equal to ReplicationParallelism. For example, the ReplicationParallelism connection attribute could be set to 6, which is higher than the value of 5 set for REPLICATION_TRACK.

Command> ALTER SESSION SET REPLICATION_TRACK = 5;
Session altered.

The following example enables replication of adding and dropping columns, tables, synonyms and indexes by setting the following on the active database in an alter standby replication pair: DDLReplicationLevel to 2 and DDLReplicationAction to 'INCLUDE'.

Command > ALTER SESSION SET DDL_REPLICATION_LEVEL=2;
Session altered.

Command > ALTER SESSION SET DDL_REPLICATION_ACTION='INCLUDE';
Session altered.

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.

SQL syntax

To add one column:

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

or 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: The default clause is required):

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

To add multiple NOT NULL columns (Note: The default clause is required):

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

The CompressColumns syntax is as follows:

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

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

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

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

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}

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

Understanding partitions when using ALTER TABLE

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 seconday partitions but are now in the initial partition.

If your database is involved in replication and you wish 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);

Description

• 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. 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.

  However, it cannot be used to alter a replicated table that is part of a TWOSAFE BY REQUEST transaction. If DDLCommitBehavior=1, this operation results in error 8051. If DDLCommitBehavior=0, the operation succeeds because a commit is performed before the ALTER TABLE operation, resulting in the ALTER TABLE operation being in a new transaction which is not part of the TWOSAFE BY REQUEST transaction.

• Columns referenced by materialized views cannot be dropped.

• 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 add a NOT NULL column to a table that is part of a replication scheme. You must remove the table from the replication scheme first before you can add a NOT NULL column to it.

  • 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.

• 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.

• The ALTER TABLE DROP statement removes one or more columns from an existing table. The 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.

• 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.

  • You must stop the replication agent before adding or dropping a foreign key on a replicated table.

• 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 tables that participate in TimesTen replication. If the operation is attempted on a table that is either being replicated or is a replicated table, TimesTen returns an error.

  • Foreign keys cannot be added or dropped on views or temporary tables.

• 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 in-memory columnar compression of tables:

  • You can only add compressed column groups with the ALTER TABLE statement if the table was enabled for compression at table creation. You can add uncompressed columns to any table, including tables enabled for compression. A table is enabled for compression during creation when OPTIMIZED FOR READ is specified. Refer to "In-memory columnar compression of tables" for more details on adding compressed column groups to a table.

  • You cannnot 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.

Examples

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 illegalindex (Ccl1 CHAR (20));
Command> ALTER TABLE illegalindex USE RANGE INDEX;
 2810: The table has no primary key so cannot change its index type
The command failed.
Command> ALTER TABLE illegalindex 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 3 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 if you want 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 ) OPTIMIZED FOR READ
 
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 ) OPTIMIZED FOR READ
 
1 table found.
(primary key columns are indicated with *)

See also

"Implementing aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide

ALTER USER

The ALTER USER statement enables a user to change the user's own password. A user with the ADMIN privilege can change another user's password.

This statement also enables a user to change another user from internal to external or from external to internal.

Required privilege

No privilege is required to change the user's own password.

ADMIN privilege is required to change another user's password.

ADMIN privilege is required to change users from internal to external and from external to internal.

SQL syntax

ALTER USER user IDENTIFIED BY {password | "password"}
ALTER USER user IDENTIFIED EXTERNALLY

Parameters

Description

• Database users can be internal or external.

  • Internal users are defined for a TimesTen database.

  • External users are defined by an external authority, such as the operating system. External users cannot be assigned a TimesTen password.

• If you are an internal user connected as user, execute this statement to change your TimesTen password.

• Passwords are case-sensitive.

• You cannot alter a user across a client/server connection. You must use a direct connection when altering a user.

• When replication is configured, this statement is replicated.

Examples

To change the password for internal user terry to "12345" from its current setting, use:

ALTER USER terry IDENTIFIED BY "12345";
User altered.

To change user terry to an external user:

ALTER USER terry IDENTIFIED EXTERNALLY;
User altered.

To change user terry back to an internal user, provide a password:

ALTER USER terry IDENTIFIED BY "secret";
User altered.

See also

CALL

Use the CALL statement to invoke a TimesTen built-in procedure or to execute a PL/SQL procedure or function that is standalone or part of a package from within SQL.

Required privilege

The privileges required for invoking each TimesTen built-in procedure are listed in the description of each procedure in the "Built-In Procedures" section in the Oracle TimesTen In-Memory Database Reference.

No privileges are required for an owner calling its own PL/SQL procedure or function that is standalone or part of a package using the CALL statement. For all other users, the EXECUTE privilege on the procedure or function or on the package in which it is defined is required.

SQL syntax

To call a TimesTen built-in procedure:

CALL [TimesTenBuiltIn [( arguments )] 

When calling PL/SQL procedures or functions that are standalone or part of a package, you can either call these by name or as the result of an expression.

To call a PL/SQL procedure:

CALL [Owner.][Package.]ProcedureName [( arguments )] 

To call a PL/SQL function that returns a parameter, one of the following are appropriate:

CALL [Owner.][Package.]FunctionName [( arguments )] INTO :return_param

Parameters

Description

Detailed information on how to execute PL/SQL procedures or functions with the CALL statement in TimesTen is provided in "How to execute PL/SQL procedures and functions" in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide, "Using CALL to execute procedures and functions" in the Oracle TimesTen In-Memory Database C Developer's Guide, or "Using CALL to execute procedures and functions" in the Oracle TimesTen In-Memory Database Java Developer's Guide.

Examples

The following is the definition of the mytest function:

create or replace function mytest return number is
begin
  return 1;
end;
/

Perform the following to execute the mytest function in a CALL statement:

Command> variable n number;
Command> call mytest() into :n;
Command> print n;
N                    : 1

The following example creates a function that returns the salary of the employee whose employee ID is specified as input, then calls the function and displays the result that was returned.

Command> CREATE OR REPLACE FUNCTION get_sal
       >   (p_id employees.employee_id%TYPE) RETURN NUMBER IS
       >    v_sal employees.salary%TYPE := 0;
       > BEGIN
       >   SELECT salary INTO v_sal FROM employees
       >     WHERE employee_id = p_id;
       >   RETURN v_sal;
       > END get_sal;
       > /
 
Function created.
 
Command> variable n number;
Command> call get_sal(100) into :n;
Command> print n;
N                    : 24000

COMMIT

The COMMIT statement ends the current transaction and makes permanent all changes performed in the transaction. A transaction is a sequence of SQL statements treated as a single unit.

Required privilege

None

SQL syntax

COMMIT [WORK]

Parameters

The COMMIT statement enables the following optional keyword:

Description

• Until you commit a transaction:

  • You can see any changes you have made during the transaction but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.

  • You can roll back (undo) changes made during the transaction with the ROLLBACK statement.

• This statement releases transaction locks.

• For passthrough, the Oracle transaction will also be committed.

• A commit closes all open cursors.

Examples

Insert row into regions table of the HR schema and commit transaction. First set autocommit to 0:

Command> SET AUTOCOMMIT 0;
Command> INSERT INTO regions VALUES (5,'Australia');
1 row inserted.
Command> COMMIT;
Command> SELECT * FROM regions;
< 1, Europe >
< 2, Americas >
< 3, Asia >
< 4, Middle East and Africa >
< 5, Australia >
5 rows found.

See also

ROLLBACK

CREATE ACTIVE STANDBY PAIR

This statement creates an active standby pair. It includes an active master database, a standby master database, and may also include one or more read-only subscribers. The active master database replicates updates to the standby master database, which propagates the updates to the subscribers.

Required privilege

ADMIN

SQL syntax

CREATE ACTIVE STANDBY PAIR
  FullStoreName, FullStoreName [ReturnServiceAttribute]
    [SUBSCRIBER FullStoreName [,...]]
    [STORE FullStoreName [StoreAttribute [...]]]
    [NetworkOperation [...] ]
    [{ INCLUDE | EXCLUDE }{TABLE [[Owner.]TableName [,...]]|
         CACHE GROUP [[Owner.]CacheGroupName [,...]]|
         SEQUENCE [[Owner.]SequenceName [,...]]} [,...]]

The syntax for ReturnServiceAttribute is

{ RETURN RECEIPT [BY REQUEST] |
  RETURN TWOSAFE [BY REQUEST] |
  NO RETURN }

Syntax for StoreAttribute is:

[ DISABLE RETURN {SUBSCRIBER | ALL} NumFailures ]
  [ RETURN SERVICES {ON | OFF} WHEN [REPLICATION] STOPPED ]
  [ DURABLE COMMIT {ON | OFF}]
  [ RESUME RETURN MilliSeconds ]
  [ LOCAL COMMIT ACTION {NO ACTION | COMMIT} ]
  [ RETURN WAIT TIME Seconds ]
  [ COMPRESS TRAFFIC {ON | OFF}
  [ PORT PortNumber ]
  [ TIMEOUT Seconds ]
  [ FAILTHRESHOLD Value ]

Syntax for NetworkOperation:

ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName
  { { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost }
      PRIORITY Priority } [...]

Parameters

Description

• CREATE ACTIVE STANDBY PAIR is immediately followed by the names of the two master databases. The master databases are later designated as ACTIVE and STANDBY using the ttRepStateSet built-in procedure. See "Setting up an active standby pair with no cache groups" in Oracle TimesTen In-Memory Database Replication Guide.

• The SUBSCRIBER clause lists one or more read-only subscriber databases. You can designate up to 127 subscriber databases.

• Replication between the active master database and the standby master database can be RETURN TWOSAFE, RETURN RECEIPT, or asynchronous. RETURN TWOSAFE ensures no transaction loss.

• Use the INCLUDE and EXCLUDE clauses to exclude the listed tables, sequences and cache groups from replication, or to include only the listed tables, sequences and cache groups, excluding all others.

• If the active standby pair has the RETURN TWOSAFE attribute and replicates a cache group, a transaction may fail if:

  • The transaction that is being replicated contains an ALTER TABLE statement or an ALTER CACHE GROUP statement

  • The transaction contains an INSERT, UPDATE or DELETE statement on a replicated table, replicated cache group or an asynchronous writethrough cache group

• Using an active standby pair to replicate read-only cache groups and asynchronous writethrough (AWT) cache groups is supported.

• You cannot use an active standby pair to replicate synchronous writethrough (SWT) cache groups. If you are using an active standby pair to replicated a database with SWT cache groups, you must either drop or exclude the SWT cache groups.

• You cannot execute the CREATE ACTIVE STANDBY PAIR statement when Oracle Clusterware is used with TimesTen.

Examples

This example creates an active standby pair whose master databases are rep1 and rep2. There is one subscriber, rep3. The type of replication is RETURN RECEIPT. The statement also sets PORT and TIMEOUT attributes for the master databases.

CREATE ACTIVE STANDBY PAIR rep1, rep2 RETURN RECEIPT
  SUBSCRIBER rep3
  STORE rep1 PORT 21000 TIMEOUT 30
  STORE rep2 PORT 22000 TIMEOUT 30;

Specify NetworkOperation clause to control network interface:

CREATE ACTIVE STANDBY PAIR rep1,rep2
ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2"
MASTERIP "1.1.1.1" PRIORITY 1 SUBSCRIBERIP "2.2.2.2" PRIORITY 1;
ROUTE MASTER rep2 ON "machine2" SUBSCRIBER rep1 ON "machine1"
MASTERIP "2.2.2.2" PRIORITY 1 SUBSCRIBERIP "1.1.1.1" PRIORITY 1;

See also

CREATE CACHE GROUP

The CREATE CACHE GROUP statement:

• Creates the table defined by the cache group

• Loads all new information associated with the cache group in the appropriate system tables.

A cache group is a set of tables related through foreign keys that cache data from tables in an Oracle database. There is one root table that does not reference any of the other tables. All other cache tables in the cache group reference exactly one other table in the cache group. In other words, the foreign key relationships form a tree.

A cache table is a set of rows satisfying the conditions:

• The rows constitute a subset of the rows of a vertical partition of an Oracle table.

• The rows are stored in a TimesTen table with the same name as the Oracle table.

If a database has more than one cache group, the cache groups must correspond to different Oracle (and TimesTen) tables.

Cache group instance refers to a row in the root table and all the child table rows related directly or indirectly to the root table rows.

User managed and system managed cache groups

A cache group can be either system managed or user managed.

A system managed cache group is fully managed by TimesTen and has fixed properties. System managed cache group types include:

• Read-only cache groups are updated in the Oracle database, and the updates are propagated from Oracle to the cache.

• Asynchronous writethrough (AWT) cache groups are updated in the cache and the updates are propagated to the Oracle database. Transactions continue executing on the cache without waiting for a commit on Oracle.

• Synchronous writethrough (SWT) cache groups are updated in the cache and the updates are propagated to the Oracle database. Transactions are committed on the cache after notification that a commit has occurred on Oracle.

Because TimesTen manages system managed cache groups, including loading and unloading the cache group, certain statements and clauses cannot be used in the definition of these cache groups, including:

• WHERE clauses in AWT and SWT cache group definitions

• READONLY, PROPAGATE and NOT PROPAGATE in cache table definitions

• AUTOREFRESH in AWT and SWT cache group definitions

The FLUSH CACHE GROUP and REFRESH CACHE GROUP operations are not allowed for AWT and SWT cache groups.

You must stop the replication agent before creating an AWT cache group.

A user managed cache group must be managed by the application or user. PROPAGATE in a user managed cache group is synchronous. The table-level READONLY keyword can only be used for user managed cache groups.

In addition, both TimesTen and Oracle must be able to parse all WHERE clauses.

Explicitly loaded cache groups and dynamic cache groups

Cache groups can be explicitly or dynamically loaded.

In cache groups that are explicitly loaded, new cache instances are loaded manually into the TimesTen cache tables from the Oracle tables using a LOAD CACHE GROUP or REFRESH CACHE GROUP statement or automatically using an autorefresh operation.

In a dynamic cache group, new cache instances can be loaded manually into the TimesTen cache tables by using a LOAD CACHE GROUP or on demand using a dynamic load operation. In a dynamic load operation, data is automatically loaded into the TimesTen cache tables from the cached Oracle tables when a SELECT, UPDATE, DELETE or INSERT statement is issued on one of the cache tables, where the data is not present in the cache table but does exist in the cached Oracle table. A manual refresh or automatic refresh operation on a dynamic cache group can result in the updating or deleting of existing cache instances, but not in the loading of new cache instances.

Any cache group type (read-only, asynchronous writethrough, synchronous writethrough, user managed) can be defined as an explicitly loaded cache group.

Any cache group type can be defined as a dynamic cache group except a user managed cache group that has both the AUTOREFRESH cache group attribute and the PROPAGATE cache table attribute.

Data in a dynamic cache group is aged out because LRU aging is defined by default. Use the ttAgingLRUConfig built-in procedure to override the space usage thresholds for LRU aging. You can also define time-based aging on a dynamic cache group to override LRU aging.

For more information on explicitly loaded and dynamic cache groups, see Oracle In-Memory Database Cache User's Guide. For more information about the dynamic load operation, see "Dynamically loading a cache instance" in Oracle In-Memory Database Cache User's Guide.

Local and global cache groups

You can create either local or global cache groups.

In a local cache group, data in the cache tables are not shared across TimesTen databases even if the databases are members of the same cache grid. Therefore, the databases can have overlapping data or the same data. Any cache group type can be defined as a local cache group. A local cache group can be either dynamically or explicitly loaded.

In a global cache group, data in the cache tables are shared among TimesTen databases within a cache grid. Updates to the same data by different grid members are coordinated by the grid. Only an AWT cache group can be defined as a global cache group.

For more information on local and global cache groups, see "Defining Cache Groups" in the Oracle In-Memory Database Cache User's Guide. In addition, see "Example of data sharing among the grid members" in Oracle In-Memory Database Cache User's Guide.

Required privilege

CREATE CACHE GROUP or CREATE ANY CACHE GROUP and

CREATE TABLE (if all tables in the cache group are owned by the current user) or CREATE ANY TABLE (if at least one of the tables in the cache group is not owned by the current user).

SQL syntax

There are CREATE CACHE GROUP statements for each type of cache group:

• CREATE READONLY CACHE GROUP

• CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP

• CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP

• CREATE USERMANAGED CACHE GROUP

There is one CREATE CACHE GROUP statement to create a global cache group:

• CREATE WRITETHROUGH GLOBAL CACHE GROUP

CREATE READONLY CACHE GROUP

For read-only cache groups, the syntax is:

CREATE [DYNAMIC] READONLY CACHE GROUP [Owner.]GroupName
 [AUTOREFRESH
  [MODE {INCREMENTAL | FULL}]
  [INTERVAL IntervalValue {MINUTE[S] | SECOND[S] | MILLISECOND[S] }]
  [STATE {ON|OFF|PAUSED}]
 ]
 FROM
  {[Owner.]TableName (
    {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
    [,FOREIGN KEY(ColumnName [,...])
            REFERENCES RefTableName (ColumnName [,...])
                    [ON DELETE CASCADE]
 [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [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]
 ]
 [WHERE ExternalSearchCondition]
} [,...];

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP

For asynchronous writethrough cache groups, the syntax is:

CREATE [DYNAMIC] [ASYNCHRONOUS] WRITETHROUGH CACHE GROUP   [Owner.]GroupName
  FROM
   {[Owner.]TableName (
     {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
     [FOREIGN KEY(ColumnName [,...])
          REFERENCES RefTableName (ColumnName [,...])]
                         [ ON DELETE CASCADE ]
 UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [AGING {LRU|
     USE ColumnName
          LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}
         [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
     }[ON|OFF]
 ]
} [,...];

CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP

For synchronous writethrough cache groups, the syntax is:

CREATE [DYNAMIC] SYNCHRONOUS WRITETHROUGH
CACHE GROUP [Owner.]GroupName
 FROM 
   {[Owner.]TableName (
     {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
     [FOREIGN KEY(ColumnName [,...])
            REFERENCES RefTableName (ColumnName [,...])}]
                             [ ON DELETE CASCADE ]
 [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [AGING {LRU|
     USE ColumnName
         LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}
         [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
     }[ON|OFF]
 ]
} [,...];

CREATE USERMANAGED CACHE GROUP

For user managed cache groups, the syntax is:

CREATE [DYNAMIC][USERMANAGED] CACHE GROUP [Owner.]GroupName
 [AUTOREFRESH
   [MODE {INCREMENTAL | FULL}]
   [INTERVAL IntervalValue {MINUTE[S] | SECOND[S] | MILLISECOND[S] }]
   [STATE {ON|OFF|PAUSED}]
 ]
  FROM 
   {[Owner.]TableName (
    {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
    [FOREIGN KEY(ColumnName[,...])
          REFERENCES RefTableName (ColumnName [,...])]
                  [ON DELETE CASCADE]
    [, {READONLY | PROPAGATE | NOT PROPAGATE}]
 [UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [AGING {LRU|
         USE ColumnName
             LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}
             [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
        }[ON|OFF]
 ]
 [WHERE ExternalSearchCondition]
} [,...];

CREATE WRITETHROUGH GLOBAL CACHE GROUP

The following syntax demonstrates how to create a global cache group to cache data within a cache grid. Specify the DYNAMIC attribute to enable dynamic load from the Oracle database for the cache group.

CREATE [DYNAMIC] [ASYNCHRONOUS] WRITETHROUGH GLOBAL CACHE GROUP  [Owner.]GroupName
  FROM
   {[Owner.]TableName (
     {ColumnDefinition[,...]}
    [,PRIMARY KEY(ColumnName[,...])]
     [FOREIGN KEY(ColumnName [,...])
          REFERENCES RefTableName (ColumnName [,...])]
                         [ ON DELETE CASCADE ]
 UNIQUE HASH ON (HashColumnName[,...]) PAGES=PrimaryPages]
 [AGING {LRU|
     USE ColumnName
          LIFETIME Num1 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}
         [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
     }[ON|OFF]
 ]
} [,...];

Parameters

Following are the parameters for the cache group definition before the FROM keyword:

Everything after the FROM keyword comprises the definitions of the Oracle tables cached in the cache group. The syntax for each table definition is similar to that of a CREATE TABLE statement. However, primary key constraints are required for the cache group table.

Table definitions have the following parameters:

Description

• Two cache groups cannot have the same owner name and group name. If you do not specify the owner name, your login becomes the owner name for the new cache group.

• Neither a cache table name nor a cache group name can contain #.

• Dynamic parameters are not allowed in the WHERE clause.

• Oracle temporary tables cannot be cached.

• Each table must correspond to a table in the Oracle database.

• You cannot use lowercase delimited identifiers to name your cache tables. Table names in TimesTen are case-insensitive and are stored as uppercase. The name of the cache table must be the same as the Oracle table name. Uppercase table names on TimesTen will not match mixed case table names on Oracle. As a workaround, create a synonym for your table in Oracle and use that synonym as the table name for the cache group. This workaround is not available for read-only cache groups or cache groups with the AUTOREFRESH parameter set.

• Each column in the cache table must match each column in the Oracle table, both in name and in data type. See "Mappings between Oracle and TimesTen data types" in Oracle In-Memory Database Cache User's Guide. In addition, each column name must be fully qualified with an owner and table name when referenced in a WHERE clause.

• The WHERE clause can only directly refer to the cache group table. Tables that are not in the cache group can only be referenced with a subquery.

• Generally, you do not have to fully qualify the column names in the WHERE clause of the CREATE CACHE GROUP, LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP or FLUSH CACHE GROUP statements. However, since TimesTen automatically generates queries that join multiple tables in the same cache group, a column needs to be fully qualified if there is more than one table in the cache group that contains columns with the same name.

• By default, a range index is created to enforce the primary key for a cache group table. Use the UNIQUE HASH clause to specify a hash index for the primary key.

  • If your application performs range queries over a cache group table's primary key, then choose a range index for that cache group table by omitting the UNIQUE HASH clause.

  • If, however, your application performs only exact match lookups on the primary key, then a hash index may offer better response time and throughput. In such a case, specify the UNIQUE HASH clause. See "CREATE TABLE" for more information on the UNIQUE HASH clause.

  • Use ALTER TABLE to change the representation of the primary key index for a table.

• For cache group tables with the PROPAGATE attribute and for tables of SWT and AWT cache groups, foreign keys specified with ON DELETE CASCADE must be a proper subset of foreign keys with ON DELETE CASCADE in the Oracle tables.

• You cannot execute the CREATE CACHE GROUP statement when performed under the serializable isolation level. An error message is returned when attempted.

AUTOREFRESH in cache groups

The AUTOREFRESH parameter automatically propagates changes from the Oracle database to TimesTen cache groups. For explicitly loaded cache groups, deletes, updates and inserts are automatically propagated from the Oracle database to the cache group. For dynamic cache groups, only deletes and updates are propagated. Inserts to the specified Oracle tables are not propagated to dynamic cache groups. They are dynamically loaded into IMDB Cache when referenced by the application. They can also be explicitly loaded by the application.

To use autorefresh with a cache group, you must specify AUTOREFRESH when you create the cache group. You can change the MODE, STATE and INTERVAL AUTOREFRESH settings after a cache group has been created by using the ALTER CACHE GROUP command. Once a cache group has been specified as either AUTOREFRESH or PROPAGATE, you cannot change these attributes.

TimesTen supports FULL or INCREMENTAL AUTOREFRESH. In FULL mode, the entire cache is periodically unloaded and then reloaded. In INCREMENTAL mode, TimesTen installs triggers in the Oracle database to track changes and periodically updates only the rows that have changed in the specified Oracle tables. The first incremental refresh is always a full refresh, unless the autorefresh state is PAUSED. The default mode is INCREMENTAL.

FULL AUTOREFRESH is more efficient when most of the Oracle table rows have been changed. INCREMENTAL AUTOREFRESH is more efficient when there are fewer changes.

TimesTen schedules an autorefresh operation when the transaction that contains a statement with AUTOREFRESH specified is committed. The statement types that cause autorefresh to be scheduled are:

• A CREATE CACHE GROUP statement in which AUTOREFRESH is specified, and the AUTOREFRESH state is specified as ON.

• An ALTER CACHE GROUP statement in which the AUTOREFRESH state has been changed to ON.

• A LOAD CACHE GROUP statement on an empty cache group whose autorefresh state is PAUSED.

The specified interval determines how often autorefresh occurs.

The current STATE of AUTOREFRESH can be ON, OFF or PAUSED. By default, the autorefresh state is PAUSED.

The NOT PROPAGATE attribute cannot be used with the AUTOREFRESH attribute.

Aging in cache groups

• You can implement sliding windows with time-based aging. See "Configuring a sliding window" in Oracle In-Memory Database Cache User's Guide.

• 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.

• LRU and time-based aging can be combined in one system. If you use only LRU aging, the aging thread wakes up based on the cycle specified for the whole database. If you use only time-based aging, the aging thread wakes up based on an optimal frequency. This frequency is determined by the values specified in the CYCLE clause for all tables. If you use both LRU and time-based aging, then the thread wakes up based on a combined consideration of both types.

• Call the ttAgingScheduleNow procedure to schedule the aging process right away regardless if the aging state is ON or OFF.

• 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.

• For LRU aging, if a child row is not a candidate for aging, then 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.

• Specify either the LRU aging or time-based aging policy on the root table. The policy applies to all tables in the cache group.

• 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.

• Restrictions on defining aging for a cache group:

  • LRU aging is not supported on a cache group defined with the autorefresh attribute, unless it is a dynamic cache group.

  • Aging is disabled by default on an explicitly loaded global cache group.

  • The aging policy cannot be added, altered, or dropped for read-only cache groups or cache groups with the AUTOREFRESH attribute while the cache agent is active. Stop the cache agent first.

  • You cannot drop the column that is used for time-based aging.

Cache grid

To cache data in a cache grid, you must create an asynchronous writethrough global cache group. Before you can create this cache group, the TimesTen database must be associated with a cache grid. For more information on creating and using a cache grid and creating and using global cache groups, see "Configuring a cache grid" and "Global cache groups" in Oracle In-Memory Database Cache User's Guide.

Examples

Create a read-only cache group:

CREATE READONLY CACHE GROUP customerorders
AUTOREFRESH INTERVAL 10 MINUTES
FROM
customer (custid INT NOT NULL,
       name CHAR(100) NOT NULL,
       addr CHAR(100),
       zip INT,
       region CHAR(10),
       PRIMARY KEY(custid)),
ordertab (orderid INT NOT NULL,
       custid INT NOT NULL,
       PRIMARY KEY (orderid),
       FOREIGN KEY (custid) REFERENCES customer(custid));

Create an asynchronous writethrough cache group:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP cstomers
FROM
customer (custid INT NOT NULL,
       name CHAR(100) NOT NULL,
       addr CHAR(100),
       zip INT,
       PRIMARY KEY(custid));

Create a synchronous writethrough cache group:

CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP customers
FROM
customer (custid INT NOT NULL,
       name CHAR(100) NOT NULL,
       addr CHAR(100),
       zip INT,
       PRIMARY KEY(custid));

Create a user managed cache group:

CREATE USERMANAGED CACHE GROUP updateanywherecustomers
AUTOREFRESH
       MODE INCREMENTAL
       INTERVAL 30 SECONDS
       STATE ON
FROM
customer (custid INT NOT NULL,
       name CHAR(100) NOT NULL,
       addr CHAR(100),
       zip INT,
       PRIMARY KEY(custid),
       PROPAGATE);

Create a cache group with time-based aging. Specify agetimestamp as the column for aging. Specify LIFETIME 2 hours, CYCLE 30 minutes. Aging state is not specified, so the default setting (ON) is used.

CREATE READONLY CACHE GROUP agingcachegroup
AUTOREFRESH
       MODE INCREMENTAL
       INTERVAL 5 MINUTES
       STATE PAUSED
FROM
customer (customerid NUMBER NOT NULL,
       agetimestamp TIMESTAMP NOT NULL,
       PRIMARY KEY (customerid))
       AGING USE agetimestamp LIFETIME 2 HOURS CYCLE 30 MINUTES;

Command> DESCRIBE customer;
Table USER.CUSTOMER:
  Columns:
   *CUSTOMERID                      NUMBER NOT NULL
    AGETIMESTAMP                    TIMESTAMP (6) NOT NULL
  AGING USE AgeTimestamp LIFETIME 2 HOURS CYCLE 30 MINUTES ON
1 table found.
(primary key columns are indicated with *)

Use a synonym for a mixed case delimited identifier table name in the Oracle database so the mixed case table name can be cached in TimesTen. First attempt to cache the mixed case Oracle table name. You see the error "Could not find 'NameofTable' in Oracle":

Command> AUTOCOMMIT 0;
Command> PASSTHROUGH 3;
Command> CREATE TABLE "MixedCase" (col1 NUMBER PRIMARY KEY NOT NULL);
Command> INSERT INTO "MixedCase" VALUES (1);
1 row inserted.
Command> COMMIT;
Command> CREATE CACHE GROUP MixedCase1 from "MixedCase" 
        (col1 NUMBER PRIMARY KEY NOT NULL);
 5140: Could not find SAMPLEUSER.MIXEDCASE in Oracle.  May not have privileges.
The command failed.

Now, using the PassThrough attribute, create the synonym "MIXEDCASE" in the Oracle database and use that synonym as the table name.

Command> AUTOCOMMIT 0;
Command> PASSTHROUGH 3;
Command> CREATE SYNONYM "MIXEDCASE" FOR "MixedCase";
Command> COMMIT;
Command> CREATE CACHE GROUP MixedCase2 FROM "MIXEDCASE" 
         (col1 NUMBER PRIMARY KEY NOT NULL);
Warning  5147: Cache group contains synonyms
Command> COMMIT;

Attempt to use a synonym name with a read-only cache group or a cache group with the AUTOREFRESH attribute. You see an error:

Command> AUTOCOMMIT 0;
Command> PASSTHROUGH 3;
Command> CREATE SYNONYM "MIXEDCASE_AUTO" FOR "MixedCase";
Command> COMMIT;
Command> CREATE READONLY CACHE GROUP MixedCase3 AUTOREFRESH MODE
         INCREMENTAL INTERVAL 10 MINUTES FROM "MIXEDCASE_AUTO" 
         (Col1 NUMBER PRIMARY KEY NOT NULL);
 5142: Autorefresh is not allowed on cache groups with Oracle synonyms
The command failed.

See also

CREATE FUNCTION

The CREATE FUNCTION statement creates a standalone stored function.

Required privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner).

SQL syntax

CREATE [OR REPLACE] FUNCTION [Owner.]FunctionName 
     [(arguments [IN|OUT|IN OUT][NOCOPY] DataType [DEFAULT expr][,...])]
     RETURN DataType [InvokerRightsClause] [DETERMINISTIC]
     {IS|AS} PlsqlFunctionBody

The syntax for the InvokerRightsClause:

AUTHID {CURRENT_USER|DEFINER}

You can specify InvokerRightsClause or DETERMINISTIC in any order.

Parameters

Restrictions

TimesTen does not support:

• parallel_enable_clause. You can specify the clause, but it has no effect.

• call_spec clause

• AS EXTERNAL

In a replication environment, the CREATE FUNCTION statement is not replicated. For more information, see "Creating a new PL/SQL object in an existing active standby pair" and "Adding a PL/SQL object to an existing replication scheme" in the Oracle TimesTen In-Memory Database Replication Guide.

When you create or replace a function, the privileges granted on the function remain the same. If you drop and re-create the object, the object privileges that were granted on the original object are revoked.

Examples

Create function get_sal with one input parameter. Return salary as type NUMBER.

Command> CREATE OR REPLACE FUNCTION get_sal
       >   (p_id employees.employee_id%TYPE) RETURN NUMBER IS
       >   v_sal employees.salary%TYPE := 0;
       > BEGIN
       >   SELECT salary INTO v_sal FROM employees
       >     WHERE employee_id = p_id;
       >   RETURN v_sal;
       > END get_sal;
       > /
 
Function created.

See also

Oracle Database PL/SQL Language Reference and Oracle Database SQL Language Reference

CREATE INDEX

The CREATE INDEX statement creates either a range or bitmap index on one or more columns of a table or materialized view.

Required privilege

No privilege is required for table or materialized view owner.

INDEX for another user's table or materialized view.

SQL syntax

CREATE [UNIQUE|BITMAP] INDEX [Owner.]IndexName ON
[Owner.]TableName ({ColumnName [ASC | DESC]}
[,... ] )

Parameters

Description

• TimesTen creates a non-unique range index by default. Specify UNIQUE to create a unique range index. Specify BITMAP to create a bitmap index.

• Specify a bitmap index on each column to increase the performance of complex queries that specify multiple predicates on multiple columns connected by the AND or OR operator. At runtime, TimesTen finds bitmaps of rows that satisfy each predicate and bitmaps from different predicates are combined using bitwise logical operation and then the resultant bitmaps are converted to qualified rows.

• Bitmap indexes are used to satisfy these predicates:

  • Equality predicates. For example: 'x1 = 1'

  • Range predicates. For example: 'y1 > 10' and'z1 BETWEEN 1 and 10'

  • AND predicates. For example: 'x1 > 10 AND y1 > 10'

  • OR predicates. For example: 'x1 > 10 OR y1 > 10'

  • Complex predicates with AND or OR. For example: '(x1 > 10 AND y1 > 10) OR (z1 > 10)'

  • NOT EQUAL predicate with AND. For example: 'x1 = 1 and y1 != 1'

• Bitmap indexes:

  • COUNT (*) optimization counts rowids from bitmaps.

  • Are used to optimize queries that group by a prefix of columns of the bitmap index.

  • Are used to optimize distinct queries and order by queries.

  • Are used in a MERGE join.

• The CREATE INDEX statement enters the definition of the index in the system catalog and initializes the necessary data structures. Any rows in the table are then added to the index. In TimesTen, performance is the same regardless of whether the table is created, indexed and populated or created, then populated and indexed.

• If UNIQUE is specified, all existing rows must have unique values in the indexed column(s).

• The new index is maintained automatically until the index is deleted by a DROP INDEX statement or until the table associated with it is dropped.

• Any prepared statements that reference the table with the new index are automatically prepared again the next time they are executed. Then the statements can take advantage, if possible, of the new index.

• NULL compares higher than all other values for sorting.

• An index on a temporary table cannot be created by a connection if any other connection has a non-empty instance of the table.

• If you are using linguistic comparisons, you can create a linguistic index. A linguistic index uses sort key values and storage is required for these values. Only one unique value for NLS_SORT is allowed for an index. For more information on linguistic indexes and linguistic comparisons, see "Using linguistic indexes" in Oracle TimesTen In-Memory Database Operations Guide.

• If you create indexes that are redundant, TimesTen generates warnings or errors. Call ttRedundantIndexCheck to see the list of redundant indexes for your tables.

• In a replicated environment for an active standby pair, if DDL_REPLICATION_LEVEL=2 when you execute the CREATE INDEX on the active database, the index will be replicated to all databases in the replication scheme. The table on which the index is created must be empty. See "Making DDL changes in an active standby pair" in the Oracle TimesTen In-Memory Database Replication Guide for more information.

• Indexes can be created on over any columns in the table. This includes compressed columns, even columns that exist in separate compression column groups.

Examples

Create a table and then create a bitmap index on the table. Use the ttIsql SHOWPLAN command to verify that the bitmap index is used in the query:

Command> CREATE TABLE tab1 (id NUMBER);
Command> INSERT INTO tab1 VALUES (10);
1 row inserted.
Command> INSERT INTO tab1 VALUES (20);
1 row inserted.
Command> CREATE BITMAP INDEX bitmap_id ON tab1 (id);
Command> COMMIT;
Command> SET AUTOCOMMIT OFF;
Command> SHOWPLAN 1;
Command> SELECT * FROM tab1 WHERE id = 10;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkBitmapScan
  TBLNAME:             TAB1
  IXNAME:              BITMAP_ID
  INDEXED CONDITION:   TAB1.ID = 10
  NOT INDEXED:         <NULL>
 
< 10 >
1 row found.

The regions table in the HR schema creates a unique index on region_id. Issue the ttIsql INDEXES command on table regions. You see the unique range index regions.

Command> INDEXES REGIONS;

Indexes on table SAMPLEUSER.REGIONS:
  REGIONS: unique range index on columns:
    REGION_ID
    (referenced by foreign key index COUNTR_REG_FK on table SAMPLEUSER.COUNTRIES)
  1 index found.

1 index found on 1 table.

Attempt to create a unique index i on table regions indexing on column region_id. You see a warning message:

Command> CREATE UNIQUE INDEX i ON regions (region_id);
Warning  2232: New index I is identical to existing index REGIONS; 
consider dropping index I

Call ttRedundantIndexCheck to see warning message for this index:

Command> CALL ttRedundantIndexCheck ('regions');
< Index SAMPLEUSER.REGIONS.I is identical to index SAMPLEUSER.REGIONS.REGIONS;
consider dropping index SAMPLEUSER.REGIONS.I >
1 row found.

Create table redundancy and define columns co11 and col2. Create two user indexes on col1 and col2. You see an error message when you attempt to create the second index r2. Index r1 is created. Index r2 is not created.

Command> CREATE TABLE redundancy (col1 CHAR (30), col2 VARCHAR2 (30));
Command> CREATE INDEX r1 ON redundancy (col1, col2);
Command> CREATE INDEX r2 ON redundancy (col1, col2);
 2231: New index R2 would be identical to existing index R1
The command failed.

Issue the ttIsql command INDEXES on table redundancy to show that only index r1 is created:

Command> INDEXES redundancy;

Indexes on table SAMPLEUSER.REDUNDANCY:
  R1: non-unique range index on columns:
    COL1
    COL2
  1 index found.

1 index found on 1 table.

This unique index ensures that all part numbers are unique.

CREATE UNIQUE INDEX purchasing.partnumindex
ON purchasing.parts (partnumber);

Create a linguistic index named german_index on table employees1. If you want to have more than one linguistic sort, create a second linguistic index.

Command> CREATE TABLE employees1 (id CHARACTER (21),
id2 character (21));
Command> CREATE INDEX german_index ON employees1
  (NLSSORT(id, 'NLS_SORT=GERMAN'));
Command> CREATE INDEX german_index2 ON employees1
  NLSSORT(id2, 'nls_sort=german_ci'));
Command> indexes employees1;
Indexes on table SAMPLEUSER.EMPLOYEES1:
  GERMAN_INDEX: non-unique range index on columns:
    NLSSORT(ID,'NLS_SORT=GERMAN')
  GERMAN_INDEX2: non-unique range index on columns:
    NLSSORT(ID2,'nls_sort=german_ci')
  2 indexes found.
1 table found.

See also

DROP INDEX

CREATE MATERIALIZED VIEW

The CREATE MATERIALIZED VIEW statement creates a view of the table specified in the SelectQuery clause. The original tables used to create a view are referred to as detail tables. The view can be refreshed synchronously or asynchronously with regard to changes in the detail tables. If you create an asynchronous materialized view, you must first create a materialized view log on the detail table. See "CREATE MATERIALIZED VIEW LOG".

Required privilege

• User executing the statement must have CREATE MATERIALIZED VIEW (if owner) or CREATE ANY MATERIALIZED VIEW (if not owner).

• Owner of the materialized view must have SELECT on the detail tables.

• Owner of the materialized view must have CREATE TABLE.

SQL syntax

CREATE MATERIALIZED VIEW [Owner.]ViewName
      [REFRESH 
        { FAST | COMPLETE } |
              [NEXT SYSDATE[+NUMTODSINTERVAL(IntegerLiteral,IntervalUnit)]] 
        |  NEXT SYSDATE[+NUMTODSINTERVAL(IntegerLiteral,IntervalUnit) ]
      ]
      AS SelectQuery
      [PRIMARY KEY (ColumnName [,...])] 
      [UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]

Parameters

[+NUMTODSINTERVAL(IntegerLiteral,
IntervalUnit)]

Description

Restrictions for all materialized views are as follows:

• You cannot create a materialized view on a detail table that includes a LOB column.

For synchronous materialized views or asynchronous materialized views that use complete refresh, the following is true for the SELECT statement:

• * Outer joins are allowed, but the SELECT list must project at least one non-nullable column from each of the inner tables specified in the OUTER JOIN.

• OUTER JOIN syntax for a SELECT in a materialized view definition is identical to that in a top-level SELECT.

• The restrictions noted in the description of SELECT statements apply. The (+) symbol must be used to specify outer joins of a materialized view.

Restrictions on synchronous materialized view and detail tables:

• A materialized view is read-only and cannot be updated directly. A materialized view is updated only when changes are made to the associated detail tables. Therefore a materialized view cannot be the target of a DELETE, UPDATE or INSERT statement.

• Materialized views defined on replicated tables may result in replication failures or inconsistencies if the materialized view is specified so that overflow or underflow conditions occur when the materialized view is updated.

• Detail tables can be replicated, but materialized views themselves cannot be replicated. If detail tables are replicated, TimesTen automatically updates the corresponding views.

• A materialized view and its detail tables cannot be part of a cache group.

• Referential constraints cannot be defined on materialized views.

• If REFRESH is specified, at least one of the refresh options of refresh method (FAST or COMPLETE) or the refresh interval (NEXT SYSDATE) must be specified. If you omit REFRESH, the materialized view is updated synchronously with updates from the detail tables.

• If you create an asynchronous materialized view with REFRESH FAST, it is recommended that you update the statistics on the materialized view log, materialized view and the base table on which the materialized view is created to increase the performance for the base table and updates on the materialized view.

• By default, a range index is created to enforce the primary key for a materialized view. Use the UNIQUE HASH clause to specify a hash index for the primary key.

  • If your application performs range queries over a materialized view's primary key, then choose a range index for that view by omitting the UNIQUE HASH clause.

  • If your application performs only exact match lookups on the primary key, then a hash index may offer better response time and throughput. In such a case, specify the UNIQUE HASH clause. See "CREATE TABLE" for more information about the UNIQUE HASH clause.

• Use ALTER TABLE to change the representation of the primary key index or resize a hash index.

• You cannot add or drop columns in the materialized view with the ALTER TABLE statement. To change the structure of the materialized view, drop and re-create the view.

• You can create indexes on the materialized view with the CREATE INDEX SQL statement.

• Use the DROP [MATERIALIZED] VIEW statement to drop a materialized view.

There are several restrictions on the query that is used to define the materialized view:

• A SELECT * query in a materialized view definition is expanded when the view is created. Columns added to the detail table after a materialized view is created do not affect the materialized view.

• Temporary tables cannot be used in a materialized view definition. Nonmaterialized views and derived tables cannot be used to define a materialized view.

• All columns in the GROUP BY list must be included in the select list.

• Aggregate view must include a COUNT(*) in the select list.

• SUM and COUNT are allowed, but not expressions involving them, including AVG.

• The following cannot be used in a SELECT statement that is creating a materialized view:

  • DISTINCT

  • FIRST

  • HAVING

  • ORDER BY

  • UNION

  • UNION ALL

  • MINUS

  • INTERSECT

  • JOIN

  • User functions: USER, CURRENT_USER, SESSION_USER

  • Subqueries

  • NEXTVAL and CURRVAL

  • Derived tables and joined tables

• Each expression in the select list must have a unique name. The name of a simple column expression is that column's name unless a column alias is defined. ROWID is considered an expression and needs an alias.

• No SELECT FOR UPDATE or SELECT FOR INSERT statements can be used on a view.

• Each inner table can only be outer joined with at most one table.

• Self joins are allowed. A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition.

There are no additional restrictions on asynchronous materialized views with full (COMPLETE) refresh.

In addition to the restrictions in a SELECT statement that is creating a materialized view, the following restrictions apply when creating asynchronous materialized views with incremental (FAST) refresh:

• Aggregate functions are not supported

• Outer joins are not supported.

• The SELECT list must include the ROWID or the primary key columns for all the detail tables.

• The materialized view log must be created for each detail table in the asynchronous material view with incremental refresh before creating the asynchronous materialized view.

• The materialized view log must include all the columns used in the asynchronous materialized views.

• TimesTen creates a unique index for a asynchronous materialized views that are refreshed incrementally. The index is created on the primary key or ROWID of the detail tables included in the SELECT list.

Invalid materialized views

The owner of a materialized view must have the SELECT privilege on its detail tables. The SELECT privilege is implied by the SELECT ANY TABLE and ADMIN system privileges. When the SELECT privilege or a higher-level system privilege on the detail tables is revoked from the owner of the materialized view, the materialized view becomes invalid.

You can select from an invalid asynchronous materialized view. Refreshing an invalid asynchronous materialized view fails with an error.

Selecting from an invalid synchronous materialized view fails with an error. Updates to the detail tables of an invalid synchronous materialized view do not update the materialized view.

You can identify invalid materialized views by using the ttIsql describe command and by inspecting the STATUS column of the SYS.DBA_OBJECTS, SYS.ALL_OBJECTS or SYS.USER_OBJECTS system tables. See Oracle TimesTen In-Memory Database System Tables and Views Reference .

If the revoked privilege is restored, you can make an invalid materialized view valid again by:

• Dropping and then re-creating the materialized view

• Refreshing an invalid asynchronous materialized view if it was originally specified with complete refreshes

For more information, see "Object privileges for materialized views" in Oracle TimesTen In-Memory Database Operations Guide.

Examples

Create a materialized view of columns from the customer and bookorder tables.

CREATE MATERIALIZED VIEW custorder AS
  SELECT custno, custname, ordno, book
    FROM customer, bookorder
    WHERE customer.custno=bookorder.custno;

Create a materialized view of columns x1 and y1 from the t1 table.

CREATE MATERIALIZED VIEW v1 AS SELECT x1, y1 FROM t1
  PRIMARY KEY (x1) UNIQUE HASH (x1) PAGES=100;

Create a materialized view from an outer join of columns x1 and y1 from the t1 and t2 tables.

CREATE MATERIALIZED VIEW v2 AS SELECT x1, y1 FROM t1, t2
  WHERE x1=x2(+);

Create an asynchronous materialized view called empmatview with incremental refresh. The materialized view will be refreshed immediately after updates to employees have been committed. The columns in empmatview are employee_id and email. You must create a materialized view log before you create an asynchronous materialized view.

CREATE MATERALIZED VIEW empmatview 
  REFRESH FAST NEXT SYSDATE 
  AS SELECT employee_id, email FROM employees;
107 rows materialized.

Create an asynchronous materialized view called empmatview1 with complete refresh. A full refresh of the materialized view occurs every 10 days. The columns in empmatview are employee_id and email. You must create a materialized view log before you create an asynchronous materialized view.

CREATE MATERIALIZED VIEW empmatview1
    REFRESH COMPLETE NEXT SYSDATE+NUMTODSINTERVAL(10,'day')
    AS SELECT employee_id, email FROM employees;
107 rows materialized.

The following example creates a materialized view empmatview2 based on selected columns employee_id and email from table employees. After the materialized view is created, create an index on the materialized view column mvemp_id of the materialized view empmatview2.

CREATE MATERIALIZED VIEW empmatview2
   AS SELECT employee_id mvemp_id, email mvemail 
         FROM employees;
107 rows materialized. 

CREATE INDEX empmvindex ON empmatview2 (mvemp_id);

See also

CREATE MATERIALIZED VIEW LOG

The CREATE MATERIALIZED VIEW LOG statement creates a log in which changes to the detail table are recorded. The log is required for an asynchronous materialized view that is refreshed incrementally. The log must be created before the materialized view is created. The log is a table in the user's schema called MVLOG$_detailTableID, where detailTableID is a system-generated ID.

This statement also creates other objects for internal use:

• A global temporary table called MVLGT$_detailTableID

• A sequence called MVSEQ$_detailTableID

The objects are dropped when the DROP MATERIALIZED VIEW LOG statement is executed.

Required privileges

SELECT on the detail table and

CREATE TABLE or CREATE ANY TABLE (if not owner).

SQL syntax

CREATE MATERIALIZED VIEW LOG ON [Owner.]TableName
  [ WITH 
       { PRIMARY KEY[, ROWID] | 
         ROWID[, PRIMARY KEY } [(columnName[,...])] 
       | (columnName[,...])
  ]

Parameters

Description

• Use the WITH clause to indicate the keys and columns for which changes will be recorded in the materialized view log. If you specify the WITH clause, the following applies:

  • Specify either the PRIMARY KEY or ROWID when using the WITH clause. However, if the WITH clause is specified without either option, it defaults implicitly to use PRIMARY KEY. In addition, the materialized view log defaults to use PRIMARY KEY if the WITH clause is omitted altogether.

  • Specify PRIMARY KEY to record changes in the primary key columns.

  • Specify the ROWID option to record the rowid of all changed rows. The ROWID option is useful when the table does not have a primary key or when you do not want to use the primary key when you create the materialized view.

  • You can specify both PRIMARY KEY and ROWID. The materialized view log may be used by more than one asynchronous materialized view using the specified table as the detail table. However, you can only specify one PRIMARY KEY clause, one ROWID clause and one column list for a materialized view log.

• Only one materialized view log is created for a table, even if the table is the detail table for more than one materialized view with FAST refreshes. Make sure to include all the columns that are used in different asynchronous materialized views with FAST refresh.

• A materialized view log cannot be created using a materialized view as the table or for tables in cache groups.

• A materialized view log cannot be altered to add or drop columns.

Examples

Create a materialized view log on the employees table. Include employee_id (the primary key) and email in the log.

CREATE MATERIALIZED VIEW LOG ON employees WITH PRIMARY KEY (email);

You can create the same materialized view log on the employees table without specifying PRIMARY KEY, which is the default and so is implied, as follows:

CREATE MATERIALIZED  VIEW LOG ON employees WITH (email);

To create a materialized view log on the employees table with only the primary key, execute the following:

CREATE MATERIALIZED VIEW LOG ON employees;

Create a materialized view log on the employees table. Include employee_id (the primary key) and row id in the log.

Command> CREATE MATERIALIZED VIEW LOG ON employees WITH primary key, rowid;

Create a materialized view log on the employees table. Include row id in the log.

Command> CREATE MATERIALIZED VIEW LOG ON employees WITH rowid;

Create a materialized view log on the employees table. Include row id, primary key (employee_id) and email in the log.

Command> CREATE MATERIALIZED VIEW LOG ON employees WITH rowid, primary key (email);

Create a materialized view log on the employees table. Include primary key, by default), and two other columns of email and last_name in the log.

Command> CREATE MATERIALIZED VIEW LOG ON employees WITH (email, last_name);

See also

CREATE PACKAGE

The CREATE PACKAGE statement creates the specification for a standalone package, which is an encapsulated collection of related procedures, functions, and other program objects stored together in your database. The package specification declares these objects. The package body defines these objects.

Required privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner).

SQL syntax

CREATE [OR REPLACE] PACKAGE [Owner.]PackageName 
      [InvokerRightsClause] {IS|AS}
      PlsqlPackageSpec

The syntax for the InvokerRightsClause:

AUTHID {CURRENT_USER | DEFINER}

Parameters

Description

In a replicated environment, the CREATE PACKAGE statement is not replicated. For more information, see "Creating a new PL/SQL object in an existing active standby pair" and "Adding a PL/SQL object to an existing replication scheme" in the Oracle TimesTen In-Memory Database Replication Guide.

When you create or replace a package, the privileges granted on the package remain the same. If you drop and re-create the object, the object privileges that were granted on the original object are revoked.

See also

Oracle Database PL/SQL Language Reference and Oracle Database SQL Language Reference

CREATE PACKAGE BODY

The CREATE PACKAGE BODY statement creates the body of a standalone package. A package is an encapsulated collection of related procedures, functions, and other program objects stored together in your database. A package specification declares these objects. A package body defines these objects.

Required privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner).

SQL syntax

CREATE [OR REPLACE] PACKAGE BODY [Owner.]PackageBody 
      {IS|AS} plsql_package_body

Parameters

Description

In a replicated environment, the CREATE PACKAGE BODY statement is not replicated. For more information, see "Creating a new PL/SQL object in an existing active standby pair" and "Adding a PL/SQL object to an existing replication scheme" in the Oracle TimesTen In-Memory Database Replication Guide.

When you create or replace a package body, the privileges granted on the package body remain the same. If you drop and re-create the object, the object privileges that were granted on the original object are revoked.

See also

CREATE PROCEDURE

The CREATE PROCEDURE statement creates a standalone stored procedure.

Required privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner).

SQL syntax

CREATE [OR REPLACE] PROCEDURE [Owner.]ProcedureName 
     [(arguments [IN|OUT|IN OUT][NOCOPY] DataType [DEFAULT expr][,...])]
     [InvokerRightsClause] [DETERMINISTIC]
     {IS|AS} plsql_procedure_body

The syntax for the InvokerRightsClause:

AUTHID {CURRENT_USER|DEFINER}

You can specify InvokerRightsClause or DETERMINISTIC in any order.

Parameters

Restrictions

TimesTen does not support:

• call_spec clause

• AS EXTERNAL clause

In a replicated environment, the CREATE PROCEDURE statement is not replicated. For more information, see "Creating a new PL/SQL object in an existing active standby pair" and "Adding a PL/SQL object to an existing replication scheme" in the Oracle TimesTen In-Memory Database Replication Guide.

Description

• The namespace for PL/SQL procedures is distinct from the TimesTen built-in procedures. You can create a PL/SQL procedure with the same name as a TimesTen built-in procedure.

• When you create or replace a procedure, the privileges granted on the procedure remain the same. If you drop and re-create the object, the object privileges that were granted on the original object are revoked.

Examples

Create a procedure query_emp to retrieve information about an employee. Pass the employee_id 171 to the procedure and retrieve the last_name and salary into two OUT parameters.

Command> CREATE OR REPLACE PROCEDURE query_emp
       >           (p_id IN employees.employee_id%TYPE,
       >            p_name  OUT employees.last_name%TYPE,
       >            p_salary OUT employees.salary%TYPE) IS
       >         BEGIN
       >           SELECT last_name, salary INTO p_name, p_salary
       >           FROM employees
       >           WHERE employee_id = p_id;
       >         END query_emp;
       >         /
 
Procedure created.

See also

CREATE REPLICATION

TimesTen SQL configuration for replication provides a programmable way to configure replication. The configuration can be embedded in C, C++ or Java code. Replication can be configured locally or from remote systems using client/server.

In addition, you need to use the ttRepAdmin utility to maintain operations not covered by the supported SQL statements. Use ttRepAdmin to change replication state, duplicate databases, list the replication configuration and view replication status.

The CREATE REPLICATION statement:

• Defines a replication scheme at a participating database.

• Installs the specified configuration in the executing database's replication system tables.

• Typically consists of one or more replication element specifications and zero or more STORE specifications.

Required privilege

ADMIN

Definitions

A replication element is an entity that TimesTen synchronizes between databases. A replication element can be a whole table or a database. A database can include most types of tables and cache groups. It can include only specified tables and cache groups, or include all tables except specified tables and cache groups. It cannot include temporary tables or views, whether materialized or nonmaterialized.

A replication scheme is a set of replication elements, as well as the databases that maintain copies of these elements.

When replicating cache groups:

• When replicating cache groups between databases, both cache groups must be identical, with the exception of the settings for AUTOREFRESH and PROPAGATE.

• When replicating a cache group with AUTOREFRESH, the cache group on the subscriber must set the autorefresh STATE to OFF. In a bidirectional replication scheme, one of the cache groups must set the autorefresh STATE to OFF.

• If a master cache group specifies PROPAGATE, the subscriber cache group must set the autorefresh STATE to OFF.

For more detailed information on SQL configuration for replication, see Oracle TimesTen In-Memory Database Replication Guide.

SQL syntax

CREATE REPLICATION [Owner.]ReplicationSchemeName
{ ELEMENT ElementName
  { DATASTORE | { TABLE [Owner.]TableName [CheckConflicts]} | 
       SEQUENCE [Owner.]SequenceName}
     { MASTER | PROPAGATOR } FullStoreName
     [TRANSMIT { NONDURABLE | DURABLE }]
     { SUBSCRIBER FullStoreName [,...]
        [ReturnServiceAttribute] } [,...] }
     [...]
     [{INCLUDE | EXCLUDE}
          {TABLE [[Owner.]TableName[,...]] |
           CACHE GROUP [[Owner.]CacheGroupName[,...]] | 
           SEQUENCE [[Owner.]SequenceName[,...]} [,...]] 
[ STORE FullStoreName [StoreAttribute [... ]]] [...]
[ NetworkOperation[...]]

Syntax for CheckConflicts is described in "CHECK CONFLICTS".

Syntax for ReturnServiceAttribute:

{ RETURN RECEIPT [BY REQUEST] |
  RETURN TWOSAFE [BY REQUEST] |
  NO RETURN }

Syntax for StoreAttribute:

[ DISABLE RETURN {SUBSCRIBER | ALL} NumFailures ]
  [ RETURN SERVICES {ON | OFF} WHEN [REPLICATION] STOPPED ]
  [ DURABLE COMMIT {ON | OFF}]
  [ RESUME RETURN MilliSeconds ]
  [ LOCAL COMMIT ACTION {NO ACTION | COMMIT} ]
  [ RETURN WAIT TIME Seconds ]
  [ COMPRESS TRAFFIC {ON | OFF}
  [ PORT PortNumber ]
  [ TIMEOUT Seconds ]
  [ FAILTHRESHOLD Value ]
  [ CONFLICT REPORTING SUSPEND AT Value ]
  [ CONFLICT REPORTING RESUME AT Value ]
  [ TABLE DEFINITION CHECKING {RELAXED|EXACT}]

Syntax for NetworkOperation:

ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName
  { { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost }
      PRIORITY Priority } [...]

Parameters

CHECK CONFLICTS

Syntax

The syntax for CHECK CONFLICTS is:

{NO CHECK |
CHECK CONFLICTS BY ROW TIMESTAMP
      COLUMN ColumnName
      [ UPDATE BY { SYSTEM | USER } ]
      [ ON EXCEPTION { ROLLBACK [ WORK ] | NO ACTION } ]
      [ {REPORT TO 'FileName'
             [ FORMAT { XML | STANDARD } ] | NO REPORT
      } ]
}

Parameters

The CHECK CONFLICTS clause of the CREATE REPLICATION or ALTER REPLICATION statement has the following parameters:

Description

• The names of all databases on the same host must be unique for each replication scheme for each TimesTen instance.

• Replication elements can only be updated (by normal application transactions) through the MASTER database. PROPAGATOR and SUBSCRIBER databases are read-only.

• If you define a replication scheme that permits multiple databases to update the same table, see "Resolving Replication Conflicts" in Oracle TimesTen In-Memory Database Replication Guide for recommendations on how to avoid conflicts when updating rows.

• SELF is intended for replication schemes where all participating databases are local. Do not use SELF for a distributed replication scheme in a production environment, where spelling out the hostname for each database in a script enables it to be used at each participating database.

• Each attribute for a given STORE may be specified only once, or not at all.

• Specifying the PORT of a database for one replication scheme specifies it for all replication schemes. All other connection attributes are specific to the replication scheme specified in the command.

• For replication schemes, DataStoreName is always the prefix of the TimesTen database checkpoint file names. These are the files with the.ds0 and.ds1 suffixes that are saved on disk by checkpoint operations.

• If a row with a default NOT INLINE VARCHAR value is replicated, the receiver creates a copy of this value for each row instead of pointing to the default value if and only if the default value of the receiving node is different from the sending node.

• To use timestamp comparison on replicated tables, you must specify a nullable column of type BINARY(8) to hold the timestamp value. Define the timestamp column when you create the table. You cannot add the timestamp column with the ALTER TABLE statement. In addition, the timestamp column cannot be part of a primary key or index.

• If you specify the XML report format, two XML documents are generated:

  • FileName.xml: This file contains the DTD for the report and the root node for the report. It includes the document definition and the include directive.

  • FileName.include: This file is included in FileName.xml and contains all the actual conflicts.

  • The FileName.include file can be truncated. Do not truncate the FileName.xml file.

  • For a complete description of the XML format, including examples of each conflict, see "Reporting conflicts to an XML file" in Oracle TimesTen In-Memory Database Replication Guide.

• If you specify a report format for an element and then drop the element, the corresponding report files are not deleted.

• Use the CONFLICT REPORTING SUSPEND AT clause to specify a high water mark threshold at which the reporting of conflict resolution is suspended. When the number of conflicts per second exceeds the specified high water mark threshold, conflict resolution reporting (if configured and reported by the report file) and SNMP are suspended and an SNMP trap is emitted to indicate that it has been suspended.

• Use the CONFLICT REPORTING RESUME AT clause to specify a low water mark threshold where the reporting of conflict resolution is resumed. When the rate of conflict falls below the low water mark threshold, conflict resolution reporting is resumed. A SNMP trap is emitted to indicate the resumption of conflict resolution. This trap provides the number of unreported conflicts during the time when conflict resolution was suspended.

• The state of whether conflict reporting is suspended or not by a replication agent does not persist across the local replication agent and the peer agent stop and restart.

• Do not use the CREATE REPLICATION statement to replicate dynamic read-only cache groups asynchronously. Use the CREATE ACTIVE STANDBY PAIR statement.

Examples

Replicate the contents of repl.tab from masterds to two subscribers, subscriber1ds and subscriber2ds.

CREATE REPLICATION repl.twosubscribers
       ELEMENT e TABLE repl.tab
         MASTER masterds ON "server1"
         SUBSCRIBER subscriber1ds ON "server2",
                    subscriber2ds ON "server3";

Replicate the entire masterds database to the subscriber, subscriber1ds. The FAILTHRESHOLD specifies that a maximum of 10 log files can accumulate on masterds before it decides that subscriber1ds has failed.

CREATE REPLICATION repl.wholestore
  ELEMENT e DATASTORE
     MASTER masterds ON "server1"
     SUBSCRIBER subscriber1ds ON "server2"
  STORE masterds FAILTHRESHOLD 10;

Bidirectionally replicate the entire westds and eastds databases and enable the RETURN TWOSAFE service.

CREATE REPLICATION repl.biwholestore
  ELEMENT e1 DATASTORE
     MASTER westds ON "westcoast"
     SUBSCRIBER eastds ON "eastcoast"
        RETURN TWOSAFE
  ELEMENT e2 DATASTORE
     MASTER eastds ON "eastcoast"
     SUBSCRIBER westds ON "westcoast"
        RETURN TWOSAFE;

Enable the return receipt service for select transaction updates to the subscriber1ds subscriber.

CREATE REPLICATION repl.twosubscribers
  ELEMENT e TABLE repl.tab
     MASTER masterds ON "server1"
     SUBSCRIBER subscriber1ds ON "server2"
        RETURN RECEIPT BY REQUEST
     SUBSCRIBER subscriber2ds ON "server3";

Replicate the contents of the customerswest table from the west database to the ROUNDUP database and the customerseast table from the east database. Enable the return receipt service for all transactions.

CREATE REPLICATION r
       ELEMENT west TABLE customerswest
         MASTER west ON "serverwest"
         SUBSCRIBER roundup ON "serverroundup"
            RETURN RECEIPT
       ELEMENT east TABLE customerseast
         MASTER east ON "servereast"
         SUBSCRIBER roundup ON "serverroundup"
            RETURN RECEIPT;

Replicate the contents of the repl.tab table from the centralds database to the propds database, which propagates the changes to the backup1ds and backup2ds databases.

CREATE REPLICATION repl.propagator
       ELEMENT a TABLE repl.tab
         MASTER centralds ON "finance"
         SUBSCRIBER proprds ON "nethandler"
       ELEMENT b TABLE repl.tab
         PROPAGATOR proprds ON "nethandler"
         SUBSCRIBER backup1ds ON "backupsystem1"
                    bakcup2ds ON "backupsystem2";

Bidirectionally replicate the contents of the repl.accounts table between the eastds and westds databases. Each database is both a master and a subscriber for the repl.accounts table.

Because the repl.accounts table can be updated on either the eastds or westds database, it includes a timestamp column (tstamp). The CHECK CONFLICTS clause establishes automatic timestamp comparison to detect any update conflicts between the two databases. In the event of a comparison failure, the entire transaction that includes an update with the older timestamp is rolled back (discarded).

CREATE REPLICATION repl.r1
ELEMENT elem_accounts_1 TABLE repl.accounts
   CHECK CONFLICTS BY ROW TIMESTAMP
      COLUMN tstamp
      UPDATE BY SYSTEM
      ON EXCEPTION ROLLBACK
   MASTER westds ON "westcoast"
   SUBSCRIBER eastds ON "eastcoast"
ELEMENT elem_accounts_2 TABLE repl.accounts
   CHECK CONFLICTS BY ROW TIMESTAMP
      COLUMN tstamp
      UPDATE BY SYSTEM
      ON EXCEPTION ROLLBACK
   MASTER eastds ON "eastcoast"
   SUBSCRIBER westds ON "westcoast";

Replicate the contents of the repl.accounts table from the activeds database to the backupds database, using the return twosafe service, and using TCP/IP port 40000 on activeds and TCP/IP port 40001 on backupds. The transactions on activeds need to be committed whenever possible, so configure replication so that the transaction is committed even after a replication timeout using LOCAL COMMIT ACTION, and so that the return twosafe service is disabled when replication is stopped. To avoid significant delays in the application if the connection to the backupds database is interrupted, configure the return service to be disabled after five transactions have timed out, but also configure the return service to be re-enabled when the backupds database's replication agent responds in under 100 milliseconds. Finally, the bandwidth between databases is limited, so configure replication to compress the data when it is replicated from the activeds database.

CREATE REPLICATION repl.r
ELEMENT elem_accounts_1 TABLE repl.accounts
   MASTER activeds ON "active"
   SUBSCRIBER backupds ON "backup"
      RETURN TWOSAFE
ELEMENT elem_accounts_2 TABLE repl.accounts
   MASTER activeds ON "active"
   SUBSCRIBER backupds ON "backup"
      RETURN TWOSAFE
STORE activeds ON "active"
   PORT 40000
   LOCAL COMMIT ACTION COMMIT
   RETURN SERVICES OFF WHEN REPLICATION STOPPED
   DISABLE RETURN SUBSCRIBER 5
   RESUME RETURN 100
   COMPRESS TRAFFIC ON
STORE backupds ON "backup"
   PORT 40001;

Illustrate conflict reporting suspend and conflict reporting resume clauses for table level replication. Use these clauses for table level replication not database replication. Issue repschemes command to show that replication scheme is created.

Command> CREATE TABLE repl.accounts (tstamp BINARY (8) NOT NULL 
PRIMARY KEY, tstamp1 BINARY (8));
Command> CREATE REPLICATION repl.r2
> ELEMENT elem_accounts_1 TABLE repl.accounts
> CHECK CONFLICTS BY ROW TIMESTAMP
> COLUMN tstamp1
> UPDATE BY SYSTEM
> ON EXCEPTION ROLLBACK WORK
> MASTER westds ON "west1"
> SUBSCRIBER eastds ON "east1"
> ELEMENT elem_accounts_2 TABLE repl.accounts
> CHECK CONFLICTS BY ROW TIMESTAMP 
> COLUMN tstamp1
> UPDATE BY SYSTEM 
> ON EXCEPTION ROLLBACK WORK 
> MASTER eastds ON "east1"
> SUBSCRIBER westds ON "west1"
> STORE westds
> CONFLICT REPORTING SUSPEND AT 20
> CONFLICT REPORTING RESUME AT 10;
Command> REPSCHEMES;

Replication Scheme REPL.R2:

  Element: ELEM_ACCOUNTS_1
  Type: Table REPL.ACCOUNTS
  Conflict Check Column: TSTAMP1
  Conflict Exception Action: Rollback Work
  Conflict Timestamp Update: System
  Conflict Report File: (none)
  Master Store: WESTDS on WEST1 Transmit Durable
  Subscriber Store: EASTDS on EAST1

  Element: ELEM_ACCOUNTS_2
  Type: Table REPL.ACCOUNTS
  Conflict Check Column: TSTAMP1
  Conflict Exception Action: Rollback Work
  Conflict Timestamp Update: System
  Conflict Report File: (none)
  Master Store: EASTDS on EAST1 Transmit Durable
  Subscriber Store: WESTDS on WEST1

  Store: EASTDS on EAST1
    Port: (auto)
    Log Fail Threshold: (none)
    Retry Timeout: 120 seconds
    Compress Traffic: Disabled

  Store: WESTDS on WEST1
    Port: (auto)
    Log Fail Threshold: (none)
    Retry Timeout: 120 seconds
    Compress Traffic: Disabled
    Conflict Reporting Suspend: 20
    Conflict Reporting Resume: 10

1 replication scheme found.

Example of NetworkOperation clause with 2 MASTERIP and SUBSCRIBERIP clauses:

CREATE REPLICATION r ELEMENT e DATASTORE
MASTER rep1 SUBSCRIBER rep2 RETURN RECEIPT
MASTERIP "1.1.1.1" PRIORITY 1 SUBSCRIBERIP "2.2.2.2"
    PRIORITY 1
MASTERIP "3.3.3.3" PRIORITY 2 SUBSCRIBERIP "4.4.4.4"
    PRIORITY 2;

Example of NetworkOperation clause. Use the default sending interface but a specific receiving network:

CREATE REPLICATION r
ELEMENT e DATASTORE
MASTER rep1 SUBSCRIBER rep2
ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2"
SUBSCRIBERIP "rep2nic2" PRIORITY 1;

Example of using the NetworkOperation clause with multiple subscribers:

CREATE REPLICATION r ELEMENT e DATASTORE
MASTER rep1 SUBSCRIBER rep2,rep3
ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2"
MASTERIP "1.1.1.1" PRIORITY 1 SUBSCRIBERIP "2.2.2.2"
    PRIORITY 1
ROUTE MASTER Rep1 ON "machine1" SUBSCRIBER Rep3 ON "machine2"
MASTERIP "3.3.3.3" PRIORITY 2 SUBSCRIBERIP "4.4.4.4";

See also

CREATE SEQUENCE

The CREATE SEQUENCE statement creates a new sequence number generator that can subsequently be used by multiple users to generate unique integers. Use the CREATE SEQUENCE statement to define the initial value of the sequence, define the increment value, the maximum or minimum value and determine if the sequence continues to generate numbers after the minimum or maximum is reached.

Required privilege

CREATE SEQUENCE (if owner) or CREATE ANY SEQUENCE (if not owner).

SQL syntax

CREATE SEQUENCE [Owner.]SequenceName
       [INCREMENT BY IncrementValue]
       [MINVALUE MinimumValue]
       [MAXVALUE MaximumValue]
       [CYCLE]
       [CACHE CacheValue]
       [START WITH StartValue]

Parameters

Description

• All parameters in the CREATE SEQUENCE statement must be integer values.

• If you do not specify a value in the parameters, TimesTen defaults to an ascending sequence that starts with 1, increments by 1, has the default maximum value and does not cycle.

• There is no ALTER SEQUENCE statement in TimesTen. To alter a sequence, use the DROP SEQUENCE statement and then create a new sequence with the same name. For example, to change the MINVALUE, drop the sequence and re-create it with the same name and with the desired MINVALUE.

• Do not create a sequence with the same name as a view or materialized view.

• Sequences with the CYCLE attribute cannot be replicated.

Incrementing SEQUENCE values with CURRVAL and NEXTVAL

To refer to the SEQUENCE values in a SQL statement, use CURRVAL and NEXTVAL.

• CURRVAL returns the value of the last call to NEXTVAL if there is one in the current session, otherwise it returns an error.

• NEXTVAL increments the current sequence value by the specified increment and returns the value for each row accessed.

The current value of a sequence is a connection-specific value. If there are two concurrent connections to the same database, each connection has its own CURRVAL of the same sequence set to its last NEXTVAL reference. When the maximum value is reached, SEQUENCE either wraps or issues an error statement, depending on the value of the CYCLE option of the CREATE SEQUENCE. In the case of recovery, sequences are not rolled back. It is possible that the range of values of a sequence can have gaps; however, each sequence value is still unique.

If you execute a single SQL statement with multiple NEXTVAL references, TimesTen only increments the sequence once, returning the same value for all occurrences of NEXTVAL. If a SQL statement contains both NEXTVAL and CURRVAL, NEXTVAL is executed first. CURRVAL and NEXTVAL have the same value in that SQL statement.

NEXTVAL and CURRVAL can be used in:

• The SelectList of a SELECT statement, but not the SelectList of a subquery.

• The SelectList of an INSERT...SELECT statement.

• The SET clause of an UPDATE statement.

Examples

Create a sequence.

CREATE SEQUENCE mysequence INCREMENT BY 1 MINVALUE 2 
       MAXVALUE 1000;

This example assumes that tab1 has 1 row in the table and that CYCLE is used:

CREATE SEQUENCE s1 MINVALUE 2 MAXVALUE 4 CYCLE;
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 2; */
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 3; */
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 4; */

After the maximum value is reached, the cycle starts from the minimum value for an ascending sequence.

SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 2; */

To create a sequence and generate a sequence number:

CREATE SEQUENCE seq INCREMENT BY 1;
INSERT INTO student VALUES (seq.NEXTVAL, 'Sally');

To use a sequence in an UPDATE SET clause:

UPDATE student SET studentno = seq.NEXTVAL WHERE name = 'Sally';

To use a sequence in a query:

SELECT seq.CURRVAL FROM student;

See also

DROP SEQUENCE

CREATE SYNONYM

The CREATE SYNONYM statement creates a public or private synonym for a database object. A synonym is an alias for a database object. The object can be a table, view, synonym, sequence, PL/SQL stored procedure, PL/SQL function, PL/SQL package, materialized view or cache group.

A private synonym is owned by a specific user and exists in that user's schema. A private synonym is accessible to users other than the owner only if those users have appropriate privileges on the underlying object and specify the schema along with the synonym name.

A public synonym is accessible to all users as long as the user has appropriate privileges on the underlying object.

CREATE SYNONYM is a DDL statement.

Synonyms can be used in these SQL statements:

• DML statements: SELECT, DELETE, INSERT, UPDATE, MERGE

• Some DDL statements: GRANT, REVOKE, CREATE TABLE ... AS SELECT, CREATE VIEW ... AS SELECT, CREATE INDEX, DROP INDEX

• Some cache group statements: LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP, FLUSH CACHE GROUP

Required privilege

CREATE SYNONYM (if owner) or CREATE ANY SYNONYM (if not owner) to create a private synonym.

CREATE PUBLIC SYNONYM to create a public synonym.

SQL syntax

CREATE [OR REPLACE] [PUBLIC] SYNONYM [owner1.]synonym FOR [owner2.]object

Parameters

Description

• The schema object does not need to exist when its synonym is created.

• Do not create a public synonym with the same name as a TimesTen built-in procedure.

• In order to use the synonym, appropriate privileges must be granted to a user for the object aliased by the synonym before using the synonym.

• A private synonym cannot have the same name as tables, views, sequences, PLSQL packages, functions, procedures, and cache groups that are in the same schema as the private synonym.

• A public synonym may have the same name as a private synonym or an object name.

• If the PassThrough attribute is set so that a query needs to executed in the Oracle database, the query is sent to the Oracle database without any changes. If the query uses a synonym for a table in a cache group, then a synonym with the same name must be defined for the corresponding Oracle table for the query to be successful.

• When an object name is used in the DML and DDL statements in which a synonym can be used, the object name is resolved as follows:

  1. Search for a match within the current schema. If no match is found, then:

  2. Search for a match with a public synonym name. If no match is found, then:

  3. Search for a match in the SYS schema. If no match is found, then:

  4. The object does not exist.

  TimesTen creates a public synonym for some objects in the SYS schema. The name of the public synonym is the same as the object name. Thus steps 2 and 3 in the object name resolution can be switched without changing the results of the search.

• In a replicated environment for an active standby pair, if DDL_REPLICATION_LEVEL=2 when you execute the CREATE SYNONYM on the active database, the synonym will be replicated to all databases in the replication scheme. See "Making DDL changes in an active standby pair" in the Oracle TimesTen In-Memory Database Replication Guide for more information.

Examples

As user ttuser, create a synonym for the jobs table. Verify that you can retrieve the information using the synonym. Display the contents of the SYS.USER_SYNONYMS system view.

Command> CREATE SYNONYM synjobs FOR jobs;
Synonym created.

Command> SELECT FIRST 2 * FROM jobs;
< AC_ACCOUNT, Public Accountant, 4200, 9000 >
< AC_MGR, Accounting Manager, 8200, 16000 >
2 rows found.
Command> SELECT FIRST 2 * FROM synjobs;
< AC_ACCOUNT, Public Accountant, 4200, 9000 >
< AC_MGR, Accounting Manager, 8200, 16000 >
2 rows found.

Command> SELECT * FROM sys.user_synonyms;
< SYNJOBS, TTUSER, JOBS, <NULL> >
1 row found.

Create a public synonym for the employees table.

Command> CREATE PUBLIC SYNONYM pubemp FOR employees;
Synonym created.

Verify that pubemp is listed as a public synonym in the SYS.ALL_SYNONYMS system view.

Command> SELECT * FROM sys.all_synonyms;
< PUBLIC, TABLES, SYS, TABLES, <NULL> >
...
< TTUSER, SYNJOBS, TTUSER, JOBS, <NULL> >
< PUBLIC, PUBEMP, TTUSER, EMPLOYEES, <NULL> >
57 rows found.

Create a synonym for the tab table in the terry schema. Describe the synonym.

Command> CREATE SYNONYM syntab FOR terry.tab;
Synonym created.
Command> DESCRIBE syntab;
 
Synonym TTUSER.SYNTAB:
  For Table TERRY.TAB
  Columns:
    COL1                            VARCHAR2 (10) INLINE
    COL2                            VARCHAR2 (10) INLINE

1 Synonyms found.

Redefine the synjobs synonym to be an alias for the employees table by using the OR REPLACE clause. Describe synjobs.

Command> CREATE OR REPLACE synjobs FOR employees;
Synonym created.
 
Command> DESCRIBE synjobs;
 
Synonym TTUSER.SYNJOBS:
  For Table TTUSER.EMPLOYEES
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE UNIQUE 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)
 
1 Synonyms found.

See also

DROP SYNONYM

CREATE TABLE

The CREATE TABLE statement defines a table.

Required privilege

CREATE TABLE (if owner) or CREATE ANY TABLE (if not owner).

The owner of the created table must have the REFERENCES privilege on tables referenced by the REFERENCE clause.

ADMIN privilege if replicating a new table across an active standby pair when DDL_REPLICATION_LEVEL=2 and DDL_REPLICATION_ACTION=INCLUDE. These attributes cause the CREATE TABLE to implicitly execute an ALTER ACTIVE STANDBY PAIR... INCLUDE TABLE statement. See "ALTER SESSION" for more details.

SQL syntax

The syntax for a persistent table is:

CREATE TABLE [Owner.]TableName
(
    {{ColumnDefinition} [,...]
     [PRIMARY KEY (ColumnName [,...]) |
     [[CONSTRAINT ForeignKeyName]
        FOREIGN KEY ([ColumnName] [,...])
        REFERENCES RefTableName
            [(ColumnName [,...])] [ON DELETE CASCADE]] [...]
    }
)
[TableCompression]
[UNIQUE HASH ON (HashColumnName [,...])
    PAGES = PrimaryPages]
[AGING {LRU|
         USE ColumnName
             LIFETIME Num1 {SECOND[S] | MINUTE[S] | HOUR[S] |DAY[S]}
               [CYCLE Num2 {SECOND[S] | MINUTE[S] |HOUR[S] |DAY[S]}]
       }[ON|OFF]
]
[AS SelectQuery]

The syntax for a global temporary table is:

CREATE GLOBAL TEMPORARY TABLE [Owner.]TableName
(
    {{ColumnDefinition} [,...]
     [PRIMARY KEY (ColumnName [,...]) |
     [[CONSTRAINT ForeignKeyName]
        FOREIGN KEY ([ColumnName] [,...])
        REFERENCES RefTableName
             [(ColumnName [,...])] [ON DELETE CASCADE]] [...]
      }
)
[UNIQUE HASH ON (HashColumnName [,...])
    PAGES = PrimaryPages]
[ON COMMIT { DELETE | PRESERVE } ROWS 
]

Parameters

Column Definition

SQL syntax

For all data types other than LOBs, the syntax is as follows:

ColumnName ColumnDataType
 [DEFAULT DefaultVal]
 [[NOT] INLINE]
 [PRIMARY KEY | UNIQUE | 
 NULL [UNIQUE] | 
 NOT NULL [PRIMARY KEY | UNIQUE] 
]

For LOB data types, you cannot create a primary key or unique constraint on LOB columns. In addition, LOB data types are stored out of line, so the INLINE attribute cannot be specified.

For all LOB data types, the syntax is:

ColumnName ColumnDataType
 [DEFAULT DefaultVal] [[NOT] NULL] |
 [[NOT] NULL] [DEFAULT DefaultVal]

Parameters

The column definition has the following parameters:

Description

• All columns participating in the primary key are NOT NULL.

• A PRIMARY KEY that is specified in the ColumnDefinition can only be specified for one column.

• PRIMARY KEY cannot be specified in both the ColumnDefinition parameters and CREATE TABLE parameters.

• For both primary key and foreign key constraints, duplicate column names are not allowed in the constraint column list.

• 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.

• You cannot create a table that has a foreign key referencing a cached table.

• UNIQUE column constraint and default column values are not supported with materialized views.

• Use the ALTER TABLE statement to change the representation of the primary key index for a table.

• If you specify the AS SelectQuery clause:

  • Data types and data type lengths are derived from the SelectQuery. Do not specify data types on the columns of the table you are creating.

  • TimesTen defines on columns in the new table NOT NULL constraints that were explicitly created on the corresponding columns of the selected table if SelectQuery selects the column rather than an expression containing the column.

  • NOT NULL constraints that were implicitly created by TimesTen on columns of the selected table (for example, primary keys) are carried over to the new table. You can override the NOT NULL constraint on the selected table by defining the new column as NULL. For example: CREATE TABLE newtable (newcol NULL) AS SELECT (col) FROM tab;

  • NOT INLINE/INLINE attributes are carried over to the new table.

  • Unique keys, foreign keys, indexes and column default values are not carried over to the new table.

  • If all expressions in SelectQuery are columns, rather than expressions, then you can omit the columns from the table you are creating. In this case, the name of the columns are the same as the columns in SelectQuery. If the SelectQuery contains an expression rather than a simple column reference, either specify a column alias or name the column in the CREATE TABLE statement.

  • Do not specify foreign keys on the table you are creating.

  • Do not specify the SELECT FOR UPDATE clause in SelectQuery.

  • The ORDER BY clause is not supported when you use the AS SelectQuery clause.

  • SelectQuery cannot contain set operators UNION, MINUS, INTERSECT.

  • In a replicated environment for an active standby pair, if DDL_REPLICATION_LEVEL=2 when you execute the CREATE TABLE on the active database, the table, including global temporary tables, will be replicated to all databases in the replication scheme. Tables are only replicated to TimesTen instances when DDL_REPLICATION_LEVEL=2.

    To include a new table into an active standby pair when the table is created, set DDL_REPLICATION_LEVEL=2 and DDL_REPLICATION_ACTION to INCLUDE before executing the CREATE TABLE statement on the active database. If DDL_REPLICATION_ACTION is set to EXCLUDE, the new table is not included in the active standby pair. You must execute the ALTER ACTIVE STANDBY PAIR INCLUDE TABLE statement to include the table after creation on all databases. In this case, the table must be empty and present on all databases before executing the ALTER ACTIVE STANDBY PAIR INCLUDE TABLE statement as the table contents will be truncated when this statement is executed.

    See "ALTER SESSION" for more information.

• By default, a range index is created to enforce the primary key. Use the UNIQUE HASH clause to specify a hash index for the primary key.

  • If your application performs range queries using a table's primary key, then choose a range index for that table by omitting the UNIQUE HASH clause.

  • If your application performs only exact match lookups on the primary key, then a hash index may offer better response time and throughput. In such a case, specify the UNIQUE HASH clause.

• TimesTen supports one hash index per table. A hash index is defined on the primary key of a table.

• A unique hash index can be specified only for the primary key.

• A hash index is created with a fixed number of buckets that remains constant for the life of the table or until the hash index is resized using an ALTER TABLE statement to change hash index size. Fewer buckets in the hash index result in more hash collisions. More buckets reduce collisions but can waste memory. Hash key comparison is a fast operation, so a small number of hash collisions does not cause a performance problem for TimesTen.

  The bucket count is derived as the ratio of the maximum table cardinality, divided by the value of the PAGES parameter. To ensure that the hash index is sized correctly, an application must indicate the expected size of the table. This is done with the PAGES parameter. The PAGES parameter should be the expected number of rows in the table, divided by 256. (Since 256 is the number of rows TimesTen stores on each page, the value provided is the expected number of pages in the table.) The application may specify a larger value for PAGES, and therefore fewer rows per bucket on average, if memory use is not an overriding concern.

• At most 16 columns are allowed in a hash key.

• 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.

  • You must stop the replication agent before adding or dropping a foreign key on a replicated table.

• The data in a global temporary is private to the current connection and does not need to be secured between users. Thus global temporary tables do not require object privileges.

• 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.

• 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.

• LRU and time-based aging can be combined in one system. If you use only LRU aging, the aging thread wakes up based on the cycle specified for the whole database. If you use only time-based aging, the aging thread wakes up based on an optimal frequency. This frequency is determined by the values specified in the CYCLE clause for all tables. If you use both LRU and time-based aging, then the thread wakes up based on a combined consideration of both types.

• 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 immediately regardless of the aging state.

• Aging restrictions:

  • LRU aging and time-based aging are not supported on detail tables of materialized views.

  • LRU aging and time-based aging are not supported on global temporary tables.

  • You cannot drop the column that is used for time-based aging.

  • The aging policy and aging state must be the same in all sites of replication.

  • 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.

In-memory columnar compression of tables

You can compress tables at the column level, which stores data more efficiently. This eliminates redundant storage of duplicate values within columns and improves the performance of SQL queries that perform full table scans.

You can define one or more columns in a table to be compressed, which is called a compressed column group. You can define one or more compressed column groups in each table.

A dictionary table is created for each compressed column group that contains a column with all the distinct values of the compressed column group. The compressed column group now contains a pointer to the row in the dictionary table for the appropriate value. The width of this pointer can be 1, 2, or 4 bytes long depending on the maximum number of entries you defined for the dictionary table. So if the column being compressed was wider than the pointer width, you have reduced the amount of space used by the table.

Figure 6-1 shows the compressed column group in the table pointing to the appropriate row in the dictionary table.

Figure 6-1 Table Compression

Description of "Figure 6-1 Table Compression"

The dictionary table has a column of pointers to each of the distinct values. When the user configures the maximum number of distinct entries for the compressed column group, the size of the compressed column group is set as follows:

• 1 byte for a maximum number of entries of 255 (2⁸-1). When the maximum number is between 1 and 255, the dictionary size is set to 255 (2⁸-1) values and the compressed column group pointer column is 1 byte.

• 2 bytes for a maximum number of entries of 65,535 (2¹⁶-1). When the maximum number is between 256 and 65,535, the dictionary size is set to 65,535 (2¹⁶-1) values and the compressed column group pointer column is 2 bytes.

• 4 bytes for a maximum number of entries of 4,294,967,295 (2³²-1). When the maximum number is between 65,536 and 4,294,967,295, the dictionary size is set to 4,294,967,295 (2³²-1) values and the compressed column group pointer column is 4 bytes. This is the default.

Syntax

The syntax for TableCompression is:

[COMPRESS (CompressColumns [,...])] OPTIMIZED FOR READ

The CompressColumns syntax is as follows:

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

Parameters

TableCompression syntax has the following parameters:

Description

• Compressed column groups can be added at the time of table creation or added later using ALTER TABLE. You can drop the entire compressed column group with the ALTER TABLE statement.

• You can create a primary or unique key, where part or or all of the columns included in the key are compressed. For compressed columns included in a primary or unique key, you can include columns that exist within a compressed column group, but you do not have to include all of the columns within the compressed column group. In addition, you can include columns from different compressed column groups.

• For compressed tables, all SQL operations lock the table. Table and index scans that access the columns of any compressed column group are somewhat slower due to dictionary lookup. However, since all the operations on the table acquire table locks, you do not need to acquire and release lower level locks. INSERT, DELETE and UPDATE operations on these tables will not scale.

• Indexes can be created on any columns in the table. This includes compressed columns and includes columns that exist in separate compression column groups.

• LOB columns cannot be compressed.

• Compression is not supported on columns in replicated tables, cache group tables, grid tables, or on global temporary tables. You cannot create a table with the CREATE TABLE AS SELECT statement when defining in-memory columnar compression for that table in that statement.

• You cannot create materialized views and materialized view logs on tables enabled for compression.

Examples

A range index is created on partnumber because it is the primary key.

Command> CREATE TABLE price
> (partnumber INTEGER NOT NULL PRIMARY KEY,
> vendornumber INTEGER NOT NULL,
> vendpartnum CHAR(20) NOT NULL,
> unitprice DECIMAL(10,2),
> deliverydays SMALLINT,
> discountqty SMALLINT);
Command> INDEXES price;
Indexes on table SAMPLEUSER.PRICE:
 PRICE: unique range index on columns:
    PARTNUMBER
  1 index found.
1 index found on 1 table.

A hash index is created on column clubname, the primary key.

CREATE TABLE recreation.clubs
(clubname CHAR(15) NOT NULL PRIMARY KEY,
 clubphone SMALLINT,
 activity CHAR(18))
UNIQUE HASH ON (clubname) PAGES = 30;

A range index is created on the two columns membername and club because together they form the primary key.

Command> CREATE TABLE recreation.members
> (membername CHAR(20) NOT NULL,
>  club CHAR(15) NOT NULL,
>  memberphone SMALLINT,
>  PRIMARY KEY (membername, club));
Command> INDEXES recreation.members;
Indexes on table RECREATION.MEMBERS:
  MEMBERS: unique range index on columns:
    MEMBERNAME
    CLUB
  1 index found on 1 table.

No hash index is created on the table recreation.events.

CREATE TABLE recreation.events
(sponsorclub CHAR(15),
 event CHAR(30),
 coordinator CHAR(20),
 results VARBINARY(10000));

A hash index is created on the column vendornumber.

CREATE TABLE purchasing.vendors
(vendornumber INTEGER NOT NULL PRIMARY KEY,
 vendorname CHAR(30) NOT NULL,
 contactname CHAR(30),
 phonenumber CHAR(15),
 vendorstreet CHAR(30) NOT NULL,
 vendorcity CHAR(20) NOT NULL,
 vendorstate CHAR(2) NOT NULL,
 vendorzipcode CHAR(10) NOT NULL,
 vendorremarks VARCHAR(60))
UNIQUE HASH ON (vendornumber) PAGES = 101;

A hash index is created on the columns membername and club because together they form the primary key.

CREATE TABLE recreation.members
    (membername CHAR(20) NOT NULL,
        club CHAR(15) NOT NULL,
        memberphone SMALLINT,
        PRIMARY KEY (membername, club))
    UNIQUE HASH ON (membername, club) PAGES = 100;

A hash index is created on the columns firstname and lastname because together they form the primary key in the table authors. A foreign key is created on the columns authorfirstname and authorlastname in the table books that references the primary key in the table authors.

CREATE TABLE authors
    (firstname VARCHAR(255) NOT NULL,
        lastname VARCHAR(255) NOT NULL,
        description VARCHAR(2000),
        PRIMARY KEY (firstname, lastname))
    UNIQUE HASH ON (firstname, lastname) PAGES=20;
CREATE TABLE books
    (title VARCHAR(100),
    authorfirstname VARCHAR(255),
    authorlastname VARCHAR(255),
    price DECIMAL(5,2),
    FOREIGN KEY (authorfirstname, authorlastname) 
    REFERENCES authors(firstname, lastname));

The following statement overrides the default character of VARCHAR columns and creates a table where one VARCHAR (10) column is NOT INLINE and one VARCHAR (144) is INLINE:

CREATE TABLE t1
    (c1 VARCHAR(10) NOT INLINE NOT NULL,
    c2 VARCHAR(144) INLINE NOT NULL);

The following statement creates a table with a UNIQUE column for book titles:

CREATE TABLE books
    (title VARCHAR(100) UNIQUE,
        authorfirstname VARCHAR(255),
        authorlastname VARCHAR(255),
        price DECIMAL(5,2),
        FOREIGN KEY (authorfirstname, authorlastname)
        REFERENCES authors(firstname, lastname));

The following statement creates a table with a default value of 1 on column x1 and a default value of SYSDATE on column d:

CREATE TABLE t1
    (x1 INT DEFAULT 1, d TIMESTAMP DEFAULT SYSDATE);

This example creates the rangex table and defines col1 as the primary key. A range index is created by default.

Command> CREATE TABLE rangex (col1 TT_INTEGER PRIMARY KEY);
Command> INDEXES rangex;
Indexes on table SAMPLEUSER.RANGEX:
  RANGEX: unique range index on columns:
    COL1
  1 index found
1 index found on 1 table.

The following statement illustrates the use of the ON DELETE CASCADE clause for parent/child tables of the HR schema. Tables with foreign keys have been altered to enable ON DELETE CASCADE.

ALTER TABLE countries
ADD CONSTRAINT countr_reg_fk
         FOREIGN KEY (region_id)
           REFERENCES regions(region_id) ON DELETE CASCADE;
ALTER TABLE locations
     ADD CONSTRAINT loc_c_id_fk
          FOREIGN KEY (country_id)
                    REFERENCES countries(country_id) ON DELETE CASCADE;
ALTER TABLE departments
     ADD CONSTRAINT dept_loc_fk
         FOREIGN KEY (location_id)
           REFERENCES locations (location_id) ON DELETE CASCADE;
ALTER TABLE employees
     ADD CONSTRAINT     emp_dept_fk
         FOREIGN KEY (department_id)
           REFERENCES departments ON DELETE CASCADE;
ALTER TABLE employees
     ADD CONSTRAINT     emp_job_fk
         FOREIGN KEY (job_id)
           REFERENCES jobs (job_id);
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_job_fk
         FOREIGN KEY (job_id)
           REFERENCES jobs;
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_emp_fk
         FOREIGN KEY (employee_id)
           REFERENCES employees ON DELETE CASCADE;
ALTER TABLE job_history
     ADD CONSTRAINT     jhist_dept_fk
         FOREIGN KEY (department_id)
           REFERENCES departments ON DELETE CASCADE;
     ;

This example shows how time resolution works with aging.

If lifetime is 3 days (resolution is in days):

• If (SYSDATE - ColumnValue) <= 3, do not age.

• If (SYSDATE - ColumnValue) > 3, then the row is a candidate for aging.

• If (SYSDATE - ColumnValue) = 3 days, 22 hours. The row is not aged out if you specified a lifetime of 3 days. The row would be aged out if you had specified a lifetime of 72 hours.

This example creates a table with LRU aging. Aging state is ON by default.

CREATE TABLE agingdemo
       (agingid NUMBER NOT NULL PRIMARY KEY,
        name  VARCHAR2 (20)
       )
       AGING LRU;
Command> DESCRIBE agingdemo;
Table USER.AGINGDEMO:
  Columns:
     *AGINGID NUMBER NOT NULL
     NAME VARCHAR2 (20) INLINE
     AGING LRU ON
1 table found.
(primary key columns are indicated with *)

This example creates a table with time-based aging. Lifetime is 3 days. Cycle is not specified, so the default is 5 minutes. Aging state is OFF.

CREATE TABLE agingdemo2
       (agingid NUMBER NOT NULL PRIMARY KEY,
        name  VARCHAR2 (20),
        agingcolumn TIMESTAMP NOT NULL
        )
        AGING USE agingcolumn LIFETIME 3 DAYS OFF;
Command> DESCRIBE agingdemo2;
Table USER.AGINGDEMO2:
  Columns:
     *AGINGID NUMBER NOT NULL
     NAME VARCHAR2 (20) INLINE
     AGINGCOLUMN TIMESTAMP (6) NOT NULL
  Aging use AGINGCOLUMN lifetime 3 days cycle 5 minutes off
1 table found.
(primary key columns are indicated with *)

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

CREATE TABLE agingdemo2
        (agingid NUMBER NOT NULL PRIMARY KEY,
        name  VARCHAR2 (20),
        agingcolumn TIMESTAMP NOT NULL
        )
        AGING USE agingcolumn LIFETIME 3 DAYS OFF;
ALTER TABLE agingdemo2
        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 agingdemo2
        DROP AGING;
ALTER TABLE agingdemo2
        ADD AGING LRU;
Command> DESCRIBE agingdemo2;
Table USER.AGINGDEMO2:
  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 *)

Attempt to create a table with time-based aging. Define aging column with data type TT_DATE and LIFETIME 3 hours. An error is generated because the LIFETIME unit must be expressed as DAYS.

Command> CREATE TABLE aging1 (col1 TT_INTEGER PRIMARY KEY, 
         col2 TT_DATE NOT NULL) AGING USE col2 LIFETIME 3 HOURS;
 2977: Only DAY lifetime unit is allowed with a TT_DATE column
The command failed.

Use AS SelectQuery clause to create the table emp. Select last_name from the employees table where employee_id between 100 and 105. You see 6 rows inserted into emp. First issue the SELECT statement to see rows that should be returned.

Command> SELECT last_name FROM employees 
 WHERE employee_id BETWEEN 100 AND 105;
< King >
< Kochhar >
< De Haan >
< Hunold >
< Ernst >
< Austin >
6 rows found.
Command> CREATE TABLE emp AS SELECT employee_id FROM employees
>WHERE employee_id BETWEEN 100 AND 105;
6 rows inserted.
Command> SELECT * FROM emp;
< King >
< Kochhar >
< De Haan >
< Hunold >
< Ernst >
< Austin >
6 rows found.

Use AS SelectQuery to create table totalsal. Sum salary and insert result into totalsalary. Define alias s for SelectQuery expression.

Command> CREATE TABLE totalsal AS SELECT SUM (salary) s FROM employees;
1 row inserted.
Command> SELECT * FROM totalsal;
< 691400 >
1 row found.

Use AS SelectQuery to create table defined with column commission_pct. Set default to .3. First describe table employees to show that column commission_pct is of type NUMBER (2,2). For table c_pct, column commission_pct inherits type NUMBER (2,2) from column commission_pct of employees table.

Command> DESCRIBE employees;
Table SAMPLEUSER.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE UNIQUE 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)

1 table found.
(primary key columns are indicated with *)
Command> CREATE TABLE c_pct (commission_pct DEFAULT .3) AS SELECT
         commission_pct FROM employees;
107 rows inserted.
Command> DESCRIBE c_pct;

Table SAMPLEUSER.C_PCT:
  Columns:
    COMMISSION_PCT                 NUMBER (2,2) DEFAULT .3

1 table found.
(primary key columns are indicated with *)

The following example creates the employees table where the job_id is compressed:

Command> CREATE TABLE EMPLOYEES
 (EMPLOYEE_ID NUMBER (6) PRIMARY KEY, 
 FIRST_NAME VARCHAR2(20), 
 LAST_NAME VARCHAR2(25) NOT NULL, 
 EMAIL VARCHAR2(25) NOT NULL, 
 PHONE_NUMBER VARCHAR2(20),
 HIRE_DATE DATE NOT NULL, 
 JOB_ID VARCHAR2(10) NOT NULL, 
 SALARY NUMBER (8,2), 
 COMMISSION_PCT NUMBER (2,2), 
 MANAGER_ID NUMBER(6), 
 DEPARTMENT_ID NUMBER(4)) 
 COMPRESS (JOB_ID BY DICTIONARY) OPTIMIZED FOR READ;

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 BY DICTIONARY ) OPTIMIZED FOR READ
 
1 table found.
(primary key columns are indicated with *)

The following example shows that there are three dictionary table sizes. The value you specify for the maximum number of entries is rounded up to the next size. For example, specifying 400 as the maximum number of job IDs creates a dictionary table that can have at most 65535 entries. The default size of 2³²-1 is not shown in the DESCRIBE output.

Command> CREATE TABLE employees 
 (employee_id NUMBER(6) PRIMARY KEY, 
  first_name VARCHAR2(20), 
  last_name VARCHAR2(25), 
  email VARCHAR2(25) NOT NULL, 
  job_id VARCHAR2(10) NOT NULL, 
  manager_id NUMBER(6), 
  department_id NUMBER(4)) 
 COMPRESS (last_name BY DICTIONARY MAXVALUES=70000, 
           job_id BY DICTIONARY MAXVALUES=400, 
           department_id BY DICTIONARY MAXVALUES=100) 
 OPTIMIZED FOR READ;

Command> DESCRIBE employees;                                                   
Table MYSCHEMA.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE
    EMAILS                          VARCHAR2 (25) INLINE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)
  COMPRESS ( LAST_NAME BY DICTIONARY,
             JOB_ID BY DICTIONARY MAXVALUES=65535,
             DEPARTMENT_ID BY DICTIONARY MAXVALUES=255 ) OPTIMIZED FOR READ
 
1 table found.
(primary key columns are indicated with *)

See also