Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
11g Release 2 (11.2.2)

Part Number E21642-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 SQL Statements

This chapter provides information about the SQL statements available in TimesTen.

SQL statements are generally considered to be either Data Manipulation Language (DML) statements or Data Definition Language (DDL) statements.

DML statements modify database objects. INSERT, UPDATE and DELETE are examples of DML statements.

DDL statements modify the database schema. CREATE TABLE and DROP TABLE are examples of DDL statements.

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:


ALTER ACTIVE STANDBY PAIR

You can change an active standby pair by:

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

Required privilege

ADMIN

SQL syntax

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

Syntax for SubscriberOperation:

{ADD | DROP } SUBSCRIBER FullStoreName

Syntax for StoreOperation:

ALTER STORE FullStoreName SET StoreAttribute

Syntax for InclusionOperation:

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

Syntax for NetworkOperation:

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

Parameters

Parameter Description
ADD SUBSCRIBER FullStoreName Indicates a subscriber database. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.
DROP SUBSCRIBER FullStoreName 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 STORE FullStoreName SET StoreAttribute 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 information on StoreAttribute clauses, see "CREATE ACTIVE STANDBY PAIR".

FullStoreName The database, specified as one of the following:
  • SELF

  • The prefix of the database file name

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 [ON Host]

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 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 MASTER FullStoreName SUBSCRIBER FullStoreName Adds NetworkOperation to replication scheme. Enables 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, "ON host" must be specified.

DROP ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName Drops NetworkOperation from replication scheme.

Can be specified more than once.

For FullStoreName, "ON host" must be specified.

MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost 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.

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

Required syntax of NetworkOperation clause. Follows MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost clause.


Description

Examples

Add a subscriber to the replication scheme.

ALTER ACTIVE STANDBY PAIR
    ADD SUBSCRIBER rep4;

Drop two subscribers from the replication scheme.

ALTER ACTIVE STANDBY PAIR
    DROP SUBCRIBER rep3
    DROP SUBSCRIBER rep4;

Alter the store attributes of the rep3 and rep4 databases.

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

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

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

Add NetworkOperation clause to active standby pair:

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

See also


CREATE ACTIVE STANDBY PAIR
DROP ACTIVE STANDBY PAIR

ALTER CACHE GROUP

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

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

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

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

Required privilege

No privilege is required for the cache group owner.

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

SQL syntax

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

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

This statement changes the AUTOREFRESH interval on the cache group:

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

This statement alters the AUTOREFRESH state:

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

Parameters

Parameter Description
[Owner.]GroupName Name assigned to the new cache group.
AUTOREFRESH Indicates that changes to Oracle tables should be automatically propagated to TimesTen. For details, see "AUTOREFRESH in cache groups".
MODE 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

See also


CREATE CACHE GROUP

ALTER FUNCTION

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

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

Required privilege

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

ALTER ANY PROCEDURE for another user's function.

SQL syntax

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

Parameters

Parameter Description
[Owner.]FunctionName Name of the function to be recompiled.
COMPILE 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.
CompilerParametersClause 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

See also


CREATE FUNCTION

ALTER PACKAGE

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

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

Required privilege

No privilege is required for the package owner.

ALTER ANY PROCEDURE for another user's package.

SQL syntax

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

Parameters

Parameter Description
[Owner.]PackageName Name of the package to be recompiled.
COMPILE 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.

CompilerParametersClause 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

See also


CREATE PACKAGE

ALTER PROCEDURE

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

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

Required privilege

No privilege is required for the procedure owner.

ALTER ANY PROCEDURE for another user's procedure.

SQL syntax

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

Parameters

Parameter Description
[Owner.]ProcedureName Name of the procedure to be recompiled.
COMPILE 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.
CompilerParametersClause 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

Examples

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

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

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

See also


CREATE PROCEDURE

ALTER REPLICATION

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

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

Required privilege

ADMIN

SQL syntax

The ALTER REPLICATION statement has the syntax:

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

Specify ElementOperation one or more times:

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

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

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

ALTER ELEMENT ElementName
    {SET NAME NewElementName | SET CheckConflicts}

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

DROP ELEMENT { ElementName | * IN FullStoreName }

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

Syntax for ReturnServiceAttribute is:

{ RETURN RECEIPT [BY REQUEST] | NO RETURN }

StoreOperation clauses:

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

Syntax for the StoreAttribute is:

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

Specify NetworkOperation one or more times:

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

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

Parameters

Parameter Description
[Owner.]ReplicationSchemeName Name assigned to the replication scheme.
ADD ELEMENT ElementName 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 ELEMENT ElementName DATASTORE

