Timesten 快捷加载oracle数据库中的表和数据

在Timesten 11.2.2 版本中新增了一个非常方便的数据导入工具 ttTableSchemaFromOraQueryGet  和  ttLoadFromOracle 。

可以不用使用cache group 就直接将表结构和数据快捷的加载至内存库中。不过该方法不能实现数据的自动刷新，要想保持数据的一致，必须手动去执行刷新命令。

用法简介：

1、配置tnsnames.ora 文件，添加一个连接串。

      配置好之后，可以测试一下联通性，确保没有配置错误。

2、使用Timesten 命令进行连接

ttisql "DSN=tt_1122;uid=scott;pwd=triger;OraclePwd=triger;OracleNetServiceName=orcl"

红色的是TT用户名，但是必须保证oracle 中也存在的同样名称的用户。黄色的是tt中scott用户的密码，绿色的是oracle中scott 的密码，orcl 是tnsnames.ora 文件中所配置的连接串名称。

3、生成表结构

call ttTableSchemaFromOraQueryGet('scott','emp','SELECT * FROM scott.emp');

该命令会生成一个emp表结构的建表语句，需要手动在TT中执行。

4、导入数据

CALL ttLoadFromOracle ('scott','emp','SELECT * FROM scott.emp');

这样就将查询中符合条件的数据全部加载到了TT中，命令成功之后会返回一个数据，即成功插入的数据总条数。

5、数据刷新

重复执行该命令就好 CALL ttLoadFromOracle ('scott','emp','SELECT * FROM scott.emp');

---------详情可参见官网

http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21633/ttisql.htm#TTOPR752

-------

Use TimesTen built-in procedures to recommend a table and load SQL query results

While the createandloadfromoraquery command automatically performs all of the tasks for creating the TimesTen table and loading the result set from the Oracle database into it, the following two built-in procedures separate the same functionality into the following two steps:

1. The ttTableSchemaFromOraQueryGet built-in procedure evaluates the SQL query and generates the CREATE TABLE SQL statement that you can choose to execute. In order to execute this statement, the user should have all required privileges to execute the query on the Oracle database. This enables you to view the table structure without execution. However, it does require you to execute the recommended CREATE TABLE statement yourself.

2. The ttLoadFromOracle built-in procedure executes the SQL query on the back-end Oracle database and then loads the result set into the TimesTen table. It requires the TimesTen table name where the results are loaded, the Oracle Database SQL SELECT statement to obtain the required rows, and the number of parallel threads that you would like to be used in parallel when loading the table with this result set.

   The call returns a single number indicating the number of rows loaded. Any subsequent calls append the retrieved rows to the table.

Note:

See "ttTableSchemaFromOraQueryGet" and "ttLoadFromOracle" in the Oracle TimesTen In-Memory Database Reference for full details on syntax, requirements, restrictions, and required privileges.

The following example connects providing the DSN, user name, password for the user on TimesTen, the password for a user with the same name on the Oracle database, and the OracleNetServiceName for the Oracle database instance. Then, it calls the ttTableSchemaFromOraQueryGet built-in procedure to evaluate the SELECT statement and return a recommended CREATE TABLE statement for the employees table. Finally, the example calls the ttLoadFromOracle built-in procedure to load the employees table with the result set from the Oracle database. The load is performed in parallel over four threads, which is the default.

Note:

If autocommit is set to off, then the user must either commit or rollback manually after loading the table.

$ ttisql "DSN=mydb;uid=oratt;pwd=timesten;
OraclePwd=oracle;OracleNetServiceName=inst1"
Copyright (c) 1996-2013, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=mydb;uid=oratt;pwd=timesten;
OraclePwd=oracle;OracleNetServiceName=inst1";
Connection successful: DSN=mydb;UID=oratt;
DataStore=/timesten/install/info/DemoDataStore/mydb;
DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;
DRIVER=/timesten/install/lib/libtten.so;PermSize=40;TempSize=32;
TypeMode=0;OracleNetServiceName=inst1;
(Default setting AutoCommit=1)

Command> call ttTableSchemaFromOraQueryGet('hr','employees',
 'SELECT * FROM hr.employees');
< CREATE TABLE "HR"."EMPLOYEES" (
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
 ) >
1 row found.

Command> CALL ttLoadFromOracle ('HR','EMPLOYEES','SELECT * FROM HR.EMPLOYEES');
< 107 >
1 row found.

Command> SELECT * FROM hr.employees;
< 100, Steven, King, SKING, 515.123.4567, 2003-06-17 00:00:00, AD_PRES, 24000,
<NULL>, <NULL>, 90 >
< 101, Neena, Kochhar, NKOCHHAR, 515.123.4568, 2005-09-21 00:00:00, AD_VP, 17000, 
<NULL>, 100, 90 >
...
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 2002-06-07 00:00:00, AC_MGR, 
12008, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 2002-06-07 00:00:00, AC_ACCOUNT, 
8300, <NULL>, 205, 110 >
107 rows found.

The following example creates a table on the Oracle database, where employee_id is a column with a PRIMARY KEY constraints and email is a column with a UNIQUE constraint.

SQL> CREATE TABLE employees    ( employee_id    NUMBER(6) PRIMARY KEY    , first_name     VARCHAR2(20)    , last_name      VARCHAR2(25) NOT NULL    , email          VARCHAR2(25) NOT NULL UNIQUE    , phone_number   VARCHAR2(20)    , hire_date      DATE NOT NULL    , job_id         VARCHAR2(10) NOT NULL    , salary         NUMBER(8,2)    , commission_pct NUMBER(2,2)    , manager_id     NUMBER(6)    , department_id  NUMBER(4)    ) ;

Table created.

Then, the following ttTableSchemaFromOraQueryGet built-in procedure evaluates the SQL query and generates a CREATE TABLE SQL statement. Note that in the suggested CREATE TABLE SQL statement the PRIMARY KEY and UNIQUE constraints are not carried over from the Oracle database. Nullability constraints are carried over from the Oracle database. This also applies to the createandloadfromoraquery command.

Command> call ttTableSchemaFromOraQueryGet ('oratt', 'employees', 'select * from oratt.employees');
< CREATE TABLE "ORATT"."EMPLOYEES" ( 
"EMPLOYEE_ID" number(6,0) NOT NULL,
"FIRST_NAME" varchar2(20 byte),
"LAST_NAME" varchar2(25 byte) NOT NULL,
"EMAIL" varchar2(25 byte) NOT NULL,
"PHONE_NUMBER" varchar2(20 byte),
"HIRE_DATE" date NOT NULL,
"JOB_ID" varchar2(10 byte) NOT NULL,
"SALARY" number(8,2),
"COMMISSION_PCT" number(2,2),
"MANAGER_ID" number(6,0),
"DEPARTMENT_ID" number(4,0)
 ) >
1 row found.