TimesTenでユーザを作る〜テーブルの確認まで #oracle

先日、TimesTen環境を仮想環境に構築してたけど、それから何もしてなかったので少し進めてみました。
といっても、ユーザを作成して、どんなテーブルがあるかだけですが…

1.sys.odbc.iniファイルとはの巻

TimesTenは、データソース名（DSN）を介して、DBにアクセスを行う
DNSは、TimesTenを識別する論理名（＝Oracleであれば、ORACLE_SIDのこと）
DSNには属性が含まれる（プロパティ的なもの）
Linux/UNIXでは、install_dir/info/sys.odbc.iniファイルに定義される
※このファイルは、別名、システムODBC.INIファイルと呼ばれる

実行結果

[timesten@localhost info]$ pwd
/home/timesten/TimesTen/tt1122/info
[timesten@localhost info]$ 
[timesten@localhost info]$ cat sys.odbc.ini
# Copyright (c) 1999, 2011, Oracle and/or its affiliates. All rights reserved. 

#########################################################################
#
# The following are the default values for connection attributes.
# In the Data Sources defined below, if the attribute is not explicitly
# set in its entry, TimesTen 11.2.2 uses the defaults as
# specified below.  For more information on these connection attributes,
# see the accompanying documentation.
#
# Lines in this file beginning with # or ; are treated as comments.
# In _attribute_=_value_ lines, the _value_ consists of everything
# after the = to the end of the line, with leading and trailing white
# space removed.
#
#########################################################################
#
#    ** attribute **   ** default **
#       AutoCreate      (1)
#       CkptFrequency   (600)
#       CkptLogVolume   (0 - off)
#       CkptRate=0      (0 - rate not limited)
#       Connections     (0 - 64 connections)
#       ConnectionCharacterSet (if DatabaseCharacterSet == TIMESTEN8
#                               then TIMESTEN8 else US7ASCII)
#       ConnectionName  (process argv[0])
#       Connections     (0 - use default setting of 64 connections)
#       DatabaseCharacterSet (no default)
#       DDLCommitBehavior    (0 = Oracle-style DDL commits)
#       Diagnostics     (1 - generate base level diagnostics ) 
#       DurableCommits  (0 - do not force log to disk on transaction commits)
#       ForceConnect    (0 = connection disallowed)
#       GroupRestrict   (none by default)
#       Isolation=1     (1 = read-committed)
#       LockLevel=0     (0 = row-level locking)
#       LockWait=10     (seconds)
#       LogAutoTruncate (1 - continue after log is truncated)
#       LogBuffSize     (65535 - measured in KB)
#       LogBufMB        (64 - measured in MB)
#       LogDir          (checkpoint directory - where db log files reside)
#       LogFileSize     (64 - measured in MB )
#       LogFlushMethod  (0 - controls sync of log data to transaction log files)
#       LogPurge        (1 - remove unneeded transaction log files)
#       MatchLogOpts    (0 - Values of Logging & LogPurge are not ignored)
#       MemoryLock      (0 - HP-UX, Linux, and Solaris platforms only
#                            and windows 64-bit)
#       NLS_LENGTH_SEMANTICS   (BYTE - default length semantic configuration)
#       NLS_NCHAR_CONV_EXCP    (0 - report data loss for data coversion between
#                               NCHAR/NVARCHAR data and CHAR/VARCHAR data)
#       NLS_SORT           (BINARY - the collating sequence to use for 
#                           linguistic comparisons)
#       Overwrite          (0 - do not overwrite the existing datastore)
#       PermSize           (32 - measured in MB)
#       PermWarnThreshold  (90 - pecentage at which warnings should be issued)
#       Preallocate        (0 - do not preallocate disk space for the datastore
#                           should be preallocated )
#       PrivateCommands    (0 - share commands between connections)
#       PWD                (no default)
#       PWDCrypt           (no default)
#       QueryThreshold     (0 - do not return an error nor throw an SNMP trap
#                           if the query times out before executing )
#       RecoveryThreads    (1 - the number of threads used to rebuild indexes
#                           during recovery)
#       SQLQueryTimeout    (0 - time limit in seconds for executing SQL queries)
#       Temporary          (0 - do not create a permanent datastore)
#       TempSize           (default is derived from PermSize - measured in MB)
#       TempWarnThreshold  (90 - percentage at which out-of-memory warnings 
#                           should be issued)
#       TypeMode=0         (0 - Oracle types)
#       UID                (operating system user ID)
#       WaitForConnect     (1 - wait until connection to the datastore is 
#                           possible)
#
# IMDBC Cache Attributes
#       CachegridEnable      (1 - All cache groups in the data store are defined
#                             as members of a cache grid)
#       CacheGridMsgWait     (60 - seconds to wait for a cache grid message from 
#                             a remote member)
#       DynamicLoadEnable    (1 - Enable dynamic load of Oracle data to dynamic 
#                             cache groups for the current connection)
#       DynamicLoadErrorMode (0 - do not return an error on a transparent dynamic
#                             load behavior)
#       OracleNetServiceName (no default)
#       OraclePWD            (no default)
#       PassThrough          (0 = SQL not passed through to Oracle)
#       RACCallback          (1 = Install the TAF and FAN callbacks)
#       TransparentLoad      (0 = do not load data)
#
# Client Connection Attributes
#       ConnectionCharacterSet (if DatabaseCharacterSet == TIMESTEN8
#                               then TIMESTEN8 else US7ASCII)
#       ConnectionName  (process argv[0])
#       PWD             (no default)
#       PWDCrypt        (no default)
#       TTC_Server      (no default)
#       TTC_Server_DSN  (no default)
#       TTC_Timeout     (60 - seconds the client waits for a connection)
#       UID             (operating system user ID)
#
# PL/SQL Connection Attributes
#       PLSQL           (default is installation dependent.  If
#                        enabled at install time, default is 1)
#
#    The following Attributes are significant only when PLSQL=1
#       PLSQL_MEMORY_ADDRESS     (platform specific default; value is entered
#                                 as Hex: 20000000 means 0x20000000)
#       PLSQL_MEMORY_SIZE        (32 - measured in MB)
#       PLSQL_CODE_TYPE=interpreted (native is not available on all platforms)
#       PLSQL_OPTIMIZE_LEVEL     (2 )
#       PLSQL_CCFLAGS            (default is "")
#       PLSQL_WARNINGS           (default is "")
#       PLSCOPE_SETTINGS         (IDENTIFIERS:NONE)
#       PLSQL_TIMEOUT            (30 - measured in seconds)
#       PLSQL_CONN_MEM_LIMIT     (100 - measured in MB)
#
#########################################################################


