6 SQL Statements

SQL Syntax

A SQL statement can have one comment that includes one or more statement level optimizer hints.

The statements in which the hints are supported vary:

• TT_CommitDMLOnSuccess is supported in the DELETE, INSERT, and UPDATE statements. It is also valid in the INSERT...SELECT statement and must follow the SELECT keyword. This hint is supported in TimesTen Scaleout only.

• The TT_GridQueryExec and TT_PartialResult hints are supported in the SELECT, INSERT...SELECT, and CREATE TABLE... AS SELECT SQL statements only and these hints must follow the SELECT keyword. These hints are supported in TimesTen Scaleout only.

• The remaining hints are supported in the DELETE, INSERT, MERGE, SELECT, UPDATE, INSERT...SELECT, and CREATE TABLE...AS SELECT SQL statements and these hints must follow the DELETE, INSERT, MERGE, SELECT, or UPDATE keyword.

Table 6-3, "Placement of statement level hints in SQL statements" shows the proper placement of hints in a SQL statement.

You embed statement level optimizer hints in comment syntax. TimesTen supports hints in comments that span one line and in comments that span more than one line. If your comment that contains the hint spans one or more lines, use the comment syntax, /*+...*/. If your comment that contains the hint spans one line, use the comment syntax, --+.

Syntax:

SQL VERB {/*+ [CommentText] hint [{hint|CommentText} [...]] */ |
          --+ [CommentText] hint [{hint|CommentText} [...]] }
hint::= ScaleoutHint | JoinOrderHint | IndexHint| FlagHint
ScaleoutHint::= TT_CommitDMLOnSuccess({0|1})|TT_GridQueryExec({LOCAL|GLOBAL})|
                TT_PartialResult(0|1)
JoinOrderHint::= TT_JoinOrder (CorrelationName CorrelationName [...])
IndexHint::= TT_Index (CorrelationName,IndexName,{0|1} [;...])
FlagHint::= FlagName (0|1)
FlagName::= TT_BranchAndBound|TT_DynamicLoadEnable|TT_DynamicLoadErrorMode|
            TT_FastPrepare|TT_FirstRow|TT_ForceCompile|TT_GenPlan|
            TT_HashGb|TT_HashScan|TT_IndexedOr|TT_MergeJoin|
            TT_NestedLoop|TT_NoRemRowIdOpt|TT_Range|
            TT_Rowid|TT_RowLock|TT_ShowJoinOrder|TT_TblLock|TT_TblScan|
            TT_TmpHash|TT_TmpRange|TT_TmpTable|TT_UseBoyerMooreStringSearch

Parameters

SELECT /*+TT_GridQueryExec(LOCAL)*/   
        COUNT(*), elementId# FROM t
        GROUP BY elementId#;

SELECT /*+TT_GridQueryExec(GLOBAL)*/   
        COUNT(*), elementId# FROM t 
        GROUP BY elementId#;

SELECT /*+TT_PartialResult(0)*/ COUNT  
       (*), elementId# FROM t GROUP BY
       elementId#;

SELECT /*+TT_PartialResult(1)*/ COUNT  
      (*), elementId# FROM t GROUP BY
      elementId#;

Command> SELECT /*+ TT_JoinOrder
           (EMPS DEPTS)*/...

Command> SELECT /*+ TT_INDEX
          (E,EMP_NAME_IX,1) */ ...

Description

• Embed statement level optimizer hints in comment syntax. Begin the comment with either /* or --. Follow the beginning comment syntax with a plus sign (+). The plus sign (+) signals TimesTen to interpret the comment as a list of hints. The plus sign (+) must follow immediately after the comment delimiter. (For example, after /* or after --). No space is permitted between the comment delimiter and the plus sign (+).

  In the following example, there is a space between the star (*) and the plus sign (+), so the hint is ignored:

  Command> SELECT /* + TT_TblScan (1) This hint is ignored because there is a
            space between the star (*) and the plus (+) sign. */ ...
  

• A hint is one of the statement level optimizer hints supported by TimesTen. There can be a space between the plus sign (+) and the hint. If the comment contains multiple hints, separate the hints by at least one space. For example, to specify two hints on one line:

  Command> SELECT --+ TT_MergeJoin (0) TT_NestedLoop (1) 
            ...
  

• You can intersperse commenting text with hints in a comment. For example,

  Command> SELECT /*+ TT_HashScan (1) This demonstrates a hint followed by a
             comment string. */ ...
  

• TimesTen ignores hints and does not return an error if:

  • Your hint does not follow the DELETE, INSERT, MERGE, SELECT or UPDATE keyword (or for TT_GridQueryExec or TT_PartialResult, the SELECT keyword). TT_CommitDMLOnSuccess must follow the DELETE, INSERT, UPDATE keyword and for INSERT...SELECT, it must follow the SELECT keyword.

  • Your hint contains misspellings or syntax errors. If you have hints that are within the same comment and some hints are correct syntactically and some hints are incorrect syntactically, TimesTen ignores the incorrect hints and accepts the correct hints.

  • You use either the TT_JoinOrder or TT_Index hint and you do not supply a closing parenthesis, the remainder of the hint string is ignored.

• For hints that conflict with each other, TimesTen uses the rightmost hint in the comment. For example, if the comment string is /*+TT_TblScan (0)...TT_TblScan (1) */, the rightmost hint, TT_TblScan(1), is used.

• Statement level optimizer hints override conflicting transaction level optimizer hints. If you specify a transaction level optimizer hint that conflicts with a statement level optimizer hint, the statement level optimizer hint overrides the conflicting transaction level optimizer hint. For example, if you call ttOptSetFlag, and enable the Range flag and then you issue a SQL query and disable the statement level optimizer flag, TT_Range, TimesTen disables the range flag for the query. After the query is executed, the original range flag setting that was in place in the transaction before the query was executed remains in effect for the duration of the transaction. For more information, see Example 6-1, "Using statement level optimizer hints for a SELECT query". The TT_GridQueryExec, TT_PartialResult hints and TT_CommitDMLOnSuccess hints are not supported at the transaction level.

• Do not use statement level optimizer hints in a subquery.

• The TimesTen query optimizer does not recognize statement level optimizer hints for passthrough statements. TimesTen passes the SQL text for passthrough statements to the Oracle database and the SQL text is processed according to the SQL rules of the Oracle database. Passthrough statements are not supported in TimesTen Scaleout.

SQL statements that support statement level optimizer hints

You can specify statement level optimizer hints in SQL statements. Not all hints are supported in all statements. You must specify the hint within comment syntax and the comment syntax must immediately follow the SQL VERB. (For example, SELECT /*+ hint */...) Table 6-3, "Placement of statement level hints in SQL statements" shows the correct placement of the statement level hint. It also indicates if a hint is not supported in the statement.

Understanding hints

Use optimizer hints to influence the TimesTen query optimizer in determining the choice of the execution plan for your query.

TT_GridQueryExec, TT_PartialResult and TT_CommitDMLOnSuccess are supported at the connection and statement levels only. This section is not valid for these hints.

To view transaction level optimizer hints, execute the built-in procedure, ttOptSetFlag. For more information on the built-in procedure, ttOptGetFlag, see "ttOptGetFlag" in Oracle TimesTen In-Memory Database Reference.

Examples

For TT_CommitDMLOnSuccess examples, see "TT_CommitDMLOnSuccess optimizer hint" for information.

For TT_GridQueryExec and TT_PartialResult examples:

• See "TT_GridQueryExec" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

• See "TT_PartialResult" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

The following examples illustrate usages of statement level and transaction level optimizer hints. The TimesTen optimizer is a cost based query optimizer and generates what it thinks is the most optimal execution plan for your statement. This plan differs from release to release. The plan is based on the indexes that exist on the referenced tables as well as the column and table statistics that are available. When you recompute statistics or change indexes, the TimesTen optimizer may change the execution plan based on the recomputed statistics and index changes. Because the execution plan may vary, these examples are included for demonstration purposes only. Examples include:

