Oracle内存数据库Timesten的使用(1)

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/jaune161/article/details/47255075

Timesten安装

…
开发环境信息
192.168.57.80 /opt/timesten/TimesTen
Daemon pid 12239 port 53396 instance ttgjds
TimesTen server pid 12248 started on port 53397

启动和停止Timesten数据库

启动Timesten

[root@bogon bin]# ttDaemonAdmin -start
TimesTen Daemon startup OK.
[root@bogon bin]# ttDaemonAdmin -force -start

停止Timesten

[root@bogon bin]# ttDaemonAdmin -stop
TimesTen Daemon stopped.

重启Timesten

[timesten@bogon info]$ ttDaemonAdmin -restart
TimesTen Daemon stopped.
TimesTen Daemon startup OK.

查看Timesten的状态

[root@bogon bin]# ttstatus
TimesTen status report as of Thu Jul 30 14:35:00 2015

Daemon pid 13029 port 53396 instance ttgjds
TimesTen server pid 13038 started on port 53397 ------------------------------------------------------------------------
Data store /opt/timesten/TimesTen/ttgjds/info/DemoDataStore/test_1122 There are no connections to the data store Replication policy : Manual Cache Agent policy : Manual PL/SQL enabled. ------------------------------------------------------------------------ Data store /opt/timesten/ttdata/database/gjds There are no connections to the data store Replication policy : Manual Cache Agent policy : Manual PL/SQL enabled. ------------------------------------------------------------------------ Data store /opt/timesten/TimesTen/ttgjds/info/DemoDataStore/sampledb_1122
There are no connections to the data store
Replication policy  : Manual
Cache Agent policy  : Manual
PL/SQL enabled. ------------------------------------------------------------------------
Accessible by group timesten
End of report

创建内存数据库

timesten采用ODBC方式连接数据库，比如创建一个名为cachedb_gjds的数据库

#首先打开配置文件
vi $TIMESTEN_HOME/info/sys.odbc.ini

在[ODBC Data Sources]中添加cachedb_gjds=TimesTen 11.2.2 Client Driver，如下所示

[ODBC Data Sources]
TT_1122=TimesTen 11.2.2 Driver
sampledb_1122=TimesTen 11.2.2 Driver
cachedb1_1122=TimesTen 11.2.2 Driver
repdb1_1122=TimesTen 11.2.2 Driver
repdb2_1122=TimesTen 11.2.2 Driver
sampledbCS_1122=TimesTen 11.2.2 Client Driver
cachedb1CS_1122=TimesTen 11.2.2 Client Driver
repdb1CS_1122=TimesTen 11.2.2 Client Driver
repdb2CS_1122=TimesTen 11.2.2 Client Driver
cachedb_gjds=TimesTen 11.2.2 Client Driver

然后添加下面的内容

#中括号中是DSN名称
[cachedb_gjds]
Driver=/opt/oracle/TimesTen/tt1122/lib/libtten.so
#ds路径cachedb_gjds不能是一个文件夹
DataStore=/opt/oracle/TimesTen/ttdata/gjds/cachedb_gjds
#内存大小
PermSize=40
#临时空间一般是PermSize/4
TempSize=32
#是否允许PLSQL连接
PLSQL=1
#数据库字符集，注意这里要与Oracle一致
DatabaseCharacterSet=ZHS16GBK
#连接的字符集
ConnectionCharacterSet=ZHS16GBK
#Oracle TNS名称
OracleNetServiceName=orcl

Timesten基本使用

1、连接Timesten，Timesten默认是不需要用户名和密码的
使用ttisql DSN_NAME或者ttisql “DSN=DSN_NAME;[attributes…]”

[timesten@bogon info]$ ttisql cachedb_gjds

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=cachedb_gjds";
Connection successful: DSN=cachedb_gjds;UID=timesten;DataStore=/opt/oracle/TimesTen/ttdata/gjds/cachedb_gjds;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/opt/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=ORCL;
(Default setting AutoCommit=1)
Command>

2、创建用户

#Timesten创建用户与Oracle一致
Command> create user gjds identified by gjds;

User created.

Command> grant connect to gjds;
Command> grant create table to gjds;
Command> grant create view to gjds;
Command> grant admin to gjds;
#用户创建完之后使用connect切换用户
Command> connect "DSN=cachedb_gjds;uid=gjds;pwd=gjds"
       > ;
Connection successful: DSN=cachedb_gjds;UID=gjds;DataStore=/opt/oracle/TimesTen/ttdattionCharacterSet=ZHS16GBK;DRIVER=/opt/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=
(Default setting AutoCommit=1)
con1: Command>

3、创建表

Command> CREATE TABLE "TEST"."TIMESTEN_SYNC_TEST" ( 
       > "ID" varchar2(10 byte) NOT NULL,
       > "NAME" varchar2(50 byte),
       > "AGE" number
       >  );
#创建表的语法跟Oracle是一模一样的，Timesten的语法几乎与Oracle一致
Command> tables
  TEST.TIMESTEN_SYNC_TEST
1 table found.
#使用tables可以查看数据库中所有的表信息
Command>INSERT INTO TIMESTEN_SYNC_TEST VALUES('1','1',20);

其他语法与Oracle一致，在此不再赘述。

配置Oracle客户端

Timesten已经安装Oracle客户端，可以不用重复安装，安装目录TIMESTEN_HOME/ttoracle_home/instantclient_11_2
默认的没有TNS_ADMIN环境变量，需要手动添加

[timesten@bogon support]$vi ~/.bash_profile
#添加 export TNS_ADMIN=/opt/oracle/TimesTen/tt1122/ttoracle_home/instantclient_11_2/network/admin

将tnsnames.ora复制到TNS_ADMIN目录
添加如下内容

ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.62)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )

创建一个与Oracle关联的用户

语法同创建用户的语法，用户名要与Oracle数据库的用户名一致。
使用下面的方式登录用户

[timesten@bogon info]$ ttisql "DSN=cachedb_gjds;uid=test;pwd=test;OraclePwd=test"

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=cachedb_gjds;uid=test;pwd=********;OraclePwd=********";
Connection successful: DSN=cachedb_gjds;UID=test;DataStore=/opt/oracle/TimesTen/ttdata/gjds/cachedb_gjds;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/opt/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=ORCL;
(Default setting AutoCommit=1)
Command>

这样登录就与Oracle用户建立了连接

使用工具创建表并导入Oracle数据

#ttTableSchemaFromOraQueryGet(['tblOwner'], 'tblName', 'Query')
#只能生成建表语句，不能创建表，需要手动执行下建表语句
Command> call ttTableSchemaFromOraQueryGet('TEST','TIMESTEN_SYNC_TEST','SELECT * FROM TEST.TIMESTEN_SYNC_TEST');
< CREATE TABLE "TEST"."TIMESTEN_SYNC_TEST" ( 
"ID" varchar2(10 byte) NOT NULL,
"NAME" varchar2(50 byte),
"AGE" number
 ) >
1 row found.

#ttLoadFromOracle(['tblOwner'], 'tblName', 'Query' [,numThreads])
#从Oracle数据库将表的数据导入
Command> CALL ttLoadFromOracle ('TEST','TIMESTEN_SYNC_TEST','SELECT * FROM TEST.TIMESTEN_SYNC_TEST');
< 1 >
1 row found.
Command> select * from TIMESTEN_SYNC_TEST;                                           
< 1, 2, 18 >
1 row found.
Command> tables
  TEST.TIMESTEN_SYNC_TEST
1 table found.

转载于:https://my.oschina.net/jaune161/blog/3015863