[ODBC Data Sources]
TT_1122=TimesTen 11.2.2 Driver
#sampledb_1122=TimesTen 11.2.2 Driver
#cachedb1_1122=TimesTen 11.2.2 Driver
#repdb1_1122=TimesTen 11.2.2 Driver
#repdb2_1122=TimesTen 11.2.2 Driver
#sampledbCS_1122=TimesTen 11.2.2 Client Driver
#cachedb1CS_1122=TimesTen 11.2.2 Client Driver
#repdb1CS_1122=TimesTen 11.2.2 Client Driver
#repdb2CS_1122=TimesTen 11.2.2 Client Driver

# -------------------------
dacek1112=TimesTen 11.2.2 Driver
# -------------------------


#####################################################################
# Instance-Specific System Database
#
# A predefined instance-specific database reserved for system use.
# It provides a well-known database for use when a connection
# is required to execute commands.
#
#####################################################################

[TT_1122]
Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/home/timesten/TimesTen/tt1122/info/TT_1122
DatabaseCharacterSet=US7ASCII

# -------------------------
# 2014/04/26(sat) Add
[dacek1112]
Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/home/timesten/TimesTen/dacek1112/info/dacek1112
DatabaseCharacterSet=US7ASCII
# -------------------------


#####################################################################
# Data source for Sample programs
#
# This database is used by the Quick Start sample programs.
# The sample database (sampledb) must be created prior to
# running the sample programs. The PermSize and TempSize attributes
# can be adjusted depending on the options used in running the
# sample benchmark programs.
#
#####################################################################

