7 SQL Developer: TimesTen Support

To create a named connection to a TimesTen database, make sure you are on the main SQL Developer page.

1. Right-click the Connections node and select New Connection.

   The New/Select Database Connection dialog appears.

2. Select the TimesTen tab.

   You are ready to enter information for the connection.

3. In the Connection Name field, enter the connection name.

4. In the Username field, enter the TimesTen user. If you use the TimesTen database to cache data from an Oracle database, enter the TimesTen cache manager user name. If you use the TimesTen database to load data from an Oracle database, enter the TimesTen user name that has SELECT privileges on the Oracle database tables that you want to load.

5. In the Password field, enter the password for the TimesTen user.

   To save the password, choose Save Password. If you save the password, you will not be prompted for the password on subsequent connections to the TimesTen database.

6. To allow TimesTen to issue an implicit commit after DML statements, select the Autocommit check box. By default, the Autocommit check box is selected.

7. Select a connection type from the Connection Type drop-down list. The following types of connections are available:

   • DSN

   • Easy Client/Server

   • Advanced

   You are now ready to define your new connection.

You must create a cache administration user in the Oracle database. This user creates, owns and maintains Oracle objects that store information used to manage a specific cache grid. This user also enforces predefined behaviors of cache groups.

After you create the cache administration user in the Oracle database, you must set the name and password for the user in the TimesTen database.

To set the cache administration user name and password, right-click the node of the connection name for the TimesTen database and select Set Cache Administration User.

The Setting the cache administration user dialog appears. For the Prompts tab, in the Cache administration user ID field, type the cache administration user name. In the Cache administration user password field, type the Oracle password for the cache administration user.

Click Apply.

You must set the cache administration user name and password for each TimesTen database that caches Oracle. If you drop and re-create the TimesTen database or drop and re-create the cache administration user in the Oracle database, then the cache administration user name and password must be reset in the TimesTen database.

After you set the cache administration user name and password, create a cache grid and associate the TimesTen database with the cache grid, you must start the TimesTen cache agent process. The cache agent processes cache operations such as loading a cache group and automatic refresh.

To start the cache agent, right-click the node of the connection name for the TimesTen database and select Start/Stop Cache Agent.

In the Starting/stopping the cache agent dialog box, select Start agent within the Prompts tab. Then click Apply to start the cache agent.

To stop a running cache agent process, right-click the node of the connection name for the TimesTen database and select Start/Stop Cache Agent in an identical fashion as was done when starting the cache agent.

In the Starting/stopping the cache agent dialog box, select Stop agent within the Prompts tab. Then click Apply to stop the cache agent.

A cache group defines the set of Oracle data to cache in the TimesTen database. When you create a cache group, cache tables are created in the TimesTen database that correspond to the Oracle tables being cached.

To create a cache group, right-click the Cache Groups node under the node of the connection name for the TimesTen database and select New Cache Group.

In the Create cache group dialog, specify a name for the cache group in the Name field within the Cache Group Attributes tab. For Type, choose the desired cache group type (read-only, asynchronous writethrough, synchronous writethrough or user managed).

Cache groups are categorized as either explicitly loaded or dynamic. If you are creating a dynamic cache group, then choose Dynamic in the Type section.

In an explicitly loaded cache group, data is loaded manually into its cache tables from the corresponding Oracle tables using a load or refresh operation, or automatically using an automatic refresh operation. In a dynamic cache group, data is loaded into its cache tables on demand from the corresponding Oracle tables using a dynamic load operation, or manually using a load operation.

A read-only, asynchronous writethrough (AWT) or synchronous writethrough (SWT) cache group can be defined as a dynamic cache group. A user managed cache group can be defined as a dynamic cache group if it does not contain all of the following characteristics:

• Automatic refresh is defined

• At least one of its cache tables are updatable

• Committed updates on one or more of its updatable cache tables are propagated to the corresponding Oracle tables

Cache groups are classified as either local or global. If you are creating a global cache group, then select the Global in the Type section.

In a local cache group, data in its cache tables are not shared across TimesTen databases even if the databases are members of the same cache grid. In a global cache group, data in its cache tables are shared across TimesTen databases that are members of the same cache grid.

Only an AWT cache group can be defined as global cache group.

See also the following related topics:

• Defining a cache group and cache table attributes

• Defining the root table

• Defining the child tables

When you drop a cache group, its cache tables are also dropped.

Under the Cache Groups node within the node of the connection name for the TimesTen database, right-click the name of the cache group to drop and select Drop.

Click Apply to drop the cache group.

For more information about dropping a cache group, see "Dropping a cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

Click the + to the left of the Cache Groups node to view the list of cache groups. Next right-click the name of the cache group to load or refresh. Select Load to load the cache group or Refresh to refresh the cache group.

The cache agent must be running to load or refresh a cache group. For information about how to start the cache agent, see Start and Stop the Cache Agent.

For a read-only cache group or a user managed cache group that has automatic refresh defined, you must set the automatic refresh state to Paused before you can load or refresh the cache group. For information about changing the automatic refresh state of a cache group, see Alter the Automatic Refresh Attributes of a Cache Group.

In the Prompts tab of the Load or Refresh dialog, specify a numeric value in the Commit every n rows field to indicate the frequency, based on the number of rows that are loaded or refreshed into the cache group, at which a commit is issued during the load or refresh operation. By default, a transaction is committed after every 256 rows are loaded or refreshed into the cache group.