• Example 6-1, "Using statement level optimizer hints for a SELECT query"

• Example 6-2, "Using on and off hinting"

• Example 6-3, "Using TT_JoinOrder to specify a join order"

• Example 6-4, "Using the statement level optimizer hint TT_INDEX"

Command> SELECT r.region_name, c.country_name
         FROM regions r, countries c
         WHERE r.region_id = c.region_id
       > ORDER BY c.region_id;

Command> EXPLAIN SELECT r.region_name, c.country_name
         FROM regions r, countries c
         WHERE r.region_id = c.region_id
         ORDER BY c.region_id;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             COUNTRIES
  IXNAME:              COUNTR_REG_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
 
  STEP:                2
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             REGIONS
  IXNAME:              REGIONS
  INDEXED CONDITION:   R.REGION_ID >= C.REGION_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               1
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   C.REGION_ID = R.REGION_ID
  NOT INDEXED:         <NULL>

Command> autocommit on;
Command> EXPLAIN SELECT /*+ TT_RowLock (1), TT_TblLock (0), TT_MergeJoin (0),
         TT_NestedLoop (1) */
         r.region_name, c.country_name
         FROM regions r, countries c
         WHERE r.region_id = c.region_id
         ORDER BY c.region_id;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           RowLkRangeScan
  TBLNAME:             REGIONS
  IXNAME:              REGIONS
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               3
  OPERATION:           RowLkRangeScan
  TBLNAME:             COUNTRIES
  IXNAME:              COUNTR_REG_FK
  INDEXED CONDITION:   C.REGION_ID = R.REGION_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               2
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               1
  OPERATION:           OrderBy
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Command> EXPLAIN SELECT r.region_name, c.country_name
         FROM regions r, countries c
         WHERE r.region_id = c.region_id
         ORDER BY c.region_id;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             COUNTRIES
  IXNAME:              COUNTR_REG_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             REGIONS
  IXNAME:              REGIONS
  INDEXED CONDITION:   R.REGION_ID >= C.REGION_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               1
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   C.REGION_ID = R.REGION_ID
  NOT INDEXED:         <NULL>

Command> CREATE TABLE test (col1 NUMBER, col2 NUMBER);
Command> CREATE HASH INDEX h_index ON test (col1);
Command> CREATE INDEX hr_index ON test (col2);

Command> autocommit off;
Command> CALL ttOptGetFlag ('Hash');
< Hash, 1 >
1 row found.
Command> CALL ttOptGetFlag ('Scan');
< Scan, 1 >
1 row found.

Command> EXPLAIN SELECT * FROM test WHERE col1 = ? and col2 = ?;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkHashScan
  TBLNAME:             TEST
  IXNAME:              H_INDEX
  INDEXED CONDITION:   TEST.COL1 = _QMARK_1
  NOT INDEXED:         TEST.COL2 = _QMARK_2

Command> EXPLAIN SELECT --+ TT_Range (1) Single line comment to set TT_Range
         * FROM TEST WHERE col1 = ? and col2 = ?;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkHashScan
  TBLNAME:             TEST
  IXNAME:              H_INDEX
  INDEXED CONDITION:   TEST.COL1 = _QMARK_1
  NOT INDEXED:         TEST.COL2 = _QMARK_2

Command> EXPLAIN SELECT /*+ TT_Range (1) TT_HashScan (0)
          Multiple line comment to enable TT_Range and disable TT_HashScan */
          * FROM TEST WHERE col1 = ? and col2 = ?;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkRangeScan
  TBLNAME:             TEST
  IXNAME:              HR_INDEX
  INDEXED CONDITION:   TEST.COL2 = _QMARK_2
  NOT INDEXED:         TEST.COL1 = _QMARK_1

Command> EXPLAIN SELECT * FROM TEST WHERE col1 = ? and col2 = ?;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkHashScan
  TBLNAME:             TEST
  IXNAME:              H_INDEX
  INDEXED CONDITION:   TEST.COL1 = _QMARK_1
  NOT INDEXED:         TEST.COL2 = _QMARK_2

Command> CALL ttOptSetOrder ('e d j');
Command> EXPLAIN SELECT *
         FROM employees e, departments d, job_history j
         WHERE e.department_id = d.department_id
         AND e.hire_date = j.start_date;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_DEPT_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             DEPARTMENTS
  IXNAME:              DEPARTMENTS
  INDEXED CONDITION:   D.DEPARTMENT_ID >= E.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               2
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   E.DEPARTMENT_ID = D.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             JOB_HISTORY
  IXNAME:              JOB_HISTORY
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         E.HIRE_DATE = J.START_DATE
 
  STEP:                5
  LEVEL:               1
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Command> EXPLAIN SELECT --+ TT_JoinOrder (e j d)
          *
          FROM employees e, departments d, job_history j
          WHERE e.department_id = d.department_id
          AND e.hire_date = j.start_date;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_DEPT_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             JOB_HISTORY
  IXNAME:              JOB_HISTORY
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         E.HIRE_DATE = J.START_DATE
 
  STEP:                3
  LEVEL:               2
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             DEPARTMENTS
  IXNAME:              DEPARTMENTS
  INDEXED CONDITION:   D.DEPARTMENT_ID >= E.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                5
  LEVEL:               1
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   E.DEPARTMENT_ID = D.DEPARTMENT_ID
  NOT INDEXED:         <NULL>

Command> EXPLAIN SELECT *
          FROM employees e, departments d, job_history j
          WHERE e.department_id = d.department_id
          AND e.hire_date = j.start_date;
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_DEPT_FK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>
 
  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             DEPARTMENTS
  IXNAME:              DEPARTMENTS
  INDEXED CONDITION:   D.DEPARTMENT_ID >= E.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                3
  LEVEL:               2
  OPERATION:           MergeJoin
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   E.DEPARTMENT_ID = D.DEPARTMENT_ID
  NOT INDEXED:         <NULL>
 
  STEP:                4
  LEVEL:               2
  OPERATION:           TblLkRangeScan
  TBLNAME:             JOB_HISTORY
  IXNAME:              JOB_HISTORY
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         E.HIRE_DATE = J.START_DATE
 
  STEP:                5
  LEVEL:               1
  OPERATION:           NestedLoop
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Command> indexes employees;
 
Indexes on table TESTUSER.EMPLOYEES:
  EMPLOYEES: unique range index on columns:
    EMPLOYEE_ID
    (referenced by foreign key index JHIST_EMP_FK on table TESTUSER.JOB_HISTORY)
  TTUNIQUE_0: unique range index on columns:
    EMAIL
  EMP_DEPT_FK: non-unique range index on columns:
    DEPARTMENT_ID
    (foreign key index references table TESTUSER.DEPARTMENTS(DEPARTMENT_ID))
  EMP_JOB_FK: non-unique range index on columns:
    JOB_ID
    (foreign key index references table TESTUSER.JOBS(JOB_ID))
  EMP_NAME_IX: non-unique range index on columns:
    LAST_NAME
    FIRST_NAME
  5 indexes found.
 
5 indexes found on 1 table.

Command> EXPLAIN SELECT e.first_name
           FROM employees e
           WHERE e.last_name BETWEEN 'A' AND 'B';
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMP_NAME_IX
  INDEXED CONDITION:   E.LAST_NAME >= 'A' AND E.LAST_NAME <= 'B'
  NOT INDEXED:         <NULL>

Command> EXPLAIN SELECT --+ TT_INDEX (E,EMP_NAME_IX,0)
           e.first_name
           FROM employees e
           WHERE e.last_name BETWEEN 'A' AND 'B';
 
Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkRangeScan
  TBLNAME:             EMPLOYEES
  IXNAME:              EMPLOYEES
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         E.LAST_NAME <= 'B' AND E.LAST_NAME >= 'A'

Required privilege

ADMIN

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

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

Syntax for SubscriberOperation:

