1 Connecting to a TimesTen Database

After you have installed and started SQL Developer as stated in "Installing and Starting SQL Developer" in the Oracle Database SQL Developer Installation Guide, you can connect to a TimesTen database after you have defined a data source name (DSN) for the database.

See "Data source names" in the Oracle TimesTen In-Memory Database Operations Guide for information about defining a TimesTen database DSN.

See "Define a DSN for the TimesTen database" in the Oracle In-Memory Database Cache User's Guide or "Defining DSNs for cached tables" in the TimesTen Cache Connect to Oracle Guide for information about defining a DSN for a TimesTen database that will be used to cache data from an Oracle database.

Note:

The component that caches Oracle data in a TimesTen database is documented in the Oracle In-Memory Database Cache User's Guide for TimesTen 11g and the TimesTen Cache Connect to Oracle Guide for TimesTen 7.0.

Set environment variables for TimesTen and SQL Developer

To access a TimesTen database from SQL Developer, install SQL Developer on the same system as the TimesTen Data Manager or TimesTen Client. If SQL Developer will be used to access a local database, SQL Developer must be installed on the same system as the TimesTen Data Manager. If SQL Developer will be used to access a remote database, SQL Developer must be installed on the same system as the TimesTen Client.

Then execute the TimesTen_install_dir/bin/ttenv.sh or TimesTen_install_dir/bin/ttenv.csh shell script on Linux systems, or the TimesTen_install_dir\bin\ttenv.bat batch file on Microsoft Windows systems corresponding to the TimesTen instance that you want to access from SQL Developer. You must execute the shell script or batch file to set the TimesTen environment variables before starting SQL Developer. The shell script or batch file adds or modifies the following environment variables:

  • CLASSPATH includes the TimesTen_install_dir/lib/ttjdbc6.jar file on Linux systems or the TimesTen_install_dir\lib\ttjdbc6.jar file on Microsoft Windows systems. SQL Developer 2.1 requires Java Development Kit (JDK) 6 be installed.

    To determine which third party JDBC drivers are currently being used, select Tools > Preferences. In the Preferences dialog box, click the + to the left of the Database node to expand the node. Select Third Party JDBC Drivers to view the path name of all third party JDBC drivers, including the TimesTen JDBC drivers, that SQL Developer is using.

  • LD_LIBRARY_PATH includes the TimesTen_install_dir/lib directory on Linux systems.

  • PATH includes the TimesTen_install_dir/bin directory on Linux systems, or the TimesTen_install_dir\bin directory on Microsoft Windows systems.

For TimesTen 7.0, ORACLE_HOME must be set to the Oracle client installation directory before starting the TimesTen main daemon process.

Configure the Oracle and TimesTen databases

Before you can cache Oracle data in a TimesTen database, you must perform certain tasks outside of SQL Developer using command-line interfaces such as SQL*Plus and the ttIsql utility, or within SQL Developer using SQL Worksheet. See "Configuring the Oracle database to cache data in TimesTen" and "Configuring a TimesTen database to cache Oracle data" in the Oracle In-Memory Database Cache User's Guide for information regarding the following tasks:

  • Create the timesten user, a schema user and the cache administration user in the Oracle database

  • Grant these Oracle users the privileges required to perform desired cache operations

  • Create the cache manager user and cache table users in the TimesTen database

  • Grant these TimesTen users the privileges required to perform desired cache operations

Note:

These tasks apply only with TimesTen 11g.

With TimesTen 7.0, create a schema user and the cache administration user in the Oracle database. Grant these Oracle users the privileges required to perform desired cache operations. Then, if the TimesTen instance has access control enabled, create an internal user in the TimesTen database whose ID is identical to the Oracle schema user. Grant this TimesTen user the privileges required to perform desired cache operations. See the TimesTen Cache Connect to Oracle Guide for information regarding these tasks.

Define a TimesTen database named connection