In the Number of threads to run in parallel field within the Prompts tab of the Load or Refresh dialog box, specify the number of cache agent process threads to spawn and use for the load or refresh operation. Parallel processing of the load or refresh operation can potentially improve the operation's throughput. The numeric value specified in this field cannot exceed 10 and should not exceed the number of CPUs on the system that can be dedicated to the parallel load or refresh operation. The default value of 1 indicates that the load or refresh operation is processed serially. For more information about parallel loading or refreshing of cache groups, see "Improving the performance of loading or refreshing a large number of cache instances" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

You can specify an optional WHERE clause for the load or refresh operation in the Where clause field within the Prompts tab of the Load or Refresh dialog. You can use a WHERE clause for a load or refresh operation on an AWT, SWT or user managed cache group that does not have automatic refresh defined. For more information about specifying a WHERE clause in a load or refresh operation, see "Loading and refreshing a cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

Click Apply to load or refresh the cache group.

For more information about loading or refreshing a cache group, see "Loading and refreshing a cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

Only user managed cache groups containing cache tables that are not all read-only, or not all updatable where updates on the tables are propagated to the corresponding Oracle tables can be flushed.

Click the + to the left of the Cache Groups node to view the list of cache groups. Next right-click the name of the cache group that you to flush. Select Flush to flush the cache group.

In the Where clause field within the Prompts tab of the Flush dialog, specify an optional WHERE clause for the flush operation.

Click Apply to flush the cache group.

For more information about flushing a cache group, see "Flushing a user managed cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

Click the + to the left of the Cache Groups node to view the list of cache groups. Next right-click the name of the cache group to unload. Select Unload to unload the cache group.

In the Where clause field within the Prompts tab of the Unload dialog, specify an optional WHERE clause for the unload operation. You can use a WHERE clause for an unload operation on an AWT, SWT or user managed cache group that does not have automatic refresh defined.

Click Apply to unload the cache group.

For more information about unloading a cache group, see "Unloading a cache group" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

You can alter the automatic refresh attributes of a read-only cache group or a user managed cache group that has automatic refresh defined.

Under the Cache Groups node, right-click the name of the cache group and select Autorefresh.

In the Prompts tab of the Autorefresh dialog, you can change any of the following automatic refresh attributes:

• To change the automatic refresh mode, in the Mode field, choose either Incremental or Full.

• To change the automatic refresh interval, in the Refresh Interval field, specify a numeric interval and units of time in minutes, seconds or milliseconds.

• To change the automatic refresh state, in the Refresh State field, choose Paused, On or Off.

Click Apply.

For more information about automatic refresh of cache groups, see "AUTOREFRESH cache group attribute" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

If you are using TimesTen release 11.2.2.2.0 or greater, then you can specify and display LOB data types in your table definitions.

If you want to create a table with a LOB data type, then in the Create Table dialog, expand the Type column header. You see CLOB, NCLOB, and BLOB as valid data types.

To view the data type for your columns, select the table and then choose the Columns tab. You see the LOB data type for your column.

If you want to add a column and specify a LOB data type, in the Add Column dialog, expand Data Type. You see CLOB, NCLOB, and BLOB among the possible choices.

For PL/SQL objects, you can specify LOB data types as IN, OUT, and IN OUT parameters. In the PL/SQL edit dialogs, for parameter types, choose CLOB, NCLOB, or BLOB.

You can specify the INLINE attribute for columns of type VARCHAR2, NVARCHAR2, and VARBINARY.

In the Create Table dialog, locate the column header named Inline. Click in the checkbox to define the column with the INLINE attribute.

You can also specify the INLINE attribute when adding a column to a table. Right-click on the table, select Column, then select Add. The Add Column dialog appears. For columns of type VARCHAR2, NVARCHAR2, and VARBINARY, click in the Inline checkbox to add the column with the INLINE attribute.

If you are using TimesTen Release 11.2.2.0 or greater, you can compute table size information.

To view table size information, you must first compute the table size:

1. Choose + to the left of the Tables node to view the list of tables.

2. Right-click the name of the table to compute table size information.

3. Select Table, then select Compute Size.

   The Compute Size dialog appears. The owner and name of the table are displayed.

4. Click in the checkbox if you want to compute out-of-line sizes.

5. Choose Apply.

   A Confirmation dialog appears.

6. Choose OK.

   The table size statistics for the table are computed.

To view the SQL for computing the table size, choose the SQL tab in the Compute Size dialog. You see that a TimesTen built-in procedure called ttComputeTabSizes is executed. After this built-in is executed, you can review the table size information for your table. Note that this table size information is on a per table basis.

To compute table sizes for all tables in your database including materialized views, system tables, and tables that are part of cache groups, use the SQL Worksheet and execute the command: Call ttComputeTabSizes (NULL,0); or to include out-of-line data: Call ttComputeTabSizes (NULL,1);.

After you compute the table size for one or more tables, you can view the table size information:

1. Choose + to the left of the Tables node to view the list of tables.

2. Choose the table you want to review table size information.

3. Choose the Sizes tab located among the tabs that display the attributes of the table.

   Table size information is displayed. Specifically, values for INLINE BYTES, OUT OF LINE BYTES, and METADATA BYTES are shown.

You can choose the Actions menu to generate or regenerate the table size. If you choose the Actions menu, then Table, then select Compute Size. The Compute Size dialog appears allowing you to compute the table size information.

