oracle tt数据库,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@]