#[sampledb_1122]
#Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so
#DataStore=/home/timesten/TimesTen/tt1122/info/DemoDataStore/sampledb_1122
#PermSize=40
#TempSize=32
#PLSQL=0
#DatabaseCharacterSet=US7ASCII

#####################################################################
# Sample Data source for Quick Start IMDB Cache tutorial
#
# This database is used by the Quick Start IMDB Cache tutorial.
# Before using the cachedb1 DSN, uncomment both the
# DatabaseCharacterSet and OracleNetServiceName attributes and insert
# the appropriate values for the database character set of your
# Oracle database and the TNS service name for your Oracle database
#####################################################################

#[cachedb1_1122]
#Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so
#DataStore=/home/timesten/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122
#PermSize=40
#TempSize=32
#PLSQL=0
##DatabaseCharacterSet=AL32UTF8
##OracleNetServiceName=MyOrclDB

#####################################################################
# Sample Data source for Quick Start Replication tutorial
#
# This database is used by the Quick Start Active Standby tutorial
#####################################################################

#[repdb1_1122]
#Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so
#DataStore=/home/timesten/TimesTen/tt1122/info/DemoDataStore/repdb1_1122
#PermSize=40
#TempSize=32
#PLSQL=0
#DatabaseCharacterSet=AL32UTF8

#####################################################################
# Sample Data source for Quick Start Replication tutorial
#
# This database is used by the Quick Start Active Standby tutorial
#####################################################################

#[repdb2_1122]
#Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so
#DataStore=/home/timesten/TimesTen/tt1122/info/DemoDataStore/repdb2_1122
#PermSize=40
#TempSize=32
#PLSQL=0
#DatabaseCharacterSet=AL32UTF8

#####################################################################
#
# New data source definitions can be added below.
#
#####################################################################

########################################################################
# This following sample definitions should be in the .odbc.ini file
# that is used for the TimesTen 11.2.2 Client.
# The Server Name is set in the TTC_SERVER attribute.
# The Server DSN is set in the TTC_SERVER_DSN attribute.
#########################################################################


#[sampledbCS_1122]
#TTC_SERVER=ttLocalHost_tt1122
#TTC_SERVER_DSN=sampledb_1122

#[cachedb1CS_1122]
#TTC_SERVER=ttLocalHost_tt1122
#TTC_SERVER_DSN=cachedb1_1122

#[repdb1CS_1122]
#TTC_SERVER=ttLocalHost_tt1122
#TTC_SERVER_DSN=repdb1_1122

#[repdb2CS_1122]
#TTC_SERVER=ttLocalHost_tt1122
#TTC_SERVER_DSN=repdb2_1122

[timesten@localhost info]$

2.TimesTen DBの作成してみるの巻

TimesTenのDBは接続するだけで簡単に作成される
既存DBは初回接続時にメモリにロードされる
DBに接続が確立すると、DBオブジェクトを作成できる
DBは、TimesTenインスタンス管理者のみが作成することができる

実行コマンド

# TimesTenデーモンの起動
    ttdaemonadmin -start

# ↑が起動しない場合
    ttdaemonadmin -force -start

# ステータスの確認
    ttstatus

# ttisqlの起動  ※初回接続時は、ここでDBが作成される
    ttisql

# DSN接続
    connect "dsn=TT_1122";

実行結果


[timesten@localhost info]$ 
[timesten@localhost info]$ ttdaemonadmin -start
TimesTen Daemon startup OK.
[timesten@localhost info]$ 
[timesten@localhost info]$ 
[timesten@localhost info]$ ttstatus
TimesTen status report as of Sat Apr 26 14:58:21 2014

