1 测试使用Cache Connect to Oracle
1.1 Setting up TimesTen and Oracle
1.1.1 设置TimesTen的环境变量
[root@server1 ~]# su - tt
[tt@server1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export TimesTen=/oracle/timesten/TimesTen/tt70
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:ORACLE_HOME/network/lib:ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
PATH=$PATH:$HOME/bin:/oracle/timesten/TimesTen/tt70/bin:$ORACLE_HOME/bin
export PATH
unset USERNAME
在这里要注意要设置$ORACLE_HOME,确保timesten用户能连接数据库
[tt@server1 ~]$ sqlplus scott/tiger@orcl1
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 10 07:50:00 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters and Data Mining options
SQL>
1.1.2 创建一个DSN
在这里一定要注意字符集的设置
#####################################################################
# Test Cache Oracle Data
#####################################################################
[CacheOracleData]
Driver=/oracle/timesten/TimesTen/tt70/lib/libtten.so
DataStore=/oracle/timesten/peyton/TempDs
AutoCreate=1
Logging=1
LogPurge=1
PermSize=16
TempSize=16
UID=scott
OracleId=orcl1
OraclePwd=tiger
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
1.1.3 创建一个TimesTen用户
ttIsql CacheOracleData
Command>CREATE USER scott IDENTIFIED BY 'tiger';
Command>GRANT ADMIN, DDL TO scott;
1.2 Creating a READONLY cache group
1.2.1 Create an Oracle table
sqlplus scottt/tiger@orcl1
SQL> CREATE TABLE readtab (a NUMBER(10,0) NOT NULL PRIMARY KEY,b CHAR(31));
SQL> INSERT INTO readtab VALUES (1, 'hello');
SQL> INSERT INTO readtab VALUES (2, 'world');
SQL> COMMIT;
1.2.2 Create a READONLY cache group
[tt@server1 ~]$ ttIsql CacheOracleData
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=CacheOracleData";
Enter password for 'scott':
Connection successful: DSN=CacheOracleData;UID=scott;DataStore=/oracle/timesten/peyton/TempDs;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=US7ASCII;DRIVER=/oracle/timesten/TimesTen/tt70/lib/libtten.so;OracleId=orcl1;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)
Command> call ttCacheUidPwdSet('scott','tiger');
Command> call ttCacheStart();
Command> CREATE READONLY CACHE GROUP readcache AUTOREFRESH INTERVAL 5 SECONDS FROM readtab (a INT NOT NULL PRIMARY KEY, b CHAR(31));
5062: gv$session could not be found on Oracle. Check that scott has SELECT privileges on gv$session
The command failed.
在这里报了一个错,由于我是rac环境,这里单独在SQL里又设置
SQL> grant select any dictionary to scott;
Command> CREATE READONLY CACHE GROUP readcache AUTOREFRESH INTERVAL 5 SECONDS FROM readtab (a INT NOT NULL PRIMARY KEY, b CHAR(31));
1.2.3 Load the cache group
Command> LOAD CACHE GROUP readcache COMMIT EVERY 30 ROWS;
Command> cachegroups;
Cache Group SCOTT.READCACHE:
Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds
Root Table: SCOTT.READTAB
Table Type: Read Only
1 cache group found.
1.2.4 Update the Oracle table
SQL> connect scott/tiger@orcl1
SQL> INSERT INTO readtab VALUES (3, 'Hello');
SQL> INSERT INTO readtab VALUES (4, 'Again');
SQL> commit;
Oracle. Check the contents of the READTAB table in ttIsql:
Command> SELECT * FROM readtab;
< 1, hello >
< 2, world >
< 3, Hello >
< 4, Again >
4 rows found.
Command> insert into readtab values (5,'bb');
8225: Table READTAB is read only
The command failed.
Attempts to update a cached table in a READONLY cache group result in
TimesTen error 8225 “Table is read only”. However, if the PassThrough
attribute is set to either 2 or 3, DML statements may be passed through the cache
to Oracle and then propagated by AUTOREFRESH back into the cache group
from Oracle.
Command> autocommit 0;
Command> set passthrough 2;
Command> delete from readtab where a = 5;
1 row deleted.
Command> commit;
SQL> select * from readtab;
A B
---------- -------------------------------
1 hello
2 world
3 Hello
4 Again
1.2.5 Stop the cache agent
Command> call ttCacheStop();
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/48010/viewspace-1017308/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/48010/viewspace-1017308/