Comments within SQL statements

A comment can appear between keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:

Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. The text can span multiple lines. End the comment with an asterisk and a slash. (*/). You do not need to separate the opening and terminating characters from the text by a space or line break.

Begin the comment with two hyphens (--). Proceed with the text of the comment. The text cannot extend to a new line. End the comment with a line break.

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

Indicates a subscriber database. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

DROP SUBSCRIBERFullStoreName

Indicates that updates should no longer be sent to the specified subscriber database. This operation fails if the replication scheme has only one subscriber. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

ALTER STOREFullStoreNameSETStoreAttribute

Indicates changes to the attributes of a database. Only the PORT and TIMEOUT attributes can be set for subscribers. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

For example, if the database path is directory/subdirectory/data.ds0, then data is the database name that should be used.

This is the database file name specified in the DataStore attribute of the DSN description with optional host ID in the form:

DataStoreName[ONHost]

Host can be either an IP address or a literal host name assigned to one or more IP addresses, as described in "Configuring host IP addresses" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. Host names containing special characters must be surrounded by double quotes. For example: "MyHost-500".

{INCLUDE|EXCLUDE}

{[TABLE [Owner.]TableName[,...]|

CACHE GROUP

[[Owner.]CacheGroupName]|[,...]

SEQUENCE [[Owner.]SequenceName[,...]}

[,...]

Includes in or excludes from replication the tables, sequences or cache groups listed.

INCLUDE adds the tables, sequences or cache groups to replication. Use one INCLUDE clause for each object type (table, sequence or cache group).

EXCLUDE removes the tables, sequences or cache groups from replication. Use one EXCLUDE clause for each object type (table, sequence or cache group).

ADD ROUTE MASTERFullStoreNameSUBSCRIBERFullStoreName

Adds NetworkOperation to replication scheme. Allows you to control the network interface that a master store uses for every outbound connection to each of its subscriber stores. In the context of the ADD ROUTE clause, each master database is a subscriber of the other master database and each read-only subscriber is a subscriber of both master databases.

Can be specified more than once.

For FullStoreName, "ONhost" must be specified.

DROP ROUTE MASTERFullStoreNameSUBSCRIBERFullStoreName

Drops NetworkOperation from replication scheme.

Can be specified more than once.

For FullStoreName, "ONhost" must be specified.

MASTERIPMasterHost|SUBSCRIBERIPSubscriberHost

MasterHost and SubscriberHost are the IP addresses for the network interface on the master and subscriber stores. Specify in dot notation or canonical format or in colon notation for IPV6.

Clause can be specified more than once. Valid for both ADD and DROP ROUTE MASTER.

PRIORITYPriority

Variable expressed as an integer from 1 to 99. Denotes the priority of the IP address. Lower integral values have higher priority. An error is returned if multiple addresses with the same priority are specified. Controls the order in which multiple IP addresses are used to establish peer connections.

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

Use ADD SUBSCRIBERFullStoreName to add a subscriber to the replication scheme.

Use DROP SUBSCRIBERFullStoreName to drop a subscriber from the replication scheme.

Use ALTER STOREFullStoreNameSETStoreAttribute 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.

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

ALTER CACHE GROUP

The ALTER CACHE GROUP statement allows 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.

Determines which rows in the cache are updated during an autorefresh. If the INCREMENTAL clause is specified, TimesTen refreshes only rows that have been changed on Oracle since the last propagation. If the FULL clause is specified or if there is neither FULL nor INCREMENTAL clause specified, TimesTen updates all rows in the cache with each autorefresh. The default mode is INCREMENTAL.

INTERVAL

IntervalValue

Indicates the interval at which autorefresh should occur in units of minutes, seconds or milliseconds. An integer value that specifies how often AUTOREFRESH should be scheduled, in minutes, seconds or milliseconds. The default value is 10 minutes. If the specified interval is not long enough for an AUTOREFRESH to complete, a runtime warning is generated and the next AUTOREFRESH waits until the current one finishes. An informational message is generated in the support log if the wait queue reaches 10.

STATE

Specifies whether AUTOREFRESH should be changed to on, off or paused. By default, the AUTOREFRESH STATE is ON.

ON

AUTOREFRESH is scheduled to occur at the specified interval.

OFF

A scheduled AUTOREFRESH is cancelled, and TimesTen does not try to maintain the information necessary for an INCREMENTAL refresh. Therefore if AUTOREFRESH is turned on again at a later time, the first refresh is FULL.

PAUSED

A scheduled AUTOREFRESH is cancelled, but TimesTen tries to maintain the information necessary for an INCREMENTAL refresh. Therefore if AUTOREFRESH is turned on again at a later time, a full refresh may not be necessary.

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.

Required keyword that causes recompilation of the function. If the function does not compile successfully, use the ttIsql command SHOW ERRORS to display the compiler error messages.

compiler_parameters_clause

Use this optional clause to specify a value for one of the PL/SQL persistent compiler parameters. The PL/SQL persistent compiler parameters are PLSQL_OPTIMIZE_LEVEL, PLSCOPE_SETTINGS and NLS_LENGTH_SEMANTICS.

You can specify each parameter once in the statement.

If you omit a parameter from this clause and you specify REUSE SETTINGS, then if a value was specified for the parameter in an earlier compilation, TimesTen uses that earlier value. If you omit a parameter and either you do not specify REUSE SETTINGS or no value has been specified for the parameter in an earlier compilation, then TimesTen obtains the value for the parameter from the session environment.

REUSE SETTINGS

Use this optional clause to prevent TimesTen from dropping and reacquiring compiler switch settings. When you specify REUSESETTINGS, TimesTen preserves the existing settings and uses them for the compilation of any parameters for which values are not specified.

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.

Required clause used to force the recompilation of the package specification, package body, or both.

[PACKAGE|SPECIFICATION|BODY]

Specify PACKAGE to recompile both the package specification and the body. Specify SPECIFICATION to recompile the package specification. Specify BODY to recompile the package body.

PACKAGE is the default.

compiler_parameters_clause

Use this optional clause to specify a value for one of the PL/SQL persistent compiler parameters. The PL/SQL persistent compiler parameters are PLSQL_OPTIMIZE_LEVEL, PLSCOPE_SETTINGS and NLS_LENGTH_SEMANTICS.

You can specify each parameter once in the statement.

If you omit a parameter from this clause and you specify REUSE SETTINGS, then if a value was specified for the parameter in an earlier compilation, TimesTen uses that earlier value. If you omit a parameter and either you do not specify REUSE SETTINGS or no value has been specified for the parameter in an earlier compilation, then TimesTen obtains the value for the parameter from the session environment.

REUSE SETTINGS

Use this optional clause to prevent TimesTen from dropping and reacquiring compiler switch settings. When you specify REUSE SETTINGS, TimesTen preserves the existing settings and uses them for the compilation of any parameters for which values are not specified.

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.

Required keyword that causes recompilation of the procedure. If the procedure does not compile successfully, use the ttIsql command SHOW ERRORS to display the compiler error messages.

compiler_parameters_clause

Use this optional clause to specify a value for one of the PL/SQL persistent compiler parameters. The PL/SQL persistent compiler parameters are PLSQL_OPTIMIZE_LEVEL, PLSCOPE_SETTINGS and NLS_LENGTH_SEMANTICS.

You can specify each parameter once in the statement.

If you omit a parameter from this clause and you specify REUSE SETTINGS, then if a value was specified for the parameter in an earlier compilation, TimesTen uses that earlier value. If you omit a parameter and either you do not specify REUSE SETTINGS or no value has been specified for the parameter in an earlier compilation, then TimesTen obtains the value for the parameter from the session environment.

REUSE SETTINGS

Use this optional clause to prevent TimesTen from dropping and reacquiring compiler switch settings. When you specify REUSE SETTINGS, TimesTen preserves the existing settings and uses them for the compilation of any parameters for which values are not specified.

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.

ALTER REPLICATION

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 TimesTen to TimesTen Replication Guide for more information.

Adds a new element to the existing replication scheme. ElementName is an identifier of up to 30 characters. With DATASTORE elements, the ElementName must be unique with respect to other DATASTORE element names within the first 20 chars.

If the element is a DATASTORE, all tables and cache groups are included in the database. SEQUENCE elements that are part of the database do not have their return services modified by this statement.

ADD ELEMENTElementNameDATASTORE

{INCLUDE | EXCLUDE}

{TABLE [[Owner.]TableName[,...]]|

CACHE GROUP[[Owner.]CacheGroupName[,...]]|

SEQUENCE[[Owner.]SequenceName[,...]]} [,...]

Adds a new DATASTORE element to the existing replication scheme. ElementName is an identifier of up to 30 characters. With DATASTORE elements, the ElementName must be unique with respect to other DATASTORE element names within the first 20 chars.

INCLUDE includes in the database only the tables and cache groups listed. Use one INCLUDE clause for each object type (table, cache group or sequence).

EXCLUDE includes in the database all tables and cache groups except the tables, cache groups and sequences listed. Use one EXCLUDE clause for each object type (table, cache group or sequence).

If the element is a sequence, RETURN attributes are not applied, no conflict checking is supported and sequences that cycle return an error.

ADD SUBSCRIBERFullStoreName

Indicates an additional subscriber database. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

ALTER ELEMENT * INFullStoreName

SET { MASTER | PROPAGATOR }FullStoreName

Makes a change to all elements for which FullStoreName is the MASTER or PROPAGATOR. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

This syntax can be used on a set of element names to:

Add, alter, or drop subscribers.

Set the MASTER or PROPAGATOR status of the element set.

SEQUENCE elements that are part of the database being altered do not have their return services modified by this statement.

ALTER ELEMENTElementName

Name of the element to which a subscriber is to be added or dropped.

ALTER ELEMENT

ElementName1

SET NAMEElementName2

Renames ElementName1 with the name ElementName2. You can only rename elements of type TABLE.

ALTER ELEMENTElementName

{INCLUDE|EXCLUDE}

{TABLE [Owner.]TableName|

CACHE GROUP[Owner.]CacheGroupName|

SEQUENCE[Owner.]SequenceName} [,...]

ElementName is the name of the element to be altered.

INCLUDE adds to the database the tables and cache groups listed. Use one INCLUDE clause for each object type (table or cache group).

EXCLUDE removes from the database the tables and cache groups listed. Use one EXCLUDE clause for each object type (table, cache group or sequence).

If the element is a sequence, RETURN attributes are not applied, no conflict checking is supported and sequences that cycle return an error.

ALTER SUBSCRIBERFullStoreName

SET RETURN RECEIPT

[BY REQUEST]|NO RETURN

Indicates an alteration to a subscriber database to enable, disable, or change the return receipt service. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

CheckConflicts

Check for replication conflicts when simultaneously writing to bidirectionally replicating TABLE elements between databases. You cannot check for conflicts when replicating elements of type DATASTORE. See "CHECK CONFLICTS".

COMPRESS TRAFFIC {ON | OFF}

Compress replicated traffic to reduce the amount of network bandwidth. ON specifies that all replicated traffic for the database defined by STORE be compressed. OFF (the default) specifies no compression. See "Compressing replicated traffic" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for details.

CONFLICT REPORTING SUSPEND ATValue

Suspends conflict resolution reporting.

Value is a non-negative integer. The default is 0 and means never suspend. Conflict reporting is suspended when the rate of conflict exceeds Value. If you set Value to 0, conflict reporting suspension is turned off.

Use this clause for table-level replication.

CONFLICT REPORTING RESUME ATValue

Resumes conflict resolution reporting.

Value is a non-negative integer. Conflict reporting is resumed when the rate of conflict falls below Value. The default is 1.

Use this clause for table level replication.

DISABLE RETURN{SUBSCRIBER | ALL}NumFailures

Set the return service failure policy so that return service blocking is disabled after the number of timeouts specified by NumFailures. Selecting SUBSCRIBER applies this policy only to the subscriber that fails to acknowledge replicated updates within the set timeout period. ALL applies this policy to all subscribers should any of the subscribers fail to respond. This failure policy can be specified for either the RETURN RECEIPT or RETURN TWOSAFE service.

If DISABLE RETURN is specified but RESUME RETURN is not specified, the return services remain off until the replication agent for the database has been restarted.

Set to override the DurableCommits setting on a database and enable durable commit when return service blocking has been disabled by DISABLE RETURN.

DROP ELEMENT * INFullStoreName

Deletes the replication description of all elements for which FullStoreName is the MASTER. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

DROP ELEMENTElementName

Deletes the replication description of ElementName.

DROP SUBSCRIBERFullStoreName

Indicates that updates should no longer be sent to the specified subscriber database. This operation fails if your replication scheme has only one subscriber. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

FAILTHRESHOLDValue

The number of log files that can accumulate for a subscriber database. If this value is exceeded, the subscriber is set to the Failed state.

For example, if the database path is directory/subdirectory/data.ds0, then data is the database name.

This is the database file name specified in the DataStore attribute of the DSN description with optional host ID in the form:

DataStoreName[ONHost]

Host can be either an IP address or a literal host name assigned to one or more IP addresses, as described in "Configuring host IP addresses" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. Host names containing special characters must be surrounded by double quotes. For example: "MyHost-500".

LOCAL COMMIT ACTION

{NO ACTION | COMMIT}

Specifies the default action to be taken for a RETURN TWOSAFE transaction in the event of a timeout.

NO ACTION: On timeout, the commit function returns to the application, leaving the transaction in the same state it was in when it entered the commit call, with the exception that the application is not able to update any replicated tables. The application can only reissue the commit. The transaction may not be rolled back. This is the default.

COMMIT: On timeout, the commit function attempts to perform a COMMIT to end the transaction locally. No more operations are possible on the same transaction.

This setting can be overridden for specific transactions by calling the ttRepSyncSet procedure with the localAction parameter.

MASTERFullStoreName

The database on which applications update the specified element. The MASTER database sends updates to its SUBSCRIBER databases. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

NO RETURN

Specifies that no return service is to be used. This is the default.

For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

PORTPortNumber

The TCP/IP port number on which the replication agent on this database listens for connections. If not specified, the replication agent allocates a port number automatically.

All TimesTen databases that replicate to each other must use the same port number.

PROPAGATORFullStoreName

The database that receives replicated updates and passes them on to other databases.

RESUME RETURNMilliSeconds

If return service blocking has been disabled by DISABLE RETURN, this attribute sets the policy on when to re-enable return service blocking. Return service blocking is re-enabled as soon as the failed subscriber acknowledges the replicated update in a period of time that is less than the specified MilliSeconds.

If DISABLE RETURN is specified but RESUME RETURN is not specified, the return services remain off until the replication agent for the database has been restarted.

RETURN RECEIPT [BY REQUEST]

Enables the return receipt service, so that applications that commit a transaction to a master database are blocked until the transaction is received by all subscribers.

RETURN RECEIPT applies the service to all transactions. If you specify RETURN RECEIPT BY REQUEST, you can use the ttRepSyncSet procedure to enable the return receipt service for selected transactions. For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

RETURN SERVICES {ON | OFF} WHEN [REPLICATION] STOPPED

Set the return service failure policy so that return service blocking is either enabled or disabled when the replication agent is in the "stop" or "pause" state.

OFF is the default when using the RETURN RECEIPT service. ON is the default when using the RETURN TWOSAFE service.

Enables the return twosafe service, so that applications that commit a transaction to a master database are blocked until the transaction is committed on all subscribers.

RETURN TWOSAFE applies the service to all transactions. If you specify RETURN TWOSAFE BY REQUEST, you can use the ttRepSyncSet procedure to enable the return receipt service for selected transactions. For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

RETURN WAIT TIMESeconds

Specifies the number of seconds to wait for return service acknowledgement. The default value is 10 seconds. A value of '0' means there is no timeout. Your application can override this timeout setting by calling the ttRepSyncSet procedure with the returnWait parameter

SET {MASTER | PROPAGATOR}FullStoreName

Sets the given database to be the MASTER or PROPAGATOR of the given elements. The FullStoreName must the be database's file base name.

SUBSCRIBERFullStoreName

A database that receives updates from the MASTER databases. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

TABLE DEFINITION CHECKING {EXACT|RELAXED}

Specifies type of table definition checking that occurs on the subscriber:

EXACT - The tables must be identical on master and subscriber.

RELAXED - The tables must have the same key definition, number of columns and column data types.

The default is EXACT.

TIMEOUTSeconds

The amount of time a database waits for a response from another database before resending the message. Default: 120 seconds.

ADD ROUTE MASTERFullStoreNameSUBSCRIBERFullStoreName

Adds NetworkOperation to replication scheme. Allows you to control the network interface that a master store uses for every outbound connection to each of its subscriber stores.

Can be specified more than once.

For FullStoreName, ON"host" must be specified.

DROP ROUTE MASTERFullStoreNameSUBSCRIBERFullStoreName

Drops NetworkOperation from replication scheme.

Can be specified more than once.

For FullStoreName, ON"host" must be specified.

MASTERIPMasterHost|SUBSCRIBERIPSubscriberHost

MasterHost and SubscriberHost are the IP addresses for the network interface on the master and subscriber stores. Specify in dot notation or canonical format or in colon notation for IPV6.

Clause can be specified more than once. Valid for both ADD and DROP ROUTE MASTER.

PRIORITYPriority

Variable expressed as an integer from 1 to 99. Denotes the priority of the IP address. Lower integral values have higher priority. An error is returned if multiple addresses with the same priority are specified. Controls the order in which multiple IP addresses are used to establish peer connections.

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 * INFullStoreName. 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 * INFullStoreName 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.

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.

Examples

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

To include a table in the active standby pair when the table is created, set the DDL_REPLICATION_ACTION connection attribute to INCLUDE. If you do not want to include a table in the active standby pair when the table is created, set DDL_REPLICATION_ACTION to EXCLUDE. The default is INCLUDE.

If set to EXCLUDE, a subsequent ALTER ACTIVE STANDBY PAIR ... INCLUDE TABLE is required to be executed on the active database to add the table to the replication scheme. All tables must be empty on all active standby databases and subscribers as the table contents will be truncated when this statement is executed.

Indicates whether DDL is replicated across all databases in an active standby pair. The value can be one of the following:

1: Default. Add or drop a column to or from a replicated table on the active database using ALTER TABLE. The change is replicated to the table in the standby database.

2: Supports replication of the creation or dropping of tables, synonyms or indexes from the active database to the standby database. This does include creating or dropping global temporary tables, but does not include CREATE TABLE AS SELECT. The CREATE INDEX statement is replicated only when the index is created on an empty table.

Determines whether an error should be reported when there is data loss during an implicit or explicit character type conversion between NCHAR/NVARCHAR2 data and CHAR/VARCHAR2 data. Specify TRUE to enable error reporting. Specify FALSE to not report errors. The default is FALSE.

Controls how long PL/SQL procedures run before being automatically terminated. n represents the time, in seconds. Specify 0 for no time limit or any positive integer. The default is 30.

When you modify this value, the new value impacts PL/SQL program units that are currently running as well as any other program units subsequently executed in the same connection.

If PL/SQL is not enabled in your database and you specify this attribute, TimesTen throws an error.

PLSQL_OPTIMIZE_LEVEL = {0|1|2|3}

Specifies the optimization level used to compile PL/SQL library units. The higher the setting, the more effort the compiler makes to optimize PL/SQL library units. Possible values are 0, 1, 2 or 3. The default is 2.

If PL/SQL is not enabled in your database and you specify this attribute, TimesTen returns an error.

When parallel replication is configured, specify the track to which the transactions belong for the current connection. All transactions on replicated tables are associated with a track. The track number setting is constant for the lifetime of the connection, unless specifically reset. The default track number is 0.

If the number specified is for a non-existent replication track X, the transaction is assigned to a track number computed as X modulo ReplicationParallelism.

You cannot change tracks in the middle of a transaction unless all preceding operations have been read operations.

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

The NLS_SORT setting affects materialized views and cache group maintenance. Use the NLSSORT() SQL function rather than relying on the NLS_SORT setting.

Character length and byte length semantics are supported to resolve potential ambiguity regarding column length and storage size. Multibyte encoding character sets are supported (For example, UTF-8 or AL32UTF8). Multibyte encodings require varying amounts of storage per character depending on the character. For example, a UTF-8 character may require from 1 to 4 bytes.

If, for example, a column is defined as CHAR (10), all 10 characters fit in this column regardless of character set encoding. However, for UTF-8 character set encoding, up to 40 bytes are required. TimesTen supports character length and byte length semantics to avoid such ambiguity.

Primary key indexes are based on the BINARY collating sequence. You cannot use nonbinary NLS_SORT with equality searches on the primary key index.

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-ASCIICHAR and VARCHAR2 characters as well as NCHAR and NVARCHAR2 characters.

TIMESTEN8 character set restrictions:

Character set conversions are not allowed.

BINARY is the only acceptable collating sequence.

CHAR semantics are ignored. Characters are single-byte.

UPPER and LOWER functions support ASCII characters only. Results for non-ASCII characters are undefined. TimesTen does not return an error.

NLS_SORT settings other than BINARY could have a performance impact on character operations.

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.

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.

The following example enables parallel replication and uses the ALTER SESSION statement to change the replication track number to 5 for the current connection. The connection attributes ReplicationParallelism is set to a value higher than 5 and ReplicationApplyOrdering is set to 1.

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

Specifies that in the column ColumnName each row must contain a unique value.

MODIFY

Specifies that an attribute of a given column is to be changed to a new value.

DEFAULT [DefaultVal|NULL]

Specifies that the column has a default value, DefaultVal. If NULL, specifies that the default value of the columns is to be dropped. If a column with a default value of SYSDATE is added, the value of the column of the existing rows only is the system date at the time the column was added. If the default value is one of the USER functions the column value is the user value of the session that executed the ALTER TABLE statement. Currently, you cannot assign a default value for the ROWID data type.

Altering the default value of a column has no impact on existing rows.

ColumnName

Name of the column for which the unique constraint or default value is to be changed. A new column cannot have the same name as an existing column or another new column.

ColumnDataType

Type of the column to be added. Some types require additional parameters. See Chapter 1, "Data Types" for the data types that can be specified.

INLINE|NOT INLINE

By default, variable-length columns whose declared column length is > 128 bytes are stored out of line. Variable-length columns whose declared column length is <= 128 bytes are stored inline. The default behavior can be overridden during table creation through the use of the INLINE and NOT INLINE keywords.

ADD CONSTRAINTConstraintNamePRIMARY KEY(ColumnName

[,... ] ) [USE HASH INDEX PAGES ={RowPages| CURRENT}]

Adds a primary key constraint to the table. Columns of the primary key must be defined as NOT NULL.

Specify ConstraintName as the name of the index used to enforce the primary key constraint. Specify ColumnName as the name(s) of the NOT NULL column(s) used for the primary key.

Specify the USE HASH INDEX clause to use a hash index for the primary key. If not specified, a range index is used for the primary key constraint. Specify either RowPages (as a positive constant) or CURRENT to calculate the page count value. If you specify CURRENT, the current number of rows in the table is used to calculate the page count value.

Specifies that a foreign key is to be dropped. Optionally specifies that an added foreign key is named by the user.

ForeignKeyName

Name of the foreign key to be added or dropped. All foreign keys are assigned a default name by the system if the name was not specified by the user. Either the user-provided name or system name can be specified in the DROP FOREIGN KEY clause.

FOREIGN KEY

Specifies that a foreign key is to be added or dropped. See "FOREIGN KEY".

REFERENCES

Specifies that the foreign key references another table.

RefTableName

The name of the table that the foreign key references.

[ON DELETECASCADE]

Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.

USE HASH INDEX PAGES = {RowPages| CURRENT}

Specifies that a hash index is to be used for the primary key. If the primary key already uses a hash index, then this clause is equivalent to the SET PAGES clause.

USE TREE INDEX

Specifies that a range index is to be used for the primary key. If the primary key already uses a range index, TimesTen ignores this clause.

SET PAGES

Resizes the hash index based on the expected number of row pages in the table. Each row page can contain up to 256 rows of data. This number determines the number of hash buckets created for the hash index. The minimum is 1. If your estimate is too small, performance may be degraded. You can specify a constant (RowPages) or the current number of row pages. See "Column Definition" for a description of hash indexes and pages.

RowPages

The number of row pages expected.

CURRENT

Use the number of row pages currently in use.

ADD AGING LRU [ON | OFF]

Adds least recently used (LRU) aging to an existing table that has no aging policy defined.

The LRU aging policy defines the type of aging (least recently used (LRU)), the aging state (ON or OFF) and the LRU aging attributes.

Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON.

LRU attributes are defined by calling the ttAgingLRUConfig procedure. LRU attributes are not defined at the SQL level.

Adds time-based aging to an existing table that has no aging policy defined.

The time-based aging policy defines the type of aging (time-based), the aging state (ON or OFF) and the time-based aging attributes.

Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON.

Time-based aging attributes are defined at the SQL level and are specified by the LIFETIME and CYCLE clauses.

Specify ColumnName as the name of the column used for time-based aging. Define the column as NOT NULL and of data type TIMESTAMP or DATE. The value of this column is subtracted from SYSDATE, truncated using the specified unit (minute, hour, day) and then compared to the LIFETIME value. If the result is greater than the LIFETIME value, then the row is a candidate for aging.

The values of the column used for aging are updated by your applications. If the value of this column is unknown for some rows, and you do not want the rows to be aged, define the column with a large default value (the column cannot be NULL).

You can define your aging column with a data type of TT_TIMESTAMP or TT_DATE. If you choose data type TT_DATE, then you must specify the LIFETIME unit as days.

Specify the LIFETIME clause after the ADD AGING USEColumnName clause if you are adding the time-based aging policy to an existing table. Specify the LIFETIME clause after the SET AGING clause to change the LIFETIME setting.

The LIFETIME clause specifies the minimum amount of time data is kept in cache.

Specify Num1 as a positive integer constant to indicate the unit of time expressed in seconds, minutes, hours or days that rows should be kept in cache. Rows that exceed the LIFETIME value are aged out (deleted from the table). If you define your aging column with data type TT_DATE, then you must specify DAYS as the LIFETIME unit.

The concept of time resolution is supported. If DAYS is specified as the time resolution, then all rows whose timestamp belongs to the same day are aged out at the same time. If HOURS is specified as the time resolution, then all rows with timestamp values within that hour are aged at the same time. A LIFETIME of 3 days is different than a LIFETIME of 72 hours (3*24) or a LIFETIME of 432 minutes (3*24*60).

CYCLENum2{SECOND[S]| MINUTE[S]| HOUR[S]|DAY[S]}

Specify the optional CYCLE clause after the LIFETIME clause if you are adding the time-based aging policy to an existing table.

CYCLE is a time-based aging attribute.

The CYCLE clause indicates how often the system should examine rows to see if data exceeds the specified LIFETIME value and should be aged out (deleted).

Specify Num2 as a positive integer constant.

If you do not specify the CYCLE clause, then the default value is 5 minutes. If you specify 0 for Num2, then the aging thread wakes up every second.

If the aging state is OFF, then aging is not done automatically and the CYCLE clause is ignored.

Specify the CYCLE clause after the SET AGING clause to change the CYCLE setting.

SET AGING {ON|OFF}

Changes the aging state. The aging policy must be previously defined. ON enables automatic aging. OFF disables automatic aging. If you want to control aging with an external scheduler, then disable aging and invoke the ttAgingScheduleNow built-in procedure.

DROP AGING

Drops the aging policy from the table. After you define an aging policy, you cannot alter it. Drop aging, then redefine.

SET AGING LIFETIMENum1{SECOND[S]| MINUTE[S]|HOUR[S] |DAY[S]}

Use this clause to change the lifetime for time-based aging.

Num1 must be a positive integer constant.

If you defined your aging column with data type TT_DATE, then you must specify DAYS as the LIFETIME unit.

SET AGING CYCLENum2{SECOND[S]| MINUTE[S]| HOUR[S]|DAY[S]}

Use this clause to change the cycle for time-based aging.

Num2 must be a positive integer constant.

Description

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

The new columns cannot be declared NOT NULL.

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-noRepUpgrade command, and restoring the table using the ttRestore -r-noRepUpgrade 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.

An error is generated if a table has no primary key and either the USE HASH INDEX clause or the USE TREE 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.

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 ttIsqlINDEXES command to show that the primary key constraint c1 is created and a range index is used:

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.

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.

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.

Specifies the password that identifies the internal user to the TimesTen database.

EXTERNALLY

Identifies the operating system user to the TimesTen database. To perform database operations as an external user, the process needs a TimesTen external user name that matches the user name authenticated by the operating system or network. A password is not required by TimesTen because the user has been authenticated by the operating system at login time.

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.

Examples

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

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:

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 allows the following optional keyword:

Parameter

Description

[WORK]

Optional clause supported for compliance with the SQL standard. COMMIT and COMMIT WORK are equivalent.

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:

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.

For example, if the database path is directory/subdirectory/data.ds0, then data is the database name that should be used.

This is the database file name specified in the DataStore attribute of the DSN description with optional host ID in the form:

DataStoreName[ONHost]

Host can be either an IP address or a literal host name assigned to one or more IP addresses, as described in "Configuring host IP addresses" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. Host names containing special characters must be surrounded by double quotes. For example: "MyHost-500".

RETURN RECEIPT [BY REQUEST]

Enables the return receipt service, so that applications that commit a transaction to an active master database are blocked until the transaction is received by the standby master database.

Specifying RETURN RECEIPT applies the service to all transactions. If you specify RETURN REQUEST BY REQUEST, you can use the ttRepSyncSet procedure to enable the return receipt service for selected transactions. For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

RETURN TWOSAFE [BY REQUEST]

Enables the return twosafe service, so that applications that commit a transaction to an active master database are blocked until the transaction is committed on the standby master database.

Specifying RETURN TWOSAFE applies the service to all transactions. If you specify RETURN TWOSAFE BY REQUEST, you can use the ttRepSyncSet procedure to enable the return receipt service for selected transactions.

For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

DISABLE RETURN {SUBSCRIBER | ALL}NumFailures

Set the return service failure policy so that return service blocking is disabled after the number of timeouts specified by NumFailures.

Specifying SUBSCRIBER is the same as specifying ALL. Both settings refer to the standby master database.

This failure policy can be specified for either the RETURN RECEIPT or RETURN TWOSAFE service.

If DISABLE RETURN has disabled return service blocking, this attribute sets the policy for when to re-enable the return service.

NO RETURN

Specifies that no return service is to be used. This is the default.

For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

RETURN WAIT TIMESeconds

Specifies the number of seconds to wait for return service acknowledgement. A value of 0 means that there is no waiting. The default value is 10 seconds.

The application can override this timeout setting by using the returnWait parameter in the ttRepSyncSet built-in procedure.

SUBSCRIBERFullStoreName[,...]]

A database that receives updates from a master database. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

STOREFullStoreName[StoreAttribute[...]]

Defines the attributes for the specified database. Attributes include PORT, TIMEOUT and FAILTHRESHOLD. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.

{INCLUDE | EXCLUDE}

{TABLE [[Owner.]TableName[,...]]|

CACHE GROUP

[[Owner.]CacheGroupName

[,...]]|

SEQUENCE

[[Owner.]SequenceName

[,...]]}

[,...]

An active standby pair replicates an entire database by default.

INCLUDE includes only the listed tables, sequences or cache groups to replication. Use one INCLUDE clause for each object type (table, sequence or cache group).

EXCLUDE removes tables, sequences or cache groups from the replication scheme. Use one EXCLUDE clause for each object type (table, sequence or cache group).

DURABLE COMMIT {ON | OFF}

Set to override the DurableCommits setting on a database and enable durable commit when return service blocking has been disabled by DISABLE RETURN.

FAILTHRESHOLDValue

The number of log files that can accumulate for a subscriber database. If this value is exceeded, the subscriber is set to the Failed state.The value 0 means "No Limit." This is the default.

Specifies the default action to be taken for a return twosafe transaction in the event of a timeout.

Note: This attribute is valid only when the RETURN TWOSAFE or RETURN TWOSAFE BY REQUEST attribute is set in the SUBSCRIBER clause.

NO ACTION: On timeout, the commit function returns to the application, leaving the transaction in the same state it was in when it entered the commit call, with the exception that the application is not able to update any replicated tables. The application can reissue the commit or rollback the call. This is the default.

COMMIT: On timeout, the commit function attempts to perform a COMMIT to end the transaction locally. No more operations are possible on the same transaction.

This setting can be overridden for specific transactions by calling the localAction parameter in the ttRepSyncSet procedure.

MASTERFullStoreName

The database on which applications update the specified element. The MASTER database sends updates to its SUBSCRIBER databases. The FullStoreName must be the database specified in the DataStore attribute of the DSN description.

PORTPortNumber

The TCP/IP port number on which the replication agent for the database listens for connections. If not specified, the replication agent automatically allocates a port number.

In an active standby pair, the standby master database listens for updates from the active master database. Read-only subscribers listen for updates from the standby master database.

ROUTE MASTERFullStoreNameSUBSCRIBERFullStoreName

Denotes the NetworkOperation clause. If specified, allows you to control the network interface that a master store uses for every outbound connection to each of its subscriber stores. In the context of the ROUTE clause, each master database is a subscriber of the other master database and each read-only subscriber is a subscriber of both master databases.

Can be specified more than once.

For FullStoreName, ON"host" must be specified.

MASTERIPMasterHost|SUBSCRIBERIPSubscriberHost

MasterHost and SubscriberHost are the IP addresses for the network interface on the master and subscriber stores. Specify in dot notation or canonical format or in colon notation for IPV6.

Clause can be specified more than once.

PRIORITYPriority

Variable expressed as an integer from 1 to 99. Denotes the priority of the IP address. Lower integral values have higher priority. An error is returned if multiple addresses with the same priority are specified. Controls the order in which multiple IP addresses are used to establish peer connections.

Set the maximum number of seconds a database waits before re-sending a message to an unresponsive database.

In an active standby pair, the active master database sends messages to the standby master database. The standby master database sends messages to the read-only subscribers.

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 TimesTen to TimesTen 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:

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 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:

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.

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.

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.

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

Parameter

Description

[Owner.]GroupName

Owner and name assigned to the new cache group.

[DYNAMIC]

If specified, a dynamic cache group is created.

AUTOREFRESH

The AUTOREFRESH parameter automatically propagates changes from the Oracle database to the cache group. For details, see "AUTOREFRESH in cache groups".

MODE [INCREMENTAL | FULL]

Determines which rows in the cache are updated during an autorefresh. If the INCREMENTAL clause is specified, TimesTen refreshes only rows that have been changed on Oracle since the last propagation. If the FULL clause is specified, TimesTen updates all rows in the cache with each autorefresh. The default autorefresh mode is INCREMENTAL.

INTERVALIntervalValue

Indicates the interval at which autorefresh should occur in units of minutes, seconds or milliseconds. IntervalValue is an integer value that specifies how often autorefresh should be scheduled, in MINUTES, SECONDS or MILLISECONDS. The default IntervalValue value is 5 minutes. If the specified interval is not long enough for an autorefresh to complete, a runtime warning is generated and the next autorefresh waits until the current one finishes. An informational message is generated in the support log if the wait queue reaches 10.

STATE [ON | OFF | PAUSED]

Specifies whether autorefresh should be ON or OFF or PAUSED when the cache group is created. You can alter this setting later by using the ALTER CACHE GROUP statement. By default, the AUTOREFRESH state is PAUSED.

FROM

Designates one or more table definitions for the cache group.

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:

Parameter

Description

[Owner.]TableName

Owner and name to be assigned to the new table. If you do not specify the owner name, your login becomes the owner name for the new table.

ColumnDefinition

Name of an individual column in a table, its data type and whether or not it is nullable. Each table must have at least one column. See "Column Definition".

PRIMARY KEY(ColumnName[,...])

Specifies that the table has a primary key. Primary key constraints are required for a cache group. ColumnName is the name of the column that forms the primary key for the table to be created. Up to 16 columns can be specified for the primary key. Cannot be specified with UNIQUE in one specification.

FOREIGN KEY(ColumnName[,...])

Specifies that the table has a foreign key. ColumnName is the name of the column that forms the foreign key for the table to be created. See "FOREIGN KEY".

REFERENCESRefTableName(ColumnName[,...])

Specifies the table which the foreign key is associated with. RefTableName is the name of the referenced table and ColumnName is the name of the column referenced in the table.

[ON DELETECASCADE]

Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.

READONLY

Specifies that changes cannot be made on the cached table.

PROPAGATE|NOT PROPAGATE

Specifies whether changes to the cached table are automatically propagate to the corresponding Oracle table at commit time.

UNIQUE HASH ON(HashColumnName)

Specifies that a hash index is created on this table. HashColumnName identifies the column that is to participate in the hash key of this table. The columns specified in the hash index must be identical to the columns in the primary key.

PAGES=PrimaryPages

Specifies the expected number of pages in the table. The PrimaryPages number determines the number of hash buckets created for the hash index. The minimum is 1. If your estimate is too small, performance is degraded. See "CREATE TABLE" for more information.

WHEREExternalSearchCondition

The WHERE clause evaluated by Oracle for the cache group table. This WHERE clause is added to every LOAD and REFRESH operation on the cache group. It may not directly reference other tables. It is parsed by both TimesTen and Oracle. See "Using a WHERE clause" in Oracle In-Memory Database Cache User's Guide.

AGING LRU [ON | OFF]

If specified, defines the LRU aging policy on the root table. The LRU aging policy applies to all tables in the cache group. The LRU aging policy defines the type of aging (least recently used (LRU)), the aging state (ON or OFF) and the LRU aging attributes.

Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON.

In dynamic cache groups, LRU aging is set ON by default. You can specify time-based aging instead. Aging is disabled by default on an explicitly loaded global cache group.

LRU aging cannot be specified on a cache group with the autorefresh attribute, unless the cache group is dynamic.

LRU attributes are defined by calling the ttAgingLRUConfig procedure. LRU attributes are not defined at the SQL level.

If specified, defines the time-based aging policy on the root table. The time-based aging policy applies to all tables in the cache group. The time-based aging policy defines the type of aging (time-based), the aging state (ON or OFF) and the time-based aging attributes.

Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON.

Time-based aging attributes are defined at the SQL level and are specified by the LIFETIME and CYCLE clauses.

Specify ColumnName as the name of the column used for time-based aging. Define the column as NOT NULL and of data type TIMESTAMP or DATE. The value of this column is subtracted from SYSDATE, truncated using the specified unit (second, minute, hour, day) and then compared to the LIFETIME value. If the result is greater than the LIFETIME value, then the row is a candidate for aging.

The values of the column used for aging are updated by your applications. If the value of this column is unknown for some rows, and you do not want the rows to be aged, define the column with a large default value (the column cannot be NULL).

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

The LIFETIME clause specifies the minimum amount of time data is kept in cache.

Specify Num1 as a positive integer constant to indicate the unit of time expressed in seconds, minutes, hours or days that rows should be kept in cache. Rows that exceed the LIFETIME value are aged out (deleted from the table).

The concept of time resolution is supported. If DAYS is specified as the time resolution, then all rows whose timestamp belongs to the same day are aged out at the same time. If HOURS is specified as the time resolution, then all rows with timestamp values within that hour are aged at the same time. A LIFETIME of 3 days is different than a LIFETIME of 72 hours (3*24) or a LIFETIME of 432 minutes (3*24*60).

[CYCLENum2{SECOND[S] | MINUTE[S] |HOUR[S]|DAY[S]}]

CYCLE is a time-based aging attribute and is optional. Specify the CYCLE clause after the LIFETIME clause.

The CYCLE clause indicates how often the system should examine rows to see if data exceeds the specified LIFETIME value and should be aged out (deleted).

Specify Num2 as a positive integer constant.

If you do not specify the CYCLE clause, then the default value is 5 minutes. If you specify 0 for Num2, then the aging thread wakes up every second.

If the aging state is OFF, then aging is not done automatically and the CYCLE clause is ignored.

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.

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.

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

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 "Cache grid" and "Global cache group" in Oracle In-Memory Database Cache User's Guide.

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":

Specify OR REPLACE to re-create the function if it already exists. Use this clause to change the definition of an existing function without dropping and re-creating it. When you re-create a function, TimesTen recompiles it.

FunctionName

Name of function.

arguments

Name of argument or parameter. You can specify 0 or more parameters for the function. If you specify a parameter, you must specify a data type for the parameter. The data type must be a PL/SQL data type.

IN is a read-only parameter. You can pass the parameter's value into the function but the function cannot pass the parameter's value out of the function and back to the calling PL/SQL block.The value of the parameter cannot be changed.

OUT is a write-only parameter. Use an OUT parameter to pass a value back from the function to the calling PL/SQL block. You can assign a value to the parameter.

IN OUT is a read/write parameter. You can pass values into the function and return a value back to the calling program (either the original, unchanged value or a new value set within the function.

IN is the default.

NOCOPY

Specify NOCOPY to instruct TimesTen to pass the parameter as fast as possible. You can enhance performance when passing a large value such as a record, an index-by-table, or a varray to an OUT or IN OUT parameter. IN parameters are always passed NOCOPY.

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

call_spec clause

AS EXTERNAL

The CREATE FUNCTION statement is not replicated.

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.

Prohibits duplicates in the index. If UNIQUE is specified, each possible combination of index key column values can occur in only one row of the table. If UNIQUE is omitted, duplicate values are allowed. When you create a unique index, all existing rows must have unique values in the indexed columns. If you specify UNIQUE, TimesTen creates a range index.

A range index:

Speeds up range searches (but can also be used for efficient equality searches)

Is optimized for in-memory data management

Provides efficient sorting by column value

BITMAP

Specify CREATE BITMAP INDEX to create an index where the information about rows with each unique value is encoded in a bitmap. Each bit in the bitmap corresponds to a row in the table.

Use a bitmap index for columns that do not have many unique values.

[Owner.]IndexName

Name to be assigned to the new index. A table cannot have two indexes with the same name. If the owner is specified, it must be the same as the owner of the table.

[Owner.]TableName

Designates the table or materialized view for which an index is to be created.

ColumnName

Name of a column to be used as an index key. You can specify up to 16 columns in order from major index key to minor index key.

[ASC|DESC]

Specifies the order of the index to be either ascending (the default) or descending. In TimesTen, this parameter is currently ignored.

Description

If you do not specify UNIQUE or BITMAP, TimesTen creates a range 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.

TheCREATE 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 TimesTen to TimesTen Replication Guide for more information.

Examples

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

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.

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

If NEXT SYSDATE is specified without NUMTODSINTERVAL, the materialized view is refreshed incrementally every time a detail table is modified. The refresh occurs in a separate transaction immediately after the transaction that modifies the detail table has been committed. You cannot specify a full refresh (COMPLETE) every time a detail table is modified.

If NEXT SYSDATE is omitted, then the materialized view will not be refreshed automatically. It must be refreshed manually.

If NEXT SYSDATE is provided without FAST or COMPLETE specified, COMPLETE is the default refresh method.

[+NUMTODSINTERVAL(IntegerLiteral,
IntervalUnit)]

If specified, the materialized view is refreshed at the specified interval. IntegerLiteral must be an integer. IntervalUnit must be one of the following values: 'DAY', 'HOUR', 'MINUTE', 'SECOND'.

If [NEXT SYSDATE[+NUMTODSINTERVAL(IntegerLiteral),IntervalUnit] is not specified, the materialized view is not refreshed automatically. You can manually refresh the view by using the REFRESH MATERIALIZED VIEW statement.

SelectQuery

Select column from the detail tables to be used in the view.

ColumnName

Name of the column(s) that forms the primary key for the view to be created. Up to 16 columns can be specified for the primary key. Each result column name of a viewed table must be unique. The column name definition cannot contain the table or owner component.

UNIQUE HASH ON

Hash index for the table. Only unique hash indexes are created. This parameter is used for equality predicates. UNIQUE HASH ON requires that a primary key be defined.

HashColumnName

Column defined in the view that is to participate in the hash key of this table. The columns specified in the hash index must be identical to the columns in the primary key.

PrimaryPages

Specifies the expected number of pages in the table. This number determines the number of hash buckets created for the hash index. The minimum is 1. If your estimate is too small, performance is degraded. See "CREATE TABLE" section for more information.

Description

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

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.

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

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

List of columns for which changes will be recorded in the log. You cannot include the primary key columns in the column list when you specify the PRIMARY KEY option.

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.

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.

Specify OR REPLACE to re-create the package specification if it already exists. Use this clause to change the specification of an existing package without dropping and recreating the package. When you change a package specification, TimesTen recompiles it.

PackageName

Name of the package.

invoker_rights_clause

Lets you specify whether the package executes with the privileges and in the database of the user who owns it or with the privileges and in the database of the CURRENT_USER.

Specify CURRENT_USER to indicate that the package executes with the privileges of the CURRENT_USER.

Specify DEFINER to indicate that the package executes with the privileges of the owner of the database in which the package resides.

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.

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.

Specify OR REPLACE to re-create the package body if it already exists. Use this clause to change the body of an existing package without dropping and recreating it. When you change a package body, TimesTen recompiles it.

PackageBody

Name of the package body.

IS|AS

Specify either IS or AS to declare the body of the function.

plsql_package_body

Specifies the package body which consists of PL/SQL subprograms.

Description

The CREATE PACKAGE BODY statement is not replicated.

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.

Specify OR REPLACE to re-create the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping and recreating it. When you re-create a procedure, TimesTen recompiles it.

ProcedureName

Name of procedure.

arguments

Name of argument/parameter. You can specify 0 or more parameters for the procedure. If you specify a parameter, you must specify a data type for the parameter. The data type must be a PL/SQL data type.

IN is a read-only parameter. You can pass the parameter's value into the procedure but the procedure cannot pass the parameter's value out of the procedure and back to the calling PL/SQL block.The value of the parameter cannot be changed.

OUT is a write-only parameter. Use an OUT parameter to pass a value back from the procedure to the calling PL/SQL block. You can assign a value to the parameter.

IN OUT is a read/write parameter. You can pass values into the procedure and return a value back to the calling program (either the original, unchanged value or a new value set within the procedure.

IN is the default.

NOCOPY

Specify NOCOPY to instruct TimesTen to pass the parameter as fast as possible. Can enhance performance when passing a large value such as a record, an index-by-table, or a varray to an OUT or IN OUT parameter. IN parameters are always passed NOCOPY.

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.

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.

Compress replicated traffic to reduce the amount of network bandwidth. ON specifies that all replicated traffic for the database defined by STORE be compressed. OFF (the default) specifies no compression. See "Compressing replicated traffic" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for details.

CONFLICT REPORTING SUSPEND ATValue

Suspends conflict resolution reporting.

Value is a non-negative integer. The default is 0 and means never suspend. Conflict reporting is suspended when the rate of conflict exceeds Value. If you set Value to 0, conflict reporting suspension is turned off.

Use this clause for table-level replication.

CONFLICT REPORTING RESUME ATValue

Resumes conflict resolution reporting.

Value is a non-negative integer. Conflict reporting is resumed when the rate of conflict falls below Value. The default is 1.

Use this clause for table level replication.

DATASTORE

Define entire database as element. This type of element can only be defined for a master database that is not configured with an element of type TABLE in the same or a different replication scheme. See "Defining replication elements" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

{INCLUDE|EXCLUDE}

{[TABLE[Owner.]TableName[,...]]|

CACHE GROUP

[[Owner.]CacheGroupName [,...]]|

SEQUENCE

[[Owner.]SequenceName[,...]]} [,...]

INCLUDE includes in the DATASTORE element only the tables, sequences or cache groups listed. Use one INCLUDE clause for each object type (table, sequence or cache group).

EXCLUDE includes in the DATASTORE element all tables, sequences or cache groups except for those listed. Use one EXCLUDE clause for each object type (table, sequence or cache group).

DISABLE RETURN {SUBSCRIBER|ALL}NumFailures

Set the return service failure policy so that return service blocking is disabled after the number of timeouts specified by NumFailures. Selecting SUBSCRIBER applies this policy only to the subscriber that fails to acknowledge replicated updates within the set timeout period. ALL applies this policy to all subscribers should any of the subscribers fail to respond. This failure policy can be specified for either the RETURN RECEIPT or RETURN TWOSAFE service.

If DISABLE RETURN is specified but RESUME RETURN is not specified, the return services remain off until the replication agent for the database has been restarted.

Set to override the DurableCommits setting on a database and enable durable commit when return service blocking has been disabled by DISABLE RETURN.

ELEMENTElementName

The entity that TimesTen synchronizes between databases. TimesTen supports the entire database (DATASTORE) and whole tables (TABLE) as replication elements.

ElementName is the name given to the replication element. The ElementName for a TABLE element can be up to 30 characters in length. The ElementName for a DATASTORE element must be unique with respect to other DATASTORE element names within the first 20 chars. Each ElementName must be unique within a replication scheme. Also, you cannot define two element descriptions for the same element.

For example, if the database path is directory/subdirectory/data.ds0, then data is the database name that should be used.

This is the database file name specified in the DataStore attribute of the DSN description with optional host ID in the form:

DataStoreName[ONHost]

Host can be either an IP address or a literal host name assigned to one or more IP addresses, as described in "Configuring host IP addresses" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. Host names containing special characters must be surrounded by double quotes. For example: "MyHost-500". Host names can be up to 30 characters long.

LOCAL COMMIT ACTION{NO ACTION|COMMIT}

Specifies the default action to be taken for a return twosafe transaction in the event of a timeout.

Note: This attribute is only valid when the RETURN TWOSAFE or RETURN TWOSAFE BY REQUEST attribute is set in the SUBSCRIBER clause.

NO ACTION: On timeout, the commit function returns to the application, leaving the transaction in the same state it was in when it entered the commit call, with the exception that the application is not able to update any replicated tables. The application can reissue the commit or rollback the call. This is the default.

COMMIT: On timeout, the commit function attempts to perform a COMMIT to end the transaction locally. No more operations are possible on the same transaction.

This setting can be overridden for specific transactions by calling the localAction parameter in the ttRepSyncSet procedure.

MASTERFullStoreName

The database on which applications update the specified element. The MASTER database sends updates to its SUBSCRIBER databases. The FullStoreName must be the database specified in the DataStore attribute of the DSN description.

NO RETURN

Specifies that no return service is to be used. This is the default.

For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

PORTPortNumber

The TCP/IP port number on which the replication agent for the database listens for connections. If not specified, the replication agent automatically allocates a port number.

PROPAGATORFullStoreName

The database that receives replicated updates and passes them on to other databases. The FullStoreName must be the database specified in the DataStore attribute of the DSN description.

RESUME RETURNMilliSeconds

If return service blocking has been disabled by DISABLE RETURN, this attribute sets the policy on when to re-enable return service blocking. Return service blocking is re-enabled as soon as the failed subscriber acknowledges the replicated update in a period of time that is less than the specified MilliSeconds.

If DISABLE RETURN is specified but RESUME RETURN is not specified, the return services remain off until the replication agent for the database has been restarted.

RETURN RECEIPT[BY REQUEST]

Enables the return receipt service, so that applications that commit a transaction to a master database are blocked until the transaction is received by all subscribers.

RETURN RECEIPT applies the service to all transactions. If you specify RETURN REQUEST BY REQUEST, you can use the ttRepSyncSet procedure to enable the return receipt service for selected transactions. For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide

RETURN SERVICES {ON|OFF} WHEN [REPLICATION] STOPPED

Set the return service failure policy so that return service blocking is either unchanged or disabled when the replication agent is in the Stop or Pause state.

OFF is the default when using the return receipt service. ON is the default when using the return twosafe service

Enables the return twosafe service, so that applications that commit a transaction to a master database are blocked until the transaction is committed on all subscribers.

Note: This service can only be used in a bidirectional replication scheme where the elements are defined as DATASTORE.

Specifying RETURN TWOSAFE applies the service to all transactions. If you specify RETURN TWOSAFE BY REQUEST, you can use the ttRepSyncSet procedure to enable the return receipt service for selected transactions. For details on the use of the return services, see "Using a return service" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

RETURN WAIT TIMESeconds

Specifies the number of seconds to wait for return service acknowledgement. The default value is 10 seconds. A value of '0' means that there is no timeout. Your application can override this timeout setting by calling the returnWait parameter in the ttRepSyncSet procedure.

The amount of time a database waits for a response from another database before resending the message. Default: 120 seconds.

TRANSMIT {DURABLE | NONDURABLE}

Specifies whether to flush the master log to disk before sending a batch of committed transactions to the subscribers.

TRANSMIT NONDURABLE specifies that records in the master log are not to be flushed to disk before they are sent to subscribers. This setting can only be used if the specified element is a DATASTORE. This is the default for RETURN TWOSAFE transactions.

TRANSMIT DURABLE specifies that records are to be flushed to disk before they are sent to subscribers. This is the default for asynchronous and RETURN RECEIPT transactions.

Specifies type of table definition checking that occurs on the subscriber:

EXACT - The tables must be identical on master and subscriber.

RELAXED - The tables must have the same key definition, number of columns and column data types.

The default is EXACT.

ROUTE MASTERFullStoreNameSUBSCRIBERFullStoreName

Denotes the NetworkOperation clause. If specified, allows you to control the network interface that a master store uses for every outbound connection to each of its subscriber stores.

Can be specified more than once.

For FullStoreName, ON "host" must be specified.

MASTERIPMasterHost|SUBSCRIBERIPSubscriberHost

MasterHost and SubscriberHost are the IP addresses for the network interface on the master and subscriber stores. Specify in dot notation or canonical format or in colon notation for IPV6.

Clause can be specified more than once.

PRIORITYPriority

Variable expressed as an integer from 1 to 99. Denotes the priority of the IP address. Lower integral values have higher priority. An error is returned if multiple addresses with the same priority are specified. Controls the order in which multiple IP addresses are used to establish peer connections.

Indicates that all update and uniqueness conflicts are to be detected. Conflicts are resolved in the manner specified by the ON EXCEPTION parameter.

It also detects delete conflicts with UPDATE operations.

COLUMNColumnName

Indicates the column in the replicated table to be used for timestamp comparison. The table is specified in the ELEMENT description by TableName.

ColumnName is a nullable column of type BINARY(8) used to store a timestamp that indicates when the row was last updated. TimesTen rejects attempts to update a row with a lower timestamp value than the stored value. The specified ColumnName must exist in the replicated table on both the master and subscriber databases.

Specifies how to resolve a detected conflict. ROW TIMESTAMP conflict detection has the resolution options:

ROLLBACK [WORK]: Abort the transaction that contains the conflicting action.

NO ACTION: Complete the transaction without performing the conflicting action (UPDATE, INSERT or DELETE).

Default is ON EXCEPTION ROLLBACK [WORK].

REPORT TO 'FileName'

Specifies the file to log updates that fail the timestamp comparison. FileName is a SQL character string that cannot exceed 1,000 characters. (SQL character string literals are single-quoted strings that may contain any sequence of characters, including spaces.) The same file can be used to log failed updates for multiple tables.

[FORMAT {XML|STANDARD}]

Optionally specifies the conflict report format for an element. The default format is STANDARD.

NO REPORT

Specify to suppress logging of failed timestamp comparisons.

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 TimesTen to TimesTen 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 allows 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 TimesTen to TimesTen 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.

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.

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.

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

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 COMMITACTION, 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 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.

The incremental value between consecutive numbers. This value can be either a positive or negative integer. It cannot be 0. If the value is positive, it is an ascending sequence. If the value is negative, it is descending. The default value is 1. In a descending sequence, the range starts from MAXVALUE to MINVALUE, and vice versa for ascending sequence.

MINVALUEMinimumValue

Specifies the minimum value for the sequence. The default minimum value is 1.

MAXVALUEMaximumValue

The largest possible value for an ascending sequence, or the starting value for a descending sequence. The default maximum value is (263) -1, which is the maximum of BIGINT.

CYCLE

Indicates that the sequence number generator continues to generate numbers after it reaches the maximum or minimum value. By default, sequences do not cycle. Once the number reaches the maximum value in the ascending sequence, the sequence wraps around and generates numbers from its minimum value. For a descending sequence, when the minimum value is reached, the sequence number wraps around, beginning from the maximum value. If CYCLE is not specified, the sequence number generator stops generating numbers when the maximum/minimum is reached and TimesTen returns an error.

CACHECacheValue

CACHE indicates the range of numbers that are cached each time. When a restart occurs, unused cached numbers are lost. If you specify a CacheValue of 1, then each use of the sequence results in an update to the database. Larger cache values result in fewer changes to the database and less overhead. The default is 20.

START WITHStartValue

Specifies the first sequence number to be generated. Use this clause to start an ascending sequence at a value that is greater than the minimum value or to start a descending sequence at a value less than the maximum. The StartValue must be greater or equal MinimumValue and StartValue must be less than or equal to MaximumValue.

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.

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.

NEXTVAL and CURRVAL can be used in:

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

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

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.

In the case of recovery, sequences are not rolled back. It is possible that the range of values of a sequence can have gaps. Each sequence value is still unique.

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.

Note:

Sequences with the CYCLE attribute cannot be replicated.

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

Specify OR REPLACE to re-create the synonym if it already exists. Use this clause to change the definition of an existing synonym without first dropping it.

[PUBLIC]

Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users, but each user must have appropriate privileges on the underlying object in order to use the synonym.

When resolving references to an object, TimesTen uses a public synonym only if the object is not prefaced by a schema name.

[owner1.]synonym

Specify the owner of the synonym. You cannot specify an owner for the synonym if you have specified PUBLIC. If you omit both PUBLIC and owner1, TimesTen creates the synonym in your own schema.

Specify the name for the synonym, which is limited to 30 bytes.

[owner2.]object

Specify the owner in which the object resides. Specify the object name for which you are creating a synonym. If you do not qualify object with owner2, the object is in your own schema. The owner2 and object do not need to exist when the synonym is created.

Description

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

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:

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

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

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

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 TimesTen to TimesTen 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.

CREATE TABLE

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.

Name to be assigned to the new table. Two tables cannot have the same owner name and table name.

If you do not specify the owner name, your login name becomes the owner name for the new table. Owners of tables in TimesTen are determined by the user ID settings or login names. Oracle table owner names must always match TimesTen table owner names.

Specifies that the table being created is a global temporary table. A temporary table is similar to a persistent table but it is effectively materialized only when referenced in a connection.

A global temporary table definition is persistent and is visible to all connections, but the table instance is local to each connection. It is created when a command referencing the table is compiled for a connection and dropped when the connection is disconnected. All instances of the same temporary table have the same name but they are identified by an additional connection ID together with the table name. Global temporary tables are allocated in temp space.

The contents of a global temporary table cannot be shared between connections. Each connection sees only its own content of the table and compiled commands that reference temporary tables are not shared among connections.

When DDL_REPLICATION_LEVEL=2, the creation of a global temporary table is replicated in an active standby pair, but the global temporary table is not included in the replication scheme.

Temporary tables are automatically excluded from active standby pairs or when the DATASTORE element has been specified.

A cache group table cannot be defined as a temporary table.

Changes to temporary tables cannot be tracked with XLA.

Operations on temporary tables do generate log records. The amount of log they generate is less than for permanent tables.

Truncate table is not supported with global temporary tables.

Local temporary tables are not supported.

No object privileges are needed to access global temporary tables.

Do not specify the ASSelectQuery clause with global temporary tables.

ColumnDefinition

An individual column in a table. Each table must have at least one column. See "Column Definition".

If you specify the ASSelectQuery clause, ColumnDefinition is optional.

ColumnName

Names of the columns that form the primary key for the table to be created. Up to 16 columns can be specified for the primary key. For a foreign key, the ColumnName is optional. If not specified for a foreign key, the reference is to the parent table's primary key.

If you specify the ASSelectQuery clause, then you do not have to specify the ColumnName. Do not specify the data type with the ASSelectQuery clause.

PRIMARY KEY

PRIMARY KEY may only be specified once in a table definition. It provides a way of identifying one or more columns that, together, form the primary key of the table. The contents of the primary key have to be unique and NOT NULL. You cannot specify a column as both UNIQUE and a single column PRIMARY KEY.

CONSTRAINTForeignKeyName

Specifies an optional user-defined name for a foreign key. If not provided by the user, the system provides a default name.

FOREIGN KEY

This specifies a foreign key constraint between the new table and the referenced table identified by RefTableName. There are two lists of columns specified in the foreign key constraint.

Columns in the first list are columns of the new table and are called the referencing columns. Columns in the second list are columns of the referenced table and are called referenced columns. These two lists must match in data type, including length, precision and scale. The referenced table must already have a primary key or unique index on the referenced column.

The column name list of referenced columns is optional. If omitted, the primary index of RefTableName is used.

The declaration of a foreign key creates a range index on the referencing columns. The user cannot drop the referenced table or its referenced index until the referencing table is dropped.

The foreign key constraint asserts that each row in the new table must match a row in the referenced table such that the contents of the referencing columns are equal to the contents of the referenced columns. Any INSERT, DELETE or UPDATE statements that violate the constraint return TimesTen error 3001.

A foreign key can be defined on a global temporary table, but it can only reference a global temporary table. If a parent table is defined with COMMIT DELETE, the child table must also have the COMMIT DELETE attribute.

A foreign key cannot reference an active parent table. An active parent table is one that has some instance materialized for a connection.

If you specify the ASSelectQuery clause, you cannot define a foreign key on the table you are creating.

[ON DELETECASCADE]

Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.

UNIQUE

UNIQUE provides a way of identifying a column where each row must contain a unique value.

UNIQUE HASH ON

Hash index for the table. Only unique hash indexes are created. This parameter is used for equality predicates. UNIQUE HASH ON requires that a primary key be defined.

HashColumnName

Column defined in the table that is to participate in the hash key of this table. The columns specified in the hash index must be identical to the columns in the primary key.

If you specify the ASSelectQuery clause, you must define HashColumnName on the table you are creating.

PrimaryPages

Specifies the expected number of pages in the table. This number affects the number of buckets that are allocated for the table's hash index. The minimum is 1. If your estimate is too small, performance is degraded.

[ON COMMIT{DELETE|PRESERVE} ROWS]

The optional statement specifies whether to delete or preserve rows when a transaction that touches a global temporary table is committed. If not specified, the rows of the temporary table are deleted.

[AGING LRU [ON|OFF]]

If specified, defines the LRU aging policy for the table. The LRU aging policy defines the type of aging (least recently used (LRU)), the aging state (ON or OFF) and the LRU aging attributes.

Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON.

LRU attributes are defined by calling the ttAgingLRUConfig procedure. LRU attributes are not defined at the SQL level.

If specified, defines the time-based aging policy for the table. The time-based aging policy defines the type of aging (time-based), the aging state (ON or OFF) and the time-based aging attributes.

Set the aging state to either ON or OFF. ON indicates that the aging state is enabled and aging is done automatically. OFF indicates that the aging state is disabled and aging is not done automatically. In both cases, the aging policy is defined. The default is ON.

Time-based aging attributes are defined at the SQL level and are specified by the LIFETIME and CYCLE clauses.

Specify ColumnName as the name of the column used for time-based aging. Define the column as NOT NULL and of data type TIMESTAMP or DATE. The value of this column is subtracted from SYSDATE, truncated using the specified unit (second, minute, hour, day) and then compared to the LIFETIME value. If the result is greater than the LIFETIME value, then the row is a candidate for aging.

The values of the column that you use for aging are updated by your applications. If the value of this column is unknown for some rows, and you do not want the rows to be aged, define the column with a large default value (the column cannot be NULL).

You can define your aging column with a data type of TT_TIMESTAMP or TT_DATE. If you choose data type TT_DATE, then you must specify the LIFETIME unit as days.

If you specify the AS SelectQuery clause, you must define the ColumnName on the table you are creating.

The LIFETIME clause specifies the minimum amount of time data is kept in cache.

Specify Num1 as a positive integer constant to indicate the unit of time expressed in seconds, minutes, hours or days that rows should be kept in cache. Rows that exceed the LIFETIME value are aged out (deleted from the table). If you define your aging column with data type TT_DATE, then you must specify DAYS as the LIFETIME unit.

The concept of time resolution is supported. If DAYS is specified as the time resolution, then all rows whose timestamp belongs to the same day are aged out at the same time. If HOURS is specified as the time resolution, then all rows with timestamp values within that hour are aged at the same time. A LIFETIME of 3 days is different than a LIFETIME of 72 hours (3*24) or a LIFETIME of 432 minutes (3*24*60).

[CYCLENum2{SECOND[S] |MINUTE[S]|HOUR[S]|DAY[S]}]

CYCLE is a time-based aging attribute and is optional. Specify the CYCLE clause after the LIFETIME clause.

The CYCLE clause indicates how often the system should examine rows to see if data exceeds the specified LIFETIME value and should be aged out (deleted).

Specify Num2 as a positive integer constant.

If you do not specify the CYCLE clause, then the default value is 5 minutes. If you specify 0 for Num2, then the aging thread wakes up every second.

If the aging state is OFF, then aging is not done automatically and the CYCLE clause is ignored.

ASSelectQuery

If specified, creates a new table from the contents of the result set of the SelectQuery. The rows returned by SelectQuery are inserted into the table.

Data types and data type lengths are derived from SelectQuery.

SelectQuery is a valid SELECT statement that may or may not contain a subquery.

Name to be assigned to one of the columns in the new table. No two columns in the table can be given the same name. A table can have a maximum of 1000 columns.

If you specify the ASSelectQuery clause, ColumnName is optional. The number of column names must match the number of columns in SelectQuery.

DEFAULTDefaultVal

Indicates that if a value is not specified for the column in an INSERT statement, the default value DefaultVal is inserted into the column. The default value specified must have a compatible type with the column's data type. A default value can be as long as the data type of the associated column allows. Currently, you cannot assign a default value for the ROWID data type. In addition, you cannot assign a default value for columns in read-only cache groups.

If the default value is one of the users, the column's data type must be either CHAR or VARCHAR2 and the column's width must be at least 30 characters.

If you specify the ASSelectQuery clause, optionally, you can specify the DEFAULT clause on the table you are creating.

ColumnDataType

Type of data the column can contain. Some data types require that you indicate a length. See Chapter 1, "Data Types" for the data types that can be specified.

If you specify the ASSelectQuery clause, do not specify ColumnDataType.

INLINE|NOT INLINE

By default, variable-length columns whose declared column length is greater than 128 bytes are stored out of line. Variable-length columns whose declared column length is less than or equal to 128 bytes are stored inline. The default behavior can be overridden during table creation through the use of the INLINE and NOT INLINE keywords.

If you specify the ASSelectQuery clause, optionally, you can specify the INLINE | NOT INLINE clause on the table you are creating.

NULL

Indicates that the column can contain NULL values.

If you specify the ASSelectQuery clause, optionally, you can specify NULL on the table you are creating.

NOT NULL

Indicates that the column cannot contain NULL values. If NOT NULL is specified, any statement that attempts to place a NULL value in the column is rejected.

If you specify the ASSelectQuery clause, optionally, you can specify NOT NULL on the table you are creating.

UNIQUE

A unique constraint placed on the column. No two rows in the table may have the same value for this column. TimesTen creates a unique range index to enforce uniqueness. This means that a column with a unique constraint can use more memory and time during execution than a column without the constraint. Cannot be used with PRIMARY KEY.

If you specify the ASSelectQuery clause, optionally, you can specify UNIQUE on the table you are creating.

PRIMARY KEY

A unique NOT NULL constraint placed on the column. No two rows in the table may have the same value for this column. Cannot be used with UNIQUE.

If you specify the ASSelectQuery clause, optionally, you can specify PRIMARY KEY on the table you are creating.

Description

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

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.

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

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, derived from the value of PAGES, to the value 20.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.

All columns participating in the primary key are NOT NULL.

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

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.

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.

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

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 TABLEnewtable(newcolNULL) 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.

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.

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.

Use ASSelectQuery 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.

Use ASSelectQuery 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.

Internal users must be given a TimesTen password. To perform database operations using an internal user name, the user must supply this password.

EXTERNALLY

Identifies the operating system user to the TimesTen database. To perform database operations as an external user, the process needs a TimesTen external user name that matches the user name authenticated by the operating system or network. A password is not required by TimesTen because the user has been authenticated by the operating system at login time.

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.

Passwords are case-sensitive.

When a user is created, the user has the privileges granted to PUBLIC and no additional privileges.

You cannot create a user across a client/server connection. You must use a direct connection when creating a user.

In TimesTen, user brad is the same as user "brad". In both cases, the name of the user is created as BRAD.

CREATE VIEW

The CREATE VIEW statement creates a view of the tables specified in the SelectQuery clause. A view is a logical table that is based on one or more detail tables. The view itself contains no data. It is sometimes called a nonmaterialized view to distinguish it from a materialized view, which does contain data that has already been calculated from detail tables.

Required privilege

The user executing the statement must have the CREATE VIEW privilege (if owner) or CREATE ANY VIEW (if not the owner) for another user's view.

The owner of the view must have the SELECT privilege on the detail tables.

SQL syntax

CREATE VIEW ViewName AS SelectQuery

Parameters

Parameter

Description

ViewName

Name assigned to the new view.

SelectQuery

Selects column from the detail tables to be used in the view. Can also create indexes on the view.

Restrictions on the SELECT query

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

A SELECT * query in a view definition is expanded when the view is created. Any columns added after a view is created do not affect the view.

The following cannot be used in a SELECT statement that is used to create a view:

DISTINCT

FIRST

ORDER BY, if used, is ignored by the CREATE VIEW statement. The result will not be sorted.

Arguments

Temporary tables

Each expression in the select list must have a unique name. A name of a simple column expression would be 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 to create a view.

Certain TimesTen query restrictions are not checked when a nonmaterialized view is created. Views that violate those restrictions may be allowed to be created, but an error is returned when the view is referenced later in an executed statement.

When a view is referenced in the FROM clause of a SELECT statement, its name is replaced by its definition as a derived table at parsing time. If it is not possible to merge all clauses of a view to the same clause in the original select query to form a legal query without the derived table, the content of this derived table is materialized. For example, if both the view and the referencing select specify aggregates, the view is materialized before its result can be joined with other tables of the select.

Specifies the number of rows to delete. FIRSTNumRows is not supported in subquery statements. NumRows must be either a positive INTEGER or a dynamic parameter placeholder. The syntax for a dynamic parameter placeholder is either ? or :DynamicParameter. The value of the dynamic parameter is supplied when the statement is executed.

[Owner.]TableName [CorrelationName]

Designates a table from which any rows satisfying the search condition are to be deleted.

[Owner.]TableName identifies a table to be deleted.

CorrelationNamespecifies an alias for the immediately preceding table. Use the correlation name to reference the table elsewhere in the DELETE statement. The scope of the CorrelationName is the SQL statement in which it is used. It must conform to the syntax rules for a basic name. See "Basic names".

SearchCondition

Specifies which rows are to be deleted. If no rows satisfy the search condition, the table is not changed. If the WHERE clause is omitted, all rows are deleted. The search condition can contain a subquery.

If all the rows of a table are deleted, the table is empty but continues to exist until you issue a DROP TABLE statement.

The DELETE operation fails if it violates any foreign key constraint. See "CREATE TABLE" for a description of the foreign key constraint.

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.

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.

Restrictions on the RETURNING clause:

Each Expression must be a simple expression. Aggregate functions are not supported.

You cannot return a sequence number into an OUT parameter.

ROWNUM and subqueries cannot be used in the RETURNING clause.

Parameters in the RETURNING clause cannot be duplicated anywhere in the DELETE statement.

SQL ERROR (3001): Foreign key violation [TTFOREIGN_0] a row in child table T2 has a parent in the delete range.

Delete an employee from employees. Declare empid and name as variables with the same data types as employee_id and last_name. Delete the row, returning employee_id and last_name into the variables. Verify that the correct row was deleted.

DROP CACHE GROUP

The DROP CACHE GROUP statement drops the table associated with the cache group, and removes the cache group definition from the CACHE_GROUP system table.

Required privilege

No privilege is required for the cache group owner or DROP ANY CACHE GROUP if not the cache group owner and

DROP ANY TABLE if at least one table in the cache group is not owned by the current user.

SQL syntax

DROP CACHE GROUP [Owner.]GroupName

Parameters

Parameter

Description

[Owner.]GroupName

Name of the cache group to be deleted.

Description

If you attempt to delete a cache group table that is in use, TimesTen returns an error.

Asynchronous writethrough cache groups cannot be dropped while the replication agent is running.

Automatically installed Oracle objects for read-only cache groups and cache groups with the AUTOREFRESH attribute are uninstalled by the cache agent. If the cache agent is not running during the DROP CACHE GROUP operation, the Oracle objects are uninstalled on the next startup of the cache agent.

If you issue a DROP CACHE GROUP statement, and there is an autorefresh operation currently running, then:

If LockWait interval is 0, the DROP CACHE GROUP statement fails with a lock timeout error.

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

DROP FUNCTION

The DROP FUNCTION statement removes a standalone stored function from the database. Do not use this statement to remove a function that is part of a package.

Required privilege

No privilege is required for the function owner.

DROP ANY PROCEDURE for another user's function.

SQL syntax

DROP FUNCTION [Owner.]FunctionName

Parameters

Parameter

Description

[Owner.]FunctionName

Name of the function to be dropped.

Description

When you drop a function, TimesTen invalidates objects that depend on the dropped function. If you subsequently reference one of these objects, TimesTen attempts to recompile the object and returns an error message if you have not re-created the dropped function.

Do not use this statement to remove a function that is part of a package. Either drop the package or redefine the package without the function using the CREATE PACKAGE statement with the OR REPLACE clause

To use the DROP FUNCTION statement, you must have PL/SQL enabled in your database. If you do not have PL/SQL enabled in your database, an error is thrown.

Examples

The following statement drops the function myfunc and invalidates all objects that depend on myfunc:

Name of the index to be dropped. It may include the name of the owner of the table that has the index.

[Owner.]TableName

Name of the table upon which the index was created.

Description

If you attempt to drop a "busy" index—an index that is in use or that enforces a foreign key—an error results. To drop a foreign key and the index associated with it, use the ALTER TABLE statement.

If an index is created through a UNIQUE column constraint, it can only be dropped by dropping the constraint with an ALTER TABLEDROP UNIQUE statement. See "CREATE TABLE" for more information about the UNIQUE column constraint.

If a DROP INDEX operation is or was active in an uncommitted transaction, other transactions doing DML operations that do not access that index are blocked.

If an index is dropped, any prepared statement that uses the index is prepared again automatically the next time the statement is executed.

If no table name is specified, the index name must be unique for the specified or implicit owner. The implicit owner, in the absence of a specified table or owner, is the current user running the program.

If no index owner is specified and a table is specified, the default owner is the table owner.

If a table is specified and no owner is specified for it, the default table owner is the current user running the program.

The table and index owners must be the same.

An index on a temporary table cannot be dropped by a connection if some other connection has an instance of the table that is not empty.

If the index is replicated across an active standby pair and if DDL_REPLICATION_LEVEL is 2, use the DROP INDEX statement to drop the index from the standby pair in the replication scheme. See "Making DDL changes in an active standby pair" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information.

Examples

Drop index partsorderedindex which is defined on table orderitems using one of the following:

DROP MATERIALIZED VIEW LOG

The DROP MATERIALIZED VIEW LOG statement removes the materialized view log for a detail table. It also drops the global temporary table that was created by the CREATE MATERIALIZED VIEW LOG statement.

Required privilege

No privilege is required for the table owner.

DROP ANY TABLE for another user's table.

SQL syntax

DROP MATERIALIZED VIEW LOG ON TableName

Parameters

Parameter

Description

TableName

Name of the detail table for which the materialized view log was created.

Description

This statement drops the materialized view log for the specified detail table. The materialized view log cannot be dropped if there is an asynchronous materialized view that depends on the log for refreshing.

DROP PACKAGE [BODY]

The DROP PACKAGE statement removes a stored package from the database. Both the specification and the body are dropped. DROP PACKAGE BODY removes only the body of the package.

Required privilege

No privilege is required for the package owner.

DROP ANY PROCEDURE for another user's package.

SQL syntax

DROP PACKAGE [BODY] [Owner.]PackageName

Parameters

Parameter

Description

PACKAGE [BODY]

Specify BODY to drop only the body of the package. Omit BODY to drop both the specification and body of the package.

[Owner.]PackageName

Name of the package to be dropped.

Description

When you drop only the body of the package, TimesTen does not invalidate dependent objects. However, you cannot execute one of the procedures or stored functions declared in the package specification until you re-create the package body.

TimesTen invalidates any objects that depend on the package specification. If you subsequently reference one of these objects, then TimesTen tries to recompile the object and returns an error if you have not re-created the dropped package.

Do not use this statement to remove a single object from the package. Instead, re-create the package without the object using the CREATE PACKAGE and CREATE PACKAGE BODY statements with the OR REPLACE clause.

To use the DROP PACKAGE [BODY] statement, you must have PL/SQL enabled in your database. If you do not have PL/SQL enabled in your database, TimesTen returns an error.

DROP PROCEDURE

The DROP PROCEDURE statement removes a standalone stored procedure from the database. Do not use this statement to remove a procedure that is part of a package.

Required privilege

No privilege is required for the procedure owner.

DROP ANY PROCEDURE for another user's procedure.

SQL syntax

DROP PROCEDURE [Owner.]ProcedureName

Parameters

Parameter

Description

[Owner.]ProcedureName

Name of the procedure to be dropped.

Description

When you drop a procedure, TimesTen invalidates objects that depend on the dropped procedure. If you subsequently reference one of these objects, TimesTen attempts to recompile the object and returns an error message if you have not re-created the dropped procedure.

Do not use this statement to remove a procedure that is part of a package. Either drop the package or redefine the package without the procedure using the CREATE PACKAGE statement with the OR REPLACE clause.

To use the DROP PROCEDURE statement, you must have PL/SQL enabled in your database. If you do not have PL/SQL enabled in your database, an error is thrown.

Examples

The following statement drops the procedure myproc and invalidates all objects that depend on myproc:

DROP SEQUENCE

The DROP SEQUENCE statement removes an existing sequence number generator.

Required privilege

No privilege is required for the sequence owner.

DROP ANY SEQUENCE for another user's sequence.

SQL syntax

DROP SEQUENCE [Owner.]SequenceName

Parameters

Parameter

Description

[Owner.]SequenceName

Name of the sequence number generator

Description

Sequences can be dropped while they are in use.

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.

If the sequence is part of a replication scheme, use the ALTER REPLICATION statement to drop the sequence from the replication scheme. Then use the DROP SEQUENCE statement to drop the sequence.

DROP SYNONYM

The DROP SYNONYM statement removes a synonym from the database.

If the synonym is replicated across an active standby pair and if DDL_REPLICATION_LEVEL is 2, use the DROP SYNONYM statement to drop the synonym from the active standby pair in the replication scheme. See "Making DDL changes in an active standby pair" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information.

Required privilege

No privilege is required to drop the private synonym by its owner. The DROP ANY SYNONYM privilege is required to drop another user's private synonym.

The DROP PUBLIC SYNONYM privilege is required to drop a PUBLIC synonym.

SQL syntax

To drop a private synonym, use the following syntax:

DROP SYNONYM [Owner.]Synonym_Name

To drop a public synonym, provide the PUBLIC keyword, as follows:

DROP PUBLIC SYNONYM Synonym_Name

Parameters

Parameter

Description

PUBLIC

Specify PUBLIC to drop a public synonym.

[Owner.]

Optionally, specify the owner for a private synonym. If you omit the owner, the private synonym must exist in the current user's schema.

Synonym_Name

Specify the name of the synonym to be dropped.

Examples

Drop the public synonym pubemp:

DROP PUBLIC SYNONYM pubemp;
Synonym dropped.

Drop the private synjobs synonym:

DROP SYNONYM synjobs;
Synonym dropped.

As user terry with DROP ANY SYNONYM privilege, drop the private syntab synonym owned by ttuser.

DROP TABLE

The DROP TABLE statement removes the specified table, including any hash indexes and any range indexes associated with it.

Required privilege

No privilege is required for the table owner.

DROP ANY TABLE for another user's table.

SQL syntax

DROP TABLE [Owner.]TableName

Parameters

Parameter

Description

[Owner.]TableName

Identifies the table to be dropped.

Description

If you attempt to drop a table that is in use, an error results.

If a DROP TABLE operation is or was active in an uncommitted transaction, other transactions doing DML operations that do not access that table are allowed to proceed.

If the table is a replicated table, you can do one of the following:

Use the DROP REPLICATION statement to drop the replication scheme before issuing the DROP TABLE statement.

If DDL_REPLICATION_LEVEL is 2, use the DROP TABLE statement to drop the table from the active standby pair in the replication scheme.

If DDL_REPLICATION_LEVEL is 1, stop the replication agent and use the ALTER ACTIVE STANDBY PAIR EXCLUDE TABLE statement to exclude the table from the replication scheme. Then use the DROP TABLE statement to drop the table.

FLUSH CACHE GROUP

The FLUSH CACHE GROUP statement flushes data from TimesTen cache tables to Oracle tables. This statement is available only for user managed cache groups. For a description of cache group types, see "User managed and system managed cache groups".

There are two variants to this operation: one that accepts a WHERE clause, and one that accepts a WITH ID clause.

FLUSH CACHE GROUP is meant to be used when commit propagation (from TimesTen to Oracle) is turned off. Instead of propagating every transaction upon commit, many transactions can be committed before changes are propagated to Oracle. For each cache instance ID, if the cache instance exists in the Oracle database, the operation in the Oracle database consists of an update. If the cache instance does not exist in the Oracle database, TimesTen inserts it.

This is useful, for example, in a shopping cart application in which many changes may be made to the cart, which uses TimesTen as a high-speed cache, before the order is committed to the master Oracle table.

Note:

Using a WITH ID clause usually results in better system performance than using a WHERE clause.

Only inserts and updates are flushed. Inserts are propagated as inserts if the record does not exist in the Oracle table or as updates (if the record already exists). It is not possible to flush a delete. That is, if a record is deleted on TimesTen, there is no way to "flush" that delete to the Oracle table. Deletes must be propagated either manually or by turning commit propagation on. Attempts to flush deleted records are silently ignored. No error or warning is issued. Records from tables that are specified as READ ONLY or PROPAGATE cannot be flushed to Oracle tables.

Required privileges

No privilege is required for the cache group owner.

FLUSH or FLUSH ANY CACHE GROUP for another user's cache group.

SQL syntax

FLUSH CACHE GROUP [Owner.]GroupName
[WHERE ConditionalExpression];

or

FLUSH CACHE GROUP [Owner.]GroupName
WITH ID (ColumnValueList)

Parameters

Parameter

Description

[Owner.]GroupName

Name of the cache group to be flushed.

ConditionalExpression

A search condition to qualify the target rows of the operation. When using more than one table with columns with the same name, the table names in subqueries in the WHERE clause of the FLUSH CACHE GROUP statement must be fully qualified.

WITH IDColumnValueList

The WITH ID clauses allows you to use primary key values to flush the cache instance. Specify ColumnValueList as either a list of literals or binding parameters to represent the primary key values.

Description

WHERE clauses are generally used to apply the operation to a set of instances, rather than to a single instance or to all instances. The flush operation uses the WHERE clause to determine which instances to send to the Oracle database.

Generally, you do not have to fully qualify the column names in the WHERE clause of the FLUSH CACHE GROUP statement. 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. Without an owner name, all tables referenced by cache group WHERE clauses are owned by the current login name executing the cache group operation.

When the WHERE clause is omitted, the entire contents of the cache group is flushed to Oracle tables. When the WHERE clause is included, it is allowed to include only the root table.

Following the execution of a FLUSH CACHE GROUP statement, the ODBC function SQLRowCount(), the JDBC method getUpdateCount(), and the OCI function OCIAttrGet() with the OCI_ATTR_ROW_COUNT argument return the number of cache instances that were flushed.

Use the WITH ID clause to specify binding parameters

Restrictions

Do not use the WITH ID clause on AWT or SWT cache groups, user managed cache groups with the propagate attribute, or autorefreshed and propagated user managed cache groups unless the cache group is a dynamic cache group.

Do not use the WITH ID clause with the COMMIT EVERYnROWS clause.

Examples

FLUSH CACHE GROUP marketbasket;
FLUSH CACHE GROUP marketbasket
WITH ID(10);

If you omit one or more of the table's columns from this list, then the value of the omitted column in the inserted row is the column default value as specified when the table was created or last altered. If any omitted column has a NOT NULL constraint and has no default value, then the database returns an error.

If you omit a list of columns completely, then you must specify values for all columns in the table

?

:DynamicParameter

Place holder for a dynamic parameter in a prepared SQL statement. The value of the dynamic parameter is supplied when the statement is executed.

If you omit any of the table's columns from the column name list, the INSERT statement places the default value in the omitted columns. If the table definition specifies NOT NULL for any of the omitted columns and there is no default value, the INSERT statement fails.

BINARY and VARBINARY data can be inserted in character or hexadecimal format:

Character format requires single quotes.

Hexadecimal format requires the prefix '0x before the value.

The INSERT operation fails if it violates a foreign key constraint. See "CREATE TABLE" for a description of the foreign key constraint.

Restrictions on the RETURNING clause:

Each Expression must be a simple expression. Aggregate functions are not supported.

You cannot return a sequence number into an OUT parameter.

ROWNUM and subqueries cannot be used in the RETURNING clause.

Parameters in the RETURNING clause cannot be duplicated anywhere in the INSERT statement.

In PL/SQL, you cannot use a RETURNING clause with a WHERE CURRENT operation.

Return the annual salary and job_id of a new employee. Declare the variables sal and jobid with the same data types as salary and job_id. Insert the row into employees. Print the variables for verification.

INSERT...SELECT

The INSERT...SELECT statement inserts the results of a query into a table.

Required privilege

No privilege is required for the object owner.

INSERT and SELECT for another user's object.

SQL syntax

INSERT INTO [Owner.]TableName [(ColumnName [,...])] InsertQuery

Parameters

Parameter

Description

[Owner.]TableName

Table to which data is to be added.

ColumnName

Column for which values are supplied. If you omit any of the table's columns from the column name list, the INSERT...SELECT statement places the default value in the omitted columns. If the table definition specifies NOT NULL, without a default value, for any of the omitted columns, the INSERT...SELECT statement fails. You can omit the column name list if you provide values for all columns of the table in the same order the columns were specified in the CREATE TABLE statement. If too few values are provided, the remaining columns are assigned default values.

A search condition to qualify the target rows of the operation. When using more than one table with columns with the same name, the table names in subqueries in the WHERE clause of the LOAD CACHE GROUP statement must be fully qualified.

n

The number of rows to insert into the cache group before committing the work. It must be a nonnegative integer. If it is 0, the entire statement is executed as one transaction.

[PARALLELNumThreads]

Provides parallel loading for cache group tables. Specifies the number of loading threads to run concurrently. One thread performs the bulk fetch from Oracle and the other threads (NumThreads - 1 threads) perform the inserts into TimesTen. Each thread uses its own connection or transaction.

The minimum value for NumThreads is 2. The maximum value is 10. If you specify a value greater than 10, TimesTen assigns the value 10.

WITH IDColumnValueList

The WITH ID clauses allows you to use primary key values to load the cache instance. Specify ColumnValueList as either a list of literals or binding parameters to represent the primary key values.

Description

LOAD CACHE GROUP loads all new instances from Oracle that satisfy the cache group definition and are not yet present in the cache group.

Before issuing the LOAD CACHE GROUP statement, ensure that the replication agent is running if the cache group is replicated or is an AWT cache group.

LOAD CACHE GROUP is executed in its own transaction, and must be the first operation in a transaction.

For an explicitly loaded cache group, LOAD CACHE GROUP does not update cache instances that are already present in the TimesTen cache tables. Therefore, LOAD CACHE GROUP loads only inserts on Oracle tables into the corresponding TimesTen cache tables.

For a dynamic cache group, LOAD CACHE GROUP loads rows that have been inserted, updated and deleted on Oracle tables into the cache tables. For more information about explicitly loaded and dynamic cache groups, see Oracle In-Memory Database Cache User's Guide.

The transaction size is the number of rows inserted before committing the work. The value of n in COMMIT EVERYnROWS must be nonnegative and is rounded up to the nearest multiple of 256 for performance reasons.

Errors cause a rollback. When rows are committed periodically, errors abort the remainder of the load. The load is rolled back to the last commit.

If the LOAD CACHE GROUP statement fails when you specify COMMIT EVERYnROWS (where n is greater than 0), the content of the target cache group could be in an inconsistent state since some of the loaded rows are already committed. Some cache instances may be partially loaded. Use the UNLOAD statement to bring it back to a consistent state, then load again.

Generally, you do not have to fully qualify the column names in the WHERE clause of the LOAD CACHE GROUP statement. 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.

When loading a read-only cache group:

The AUTOREFRESH state must be paused.

The LOAD CACHE GROUP statement cannot have a WHERE clause (except on a dynamic cache group).

The cache group must be empty.

If the automatic refresh state of a cache group (explicitly loaded or dynamic) is PAUSED, the state is changed to ON after a LOAD CACHE GROUP statement that was issued on the cache group completes.If the automatic refresh state of a dynamic cache group is PAUSED and the cache tables are populated, the state remains PAUSED after a LOAD CACHE GROUP statement that was issued on the cache group completes.

Following the execution of a LOAD CACHE GROUP statement, the ODBC function SQLRowCount(), the JDBC method getUpdateCount(), and the OCI function OCIAttrGet() with the OCI_ATTR_ROW_COUNT argument return the number of cache instances that were loaded.

Use the WITH ID clause as follows:

In place of the WHERE clause for faster loading of the cache instance

To specify binding parameters

If you want to roll back the load transaction upon failure

Restrictions

Do not reference child tables in the WHERE clause.

Do not specify the PARALLEL clause in the following circumstances:

With the WITH ID clause

With the COMMIT EVERY 0 ROWS clause

When database level locking is enabled (connection attribute LockLevel is set to 1)

Do not use the WITH ID clause when loading these types of cache groups:

Explicitly loaded read-only cache group

Explicitly loaded user managed cache group with the autorefresh attribute

User managed cache group with the AUTOREFRESH and PROPAGATE attributes

Do not use the WITH ID clause with the COMMIT EVERYnROWS clause.

The WITH ID clause cannot be used to acquire a cache instance from another cache grid member.

Use the HR schema to illustrate the use of the PARALLEL clause with the LOAD CACHE GROUP statement. The COMMIT EVERYnROWS clause (where n is greater than 0) is required. Issue the CACHEGROUPS command. You see cache group cg2 is defined and the autorefresh state is paused. Unload cache group cg2, then specify the LOAD CACHE GROUP statement with the PARALLEL clause to provide parallel loading. You see 25 cache instances loaded.

The following example loads only the cache instances for customers whose customer number is greater than or equal to 5000 into the TimesTen cache tables in the new_customers cache group from the corresponding Oracle tables:

MERGE

The MERGE statement allows you to select rows from one or more sources for update or insertion into a target table. You can specify conditions that are used to evaluate which rows are updated or inserted into the target table.

Use this statement to combine multiple INSERT and UPDATE statements.

MERGE is a deterministic statement: You cannot update the same row of the target table multiple times in the same MERGE statement.

Required privilege

No privilege is required for the owner of the target table and the source table.

INSERT or UPDATE on a target table owned by another user and SELECT on a source table owned by another user.

Name of the target table. This is the table in which rows are either updated or inserted.

[Alias]

You can optionally specify an alias name for the target or source table.

USING {[Owner.]SourceTableName| (Subquery)} [Alias]

The USING clause indicates the table name or the subquery that is used for the source of the data. Use a subquery if you want to use joins or aggregates. Optionally, you can specify an alias for the table name or the subquery.

ON (Condition)

You specify the condition that is used to evaluate each row of the target table to determine if the row should be considered for either a merge insert or a merge update. If the condition is true when evaluated, then the MergeUpdateClause is considered for the target row using the matching row from the SourceTableName. An error is generated if more than one row in the source table matches the same row in the target table. If the condition is not true when evaluated, then the MergeInsertClause is considered for that row.

SETSetClause

Clause used with the UPDATE statement. For information on the UPDATE statement, see "UPDATE".

[WHERECondition1]

For each row that matches the ON (Condition), Condition1 is evaluated. If the condition is true when evaluated, the row is updated. You can refer to either the target table or the source table in this clause. You cannot use a subquery. The clause is optional.

INSERT [Columns[,...]]VALUES({{Expression|DEFAULT|NULL} [,...]})

Columns to insert into the target table. For more information on the INSERT statement, see "INSERT".

[WHERECondition2]

If specified, Condition2 is evaluated. If the condition is true when evaluated, the row is inserted into the target table. The condition can refer to the source table only. You cannot use a subquery.

Description

You can specify the MergeUpdateClause and MergeInsertClause together or separately. If you specify both, they can be in either order.

If DUAL is the only table specified in the USING clause and it is not referenced elsewhere in the MERGE statement, specify DUAL as a simple table rather than using it in a subquery. In this simple case, to help performance, specify a key condition on a unique index of the target table in the ON clause.

Restrictions on the MergeUpdateClause:

You cannot update a column that is referenced in the ON condition clause.

You cannot update source table columns.

Restrictions on the MergeInsertClause:

You cannot insert values of target table columns.

Other restrictions:

Do not use the set operators in the subquery of the source table.

Do not use a subquery in the WHERE condition of either the MergeUpdateClause or the MergeInsertClause.

The target table cannot be a detail table of a materialized view.

The RETURNING clause cannot be used in a MERGE statement.

Examples

In this example, dual is specified as a simple table. There is a key condition on the UNIQUE index of the target table specified in the ON clause. The DuplicateBindMode attribute is set to 1 in this example. (The default is 0.)

In this example, a table called contacts is created with columns employee_id and manager_id. One row is inserted into the contacts table with values 101 and NULL for employee_id and manager_id, respectively. The MERGE statement is used to insert rows into the contacts table using the data in the employees table. A SELECT FIRST 3 rows is used to illustrate that in the case where employee_id is equal to 101, manager_id is updated to 100. The remaining 106 rows from the employees table are inserted into the contacts table:

A search condition to qualify the target rows of the operation. When using more than one table with columns with the same name, the table names in subqueries in the WHERE clause of the REFRESH CACHE GROUP statement must be fully qualified.

n

The number of rows to insert into the cache group before committing the work. The value must be a nonnegative integer. If the value is 0, the entire statement is executed as one transaction.

[PARALLELNumThreads]

Provides parallel loading for cache group tables. Specifies the number of loading threads to run concurrently. One thread performs the bulk fetch from Oracle and the other threads (NumThreads - 1 threads) perform the inserts into TimesTen. Each thread uses its own connection or transaction.

The minimum value for NumThreads is 2. The maximum value is 10. If you specify a value greater than 10, TimesTen assigns the value 10.

WITH IDColumnValueList

The WITH ID clauses allows you to use primary key values to refresh the cache instance. Specify ColumnValueList as either a list of literals or binding parameters to represent the primary key values.

Description

A REFRESH CACHE GROUP statement must be executed in its own transaction.

Before issuing the REFRESH CACHE GROUP statement, ensure that the replication agent is running if the cache group is replicated or is an AWT cache group.

REFRESH CACHE GROUP replaces all or specified cache instances in the TimesTen cache tables with the most current data from the corresponding Oracle tables even if an instance is already present in the cache tables. For explicitly loaded cache groups, a refresh operation is equivalent to an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement. Operations on all rows in the Oracle tables including inserts, updates, and deletes are applied to the cache tables. For dynamic cache groups, a refresh operation refreshes only rows that are updated or deleted on Oracle tables into the cache tables. For more information on explicitly loaded and dynamic cache groups, see Oracle In-Memory Database Cache User's Guide.

When refreshing a read-only cache group:

The AUTOREFRESH statement must be paused, and

The REFRESH statement cannot have a WHERE clause unless the cache group is a dynamic cache group.

If the automatic refresh state of a cache group (dynamic or explicitly loaded) is PAUSED, the state is changed to ON after an unconditional REFRESH CACHE GROUP statement issued on the cache group completes.

If the automatic refresh state of a dynamic cache group is PAUSED, the state remains PAUSED after a REFRESH CACHE GROUP...WITH ID statement completes.

Generally, you do not have to fully qualify the column names in the WHERE clause of the REFRESH CACHE GROUP statement. 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.

If the REFRESH CACHE GROUP statement fails when you specify COMMIT EVERYnROWS (where n is greater than 0), the content of the target cache group could be in an inconsistent state since some of the loaded rows are already committed. Some cache instances may be partially loaded. Use the UNLOAD CACHE GROUP statement to unload the cache group, then use the LOAD CACHE GROUP statement to reload the cache group.

Following the execution of a REFRESH CACHE GROUP statement, the ODBC function SQLRowCount(), the JDBC method getUpdateCount(), and the OCI function OCIAttrGet() with the OCI_ATTR_ROW_COUNT argument return the number of cache instances that were refreshed.

Use the WITH ID clause:

In place of the WHERE clause for faster refreshing of the cache instance

To specify binding parameters

If you want to roll back the refresh transaction upon failure

Restrictions

Do not specify the PARALLEL clause:

With the WITH ID clause

With the COMMIT EVERY 0 ROWS clause

When database level locking is enabled (connection attribute LockLevel is set to 1)

Do not use the WITH ID clause when refreshing these types of cache groups:

Use the HR schema to illustrate the use of the PARALLEL clause with the REFRESH CACHE GROUP statement. The COMMIT EVERYnROWS (where n is greater than 0) is required. Issue the CACHEGROUPS command. You see cache group cg2 is defined and the autorefresh state is paused. Specify the REFRESH CACHE GROUP statement with the PARALLEL clause to provide parallel loading. You see 25 cache instances refreshed.

REFRESH MATERIALIZED VIEW

No privilege is required for the owner of the materialized view log tables.

SELECT ANY TABLE if not the owner of materialized view log tables.

Required privilege on the materialized view:

No privilege is required for the owner of the materialized view.

SELECT ANY TABLE if not the owner of materialized view.

SQL syntax

REFRESH MATERIALIZED VIEW ViewName

Parameters

Parameter

Description

ViewName

Name of the asynchronous materialized view

Description

This statement refreshes the specified asynchronous materialized view. It is executed in a separate thread as a separate transaction and committed. The user transaction is not affected, but the user thread waits for the refresh operation to be completed before returning to the user. If you have not specified a refresh interval for an asynchronous materialized view, using this statement is the only way to refresh the view. If you have specified a refresh interval, you can still use this statement to refresh the view manually.

Since the refresh operation is always performed in a separate transaction, the refresh operation does not wait for any uncommitted user transactions to commit. Only the committed rows are considered for the refresh operation. This is true for the manual refresh statement as well as the automatic refresh that takes place at regular intervals.

If the CREATE MATERIALIZED VIEW statement for the view specified a FAST refresh, then the REFRESH MATERIALIZED VIEW statement uses the incremental refresh method. Otherwise this statement uses the full refresh method.

Name of the user from whom privileges are to be revoked. The user name must first have been introduced to the TimesTen database through a CREATE USER statement.

[owner.]object

object is the name of the object on which privileges are being revoked. owner is the owner of the object. If owner is not specified, then the user who is revoking the privilege is known as the owner.

PUBLIC

Specifies that the privilege is revoked for all users.

Description

Privileges on objects cannot be revoked from the owner of the objects.

Any user who can grant a privilege can revoke the privilege even if they were not the user who originally granted the privilege.

Privileges must be revoked at the same level they were granted. You cannot revoke an object privilege from a user who has the associated system privilege. For example, if you grant SELECT ANY TABLE to a user and then try to revoke SELECT ON bob.table1, the revoke fails unless you have specifically granted SELECT ON bob.table1 in addition to SELECT ANY TABLE.

If a user has been granted all system privileges, you can revoke a specific privilege. For example, you can revoke ALTER ANY TABLE from a user who has been granted all system privileges.

If a user has been granted all object privileges, you can revoke a specific privilege on a specific object from the user. For example, you can revoke the DELETE privilege on table customers from user terry even if terry has previously been granted all object privileges.

You can revoke all privileges from a user even if the user has not previously been granted all privileges.

You cannot revoke a specific privilege from a user who has not been granted the privilege.

You cannot revoke privileges on objects owned by a user.

You cannot revoke system privileges and object privileges in the same statement.

You can specify only one object in an object privilege statement.

Revoking the SELECT privilege on a detail table or a system privilege that includes the SELECT privilege from user2 on a detail table owned by user1 causes associated materialized views owned by user2 to be marked invalid. See "Invalid materialized views".

Examples

Revoke the ADMIN and DDL privileges from the user terry:

REVOKE admin, ddl FROM terry;

Assuming the revoker has ADMIN privilege, revoke the UPDATE privilege from terry on the customers table owned by pat:

Specifies the number of rows to retrieve. NumRows must be either a positive INTEGER value or a dynamic parameter placeholder. The syntax for a dynamic parameter placeholder is either ? or :DynamicParameter. The value of the dynamic parameter is supplied when the statement is executed.

ROWSmTOn

Specifies the range of rows to retrieve where m is the first row to be selected and n is the last row to be selected. Row counting starts at row 1. The query SELECT ROWS 1 TOn returns the same rows as SELECT FIRSTNumRows assuming the queries are ordered and n and NumRows have the same value.

Use either a positive INTEGER value or a dynamic parameter placeholder for m and n values. The syntax for a dynamic parameter placeholder is either ? or :DynamicParameter. The value of the dynamic parameter is supplied when the statement is executed.

ALL

Prevents elimination of duplicate rows from the query result. If neither ALL nor DISTINCT is specified, ALL is the default.

DISTINCT

Ensures that each row in the query result is unique. All NULL values are considered equal for this comparison. Duplicate rows are not evaluated.

SelectList

Specifies how the columns of the query result are to be derived. The syntax of select list is presented under "SelectList".

FROMTableSpec

Identifies the tables referenced in the SELECT statement. The maximum number of tables per query is 24.

TableSpec identifies a table from which rows are selected. The table can be a derived table, which is the result of a SELECT statement in the FROM clause. The syntax of TableSpec is presented under "TableSpec".

WHERESearchCondition

The WHERE clause determines the set of rows to be retrieved. Normally, rows for which SearchCondition is FALSE or NULL are excluded from processing, but SearchCondition can be used to specify an outer join in which rows from an outer table that do not have SearchCondition evaluated to TRUE with respect to any rows from the associated inner table are also returned, with projected expressions referencing the inner table set to NULL.

The unary (+) operator may follow some column and ROWID expressions to indicate an outer join. The (+) operator must follow all column and ROWID expressions in the join conditions that refer to the inner table. There are several conditions on the placement of the (+) operator. These generally restrict the type of outer join queries that can be expressed. The (+) operator may appear in WHERE clauses but not in HAVING clauses. Two tables cannot be outer joined together. An outer join condition cannot be connected by OR.

The GROUP BY clause identifies one or more expressions to be used for grouping when aggregate functions are specified in the select list and when you want to apply the function to groups of rows.

The expression can be of various complexities. For example, it can designate single or multiple columns. It can include aggregate functions, arithmetic operations, the ROWID pseudocolumn, or NULL. It can also be a date or user function, a constant, or a dynamic parameter.

When you use the GROUP BY clause, the select list can contain only aggregate functions and columns referenced in the GROUP BY clause. If the select list contains the construct *, TableName.*, or Owner.TableName.*, then the GROUP BY clause must contain all columns that the * includes. NULL values are considered equivalent in grouping rows. If all other columns are equal, all NULL values in a column are placed in a single group.

If the GROUP BY clause is omitted, the entire query result is treated as one group.

HAVING

The HAVING clause can be used in a SELECT statement to filter groups of an aggregate result. The existence of a HAVING clause in a SELECT statement turns the query into an aggregate query. All columns referenced outside the sources of aggregate functions in any clause except the WHERE clause must be included in the GROUP BY clause.

Subqueries can be specified in the HAVING clause.

(+)

A simple join (also called an inner join) returns a row for each pair of rows from the joined tables that satisfy the join condition specified in SearchCondition. Outer joins are an extension of this operator in which all rows of the outer table are returned, whether or not matching rows from the joined inner table are found. In the case no matching rows are found, any projected expressions referencing the inner table are given the value NULL.

ORDER BY

Sorts the query result rows in order by specified columns or expressions. Specify the sort key columns in order from major sort key to minor sort key. For each column, you can specify whether the sort order is to be ascending or descending. If neither ASC (ascending) nor DESC (descending) is specified, ascending order is used. All character data types are sorted according to the current value of the NLS_SORT session parameter.

The ORDER BY clause supports column aliases, which can be referenced only in an ORDER BY clause. A single query may declare several column aliases with the same name, but any reference to that alias results in an error.

ColumnID

Must correspond to a column in the select list. You can identify a column to be sorted by specifying its name or its ordinal number. The first column in the select list is column number 1. It is better to use a column number when referring to columns in the select list if they are not simple columns. Some examples are aggregate functions, arithmetic expressions, and constants.

A ColumnID in the ORDER BY clause has this syntax:

{ColumnNumber|[[Owner.]TableName.]ColumnName}

ColumnAlias

Used in an ORDER BY clause, the column alias must correspond to a column in the select list. The same alias can identify multiple columns.

{* | [Owner.]TableName.* |

{Expression| [[Owner.]TableName.]ColumnName|

[[Owner.]TableName.]ROWID

}

[[AS]ColumnAlias]} [,...]

FOR UPDATE

[OF [[Owner.]

TableName.]

ColumnName[,...]]

[NOWAIT | WAITSeconds]

FOR UPDATE

FOR UPDATE maintains a lock on an element (usually a row) until the end of the current transaction, regardless of isolation. All other transactions are excluded from performing any operation on that element until the transaction is committed or rolled back.

FOR UPDATE may be used with joins and the ORDER BY, GROUP BY, and DISTINCT clauses. Update locks are obtained on either tables or rows, depending on the table/row locking method chosen by the optimizer.

Rows from all tables that satisfy the WHERE clause are locked in UPDATE mode unless the FOR UPDATE OF clause is specified. This clause specifies which tables to lock.

If using row locks, all qualifying rows in all tables from the table list in the FROM clause are locked in update mode. Qualifying rows are those rows that satisfy the WHERE clause. When table locks are used, the table is locked in update mode whether or not there are any qualifying rows.

If the serializable isolation level and row locking are enabled, nonqualifying rows are downgraded to Shared mode. If a read-committed isolation level and row locking are turned on, nonqualifying rows are unlocked.

SELECT...FOR UPDATE locks are not blocked by SELECT locks.

FOR UPDATE [OF [[Owner.]TableName.]ColumnName [,...] ]

This mode optionally includes the name of the column or columns in the table to be locked for update.

[NOWAIT | WAITSeconds]

This specifies how to proceed if the selected rows are locked. It does not apply to table-level locks or database-level locks.

NOWAIT specifies that there is no waiting period for locks. An error is returned if the lock is not available.

WAITSeconds specifies the lock timeout setting.

An error is returned if the lock is not obtained in the specified amount of time.

The lock timeout setting is expressed in seconds or fractions of second. The data type for Seconds is NUMBER. Values between 0.0 and 1000000.0 are valid.

If neither NOWAIT nor WAIT is specified, the lock timeout interval for the transaction is used.

SelectQuery1

{UNION [ALL] | MINUS | INTERSECT}

SelectQuery2

Specifies that the results of SelectQuery1 and SelectQuery2 are to be combined, where SelectQuery1 and SelectQuery2 are general SELECT statements with some restrictions.

The UNION operator combines the results of two queries where the SelectList is compatible. If UNION ALL is specified, duplicate rows from both SELECT statements are retained. Otherwise, duplicates are removed.

The MINUS operator combines rows returned by the first query but not by the second into a single result.

The INTERSECT operator combines only those rows returned by both queries into a single result.

The data type of corresponding selected entries in both SELECT statements must be compatible. One type can be converted to the other type using the CAST operator. Nullability does not need to match.

The length of a column in the result is the longer length of correspondent selected values for the column. The column names of the final result are the column names of the leftmost select.

You can combine multiple queries using the set operators UNION, UNION ALL, MINUS, and INTERSECT.

One or both operands of a set operator can be a set operator. Multiple or nested set operators are evaluated from left to right.

The set operators can be mixed in the same query.

Restrictions on the SELECT statement that specify the set operators are as follows:

Neither SELECT statement can specify FIRSTNumRows.

ORDER BY can be specified to sort the final result but cannot be used with any individual operand of a set operator. Only column names of tables or column alias from the leftmost SELECT statement can be specified in the ORDER BY clause.

GROUP BY can be used to group an individual SELECT operand of a set operator but cannot be used to group a set operator result.

The set operators cannot be used in materialized view or a joined table.

Description

When you use a correlation name, the correlation name must conform to the syntax rules for a basic name. (See "Basic names".) All correlation names within one SELECT statement must be unique. Correlation names are useful when you join a table to itself. Define multiple correlation names for the table in the FROM clause and use the correlation names in the select list and the WHERE clause to qualify columns from that table. See "TableSpec" for more information about correlation names.

SELECT...FOR UPDATE is supported in a SELECT statement that specifies a subquery, but it can be specified only in the outermost query.

If your query specifies either FIRSTNumRows or ROWSmTOn, ROWNUM may not be used to restrict the number of rows returned.

FIRSTNumRows and ROWSmTOn cannot be used together in the same SELECT statement.

Examples

This example shows the use of a column alias (max_salary) in the SELECT statement:

A condition cannot use the IN operator to compare a column marked with (+). For example, the following query returns an error:

SELECT * FROM t1, t2, t3
WHERE t1.x = t2.x(+) AND t2.y(+) IN (4,5);

The following query is valid:

SELECT * FROM t1, t2, t3
WHERE t1.x = t2.x(+) AND t1.y IN (4,5);

The following query results in an inner join. The condition without the (+) operator is treated as an inner join condition:

SELECT * FROM t1, t2
WHERE t1.x = t2.x(+) AND t1.y = t2.y;

In the following query, the WHERE clause contains a condition that compares an inner table column of an outer join with a constant. The (+) operator is not specified and hence the condition is treated as an inner join condition.

SELECT * FROM t1
WHERE x1 IN (SELECT x2 FROM t2)
UNION
SELECT * FROM t1
WHERE x1 IN (SELECT x3 FROM t3);

Select all orders that have the same price as the highest price in their category:

SELECT * FROM orders WHERE price = (SELECT MAX(price)
FROM stock WHERE stock.cat=orders.cat);

The next example illustrates the use of the INTERSECT set operator. There is a department_id value in the employees table that is NULL. In the departments table, the department_id is defined as a NOT NULL primary key. The rows returned from using the INTERSECT set operator do not include the row in the departments table whose department_id value is NULL.

The next example illustrates the use of the MINUS set operator by combining rows returned by the first query but not the second. The row containing the NULLdepartment_id value in the employees table is the only row returned.

The following example locks the employees table for update and waits 10 seconds for the lock to be available. An error is returned if the lock is not acquired in 10 seconds. The first five rows are selected.

The SelectList parameter of the SELECT statement has the following parameters:

Parameter

Description

*

Includes, as columns of the query result, all columns of all tables specified in the FROM clause.

[Owner.]TableName.*

Includes all columns of the specified table in the result.

Expression

An aggregate query includes a GROUP BY clause or an aggregate function.

When the select list is not an aggregate query, the column reference must reference a table in the FROM clause.

A column reference in the select list of an aggregate query must reference a column list in the GROUP BY clause. If there is no GROUP BY clause, then the column reference must reference a table in the FROM clause.

[[Owner.]Table.]ColumnName

Includes a particular column from the named owner's indicated table. You can also specify the CURRVAL or NEXTVAL column of a sequence.

[[Owner.]Table.] ROWID

Includes the ROWID pseudocolumn from the named owner's indicated table.

NULL

When NULL is specified, the default for the resulting data type is VARCHAR(0). You can use the CAST function to convert the result to a different data type. NULL can be specified in the ORDER BY clause.

ColumnAlias

Used in an ORDER BY clause, the column alias must correspond to a column in the select list. The same alias can identify multiple columns.

{*|[Owner.]TableName.*|

{Expression|[[Owner.]TableName.]ColumnName|

[[Owner.]TableName.]ROWID

}

[[AS]ColumnAlias]} [,...]

Description

The clauses must be specified in the order given in the syntax diagram.

TimesTen does not support subqueries in the select list

A result column in the select list can be derived in any of the following ways:

A result column can be taken directly from one of the tables listed in the FROM clause.

Values in a result column can be computed, using an arithmetic expression, from values in a specified column of a table listed in the FROM clause.

Values in several columns of a single table can be combined in an arithmetic expression to produce the result column values.

Aggregate functions (AVG, MAX, MIN, SUM, and COUNT) can be used to compute result column values over groups of rows. Aggregate functions can be used alone or in an expression. You can specify aggregate functions containing the DISTINCT option that operate on different columns in the same table. If the GROUP BY clause is not specified, the function is applied over all rows that satisfy the query. If the GROUP BY clause is specified, the function is applied once for each group defined by the GROUP BY clause. When you use aggregate functions with the GROUP BY clause, the select list can contain aggregate functions, arithmetic expressions, and columns in the GROUP BY clause.

A result column containing a fixed value can be created by specifying a constant or an expression involving only constants.

In addition to specifying how the result columns are derived, the select list also controls their relative position from left to right in the query result. The first result column specified by the select list becomes the leftmost column in the query result, and so on.

Result columns in the select list are numbered from left to right. The leftmost column is number 1. Result columns can be referred to by column number in the ORDER BY clause. This is especially useful if you want to refer to a column defined by an arithmetic expression or an aggregate.

To join a table with itself, define multiple correlation names for the table in the FROM clause and use the correlation names in the select list and the WHERE clause to qualify columns from that table.

When you use the GROUP BY clause, one answer is returned per group in accordance with the select list, as follows:

The WHERE clause eliminates rows before groups are formed.

The GROUP BY clause groups the resulting rows.

The select list aggregate functions are computed for each group.

Examples

In this example, one value, the average number of days you wait for a part, is returned by the statement:

SELECT AVG(deliverydays)
FROM purchasing.supplyprice;

The part number and delivery time for all parts that take fewer than 20 days to deliver are returned by the following statement:

The part number and average price of each part are returned by the following statement:

SELECT partnumber, AVG(unitprice)
FROM purchasing.supplyprice
GROUP BY partnumber;

In the following example, the join returns names and locations of California suppliers. Rows are returned in ascending order by partnumber values. Rows containing duplicate part numbers are returned in ascending order by vendorname values. The FROM clause defines two correlation names (v and s), which are used in both the select list and the WHERE clause. The vendornumber column is the only common column between vendors and supplyprice.

The next example shows how to retrieve the rowid of a specific row. The retrieved rowid value can be used later for another SELECT, DELETE, or UPDATE statement.

SELECT rowid
FROM purchasing.vendors
WHERE vendornumber = 123;

The following example shows how to use a column alias to retrieve data from the table employees.

SELECT max(salary) AS max_salary FROM employees;

TableSpec

SQL syntax

The TableSpec parameter of the SELECT statement has the following syntax:

{[Owner.]TableName [CorrelationName] | JoinedTable | DerivedTable}

A simple table specification has the following syntax:

[Owner.]TableName

Parameters

The TableSpec parameter of the SELECT statement has the following parameters:

Parameter

Description

[Owner.]TableName

Identifies a table to be referenced.

CorrelationName

CorrelationName specifies an alias for the immediately preceding table. When accessing columns of that table elsewhere in the SELECT statement, use the correlation name instead of the actual table name within the statement. The scope of the correlation name is the SQL statement in which it is used. The correlation name must conform to the syntax rules for a basic name. See "Basic names".

All correlation names within one statement must be unique.

DerivedTable

Specifies a table derived from the evaluation of a SELECT statement. No FIRSTNumRows or ROWSmTOn clauses are allowed in this SELECT statement.

JoinedTable

Specifies the query that defines the table join. The syntax of JoinedTable is presented under "JoinedTable".

DerivedTable

A derived table is the result of a SELECT statement in the FROM clause, with an alias.

SQL syntax

The syntax for DerivedTable is as follows:

(Subquery) [CorrelationName]

Parameters

The DerivedTable parameter of the TableSpec clause of a SELECT statement has the following parameters:

Optionally use CorrelationName to specify an alias for the derived table. It must be different from any table name referenced in the query.

Description

When using a derived table, these restrictions apply:

The DUAL table can be used in a SELECT statement that references no other tables, but needs to return at least one row. Selecting from DUAL is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once.

Subquery cannot refer to a column from another derived table.

A derived table cannot be used as a source of a joined table.

A derived table cannot be used as a target of a DELETE or UPDATE statement.

JoinedTable

The JoinedTable parameter specifies a table derived from CROSS JOIN, INNER JOIN, LEFT OUTER JOIN or RIGHT OUTER JOIN.

SQL syntax

The syntax for JoinedTable is as follows:

{CrossJoin | QualifiedJoin}

Where CrossJoin is:

TableSpec1 CROSS JOIN TableSpec2

And QualifiedJoin is:

TableSpec1 [JoinType] JOIN TableSpec2 ON SearchCondition

In the QualifiedJoin parameter, JoinType syntax is as follows:

{INNER | LEFT [OUTER] | RIGHT [OUTER]}

Parameters

The JoinedTable parameter of the TableSpec clause of a SELECT statement has the following parameters:

Parameter

Description

CrossJoin

Performs a cross join on two tables. A cross join returns a result table that is the cartesian product of the input tables. The result is the same as that of a query with the following syntax:

SELECTSelectlistFROMTable1,Table2

QualifiedJoin

Specifies that the join is of type JoinType.

TableSpec1

Specifies the first table of the JOIN clause.

TableSpec2

Specifies the second table of the JOIN clause.

JoinTypeJOIN

Specifies the type of join to perform. These are the supported join types:

INNER

LEFT [OUTER]

RIGHT [OUTER]

INNER JOIN returns a result table that combines the rows from two tables that meet SearchCondition.

LEFT OUTER JOIN returns join rows that match SearchCondition and rows from the first table that do not have SearchCondition evaluated as true with any row from the second table.

RIGHT OUTER JOIN returns join rows that match SearchCondition and rows from the second table that do not have SearchCondition evaluated as true with any row from the first table.

ONSearchCondition

Specifies the search criteria to be used in a JOIN parameter. SearchCondition can refer only to tables referenced in the current qualified join.

Description

FULL OUTER JOIN is not supported.

A joined table can be used to replace a table in a FROM clause anywhere except in a statement that defines a materialized view. Thus, a joined table can be used in UNION, MINUS, INTERSECT, a subquery, a nonmaterialized view, or a derived table.

A subquery cannot be specified in the operand of a joined table. For example, the following statement is not supported:

A temporary table cannot be specified as an operand of a joined table.

OUTER JOIN can be specified in two ways, either using the (+) operator in SearchCondition of the WHERE clause or using a JOIN table operation. The two specification methods cannot coexist in the same statement.

Join order and grouping can be specified with a JoinedTable operation, but they cannot be specified with the (+) operator. For example, the following operation cannot be specified with the (+) operator:

t LEFT JOIN (t2 INNER JOIN t3 ON x2=x3) ON (x1 = x2 - x3)

Examples

These examples use the regions and countries tables from the HR schema.

TRUNCATE TABLE

The TRUNCATE TABLE statement is similar to a DELETE statement that deletes all rows. However, it is faster than DELETE in most circumstances, as DELETE removes each row individually.

Required privilege

No privilege is required for the table owner.

DELETE for another user's table.

SQL syntax

TRUNCATE TABLE [Owner.]TableName

Parameters

Parameter

Description

[Owner.]TableName

Identifies the table to be truncated.

Description

TRUNCATE is a DDL statement and thus is controlled by the DDLCommitBehavior attribute. If DDLCommitBehavior=0 (the default), then a commit is performed before and after execution of the TRUNCATE statement. If DDLCommitBehavior=1, then TRUNCATE is part of a transaction and these transactional rules apply:

TRUNCATE operations can be rolled back.

Subsequent INSERT statements are not allowed in the same transaction as a TRUNCATE statement.

Concurrent read committed read operations are allowed, and semantics of the reads are the same as for read committed reads in presence of DELETE statements

TRUNCATE is allowed even when there are child tables. However, child tables need to be empty for TRUNCATE to proceed. If any of the child tables have any rows in them, TimesTen returns an error indicating that a child table is not empty.

TRUNCATE is not supported with any detail table of a materialized view, table that is a part of a cache group, or temporary table.

When a table contains out-of-line varying-length data, the performance of TRUNCATE TABLE is similar to that of DELETE statement that deletes all rows in a table. For more details on out-of line data, see "Numeric data types".

Where tables are being replicated, the TRUNCATE statement replicates to the subscriber, even when no rows are operated upon.

When tables are being replicated with timestamp conflict checking enabled, conflicts are not reported.

Use the UNLOAD CACHE GROUP statement carefully with cache groups that have the AUTOREFRESH attribute. A row that is unloaded can reappear in the cache group as the result of an autorefresh operation if the row or its child rows are updated in Oracle Database.

Following the execution of an UNLOAD CACHE GROUP statement, the ODBC function SQLRowCount(), the JDBC method getUpdateCount(), and the OCI function OCIAttrGet() with the OCI_ATTR_ROW_COUNT argument return the number of cache instances that were unloaded.

Use the WITH ID clause to specify binding parameters.

Restrictions

Do not reference child tables in the WHERE clause.

Do not use the WITH ID clause on read-only cache groups or user managed cache groups with the autorefresh attribute unless the cache group is a dynamic cache group.

Specifies the number of rows to update. FIRSTNumRows is not supported in subquery statements. NumRows must be either a positive INTEGER value or a dynamic parameter placeholder. The syntax for a dynamic parameter placeholder is either ? or :DynamicParameter. The value of the dynamic parameter is supplied when the statement is executed.

[Owner.]TableName[CorrelationName]

[Owner.]TableName identifies the table to be updated.

CorrelationName specifies an alias for the table and must conform to the syntax rules for a basic name according to "Basic names". When accessing columns of that table elsewhere in the UPDATE statement, use the correlation name instead of the actual table name. The scope of the correlation name is the SQL statement in which it is used.

All correlation names within one statement must be unique.

SETColumnName

ColumnName specifies a column to be updated. You can update several columns of the same table with a single UPDATE statement. Primary key columns can be included in the list of columns to be updated as long as the values of the primary key columns are not changed.

Expression1

Any expression that does not contain an aggregate function. The expression is evaluated for each row qualifying for the update operation. The data type of the expression must be compatible with the data type of the updated column. Expression1 can specify a column or sequence CURRVAL or NEXTVAL reference when updating values.

NULL

Puts a NULL value in the specified column of each row satisfying the WHERE clause. The column must allow NULL values.

DEFAULT

Specifies that the column should be updated with the default value.

WHERESearchCondition

The search condition can contain a subquery. All rows for which the search condition is true are updated as specified in the SET clause. Rows that do not satisfy the search condition are not affected. If no rows satisfy the search condition, the table is not changed.

This next example updates job_id, salary and department_id for an employee whose last name is'Jones' in the employees table. The values of salary, last_name and department_id are returned into variables.

Specifies the column to be updated. You can update several columns of the same table with a single UPDATE statement. The SET clause can contain only one subquery, although this subquery can be nested.

The number of values in the select list of the subquery must be the same as the number of columns specified in the SET clause. An error is returned if the subquery returns more than one row for any updated row.

WHERESearchCondition

The search condition can contain a subquery. All rows for which the search condition is true are updated as specified in the SET clause. Rows that do not satisfy the search condition are not affected. If no rows satisfy the search condition, the table is not changed.

Description

The subquery in the SET clause of a join update does not reduce the number of rows from the target table that are to be updated. The reduction must be specified using the WHERE clause. Thus if a row from the target table qualifies the WHERE clause but the subquery returns no rows for this row, this row is updated with a NULL value in the updated column.

Examples

In this example, if a row from t1 has no match in t2, then its x1 value in the first select and its x1 and y1 values in the second select are set to NULL.

UPDATE t1 SET x1=(SELECT x2 FROM t2 WHERE id1=id2);
UPDATE t1 SET (x1,y1)=(SELECT x2,y2 FROM t2 WHERE id1=id2);

In order to restrict the UPDATE statement to update only rows from t1 that have a match in t2, a WHERE clause with a subquery has to be provided as follows:

UPDATE t1 SET x1=(SELECT x2 FROM t2 WHERE id1=id2)
WHERE id1 IN (SELECT id2 FROM t2);
UPDATE t1 SET (x1,y1)=(SELECT x2,y2 FROM t2 WHERE id1=id2)
WHERE id1 IN (SELECT id2 FROM t2);