1.确认oracle中基础表jobs
[timesten@XAG816 ~]$ sqlplus ttuser/oracle@DG
SQL> desc jobs;
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
SQL> select count(1) from jobs;
COUNT(1)
----------
19
- 将基础表的读写权限赋予oracle的缓存管理用户
grant select,insert,update,delete on jobs to cacheadm;
3.使用缓存管理用户登录TimesTen，启动缓存代理并建立缓存组
[timesten@XAG816 oraclescripts]$
ttisql -connstr "dsn=cachedb1_1122; uid = cacheadm; pwd = timesten; oraclepwd = oracle" -e 'set prompt "CACHEADM>"'
#启动缓存代理
CACHEADM>call ttcachestart;
[timesten@XAG816 ~]$ ttAdmin -query cachedb1_1122 | grep Started
Replication Manually Started : False
Cache Agent Manually Started : True
#建立缓存组
CACHEADM>
create dynamic asynchronous writethrough cache group d_awt
from
ttuser.jobs
(
job_id VARCHAR2(10 byte) not null,
job_title VARCHAR2(35 byte) not null,
min_salary NUMBER(6),
max_salary NUMBER(6),
primary key(job_id)
) aging lru on;
CACHEADM>cachegroups d_awt;
Cache Group CACHEADM.D_AWT:
Cache Group Type: Asynchronous Writethrough (Dynamic)
Autorefresh: No
Aging: LRU on
Root Table: TTUSER.JOBS
Table Type: Propagate
1 cache group found.
Cache Group CACHEADM.AWT:
Cache Group Type: Asynchronous Writethrough
Autorefresh: No
Aging: No aging defined
Root Table: TTUSER.JOBS
Table Type: Propagate
1 cache group found.
CACHEADM>repschemes
Replication Scheme TTREP._AWTREPSCHEME:
Element: _1798032
Type: Table TTUSER.JOBS
Master Store: CACHEDB1_1122 on XAG816.COM Transmit Durable
Subscriber Store: _ORACLE from XAG816.COM
Store: CACHEDB1_1122 on XAG816.COM
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled
Store: _ORACLE from XAG816.COM
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled
1 replication scheme found.
#启动复制代理
CACHEADM> call ttrepstart;
[timesten@XAG816 ~]$ ttAdmin -query cachedb1_1122 | grep Started
Replication Manually Started : True
Cache Agent Manually Started : True
#查询缓存表的数据
[timesten@XAG816 ~]$
ttisql -connstr "dsn=cachedb1_1122; uid = ttuser;pwd = timesten" -e 'set prompt "TTUSER>"'
TTUSER>select * from jobs where job_id = 'FI_MGR';
15022: OraclePwd connection attribute needs to be specified and has to be non-empty for using TimesTen Cache features
5109: Cache Connect general error: BDB connection not open.
[timesten@XAG816 ~]$ ttisql -connstr "dsn=cachedb1_1122; uid = ttuser;pwd = timesten; oraclepwd=oracle" -e 'set prompt "TTUSER>"'
TTUSER>select * from jobs where job_id = 'FI_MGR';
< FI_MGR, Finance Manager, 8200, 16000 >
TTUSER>select count(1) from jobs;
< 1 >
CACHEADM>load cache group d_awt commit every 256 rows;
TTUSER>select count(*) from jobs;
< 19 >
TTUSER>insert into jobs values('A001','E1',10000,20000);
1 row inserted.
SQL> select * from jobs where job_id='A001';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
A001 E1 10000 20000
TTUSER>alter table jobs add job_name VARCHAR2(20);
5026: Cannot alter cached tables
TTUSER>update jobs set min_salary = 8888 where job_id='A001';
SQL> select * from jobs where job_id='A001';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
A001 E1 8888 20000
- 测试后清理
CACHEADM>call ttrepstop;
CACHEADM>drop cache group d_awt;
TTUSER>select * from jobs;
2206: Table TTUSER.JOBS not found