{ADD | DROP } SUBSCRIBER FullStoreName

Syntax for StoreOperation:

ALTER STORE FullStoreName SET StoreAttribute

Syntax for InclusionOperation:

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

Syntax for NetworkOperation:

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

Parameters

Description

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

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

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

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

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

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

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

• Do not use the EXCLUDE clause for AWT cache groups.

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

Examples

Add a subscriber to the replication scheme.

ALTER ACTIVE STANDBY PAIR
    ADD SUBSCRIBER rep4;

Drop two subscribers from the replication scheme.

ALTER ACTIVE STANDBY PAIR
    DROP SUBCRIBER rep3
    DROP SUBSCRIBER rep4;

Alter the store attributes of the rep3 and rep4 databases.

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

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

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

Add NetworkOperation clause to active standby pair:

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

See also

Required privilege

No privilege is required for the cache group owner.

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

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

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

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

This statement changes the AUTOREFRESH interval on the cache group:

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

This statement alters the AUTOREFRESH state:

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

Parameters

Description

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

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

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

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

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

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

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

See also

Required privilege

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

ALTER ANY PROCEDURE for another user's function.

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

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

Parameters

Description

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

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

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

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

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

See also

Required privilege

No privilege is required for the package owner.

ALTER ANY PROCEDURE for another user's package.

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

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

Parameters

Description

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

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

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

See also

Required privilege

No privilege is required for the procedure owner.

ALTER ANY PROCEDURE for another user's procedure.

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

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

Parameters

Description

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

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

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

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

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

Examples

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

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

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

See also

Required privilege

ADMIN

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

ALTER PROFILE profile LIMIT password_parameters

password_parameters::=
[FAILED_LOGIN_ATTEMPTS password_parameter_options]
[PASSWORD_LIFE_TIME password_parameter_options]
[PASSWORD_REUSE_TIME password_parameter_options]
[PASSWORD_REUSE_MAX password_parameter_options]
[PASSWORD_LOCK_TIME password_parameter_options]
[PASSWORD_GRACE_TIME password_parameter_options]
[PASSWORD_COMPLEXITY_CHECKER password_checker_options]

password_parameter_options::=
UNLIMITED|DEFAULT|constant

password_checker_options::=
NULL|DEFAULT

Parameters

Description

• Use the ALTER PROFILE statement to modify a previously created profile. See "CREATE PROFILE" for information on creating a profile.

• If you make a change to a profile (by using the ALTER PROFILE statement), and the profile is assigned to users, the change does not affect the users that are currently connected to the database. However, the change does affect the users that subsequently connect to the database.

• You can alter the DEFAULT profile. See "Example 1: Alter the DEFAULT profile" for an example of altering the DEFAULT profile.

• You cannot alter the instance administrator's profile.

Examples

Example 1: Alter the DEFAULT profile

This example verifies the values of the password parameters in the DEFAULT profile. It then alters the profile with different values. Users that are assigned the DEFAULT profile will inherit the modified values at the user's next connection to the database.

Command> SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND 
           resource_type='PASSWORD';
< DEFAULT, FAILED_LOGIN_ATTEMPTS, PASSWORD, 10 >
< DEFAULT, PASSWORD_LIFE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_MAX, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, NULL >
< DEFAULT, PASSWORD_LOCK_TIME, PASSWORD, .0034 >
< DEFAULT, PASSWORD_GRACE_TIME, PASSWORD, UNLIMITED >
7 rows found.

Create the user1 user and do not specify a profile. User1 is assigned the DEFAULT profile. Use the ALTER PROFILE statement to change the value of the FAILED_LOGIN_ATTEMPTS password parameter to 5 and the value of the PASSWORD_LOCK_TIME password parameter to 1 for the DEFAULT profile. Enclose DEFAULT in double quotation marks as DEFAULT is a reserved word. Connect to the database five times as user1 supplying an incorrect password each time. On the sixth attempt, the user1 account is locked.

Command> CREATE USER user1 IDENTIFIED BY user1;
 
User created.
Command> GRANT CONNECT TO user1;

Query the dba_users system view to verify that user1 is assigned the DEFAULT profile.

Command> SELECT profile FROM dba_users WHERE username='USER1';
< DEFAULT >
1 row found.

Use the ALTER PROFILE statement to modify the DEFAULT profile.

Command> ALTER PROFILE "DEFAULT" LIMIT 
           FAILED_LOGIN_ATTEMPTS 5 
           PASSWORD_LOCK_TIME 1;
 
Profile altered.
 

Query the dba_profiles system view to verify the values are changed (represented in bold).

Command> SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND 
   resource_type='PASSWORD';
< DEFAULT, FAILED_LOGIN_ATTEMPTS, PASSWORD, 5 >
< DEFAULT, PASSWORD_LIFE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_MAX, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, NULL >
< DEFAULT, PASSWORD_LOCK_TIME, PASSWORD, 1 >
< DEFAULT, PASSWORD_GRACE_TIME, PASSWORD, UNLIMITED >
7 rows found.

Attempt to connect to the database as user1. Supply an incorrect password. On the sixth attempt, the user1 account is locked.

Command> connect adding "uid=user1;pwd=user1_test1" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test2" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test3" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test4" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test5" as user1;
 7001: User authentication failed
The command failed.
none: Command> connect adding "uid=user1;pwd=user1_test6" as user1;
15179: the account is locked
The command failed.

Example 2: Create a profile then alter the profile

This example creates the profile1 profile and specifies values for the FAILED_LOGIN_ATTEMPTS, the PASSWORD_LIFE_TIME, the PASSWORD_LOCK_TIME, and the PASSWORD_GRACE_TIME password parameters. It then alters the profile1 profile to modify the PASSWORD_REUSE_TIME and the PASSWORD_REUSE_MAX password parameters.

Command> CREATE PROFILE profile1 LIMIT 
           FAILED_LOGIN_ATTEMPTS 3 
           PASSWORD_LIFE_TIME 90 
           PASSWORD_LOCK_TIME 30 
           PASSWORD_GRACE_TIME 10;
 
Profile created.

Query the dba_profiles system view to verify the values for the password parameters. Note that the PASSWORD_REUSE_TIME and the PASSWORD_REUSE_MAX password parameters each have a value of DEFAULT (represented in bold). These password parameters were not specified in the CREATE PROFILE definition, so TimesTen assigns a value of DEFAULT to each parameter. The values for these parameters are derived from the values in the DEFAULT profile.

Command> SELECT * FROM dba_profiles WHERE profile = 'PROFILE1' AND
           resource_type= 'PASSWORD';
< PROFILE1, FAILED_LOGIN_ATTEMPTS, PASSWORD, 3 >
< PROFILE1, PASSWORD_LIFE_TIME, PASSWORD, 90 >
< PROFILE1, PASSWORD_REUSE_TIME, PASSWORD, DEFAULT >
< PROFILE1, PASSWORD_REUSE_MAX, PASSWORD, DEFAULT >
< PROFILE1, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, DEFAULT >
< PROFILE1, PASSWORD_LOCK_TIME, PASSWORD, 30 >
< PROFILE1, PASSWORD_GRACE_TIME, PASSWORD, 10 >
7 rows found.

Alter the profile1 profile, specifying a value of 20 for the PASSWORD_REUSE_TIME password and a value of 15 for the PASSWORD_REUSE_MAX password parameter (represented in bold). A user assigned this profile can reuse the same password after 20 days if the password has been changed 15 times.

Command> ALTER PROFILE profile1 LIMIT 
           PASSWORD_REUSE_TIME 20 
           PASSWORD_REUSE_MAX 15;
 
Profile altered.

Query the dba_profiles system view to verify the values for the password parameters are changed (represented in bold).

Command> SELECT * FROM dba_profiles WHERE profile = 'PROFILE1' AND 
           resource_type= 'PASSWORD';
