Usage Example

This section explains how to use the utility. The typical sequence of steps is as follows:
  1. Determine the access information for the Oracle database, including the user name, password, host name, port, service name etc.
  2. Ensure that the system on which you intend to run ttImportFromOracle has a working Oracle client installation (the InstantClient is sufficient). Configure the client to connect to the target Oracle database, and verify the connection using SQL*Plus.
  3. Download the appropriate ttImportFromOracle binary for your platform. Place the binary in a location from where you can easily run it. On Unix or Linux platforms, set the appropriate execute permission on the binary.
  4. Identify the tables you wish to analyze and import.
  5. Run the utility to generate the necessary DDL files and other scripts.
  6. Edit (if required) and then run the CreateUsers.sql file to create the required user(s).
  7. Run the CreateTables.sql file to create the tables.
  8. Import the data into TimesTen by running the ttPDL.sh script.
  9. If desired, run the CreateIndexes.sql file to create the imported indexes and constraints.
  10. To update the optimizer statistics, run the UpdateStats.sql file.
Now, each of these steps are described in detail and provide examples. The Oracle database server is accessed by TNS name MYDB. The TNS entry is: 
MYDB =  
(DESCRIPTION =  
(ADDRESS =   
(PROTOCOL = TCP)(HOST = oel5-odb1.oracle.net)(PORT = 1521))  
(CONNECT_DATA =  
(SERVER = DEDICATED)  
(SERVICE_NAME = orcl)  
)  
)

For any Oracle application, the TNS_ADMIN environment variable is set to point to the directory containing the tnsnames.ora file before running the utility if TNS name resolution is the desired method for accessing the Oracle DB server. ttImportFromOracle also supports the use of Oracle Easy Connect syntax serverhostname:portno/dbservicename for accessing the Oracle database.

In this example, the credentials to access the Oracle database are username scott and password test. User scott has the following tables, indexes, and data: 
CREATE TABLE PARENTTAB  
(  
P_PK          NUMBER(10,0) NOT NULL,  
P_C1          NUMBER,  
P_C2          DATE NOT NULL,  
P_C3          VARCHAR2(200),  
P_C4          NVARCHAR2(1000),  
CONSTRAINT CP_PK PRIMARY KEY ( P_PK )  
);  