Choose Refresh to refresh the displayed table size data.

You can view table size information for all tables that you have computed table sizes. To view such information, select TimesTen Reports, then Table, then select Table Sizes.

For more information on the ttComputeTabSizes built-in procedure, see "ttComputeTabSizes" in Oracle TimesTen In-Memory Database Reference.

You can test a PL/SQL function, procedure, or package by defining a REF CURSOR as an OUTPUT parameter in your PL/SQL function, procedure, or package. After you define a REF CURSOR, compile and run your PL/SQL function, procedure, or package. The Run dialog appears. Choose OK.

After you choose OK in the Run PL/SQL dialog, you can view the details of the run in the two tabs:

1. IdeConnections: Displays the status of your run including any errors encountered.

2. Output Variables: Shows the output from the execution of the function, procedure, or package.

You can create a range index or a bitmap index on a regular table or on a cache table.

To create an index, click the + to the left of the Tables node to view the list of tables. Then right-click the name of the table to create an index on and select Index, then select Create Index.

You can also right-click the Indexes node and select New Index.

In the Properties tab of the Create Index dialog, locate the Table drop-down menu. From this menu, select the table for the index.

For Type:

• To create a non-unique range index, choose Non-unique

• To create a unique range index, choose Unique

• To create a bitmap index, choose Bitmap

For information about the different types of indexes supported in TimesTen, see "CREATE INDEX" in the Oracle TimesTen In-Memory Database SQL Reference.

In the Index section, specify the columns for the index. The columns can be selected from the Column Name or Expression drop-down select list. For each indexed column, from the Order select list, you can specify whether the column is to be sorted in ascending or descending order. The default sort order is ascending.

To create a composite index, click + to add columns to the index definition. Click X to remove columns from the index definition.

In the DDL tab of the Create Index dialog, you can view the CREATE INDEX statement used to create the index.

Click OK to create the index.

You can create a primary key constraint on a table that does not have a primary key defined. Follow these steps:

1. Click the + to the left of the Tables node to view the list of tables. Then right-click the name of the table to create a primary key constraint.

2. Select Constraint, then select Add Primary Key.

   The Add primary key dialog appears.

3. In the key index type field choose either Use range to create a primary key using a range index or Use hash to create a primary key using a hash index.

   If you choose Use hash, you see the text with number of followed by a drop-down list of either row or pages. Select either row or pages and in the box after the colon (:), enter the number of rows or pages. The default selection is row.

   For more information on hash index sizing, see "Column Definition" in the Oracle TimesTen In-Memory Database SQL Reference.

4. In the Constraint Name field, enter the constraint name. The name cannot exceed 30 characters in length.

5. In the Column field(s), you see a drop-down list of column names. Choose the column name(s) to use for the primary key.

6. Click Apply.

   The Confirm dialog box appears indicating that the primary key constraint has been added.

7. Click OK.

   The primary key constraint has been added successfully.

To view the SQL statement used to create the primary key constraint, choose the SQL tab of the Add primary key dialog box.

For information about creating a primary key constraint, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference or "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

You can change the attributes of a primary key constraint after you have defined a primary key on a table. Follow these steps:

1. Click the + to the left of the Tables node to view the list of tables. Then right-click the name of the table on which to change the primary key attributes.

2. Select Constraint, then select Change Primary Key Parameters.

   The Change parameters of a primary key dialog appears.

3. In the key index type field choose either Use range to change the primary key constraint from using a hash index to using a range index or Use hash to change the primary key constraint from using a range index to using a hash index.

   If you choose Use hash, you see the text with number of followed by a drop-down list of either row or pages. Select either row or pages and in the box after the colon (:), enter the number of rows or pages. The default selection is row.

   For more information on hash index sizing, see "Column Definition" in Oracle TimesTen In-Memory Database SQL Reference.

4. Click Apply.

   The Confirm dialog appears indicating that the index(es) for the primary key constraint has been changed.

5. Click OK.

   The index(es) for the primary key constraint has been changed successfully.

To view the SQL statement used to change the attributes of the primary key constraint, choose the SQL tab of the Change parameters of a primary key dialog.

For information about changing the attributes of a primary key constraint, see "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

You can create a foreign key constraint on a table. Make sure you are on the main SQL Developer page and that your TimesTen connection is expanded.

1. Click the + to the left of the Tables node to expand the list of tables.

2. Right-click the name of the table and select Constraint, then select Add Foreign Key.

   The Add Foreign Key dialog displays. The Owner and Name fields are auto-filled and you cannot edit these fields. Prepare to enter the Constraint Name.

3. To enable the ON DELETE CASCADE referential action, select the Cascade delete foreign key checkbox. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.

4. In the Constraint Name field, enter the name of the foreign key.

5. Select the column on which to apply the foreign key constraint from the Column Name drop-down list.

6. Select the table name that the foreign key references from the Referenced Table Name drop-down list.

7. Select the column that the foreign key references from the Referenced Column drop-down list.

   To view the SQL statement that will be used to create the foreign key constraint, choose the SQL tab of the Add Foreign Key dialog box.

8. Click Apply.

   The Confirmation dialog box displays indicating that the foreign key constraint has been added.

9. Click OK.

   The foreign key constraint has been added successfully.

For information about creating a foreign key constraint, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference or "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

You can drop a constraint from a TimesTen table. Make sure you are on the main SQL Developer page and that your TimesTen connection is expanded.