< PROFILE1, FAILED_LOGIN_ATTEMPTS, PASSWORD, 3 >
< PROFILE1, PASSWORD_LIFE_TIME, PASSWORD, 90 >
< PROFILE1, PASSWORD_REUSE_TIME, PASSWORD, 20 >
< PROFILE1, PASSWORD_REUSE_MAX, PASSWORD, 15 >
< PROFILE1, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, DEFAULT >
< PROFILE1, PASSWORD_LOCK_TIME, PASSWORD, 30 >
< PROFILE1, PASSWORD_GRACE_TIME, PASSWORD, 10 >
7 rows found.

See also

Required privilege

ADMIN

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

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

DROP ELEMENT { ElementName | * IN FullStoreName }

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

Syntax for ReturnServiceAttribute is:

{ RETURN RECEIPT [BY REQUEST] | NO RETURN }

StoreOperation clauses:

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

Syntax for the StoreAttribute is:

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

Specify NetworkOperation one or more times:

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

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

Parameters

Description

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

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

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

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

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

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

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

  Note:

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

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

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

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

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

Examples

This example sets up a classic replication scheme 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 ds1 database to my.rep1 replication scheme. Include my.tab2 table in the database.

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

This example adds ds2 database to a replication scheme but excludes my.tab1 table.

ALTER REPLICATION my.rep1
  ADD ELEMENT ds2 DATASTORE
     MASTER rep2
     SUBSCRIBER rep1
     EXCLUDE TABLE my.tab1;

Add NetworkOperation clause:

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

Drop NetworkOperation clause:

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

See also

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

Required privilege

No privilege is required for the sequence owner.

ALTER ANY SEQUENCE privilege for another user's sequence.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

ALTER SEQUENCE [Owner.]SequenceName BATCH BatchValue

Parameters

Description

• Use this statement to change the batch value for a sequence in TimesTen Scaleout. The change affects future sequence numbers.

• This statement cannot be used to alter any other values supported in the CREATE SEQUENCE statement. In this case, use the DROP SEQUENCE statement and then create a new sequence with the same name. For example, to change the MINVALUE, drop the sequence and recreate it with the same name and with the desired MINVALUE.

See "Using sequences" in Oracle TimesTen In-Memory Database Scaleout User's Guide for more information.

Examples

To change the batch value for the sequence:

ALTER SEQUENCE myseq BATCH 2000;
Sequence altered

See also

Required privilege

None

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. However, these parameters are not supported:

• DDL_REPLICATION_ACTION

• DDL_REPLICATION_LEVEL

• REPLICATION_TRACK

SQL syntax

ALTER SESSION SET
  {COMMIT_BUFFER_SIZE_MAX = n |
   DDL_REPLICATION_ACTION={'INCLUDE'|'EXCLUDE'} | 
   DDL_REPLICATION_LEVEL={1|2|3} |
   ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED} |
   NLS_SORT = {BINARY| SortName} |
   NLS_LENGTH_SEMANTICS = {BYTE|CHAR} |
   NLS_NCHAR_CONV_EXCP = {TRUE|FALSE} |
   PLSQL_TIMEOUT = n |
   PLSQL_OPTIMIZE_LEVEL = {0|1|2|3}|
   PLSCOPE_SETTINGS = {'IDENTIFIERS:ALL'|'IDENTIFIERS:NONE'} |
   PLSQL_CONN_MEM_LIMIT = n |
   PLSQL_CCFLAGS = 'name1:value1, name2:value2,..., nameN:valueN' |
   PLSQL_SESSION_CACHED_CURSORS = n |
   REPLICATION_TRACK = TrackNumber |
   } 

Parameters

PLSQL_CCFLAGS = 'DEBUG:TRUE'

Description

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

• In cases of client failover, if an ALTER SESSION statement is issued in the failed connection, the setting is not seen or carried over to the new connection. You must re-issue the ALTER SESSION statement and re-specify the value for that parameter. For more information on client failover, in TimesTen Classic, see "Using automatic client failover" in the Oracle TimesTen In-Memory Database Operations Guide and, in TimesTen Scaleout, see "Client connection failover" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

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

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

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

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

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

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

• To add an existing sequence or view to an active standby pair, set DDL_REPLICATION_LEVEL to 3. To include the sequence in the replication scheme, DDL_REPLICATION_ACTION must be set to INCLUDE. This does not apply to materialized views.

• Objects are replicated only when the receiving database is of a TimesTen release that supports that level of replication, and is configured for an active standby pair replication scheme. For example, replication of sequences (requiring DDL_REPLICATION_LEVEL=3) to a database release prior to 11.2.2.7.0 is not supported. The receiving database must be of at least release 11.2.1.8.0 for replication of objects supported by DDL_REPLICATION_LEVEL=2.

Examples

Use the ALTER SESSION statement to change COMMIT_BUFFER_SIZE_MAX to 500 MB. First, call ttConfiguration to display the current connection setting. Use the ALTER SESSION statement to change the COMMIT_BUFFER_SIZE_MAX setting to 500. Call ttConfiguration to display the new setting.

Command>  CALL ttConfiguration ('CommitBufferSizeMax');
< CommitBufferSizeMax, 0 >
1 row found.
Command> ALTER SESSION SET COMMIT_BUFFER_SIZE_MAX = 500;
 
Session altered.
 