To create a named connection to a TimesTen database, right-click the Connections node and select New Connection.

Surrounding text describes newconnection.gif.

In the New/Select Database Connection dialog box, click the TimesTen tab and fill in the fields by specifying:

  • A connection name in the Connection Name field

  • A TimesTen user name in the Username field. If the TimesTen database will be used to cache data from an Oracle database, specify the TimesTen cache manager user name (or the internal TimesTen user whose ID is identical to the Oracle schema user for TimesTen 7.0) in the Username field.

  • The password for the user specified within the Username field in the Password field.

    To save the password in the named connection, put a check in the Save Password check box. The Connection Information dialog box will not appear and prompt you to enter a password on subsequent connections to the TimesTen database using the named connection.

  • If the TimesTen database will be used to cache data from an Oracle database, specify the password of the Oracle user in the Oracle Password (for Cache) field. The Oracle user has the same name as the TimesTen user that was specified in the Username field.

    Otherwise, leave this field blank.

Surrounding text describes defineconnection.gif.

In the DSN pull-down menu, select a predefined DSN that references the TimesTen database that you want to connect to. You can select either a Data Manager DSN to establish a direct connection or a client DSN to establish a client/server connection. If you are going to connect to TimesTen 7.0 and 11g databases from the same SQL Developer session using client/server connections, you must have SQL Developer use the TimesTen 11g JDBC driver (ttjdbc6.jar file) and not the TimesTen 7.0 driver.

If you selected User specified in the DSN pull-down menu, select either the Client/server button to establish a client/server connection or the Direct driver button to establish a direct connection.

See "Configuring TimesTen Client and Server" in the Oracle TimesTen In-Memory Database Operations Guide for information about configuring a client/server connection to a TimesTen database.

In the Connection string field, specify a connection string to identify the DSN and set DSN attributes if you selected User specified in the DSN pull-down menu. Each attribute setting must be separated by a semicolon. Do not delimit the connection string with a pair of double quotation marks.

See "Connecting to a data store using a connection string" in the Oracle TimesTen In-Memory Database Operations Guide for information about the format and contents of a connection string.

Click the Save button to save the connection name under the Connections node so that you can establish a connection to the database using the settings in the named connection without having to specify the values in the fields of the New/Select Database Connection dialog box again.

Click the Connect button to connect to the TimesTen database.

Use an existing TimesTen database named connection

If a named connection for the TimesTen database that you want to connect to already exists under the Connections node, right-click the node that contains the connection name and select Connect, or click the + to the left of the node that contains the connection name.

Surrounding text describes existingconnection.gif.

If the Connection Information dialog box appears, specify a TimesTen user name (the TimesTen cache manager user name if the TimesTen database will be used to cache data from an Oracle database) in the Username field, if this field does not already contain a user name, and the corresponding password for this user in the Password field. Then click the OK button to establish the connection.

Surrounding text describes connectioninformation.gif.

After you connect to a TimesTen database, you can browse the existing elements or create new elements such as tables, indexes, views, sequences, and PL/SQL packages, procedures and functions. See the Oracle Database SQL Developer User's Guide for information on creating these elements.

The rest of this guide focuses on working with TimesTen-specific elements such as cache groups, and features such as aging policies.

Disconnect an existing TimesTen database connection

To disconnect an existing connection from a TimesTen database, under the Connections node right-click the node that contains the name of the database connection that you want to disconnect and select Disconnect.

Surrounding text describes disconnect.gif.

For more information about creating and managing database connections in SQL Developer, see "Database Connections" in the Oracle Database SQL Developer User's Guide.

For information about creating the Oracle and TimesTen users required to cache Oracle data in a TimesTen database, see "Create the Oracle users" and "Create the TimesTen users" in the Oracle In-Memory Database Cache User's Guide, or "Create Oracle users and set privileges" and "Create an account on TimesTen" in the TimesTen Cache Connect to Oracle Guide.