1. Click the + to the left of the Tables node to expand the list of tables.

2. Right-click the name of the table and select Constraint, then select Drop.

   The Drop dialog displays. The Owner and Name fields are auto-filled and you cannot edit these fields. Prepare to select the Constraint.

3. Select the constraint that you want to drop from the Constraint drop-down list.

   To view the SQL statement that will be used to drop the constraint, choose the SQL tab of the Drop dialog box.

4. Click Apply.

   The Confirmation dialog box displays indicating that the constrain has been dropped.

5. Click OK.

   The constraint has been successfully dropped.

To add an aging policy to a regular table or a cache table, under the Tables node, right-click the name of the table to add an aging policy to and select Aging, then select Add Usage-based to add an LRU aging policy. To add a time-based aging policy select Aging, then select Add Time-based. An aging policy can be added to a cache table only if it is the root table of a cache group.

To add an LRU aging policy to the table, in the Prompts tab of the Add Usage-based dialog box, specify an aging state by choosing either On or Off in the Usage-based aging field. The default aging state is on. Click Apply to add the LRU aging policy to the table.

The TimesTen database memory usage thresholds determine when data starts and stops being deleted from the table. The default memory usage threshold that determines when data starts being deleted from the table is 90%. The default memory usage threshold that determines when data stops being deleted from the table is 80%. The default LRU aging cycle is 1 minute. For information about how to change these thresholds and the LRU aging cycle., see Change the Memory Usage Thresholds and LRU Aging Cycle.

An LRU aging policy can be added to any regular table, and only to cache tables in an AWT, SWT or user managed cache group that does not have automatic refresh defined.

To add a time-based aging policy to the table, in the Prompts tab of the Add time-based dialog box, select the column name from Column to store the timestamp value indicating when each row was added or most recently updated.

In the Life-time field, indicate the length of time in which data that has not been updated is to be kept in the table by specifying a numeric value followed by a unit of minutes, hours or days in the drop-down menu.

In the Cycle field, indicate the frequency at which data is to be aged out of the table by specifying a numeric value followed by a unit of minutes, hours or days. The default time-based aging cycle is 5 minutes.

Specify an aging state by choosing either On or Off in the Time-based aging field. The default aging state is on. Then click Apply to add the time-based aging policy to the table.

A time-based aging policy can only be added to a table that contains a non-nullable DATE or TIMESTAMP column.

To drop an existing aging policy from a regular table or a cache table, under the Tables node, right-click the name of the table to drop an aging policy from and then select Aging, then select Drop.

Click Apply to drop the aging policy from the table.

To change the aging state of a regular table's or cache table's existing aging policy, under the Tables node, right-click the name of the table to change the aging state of and then select Aging, then select Change State On/Off.

In the Prompts tab of the Change state on/off dialog, change the aging state by selecting either On or Off in the Change aging state field. Click Apply to change the aging state of the table.

To change the memory usage thresholds and the LRU aging cycle, right-click the node of the connection name for the TimesTen database and choose Change Usage-based Aging Attributes.

In the Prompts tab of the Changing the usage-based thresholds dialog box, specify the threshold that determines when data starts being deleted from the tables in the High usage threshold field. Specify the threshold that determines when data stops being deleted from the tables in the Low usage threshold field. Specify the LRU aging cycle in the Update Frequency field. Then click Apply to change the memory usage thresholds and the LRU aging cycle.

The new settings apply to all tables that have an LRU aging policy defined.

To change the lifetime and cycle of a regular table's or cache table's existing time-based aging policy, under the Tables node, right-click the name of the table to change the lifetime and cycle of and select Aging, then select Change Lifetime and Cycle.

In the Life-time field within the Prompts tab of the Change Lifetime and Cycle dialog box, change the length of time in which data that has not been updated is to be kept in the table by specifying a numeric value followed by a unit of minutes, hours or days.

In the Cycle field, change the frequency at which data is to be aged out of the table by specifying a numeric value followed by a unit of minutes, hours or days.

Then click Apply to change the lifetime and cycle for the table.

You can grant privileges to and revoke privileges from the following database objects:

• Regular and cache tables

• Regular and materialized views

• Sequences

• PL/SQL packages, procedures and functions

To grant privileges on an object, right-click the name of the object and select Privileges, then select Grant.

In the Users drop-down menu of the Grant dialog box, select the user to grant object privileges to the object. In the Privileges section, select the All check box or click >> to grant all available object privileges on the object to the selected user. Otherwise, select the individual privileges from the Available Privileges list for the privileges to grant to the selected user and then click > to move those privileges into the Selected Privileges list. To select multiple privileges, press and hold the CTRL key, and click the desired privileges. Click Apply to grant the selected object privileges on the object to the selected user.

For a particular object type, only the available object privileges are shown. For example, DELETE, INDEX, INSERT, REFERENCES, SELECT and UPDATE privileges can be granted on a table. However, only INDEX, REFERENCES and SELECT privileges can be granted on a materialized view.

Only users with the ADMIN system privilege or the owner of an object can grant object privileges on the object.

For more information about the set of privileges available to each type of object, see "Object privileges" in the Oracle TimesTen In-Memory Database SQL Reference.

To revoke privileges from an object, right-click the name of the object and select Privileges, then select Revoke.

