实战:oracletimesten11.2.2.7.0oncentos6.5(三)
------4.2 timesten端创建相关用户和权限
----步骤0：修改DSN并设置需要的数据目录文件
#vi /app/timesten/TimesTen/ttwind/info/sys.odbc.ini
[ttwind]
Driver=/app/timesten/TimesTen/ttwind/lib/libtten.so
DataStore=/app/timesten/TimesTen/ttwind/info/datastore/ttwind
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
OracleNetServiceName=orcl
mkdir -p /app/timesten/TimesTen/ttwind/info/datastore/ttwind
chmod -R 770 /app/timesten/TimesTen/ttwind/info/datastore/ttwind
ttIsql ttwind
--步骤1：cache管理用户
CREATE USER cacheuser IDENTIFIED BY cacheuser ;
GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE,DROP ANY TABLE TO cacheuser;
--步骤2：cache对应用户
create user scott identified by tiger;
grant create session ,adminto scott;
--步骤3：将oracle cache管理用户与timesten关联,设置oracle连接的用户名和密码,
cache agent将会使用这个用户名和密码去oracle中读取
call ttcacheuidpwdset ('cacheuser','cacheuser');
--步骤4：创建cache grid
$ ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser"
Command> call ttGridCreate('myGrid');
--步骤5：将cache database 和cache grid关联即把当前的grid设为刚创建好的myGrid
Command> call ttGridNameSet('myGrid');
--步骤6：测试
autocommit 0;
set passthrough 2;
select * from v$version;
select table_name from user_tables where table_name like '%MYGRID%';
set passthrough 0;
--步骤7：测试只读缓存集合
$ ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser"
ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser;OracleNetServiceName=orcl"
call ttCacheStart;
call ttCacheStop;
drop Cache Group Cachtest;
create readonly cache group cachtest
autorefresh interval 5 seconds
mode incremental
from scott.t1
(
sid int not null primary key,
sname varchar2(10)
);
查看用户下所有的用户表
sqltables;
查看用户下所有Cache
cachegroups;
--删除cache group
drop Cache Group Cachtest;
--加载
load cache group cachtest commit every 10 rows;
************************************************************************
5.Timesten 快捷加载oracle数据库中的表和数据
************************************************************************
ttisql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;"
ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser;OracleNetServiceName=orcl"
ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser; OracleId=ORCL"
autocommit 0;
set passthrough 2;
--生成表结构
call ttTableSchemaFromOraQueryGet('scott','emp','SELECT * FROM scott.emp');
--导入数据
CALL ttLoadFromOracle ('scott','emp','SELECT * FROM scott.emp');
CREATE TABLE "SCOTT"."EMP" (
"EMPNO" number(4,0) NOT NULL,
"ENAME" varchar2(10 byte),
"JOB" varchar2(9 byte),
"MGR" number(4,0),
"HIREDATE" date,
"SAL" number(7,2),
"COMM" number(7,2),
"DEPTNO" number(2,0)
) >
create readonly cache group cacheuser.emp
autorefresh interval 5 seconds
mode incremental
from scott.t1
(
sid int not null primary key,
sname varchar2(10)
);