Create/Work with an Oracle TimesTen 11.2.2 Database - Windows

In order to create and work with TimesTen database we will perform the below tasks.

1- Define Datasource (DSN)

2- Specify DSN, data source path & name, the size of database and the database character set.

3-Verify that database daemon is running
4-Use ttisql to connect and create/work the database

Go to control panel to create a DSN. A DSN defines the configuration of the database, you must define a DSN before creating your database.

DataStore is the location and the name of the database files.

go to First Connection tab

Permanent Data Size is the size in MB for the permanent partition of the database, set it to your desired size eg; 100M.

Temporary Data Size is the size in MB for the temporary partition of the database, set it to your desired size eg; 80M

Verify that database daemon

The main database daemon process must be running before a database can be created. To check this, run the ttstatus command. The ttstatus command can only be run if the TimeTen environment variables are set. You need to run the ttenv.bat script located in bin directory of the TimesTen installation folder.

C:\Users\inam.HOME>ttstatus
ttstatus: Could not connect to the TimesTen service.
If the TimesTen service is not running, please start it
by running "ttDaemonAdmin -start".
Check after the start of daemon
C:\Users\inam.HOME>ttstatus
TimesTen status report as of Tue Jan 01 10:09:24 2013

Daemon pid 7904 port 53396 instance tt1122_64
TimesTen server pid 5844 started on port 53397
---------------------------------------------------------
Accessible by group HOME\Domain Users
End of report

Create and Connect with DB

Use the SQL command line utility ttisql to connect to my_ttdb. For the first connection, the database my_ttdb is being created and then loaded into memory. After loading of database , commands against database can be run.

C:\Users\inam.HOME>ttisql my_ttdb
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=my_ttdb";
Connection successful: DSN=my_ttdb;UID=Inam;DataStore=C:\TimesTen\ttdata\my_ttdb;DatabaseCharacterSet=AL32UTF8;Connectio
nCharacterSet=US7ASCII;DRIVER=C:\TimesTen\TT1122~1\bin\ttdv1122.dll;LogDir=C:\TimesTen\ttlogs;PermSize=100;TempSize=80;T
ypeMode=0;
(Default setting AutoCommit=1)
Command>
Command> dssize
  PERM_ALLOCATED_SIZE:      102400
  PERM_IN_USE_SIZE:         8976
  PERM_IN_USE_HIGH_WATER:   8976
  TEMP_ALLOCATED_SIZE:      81920
  TEMP_IN_USE_SIZE:         8711
  TEMP_IN_USE_HIGH_WATER:   8775
--- check the db status now
Command> host ttstatus
TimesTen status report as of Tue Jan 01 10:59:07 2013

Daemon pid 7904 port 53396 instance tt1122_64
TimesTen server pid 5844 started on port 53397
------------------------------------------------------------------------
Data store c:\timesten\ttdata\my_ttdb
There are 12 connections to the data store
Shared Memory KEY Global\my_ttdb.c||timesten|ttdata.SHM.10 HANDLE 0x2dc (Lock Status Unknown)
PL/SQL Memory KEY Global\my_ttdb.c||timesten|ttdata.SHM.11 HANDLE 0xcc Address 0x000000005B8C0000
Type            PID     Context             Connection Name              ConnID
Process         6612    0x0000000001dff9d0  my_ttdb                           1
Subdaemon       8600    0x0000000000c14660  Manager                        2047
Subdaemon       8600    0x00000000018e0080  Rollback                       2046
Subdaemon       8600    0x0000000001a35c50  Aging                          2042
Subdaemon       8600    0x0000000001a6bf00  Log Marker                     2041
Subdaemon       8600    0x0000000015322020  AsyncMV                        2038
Subdaemon       8600    0x00000000153d8330  Flusher                        2045
Subdaemon       8600    0x0000000015468450  Deadlock Detector              2043
Subdaemon       8600    0x00000000154e8fa0  Checkpoint                     2037
Subdaemon       8600    0x000000001557a0d0  HistGC                         2040
Subdaemon       8600    0x0000000015590360  IndexGC                        2039
Subdaemon       8600    0x0000000015e80080  Monitor                        2044
Replication policy  : Manual
Cache Agent policy  : Manual
PL/SQL enabled.
------------------------------------------------------------------------
Accessible by group HOME\Domain Users
End of report
Command>
my_ttdb is the current ttlsql connection
The other connections are TimesTen system processes for managing the database.
Working on DB
Create a database user and grant him privileges.

Command> create user abuzar identified by abuzar;
User created.
Command> grant create session, create table to abuzar;
Command>

connect with the newly created user

Command> connect "dsn=my_ttdb;uid=abuzar";
Enter password for 'abuzar':
Connection successful: DSN=my_ttdb;UID=abuzar;DataStore=C:\TimesTen\ttdata\my_ttdb;DatabaseCharacterSet=AL32UTF8;Connect
ionCharacterSet=US7ASCII;DRIVER=C:\TimesTen\TT1122~1\bin\ttdv1122.dll;LogDir=C:\TimesTen\ttlogs;PermSize=100;TempSize=80
;TypeMode=0;
(Default setting AutoCommit=1)
con1: Command>
-- Create Table
con1: Command> create table mytable(
             > id NUMBER NOT NULL PRIMARY KEY,
             > title VARCHAR2(300) NOT NULL,
             > code VARCHAR2(50),
             > count NUMBER(3),
             > last_updated DATE);
con1: Command>

-- create the Index
con1: Command> create index my_tableidx on mytable(code);
The tables command lists the tables owned by the user Abuzar

con1: Command> tables;
  ABUZAR.MYTABLE
1 table found.
con1: Command> indexes;
Indexes on table ABUZAR.MYTABLE:
  MYTABLE: unique range index on columns:
    ID
  MY_TABLEIDX: non-unique range index on columns:
    CODE
  2 indexes found.

2 indexes found on 1 table.


con1: Command> exit;
Disconnecting from my_ttdb...
Disconnecting from con1...
Done.


Checking contents

Check the content of the DataStore and Transaction Log directories. The database checkpoint and transaction log files should be created.

TimesTen maintains two complete images of the contents of a database on disk. These images are called checkpoint files.

Transaction logs are persisted to disk. They are used for database recovery and replication.