Daemon pid 3331 port 53396 instance tt1122
TimesTen server pid 3340 started on port 53397
------------------------------------------------------------------------
Data store /home/timesten/TimesTen/tt1122/info/TT_1122
There are no connections to the data store
Replication policy  : Manual
Cache Agent policy  : Manual
------------------------------------------------------------------------
Accessible by group ttadmin
End of report
[timesten@localhost info]$ 
[timesten@localhost info]$ 
[timesten@localhost info]$ ll | grep TT
-rw-rw----. 1 timesten ttadmin 22964768  4月 22 21:23 2014 TT_1122.ds0
-rw-rw----. 1 timesten ttadmin 22964768  4月 22 21:24 2014 TT_1122.ds1
-rw-rw----. 1 timesten ttadmin  3745792  4月 22 21:24 2014 TT_1122.log0
-rw-rw----. 1 timesten ttadmin 67108864  4月 22 21:12 2014 TT_1122.res0
-rw-rw----. 1 timesten ttadmin 67108864  4月 22 21:12 2014 TT_1122.res1
-rw-rw----. 1 timesten ttadmin 67108864  4月 22 21:12 2014 TT_1122.res2
[timesten@localhost info]$ 
[timesten@localhost info]$ 
[timesten@localhost info]$ 
[timesten@localhost info]$ 
[timesten@localhost info]$ ttisql

Copyright (c) 1996, 2013, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> 
Command> 
Command> connect "dsn=TT_1122";
Connection successful: DSN=TT_1122;UID=timesten;DataStore=/home/timesten/TimesTen/tt1122/info/TT_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/timesten/TimesTen/tt1122/lib/libtten.so;TypeMode=0;
(Default setting AutoCommit=1)
Command>
Command>

3.ユーザと権限の管理についての巻

下記のコマンドでユーザを作成する
※通常のOracleと同じ感じ

実行コマンド

# ユーザ作成

create user dacek1112 identified by *****;

実行結果

Command> create user dacek1112 identified by *****;

User created.

Command>

ユーザ作成後、適切な権限を付与する必要がある
- 権限は２種類
  - システム権限
    - DBで複数のオブジェクトにわたってシステムレベルのアクティビティを実行したり、
      特定のタイプの任意のスキーマオブジェクトでアクションを実行できる
    - この権限をもつと、そのユーザに関連するスキーマに表を作成できる
  - オブジェクト権限
    - 特定のスキーマオブジェクトで特定のアクションを実行する
    - この権限を付与されると、表から行の削除ができる
    - オブジェクトの所有者(作成者)には、常にオブジェクトに対するすべての権限がある

SQL文で権限を付与する場合は、GRANT（付与） または REVOKE（削除） を使用する

GRANT

SQL構文

GRANT {Privilege [,...] | ALL [PRIVILEGES]} TO {User |PUBLIC} [,...]

パラメータ

項目名	説明
Privilege	ADMIN CONNECT CREATE DATASTORE DDL WRITE SELECT
ALL [PRIVILEGES]	ユーザーにすべてのTimesTen権限を割り当てる
User	権限を付与するユーザーの名前
PUBLIC	TimesTenインスタンスで現在定義されているすべてのユーザー名、および今後定義されるすべてのユーザー名に権限を付与することを指定

コマンド例
```
GRANT ADMIN TO dacek1112;
```

REVOKE

SQL文

REVOKE {Privilege [ , ... ] | ALL [PRIVILEGES]} FROM {User |PUBLIC} [,...]

パラメータ

項目名	説明
Privilege	ADMIN CONNECT CREATE DATASTORE DDL WRITE SELECT
ALL [PRIVILEGES]	ユーザーからすべてのTimesTen権限を削除する
User	権限を削除するユーザーの名前
PUBLIC	TimesTenインスタンスで現在定義されているすべてのユーザー、および今後定義されるすべてのユーザーから権限を削除することを指定する