In the Users drop-down menu of the Revoke dialog box, select the user to revoke object privileges from the object. In the Privileges section, select the All check box or click >> to revoke all granted object privileges on the object from the selected user. Otherwise, select the individual privileges from the Available Privileges list for the privileges to revoke from the selected user and then click > to move those privileges into the Selected Privileges list. To select multiple privileges, press and hold the CTRL key, and click the desired privileges. Click Apply to revoke the selected object privileges on the object from the selected user.

The Users drop-down menu shows only users who have been granted object privileges on the object. For a particular user, only the object privileges that they have been granted are shown.

Only users with the ADMIN system privilege or the owner of an object can revoke object privileges from the object.

You can load data into your table using Import Data. Before you can load your data, you must export the data into a file. To export the data, right-click on the name of the table and choose Export. After successfully exporting your data, you can import the data.

1. Choose + to the left of the Tables node to view the list of tables.

2. Right-click on the table name.

3. Select Table, then select Import Data.

   An Open dialog appears. Locate the data file to import. This file is the file you previously exported.

4. Choose Open.

   The data is loaded into the table.

The replication agent process transmits committed updates on replicated tables in a master TimesTen database to the corresponding replicated tables in a subscriber TimesTen database. The replication agent is also responsible for asynchronously propagating committed updates on cache tables in AWT cache groups to the corresponding Oracle tables. After you create a replication scheme or an AWT cache group, you must start the replication agent if it is not running.

To start the replication agent, right-click the node of the connection name for the TimesTen database and select Start/Stop Replication Agent.

The Starting/stopping the replication agent dialog appears. In the Prompts tab, select Start agent. Click Apply.

If there are no replication schemes or AWT cache groups in your TimesTen connection, the replication agent is not started.

To stop a running replication agent process, right-click the node of the connection name for the TimesTen database and select Start/Stop Replication Agent.

The Starting/stopping the replication agent dialog appears. In the Prompts tab, select Stop agent. Click Apply.

The query optimizer uses statistics stored in TimesTen database system tables to determine the optimal execution plan for a statement. TimesTen stores table level statistics in the SYS.TBL_STATS table. Column level statistics, such as the minimum and maximum value and the number of unique values in a column, are stored in the SYS.COL_STATS table.

TimesTen does not compute table and column statistics as updates occur on regular and cache tables. Instead, TimesTen updates statistics when an explicit request is made.

You can update statistics for all tables owned by a user or for a specific table owned by an user.

To update statistics for all tables, right-click the Tables node and select Statistics, then select Update.

To update statistics for a specific table, click the + to the left of the Tables node. Right-click the name of the table and select Statistics, then select Update.

In the Prompts tab of the Update statistics dialog, choose Invalidate referenced commands to invalidate the execution plans of statements that reference the table for which statistics are updated. When you invalidate the execution plan of a statement, TimesTen recompiles or reprepares that statement upon its next execution.

For Interval type for table statistics, choose Complete interval to divide the rows of the table into two or more intervals and compute statistics on each interval, or Single interval to compute statistics on the entire set of rows as a single interval. You must define a range index on the table to compute complete interval statistics.

Click Apply.

To view the optimizer statistics of a table, click the name of the table.

In the Statistics tab of the table pane, the top section shows the table statistics such as:

• The number of rows in the table

• A timestamp indicating when statistics were most recently updated for the table

The bottom section shows the column statistics. For each column, the following information is displayed:

• The name of the column

• The number of intervals the data is divided into to compute statistics

• Total number of NULL values

• Total number of non-NULL unique values

• Total number of rows in each interval

For each interval of each column, SQL Developer displays the following:

• The number of unique values other than the most frequently occurring value

• The number of rows that contain a value other than the most frequently occurring value

• The number of rows that contain the most frequently occurring value

• The minimum value

• The maximum value

• The most frequently occurring value

For more information about query optimizer statistics, see "Statistics" in the Oracle In-Memory Database Cache Introduction, "When optimization occurs" in the Oracle TimesTen In-Memory Database Operations Guide or "ttOptUpdateStats" in the Oracle TimesTen In-Memory Database Reference.

You can generate a set of SQL statements that allow you to restore the table statistics to the current state. You can generate SQL statements for a single table or for user tables that the connection user has permission to access. After you export your table statistics to a .sql file, you can import your table statistics by opening the .sql file in SQL Developer and running the script file.

To export the current TimesTen table statistics, make sure you are on the main SQL Developer page.

1. To save table statistics for a single table, right-click the name of the table and select Statistics, then select Export. To save table statistics for all user tables, right-click Tables and select Statistics, then select Export.

   The Export Optimizer Statistics dialog displays. Locate the Save button at the bottom right of the dialog.

2. Click Save.

   The Save Optimizer Statistics Export Commands dialog displays.

3. Select the directory where you want to save the current TimesTen table statistics.

4. In the File Name field, define the name of your current TimesTen table statistics.

   The default filename of the current TimesTen table statistics is connection_name-exportstatistics-YYYYMMDDMISS.sql, where connection_name is the name of the connection. YYYYMMDDHHMISS is a timestamp of when the recommendations were made, where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, and SS the seconds.

5. Click Save.

   TimesTen saves your current TimesTen table statistics.

A snapshot is a collection of performance metrics from a database. You can create a report comparing two snapshots to analyze the performance of your database.

To create a new snapshot of a database, make sure you are on the main SQL Developer page and that you have expanded the TimesTen connection in the connections navigator.

