一、Oracle数据库创建相关用户和权限
1.创建timesten用户
store information about cache grids
SQL>
CREATE
TABLESPACE cachetblsp DATAFILE
2
'E:\ORACLE\ORADATA\XFF\datfttuser.dbf'
3
SIZE
10M autoextend
on
next
10m maxsize 30g;
表空间已创建。
SQL> @E:\oracle\timesten\oraclescripts\initCacheGlobalSchema
"cachetblsp"
Please enter the tablespace
where
TIMESTEN
user
is
to
be created
The value chosen
for
tablespace
is
cachetblsp
******* Creation
of
TIMESTEN
schema
and
TT_CACHE_ADMIN_ROLE starts *******
1. Creating TIMESTEN
schema
2. Creating TIMESTEN.TT_GRIDID
table
3. Creating TIMESTEN.TT_GRIDINFO
table
4. Creating TT_CACHE_ADMIN_ROLE role
5. Granting
privileges
to
TT_CACHE_ADMIN_ROLE
** Creation
of
TIMESTEN
schema
and
TT_CACHE_ADMIN_ROLE done successfully **
PL/SQL 过程已成功完成。
|
2.创建测试用户
the Oracle tables to be cached in a TimesTen database
SQL>
create
user
xff identified
by
xifenfei;
用户已创建。
SQL>
grant
create
session,resource
to
xff;
授权成功。
|
3.创建cache管理用户
creates and maintains Oracle objects that store information used to manage cache grids and enforce predefined behaviors of particular cache group types.
SQL>
CREATE
USER
cacheuser IDENTIFIED
BY
oracle
2
DEFAULT
TABLESPACE cachetblsp QUOTA UNLIMITED
ON
cachetblsp;
用户已创建。
SQL> @grantCacheAdminPrivileges
"cacheuser"
Please enter the administrator
user
id
The value chosen
for
administrator
user
id
is
cacheuser
***************** Initialization
for
cache admin begins ******************
0. Granting the
CREATE
SESSION privilege
to
CACHEUSER
1. Granting the TT_CACHE_ADMIN_ROLE
to
CACHEUSER
2. Granting the DBMS_LOCK package privilege
to
CACHEUSER
3. Granting the RESOURCE privilege
to
CACHEUSER
4. Granting the
CREATE
PROCEDURE
privilege
to
CACHEUSER
5. Granting the
CREATE
ANY
TRIGGER
privilege
to
CACHEUSER
6. Granting the DBMS_LOB package privilege
to
CACHEUSER
7. Granting the
SELECT
on
SYS.ALL_OBJECTS privilege
to
CACHEUSER
8. Granting the
SELECT
on
SYS.ALL_SYNONYMS privilege
to
CACHEUSER
9. Checking if the cache administrator
user
has permissions
on
the
default
tablespace
Permission exists
11. Granting the
CREATE
ANY
TYPE privilege
to
CACHEUSER
********* Initialization
for
cache admin
user
done successfully *********
|
二、TimesTen创建相关用户
1.cache管理用户
A cache manager user performs cache grid and cache group operations. The TimesTen cache manager user must have the same name as an Oracle user that can access the cached Oracle tables.
Command>
CREATE
USER
cacheuser IDENTIFIED
BY
timesten;
User
created.
Command>
GRANT
CREATE
SESSION, CACHE_MANAGER,
CREATE
ANY
TABLE
TO
cacheuser;
|
2.cache对应用户
You must create a TimesTen cache table user with the same name as an Oracle schema user for each schema user who owns or will own Oracle tables to be cached in the TimesTen database
Command>
create
user
xff identified
by
timesten;
User
created.
|
三、配置DSN
Data Store Path + Name: E:\oracle\timesten\mytt_db\data
Permanent Data Size: 64
Oracle Net Service Name: XFF
Database Character Set: ZHS16GBK
|
四、在TT中设置cache管理用户名和密码
ttIsql
"DSN=my_ttdb;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheUidPwdSet(
'cacheuser'
,
'oracle'
);
Warning 5183: Function kollglid2 was not
in
the library. The
function
will not
be called.
Warning 5183: Function kollgsnp2 was not
in
the library. The
function
will not
be called.
Warning 5186: The OCI client library
in
use does not contain required routines
to support caching LOBS from Oracle.
--第一次调用相关函数因为没有编译出错，再次调用即可
Command> call ttCacheUidPwdSet(
'cacheuser'
,
'oracle'
);
|
五、创建测试表（Oracle db中）
SQL>
CREATE
TABLE
readtab (keyval NUMBER
NOT
NULL
PRIMARY
KEY
, str VARCHAR2(32));
表已创建。
SQL>
CREATE
TABLE
writetab (pk NUMBER
NOT
NULL
PRIMARY
KEY
, attr VARCHAR2(40));
表已创建。
SQL>
INSERT
INTO
readtab
VALUES
(1,
'Hello'
);
已创建 1 行。
SQL>
INSERT
INTO
readtab
VALUES
(2,
'World'
);
已创建 1 行。
SQL>
INSERT
INTO
writetab
VALUES
(100,
'XIFENFEI'
);
已创建 1 行。
SQL>
INSERT
INTO
writetab
VALUES
(101,
'WWW.XIFENFEI.COM'
);
已创建 1 行。
SQL>
COMMIT
;
提交完成。
SQL>
GRANT
SELECT
ON
readtab
TO
cacheuser;
授权成功。
SQL>
GRANT
SELECT
ON
writetab
TO
cacheuser;
授权成功。
SQL>
GRANT
INSERT
ON
writetab
TO
cacheuser;
授权成功。
SQL>
GRANT
UPDATE
ON
writetab
TO
cacheuser;
授权成功。
SQL>
GRANT
DELETE
ON
writetab
TO
cacheuser;
授权成功。
|
六、TimesTen相关配置
1.创建cache grid
Command> call ttGridCreate(
'myGrid'
);
Command> call ttGridNameSet(
'myGrid'
);
|
2.Start the cache agent
Command> call ttCacheStart;
|
3.创建cache group
Command> call ttCacheStart;
Command> CREATE READONLY CACHE GROUP readcache
> AUTOREFRESH INTERVAL 5 SECONDS
> FROM XFF.readtab
> (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));
Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP writecache
> FROM XFF.writetab
> (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));
Command> cachegroups;
Cache Group CACHEUSER.READCACHE:
Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: Paused
Autorefresh Interval: 5 Seconds
Autorefresh Status: ok
Aging: No aging defined
Root Table: XFF.READTAB
Table Type: Read Only
Cache Group CACHEUSER.WRITECACHE:
Cache Group Type: Asynchronous Writethrough (Dynamic)
Autorefresh: No
Aging: LRU on
Root Table: XFF.WRITETAB
Table Type: Propagate
2 cache
groups
found.
|
4.Start the replication agent for the AWT cache group
Command> call ttRepStart;
|
5.Manually load the cache group
Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
2 cache instances affected.
Command> LOAD CACHE GROUP writecache COMMIT EVERY 256 ROWS;
2 cache instances affected.
|
七、TT授权
主要是为了直接在cacheuser中操作方便，无其他应意义
ttisql my_ttdb
Command> GRANT SELECT ON xff.readtab TO cacheuser;
Command> GRANT SELECT ON xff.writetab TO cacheuser;
Command> GRANT UPDATE ON xff.writetab TO cacheuser;
Command> GRANT DELETE ON xff.writetab TO cacheuser;
Command> GRANT INSERT ON xff.writetab TO cacheuser;
|
八、相关测试
1.测试READTAB表
Command> SELECT * FROM XFF.READTAB;
< 1, Hello >
< 2, World >
2 rows found.
SQL> INSERT INTO readtab VALUES (3,
'Welcome'
);
已创建 1 行。
SQL> commit;
提交完成。
Command> SELECT * FROM XFF.READTAB;
< 1, Hello >
< 2, World >
< 3, Welcome >
3 rows found.
SQL> update readtab
set
str=
'www.xifenfei'
;
已更新3行。
SQL> commit;
提交完成。
Command> SELECT * FROM XFF.READTAB;
< 1, www.xifenfei >
< 2, www.xifenfei >
< 3, www.xifenfei >
3 rows found.
SQL> delete from readtab where keyval=3;
已删除 1 行。
SQL> commit;
提交完成。
Command> SELECT * FROM XFF.READTAB;
< 1, www.xifenfei >
< 2, www.xifenfei >
2 rows found.
|
2.测试WRITETAB表
Command> SELECT * FROM XFF.writeTAB;
< 100, XIFENFEI >
< 101, WWW.XIFENFEI.COM >
2 rows found.
Command> update xff.writetab
set
attr=
'www.xifenfei.com'
where pk=100;
1 row updated.
Command> commit;
SQL>
select
* from writetab;
PK ATTR
---------- ----------------------------------------
100 www.xifenfei.com
101 WWW.XIFENFEI.COM
Command> insert into xff.writetab values(102,
'xifenfei'
);
1 row inserted.
Command> commit;
SQL>
select
* from writetab;
PK ATTR
---------- --------------------------------
102 xifenfei
100 www.xifenfei.com
101 WWW.XIFENFEI.COM
|
扫一扫
384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