コマンド例
```
REVOKE ADMIN FROM dacek1112;
```

ユーザが作成されたかの確認

実行コマンド

select * from SYS.USER$;

実行結果

Command> 
Command> select * from SYS.USER$;
< 0, SYS                           , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825416, 0, 0, 0000, 00000000000000000000000000000000 >
< 1, PUBLIC                        , 0, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825432, 0, 0, 0000, 00000000000000000000000000000000 >
< 2, TTREP                         , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825448, 0, 0, 0000, 00000000000000000000000000000000 >
< 3, SYSTEM                        , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825464, 0, 0, 0000, 00000000000000000000000000000000 >
< 4, GRID                          , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825480, 0, 0, 0000, 00000000000000000000000000000000 >
< 10, TIMESTEN                      , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825496, 1, 0, 0000, 00000000000000000000000000000000 >
< 11, DACEK1112                     , 1, 0, $tten1$03e8$LiLAcACrE82fnLs7M5EGEtjEjR5Z2Ng3vmZIfr1Z1TJ4FJub, 2014-04-27 10:08:31.438613, <NULL>, <NULL>, 2825512, 0, 0, 0000, 00000000000000000000000000000000 >
< 12, DACEK                         , 1, 0, $tten1$03e8$nIgiebwjfIqhXzruNZCdeMBduu2QXAsM3V1NYseV1hotChgK, 2014-04-27 10:18:34.915839, <NULL>, <NULL>, 2825528, 0, 0, 0000, 00000000000000000000000000000000 >
8 rows found.
Command>

4.TimesTenにはどんなテーブルがあるのか見るだけの巻

実行コマンド

alltables;

実行結果

Command> 
Command> select * from SYS.USER$;
< 0, SYS                           , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825416, 0, 0, 0000, 00000000000000000000000000000000 >
< 1, PUBLIC                        , 0, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825432, 0, 0, 0000, 00000000000000000000000000000000 >
< 2, TTREP                         , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825448, 0, 0, 0000, 00000000000000000000000000000000 >
< 3, SYSTEM                        , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825464, 0, 0, 0000, 00000000000000000000000000000000 >
< 4, GRID                          , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825480, 0, 0, 0000, 00000000000000000000000000000000 >
< 10, TIMESTEN                      , 1, 0, <NULL>, 2014-04-22 21:12:42.559492, <NULL>, <NULL>, 2825496, 1, 0, 0000, 00000000000000000000000000000000 >
< 11, DACEK1112                     , 1, 0, $tten1$03e8$LiLAcACrE82fnLs7M5EGEtjEjR5Z2Ng3vmZIfr1Z1TJ4FJub, 2014-04-27 10:08:31.438613, <NULL>, <NULL>, 2825512, 0, 0, 0000, 00000000000000000000000000000000 >
< 12, DACEK                         , 1, 0, $tten1$03e8$nIgiebwjfIqhXzruNZCdeMBduu2QXAsM3V1NYseV1hotChgK, 2014-04-27 10:18:34.915839, <NULL>, <NULL>, 2825528, 0, 0, 0000, 00000000000000000000000000000000 >
8 rows found.
Command> 
Command>

TimesTenでユーザを作る〜テーブルの確認まで

先日、TimesTen環境を仮想環境に構築してたけど、それから何もしてなかったので少し進めてみました。といっても、ユーザを作成して、どんなテーブルがあるかだけですが…

1.sys.odbc.iniファイルとはの巻

2.TimesTen DBの作成してみるの巻

3.ユーザと権限の管理についての巻

4.TimesTenにはどんなテーブルがあるのか見るだけの巻

★次回は、もうちょっと進めてやりたいな…

先日、TimesTen環境を仮想環境に構築してたけど、それから何もしてなかったので少し進めてみました。
といっても、ユーザを作成して、どんなテーブルがあるかだけですが…