5 Loading data from an Oracle database into a TimesTen database

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

Note:

SQL Developer uses functions from the ttLoadFromOracle built-in procedure to load data from an Oracle database into a TimesTen database. For more information about the ttLoadFromOracle utility, see "ttLoadFromOracle" in the Oracle TimesTen In-Memory Database Reference.

This chapter provides information about loading Oracle database data into a TimesTen table.

Topics include:

Loading data from Oracle database tables

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. For more information on defining your TimesTen database connection, see "Defining a TimesTen database named 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.

    Figure 5-1 Loading data from Oracle database tables

    Description of Figure 5-1 follows
    Description of "Figure 5-1 Loading data 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.

    To add another Oracle database schema, see "Adding an Oracle database schema". To remove an Oracle database schema, see "Removing an Oracle database schema". To load custom query results for an Oracle database schema, see "Loading custom query results".

    Figure 5-2 Expand the Oracle database schema name

    Description of Figure 5-2 follows
    Description of "Figure 5-2 Expand the Oracle database schema name"

    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.

    To apply a filter to the Oracle database schema tables, see "Applying a filter to Oracle database schema tables".

    Figure 5-3 Load an Oracle database table

    Description of Figure 5-3 follows
    Description of "Figure 5-3 Load an Oracle database table"

    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. For more information on valid Oracle Database SQL queries, see Oracle Database SQL Language Reference.

    • 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 per parallel load 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. One parallel thread performs the bulk fetch from the Oracle database and the remaining parallel threads perform the insert operations into the TimesTen database.

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

    Figure 5-4 Select the index creation policy

    Description of Figure 5-4 follows
    Description of "Figure 5-4 Select the index creation policy"

    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.

Adding an Oracle database schema

To add tables from another Oracle database schema, follow these steps. Before following these steps, make sure you have completed the steps up to step 1 from "Loading data from Oracle database tables".

  1. Click the + located above the Oracle database schema navigation tree.

    Figure 5-7 Add an Oracle database schema

    Description of Figure 5-7 follows
    Description of "Figure 5-7 Add an Oracle database schema "

    The Choose Schema dialog displays. Only Oracle database schemas that the connection user has access to display.

  2. Select the Oracle database schema from the Schema name drop-down list.

    Figure 5-8 Choose an Oracle database schema

    Description of Figure 5-8 follows
    Description of "Figure 5-8 Choose an Oracle database schema"

    You are now ready to add the Oracle database schema.

  3. Click Ok.

    You have successfully added the Oracle database schema. To load data from the tables of the Oracle database schema, see step 2 from "Loading data from Oracle database tables".

Removing an Oracle database schema

To remove an Oracle database schema, follow these steps. This only removes Oracle database schemas from being available for the Load from Oracle database feature. You cannot remove the Oracle database schema of the connection user. Before following these steps, make sure you have completed the steps up to step 1 from "Loading data from Oracle database tables".

  1. Select the Oracle database schema that you want to remove in the schema navigation tree.

  2. Click the - located above the Oracle database schema navigation tree.

    Figure 5-10 Remove the Oracle database schema

    Description of Figure 5-10 follows
    Description of "Figure 5-10 Remove the Oracle database schema"

    You have successfully removed the Oracle database schema.

Applying a filter to Oracle database schema tables

To apply a filter to the Oracle database schema tables, follow these steps. Before following these steps, make sure you have completed the steps up to step 1 from "Loading data from Oracle database tables".

  1. Right-click the name of the Oracle database schema that you want to apply a filter to and select Apply Filter.

    The Filter dialog displays.

  2. Select the filter condition from the Table Name drop-down list:

    • =: Returns results that are equal to a specified pattern.

    • <>: Returns results that are not equal to a specified pattern.

    • LIKE: Returns results that match a specified pattern.

    • NOT LIKE: Returns results that do not match a specified pattern.

    Figure 5-12 Filter condition

    Description of Figure 5-12 follows
    Description of "Figure 5-12 Filter condition"

    Locate the text field located next to the Table Name drop-down list.

  3. Specify a pattern for the filter condition in the text field located next to the Table Name drop-down list. You can use a wildcard for your filter:

    • _: A substitute for exactly one character. For example Sm_th shows all tables that contain five characters, begin with Sm, and end with th.

    • %: A substitute for zero or more characters. For example EMP% shows all tables that begin with the letters EMP.

  4. Click OK.

    You have successfully applied a filter to the Oracle database schema tables. To load data from the tables of the Oracle database schema, see step 2 from "Loading data from Oracle database tables".

Loading custom query results

To load custom query results for an Oracle database schema, follow these steps. Before following these steps, make sure you have completed the steps up to step 1 from "Loading data from Oracle database tables".

  1. Right-click the Schemas option and select Load custom query results.

    Figure 5-14 Load custom query results

    Description of Figure 5-14 follows
    Description of "Figure 5-14 Load custom query results"

    The Custom Query dialog displays. You are now required to enter the information for your custom Oracle query. The first property is the Schema name. You cannot edit this property and the default value is the connection user.

  2. In the TimesTen Username text field, enter the owner of the TimesTen table. Make sure that the table owner you specify exists in TimesTen and that the TimesTen connection user has privileges to create a table or insert into an existing table owned by the specified table owner.

  3. In the TimesTen Table Name text field, enter the name of the TimesTen table.

  4. In the Select Query text field, enter the SQL query that you want to execute on the Oracle database to generate the desired result set. For more information on valid Oracle Database SQL queries, see Oracle Database SQL Language Reference.

  5. In the Parallel Thread Count text field, enter the number of parallel threads that will be used to load the Oracle database table into the TimesTen database. TimesTen recommends a value of four.

  6. Click OK.

    Figure 5-15 Load the custom query

    Description of Figure 5-15 follows
    Description of "Figure 5-15 Load the custom query"

    You have successfully loaded a custom Oracle database query. Your custom query is loaded into the right pane of the Load Tables from Oracle dialog. For more information on how to load Oracle database data from your custom query into a TimesTen database, see step 4 from "Loading data from Oracle database tables".

Loading data from an Oracle database table into an existing TimesTen table

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.

    Figure 5-16 Expand Tables

    Description of Figure 5-16 follows
    Description of "Figure 5-16 Expand Tables"

    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.

    Figure 5-17 Load data from an Oracle database table

    Description of Figure 5-17 follows
    Description of "Figure 5-17 Load data from an Oracle database table"

    The Load Tables from Oracle dialog displays. The Schema name, TimesTen Username, and TimesTen Table Name fields 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. For more information on valid Oracle Database SQL queries, see Oracle Database SQL Language Reference.

  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.

    Figure 5-18 Load Tables from Oracle

    Description of Figure 5-18 follows
    Description of "Figure 5-18 Load Tables from Oracle"

    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.