TimesTen如何缓存两个用户的Schema

如果Oracle数据库中有两张表，分别属于不同的Schema, TimesTen是否可以缓存?
答案是可以的，来看一个例子，顺便了解一下概念；

首先在Oracle数据库中建立两个用户user1和user2，并且分布在两个用户下建表t1和t2:

SQL> create user user1 identified by oracle;
User created.
SQL> create user user2 identified by oracle;
User created.
SQL> grant create session, create table to user1;
Grant succeeded.
SQL> grant create session, create table to user2;
Grant succeeded.
SQL> ALTER USER user1 quota unlimited on USERS;
User altered.
SQL> ALTER USER user2 quota unlimited on USERS;

User altered.

SQL> connect user1/oracle
Connected.
SQL> create table t1(k int, v int, primary key(k));
Table created.
SQL> insert into t1 values(1,1);
1 row created.
SQL> grant select on t1 to cacheadm;
Grant succeeded.

SQL> connect user2/oracle
Connected.
SQL> create table t2(k int, v int, primary key(k));
Table created.
SQL> insert into t2 values(2,1);
1 row created.
SQL>  grant select on t2  to cacheadm;
Grant succeeded.

在TimesTen数据库端，也分别建立用户user1和user2, 使用cacheadm用户建立两个缓存组：

Command> create user user1 identified by timesten;
User created.
Command> create user user2 identified by timesten;
User created.
Command> grant create session to user1;
Command> 
Command> grant create session to user2;


Command> cachegroups;

Cache Group CACHEADM.USER1_CG:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined

  Root Table: USER1.T1
  Table Type: Read Only

Cache Group CACHEADM.USER2_CG:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined

  Root Table: USER2.T2
  Table Type: Read Only

接下来，就可以在TimesTen中使用user1或user2访问数据了：

[timesten@ttlab ~]$ ttisql "dsn=sampledb_1122;uid=user1"

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



connect "dsn=sampledb_1122;uid=user1";
Enter password for 'user1': 
Connection successful: DSN=sampledb_1122;UID=user1;DataStore=/home/timesten/TimesTen/tt1122/info/DemoDataStore/sampledb_1122;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/home/timesten/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=orcl;
(Default setting AutoCommit=1)
Command> select * from t1;
< 1, 1 >
1 row found.
Command> select * from user2.t2;
15100: User USER1 lacks privilege SELECT on USER2.T2
The command failed.

总结以上的测试，如下图：

也就是说，Oracle中表的属主用户，在TimesTen中也必须有此用户。这时最关键的一点。

因此也建议使用独立的缓存管理员，而非直接赋予user1或user2 CACHE_MANAGER权限。剩下的问题就靠权限解决了，你也可以基于这两个表建立视图。

还有一个重要概念，一个TimesTen只能缓存一个Oracle数据库，多个是不行的！