{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 SUBSCRIBER FullStoreName Indicates an additional subscriber database. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.
ALTER ELEMENT * IN FullStoreName

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 ELEMENT ElementName Name of the element to which a subscriber is to be added or dropped.
ALTER ELEMENT

ElementName1

SET NAME ElementName2

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

{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 SUBSCRIBER FullStoreName

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 Replication Guide for details.
CONFLICT REPORTING SUSPEND AT Value 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 AT Value 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.

See "Managing return service timeout errors and replication state changes" in Oracle TimesTen In-Memory Database Replication Guide for details.

DURABLE COMMIT {ON | OFF} Overrides the DurableCommits general connection attribute setting. DURABLE COMMIT ON enables durable commits regardless of whether the replication agent is running or stopped.
DROP ELEMENT * IN FullStoreName 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 ELEMENT ElementName Deletes the replication description of ElementName.
DROP SUBSCRIBER FullStoreName 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.
FAILTHRESHOLD Value 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.

See "Setting the log failure threshold" in Oracle TimesTen In-Memory Database Replication Guide for more information.

FullStoreName The database, specified as one of the following:
  • SELF

  • The prefix of the database file name

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 [ON Host]

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

MASTER FullStoreName 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 Replication Guide.

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

PROPAGATOR FullStoreName The database that receives replicated updates and passes them on to other databases.
RESUME RETURN MilliSeconds 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 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.

See "Managing return service timeout errors and replication state changes" in Oracle TimesTen In-Memory Database Replication Guide for details.

RETURN TWOSAFE [BY REQUEST] 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 Replication Guide.

RETURN WAIT TIME Seconds 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.
SUBSCRIBER FullStoreName 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.

TIMEOUT Seconds The maximum number of seconds the replication agent waits for a response from the database. Default: 120 seconds.
ADD ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName Adds NetworkOperation to replication scheme. Enables 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 MASTER FullStoreName SUBSCRIBER FullStoreName Drops NetworkOperation from replication scheme.

Can be specified more than once.

For FullStoreName, ON "host" must be specified.

MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost 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.

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

Required syntax of NetworkOperation clause. Follows MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost clause.


Description

Examples

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

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

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

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

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

ALTER REPLICATION r1
   ALTER ELEMENT e3
     SET NAME newelementname;

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

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

This element changes the port number for east.

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

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

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

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

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

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

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

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

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

Add NetworkOperation clause:

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

Drop NetworkOperation clause:

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

See also


ALTER ACTIVE STANDBY PAIR
CREATE ACTIVE STANDBY PAIR
CREATE REPLICATION
DROP ACTIVE STANDBY PAIR
DROP REPLICATION

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


ALTER SESSION

The ALTER SESSION statement changes session parameters dynamically.

Required privilege

None

SQL syntax

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

Parameters

Parameter Description
DDL_REPLICATION_ACTION={'INCLUDE'|'EXCLUDE'} 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.

This attribute is only valid if DDL_REPLICATION_LEVEL=2.

See "Making DDL changes in an active standby pair" in the Oracle TimesTen In-Memory Database Replication Guide for more information.

DDL_REPLICATION_LEVEL={1|2} 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.

See "Making DDL changes in an active standby pair" in the Oracle TimesTen In-Memory Database Replication Guide for more information.

NLS_SORT={BINARY| SortName} Indicates which collation sequence to use for linguistic comparisons.

Append _CI or _AI to either BINARY or the SortName value if you want to do case-insensitive or accent-insensitive sorting.

If you do not specify NLS_SORT, the default is BINARY.

For a complete list of supported values for SortName, see "Linguistic sorts" in Oracle TimesTen In-Memory Database Operations Guide.

For more information on case-insensitive or accent-insensitive sorting, see "Case-insensitive and accent-insensitive linguistic sorts" in Oracle TimesTen In-Memory Database Operations Guide.

NLS_LENGTH_SEMANTICS ={BYTE|CHAR} Sets the default length semantics configuration. BYTE indicates byte length semantics. CHAR indicates character length semantics. The default is BYTE.

For more information on length semantics, see "Length semantics and data storage" in Oracle TimesTen In-Memory Database Operations Guide.

NLS_NCHAR_CONV_EXCP = {TRUE|FALSE} 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.
ISOLATION_LEVEL = {SERIALIZABLE|READ COMMITTED} Sets isolation level. Change takes effect starting with next transaction.

For a descriptions of the isolation levels, see Oracle TimesTen In-Memory Database Operations Guide.

PLSQL_TIMEOUT= n 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.

For more information, see "PLSQL_OPTIMIZE_LEVEL" in Oracle TimesTen In-Memory Database Reference.

PLSCOPE_SETTINGS = '{IDENTIFIERS:ALL |IDENTIFIERS:NONE}' Controls whether or not the PL/SQL compiler generates cross-reference information. Specify IDENTIFIERS:ALL to generate cross-reference information. The default is IDENTIFIERS:NONE.

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

For more information, see "PLSCOPE_SETTINGS" in Oracle TimesTen In-Memory Database Reference.

PLSQL_CONN_MEM_LIMIT = n Specifies the maximum amount of process heap memory that PL/SQL can use for this connection. n is an integer expressed in megabytes. The default is 100.

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

For more information, see "PLSQL_CONN_MEM_LIMIT" in Oracle TimesTen In-Memory Database Reference.

REPLICATION_TRACK = TrackNumber 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.

For more information, see "Configuring user-defined parallel replication for other replication schemes" in Oracle TimesTen In-Memory Database Replication Guide.


Description

Examples

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

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

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

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

Command> ALTER SESSION SET PLSQL_TIMEOUT = 20;

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

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

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

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

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

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

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

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

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

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

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

ALTER TABLE

The ALTER TABLE statement changes an existing table definition.

Required privilege

No privilege is required for the table owner.

ALTER ANY TABLE for another user's table.

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

SQL syntax

To add one column:

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

or to add multiple columns:

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

To add a NOT NULL column (Note: The default clause is required):

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

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

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

The CompressColumns syntax is as follows:

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

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

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

To add a primary key constraint using a range index:

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

To add a primary key constraint using a hash index:

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

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

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

To remove a foreign key:

ALTER TABLE [Owner.]TableName 
DROP CONSTRAINT ForeignKeyName

To resize a hash index:

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

To change the primary key to use a hash index:

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

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

ALTER TABLE [Owner.]TableName
USE RANGE INDEX

To change the default value of a column:

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

To add or drop a unique constraint on a column:

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

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

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

To add LRU aging:

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

To add time-based aging:

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

To change the aging state:

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

To drop aging:

ALTER TABLE [Owner.]TableName
DROP AGING

To change the lifetime for time-based aging:

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

To change the cycle for time-based aging:

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

Parameters

Parameter Description
[Owner.] TableName Identifies the table to be altered.
UNIQUE Specifies that in the column ColumnName each row must contain a unique value.
MODIFY Specifies that an attribute of a given column is to be changed to a new value.
DEFAULT [DefaultVal |NULL] Specifies that the column has a default value, DefaultVal. If NULL, specifies that the default value of the columns is to be dropped. If a column with a default value of SYSDATE is added, the value of the column of the existing rows only is the system date at the time the column was added. If the default value is one of the USER functions the column value is the user value of the session that executed the ALTER TABLE statement. Currently, you cannot assign a default value for the ROWID data type.

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

ColumnName Name of the column participating in the ALTER TABLE statement. A new column cannot have the same name as an existing column or another new column. If you add a NOT NULL column, you must include the DEFAULT clause.
ColumnDataType Type of the column to be added. Some types require additional parameters. See Chapter 1, "Data Types" for the data types that can be specified.
NOT NULL If you add a column, you can specify NOT NULL for the column. If you specify NOT NULL, then you must include the DEFAULT clause.
INLINE|NOT INLINE By default, variable-length columns whose declared column length is > 128 bytes are stored out of line. Variable-length columns whose declared column length is <= 128 bytes are stored inline. The default behavior can be overridden during table creation through the use of the INLINE and NOT INLINE keywords.
COMPRESS (CompressColumns [,...]) Defines a compressed column group for a table that is enabled for compression. This can include one or more columns in the table.

Only INLINE columns are supported when you specify multiple columns in a compressed column group. You can only specify out-of-line columns in a compression group on its own.

Each compressed column group is limited to a maximum of 16 columns.

For more details on compression columns, see "In-memory columnar compression of tables".

BY DICTIONARY Defines a compression dictionary for each compressed column group.
MAXVALUES = CompressMax CompressMax is the total number of distinct values in the table and sets the size for the compressed column group pointer column to 1, 2, or 4 bytes and sets the size for the maximum number of entries in the dictionary table.

For the dictionary table, NULL is counted as one unique value.

CompressMax takes an integer between 1 and 232-1.

The maximum size defaults to size of 232-1 if the MAXVALUES clause is omitted, which uses 4 bytes for the pointer column. An error is thrown if the value is greater than 232-1.

For more details on maximum sizing for compression dictionaries, see "In-memory columnar compression of tables".

ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName

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

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

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

Specify the USE HASH INDEX clause to use a hash index for the primary key. If not specified, a range index is used for the primary key constraint. 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.

See the Definition section in "Column Definition" for more details on hash indexes and pages.

CONSTRAINT Specifies that a foreign key is to be dropped. Optionally specifies that an added foreign key is named by the user.
ForeignKeyName Name of the foreign key to be added or dropped. All foreign keys are assigned a default name by the system if the name was not specified by the user. Either the user-provided name or system name can be specified in the DROP FOREIGN KEY clause.
FOREIGN KEY Specifies that a foreign key is to be added 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 DELETE CASCADE] Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.
USE HASH INDEX PAGES = {RowPages | CURRENT} 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 RANGE 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.

For more information about LRU aging, see "Implementing aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide.

ADD AGING USE ColumnName...[ON| OFF] Adds time-based aging to an existing table that has no aging policy defined.

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

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

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

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

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

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

For more information about time-based aging, see "Implementing aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide.

LIFETIME Num1 {SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S] Specify the LIFETIME clause after the ADD AGING USE ColumnName clause if you are adding the time-based aging policy to an existing table. Specify the LIFETIME clause after the SET AGING clause to change the LIFETIME setting.

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

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

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

CYCLE Num2 {SECOND[S]| MINUTE[S]| HOUR[S]|DAY[S]} Specify the optional CYCLE clause after the LIFETIME clause if you are adding the time-based aging policy to an existing table.

CYCLE is a time-based aging attribute.

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

Specify Num2 as a positive integer constant.

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

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

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

SET AGING {ON|OFF} Changes the aging state. The aging policy must be previously defined. ON enables automatic aging. OFF disables automatic aging. 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 LIFETIME Num1 {SECOND[S]| MINUTE[S]|HOUR[S] |DAY[S]} Use this clause to change the lifetime for time-based aging.

Num1 must be a positive integer constant.

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

SET AGING CYCLE Num2 {SECOND[S]| MINUTE[S]| HOUR[S]|DAY[S]} Use this clause to change the cycle for time-based aging.

Num2 must be a positive integer constant.


Understanding partitions when using ALTER TABLE

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

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

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

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

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

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

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

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

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

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

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

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

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

ttDestroy sampledb_1122

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

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

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

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

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

Description

Examples

Add returnrate column to parts table.

ALTER TABLE parts ADD COLUMN returnrate DOUBLE;

Add numsssign and prevdept columns to contractor table.

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

Remove addr1 and addr2 columns from employee table.

ALTER TABLE employee DROP ( addr1, addr2 );

Drop the UNIQUE title column of the books table.

ALTER TABLE books DROP UNIQUE (title);

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

ALTER TABLE t1 ADD (x1 INT DEFAULT 5);

Change the default value of column x1 to 2:

ALTER TABLE t1 MODIFY (x1 DEFAULT 2);

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

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

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

1 index found on 1 table.

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

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

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

1 table found.

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

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

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

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

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

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

Alter the pkey table to use a hash index:

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

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

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

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

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

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

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

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

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

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

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

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

Drop aging on the table and redefine with LRU aging.

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

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

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

ALTER TABLE AgingDemo4
       SET AGING OFF;

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

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

Call ttAgingScheduleNow to invoke aging with an external scheduler:

Command> CALL ttAgingScheduleNow ('agingdemo4');

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

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

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

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

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

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

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

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

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

See also


CREATE TABLE
DROP TABLE

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


ALTER USER

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

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

Required privilege

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

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

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

SQL syntax

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

Parameters

Parameter Description
user Name of the user whose password is being changed.
IDENTIFIED BY Identification clause.
password |"password" 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

Examples

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

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

To change user terry to an external user:

ALTER USER terry IDENTIFIED EXTERNALLY;
User altered.

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

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

See also


CREATE USER
DROP USER
GRANT
REVOKE

CALL

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

Required privilege

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

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

SQL syntax

To call a TimesTen built-in procedure:

CALL [TimesTenBuiltIn [( arguments )] 

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

To call a PL/SQL procedure:

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

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

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

Note:

A user's own PL/SQL procedure or function takes precedence over a TimesTen built-in procedure with the same name.

Parameters

Parameter Description
TimesTenBuiltIn Name of the TimesTen built-in procedure. For a full list of TimesTen built-in procedures, see "Built-In Procedures" in the Oracle TimesTen In-Memory Database Reference.
[Owner.]ProcedureName Name of the PL/SQL procedure. You can optionally specify the owner of the procedure.
[Owner.]FunctionName Name of the PL/SQL function. You can optionally specify the owner of the function.
arguments Specify 0 or more arguments for the PL/SQL procedure or function.
INTO If the routine is a function, the INTO clause is required.
return_param Specify the host variable that stores the return value of the function.

Description

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

Examples

The following is the definition of the mytest function:

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

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

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

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

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

COMMIT

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

Required privilege

None

SQL syntax

COMMIT [WORK]

Parameters

The COMMIT statement enables the following optional keyword:

Parameter Description
[WORK] Optional clause supported for compliance with the SQL standard. COMMIT and COMMIT WORK are equivalent.

Description

Examples

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

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

See also

ROLLBACK


CREATE ACTIVE STANDBY PAIR

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

Required privilege

ADMIN

SQL syntax

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

The syntax for ReturnServiceAttribute is

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

Syntax for StoreAttribute is:

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

Syntax for NetworkOperation:

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

Parameters

Parameter Description
FullStoreName The database, specified as one of the following:
  • SELF

  • The prefix of the database file name

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 [ON Host]

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

See "Managing return service timeout errors and replication state changes" in Oracle TimesTen In-Memory Database Replication Guide for details.

RESUME RETURN Milliseconds 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 Replication Guide.

RETURN WAIT TIME Seconds 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.

SUBSCRIBER FullStoreName [,...]] A database that receives updates from a master database. FullStoreName is the database file name specified in the DataStore attribute of the DSN description.
STORE FullStoreName [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} Overrides the DurableCommits general connection attribute setting. DURABLE COMMIT ON enables durable commits regardless of whether the replication agent is running or stopped. It also enables durable commits when the ttRepStateSave built-in procedure has marked the standby database as failed.
FAILTHRESHOLD Value 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.

See "Setting the log failure threshold" in Oracle TimesTen In-Memory Database Replication Guide for more information.

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

MASTER FullStoreName 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.
PORT PortNumber 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 MASTER FullStoreName SUBSCRIBER FullStoreName Denotes the NetworkOperation clause. If specified, enables 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, you can define the following:
  • A route for the active database to the standby database and for the standby database to the active database for when failover occurs.

  • A route for a read-only subscriber to the active and standby databases.

When using active standby pairs, ROUTE should be specified at least twice for an active standby pair with no read only subscribers. Then, ROUTE should be specified twice more for each read only subscriber on the active standby pair.

For FullStoreName, ON "host" must be specified.

MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost 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.

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

Required syntax of NetworkOperation clause. Follows MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost clause.

TIMEOUT Seconds The maximum number of seconds the replication agent waits for a response from the database. Default: 120 seconds.

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

Examples

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

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

Specify NetworkOperation clause to control network interface:

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

See also


ALTER ACTIVE STANDBY PAIR
DROP ACTIVE STANDBY PAIR

CREATE CACHE GROUP

The CREATE CACHE GROUP statement:

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:

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:

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:

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

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

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

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

Explicitly loaded cache groups and dynamic cache groups

Cache groups can be explicitly or dynamically loaded.

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

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

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

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

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

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

Local and global cache groups

You can create either local or global cache groups.

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

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

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

Required privilege

CREATE CACHE GROUP or CREATE ANY CACHE GROUP and

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

SQL syntax

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

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

CREATE READONLY CACHE GROUP

For read-only cache groups, the syntax is:

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

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP

For asynchronous writethrough cache groups, the syntax is:

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

CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP

For synchronous writethrough cache groups, the syntax is:

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

CREATE USERMANAGED CACHE GROUP

For user managed cache groups, the syntax is:

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

CREATE WRITETHROUGH GLOBAL CACHE GROUP

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

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

Parameters

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

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.
INTERVAL IntervalValue 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".
REFERENCES RefTableName (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 DELETE CASCADE] Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.
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.
WHERE ExternalSearchCondition 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.

For more information about LRU aging, see "Implementing aging on a cache group" in Oracle In-Memory Database Cache User's Guide.

AGING USE ColumnName...[ON|OFF] 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.

For more information about time-based aging, see "Implementing aging on a cache group" in Oracle In-Memory Database Cache User's Guide.

LIFETIME Num1 {SECOND[S]|MINUTE[S]|HOUR[S]DAY[S]} LIFETIME is a time-based aging attribute and is a required clause.

Specify the LIFETIME clause after the AGING USE ColumnName clause.

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

[CYCLE Num2 {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

AUTOREFRESH in cache groups

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

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

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

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

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

The specified interval determines how often autorefresh occurs.

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

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

Aging in cache groups

Cache grid

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

Examples

Create a read-only cache group:

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

Create an asynchronous writethrough cache group:

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

Create a synchronous writethrough cache group:

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

Create a user managed cache group:

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

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

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

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

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

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

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

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

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

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

See also


ALTER CACHE GROUP
ALTER TABLE
DROP CACHE GROUP
FLUSH CACHE GROUP
LOAD CACHE GROUP
UNLOAD CACHE GROUP

CREATE FUNCTION

The CREATE FUNCTION statement creates a standalone stored function.

Required privilege

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

SQL syntax

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

The syntax for the InvokerRightsClause:

AUTHID {CURRENT_USER|DEFINER}

You can specify InvokerRightsClause or DETERMINISTIC in any order.

Parameters

Parameter Description
OR REPLACE 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.

For more information on PL/SQL data types, see Chapter 3, "PL/SQL Data Types" in the Oracle Database PL/SQL Language Reference.

IN| OUT |IN OUT Parameter modes.

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.

For more information on NOCOPY, see Oracle Database SQL Language Reference.

DEFAULT expr Use this clause to specify a default value for the parameter. You can specify := in place of the keyword DEFAULT.
RETURN DataType Required clause. A function must return a value. You must specify the data type of the return value of the function.

Do not specify a length, precision, or scale for the data type.

The data type is a PL/SQL data type.

For more information on PL/SQL data types, see Chapter 3, "PL/SQL Data Types" in the Oracle Database PL/SQL Language Reference.

InvokerRightsClause Lets you specify whether the SQL statements in PL/SQL functions or procedures execute with definer's or invoker's rights. The AUTHID setting affects the name resolution and privilege checking of SQL statements that a PL/SQL procedure or function issues at runtime, as follows:
  • Specify DEFINER so that SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. DEFINER is the default.

  • Specify CURRENT_USER so that SQL name resolution and privilege checking operate as though the current user (the invoker) is running it.

For more information, see the "Definer's rights and invoker's rights" section in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide or the Oracle Database PL/SQL Language Reference.

DETERMINISTIC Specify DETERMINISTIC to indicate that the function should return the same result value whenever it is called with the same values for its parameters.

For more information on the DETERMNISTIC clause, see Oracle Database SQL Language Reference.

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

Restrictions

TimesTen does not support:

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

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

Examples

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

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

See also

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


CREATE INDEX

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

Note:

Consider using a hash index if your application uses exact match search. Hash indexes provide performance gains over range indexes. Create a hash index using CREATE TABLE.

Required privilege

No privilege is required for table or materialized view owner.

INDEX for another user's table or materialized view.

SQL syntax

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

Parameters

Parameter Description
UNIQUE 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

Examples

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

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

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

Command> INDEXES REGIONS;

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

1 index found on 1 table.

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

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

Call ttRedundantIndexCheck to see warning message for this index:

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

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

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

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

Command> INDEXES redundancy;

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

1 index found on 1 table.

This unique index ensures that all part numbers are unique.

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

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

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

See also

DROP INDEX


CREATE MATERIALIZED VIEW

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

Required privilege

SQL syntax

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

Parameters

Parameter Description
[Owner.]ViewName Name assigned to the new view.
REFRESH Specifies an asynchronous materialized view.
FAST | COMPLETE Refresh methods. FAST specifies incremental refresh. COMPLETE specifies full refresh.
NEXT SYSDATE 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 for all materialized views are as follows:

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

Restrictions on synchronous materialized view and detail tables:

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

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:

Invalid materialized views

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

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

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

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

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

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

Examples

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

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

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

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

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

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

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

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

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

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

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

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

CREATE INDEX empmvindex ON empmatview2 (mvemp_id);

See also


CREATE MATERIALIZED VIEW LOG
CREATE TABLE
CREATE VIEW
DROP [MATERIALIZED] VIEW
REFRESH MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG

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

This statement also creates other objects for internal use:

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

Required privileges

SELECT on the detail table and

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

SQL syntax

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

Parameters

Parameter Description
[Owner.]TableName Name of the detail table for the materialized view
[(columnName[,...]) 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

Examples

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

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

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

CREATE MATERIALIZED  VIEW LOG ON employees WITH (email);

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

CREATE MATERIALIZED VIEW LOG ON employees;

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

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

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

Command> CREATE MATERIALIZED VIEW LOG ON employees WITH rowid;

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

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

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

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

See also


CREATE MATERIALIZED VIEW
DROP MATERIALIZED VIEW 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.

Required privilege

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

SQL syntax

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

The syntax for the InvokerRightsClause:

AUTHID {CURRENT_USER | DEFINER}

Parameters

Parameter Description
OR REPLACE 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.
InvokerRightsClause Lets you specify whether the SQL statements in PL/SQL functions or procedures execute with definer's or invoker's rights. The AUTHID setting affects the name resolution and privilege checking of SQL statements that a PL/SQL procedure or function issues at runtime, as follows:
  • Specify DEFINER so that SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. DEFINER is the default.

  • Specify CURRENT_USER so that SQL name resolution and privilege checking operate as though the current user (the invoker) is running it.

For more information, see the "Definer's rights and invoker's rights" section in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide or the Oracle Database PL/SQL Language Reference.

IS|AS Specify either IS or AS to declare the body of the function.
PlsqlPackageSpec Specifies the package specification. Can include type definitions, cursor declarations, variable declarations, constant declarations, exception declarations and PL/SQL subprogram declarations.

Description

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

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

See also

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


CREATE PACKAGE BODY

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

Required privilege

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

SQL syntax

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

Parameters

Parameter Description
OR REPLACE 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

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

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

See also


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

CREATE PROCEDURE

The CREATE PROCEDURE statement creates a standalone stored procedure.

Required privilege

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

SQL syntax

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

The syntax for the InvokerRightsClause:

AUTHID {CURRENT_USER|DEFINER}

You can specify InvokerRightsClause or DETERMINISTIC in any order.

Parameters

Parameter Description
OR REPLACE 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.

For more information on PL/SQL data types, see Chapter 3, "PL/SQL Data Types" in the Oracle Database PL/SQL Language Reference.

[IN| OUT |IN OUT] Parameter modes.

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.

For more information on NOCOPY, see Oracle Database SQL Language Reference

DEFAULT expr Use this clause to specify a DEFAULT value for the parameter. You can specify := in place of the keyword DEFAULT.
InvokerRightsClause Lets you specify whether the SQL statements in PL/SQL functions or procedures execute with definer's or invoker's rights. The AUTHID setting affects the name resolution and privilege checking of SQL statements that a PL/SQL procedure or function issues at runtime, as follows:
  • Specify DEFINER so that SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. DEFINER is the default.

  • Specify CURRENT_USER so that SQL name resolution and privilege checking operate as though the current user (the invoker) is running it.

For more information, see the "Definer's rights and invoker's rights" section in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide or the Oracle Database PL/SQL Language Reference.

DETERMINISTIC Specify DETERMINISTIC to indicate that the procedure should return the same result value whenever it is called with the same values for its parameters.

For more information on the DETERMNISTIC clause, see Oracle Database SQL Language Reference.

IS|AS Specify either IS or AS to declare the body of the procedure.
plsql_procedure_body Specifies the procedure body.

Restrictions

TimesTen does not support:

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

Description

Examples

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

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

See also


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

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:

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:

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

SQL syntax

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

Syntax for CheckConflicts is described in "CHECK CONFLICTS".

Syntax for ReturnServiceAttribute:

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

Syntax for StoreAttribute:

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

Syntax for NetworkOperation:

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

Parameters

Parameter Description
[Owner.]ReplicationSchemeName Name assigned to the new replication scheme. Replication schemes should have names that are unique from all other database objects.
CheckConflicts Check for replication conflicts when simultaneously writing to bidirectionally replicated databases. 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 Replication Guide for details.
CONFLICT REPORTING SUSPEND AT Value 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 AT Value 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 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.

See "Managing return service timeout errors and replication state changes" in Oracle TimesTen In-Memory Database Replication Guide for details.

DURABLE COMMIT {ON|OFF} Overrides the DurableCommits general connection attribute setting. DURABLE COMMIT ON enables durable commits regardless of whether the replication agent is running or stopped.
ELEMENT ElementName 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.

See "Defining replication elements" in Oracle TimesTen In-Memory Database Replication Guide for details.

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

See "Setting the log failure threshold" in Oracle TimesTen In-Memory Database Replication Guide.

FullStoreName The database, specified as one of the following:
  • SELF

  • The prefix of the database file name

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 [ON Host]

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

MASTER FullStoreName 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 Replication Guide.

PORT PortNumber 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.
PROPAGATOR FullStoreName 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 RETURN MilliSeconds 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 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

See "Managing return service timeout errors and replication state changes" in Oracle TimesTen In-Memory Database Replication Guide for details.

RETURN TWOSAFE [BY REQUEST] 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 Replication Guide.

RETURN WAIT TIME Seconds 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.
SEQUENCE [Owner.]SequenceName Define the sequence specified by [Owner.]SequenceName as element. See "Defining replication elements" in Oracle TimesTen In-Memory Database Replication Guide for details.
STORE FullStoreName Defines the attributes for a given database. Attributes include PORT, TIMEOUT and FAILTHRESHOLD. The FullStoreName must be the database specified in the DataStore attribute of the DSN description.
SUBSCRIBER FullStoreName A database that receives updates from the MASTER databases. The FullStoreName must be the database specified in the DataStore attribute of the DSN description.
TABLE [Owner.]TableName Define the table specified by [Owner.]TableName as element. See "Defining replication elements" in Oracle TimesTen In-Memory Database Replication Guide for details.
TIMEOUT Seconds The maximum number of seconds the replication agent waits for a response from the database. 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.

Note: TRANSMIT DURABLE has no effect on RETURN TWOSAFE transactions.

Note: TRANSMIT DURABLE cannot be set for active standby pairs.

See "Setting transmit durability on database elements" and "Replicating the entire master database with TRANSMIT NONDURABLE" in Oracle TimesTen In-Memory Database Replication Guide for more information.

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.

ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName Denotes the NetworkOperation clause. If specified, enables 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.

MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost 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.

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

Required syntax of NetworkOperation clause. Follows MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost clause.



CHECK CONFLICTS

Syntax

The syntax for CHECK CONFLICTS is:

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

Note:

A CHECK CONFLICT clause can only be used for elements of type TABLE.

Parameters

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

Parameter Description
CHECK CONFLICTS BY ROW TIMESTAMP 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.

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

NO CHECK Specify to suppress conflict resolution for a given element.
UPDATE BY {SYSTEM | USER} Specifies whether the timestamp values are maintained by TimesTen (SYSTEM) or the application (USER). The replicated table in the master and subscriber databases must use the same UPDATE BY specification. See "Enabling system timestamp column maintenance" and "Enabling user timestamp column maintenance" in Oracle TimesTen In-Memory Database Replication Guide for more information. The default is UPDATE BY SYSTEM.
ON EXCEPTION {ROLLBACK[WORK |NO ACTION} 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

Examples

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Replication Scheme REPL.R2:

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

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

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

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

1 replication scheme found.

Example of NetworkOperation clause with 2 MASTERIP and SUBSCRIBERIP clauses:

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

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

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

Example of using the NetworkOperation clause with multiple subscribers:

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

See also


ALTER ACTIVE STANDBY PAIR
ALTER REPLICATION
CREATE ACTIVE STANDBY PAIR
DROP ACTIVE STANDBY PAIR
DROP REPLICATION

CREATE SEQUENCE

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

Required privilege

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

SQL syntax

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

Parameters

Parameter Description
SEQUENCE [Owner.]SequenceName Name of the sequence number generator.
INCREMENT BY IncrementValue 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.
MINVALUE MinimumValue Specifies the minimum value for the sequence. The default minimum value is 1.
MAXVALUE MaximumValue 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.
CACHE CacheValue 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 WITH StartValue 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.

Description

Incrementing SEQUENCE values with CURRVAL and NEXTVAL

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

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

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

Note:

NEXTVAL cannot be used in a query on a standby node of an active standby pair.

NEXTVAL and CURRVAL can be used in:

Examples

Create a sequence.

CREATE SEQUENCE mysequence INCREMENT BY 1 MINVALUE 2 
       MAXVALUE 1000;

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

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

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

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

To create a sequence and generate a sequence number:

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

To use a sequence in an UPDATE SET clause:

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

To use a sequence in a query:

SELECT seq.CURRVAL FROM student;

See also

DROP SEQUENCE


CREATE SYNONYM

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

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

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

CREATE SYNONYM is a DDL statement.

Synonyms can be used in these SQL statements:

Required privilege

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

CREATE PUBLIC SYNONYM to create a public synonym.

SQL syntax

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

Parameters

Parameter Description
[OR REPLACE] 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

Examples

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

Command> CREATE SYNONYM synjobs FOR jobs;
Synonym created.

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

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

Create a public synonym for the employees table.

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

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

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

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

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

1 Synonyms found.

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

Command> CREATE OR REPLACE synjobs FOR employees;
Synonym created.
 
Command> DESCRIBE synjobs;
 
Synonym TTUSER.SYNJOBS:
  For Table TTUSER.EMPLOYEES
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE UNIQUE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)
 
1 Synonyms found.

See also

DROP SYNONYM


CREATE TABLE

The CREATE TABLE statement defines a table.

Required privilege

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

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

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

SQL syntax

The syntax for a persistent table is:

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

The syntax for a global temporary table is:

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

Parameters

Parameter Description
[Owner.]TableName 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.

For rules on creating names, see "Basic names".

GLOBAL TEMPORARY 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 AS SelectQuery 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 AS SelectQuery 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 AS SelectQuery clause, you do not have to specify the ColumnName. Do not specify the data type with the AS SelectQuery 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.
CONSTRAINT ForeignKeyName 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.

TimesTen supports SQL-92 "NO ACTION" update and delete rules and ON DELETE CASCADE. Foreign key constraints are not deferrable.

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 AS SelectQuery clause, you cannot define a foreign key on the table you are creating.

[ON DELETE CASCADE] Enables the ON DELETE CASCADE referential action. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.
TableCompression Defines compression at the column level, which stores data more efficiently. Eliminates redundant storage of duplicate values within columns and improves the performance of SQL queries that perform full table scans. See "In-memory columnar compression of tables" for details.
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 AS SelectQuery 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.

For more information about LRU aging, see "Implementing aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide.

[AGING USE ColumnName...[ON|OFF]] 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.

For more information about time-based aging, see "Implementing aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide.

LIFETIME Num1 {SECOND[S]| MINUTE[S]|HOUR[S]| DAY[S]} LIFETIME is a time-based aging attribute and is a required clause.

Specify the LIFETIME clause after the AGING USE ColumnName clause.

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

[CYCLE Num2 {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.

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



Column Definition

SQL syntax

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

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

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

For all LOB data types, the syntax is:

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

Parameters

The column definition has the following parameters:

Parameter Description
ColumnName 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 AS SelectQuery clause, ColumnName is optional. The number of column names must match the number of columns in SelectQuery.

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 AS SelectQuery clause, do not specify ColumnDataType.

DEFAULT DefaultVal 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. You cannot assign a default value for the ROWID data type or for columns in read-only cache groups. In addition, you cannot use a function within the DEFAULT clause.

Legal data types for DefaultVal can be one of:

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 AS SelectQuery clause, optionally, you can specify the DEFAULT clause on the table you are creating.

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 AS SelectQuery 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 AS SelectQuery 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 AS SelectQuery 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 AS SelectQuery 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 AS SelectQuery clause, optionally, you can specify PRIMARY KEY on the table you are creating.


Description


In-memory columnar compression of tables

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

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

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

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

Figure 6-1 Table Compression

Description of Figure 6-1 follows
Description of "Figure 6-1 Table Compression"

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

Syntax

The syntax for TableCompression is:

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

The CompressColumns syntax is as follows:

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

Parameters

TableCompression syntax has the following parameters:

Parameter Description
COMPRESS (CompressColumns [,...]) Defines a compressed column group for a table that is enabled for compression. This can include one or more columns in the table. However, a column can be included in only one compressed column group.

Only INLINE columns are supported when you specify multiple columns in a compressed column group. You can only specify out-of-line columns in a compression group on its own.

Each compressed column group is limited to a maximum of 16 columns.

OPTIMIZED FOR READ When specified on the CREATE TABLE statement, enables the table for compressed column groups. You can add compressed column groups at table creation with the CREATE TABLE statement or later with the ALTER TABLE statement.
BY DICTIONARY Defines a compression dictionary for each compressed column group.
MAXVALUES = CompressMax CompressMax is the total number of distinct values in the table and sets the size for the compressed column group pointer column to 1, 2, or 4 bytes and sets the size for the maximum number of entries in the dictionary table.

For the dictionary table, NULL is counted as one unique value.

CompressMax takes an integer between 1 and 232-1.

The maximum size defaults to size of 232-1 if the MAXVALUES clause is omitted, which uses 4 bytes for the pointer column. An error is thrown if the value is greater than 232-1.


Description

Examples

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

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

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

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

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

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

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

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

A hash index is created on the column vendornumber.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

This example shows how time resolution works with aging.

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

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

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

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

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

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

CREATE TABLE agingdemo2
        (agingid NUMBER NOT NULL PRIMARY KEY,
        name  VARCHAR2 (20),
        agingcolumn TIMESTAMP NOT NULL
        )
        AGING USE agingcolumn LIFETIME 3 DAYS OFF;
ALTER TABLE agingdemo2
        ADD AGING LRU;
 2980: Cannot add aging policy to a table with an existing aging policy. Have to
drop the old aging first
The command failed.
DROP aging on the table and redefine with LRU aging.
ALTER TABLE agingdemo2
        DROP AGING;
ALTER TABLE agingdemo2
        ADD AGING LRU;
Command> DESCRIBE agingdemo2;
Table USER.AGINGDEMO2:
  Columns:
   *AGINGID                         NUMBER NOT NULL
    NAME                            VARCHAR2 (20) INLINE
    AGINGCOLUMN                     TIMESTAMP (6) NOT NULL
  Aging lru on
1 table found.
(primary key columns are indicated with *)

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

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

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

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

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

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

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

Command> DESCRIBE employees;
Table SAMPLEUSER.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE UNIQUE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)

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

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

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

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

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

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

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

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

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

See also


ALTER TABLE
DROP TABLE
TRUNCATE TABLE
UPDATE

CREATE USER

The CREATE USER statement creates a user of a TimesTen database.

Required privilege

ADMIN

SQL syntax

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

Parameters

Parameter Description
user Name of the user that is being added to the database.
IDENTIFIED Identification clause.
BY {password |"password"} 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

Examples

To create the internal user terry with the password "secret", use:

CREATE USER terry IDENTIFIED BY "secret";
User created.

Verify that user terry has been created:

Command> SELECT * FROM sys.all_users WHERE username='TERRY';
< TERRY, 11, 2009-05-12 10:28:04.610353 >
1 row found.

To identify the external user pat to the TimesTen database, use:

CREATE USER pat IDENTIFIED EXTERNALLY;
User created.

See also


ALTER USER
DROP USER
GRANT
REVOKE

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 [Owner.]ViewName AS SelectQuery

Parameters

Parameter Description
[Owner.]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.

Examples

Create a nonmaterialized view from the employees table.

Command> CREATE VIEW v1 AS SELECT employee_id, email FROM employees;
Command> SELECT FIRST 5 * FROM v1;
< 100, SKING >
< 101, NKOCHHAR >
< 102, LDEHAAN >
< 103, AHUNOLD >
< 104, BERNST >
5 rows found.

Create a nonmaterialized view tview with column max1 from an aggregate query on the table t1.

CREATE VIEW tview (max1) AS SELECT MAX(x1) FROM t1;

See also


CREATE MATERIALIZED VIEW
CREATE TABLE
DROP [MATERIALIZED] VIEW

DELETE

The DELETE statement deletes rows from a table.

Required privilege

No privilege is required for the table owner.

DELETE on the table for another user's table.

SQL syntax

DELETE [FIRST NumRows] FROM [Owner.]TableName [CorrelationName] 
[WHERE SearchCondition]
[RETURNING|RETURN Expression[,...]INTO DataItem[,...]]

Parameters

Parameter Description
FIRST NumRows Specifies the number of rows to delete. FIRST NumRows 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.

CorrelationName specifies 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.
Expression Valid expression syntax. See Chapter 3, "Expressions".
DataItem Host variable or PL/SQL variable that stores the retrieved Expression value.

Description

Examples

Rows for orders whose quantity is less than 50 are deleted.

DELETE FROM purchasing.orderitems
WHERE quantity < 50;

The following query deletes all the duplicate orders assuming that id is not a primary key:

DELETE FROM orders a
WHERE EXISTS (SELECT 1 FROM orders b
WHERE a.id = b.id and a.rowid < b.rowid);

The following sequence of statements causes a foreign key violation.

CREATE TABLE master (name CHAR(30), id CHAR(4) NOT NULL PRIMARY KEY);
CREATE TABLE details
  (masterid CHAR(4),description VARCHAR(200), 
   FOREIGN KEY (masterid) REFERENCES master(id));
INSERT INTO master('Elephant', '0001');
INSERT INTO details('0001', 'A VERY BIG ANIMAL');
DELETE FROM master WHERE id = '0001';

If you attempt to delete a "busy" table, an error results. In this example, t1 is a "busy" table that is a parent table with foreign key constraints based on it.

CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL,
       PRIMARY KEY (a));
CREATE TABLE t2 (c INT NOT NULL, 
       FOREIGN KEY (c) REFERENCES t1(a));
INSERT INTO t1 VALUES (1,1);
INSERT INTO t2 VALUES (1);
DELETE FROM t1;

An error is returned:

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.

Command> VARIABLE empid NUMBER(6) NOT NULL;
Command> VARIABLE name VARCHAR2(25) INLINE NOT NULL;
Command> DELETE FROM employees WHERE last_name='Ernst'
       > RETURNING employee_id, last_name INTO :empid,:name;
1 row deleted.
Command> PRINT empid name;
EMPID                : 104
NAME                 : Ernst

DROP ACTIVE STANDBY PAIR

This statement drops an active standby pair replication scheme.

Required privilege

ADMIN

SQL syntax

DROP ACTIVE STANDBY PAIR

Parameters

DROP ACTIVE STANDBY PAIR has no parameters.

Description

The active standby pair is dropped, but all objects such as tables, cache groups, and materialized views still exist on the database on which the statement was issued.

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

See also


ALTER ACTIVE STANDBY PAIR
CREATE ACTIVE STANDBY PAIR

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

Examples

DROP CACHE GROUP westerncustomers;

See also


ALTER CACHE GROUP
CREATE CACHE GROUP

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

Examples

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

Command> DROP FUNCTION myfunc;
 
Function dropped.

If PL/SQL is not enabled in your database, TimesTen returns an error:

Command> DROP FUNCTION myfunc;
 8501: PL/SQL feature not installed in this TimesTen database
The command failed.

See also

CREATE FUNCTION


DROP INDEX

The DROP INDEX statement removes the specified index.

Required privilege

No privilege is required for the index owner.

DROP ANY INDEX for another user's index.

SQL syntax

DROP INDEX [Owner.]IndexName [FROM [Owner.]TableName]

Parameters

Parameter Description
[Owner.]IndexName 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

Examples

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

DROP INDEX partsorderedindex
   FROM purchasing.orderitems;

or

DROP INDEX purchasing.partsorderedindex;

See also

CREATE INDEX


DROP [MATERIALIZED] VIEW

The DROP [MATERIALIZED] VIEW statement removes the specified view, including any hash indexes and any range indexes associated with it.

Required privilege

SQL syntax

DROP [MATERIALIZED] VIEW [Owner.]ViewName

Parameters

Parameter Description
MATERIALIZED Specifies that the view is materialized.
[Owner.]ViewName Identifies the view to be dropped.

Description

When you perform a DROP VIEW operation on a materialized view, the detail tables are updated and locked. An error may result if the detail table was already locked by another transaction.

Examples

The following statement drops the custorder view.

DROP VIEW custorder;

See also


CREATE MATERIALIZED VIEW
CREATE VIEW

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 [Owner.]TableName

Parameters

Parameter Description
[Owner.]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.

Examples

DROP MATERIALIZED VIEW LOG ON employees;

See also


CREATE MATERIALIZED VIEW LOG
CREATE MATERIALIZED VIEW
DROP [MATERIALIZED] VIEW

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

Example

The following statement drops the body of package samplePackage:

Command> DROP PACKAGE BODY SamplePackage;
Package body dropped.

To drop both the specification and body of package samplepackage:

Command> DROP PACKAGE samplepackage;
Package dropped.

See also

CREATE PACKAGE


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

Examples

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

Command> DROP PROCEDURE myproc;
Procedure dropped.

If PL/SQL is not enabled in your database, TimesTen returns an error:

Command> DROP PROCEDURE myproc;
 
 8501: PL/SQL feature not installed in this TimesTen databaseThe command failed.

See also

CREATE PROCEDURE


DROP REPLICATION

The DROP REPLICATION statement destroys a replication scheme and removes it from the executing database.

Required privilege

ADMIN

SQL syntax

DROP REPLICATION [Owner.]ReplicationSchemeName

Parameters

Parameter Description
[Owner.]ReplicationSchemeName Name assigned to the replication scheme.

Description

Dropping the last replication scheme at a database does not delete the replicated tables. These tables exist and persist at a database whether or not any replication schemes are defined.

Examples

The following statement erases the executing database's knowledge of replication scheme, r:

DROP REPLICATION r;

See also


ALTER REPLICATION
CREATE REPLICATION

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

Examples

The following statement drops mysequence:

DROP SEQUENCE mysequence;

See also

CREATE 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 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.]SynonymName

To drop a public synonym:

DROP PUBLIC SYNONYM SynonymName

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.
SynonymName 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 SYNONYM ttuser.syntab;
Synonym dropped.

See also

CREATE SYNONYM


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

Examples

CREATE TABLE vendorperf
   (ordernumber INTEGER,
    delivday TT_SMALLINT,
    delivmonth TT_SMALLINT,
    delivyear TT_SMALLINT,
    delivqty TT_SMALLINT,
    remarks VARCHAR2(60));
CREATE UNIQUE INDEX vendorperfindex ON vendorperf (ordernumber);

The following statement drops the table and index.

DROP TABLE vendorperf ;

DROP USER

The DROP USER statement removes a user from the database.

Required privilege

ADMIN

SQL syntax

DROP USER user

Parameters

Parameter Description
user Name of the user that is being dropped from the database.

Description

Before you can drop a user:

Examples

Drop user terry from the database:

DROP USER terry;
User dropped.

See also


CREATE USER
ALTER USER
GRANT
REVOKE

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 ID ColumnValueList The WITH ID clauses enables 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

Restrictions

Examples

FLUSH CACHE GROUP marketbasket;

FLUSH CACHE GROUP marketbasket
WITH ID(10);

See also


CREATE CACHE GROUP

GRANT

The GRANT statement assigns one or more privileges to a user.

Required privilege

ADMIN to grant system privileges.

ADMIN or the object owner to grant object privileges.

SQL syntax

GRANT {SystemPrivilege [,...] | ALL [PRIVILEGES]} [...] TO {user |PUBLIC} [,...]

GRANT {{ObjectPrivilege [,...] | ALL [PRIVILEGES]} ON {[owner.]object}[,...]} TO
{user | PUBLIC} [,...]

Parameters

The following parameters are for granting system privileges:

Parameter Description
SystemPrivilege See "System privileges" for a list of acceptable values.
ALL [PRIVILEGES] Assigns all system privileges to the user.
user Name of the user to whom privileges are being granted. The user name must first have been introduced to the TimesTen database by a CREATE USER statement.
PUBLIC Specifies that the privilege is granted to all users.

The following parameters are for granting object privileges:

Parameter Description
ObjectPrivilege See "Object privileges" for a list of acceptable values.
ALL [PRIVILEGES] Assigns all object privileges to the user.
[owner.]object object is the name of the object on which privileges are being granted. owner is the owner of the object. If owner is not specified, the user who is granting the privilege is the owner.
user Name of the user to whom privileges are being granted. The user must exist in the database.
PUBLIC Specifies that the privilege is granted to all users.

Description

Examples

Grant the ADMIN privilege to the user terry:

GRANT admin TO terry;

Assuming the grantor has ADMIN privilege, grant the SELECT privilege to user terry on the customers table owned by user pat:

GRANT SELECT ON pat.customers TO terry;

Grant an object privilege to user terry:

GRANT SELECT ON emp_details_view TO terry;

See also


CREATE USER
ALTER USER
DROP USER
REVOKE
The PUBLIC role

INSERT

The INSERT statement adds rows to a table.

The following expressions can be used in the VALUES clause of an INSERT statement:

Required privilege

No privilege is required for the table owner.

INSERT for another user's table.

SQL syntax

INSERT INTO [Owner.]TableName [(Column [,...])]
VALUES (SingleRowValues)
[RETURNING|RETURN Expression[,...] INTO DataItem[,...]]

The SingleRowValues parameter has the syntax:

{NULL|{?|:DynamicParameter}|{Constant}| DEFAULT}[,...]

Parameters

Parameter Description
Owner The owner of the table into which data is inserted.
TableName Name of the table into which data is inserted.
Column Each column in this list is assigned a value from SingleRowValues.

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

Placeholder for a dynamic parameter in a prepared SQL statement. The value of the dynamic parameter is supplied when the statement is executed.
Constant A specific value. See "Constants".
DEFAULT Specifies that the column should be updated with the default value.
Expression Valid expression syntax. See Chapter 3, "Expressions".
DataItem Host variable or PL/SQL variable that stores the retrieved Expression value.

Description

Examples

A new single row is added to the purchasing.vendors table.

INSERT INTO purchasing.vendors
VALUES (9016,
       'Secure Systems, Inc.',
       'Jane Secret',
       '454-255-2087',
       '1111 Encryption Way',
       'Hush',
       'MD',
       '00007',
       'discount rates are secret');

:pno and :pname are dynamic parameters whose values are supplied at runtime.

INSERT INTO purchasing.parts (partnumber, partname)
  VALUES (:pno, :pname);

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.

Command> VARIABLE sal12 NUMBER(8,2);
Command> VARIABLE jobid VARCHAR2(10) INLINE NOT NULL;

Command> INSERT INTO employees(employee_id, last_name, email, hire_date, 
       > job_id, salary)
       > VALUES (211,'Doe','JDOE',sysdate,'ST_CLERK',2400)
       > RETURNING salary*12, job_id INTO :sal12,:jobid;
1 row inserted.

PRINT sal12 jobid;
SAL12                 : 28800
JOBID                 : ST_CLERK

See also


CREATE TABLE
INSERT...SELECT
Chapter 3, "Expressions"

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.
InsertQuery Any supported SELECT query. See "SELECT".

Description

Examples

New rows are added to the purchasing.parts table that describe which parts are delivered in 20 days or less.

INSERT INTO purchasing.parts
SELECT partnumber, deliverydays
FROM purchasing.supplyprice
WHERE deliverydays < 20;

LOAD CACHE GROUP

The LOAD CACHE GROUP statement loads data from an Oracle table into a TimesTen cache group. The load operation is local. It is not propagated across cache grid members.

Required privilege

No privilege is required for the cache group owner.

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

SQL syntax

LOAD CACHE GROUP [Owner.]GroupName 
[WHERE ConditionalExpression] 
COMMIT EVERY n ROWS
[PARALLEL NumThreads]

or

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

Parameters

Parameter Description
[Owner.]GroupName Name assigned to the cache group.
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 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.
[PARALLEL NumThreads] 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 ID ColumnValueList The WITH ID clauses enables 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

Restrictions

Examples

CREATE CACHE GROUP recreation.cache
    FROM recreation.clubs (
        clubname CHAR(15) NOT NULL,
        clubphone SMALLINT,
        activity CHAR(18),
        PRIMARY KEY(clubname))
    WHERE (recreation.clubs.activity IS NOT NULL);

LOAD CACHE GROUP recreation.cache
    COMMIT EVERY 30 ROWS;

Use the HR schema to illustrate the use of the PARALLEL clause with the LOAD CACHE GROUP statement. The COMMIT EVERY n ROWS 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.

Command> CACHEGROUPS;

Cache Group SAMPLEUSER.CG2:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 1.5 Minutes

  Root Table: SAMPLEUSER.COUNTRIES
  Table Type: Read Only

  Child Table: SAMPLEUSER.LOCATIONS
  Table Type: Read Only

  Child Table: SAMPLEUSER.DEPARTMENTS
  Table Type: Read Only

1 cache group found.

Command> UNLOAD CACHE GROUP cg2;
25 cache instances affected.
Command> COMMIT;
Command> LOAD CACHE GROUP cg2 COMMIT EVERY 10 ROWS PARALLEL 2;
25 cache instances affected.
Command> COMMIT;

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:

LOAD CACHE GROUP new_customers WHERE (oratt.customer.cust_num >= 5000) COMMIT
EVERY 256 ROWS;

See also


REFRESH CACHE GROUP
UNLOAD CACHE GROUP

MERGE

The MERGE statement enables 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.

SQL syntax

MERGE INTO [Owner.]TargetTableName [Alias] USING
    {[Owner.]SourceTableName|(Subquery)}[Alias] ON (Condtion)
    {MergeUpdateClause MergeInsertClause |
     MergeInsertClause MergeUpdateClause |
     MergeUpdateClause | MergeInsertClause
    }

The syntax for MergeUpdateClause is as follows:

WHEN MATCHED THEN UPDATE SET SetClause [WHERE Condition1]

The syntax for MergeInsertClause is as follows:

WHEN NOT MATCHED THEN INSERT [Columns [,...]] VALUES
    ( {{Expression | DEFAULT|NULL} [,...] }) [WHERE Condition2]

Parameters

Parameter Description
[Owner.]TargetTableName 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.
SET SetClause Clause used with the UPDATE statement. For information on the UPDATE statement, see "UPDATE".
[WHERE Condition1] 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".
[WHERE Condition2] 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

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

Command> CREATE TABLE mergedualex (col1 TT_INTEGER NOT NULL, 
       > col2 TT_INTEGER, PRIMARY KEY (col1));
Command> MERGE INTO mergedualex USING dual ON (col1 = :v1)
       > WHEN MATCHED THEN UPDATE SET col2 = col2 + 1
       > WHEN NOT MATCHED THEN INSERT VALUES (:v1, 1);

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1  'V1' (TT_INTEGER) > 10
1 row merged.
Command> SELECT * FROM mergedualex;
< 10, 1 >
1 row found.

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:

Command> CREATE TABLE contacts (employee_id NUMBER (6) NOT NULL PRIMARY KEY, 
       > manager_id NUMBER (6));
Command> SELECT employee_id, manager_id FROM employees WHERE employee_id =101;
< 101, 100 >
1 row found.
Command> INSERT INTO contacts VALUES (101,null);
1 row inserted.
Command> SELECT COUNT (*) FROM employees;
< 107 >
1 row found.
Command> MERGE INTO contacts c
       >   USING employees e
       >   ON (c.employee_id = e.employee_id)
       > WHEN MATCHED THEN
       >   UPDATE SET c.manager_id = e.manager_id
       > WHEN NOT MATCHED THEN
       >   INSERT (employee_id, manager_id)
       >   VALUES (e.employee_id, e.manager_id);
107 rows merged.
Command> SELECT COUNT (*) FROM contacts;
< 107 >
1 row found.
Command> SELECT FIRST 3 employee_id,manager_id FROM employees;
< 100, <NULL> >
< 101, 100 >
< 102, 100 >
3 rows found.
Command> SELECT FIRST 3 employee_id, manager_id FROM contacts;
< 100, <NULL> >
< 101, 100 >
< 102, 100 >
3 rows found.

REFRESH CACHE GROUP

The REFRESH CACHE GROUP statement is equivalent to an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement.

Required privilege

SQL syntax

REFRESH CACHE GROUP [Owner.]GroupName 
[WHERE ConditionalExpression]
COMMIT EVERY n ROWS
[PARALLEL NumThreads]

or

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

Parameters

Parameter Description
[Owner.]GroupName Name assigned to the cache group.
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 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.
[PARALLEL NumThreads] 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 ID ColumnValueList The WITH ID clauses enables 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

Restrictions

Examples

REFRESH CACHE GROUP recreation.cache COMMIT EVERY 30 ROWS;

Is equivalent to:

UNLOAD CACHE GROUP recreation.cache;
LOAD CACHE GROUP recreation.cache COMMIT EVERY 30 ROWS;

Use the HR schema to illustrate the use of the PARALLEL clause with the REFRESH CACHE GROUP statement. The COMMIT EVERY n ROWS (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.

Command> CACHEGROUPS;

Cache Group SAMPLEUSER.CG2:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 1.5 Minutes

  Root Table: SAMPLEUSER.COUNTRIES
  Table Type: Read Only

  Child Table: SAMPLEUSER.LOCATIONS
  Table Type: Read Only

  Child Table: SAMPLEUSER.DEPARTMENTS
  Table Type: Read Only

1 cache group found.
Command> REFRESH CACHE GROUP cg2 COMMIT EVERY 20 ROWS PARALLEL 2;
25 cache instances affected.

See also


ALTER CACHE GROUP
CREATE CACHE GROUP
DROP CACHE GROUP
FLUSH CACHE GROUP
LOAD CACHE GROUP
UNLOAD CACHE GROUP

REFRESH MATERIALIZED VIEW

The REFRESH MATERIALIZED VIEW statement refreshes an asynchronous materialized view manually.

Required privilege

Required privilege on the materialized view log tables:

Required privilege on the 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.

Examples

REFRESH MATERALIZED VIEW bookorders;

See also


CREATE MATERIALIZED VIEW
DROP [MATERIALIZED] VIEW

REVOKE

The REVOKE statement removes one or more privileges from a user.

Required privilege

ADMIN to revoke system privileges.

ADMIN or object owner to revoke object privileges.

SQL syntax

REVOKE {SystemPrivilege [,...] | ALL [PRIVILEGES]} FROM {User |PUBLIC} [,...]

REVOKE {{ObjectPrivilege [,...] | ALL [PRIVILEGES]} ON {[Owner.Object}} [,...]
FROM {user | PUBLIC}[,...]

Parameters

The following parameters are for revoking system privileges:

Parameter Description
SystemPrivilege See "System privileges" for a list of acceptable values.
ALL [PRIVILEGES] Revokes all system privileges from the user.
User Name of the user from whom privileges are being revoked. The user name must first have been introduced to the TimesTen database by a CREATE USER statement.
PUBLIC Specifies that the privilege is revoked for all users.

The following parameters are for revoking object privileges:

Parameter Description
ObjectPrivilege See "Object privileges" for a list of acceptable values.
ALL [PRIVILEGES] Revokes all object privileges from the user.
User 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

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:

REVOKE update ON pat.customers FROM terry;

See also


ALTER USER
CREATE USER
DROP USER
GRANT
"The PUBLIC role"

ROLLBACK

Use the ROLLBACK statement to undo work done in the current transaction.

Required privilege

None

SQL syntax

ROLLBACK [WORK]

Parameters

The ROLLBACK statement enables the following optional keyword:

Parameter Description
[WORK] Optional clause supported for compliance with the SQL standard. ROLLBACK and ROLLBACK WORK are equivalent.

Description

When the PassThrough connection attribute is specified with a value greater than zero, the Oracle transaction will also be rolled back.

A rollback closes all open cursors.

Examples

Insert a row into the regions table of the HR schema and then roll back the transaction. First set AUTOCOMMIT to 0:

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

See also


COMMIT

SELECT

The SELECT statement retrieves data from one or more tables. The retrieved data is presented in the form of a table that is called the result table, result set, or query result.

Required privilege

No privilege is required for the object owner.

SELECT for another user's object.

SELECT...FOR UPDATE also requires UPDATE privilege for another user's object.

SQL syntax

The general syntax for a SELECT statement is the following:

[WithClause] SELECT [FIRST NumRows | ROWS m TO n] [ALL | DISTINCT] SelectList
  FROM TableSpec [,...]
  [WHERE SearchCondition]
  [GROUP BY GroupByClause [,...] [HAVING SearchCondition]]
  [ORDER BY OrderByClause [,...]]
  [FOR UPDATE [OF [[Owner.]TableName.]ColumnName [,...]] 
    [NOWAIT | WAIT Seconds] ]

The syntax for a SELECT statement that contains the set operators UNION, UNION ALL, MINUS, or INTERSECT is as follows:

SELECT [ROWS m TO n] [ALL] SelectList
  FROM TableSpec [,...]
    [WHERE SearchCondition]
    [GROUP BY GroupByClause [,...] [HAVING SearchCondition] [,...]]
  {UNION [ALL] | MINUS | INTERSECT}
  SELECT [ROWS m TO n] [ALL] SelectList
    FROM TableSpec [,...]
     [WHERE SearchCondition]
     [GROUP BY GroupByClause [,...] [HAVING SearchCondition [,...] ] ]
     [ORDER BY OrderByClause [,...] ]

The syntax for OrderByClause is as follows:

{ColumnID|ColumnAlias|Expression} [ASC|DESC] [NULLS { FIRST|LAST }]

Parameters

Parameter Description
[WithClause] The WITH clause, also known as subquery factoring, enables you to assign a name to a subquery block, which can subsequently be referenced multiple times within the top-level SELECT statement. The syntax of the WITH clause is presented under "WithClause".
FIRST NumRows 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.
ROWS m TO n 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 TO n returns the same rows as SELECT FIRST NumRows 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.

You cannot use SELECT ... on a LOB column.

SelectList Specifies how the columns of the query result are to be derived. The syntax of select list is presented under "SelectList".
FROM TableSpec 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".

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

See Chapter 5, "Search Conditions" for more information on search conditions.

GROUP BY GroupByClause [,...] 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 syntax and description for the GROUP BY clause is described in "GROUP BY clause".
HAVING SearchCondition 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 OrderByClause [,...] 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.

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]} [,...]

ASC|DESC For each column designated in the ORDER BY clause, 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.
NULLS { FIRST | LAST } Valid with ORDER BY clause and is optional. If you specify ASC or DESC, NULLS FIRST or NULLS LAST must follow ASC or DESC.

Specify NULLS FIRST to have rows with NULL values returned first in your ordered query. Specify NULLS LAST to have rows with NULL values returned last in your ordered query. NULLS LAST is the default when rows are returned in ascending order. NULLS FIRST is the default when rows are returned in descending order.

If you specify the ORDER BY clause without the ASC or DESC clause and without the NULLS FIRST or NULLS LAST clause, the default ordering sequence is ascending NULLS LAST.

FOR UPDATE

[OF [[Owner.]

TableName.]

ColumnName [,...]]

[NOWAIT | WAIT Seconds]

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 | WAIT Seconds ]

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

  • WAIT Seconds 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 FIRST NumRows.

  • 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

Examples

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

SELECT MAX(salary) AS max_salary 
FROM employees 
WHERE employees.hire_date > '2000-01-01 00:00:00';
< 10500 >
1 row found.

This example uses two tables, orders and lineitems.

The orders table and lineitems table are created as follows:

CREATE TABLE orders(orderno INTEGER, orderdate DATE, customer CHAR(20));

CREATE TABLE lineitems(orderno INTEGER, lineno INTEGER, 
  qty INTEGER, unitprice DECIMAL(10,2));

Thus for each order, there is one record in the orders table and a record for each line of the order in lineitems.

To find the total value of all orders entered since the beginning of the year, use the HAVING clause to select only those orders that were entered on or after January 1, 2000:

SELECT o.orderno, customer, orderdate, SUM(qty * unitprice)
FROM orders o, lineitems l
WHERE o.orderno=l.orderno
GROUP BY o.orderno, customer, orderdate
HAVING orderdate >= DATE '2000-01-01';

Consider this query:

SELECT * FROM tablea, tableb
WHERE tablea.column1 = tableb.column1 AND tableb.column2 > 5
FOR UPDATE;

The query locks all rows in tablea where:

The query also locks all rows in tableb where:

If no WHERE clause is specified, all rows in both tables are locked.

This example demonstrates the (+) join operator:

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

The following query returns an error because an outer join condition cannot be connected by OR:

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

The following query is valid:

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

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, t2
WHERE t1.x = t2.x(+) AND t2.y = 3;

For more join examples, see "JoinedTable".

This example returns the current sequence value in the student table:

SELECT SEQ.CURRVAL FROM student;

The following query produces a derived table because it contains a SELECT statement in the FROM clause:

SELECT * FROM t1, (SELECT MAX(x2) maxx2 FROM t2) tab2 
WHERE t1.x1 = tab2.maxx2;

The following query joins the results of two SELECT statements:

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.

Command> SELECT department_id FROM employees INTERSECT SELECT department_id 
       > FROM departments;
< 10 >
< 20 >
< 30 >
< 40 >
< 50 >
< 60 >
< 70 >
< 80 >
< 90 >
< 100 >
< 110 >
11 rows found.
Command> SELECT DISTINCT department_id FROM employees;
< 10 >
< 20 >
< 30 >
< 40 >
< 50 >
< 60 >
< 70 >
< 80 >
< 90 >
< 100 >
< 110 >
< <NULL> >
12 rows found.

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 NULL department_id value in the employees table is the only row returned.

Command> SELECT department_id FROM employees 
       > MINUS SELECT department_id FROM departments;
< <NULL> >
1 row found.

The following example illustrates the use of the SUBSTR expression in a GROUP BY clause and the use of a subquery in a HAVING clause. The first 10 rows are returned.

Command> SELECT ROWS 1 TO 10 SUBSTR (job_id, 4,10), department_id, manager_id, 
       > SUM (salary) FROM employees
       > GROUP BY SUBSTR (job_id,4,10),department_id, manager_id
       > HAVING (department_id, manager_id) IN
       > (SELECT department_id, manager_id FROM employees x
       > WHERE x.department_id = employees.department_id)
       > ORDER BY SUBSTR (job_id, 4,10),department_id,manager_id;
< ACCOUNT, 100, 108, 39600 >
< ACCOUNT, 110, 205, 8300 >
< ASST, 10, 101, 4400 >
< CLERK, 30, 114, 13900 >
< CLERK, 50, 120, 22100 >
< CLERK, 50, 121, 25400 >
< CLERK, 50, 122, 23600 >
< CLERK, 50, 123, 25900 >
< CLERK, 50, 124, 23000 >
< MAN, 20, 100, 13000 >
10 rows found.

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.

Command> SELECT FIRST 5 last_name FROM employees FOR UPDATE WAIT 10;
< King >
< Kochhar >
< De Haan >
< Hunold >
< Ernst >
5 rows found.

The next example locks the departments table for update. If the selected rows are locked by another process, an error is returned if the lock is not available. This is because NOWAIT is specified.

Command> SELECT FIRST 5 last_name e FROM employees e, departments d 
       > WHERE e.department_id = d.department_id 
       > FOR UPDATE OF d.department_id NOWAIT;
< Whalen >
< Hartstein >
< Fay >
< Raphaely >
< Khoo >
5 rows found.

Use the HR schema to illustrate the use of a subquery with the FOR UPDATE clause:

Command> SELECT employee_id, job_id FROM job_history 
       > WHERE (employee_id, job_id) NOT IN (SELECT employee_id, job_id 
       > FROM employees) 
       > FOR UPDATE;
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.

Use a dynamic parameter placeholder for SELECT ROWS m TO n and SELECT FIRST:

Command> SELECT ROWS ? TO ? employee_id FROM employees;

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 (TT_INTEGER) > 1
Enter Parameter 2 (TT_INTEGER) > 3
< 100 >
< 101 >
< 102 >
3 rows found.
Command> SELECT ROWS :a TO :b employee_id FROM employees;

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 (TT_INTEGER) > 1
Enter Parameter 2 (TT_INTEGER) > 3
< 100 >
< 101 >
< 102 >
3 rows found.
Command> SELECT FIRST ? employee_id FROM employees;

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 (TT_INTEGER) > 3
< 100 >
< 101 >
< 102 >
3 rows found.

The following example illustrates the use of NULLS LAST in the ORDER BY clause. Query the employees table to find employees with a commission percentage greater than .30 or a commission percentage that is NULL. Select the first seven employees and order by commission_pct and last_name. Order commision_pct in descending order and use NULLS LAST to display rows with NULL values last in the query. Output commission_pct and last_name.

Command> SELECT FIRST 7 commission_pct,last_name
         FROM employees where commission_pct > .30
         OR commission_pct IS NULL
         ORDER BY commission_pct DESC NULLS LAST,last_name;
< .4, Russell >
< .35, King >
< .35, McEwen >
< .35, Sully >
< <NULL>, Atkinson >
< <NULL>, Austin >
< <NULL>, Baer >
7 rows found.

WithClause

Syntax

WithClause has the following syntax:

WITH QueryName AS ( Subquery ) [, QueryName AS ( Subquery )] ...

Parameters

WithClause has the following parameter:

Parameter Description
QueryName AS (Subquery) Specifies an alias for a subquery that can be used multiple times within the SELECT statement.

Description

Subquery factoring provides the WITH clause that enables you to assign a name to a subquery block, which can subsequently be referenced multiple times within the main SELECT query. The query name is visible to the main query and any subquery contained in the main query.

The WITH clause can only be defined as a prefix to the main SELECT statement.

Subquery factoring is useful in simplifying complex queries that use duplicate or complex subquery blocks in one or more places. In addition, TimesTen uses subquery factoring to optimize the query by evaluating and materializing the subquery block once and providing the result for each reference in the SELECT statement.

Restrictions using the WITH clause:

Example

The following example creates the query names dept_costs and avg_cost for the initial query block, then uses these names in the body of the main query.

Command> WITH dept_costs AS (
> SELECT department_name, SUM(salary) dept_total
> FROM employees e, departments d
> WHERE e.department_id = d.department_id
> GROUP BY department_name),
> avg_cost AS (
> SELECT SUM(dept_total)/COUNT(*) avg
> FROM dept_costs)
> SELECT * FROM dept_costs
> WHERE dept_total >
> (SELECT avg FROM avg_cost)
> ORDER BY department_name;

> DEPARTMENT_NAME DEPT_TOTAL
-------------------------------
Sales 304500
Shipping 156400

SelectList

SQL syntax

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

{* | [Owner.]TableName.* | 
  { Expression | [[Owner.]TableName.]ColumnName |
    [[Owner.]TableName.]ROWID | NULL
  }
  [[AS] ColumnAlias] } [,...]

Parameters

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. See "GROUP BY clause" for more information on the GROUP BY 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. See "Incrementing SEQUENCE values with CURRVAL and NEXTVAL" for more details.
[[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

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:

SELECT partnumber, deliverydays
FROM purchasing.supplyprice
WHERE deliverydays < 20;

Multiple rows may be returned for a single part.

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.

SELECT partnumber, vendorname, s.vendornumber,vendorcity
  FROM purchasing.supplyprice s, purchasing.vendors v
  WHERE s.vendornumber = v.vendornumber AND vendorstate = 'CA'
ORDER BY partnumber, vendorname;

The following query joins table purchasing.parts to itself to determine which parts have the same sales price as the part whose serial number is '1133-P-01'.

SELECT q.partnumber, q.salesprice
  FROM purchasing.parts p, purchasing.parts q
  WHERE p.salesprice = q.salesprice AND p.serialnumber = '1133-P-01';

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.

JoinedTable Specifies the query that defines the table join. The syntax of JoinedTable is presented under "JoinedTable".
DerivedTable Specifies a table derived from the evaluation of a SELECT statement. No FIRST NumRows or ROWS m TO n clauses are allowed in this SELECT statement. The syntax of DerivedTable is presented under "DerivedTable"


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:

SELECT Selectlist FROM Table1, 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.
JoinType JOIN 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.

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

Description

Examples

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

Perform a left outer join:

SELECT * FROM regions LEFT JOIN countries
  ON regions.region_id=countries.region_id
  WHERE regions.region_id=3;

< 3, Asia, JP, Japan, 3 >
< 3, Asia, CN, China, 3 >
< 3, Asia, IN, India, 3 >
< 3, Asia, AU, Australia, 3 >
< 3, Asia, SG, Singapore, 3 >
< 3, Asia, HK, HongKong, 3 >
6 rows found.

You can also perform a left outer join with the (+) operator, as follows.

SELECT * FROM regions, countries
  WHERE regions.region_id=countries.region_id (+)
    AND regions.region_id=3;

For more examples of joins specified with the (+) operator, see "Examples".

The following performs a right outer join:

SELECT * FROM regions RIGHT JOIN countries
  ON regions.region_id=wountries.region_id
  WHERE regions.region_id=3;

< AU, Australia, 3, 3, Asia >
< CN, China, 3, 3, Asia >
< HK, HongKong, 3, 3, Asia >
< IN, India, 3, 3, Asia >
< JP, Japan, 3, 3, Asia >
< SG, Singapore, 3, 3, Asia >
6 rows found.

The next example performs a right outer join with the (+) operator:

SELECT * FROM countries, regions
       WHERE regions.region_id (+)=countries.region_id
       AND countries.region_id=3;
< JP, Japan, 3, 3, Asia >
< CN, China, 3, 3, Asia >
< IN, India, 3, 3, Asia >
< AU, Australia, 3, 3, Asia >
< SG, Singapore, 3, 3, Asia >
< HK, HongKong, 3, 3, Asia >
6 rows found.

Note that the right join methods produce the same rows but in a different display order. There should be no expectation of row order for join results.

The following performs an inner join:

SELECT * FROM regions INNER JOIN countries
  ON regions.region_id=countries.region_id
  WHERE regions.region_id=2;

< 2, Americas, US, United States of America, 2 >
< 2, Americas, CA, Canada, 2 >
< 2, Americas, BR, Brazil, 2 >
< 2, Americas, MX, Mexico, 2 >
< 2, Americas, AR, Argentina, 2 >
5 rows found.

The next example performs a cross join:

SELECT * FROM regions CROSS JOIN countries 
  WHERE regions.region_id=1;

< 1, Europe, AR, Argentina, 2 >
< 1, Europe, AU, Australia, 3 >
< 1, Europe, BE, Belgium, 1 >
< 1, Europe, BR, Brazil, 2 >
...
< 1, Europe, SG, Singapore, 3 >
< 1, Europe, UK, United Kingdom, 1 >
< 1, Europe, US, United States of America, 2 >
< 1, Europe, ZM, Zambia, 4 >
< 1, Europe, ZW, Zimbabwe, 4 >
25 rows found.

See also


CREATE TABLE
INSERT
INSERT...SELECT
UPDATE

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:

Parameter Description
Subquery For information on subqueries, see "Subqueries".
CorrelationName 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:


GROUP BY clause

Specify the GROUP BY clause if you want the database to group the selected rows based on the value of expressions for each row and return a single row of summary information for each group. If the GROUP BY clause is omitted, the entire query result is treated as one group. If this clause contains CUBE or ROLLUP, the results contain superaggregate groupings in addition to the regular groupings.

The expressions in the GROUP BY clause can do the following:

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

Note:

To identify and potentially eliminate NULL groupings from the superaggregate groupings, use the GROUPING function, as described in "GROUPING".

SQL syntax

The general syntax for the GROUP BY clause is the following:

GROUP BY
 {Expression | RollupCubeClause | GroupingSetsClause }[,...]
 
GroupingSetsClause::=  GROUPING SETS 
 GroupingExpressionList | RollupCubeClause [,...]

RollupCubeClause
{ ROLLUP | CUBE } ( GroupingExpressionList ) }

GroupingExpressionList::=   
{ Expression | ExpressionList [, { Expression | ExpressionList } ] ...}
 
ExpressionList :: = ( Expression [, Expression ] ...)

Parameters

Parameter Description
Expression Valid expression syntax. See Chapter 3, "Expressions".
RollupCubeClause The GROUP BY clause may include one or more ROLLUP or CUBE clauses.
GroupingSetsClause The GROUP BY clause may include one or more GROUPING SETS clauses. The GROUPING SETS clause enables you to explicitly specify which groupings of data that the database returns. For more information, see "GROUPING SETS".
GroupingExpressionList The GROUP BY clause can contain multiple expressions or expression lists.
ROLLUP GroupingExpressionList The ROLLUP clause is used to generate superaggregate rows from groups. For more information, see "ROLLUP".
CUBE GroupingExpressionList The CUBE clause groups selected rows based on the values of all possible combinations of the grouping columns in the CUBE clause. For more information, see "CUBE".
ExpressionList A list of one or more expressions, each separated by a comma.

Examples

The following GROUP BY example sums the salaries for employees in the employees table and uses the SUBSTR expression to group the data by job function.

Command> SELECT SUBSTR (job_id, 4,10), SUM (salary) FROM employees
       > GROUP BY SUBSTR (job_id,4,10);
< PRES, 24000 >
< VP, 34000 >
< PROG, 28800 >
< MGR, 24000 >
< ACCOUNT, 47900 >
< MAN, 121400 >
< CLERK, 133900 >
< REP, 273000 >
< ASST, 4400 >
9 rows found.

Query emp_details_view to select the first 10 departments and managers within the department and count the number of employees in the department with the same manager. Use the GROUP BY clause to group the result by department and manager.

Command> columnlabels on;
Command> SELECT first 10 department_id AS DEPT, manager_id AS MGR,
       > COUNT(employee_id) AS NUM_EMP
       > FROM emp_details_view
       > GROUP BY (department_id, manager_id)
       > ORDER BY department_id, manager_id;

DEPT, MGR, NUM_EMP
< 10, 101, 1 >
< 20, 100, 1 >
< 20, 201, 1 >
< 30, 100, 1 >
< 30, 114, 5 >
< 40, 101, 1 >
< 50, 100, 5 >
< 50, 120, 8 >
< 50, 121, 8 >
< 50, 122, 8 >
10 rows found.

ROLLUP, CUBE and GROUPING SETS clauses

The following definitions describe how columns can be grouped within the ROLLUP, CUBE or GROUPING SETS clauses:

Duplicate grouping columns can be used in ROLLUP, CUBE or GROUPING SETS. However, it does result in duplicated result rows.

Restrictions for ROLLUP, CUBE and GROUPING SETS clauses are as follows:

The following sections describe the ROLLUP, CUBE and GROUPING SETS clauses:

GROUPING SETS

The GROUPING SETS clause enables you to explicitly specify which groupings of data that the database returns. You specify only the desired groups by enclosing them within parentheses, so the database only generates the superaggregate summaries in which you are interested.

The following statement produces three groups: one group returns results for each gender and year columns, a second for a summary superaggregate for each of the months and the last result for the grand total.

SELECT GENDER, YEAR, MONTH,
   SUM (NUM_OF_STUDENTS) AS TOTAL
   FROM INSTRUCTOR_SUMMARY
   GROUP BY GROUPING SETS ((GENDER, YEAR),  -- 1ST GROUP
                           (MONTH), -- 2ND GROUP
                           ()); -- 3RD GROUP
 

You can combine multiple GROUPING SETS to generate specific combinations between the multiple GROUPING SETS. The following statement contains two GROUPING SETS clauses:

GROUP BY GROUPING SETS (YEAR, MONTH),
         GROUPING SETS (WEEK, DAY);

This is equivalent to the following GROUPING SETS statement:

GROUP BY GROUPING SETS (YEAR, WEEK), 
 (YEAR, DAY), 
 (MONTH, WEEK), 
 (MONTH, DAY);

When a GROUP BY clause has both regular grouping columns and a GROUPING SETS clause, the results are grouped by the regular grouping column as follows:

GROUP BY a, b GROUPING SETS(c, d);

This is equivalent to the following:

GROUP BY GROUPING SETS((a, b, c), (a, b, d));

Example

The following example specifies the grouping sets of (region_name, country_name), state_province, and grand totals.

Command> SELECT region_name AS Region,
       >    country_name AS Country,
       >    state_province AS State,
       >    COUNT(employee_id) AS "Total Emp"
       > FROM regions r, countries c, locations l, departments d, employees e
       > WHERE r.region_id = c.region_id AND
       >    l.country_id = c.country_id AND
       >    d.location_id = l.location_id AND
       >    d.department_id = e.department_id
       > GROUP BY grouping sets((region_name, country_name), state_province, ())
       > ORDER BY region_name, state_province;
 
REGION, COUNTRY, STATE, TOTAL EMP
< Americas, Canada, <NULL>, 2 >
< Americas, United States of America, <NULL>, 68 >
< Europe, Germany, <NULL>, 1 >
< Europe, United Kingdom, <NULL>, 35 >
< <NULL>, <NULL>, Bavaria, 1 >
< <NULL>, <NULL>, California, 45 >
< <NULL>, <NULL>, Ontario, 2 >
< <NULL>, <NULL>, Oxford, 34 >
< <NULL>, <NULL>, Texas, 5 >
< <NULL>, <NULL>, Washington, 18 >
< <NULL>, <NULL>, <NULL>, 106 >
< <NULL>, <NULL>, <NULL>, 1 >
12 rows found.

ROLLUP

ROLLUP is used within the GROUP BY clause. When used with SUM, ROLLUP generates subtotals from most detailed level (all columns specified in the ROLLUP clause) to the grand total level, by removing one column at each level. These are known as superaggregate rows.

The ROLLUP clause returns the following:

You can group columns using composite columns inside parentheses. For example, in the following statement:

GROUP BY ROLLUP( (a, b), (c, d), e);

The (a, b) and (c, d) composite columns are treated as a unit when the database produces the ROLLUP results. In this example, the grouping sets returned are as follows: ( (a, b), (c, d), e ), ( (a, b), (c, d) ), (a, b) and ().

You can execute several ROLLUP clauses within your SELECT statement, as follows:

SELECT C1, COUNT(*)
FROM T
GROUP BY ROLLUP(a, b), ROLLUP(c, d);

This is equivalent to the following statement:

SELECT C1, COUNT(*)
FROM T
GROUP BY GROUPING SETS((a, b),(a),()), 
 GROUPING SETS((c, d),(c), ());

Examples

Query the employees table to select the first 10 departments and return the number of employees under each manager in each department. Use ROLLUP to subtotal the number of employees in each department and return a grand total of all employees in the company.

Command> SELECT first 10 department_id AS Dept,
       > manager_id AS Mgr,
       > COUNT(employee_id) AS "Total emp"
       > FROM employees
       > GROUP BY ROLLUP(department_id, manager_id)
       > ORDER BY department_id, manager_id;
 
DEPT, MGR, TOTAL EMP
< 10, 101, 1 >
< 10, <NULL>, 1 >
< 20, 100, 1 >
< 20, 201, 1 >
< 20, <NULL>, 2 >
< 30, 100, 1 >
< 30, 114, 5 >
< 30, <NULL>, 6 >
< 40, 101, 1 >
< 40, <NULL>, 1 >
10 rows found.

The following query returns the number of employees in each region, country and state or province. The rollup returns superaggregate rows for subtotals of all employees in each state or province and in each country and a grand total for all employees in the company. By combining the region and country as its own unit (within parenthesis), the rollup does not return all employees for each region.

Command> SELECT region_name AS Region,       >  country_name AS Country,       >  state_province AS State,       >  COUNT(employee_id) AS "Total Emp"       >  FROM regions r, countries c, locations l, departments d, employees e       >  WHERE r.region_id = c.region_id       >  AND l.country_id = c.country_id       >  AND d.location_id = l.location_id       >  AND d.department_id = e.department_id       >  GROUP BY rollup((region_name, country_name), state_province)       >  ORDER BY region_name;REGION, COUNTRY, STATE, TOTAL EMP< Americas, Canada, Ontario, 2 >< Americas, United States of America, Texas, 5 >< Americas, United States of America, California, 45 >< Americas, United States of America, Washington, 18 >< Americas, Canada, <NULL>, 2 >< Americas, United States of America, <NULL>, 68 >< Europe, Germany, Bavaria, 1 >< Europe, United Kingdom, <NULL>, 1 >< Europe, United Kingdom, Oxford, 34 >< Europe, Germany, <NULL>, 1 >< Europe, United Kingdom, <NULL>, 35 >< <NULL>, <NULL>, <NULL>, 106 >12 rows found. 

CUBE

The CUBE clause groups the selected rows based on the values of all possible combinations of the grouping columns in the CUBE clause. It returns a single row of summary information for each group. For example, three expressions (n=3) in the CUBE clause results in 2n = 23 = 8 groupings. Rows grouped on the values of n expressions are called regular rows; all others are called superaggregate rows. You can group using composite columns. For instance, a commonly requested CUBE operation is for state sales subtotals on all combinations of month, state, and product sold.

For instance, if you specify GROUP BY CUBE(a, b, c), the resulting aggregate groupings generated are as follows: (a,b,c), (a,b), (a,c), (b,c), a, b, c, ( ).

Example

To return the number of employees for each region and country, issue the following query:

Command> SELECT region_name AS Region,
       > country_name AS Country,
       > COUNT(employee_id) AS "Total Emp"
       > FROM regions r, countries c, locations l, departments d, employees e
       > WHERE r.region_id = c.region_id
       > AND l.country_id = c.country_id
       > AND d.location_id = l.location_id
       > AND d.department_id = e.department_id
       > GROUP BY CUBE(region_name, country_name)
       > ORDER BY region_name;
 
REGION, COUNTRY, TOTAL EMP
< Americas, Canada, 2 >
< Americas, United States of America, 68 >
< Americas, <NULL>, 70 >
< Europe, Germany, 1 >
< Europe, United Kingdom, 35 >
< Europe, <NULL>, 36 >
< <NULL>, Canada, 2 >
< <NULL>, Germany, 1 >
< <NULL>, United Kingdom, 35 >
< <NULL>, United States of America, 68 >
< <NULL>, <NULL>, 106 >
11 rows found.


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

Examples

To delete all the rows from the recreation.clubs table, use:

TRUNCATE TABLE recreation.clubs;

See also


ALTER TABLE
DROP TABLE

UNLOAD CACHE GROUP

The UNLOAD CACHE GROUP statement deletes all rows from the cache group. The unload operation is local. It is not propagated across cache grid members.

Required privilege

No privilege is required for the cache group owner.

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

SQL syntax

UNLOAD CACHE GROUP [Owner.]GroupName 
[WHERE ConditionalExpression]

or

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

Parameters

Parameter Description
[Owner.]GroupName Name assigned to the cache group.
ConditionalExpression A search condition to qualify the target rows of the operation.
WITH ID ColumnValueList The WITH ID clauses enables you to use primary key values to unload the cache instance. Specify ColumnValueList as either a list of literals or binding parameters to represent the primary key values.

Description

Restrictions

Examples

CREATE CACHE GROUP recreation.cache
    FROM recreation.clubs (
        clubname CHAR(15) NOT NULL,
        clubphone SMALLINT,
        activity CHAR(18),
        PRIMARY KEY(clubname))
    WHERE (recreation.clubs.activity IS NOT NULL);
UNLOAD CACHE GROUP recreation.cache;

See also


ALTER CACHE GROUP
CREATE CACHE GROUP
DROP CACHE GROUP
FLUSH CACHE GROUP
LOAD CACHE GROUP

UPDATE

The UPDATE statement updates the values of one or more columns in all rows of a table or in rows that satisfy a search condition.

Required privilege

No privilege is required for the table owner.

UPDATE for another user's table.

SQL syntax

UPDATE [FIRST NumRows] 
{[Owner.]TableName [CorrelationName]}
SET {ColumnName =
{Expression1 | NULL | DEFAULT}} [,...]
[ WHERE SearchCondition ]
RETURNING|RETURN Expression2[,...] INTO DataItem[,...]

Parameters

Parameter Description
FIRST NumRows Specifies the number of rows to update. FIRST NumRows 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.

SET ColumnName 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. See "Incrementing SEQUENCE values with CURRVAL and NEXTVAL" for more details.
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.
WHERE SearchCondition 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.
Expression2 Valid expression syntax. See Chapter 3, "Expressions".
DataItem Host variable or PL/SQL variable that stores the retrieved Expression2 value.

Description

Examples

The following example increases the price of parts costing more than $500 by 25 percent:

UPDATE purchasing.parts
SET salesprice = salesprice * 1.25
WHERE salesprice > 500.00;

This next example updates the column with the NEXTVAL value from sequence seq:

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

The following query updates the status of all the customers who have at least one unshipped order:

UPDATE customers SET customers.status = 'unshipped'
WHERE customers.id = ANY
    (SELECT orders.custid FROM orders
        WHERE orders.status = 'unshipped');

The following statement updates all the duplicate orders, assuming id is not a primary key:

UPDATE orders a
    WHERE EXISTS (SELECT 1 FROM orders b
        WHERE a.id = b.id AND a.rowid < b.rowid);

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.

Command> VARIABLE bnd1 NUMBER(8,2);
Command> VARIABLE bnd2 VARCHAR2(25) INLINE NOT NULL;
Command> VARIABLE bnd3 NUMBER(4);
Command> UPDATE employees SET job_id='SA_MAN', salary=salary+1000,
       > department_id=140 WHERE last_name='Jones'
       > RETURNING salary*0.25, last_name, department_id
       > INTO :bnd1, :bnd2, :bnd3;
1 row updated.
Command> PRINT bnd1 bnd2 bnd3;
BND1                 : 950
BND2                 : Jones
BND3                 : 140

Join update

TimesTen supports join update statements. A join update can be used to update one or more columns of a table using the result of a subquery.

Syntax

UPDATE [Owner.]TableName
SET ColumnName=Subquery
 [WHERE SearchCondition]

or

UPDATE [Owner.]TableName
SET (ColumnName[,...])=Subquery
 [WHERE SearchCondition]

Parameters

A join update statement has the following parameters:

Parameter Description
[Owner.]TableName Identifies the table to be updated.
SET (ColumnName[,...])= Subquery 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.

WHERE SearchCondition 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);

See also

SELECT