1.准备测试表和数据

SQL> CREATE TABLE customer
  2  (cust_num NUMBER(6) NOT NULL PRIMARY KEY,
  3   name     VARCHAR2(50)
  4  );
Table created.
SQL> CREATE TABLE orders
  2  (ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
  3   cust_num     NUMBER(6) NOT NULL
  4  );
Table created.
SQL> insert into customer values(1,'wwww.xifenfei.com1');
1 row created.
SQL> insert into customer values(2,'wwww.xifenfei.com2');
1 row created.
SQL> insert into customer values(3,'wwww.xifenfei.com3');
1 row created.
SQL> insert into customer values(4,'wwww.xifenfei.com4');
1 row created.
SQL> insert into orders(cust_num,ord_num) values(1,1);
1 row created.
SQL> insert into orders (cust_num,ord_num) values(1,2);
1 row created.
SQL> insert into orders (cust_num,ord_num) values(3,5);
1 row created.
SQL> insert into orders (cust_num,ord_num) values(3,6);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from customer;
  CUST_NUM NAME
---------- --------------------------------------------------
         1 wwww.xifenfei.com1
         2 wwww.xifenfei.com2
         3 wwww.xifenfei.com3
         4 wwww.xifenfei.com4
SQL> select * from orders;
   ORD_NUM   CUST_NUM
---------- ----------
         1          1
         2          1
         5          3
         6          3
SQL> grant select on oratt.customer to cacheuser;
Grant succeeded.
SQL> grant select on oratt.orders to cacheuser;
Grant succeeded.

2.创建cache group

[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command>  drop cache group cacheuser.customer_orders;
Command> CREATE READONLY CACHE GROUP customer_orders
       > AUTOREFRESH INTERVAL 5 SECONDS
       > STATE ON
       > FROM oratt.customer
       >  (cust_num NUMBER(6) NOT NULL,
       >   name     VARCHAR2(50),
       >   PRIMARY KEY(cust_num)),
       > oratt.orders
       >  (ord_num      NUMBER(10) NOT NULL,
       >   cust_num     NUMBER(6) NOT NULL,
       >   PRIMARY KEY(ord_num),
       >   FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));
Command> cachegroups;
Cache Group CACHEUSER.CUSTOMER_ORDERS:
  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: ORATT.CUSTOMER
  Table Type: Read Only
  Child Table: ORATT.ORDERS
  Table Type: Read Only
1 cache groups found.

3.TT中表访问授权

[oracle@xifenfei ~]$ ttisql tt_1122
Command> grant select on oratt.customer to cacheuser;
Command> grant select on oratt.orders to cacheuser;

4.测试数据初始化

[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> select * from oratt.customer;
< 1, wwww.xifenfei.com1 >
< 2, wwww.xifenfei.com2 >
< 3, wwww.xifenfei.com3 >
< 4, wwww.xifenfei.com4 >
4 rows found.
Command> select * from oratt.orders;
< 1, 1 >
< 2, 1 >
< 5, 3 >
< 6, 3 >
4 rows found.

5.ORACLE修改数据

SQL> update customer set name='xifenfei' where cust_num=2;
1 row updated.
SQL> insert into customer values(5,'wwww.xifenfei.com5');
1 row created.
SQL> delete from customer where cust_num=1;
1 row deleted.
SQL> commit;
Commit complete.

6.TT中验证数据

Command> select * from oratt.customer;
< 2, xifenfei >
< 3, wwww.xifenfei.com3 >
< 4, wwww.xifenfei.com4 >
< 5, wwww.xifenfei.com5 >
4 rows found.
Command> select * from oratt.orders;
< 5, 3 >
< 6, 3 >
2 rows found.

7.补充说明
7.1)在oracle中需要授权cacheuser有访问oratt中相关表权限，不然创建cache group失败
7.2)自动刷新数据需要设置AUTOREFRESH STATE ON，其他方法初始化关联表的数据暂未知
7.3)在TT中，关联表删除是级联的