1. Right-click the name of the database and select TimesTen Performance Metrics, then select Create Snapshot.

   The New TimesTen Metrics Snapshot dialog displays.

2. In the Prompts tab of the New TimesTen Metrics Snapshot dialog, select the desired capture level from the Capture Level drop-down list:

   • TYPICAL: For a typical set of metrics. This level is appropriate for most purposes. This is the default capture level.

   • NONE: For metrics outside of SYS.SYSTEMSTATS only.

   • BASIC: For a minimal basic set of metrics.

   • ALL: For all available metrics.

   Make sure to use the same capture level for any two snapshots to be used in a report. An error dialog displays if you attempt to generate a report that compares two snapshots with different capture levels.

   You are now ready to create a new snapshot.

3. Click Apply to create the snapshot.

   A confirmation dialog shows the ID number of the snapshot. TimesTen generates the snapshot ID.

4. Click OK.

   TimesTen creates the snapshot with the specified snapshot ID. The snapshot ID is useful for creating TimesTen metrics reports.

Consider dropping unneeded snapshots to free up permanent space. If you need to save more snapshots of your database, consider editing the TimesTen performance metrics configuration. See "Editing the TimesTen performance metrics configuration" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.

To drop a new snapshot from a database, make sure you are on the main SQL Developer page.

1. Right-click the name of the database and select TimesTen Performance Metrics, then select Drop Snapshot.

2. By default, the Prompts tab of the Drop TimesTen Metrics Snapshot(s) dialog is selected. In the Selector column, select the snapshots you want to drop.

   You can also click on the column header to sort the table based on the column. A description of each column follows:

   • Selector

     The check box you select to determine the snapshots that you want to drop.

   • Snapshot

     The snapshot ID of the snapshot.

   • Timestamp

     The timestamp of when the snapshot was captured.

   • Capture Level

     The capture level of the snapshot. This value can be TYPICAL, NONE, BASIC, or ALL.

   To select all snapshots, click the Select all check box.

3. Once you have selected the snapshots that you want to drop, click the Apply button.

   A Confirm dialog displays asking you if you want to proceed with dropping the selected snapshots.

4. Click Yes.

   A confirmation dialog displays a message indicating that one or more TimesTen Metrics snapshots were dropped.

5. Click OK.

   You successfully dropped the TimesTen metrics snapshots.

TimesTen performance metrics configuration allows you to modify the maximum snapshot count and the maximum snapshot retention size. These configuration settings are useful both in preventing your database from running out of permanent space and in allowing you to save more snapshots.

Once the maximum snapshot count or the maximum snapshot retention size is reached, TimesTen removes the oldest snapshots to free up permanent space. By default, the maximum snapshot count is 256 and the maximum snapshot retention size is 256 MB.

To edit the TimesTen performance metrics configuration for a database, make sure you are on the main SQL Developer page.

1. Right-click the name of the database and select TimesTen Performance Metrics, then select Edit Configuration.

   The TimesTen Metrics Configuration dialog displays and you see the current configuration for the TimesTen metrics.

2. The Prompts tab of the TimesTen Metrics Configuration dialog is selected. In the Maximum Snapshot Count field, specify a numeric value to indicate the maximum number of snapshots. By default, the maximum number of snapshots is 256.

3. In the Maximum Snapshot Retention Size (MB) field, specify the maximum total size of all stored snapshots, in megabytes. By default, the maximum snapshot retention size is 256 MB.

4. Click Apply.

   You have applied the new TimesTen Metrics configuration settings.

TimesTen performance metrics reports use the data from two specified snapshots of TimesTen metrics to produce a report in HTML format. TimesTen metrics reports include information for each metric. For more information about the various metrics displayed in the report, see "Report examples" in the Oracle TimesTen In-Memory Database Reference.

To generate a new report for a database, make sure you are on the main SQL Developer page.

1. Right-click the name of the database and select TimesTen Performance Metrics, then select Report.

   The Create TimesTen Metrics Report dialog displays.

2. By default, the Report generator tab of the Create TimesTen Metrics Report dialog is selected. In the Selector column, select two snapshots to compare.

   You can also click on the column header to sort the table based on the column. A description of each column follows:

   • Selector

     The check box that determines the snapshots that are compared in the metrics report. Make sure to only select two snapshots with the same capture level. An error dialog displays if you attempt to generate a report that compares more than two snapshots.

   • Snapshot

     The snapshot ID of the snapshot.

   • Timestamp

     The timestamp of when the snapshot was captured.

   • Capture Level

     The capture level of the snapshot. This value can be TYPICAL, NONE, BASIC, or ALL.

3. Once you have selected two snapshots with the same capture level, click the Generate report button. An error dialog displays if you attempt to generate a report that compares snapshots with different capture levels.

   TimesTen generates your TimesTen metrics report. You can view the generated TimesTen metrics report in the pane located below the Generate report button.

   If you do not want to save the report, click Close. If you want to save the report, see Save a report.

Before attempting to capture data for the TimesTen Index Advisor, make sure your table statistics have been updated in the past 24 hours. Up-to-date table statistics provide the most up-to-date statistics for data collection and allow statements to be prepared with the optimized query plan. For more information about updating table statistics, see "Updating the table and column statistics used by the query optimizer" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.

You have the following options:

• Capture data from a SQL worksheet

• Capture data from a SQL workload script