Command> CALL ttConfiguration ('CommitBufferSizeMax');
< CommitBufferSizeMax, 500 >
1 row found.

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;
(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 automatic parallel replication with disabled commit dependencies. It uses the ALTER SESSION statement to change the replication track number to 5 for the current connection. To enable automatic parallel replication with disabled commit dependencies for replication schemes, set ReplicationApplyOrdering to 2. 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: DDL_REPLICATON_LEVEL set to 2 and DDLReplicationAction set to 'INCLUDE'.

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

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

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.

After reviewing this section, see:

• ALTER TABLE: Usage with TimesTen Scaleout

• ALTER TABLE: Usage with TimesTen Classic

ALTER TABLE: Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. Column-based compression and aging are not supported.

See:

• SQL syntax for ALTER TABLE: TimesTen Scaleout

• Parameters for ALTER TABLE: TimesTen Scaleout

• Description for ALTER TABLE: TimesTen Scaleout

• Examples for ALTER TABLE: TimesTen Scaleout

ALTER TABLE: Usage with TimesTen Classic

See:

• SQL syntax for ALTER TABLE: TimesTen Classic

• Parameters for ALTER TABLE: TimesTen Classic

• Description for ALTER TABLE: TimesTen Classic

• Examples for ALTER TABLE: TimesTen Classic

SQL syntax for ALTER TABLE: TimesTen Scaleout

To change the distribution key in TimesTen Scaleout:

ALTER TABLE [Owner.]TableName DistributionClause

To add one column:

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

To add multiple columns:

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

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

ALTER TABLE [Owner.]TableName
  ADD [COLUMN] ColumnName ColumnDataType
    NOT NULL [ENABLE] DEFAULT DefaultVal [[NOT] INLINE] [UNIQUE]

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

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

To remove columns.

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

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

ALTER TABLE [Owner.]TableName
USE RANGE INDEX

To change the default value of a column:

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

To add or drop a unique constraint on a column:

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

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

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

Parameters for ALTER TABLE: TimesTen Scaleout

Description for ALTER TABLE: TimesTen Scaleout

• You can alter tables to change defaults or add and drop columns and constraints. However, you cannot change the distribution scheme unless the table is empty. In addition, you cannot drop a constraint that is named in the DISTRIBUTE BY REFERENCE clause. See "CREATE TABLE" for information on the distribution schemes. See "Altering tables" in Oracle TimesTen In-Memory Database Scaleout User's Guide for more information.

• The ALTER TABLE statement cannot be used to alter a temporary table.

• The ALTER TABLE ADD [COLUMN] ColumnName statement adds one or more new columns to an existing table. When you add one or more columns, the new columns are added to the end of all existing rows of the table in one new partition.

• Columns referenced by materialized views cannot be dropped.

• You cannot use the ALTER TABLE statement to add a column, drop a column, or add a constraint for cache group tables.

• Only one partition is added to the table per statement regardless of the number of columns added.

• You can ALTER a table to add a NOT NULL column with a default value. The DEFAULT clause is required. Restrictions include:

  • You cannot use the column as a primary key column. Specifically, you cannot specify the column in the statement: ALTER TABLE ADD ConstraintName PRIMARY KEY (ColumnName [,...]).

• NULL is the initial value for all added columns, unless a default value is specified for the new column.

• The total number of columns in the table cannot exceed 1000. In addition, the total number of partitions in a table cannot exceed 1000, one of which is used by TimesTen.

• Use the ADD CONSTRAINT ... PRIMARY KEY clause to add a primary key constraint to a regular table or to a detailed or materialized view table. Do not use this clause on a table that already has a primary key.

• If you use the ADD CONSTRAINT... PRIMARY KEY clause to add a primary key constraint, and you do not specify the USE HASH INDEX clause, then a range index is used for the primary key constraint.

• Do not specify the ADD CONSTRAINT ... PRIMARY KEY clause on a global temporary table.

• As the result of an ALTER TABLE ADD statement, an additional read occurs for each new partition during queries. Therefore, altered tables may have slightly degraded performance. The performance can only by restored by dropping and recreating the table, or by using the ttMigrate create -c -relaxedUpgrade command, and restoring the table using the ttRestore -r -relaxedUpgrade command. Dropping the added column does not recover the lost performance or decrease the number of partitions.

• When you use the ALTER TABLE DROP statement to remove one or more columns from an existing table, dropped columns are removed from all current rows of the table. Subsequent SQL statements must not attempt to make any use of the dropped columns. You cannot drop columns that are in the table's primary key. You cannot drop columns that are in any of the table's foreign keys until you have dropped all foreign keys. You cannot drop columns that are indexed until all indexes on the column have been dropped. ALTER TABLE cannot be used to drop all of the columns of a table. Use DROP TABLE instead.

• When a column is dropped from a table, all commands referencing that table need to be recompiled. An error may result at recompilation time if a dropped column was referenced. The application must re-prepare those commands, and rebuild any parameters and result columns. When a column is added to a table, the commands that contain a SELECT * statement are invalidated. Only these commands must be re-prepared. All other commands continue to work as expected.

• When you drop a column, the column space is not freed.

• When you add a UNIQUE constraint, there is overhead incurred (in terms of additional space and additional time). This is because an index is created to maintain the UNIQUE constraint. You cannot use the DROP INDEX statement to drop an index used to maintain the UNIQUE constraint.

• A UNIQUE constraint and its associated index cannot be dropped if it is being used as a unique index on a replicated table.

• Use ALTER TABLE...USE RANGE INDEX if your application performs range queries over a table's primary key.

• Use ALTER TABLE...USE HASH INDEX if your application performs exact match lookups on a table's primary key.

• An error is generated if a table has no primary key and either the USE HASH INDEX clause or the USE RANGE INDEX clause is specified.

• If ON DELETE CASCADE is specified on a foreign key constraint for a child table, a user can delete rows from a parent table for which the user has the DELETE privilege without requiring explicit DELETE privilege on the child table.

• To change the ON DELETE CASCADE triggered action, drop then redefine the foreign key constraint.

• ON DELETE CASCADE is supported on detail tables of a materialized view. If you have a materialized view defined over a child table, a deletion from the parent table causes cascaded deletes in the child table. This, in turn, triggers changes in the materialized view.

• The total number of rows reported by the DELETE statement does not include rows deleted from child tables as a result of the ON DELETE CASCADE action.

• For ON DELETE CASCADE, since different paths may lead from a parent table to a child table, the following rule is enforced:

• Either all paths from a parent table to a child table are "delete" paths or all paths from a parent table to a child table are "do not delete" paths.

  • Specify ON DELETE CASCADE on all child tables on the "delete" path.

  • This rule does not apply to paths from one parent to different children or from different parents to the same child.

• For ON DELETE CASCADE, a second rule is also enforced:

• If a table is reached by a "delete" path, then all its children are also reached by a "delete" path.

• The ALTER TABLE ADD/DROP CONSTRAINT statement has the following restrictions:

  • When a foreign key is dropped, TimesTen also drops the index associated with the foreign key. Attempting to drop an index associated with a foreign key using the regular DROP INDEX statement results in an error.

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

  • You cannot use ALTER TABLE to drop a primary key constraint. You would have to drop and recreate the table in order to drop the constraint.

Examples for ALTER TABLE: TimesTen Scaleout

Table 6-6, "ALTER TABLE rules" shows the rules associated with altering tables. Supporting examples follow.

ALTER TABLE t1 ADD CONSTRAINT c1
 PRIMARY KEY (p);

CREATE TABLE t1 (c1 NUMBER, 
 c2 VARCHAR2 (10));

ALTER TABLE t1 
 DISTRIBUTE BY HASH (c1);

ALTER TABLE t1 ADD CONSTRAINT c1
 FOREIGN KEY (f1)REFERENCES t2 (c2);

CREATE TABLE t1...CONSTRAINT fk1...
 DISTRIBUTE BY REFERENCE(fk1);

ALTER TABLE t1 DROP CONSTRAINT(fk1);

These examples support the information in the "ALTER TABLE rules" table:

• Example 6-8, "Use ALTER TABLE to add a primary key constraint"

• Example 6-9, "Add primary key constraint on table distributed on unique column"

• Example 6-10, "Use ALTER TABLE to change the distribution key"

• Example 6-11, "Add a foreign key constraint that is not part of the distribution key"

• Example 6-12, "Attempt to drop a foreign key constraint used as a distribution key"

Command> CREATE TABLE mytable (col1 NUMBER NOT NULL, col2 VARCHAR2 (32));
Command> describe mytable;
 
Table SAMPLEUSER.MYTABLE:
  Columns:
    COL1                            NUMBER NOT NULL
    COL2                            VARCHAR2 (32) INLINE
  DISTRIBUTE BY HASH
 
1 table found.
(primary key columns are indicated with *)

Command> ALTER TABLE mytable ADD CONSTRAINT c1 PRIMARY KEY (col1);
Command> describe mytable;
 
Table SAMPLEUSER.MYTABLE:
  Columns:
   *COL1                            NUMBER NOT NULL
    COL2                            VARCHAR2 (32) INLINE
  DISTRIBUTE BY HASH
 
1 table found.
(primary key columns are indicated with *)

Command> CREATE TABLE mytab (id TT_INTEGER NOT NULL, id2 TT_INTEGER UNIQUE, 
           id3 TT_INTEGER) distribute by hash (id2);
Command> ALTER TABLE mytab ADD CONSTRAINT c1 PRIMARY KEY (id);                  Command> describe mytab;
 
Table SAMPLEUSER.MYTAB:
  Columns:
   *ID                              TT_INTEGER NOT NULL
    ID2                             TT_INTEGER UNIQUE
    ID3                             TT_INTEGER
  DISTRIBUTE BY HASH (ID2)
 
1 table found.
(primary key columns are indicated with *)

Command> CREATE TABLE mytable2 (col1 NUMBER NOT NULL, col2 VARCHAR2 (32))
         DISTRIBUTE BY HASH (col1,col2);
Command> describe mytable2;
 
Table SAMPLEUSER.MYTABLE2:
  Columns:
    COL1                            NUMBER NOT NULL
    COL2                            VARCHAR2 (32) INLINE
  DISTRIBUTE BY HASH (COL1, COL2)
 
1 table found.
(primary key columns are indicated with *)

Command> ALTER TABLE mytable2 DISTRIBUTE BY HASH (col1);
Command> describe mytable2;
 
Table SAMPLEUSER.MYTABLE2:
  Columns:
    COL1                            NUMBER NOT NULL
    COL2                            VARCHAR2 (32) INLINE
  DISTRIBUTE BY HASH (COL1)
 
1 table found.
(primary key columns are indicated with *)

Command> INSERT INTO mytable2 VALUES (10, 'test');
1 row inserted.
Command> commit;
Command> ALTER TABLE mytable2 DISTRIBUTE BY HASH (col1,col2);
 1069: Table not empty. Alter table distribution is only permitted on empty
tables.
The command failed.

Command> describe accounts;
 
Table SAMPLEUSER.ACCOUNTS:
  Columns:
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
    PHONE                           VARCHAR2 (15) INLINE NOT NULL
    ACCOUNT_TYPE                    CHAR (1) NOT NULL
    STATUS                          NUMBER (2) NOT NULL
    CURRENT_BALANCE                 NUMBER (10,2) NOT NULL
    PREV_BALANCE                    NUMBER (10,2) NOT NULL
    DATE_CREATED                    DATE NOT NULL
    CUST_ID                         NUMBER (10) NOT NULL
  DISTRIBUTE BY REFERENCE (FK_CUSTOMER)
 
1 table found.
(primary key columns are indicated with *)

Command> describe accounts2;
 
Table SAMPLEUSER.ACCOUNTS2:
  Columns:
   *ACCOUNTS2_ID                    NUMBER (10) NOT NULL
    ACCOUNT_ORIG_ID                 NUMBER (10) NOT NULL
    STATUS                          NUMBER (2) NOT NULL
  DISTRIBUTE BY HASH (ACCOUNTS2_ID)
 
1 table found.
(primary key columns are indicated with *)

Command> ALTER TABLE accounts2 ADD CONSTRAINT accounts2_fk FOREIGN KEY 
           (account_orig_id) REFERENCES accounts (account_id);

Command> indexes accounts2;
 
Indexes on table SAMPLEUSER.ACCOUNTS2:
  ACCOUNTS2: unique range index on columns:
    ACCOUNTS2_ID
  ACCOUNTS2_FK: non-unique range index on columns:
    ACCOUNT_ORIG_ID
    (foreign key index references table SAMPLEUSER.ACCOUNTS(ACCOUNT_ID))
  2 indexes found.
 
2 indexes found on 1 table.

Command> describe transactions;
 
Table SAMPLEUSER.TRANSACTIONS:
  Columns:
   *TRANSACTION_ID                  NUMBER (10) NOT NULL
   *ACCOUNT_ID                      NUMBER (10) NOT NULL
   *TRANSACTION_TS                  TIMESTAMP (6) NOT NULL
    DESCRIPTION                     VARCHAR2 (60) INLINE
    OPTYPE                          CHAR (1) NOT NULL
    AMOUNT                          NUMBER (6,2) NOT NULL
  DISTRIBUTE BY REFERENCE (FK_ACCOUNTS)
 
1 table found.
(primary key columns are indicated with *)

Command> ALTER TABLE transactions DROP CONSTRAINT fk_accounts;
 1072: Dropping a table's reference by distribution foreign key is not allowed.
The command failed.

SQL syntax for ALTER TABLE: TimesTen Classic

To add one column:

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

To add multiple columns:

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

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

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

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

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

The CompressColumns syntax is as follows:

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

To remove columns.

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

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

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

ALTER TABLE [Owner.]TableName
USE RANGE INDEX

To change the default value of a column:

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

To add or drop a unique constraint on a column:

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

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

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

To add LRU aging:

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

To add time-based aging:

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

To change the aging state:

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

To drop aging:

ALTER TABLE [Owner.]TableName
DROP AGING

To change the lifetime for time-based aging:

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

To change the cycle for time-based aging:

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

Parameters for ALTER TABLE: TimesTen Classic

Description for ALTER TABLE: TimesTen Classic

• The ALTER TABLE statement cannot be used to alter a temporary table.

• The ALTER TABLE ADD [COLUMN] ColumnName statement adds one or more new columns to an existing table. When you add one or more columns, the new columns are added to the end of all existing rows of the table in one new partition.

• The ALTER TABLE ADD or DROP COLUMN statement can be used to add or drop columns from replicated tables.

  Do not use ALTER TABLE to alter a replicated table that is part of a TWOSAFE BY REQUEST transaction.

• Columns referenced by materialized views cannot be dropped.

• You cannot use the ALTER TABLE statement to add a column, drop a column, or add a constraint for cache group tables.

• Only one partition is added to the table per statement regardless of the number of columns added.

• You can ALTER a table to add a NOT NULL column with a default value. The DEFAULT clause is required. Restrictions include:

  • You cannot use the column as a primary key column. Specifically, you cannot specify the column in the statement: ALTER TABLE ADD ConstraintName PRIMARY KEY (ColumnName [,...]).

  • You cannot use the column for time-based aging. Specifically, you cannot specify the column in the statement ALTER TABLE ADD AGING USE ColumnName.

    Note:

    To add a NOT NULL column to a table that is part of a replication scheme, DDL_REPLICATON_LEVEL must be 3 or greater.

• NULL is the initial value for all added columns, unless a default value is specified for the new column.

• The total number of columns in the table cannot exceed 1000. In addition, the total number of partitions in a table cannot exceed 1000, one of which is used by TimesTen.

• Use the ADD CONSTRAINT ... PRIMARY KEY clause to add a primary key constraint to a regular table or to a detailed or materialized view table. Do not use this clause on a table that already has a primary key.

• If you use the ADD CONSTRAINT... PRIMARY KEY clause to add a primary key constraint, and you do not specify the USE HASH INDEX clause, then a range index is used for the primary key constraint.

• If a table is replicated and the replication agent is active, you cannot use the ADD CONSTRAINT ... PRIMARY KEY clause. Stop the replication agent first.

• Do not specify the ADD CONSTRAINT ... PRIMARY KEY clause on a global temporary table.

• Do not specify the ADD CONSTRAINT ... PRIMARY KEY clause on a cache group table because cache group tables defined with a primary key must be defined in the CREATE CACHE GROUP statement.

• As the result of an ALTER TABLE ADD statement, an additional read occurs for each new partition during queries. Therefore, altered tables may have slightly degraded performance. The performance can only by restored by dropping and recreating the table, or by using the ttMigrate create -c -relaxedUpgrade command, and restoring the table using the ttRestore -r -relaxedUpgrade command. Dropping the added column does not recover the lost performance or decrease the number of partitions.

• When you use the ALTER TABLE DROP statement to remove one or more columns from an existing table, dropped columns are removed from all current rows of the table. Subsequent SQL statements must not attempt to make any use of the dropped columns. You cannot drop columns that are in the table's primary key. You cannot drop columns that are in any of the table's foreign keys until you have dropped all foreign keys. You cannot drop columns that are indexed until all indexes on the column have been dropped. ALTER TABLE cannot be used to drop all of the columns of a table. Use DROP TABLE instead.

• When a column is dropped from a table, all commands referencing that table need to be recompiled. An error may result at recompilation time if a dropped column was referenced. The application must re-prepare those commands, and rebuild any parameters and result columns. When a column is added to a table, the commands that contain a SELECT * statement are invalidated. Only these commands must be re-prepared. All other commands continue to work as expected.

• When you drop a column, the column space is not freed.

• When you add a UNIQUE constraint, there is overhead incurred (in terms of additional space and additional time). This is because an index is created to maintain the UNIQUE constraint. You cannot use the DROP INDEX statement to drop an index used to maintain the UNIQUE constraint.

• A UNIQUE constraint and its associated index cannot be dropped if it is being used as a unique index on a replicated table.

• Use ALTER TABLE...USE RANGE INDEX if your application performs range queries over a table's primary key.

• Use ALTER TABLE...USE HASH INDEX if your application performs exact match lookups on a table's primary key.

• An error is generated if a table has no primary key and either the USE HASH INDEX clause or the USE RANGE INDEX clause is specified.

• Make sure to stop the replication agent before adding or dropping a foreign key on a replicated table.

• If ON DELETE CASCADE is specified on a foreign key constraint for a child table, a user can delete rows from a parent table for which the user has the DELETE privilege without requiring explicit DELETE privilege on the child table.

• To change the ON DELETE CASCADE triggered action, drop then redefine the foreign key constraint.

• ON DELETE CASCADE is supported on detail tables of a materialized view. If you have a materialized view defined over a child table, a deletion from the parent table causes cascaded deletes in the child table. This, in turn, triggers changes in the materialized view.

• The total number of rows reported by the DELETE statement does not include rows deleted from child tables as a result of the ON DELETE CASCADE action.

• For ON DELETE CASCADE, since different paths may lead from a parent table to a child table, the following rule is enforced:

• Either all paths from a parent table to a child table are "delete" paths or all paths from a parent table to a child table are "do not delete" paths.

  • Specify ON DELETE CASCADE on all child tables on the "delete" path.

  • This rule does not apply to paths from one parent to different children or from different parents to the same child.

• For ON DELETE CASCADE, a second rule is also enforced:

• If a table is reached by a "delete" path, then all its children are also reached by a "delete" path.

• For ON DELETE CASCADE with replication, the following restrictions apply:

  • The foreign keys specified with ON DELETE CASCADE must match between the Master and subscriber for replicated tables. Checking is done at runtime. If there is an error, the receiver thread stops working.

  • All tables in the delete cascade tree have to be replicated if any table in the tree is replicated. This restriction is checked when the replication scheme is created or when a foreign key with ON DELETE CASCADE is added to one of the replication tables. If an error is found, the operation is aborted. You may be required to drop the replication scheme first before trying to change the foreign key constraint.

• The ALTER TABLE ADD/DROP CONSTRAINT statement has the following restrictions:

  • When a foreign key is dropped, TimesTen also drops the index associated with the foreign key. Attempting to drop an index associated with a foreign key using the regular DROP INDEX statement results in an error.

  • Foreign keys cannot be added or dropped on tables in a cache group.

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

  • You cannot use ALTER TABLE to drop a primary key constraint. You would have to drop and recreate the table in order to drop the constraint.

• After you have defined an aging policy for the table, you cannot change the policy from LRU to time-based or from time-based to LRU. You must first drop aging and then alter the table to add a new aging policy.

• The aging policy must be defined to change the aging state.

• The following rules determine if a row is accessed or referenced for LRU aging:

  • Any rows used to build the result set of a SELECT statement.

  • Any rows used to build the result set of an INSERT ... SELECT statement.

  • Any rows that are about to be updated or deleted.

• Compiled commands are marked invalid and need recompilation when you either drop LRU aging from or add LRU aging to tables that are referenced in the commands.

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

• For the time-based aging policy, you cannot add or modify the aging column. This is because you cannot add or modify a NOT NULL column.

• Aging restrictions:

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

  • Tables that are related by foreign keys must have the same aging policy.

  • For LRU aging, if a child row is not a candidate for aging, neither this child row nor its parent row are deleted. ON DELETE CASCADE settings are ignored.

  • For time-based aging, if a parent row is a candidate for aging, then all child rows are deleted. ON DELETE CASCADE (whether specified or not) is ignored.

• Restrictions for column-based compression of tables:

  • You can add compressed column groups with the ALTER TABLE statement only if the table was enabled for compression at table creation. You can add uncompressed columns to any table, including tables enabled for compression. Refer to "Column-based compression of tables (TimesTen Classic)" for more details on adding compressed column groups to a table.

  • You cannot modify columns of a compressed column group.

  • You can drop all columns within a compressed column group with the ALTER TABLE command; when removing columns in a compressed column group, all columns in the compressed column group must be specified for removal.

  • You cannot use ALTER TABLE to modify an existing uncompressed column to make it compressed. For example:

    Command> create table mytab (a varchar2 (30), b int, c int) compress ((a,b)
               by dictionary);
    Command> alter table mytab add (d int) compress (c by dictionary);
    2246: Cannot change compression clause for already defined column C 
    The command failed.
    

Understanding partitions when using ALTER TABLE in TimesTen

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

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

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

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

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

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

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

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

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

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

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

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

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

ttDestroy sampledb_1122

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

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

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

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

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

Examples for ALTER TABLE: TimesTen Classic

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

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

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

• If (SYSDATE - ColumnValue) = 3 days, 22 hours, then row is not aged out because lifetime was specified in days. The row would be aged out if lifetime had been specified as 72 hours.

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

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

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

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

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

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

Drop aging on the table and redefine with LRU aging.

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

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

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

ALTER TABLE AgingDemo4
       SET AGING OFF;

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

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

Call ttAgingScheduleNow to invoke aging with an external scheduler:

Command> CALL ttAgingScheduleNow ('agingdemo4');

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

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

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

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

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

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

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

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

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

See also

Required privilege

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

ADMIN privilege is required for all other operations.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

This is the syntax for ALTER USER...IDENTIFIED BY. Ensure to specify at least one of these clauses: IDENTIFIED BY, PROFILE, ACCOUNT, or PASSWORD EXPIRE.

ALTER USER user [IDENTIFIED BY {password | "password"}]
  [PROFILE profile] [ACCOUNT {LOCK|UNLOCK}] [PASSWORD EXPIRE]

This is the syntax for ALTER USER...IDENTIFIED EXTERNALLY. Ensure to specify at least one of these clauses: IDENTIFIED EXTERNALLY, PROFILE, or ACCOUNT.

ALTER USER user [IDENTIFIED EXTERNALLY]
  [PROFILE profile] [ACCOUNT {LOCK|UNLOCK}]

Parameters

Description

• Database users can be internal or external.

  • Internal users are defined for a TimesTen database.

  • External users are defined by the operating system. External users cannot be assigned a TimesTen password.

• Passwords are case-sensitive.

• Use the PROFILE clause to change the profile for a user. See "CREATE PROFILE" for details.

• Use the ACCOUNT LOCK or ACCOUNT UNLOCK to change the lock settings for the user account.

• Use the PASSWORD EXPIRE clause to expire the user's password and force a password change before the user can connect to the database.

• You can alter a user over a client/sever connection if the connection is encrypted with TLS. See "Transport Layer Security for TimesTen Client/Server" in the Oracle TimesTen In-Memory Database Security Guide for details.

• When replication is configured, this statement is replicated.

Examples

Example 1: Change the user's profile

This example creates the user1 user and assigns the user1 user the profile1 profile. The example then uses the ALTER USER statement to change the user1 user's profile to profile2.

Command> CREATE USER user1 IDENTIFIED BY user1 PROFILE profile1;
 
User created.
 
Command> ALTER USER user1 PROFILE profile2;
 
User altered.

Query the dba_users system view to verify the user1 profile has been changed to profile2.

Command> SELECT profile FROM dba_users WHERE username = 'USER1';
< PROFILE2 >
1 row found.

Example 2: Lock and unlock a user's account

This example creates the user2 user. It then uses the ALTER USER statement to lock and then unlock the user2 user's account.

Command> CREATE USER user2 IDENTIFIED BY user2 PROFILE profile1;
 
User created.
 
Command> ALTER USER user2 ACCOUNT LOCK;
 
User altered.
 

Grant the CONNECT privilege to user2;

Command> GRANT CONNECT TO user2;

Attempt to connect to the database as user2. The user2 account is locked so the connection fails.

Command> connect adding "UID=user2;PWD=user2" as user2;
15179: the account is locked
The command failed.

As the instance administrator, reconnect to the database and use the ALTER USER statement to unlock the user2 account.

none: Command> use database1
database1: Command> ALTER USER user2 ACCOUNT UNLOCK;
 
User altered.
 

Attempt to connect to the database as the user2 user. The connection succeeds.

database1: Command> connect adding "UID=user2;PWD=user2" as user2;
Connection successful: DSN=database1;UID=user3;DataStore=/scratch/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Example 3: Expire a user's password

This example uses the ALTER USER statement to change the user2 user's account to expire the password. A user with ADMIN privilege must change the user2 password before user2 can connect to the database.

Command> ALTER USER user2 PASSWORD EXPIRE;
 
User altered.
 

Attempt to connect to the database as user2. The user2 password must be changed before the user2 user can connect to the database.

Command>  connect adding "UID=user2;PWD=user2" as user2;
15180: the password has expired
The command failed.

As the instance administrator, reconnect to the database and use the ALTER USER statement to change the user2 password.

none: Command> use database1
database1: Command>  ALTER USER user2 IDENTIFIED BY newuser2password;
 
User altered.
 

Attempt to connect to the database a the user2 user. The connection succeeds.

database1: Command> connect adding "UID=user2;PWD=newuser2password" as user2;
Connection successful: DSN=database1;UID=user4;DataStore=/scratch/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Example 4: Change a user from external to internal and internal to external

This example uses the ALTER USER statement to change the user2 internal user to an external user and then back to an internal user.

Command> ALTER USER user2 IDENTIFIED EXTERNALLY;
 
User altered.

Use the ALTER USER statement to change the user2 external user back to an internal user.

Command> ALTER USER user2 IDENTIFIED BY user2_password_change;
 
User altered.

See also

Required privilege

The privileges required for executing 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.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

To call a TimesTen built-in procedure:

CALL TimesTenBuiltIn [( arguments )]

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

To call a PL/SQL procedure:

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

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

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

Parameters

Description

Detailed information on how to execute PL/SQL procedures or functions with the CALL statement in TimesTen is provided in "Executing 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

Required privilege

None

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

COMMIT [WORK]

Parameters

The COMMIT statement enables the following optional keyword:

Description

• Until you commit a transaction:

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

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

• This statement releases transaction locks.

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

• A commit closes all open cursors.

Examples

Insert a 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

Required privilege

ADMIN

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

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

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 
  TABLE DEFINITION CHECKING {RELAXED|EXACT}

Syntax for NetworkOperation:

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

Parameters

Description

• After you create an active standby pair, make one of your databases the active database. To accomplish this, call ttRepStateSet ('ACTIVE'). Then use ttRepAdmin to duplicate the active database to the second database. When the operation is successful, the second database becomes the standby database. For more information, see "Setting up an active standby pair with no cache groups" in Oracle TimesTen In-Memory Database Replication Guide.

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

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

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

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

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

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

• You can use an active standby pair to replicate read-only cache groups and asynchronous writethrough (AWT) cache groups. You cannot use an active standby pair to replicate synchronous writethrough (SWT) cache groups or user managed cache groups.

• You cannot use the EXCLUDE clause for AWT cache groups.

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

Examples

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

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

Specify NetworkOperation clause to control network interface:

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

See also

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

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

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

• CREATE READONLY CACHE GROUP

• CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP

• CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP

• CREATE USERMANAGED CACHE GROUP

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

Parameters

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

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

Table definitions have the following parameters.

Description

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

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

• Dynamic parameters are not allowed in the WHERE clause.

• Oracle Database temporary tables cannot be cached.

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

• In the Oracle database, you can define a parent/child relationship and then insert a null value into the foreign key column of the child table. This means this row in the child table references a null parent. You can then create a cache group and cache the parent/child relationship of the Oracle database tables. However, if you load data from the Oracle database tables into the cache group, the row that contains the null value of the foreign key column is not loaded. TimesTen recommends that you do not create cache groups if the tables you cache define a parent/child relationship in which the foreign key represents a null parent.

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

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

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

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

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

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

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

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

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

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

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

Required privilege

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

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

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

InvokerRightsClause::=
AUTHID {CURRENT_USER|DEFINER}

AccessibleByClause::=
ACCESSIBLE BY (accessor[,...])

accessor::= 
[UnitKind][Owner.]UnitName

You can specify InvokerRightsClause, AccessibleByClause, or DETERMINISTIC in any order.

Parameters

Description

• AccessibleByClause:

  • The compiler checks the validity of the syntax of the ACCESSIBLE BY clause, but does not check that the accessor exists. Therefore, you can define an accessor that does yet exist in the owner's schema.

  • When you invoke the function, the compiler first does the normal permission checks on the invocation. If any check fails, the invocation fails, even if the invoker is an accessor. If all normal permission checks on the invocation succeed, and the function has no ACCESSIBLE BY clause, the invocation succeeds. If the function has an ACCESSIBLE BY clause, the invocation succeeds only if the invoker is an accessor.

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

• 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 classic replication scheme" in the Oracle TimesTen In-Memory Database Replication Guide.

• TimesTen does not support:

  • parallel_enable_clause

    You can specify this clause, but it has no effect.

  • call_spec clause

  • AS EXTERNAL clause

Examples

Using the AccessibleByClause:

This example creates the ProtectedFunction function. The ACCESSIBLE BY clause is used to restrict the invocation of the function to the CallingProc1 and CallingProc2 procedures. Note that for CallingProc1, the type of PL/SQL unit is not specified and for CallingProc2, the type of PL/SQL unit is specified (PROCEDURE).

Command> CREATE OR REPLACE FUNCTION ProtectedFunction (a IN NUMBER)
           RETURN NUMBER
         ACCESSIBLE BY (CallingProc1, PROCEDURE CallingProc2)
         AS
         BEGIN
           RETURN a * 1;
         END;
         /
         
Function created.

Create the CallingProc1 and CallingProc2 procedures.

Command> CREATE OR REPLACE PROCEDURE CallingProc1 AS
           a NUMBER:=1;
         BEGIN
           a:=ProtectedFunction(a);
           DBMS_OUTPUT.PUT_LINE ('Calling Procedure: '|| a);
         END;
         /
         
Procedure created.

Command> CREATE OR REPLACE PROCEDURE CallingProc2
         AS
           a NUMBER:=2;
         BEGIN
           a:=ProtectedFunction(a);
           DBMS_OUTPUT.PUT_LINE ('Calling Procedure: '|| a);
         END;
         /
         
Procedure created.

Call the procedures. CallingProc1 and CallingProc2 are in the white list, resulting in successful execution.

Command> SET SERVEROUTPUT ON
Command> exec CallingProc1;
Calling Procedure: 1
 
PL/SQL procedure successfully completed.
 
Command> exec CallingProc2;
Calling Procedure: 2
 
PL/SQL procedure successfully completed.

Illustrating the syntax for creating a PL/SQL function

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.

Required privilege

No privilege is required for owner.

If not the owner, the system privilege, CREATE ANY INDEX, or the object privilege, INDEX, is required.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

To create a range index:

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

To create a hash index:

CREATE [UNIQUE] HASH INDEX [Owner.]IndexName ON
[Owner.]TableName ({ColumnName [ASC | DESC]}
[,... ] ) [ PAGES = RowPages | CURRENT ]

Parameters

Description

• TimesTen creates a nonunique range index by default. Specify CREATE UNIQUE INDEX to create a unique range index.

• To create a nonunique hash index, specify CREATE HASH INDEX. To create a unique hash index, specify CREATE UNIQUE HASH INDEX.

• You cannot create an index on LOB columns.

• The CREATE INDEX statement enters the definition of the index in the system catalog and initializes the necessary data structures. Any rows in the table are then added to the index.

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

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

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

• NULL compares higher than all other values for sorting.

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

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

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

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

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

• To change the size or type of a hash index, drop the hash index and create a new index.

• A hash index is created with a fixed size that remains constant for the life of the table. To resize the hash index, drop and recreate the index. A smaller hash index results in more hash collisions. A larger hash index reduces collisions but can waste memory. Hash key comparison is a fast operation, so a small number of hash collisions should not cause a performance problem for TimesTen.

  To ensure that your hash index is sized correctly, your application must indicate the expected size of your table with the value of the RowPages parameter of the SET PAGES clause. Compute this value by dividing the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for the value of RowPages (256000/256=1000).

• The maximum number of columns that can be specified for an index is 16.