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.orasos.com1');
1 row created.
SQL> insert into customer values(2,'wwww.orasos.com2');
1 row created.
SQL> insert into customer values(3,'wwww.orasos.com3');
1 row created.
SQL> insert into customer values(4,'wwww.orasos.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.orasos.com1
2 wwww.orasos.com2
3 wwww.orasos.com3
4 wwww.orasos.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.orasos.com1 > < 2, wwww.orasos.com2 > < 3, wwww.orasos.com3 > < 4, wwww.orasos.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.orasos.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.orasos.com3 > < 4, wwww.orasos.com4 > < 5, wwww.orasos.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中，关联表删除是级联的