Before attempting to capture data for the TimesTen Index Advisor, make sure your table statistics have been updated in the past 24 hours. Up-to-date table statistics provide the most up-to-date statistics for data collection and allow statements to be prepared with the optimized query plan. For more information about updating table statistics, see "Updating the table and column statistics used by the query optimizer" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.

To collect data for the TimesTen index advisor at the database level, make sure you are on the main SQL Developer page and that your TimesTen connection is expanded.

1. Right-click the name of the database and select Index Advisor, then select Database Level Capture.

   • If your table statistics are outdated, an information dialog displays information about the outdated tables. It is recommended that your table statistics are up to date.

   • If your table statistics are up to date, the Index Advisor - Database Level Capture dialog displays.

2. To update your table statistics, click Cancel and follow the steps in "Updating the table and column statistics used by the query optimizer" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide. If you want to proceed without updating your table statistics, click OK.

   The Index Advisor - Database Capture dialog displays. Locate the Start button at the bottom of the dialog.

3. In the Control tab of the Index Advisor - Connect Level Capture dialog, click Start.

   The Index Advisor Database Capture In Progress dialog displays. Locate the Details >> button.

4. Click Details >>.

   A details pane expands that shows progress information about the index advisor database level capture. Locate the Close button at the bottom of the dialog.

5. Click Close.

   The Index Advisor Database Capture In Progress dialog closes.

6. Once you have captured your desired SQL workload, click Stop.

   If the TimesTen index advisor has recommendations, the Index Advisor Database Capture In Progress dialog displays.

   If the TimesTen index advisor does not have any recommendations, the Index Recommendation Feedback dialog displays. You may close this dialog to return to the main SQL developer page.

7. If the TimesTen index advisor has recommendations, click Details >>.

   A details pane expands that shows progress information about the index advisor database capture. Locate the Close button at the bottom of the dialog.

8. Click Close.

   The Index Advisor Database Capture In Progress dialog closes. The Index Advisor Database Capture dialog shows information about the completed database capture. Locate the Index Recommendations tab at the top of the dialog.

9. Click the Index Recommendations tab.

   The Index Recommendations tab of the Index Advisor - Database Capture dialog displays.

10. In the Selector column, select the index recommendations that you want to create.

    You can also click on the column header to sort the table based on the column. A description of each column follows:

    • Selector

      The check box you select to determine the index recommendations that you want to create.

    • Index Recommendation

      The CREATE INDEX statement that the TimesTen index advisor recommends.

    • Affected Statement Count

      The number of statements that benefit from the recommendation.

    • Created

      Shows if you have already created the index recommendation. This value can be Yes or No.

    If you want to select all index recommendations, click the Select all check box.

11. Once you have selected the index recommendations that you want to apply, click the Create Selected Indexes button.

    The Creating Selected Indexes dialog displays. Locate the Details >> button.

12. Click Details >>.

    A details pane expands that shows progress information about creating the selected indexes. Locate the Close button at the bottom of the dialog.

13. Click Close.

    The Creating Selected Indexes dialog closes. The indexes are created. If you want to save the index recommendations, see "Saving index recommendations" in the Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.

The TimesTen index advisor capture state allows you to view the status of connection and database level captures. To view the TimesTen index advisor capture state for a database, make sure you are on the main SQL Developer page.

1. Right-click the name of the database and select Index Advisor, then select Index Advisor Capture State.

   The Index Advisor Capture State dialog displays and you see the current status of connection and database level captures.

   You can also click on the column header to sort the table based on the column. A description of each column follows:

   • Connection ID

     The connection ID of the connection that initiated the TimesTen index advisor capture.

   • Capture Level

     The capture level of the TimesTen index advisor capture. This value can be Connection or Database.

   • Capture Mode

     The capture mode of the TimesTen index advisor capture. This value can be Execute SQL or Prepare SQL.

   • Capture State

     The capture state of the TimesTen index advisor capture. This value can be In Progress or Completed.

   • Prepared Count

     The number of prepared SQL statements during the capture period.

   • Execute Count

     The number of executed SQL statements during the capture period.

   • Start Time

     The timestamp of when the index advisor capture began. The timestamp format is YYYY-MM-DD HH:MI:SS.FF, where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, SS the seconds, and FF the milliseconds.

   • End Time

     The timestamp of when the index advisor capture ended. The timestamp format is YYYY-MM-DD HH:MI:SS.FF, where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, SS the seconds, and FF the milliseconds. This value is empty if the Capture State is in In Progress.

2. Click Refresh to update the table with the most current information of the TimesTen index advisor captures.

   The Index Advisor Capture State dialog is refreshed with the most recent capture state information.

SQL Developer allows you to load data from an Oracle database into a TimesTen database without creating a cache grid, cache group, or cache table.

Before attempting to load data from an Oracle database into a TimesTen database:

• Make sure you are using Oracle TimesTen In-Memory Database Release 11.2.2.4 (or later).

• Make sure you have specified the Oracle connection information (Oracle Database Password and Oracle Net Service Name) for your TimesTen SQL developer connection.

To load data from an Oracle database into a TimesTen database, make sure you are on the main SQL Developer page and that your TimesTen connection is expanded.

1. Right-click the Tables option and select Load from Oracle Database Tables.

   The Load from Oracle Database Tables dialog displays. Locate your Oracle database schema name from the navigation tree on the left of the Load from Oracle Database Tables dialog.

