TimesTen如何缓存两个用户的Schema

2017-3-7来源:SQL技巧人气:504

如果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数据库,多个是不行的!