Oracle Timesten 7.0 内存数据库: (4) 使用ttisql

ttIsql就像oracle的sqlplus一样,是个基本的管理工具.

1.运行模式

(1) 批处理模式: 把要运行的语句保存在一个文件里,然后在运行时指定要运行的文件,例如:
ttisql -f test.sql ttdemo
也可以先进入命令行界面,然后再用run命令调用一个文件,例如
~/bin # ttisql ttdemo
Command> run test.sql

(2) 交互模式:

2.获取帮助

help命令的基本用法是先用"help;"查看可用的命令,然后按命令查看"help cmd_name".


3.查看命令历史

这个更多的带有unix shell的特征,输入history或者h(简写)可以显示命令历史 .

4. 几个常用的命令

(1) describle(desc): 显示table,prepared statements和procedures的信息.

(2) cachegroups: 显示cachegroup的属性信息.

Command> cachegroup itpub110.readcache;

Cache Group ITPUB110.READCACHE:

Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds

Root Table: SYSTEM.READTAB
Table Type: Read Only

1 cache group found.
Command>

(3) dssize: 显示data store的大小,包括永久分区和临时分区,

Command> dssize;

PERM_ALLOCATED_SIZE: 20480
PERM_IN_USE_SIZE: 1719
PERM_IN_USE_HIGH_WATER: 1719
TEMP_ALLOCATED_SIZE: 20480
TEMP_IN_USE_SIZE: 6334
TEMP_IN_USE_HIGH_WATER: 6461

(4) monitor: 显示 data store的大小,联接,事务,checkpoint,锁等统计信息.

Command> monitor;

TIME_OF_1ST_CONNECT: Tue May 19 18:14:37 2009
DS_CONNECTS: 20
DS_DISCONNECTS: 8
DS_CHECKPOINTS: 98
DS_CHECKPOINTS_FUZZY: 96
DS_COMPACTS: 0
PERM_ALLOCATED_SIZE: 20480
PERM_IN_USE_SIZE: 1719
PERM_IN_USE_HIGH_WATER: 1719
TEMP_ALLOCATED_SIZE: 20480
TEMP_IN_USE_SIZE: 6334
TEMP_IN_USE_HIGH_WATER: 6461
SYS18: 0
XACT_BEGINS: 47079
XACT_COMMITS: 47078
XACT_D_COMMITS: 11469
XACT_ROLLBACKS: 0
LOG_FORCES: 108
DEADLOCKS: 0
LOCK_TIMEOUTS: 0
LOCK_GRANTS_IMMED: 210950
LOCK_GRANTS_WAIT: 0
SYS19: 0
CMD_PREPARES: 50
CMD_REPREPARES: 0
CMD_TEMP_INDEXES: 0
LAST_LOG_FILE: 0
REPHOLD_LOG_FILE: -1
REPHOLD_LOG_OFF: -1
REP_XACT_COUNT: 0
REP_CONFLICT_COUNT: 0
REP_PEER_CONNECTIONS: 0
REP_PEER_RETRIES: 0
FIRST_LOG_FILE: 0
LOG_BYTES_TO_LOG_BUFFER: 183088
LOG_FS_READS: 0
LOG_FS_WRITES: 108
LOG_BUFFER_WAITS: 0
CHECKPOINT_BYTES_WRITTEN: 265608
SYS1: 11882
SYS2: 11882
SYS3: 0
SYS4: 0
SYS5: 0
SYS6: 0
SYS7: 4
SYS8: 0
REQUIRED_RECOVERY: 0
SYS11: 0
SYS12: 1
TYPE_MODE: 0
SYS13: 0
SYS14: 0
SYS15: 0
SYS16: 0
SYS17: 0
SYS9:

5. 定制ttisql的特性

ttisql也可以通过show/set命令显示和修改一些特性,例如:

Command> show autoCommit;
autocommit = 0 (OFF)
Command> set autoCommit 1;
Command> show autoCommit;
autocommit = 1 (ON)

象Unix shell一样,命令提示符也是可以改变的:

Command> set prompt "tt7-ttdemo>";
tt7-ttdemo>select sysdate from dual;
< 2009-05-19 17:31:14 >
1 row found.
tt7-ttdemo>


和SQL/PLUS类似,可以通过设置timing收集SQL语句的执行时间信息:

Command> set timing 1;
Command> connect ttdemo;
Connection successful: DSN=ttdemo;UID=itpub110;DataStore=/var/TimesTen/tmp/ttdemo;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;

DRIVER=/opt/TimesTen/tt70/lib/libtten.so;OracleId=shacol13;Authenticate=0;Isolation=1;PermSize=20;TempSize=20;TypeMode=0;
(Default setting AutoCommit=1)
Execution time (SQLDriverConnect) = 0.003177 seconds.
con1: Command> select count(*) from readtab;
< 2 >
1 row found.
Execution time (SQLExecute + Fetch Loop) = 0.000102 seconds.

可以通过show all查看当前的配置:

Command> show all;

Connection independent attribute values:

columnlabels = 0 (OFF)
editline = 1 (ON)
multipleconnections = 0 (OFF)
ncharencoding = LOCALE (US7ASCII)
prompt = 'Command> '
timing = 0 (OFF)
verbosity = 2
vertical = 0 (OFF)

Connection specific attribute values:

autocommit = 0 (OFF)
Client timeout = 0
Connection String = DSN=ttdemo;UID=itpub110;DataStore=/var/TimesTen/tmp/ttdemo;DatabaseCharacterSet=L32UTF8;ConnectionCharacterSet=US7ASCII;

DRIVER=/opt/TimesTen/tt70/lib/libtten.so;OracleId=shacol13Authenticate=0;Isolation=1;PermSize=20;TempSize=20;TypeMode=0;
isolation = READ_COMMITTED
Prefetch count = 5
Query threshold = 0 seconds (no threshold)
Query timeout = 0 seconds (no timeout)

Current Optimizer Settings:
Scan: 1
Hash: 1
Ttree: 1
TmpHash: 1
TmpTtree: 1
TmpTable: 1
NestedLoop: 1
MergeJoin: 1
GenPlan: 0
TblLock: 1
RowLock: 1
Rowid: 1
FirstRow: 0
IndexedOr: 1
PassThrough: 0
BranchAndBound: 1
ForceCompile: 0
CrViewSemCheck: 1
ShowJoinOrder: 0
CrViewSemCheck: 1
TransparentLoad: 0
UseBoyerMooreStringSearch: 0

Current Join Order:
<>

6.事务

支持常见的事务控制命令:
autocommit: 是否支持自动事务,缺省值是1.
commit:
commitDurable: 确保提交的数据已被写入事务日志，不会因为data store failure而丢失
rollback:
isolation: 查看/设置isolation级别,支持两种隔离级别: Read Committed 和 Serializable
sqlQueryTimeout:查看/设置一个SQL可以运行的最大时间,以秒为单位.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/100682/viewspace-1022301/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/100682/viewspace-1022301/