2. Click the + located next to your Oracle database schema name to expand the Oracle database tables list.

   Your Oracle database schema expands showing a list of available Oracle database tables. Locate the Oracle database tables you want to load into the TimesTen database.

3. Right-click the name of the Oracle database table that you want to load into the TimesTen database and select Load. You can repeat this step, including for the same Oracle database table, to load multiple Oracle database tables.

   If you want to load all of the Oracle database tables, right-click the Oracle database schema name and select Load All.The TimesTen Username dialog displays. Enter the TimesTen user for which you want to create the tables and click OK.

   Your selected tables are added to the right pane and are displayed in a table format.

4. Review the selected Oracle database tables and edit any necessary fields. You can edit a field by double clicking it. A description of each column follows:

   • TimesTen Username

     The owner of the TimesTen table that you are creating. By default, this is the TimesTen connection user. You can edit this column. An error message is displayed if the specified table owner does not exist or the TimesTen connection user does not have privileges to access the specified table owner.

   • TimesTen Table Name

     The name of the TimesTen table that you are creating. By default, this is the same table name as the Oracle database table. You can edit this column.

   • Query to Run on the Oracle database

     The SQL query executed on the Oracle database to generate the desired result set. By default, this is a SELECT * FROM oracledb_tbl query, where oracledb_tbl is the Oracle database table from which you are loading data. You can edit this column.

   • Row Count

     The number of rows that TimesTen loads from the Oracle database table. This is the row count of the result set from the "Query to Run on the Oracle database" field. By default, this value is not counted.

     To populate the Row Count field for a specific Oracle database table, right-click on the Row Count field for your table, then select Count.

     To populate the Row Count fields for all the selected Oracle database tables, right-click on a Row Count field of any table, then select Count All. A confirmation dialog displays. Click Yes to continue with the row count of all of your previously selected Oracle database tables.

   • Create Table Statement

     The SQL statement that TimesTen uses to create the table to load your Oracle database data. You cannot edit this column. Review the CREATE TABLE statement by double clicking on the Create Table Statement field.

     If a TimesTen table with the same name already exists in the TimesTen database, the data from the Oracle database table is appended to the existing TimesTen table. TimesTen does not load table constraints from the Oracle database table. An error dialog displays if a TimesTen table with the same name but different table structure already exists in the TimesTen database.

   • Number of Parallel Loads

     The number of parallel loads that will be used to load the Oracle database table into the TimesTen database. If the table contains less than 500, 000 rows the default value is one. If the Oracle database table contains more than 500, 000 rows the default value is two. You can edit this column.

     TimesTen recommends the use of multiple parallel loads when you are loading a large number of rows. Multiple parallel loads speeds up the process of loading rows by having each parallel load import a subset of rows from the Oracle database table into the TimesTen database.

   • Number of Threads per Parallel Load

     The number of parallel threads that will be used to load the Oracle database table into the TimesTen database. By default, the value is four. You can edit this column. An error dialog displays if you attempt to use a number of parallel threads that is less than two.

   • Status

     The status of the data loading operation from the Oracle database to the TimesTen database. This value is empty before starting the load operation. Once you start the load tables from the Oracle database operation, this value can be IN PROGRESS, DONE, or FAILED.

   Locate the Index Creation Policy at the bottom of the Load from Oracle Database Tables dialog.

5. Select the desired index creation policy from the Index Creation Policy drop-down list:

   • Copy all indexes from peer Oracle database table: TimesTen creates all of the indexes that exist on the peer Oracle database table. This is the default index creation policy.

   • Copy unique indexes only from peer Oracle database table: TimesTen creates all of the unique indexes that exist on the peer Oracle database table.

   • Do not copy indexes from peer Oracle database table: TimesTen does not creates any indexes.

   Locate the Load button in the bottom right of the Load from Oracle Database Tables dialog.

6. Once you edit all of your desired fields, click the Load button.

   A progress dialog displays. Verify that each field of the Status column of the Load from Oracle Database Tables dialog is marked as DONE.

   If any Status field is marked as something other than DONE, make sure that all the data you entered is correct. Any status that is not marked as DONE indicates that an error occurred.

   If all of the Status fields are marked as DONE, your TimesTen tables are created and populated with the Oracle database table data. Double click the DONE status to see more information regarding the imported indexes.

   Locate the Close button in the bottom right of the Load from Oracle Database Tables dialog.

7. Click Close.

   You have successfully loaded data from the Oracle database to the TimesTen database.

To load data from an Oracle database table into an existing TimesTen table, follow these steps. Make sure you are on the main SQL Developer page and that your TimesTen connection is expanded.

1. Click the + located next to Tables to expand the TimesTen tables list.

   The TimesTen tables list expands.

2. Right-click the name of the table and select the Table option, then select the Load from Oracle option.

   The Load Tables from Oracle dialog displays. The Schema name, TimesTen Username, and TimesTen Table Name are auto-filled and you cannot edit these fields. Prepare to enter the SQL query and the Parallel Thread Count.

3. In the Select Query field, enter the SQL query to execute on the Oracle database. The result of the SQL query is loaded into your TimesTen table.

4. In the Parallel Thread Count field, enter the number of parallel threads that will be used to load the Oracle database table into the TimesTen database. An error dialog displays if you attempt to use a number of parallel threads that is less than two.

5. Click Load.

   A progress dialog displays. Once TimesTen loads the data from the Oracle database table, the progress dialog closes. If you execute the query on a small Oracle database table, the progress dialog may close almost immediately.