CREATE UNIQUE INDEX P_IX1 ON PARENTTAB( P_C1 );  
CREATE INDEX P_IX2 ON PARENTTAB( P_C2 );  
INSERT INTO PARENTTAB VALUES(1, 10, '2012-12-01', 'ABCDE', 'ABCDE');  
INSERT INTO PARENTTAB VALUES(2, 20, '2012-12-02', NULL, 'A');  
INSERT INTO PARENTTAB VALUES(3, 30, '2012-12-03', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL);  
INSERT INTO PARENTTAB VALUES(4, 40, '2012-12-04', 'ABC', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');  
INSERT INTO PARENTTAB VALUES(5, 50, '2012-12-05', 'ABCDE', NULL);  
CREATE TABLE CHILDTAB  
(  
C_PK          NUMBER(6,0) NOT NULL,  
C_FK          NUMBER(10,0),  
C_C1          VARCHAR2(255),  
CONSTRAINT CC_PK PRIMARY KEY ( C_PK ),  
CONSTRAINT CC_FK FOREIGN KEY ( C_FK)  
REFERENCES PARENTTAB( P_PK )  
);  
INSERT INTO CHILDTAB VALUES ( 1, 3, 'ZZZ');  
INSERT INTO CHILDTAB VALUES ( 2, 2, NULL );  
INSERT INTO CHILDTAB VALUES ( 3, 5, 'ABCDEFGHIJKLMNOPQRST' );
The following example uses a TimesTen installation in which the instance administrator is oracle and a DSN named MYTTDB is configured as follows:
[MYTTDB]  
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so  
DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/myttdb  
PermSize=128  
TempSize=32  
PLSQL=1  
DatabaseCharacterSet=AL32UTF8  
ConnectionCharacterSet=AL32UTF8  
OracleNetServiceName=MYDB

The necessary network and TNS configuration is in place to allow TimesTen database to communicate with the Oracle database.

First, run ttImportFromOracle with the default options to analyze these simple tables and generate DDL files and scripts. On the TimesTen system, run the following command: 
$ ttImportFromOracle  –oraConn  scott/test@mydb –tables parenttab childtab
This results in the following output as the utility runs the various processing steps:
Beginning processing  
Resolving any tablename wildcards  
Eliminating any duplicate tables  
Getting metadata from source  
Generating database user list  
Assigning TimesTen datatypes  
Analyzing source tables  
	 
Analyzing table 'SCOTT.PARENTTAB' ...  
Analyzing table 'SCOTT.CHILDTAB' ...  
Estimating table sizes  
Estimating index sizes  
Evaluating parallel data load   
Generating output files  
Finished processing
The following output files are generated:
$ ls ttPDL.sh  
CreateIndexes.sql  
CreateTables.sql  
CreateUsers.sql  
DropIndexes.sql  
DropTables.sql  
LoadData.sql  
TableList.txt 
UpdateStats.sql
The following is the content of the CreateUsers.sql file:
----------------------------------------------------------------------------
-- Generated by ttImportFromOracle 26.1.1.4.0 - Tue Aug 18 22:13:57 2026
----------------------------------------------------------------------------

-- Replace the password below with a unique secret.
-- Remove PASSWORD EXPIRE and ACCOUNT LOCK when this
-- user is ready to authenticate.

CREATE USER SCOTT IDENTIFIED BY 'replace_this_password' PASSWORD EXPIRE ACCOUNT LOCK;

GRANT CREATE SESSION, CREATE TABLE TO SCOTT;

GRANT CREATE VIEW, CREATE SEQUENCE, CREATE SYNONYM TO SCOTT;

This script can be used to create the scott user in the TimesTen database. By default, the password is set to the same value as the username. You should edit this file to use a more secure password or change the user password after the user is created.

Some of the permission grants are commented out because it may not be desirable to grant this user all of these privileges. If you want to grant any of these additional privileges, edit the file accordingly before running it.

The following is the contents of the CreateTables.sql file: 

----------------------------------------------------------------------------  
-- Generated by ttImportFromOracle 3.4.0 - Mon Feb  3 16:30:33 GMT 2014  
------------------------------------------------------------------------------  
-- See end of file for table size totals  --  
-- Source is Oracle Database 11.2.0.2.0  --  
-- Character set is AL32UTF8  --  
------------------------------------------------------------------------------  
-- Type mapping modes:  
-- 	Numeric: 1 - STANDARD  
--        Varlen: 0 - NONE  
--        RAW/LONG/LONG RAW: 1 - YES  
--        LOBs: 1 - YES  
--        TIMESTAMP WITH TZ: 1 - YES 
--        Compression mode: 0 - NONE  
----------------------------------------------------------------------------   
-- ************************************************************  
-- Rows: 5  
-- Bytes per row: Oracle  - max 2251  
--                TimesTen - inline 53, not inline max 2232, total 2285  
-- Estimated table size = 29265 bytes  

CREATE TABLE SCOTT.PARENTTAB  
(  
P_PK                           TT_BIGINT NOT NULL,  
-- Oracle type:                    NUMBER(10,0)  
P_C1                           NUMBER,  
-- Oracle type:                    NUMBER  
P_C2                           DATE NOT NULL,  
-- Oracle type:                    DATE  
P_C3                           VARCHAR2(200 BYTE) NOT INLINE,  
-- Oracle type:                    VARCHAR2(200 BYTE)  
P_C4                           NVARCHAR2(1000) NOT INLINE  
-- Oracle type:                     NVARCHAR2(1000)  
);  
-- ************************************************************  
-- Rows: 3  
-- Bytes per row: Oracle  - max 299  
–- TimesTen - inline 20, not inline max 271, total 291  
-- Estimated table size = 15205 bytes  

CREATE TABLE SCOTT.CHILDTAB  
(  
C_PK     TT_INTEGER NOT NULL,  
-- Oracle type:    NUMBER(6,0)   
C_FK      TT_BIGINT,  
-- Oracle type:    NUMBER(10,0)  
C_C1      VARCHAR2(255 BYTE) NOT INLINE  
-- Oracle type:    VARCHAR2(255 BYTE)  
); 
----------------------------------------------------  
-- Estimated total size for all tables: 44470 bytes  
----------------------------------------------------

Here, ttImportFromOracle applies the default (standard) type mapping and maps some numeric columns to native TimesTen integer types. It also explicitly specifies the character length semantics and the INLINE/NOT INLINE storage settings in the generated DDL. Because all variable-length columns have maximum size greater than 128 bytes, ttImportFromOracle stores them as NOT INLINE.

The following is the contents of the CreateIndexes.sql file: 

	 
----------------------------------------------------------------------------  
-- Generated by ttImportFromOracle 3.4.0 - Mon Feb  3 16:30:33 GMT 2014  
----------------------------------------------------------------------------  
-- See end of file for index size totals. Sizing estimates for alternate  
-- index types are shown, enclosed in parentheses, for information only and 
-- are not included in the total. Index types should be chosen based on  
-- workload not on size.  
----------------------------------------------------------------------------	  
-- timing 1;  
-- ************************************************************  
-- Index: SCOTT.P_IX1  
-- Table: SCOTT.PARENTTAB  
-- Rows:  5  
-- Ttree estimated size = 4074 bytes  
-- (Btree estimated size = 6194 bytes)  
-- (Hash estimated size = 3114 bytes)  

CREATE UNIQUE INDEX SCOTT.P_IX1  
	ON SCOTT.PARENTTAB  
	(  
		P_C1  
	);  

-- ************************************************************  
-- Index: SCOTT.P_IX2  
-- Table: SCOTT.PARENTTAB  
-- Rows:  5  
-- Ttree estimated size = 4074 bytes  
-- (Btree estimated size = 5170 bytes)  
-- (Hash estimated size = 2922 bytes)  

CREATE INDEX SCOTT.P_IX2  
	ON SCOTT.PARENTTAB  
	(  
		P_C2  
	);  

-- ************************************************************  
-- Index: SCOTT.CP_PK  
-- Table: SCOTT.PARENTTAB  
-- Rows:  5  
-- Ttree estimated size = 4074 bytes  
-- (Btree estimated size = 5170 bytes)  
-- (Hash estimated size = 2874 bytes)  

ALTER TABLE SCOTT.PARENTTAB  
	ADD CONSTRAINT CP_PK  
	PRIMARY KEY  
	(  
	P_PK  
	);

************************************************************  
-- Index: SCOTT.CC_PK  
-- Table: SCOTT.CHILDTAB  
-- Rows:  3  
-- Ttree estimated size = 4074 bytes  
-- (Btree estimated size = 5170 bytes)  
-- (Hash estimated size = 2874 bytes)  

ALTER TABLE SCOTT.CHILDTAB  
	ADD CONSTRAINT CC_PK  
	PRIMARY KEY  
	(  
		C_PK  
	);  

************************************************************  
-- Index: SCOTT.CC_FK  
-- Table: SCOTT.CHILDTAB  
-- Rows:  3  
-- Ttree estimated size = 4074 bytes  
-- (Btree estimated size = 5170 bytes)  
-- (Hash estimated size = 2922 bytes)  

ALTER TABLE SCOTT.CHILDTAB  
	ADD CONSTRAINT CC_FK FOREIGN KEY  
	(  
		C_FK  
	)  
REFERENCES SCOTT.PARENTTAB  
	(  
		P_PK  
	); 

–-----------------------------------------------------------------------------  
-- Estimated total index size for all indexes = 20370 bytes 
-----------------------------------------------------------------------------

ttImportFromOracle imports all unique indexes, non-unique indexes, primary keys, and foreign key definitions. This is the default behavior.

The following is the contents of the TableList.txt file: 

############################################################################  
## Generated by ttImportFromOracle 3.4.0 - Mon Feb  3 16:30:33 GMT 2014  
############################################################################   
SCOTT.PARENTTAB:rows=5:ttwriters=3:oradop=1:orapar=none  
SCOTT.CHILDTAB:rows=3:ttwriters=3:oradop=1:orapar=none

This is the generated table file, which you can use with the -tableFile option.

Now, rerun the utility with aggressive type mapping for both numeric and variable-length data types (-typeMap 2,1) and enable compression evaluation (-compression 2).

In addition, suppress the output of foreign key and primary key definitions by specifying -constraints 0,0. This example also uses Easy Connect syntax for illustration. 

$ ttImportFromOracle -oraConn scott/test@oel5-odb1.oracle.net:1521/orcl  -tableFile tablelist.txt -typeMap 2,1 -compression 2 -constraints
Beginning processing   
Resolving any tablename wildcards  
Eliminating any duplicate tables   
Getting metadata from source   
Generating database user list   
Assigning TimesTen datatypes   
Analyzing source tables (this may take some time)   
Analyzing table 'SCOTT.PARENTTAB' ...   
Analyzing table 'SCOTT.CHILDTAB' ...   
Optimizing TimesTen datatypes   
Estimating compression ratios   
Estimating index sizes   
Evaluating parallel data load   
Generating output files   
Finished processing

Let's see how this affects the output files.

The following is the contents of the CreateTables.sql file: 

----------------------------------------------------------------------------  
-- Generated by ttImportFromOracle 3.4.0 - Mon Feb  3 16:30:33 GMT 2014 
 ---------------------------------------------------------------------------- 
--  
-- See end of file for table size totals  
--  
-- Source is Oracle Database 11.2.0.2.0  
--  
-- Character set is AL32UTF8  
--  
---------------------------------------------------------------------------- 
--  
-- Type mapping modes:  
--    Numeric: 2 - AGGRESSIVE  
--    Varlen: 1 - AGGRESSIVE  
--    RAW/LONG/LONG RAW: 1 - YES  
--    LOBs: 1 - YES  
--  TIMESTAMP WITH TZ: 1 - YES  
--  
-- Compression mode: 2 - VARIABLE  
--                   
Target = 70%, minrows = 1024  
--  
----------------------------------------------------------------------------   
-- ************************************************************  
-- Rows: 5  
-- Bytes per row: Oracle   - max 2251
--                TimesTen - inline 209, not inline max 0, total 209  
-- Estimated table size = 56363 bytes  
-- Table does not qualify for compression  

CREATE TABLE SCOTT.PARENTTAB  
(  
P_PK               TT_TINYINT NOT NULL,  
-- Oracle type:       NUMBER(10,0)  
P_C1                TT_TINYINT,  
-- Oracle type:       NUMBER   
P_C2                 DATE NOT NULL,  
-- Oracle type:        DATE  
P_C3                VARCHAR2(104 BYTE) INLINE,  
-- Oracle type:        VARCHAR2(200 BYTE)  
P_C4                NVARCHAR2(26) INLINE  
-- Oracle type:        NVARCHAR2(1000)  
);        

-- ************************************************************  
-- Rows: 3  
-- Bytes per row: Oracle   - max 299  
-- TimesTen - inline 100, not inline max 0, total 100  

-- Estimated table size = 33205 bytes  

-- Table does not qualify for compression  
CREATE TABLE SCOTT.CHILDTAB  
(  
C_PK        TT_TINYINT NOT NULL,  
-- Oracle type:   NUMBER(6,0)   
C_FK        TT_TINYINT,  
-- Oracle type:   NUMBER(10,0)  
C_C1        VARCHAR2(80 BYTE) INLINE  
-- Oracle type:    VARCHAR2(255 BYTE)  
);  

----------------------------------------------------------------------------  
-- Estimated total uncompressed size for all tables: 89568 bytes  
-- Estimated total compressed size for all tables:   89568 bytes  
-- Estimated total actual size for all tables:       89568 bytes  
--------------------------------------------------------------------------

As it can be seen, there are several differences compared to the standard type mapping. All numeric columns are mapped to native TimesTen integer types and, based on the actual range of values in the tables, are mapped to TT_TINYINT.

ttImportFromOracle maps the variable-length columns to much smaller maximum lengths, as a result all of them are INLINE. As a result, the tables occupy more space because ttImportFromOracle changes the [N]VARCHAR2 columns from NOT INLINE to INLINE.

A compression evaluation is performed for each table and outputs the results as comments. As expected, compression provides little or no benefit for small tables. However, it can significantly reduce the storage requirements of tables that contain many more rows and a high percentage of duplicate values.

The following is the contents of the CreateIndexes.sql file: 

----------------------------------------------------------------------------  
-- Generated by ttImportFromOracle 3.4.0 - Mon Feb  3 16:30:33 GMT 2014  
---------------------------------------------------------------------------- 
--  
-- See end of file for index size totals. Sizing estimates for alternate  
-- index types are shown, enclosed in parentheses, for information only and 
-- are not included in the total. Index types should be chosen based on  
--  workload not on size.
--
----------------------------------------------------------------------------  
-- timing 1;  
-- ************************************************************  
-- Index: SCOTT.P_IX1  
-- Table: SCOTT.PARENTTAB  
-- Rows:  5  
-- Ttree estimated size = 4074 bytes  
-- (Btree estimated size = 6194 bytes)  
-- (Hash estimated size = 3114 bytes)  

CREATE UNIQUE INDEX SCOTT.P_IX1  
	ON SCOTT.PARENTTAB  
	(  
		P_C1  
	);  
-- ************************************************************ 
-- Index: SCOTT.P_IX2 
-- Table: SCOTT.PARENTTAB  
-- Rows:  5  
-- Ttree estimated size = 4074 bytes  
-- (Btree estimated size = 5170 bytes)  
-- (Hash estimated size = 2922 bytes)  

CREATE INDEX SCOTT.P_IX2  
	ON SCOTT.PARENTTAB  
	(  
		P_C2  
	);  

----------------------------------------------------------------------------  
-- Estimated total index size for all tables = 8148 bytes  
-----------------------------------------------------------------------

You can see that the primary and foreign key definitions are not included. Run some of the generated files to create the user, tables, and indexes in TimesTen and then to import the data. To create the user scott in TimesTen, use the CreateUsers.sql file. 

$ ttIsql -f CreateUsers.sql myttdb    
Copyright (c) 1996-2011, Oracle.  All rights reserved.  Type 
? or "help" for help, type "exit" to quit ttIsql.  
---

run "CreateUsers.sql";   

----------------------------------------------------------------------------  
-- Generated by ttImportFromOracle 3.4.0 - Mon Feb  3 16:30:33 GMT 2014  
----------------------------------------------------------------------------   
CREATE USER SCOTT IDENTIFIED BY 'scott';    
User created.     
GRANT CREATE SESSION, CREATE TABLE TO SCOTT;    

-- GRANT CREATE VIEW, CREATE SEQUENCE, CREATE SYNONYM    
--       TO SCOTT;   
exit;   
Disconnecting...   
Done.

Now, you create the tables.

$ ttIsql -f CreateTables.sql myttdb    
Copyright (c) 1996-2011, Oracle.  All rights reserved.  Type 
? or "help" for help, type "exit" to quit ttIsql.  
--- 
run "CreateTables.sql";  

----------------------------------------------------------------------------  
-- Generated by ttImportFromOracle 3.4.0 - Mon Feb  3 16:30:33 GMT 2014  
---------------------------------------------------------------------------- 
--  
-- See end of file for table size totals  
--
-- Source is Oracle Database 11.2.0.2.0  
--
-- Character set is AL32UTF8  
--  
---------------------------------------------------------------------------- 
--  
-- Type mapping modes:  
--              Numeric: 2 - AGGRESSIVE  
--              Varlen: 1 - AGGRESSIVE  
--              RAW/LONG/LONG RAW: 1 - YES  
--              LOBs: 1 - YES  
--              TIMESTAMP WITH TZ: 1 - YES  
--  
--              Compression mode: 2 - VARIABLE  
--              Target = 70%, minrows = 1024  
--  ----------------------------------------------------------------------------   
-- ************************************************************  
-- Rows: 5  
-- Bytes per row: Oracle   - max 2251  
--                TimesTen - inline 209, not inline max 0, total 209  
-- Estimated table size = 56363 bytes  
-- Table does not qualify for compression  

CREATE TABLE SCOTT.PARENTTAB  
(  
  P_PK                           TT_TINYINT NOT NULL,  
-- Oracle type:                      NUMBER(10,0)  
  P_C1                           TT_TINYINT,  
-- Oracle type:                      NUMBER  
  P_C2                           DATE NOT NULL,  
-- Oracle type:                      DATE  
  P_C3                           VARCHAR2(104 BYTE) INLINE, 
-- Oracle type:                      VARCHAR2(200 BYTE)  
  P_C4                           NVARCHAR2(26) INLINE  
-- Oracle type:                      NVARCHAR2(1000)  
);   
--  
----------------------------------------------------------------------------  
-- Estimated total uncompressed size for all tables: 89568 bytes  
-- Estimated total compressed size for all tables:   89568 bytes  
-- Estimated total actual size for all tables:       89568 bytes  
----------------------------------------------------------------------------  
exit;  
Disconnecting...  
Done.

Now, load the data. Although these simple tables contain only a few rows and could be loaded with the basic LoadData.sql script, this example uses the parallel data load script ttPDL.sh.

The ttPDL.sh script requires a TimesTen connection string that includes a DSN and a UID as shown below. Omit TimesTen and Oracle passwords from the command line so the script prompts for their values that are more secure than specifying them on the command line. 

$ ttPDL.sh "DSN=myttdb;UID=scott"  
TimesTen Password for scott:  
Oracle password for scott:   
ttImportFromOracle parallel data load started at Wed Feb  5 16:30:32 GMT 2014   
Work directory is '/tmp/ttPDL/wrk18193'  
Data will be loaded using flashback query as of timestamp 2014-02-05 16:30:32  
 
----------------------------------------  
Executing Parallel Load Group: 1  
Group started at: Wed Feb  5 16:30:32 GMT 2014  
Total rows loaded: 8  
Group finished at: Wed Feb  5 16:30:40 GMT 2014  
---------------------------------------- 

Load completed: Log in ttPDLlog.txt  
ttImportFromOracle parallel data load finished at Wed Feb  5 16:30:41 GMT 2014

Next, you create indexes.

$ ttIsql -f CreateIndexes.sql myttdb    
Copyright (c) 1996-2011, Oracle.  All rights reserved.  Type 
? or "help" for help, type "exit" to quit ttIsql.  
--
run "CreateIndexes.sql";  

----------------------------------------------------------------------------  
-- Generated by ttImportFromOracle 3.4.0 - Mon Feb  3 16:30:33 GMT 2014  
---------------------------------------------------------------------------- 
--  
-- See end of file for index size totals. Sizing estimates for alternate  
-- index types are shown, enclosed in parentheses, for information only and 
-- are not included in the total. Index types should be chosen based on  
-- workload not on size.  
--  
----------------------------------------------------------------------------   
-- timing 1; 
-- ************************************************************  
-- Index: SCOTT.P_IX1  
-- Table: SCOTT.PARENTTAB  
-- Rows:  5  
-- Ttree estimated size = 4074 bytes 
--  
-- (Btree estimated size = 6194 bytes)  
-- (Hash estimated size = 3114 bytes) 

CREATE UNIQUE INDEX SCOTT.P_IX1  
          ON SCOTT.PARENTTAB  
             (  
                 P_C1  
             );  
-- ************************************************************  
-- Index: SCOTT.P_IX2  
-- Table: SCOTT.PARENTTAB 
-- Rows:  5  
-- Ttree estimated size = 4074 bytes  
-- (Btree estimated size = 5170 bytes)  
-- (Hash estimated size = 2922 bytes)  

CREATE INDEX SCOTT.P_IX2  
          ON SCOTT.PARENTTAB  
             (  
                 P_C2  
             );  
------------------------------------------------------------------------------  
-- Estimated total index size for all tables = 8148 bytes  
----------------------------------------------------------------------------  
exit;  
Disconnecting...  
Done.

Finally, you update the statistics:

$ ttIsql –f UpdateStats.sql myttdb  
	 
--
-- timing 1;  
  
call ttOptUpdateStats('SCOTT.PARENTTAB',1);  
  
call ttOptUpdateStats('SCOTT.CHILDTAB',1);  
 quit;  
Disconnecting...  
Done.