ttImportFromOracle

ttImportFromOracle analyzes a set of tables in an Oracle database and generates various SQL script files to allow those tables and their data to be imported into TimesTen. Options are available to import associated objects, such as primary and foreign keys. A key feature is the ability to analyze the table schema and data content to recommend the optimal data type mapping and compression.

The main features of ttImportFromOracle are:

Generates optimized scripts for importing objects from Oracle to TimesTen while minimizing the memory required to store those objects in TimesTen.
Supports Oracle databases configured with MAX_STRING_SIZE=EXTENDED and validates extended columns individually.
Maps native Oracle JSON columns to the TimesTen JSON data type. The maximum supported TimesTen JSON size is 4,194,304 bytes.
Optional data type mapping helps conserve storage space by mapping Oracle NUMBER columns to the most appropriate TimesTen native types or lower-precision NUMBER types, and by mapping [N]VARCHAR2 columns to smaller maximum lengths based on an analysis of the Oracle source data.
Provides multiple options for mapping RAW, LONG, LONG RAW, and LOB data types to compatible TimesTen data types.
Optional Compression Evaluation and Compression Clause Generation based on data analysis in Oracle.
Analyzes one or more Oracle tables in a single invocation.
Selectively include or exclude the import of primary keys, foreign keys, unique indexes, non-unique indexes, bitmap indexes, and materialized views.
The generated scripts invoke ttLoadFromOracle() built-in that enables parallel data loading to minimize elapsed time and maximize hardware utilization.
Handles tables containing columns with unsupported data types.
Provides detailed size estimates for tables and indexes.
Provides an extensive set of options for greater flexibility.

Note:

To use this utility, you must ensure that the following prerequisites are met:

The utility supports Oracle Database 19c, 23ai, and 26ai releases. Oracle Database 23ai and later releases support the JSON data type. Older versions are not supported.
Supports on a wide range of platforms:
- Linux ARM 64-bit
- Linux x86 64-bit
The system on which you plan to run the utility must have a compatible Oracle Client installed (Oracle Instant Client is sufficient) and configured to connect to the Oracle database.
Before running the utility, ensure that your shell environment is properly configured, including variables such as PATH and LD_LIBRARY_PATH (or the equivalent on your platform).

Required Privilege

This utility requires the following privileges for the source Oracle database:

CREATE SESSION
SELECT privilege on the source tables being imported
Access to the Oracle metadata views required by the utility, including ALL_TABLES, ALL_TAB_COLUMNS, ALL_INDEXES, and ALL_CONSTRAINTS.
When consistency=2 (see Detailed Description of Options) is specified for ttImportFromOracle, the Oracle user must also be granted execute privileges on DBMS_FLASHBACK: GRANT EXECUTE ON DBMS_FLASHBACK TO oracle_user;

For the TimesTen database:

CREATE SESSION to connect to the TimesTen database
CREATE TABLE to run CreateTables.sql
CREATE INDEX to run CreateIndexes.sql, including the creation of primary and foreign keys
INSERT privilege on the target tables to run LoadData.sql or ttPDL.sh. This privilege is implicit when the account owns the tables.
Permission to update optimizer statistics for UpdateStats.sql.
Ownership of the target tables and indexes, or equivalent privileges, to run DropTables.sql and DropIndexes.sql.
TimesTen instance administrator privileges are required only to run CreateUsers.sql, which creates the TimesTen database user.

Syntax

ttImportFromOracle [-h | -help | -? [full] ]  
  
ttImportFromOracle [-V | -version]  
  
ttImportFromOracle  -oraConn ouser[/opasswd][@oserver]  
                    [-ttOwner ttuser]  
 	            [-tables tablespec [tablespec]...]...                    
		      [-tableFile filepath]...  
                   [-importMviews { 0 | 1 }]  
                   [-typeMap num[,var[,rawlong[,lob[,tstz[,padding[,stats]]]]]]]  
                   [-compression level[,padding[,stats[,cfactor[,minrows]]]]]  
                   [-inlineLimit n]  
                   [-parallel dop]  
                   [-dataLoadParallelism dlpspec]  
                   [-indexes unique[,nonunique[,bitmap[,nodups]]]]  
                   [-constraints pkey[,fkey]]  
                   [-outputDir dirpath]  
                   [-prefix fnprefix]  
                   [-target { unix | windows }]  
                   [-onBadType { 0 | 1 | 2 }]  
                   [-verbosity { 0 | 1 }]

Options

ttImportFromOracle has these options:

Option	Description
`-h \| -help \| -? [full]`	Shows help (showing the above syntax).
`-V \| -version`	Displays the release number.
`-oraConn ouser[/opasswd][@oserver]`	Specifies connection credentials for the source Oracle database. `ouser` - Oracle username `opasswd` - The password for the `ouser`. If this is omitted, the user is prompted for the password. `oserver` - Identifies the source Oracle database server.
`-ttOwner ttuser`	Controls object ownership in TimesTen. `ttuser` - The name of the TimesTen user who owns all the imported objects.
`-tables tablespec [tablespec]...`	Specifies tables to be imported. `tablespec` - A table specification in the format: `[owner.]name[:attribute]...` This option can be specified multiple times to build up a list of tables to be imported.
`-tableFile filepath`	Specify a file containing a list of table specifications. `filepath` - Path of a text file containing table specifications of tables to be imported. This option can be specified multiple times and it can also be combined with one or more occurrences of the '-tables' option.
`-importMviews { 0 \| 1 }`	Controls whether materialized views are imported. `0 (default)` - Materialized views are not imported. `1` - Materialized views are imported as regular tables.
`-typeMap num \| var \| rawlong \| lob \| tstz \| padding \| stats`	Controls how source column data types are mapped to TimesTen types. `num` - Controls type mapping for numeric columns. Values are: `0` - Type mapping is not performed. `1` (default) - Standard numeric type mapping is performed (`NUMBER` to TimesTen native integer). `2` - Aggressive type mapping is performed for `NUMBER` columns. `var` - Controls type mapping for `VARCHAR2` and `NVARCHAR2` columns and variable-length analysis for `JSON` columns. Values are: `0` (default) - Type mapping is not performed. `1` - Aggressive type mapping is performed for `VARCHAR2` and `NVARCHAR2` columns. `JSON` columns are also analyzed for maximum data length; `JSON` remains mapped to native TimesTen `JSON`. `rawlong` - Controls type mapping for `RAW`, `LONG`, and `LONG RAW` columns. Values are: `0` - Type mapping is not performed. `RAW`, `LONG`, and `LONG RAW` are unsupported data types. `1` (default) - `RAW(n)` is mapped to `VARBINARY(n)`, `LONG` to `VARCHAR2(4194304)`, and `LONG RAW` to `VARBINARY(4194304)`. In addition, for `RAW(n)`, `var` mapping is applied. `lob` - Controls type mapping for `LOB` columns. Values are: `0` - Type mapping is not performed. `1` (default) - `CLOB` is mapped to `VARCHAR2(4194304)`, `NCLOB` to `NVARCHAR2(2097152)`, and `BLOB` to `VARBINARY(4194304)`. In addition, `var` mapping is applied. `tstz` - Controls type mapping for `TIMESTAMP WITH TIME ZONE`. Values are: `0` - Type mapping is not performed. `TIMESTAMP WITH TIME ZONE` is an unsupported data type. `1` (default) - `TIMESTAMP WITH TIME ZONE` is mapped to `TIMESTAMP`. When data is imported, the timezone component is lost. `padding` - An integer that specifies a percentage. For example, `50` specifies `50%`. When using `num=2` or `var=1`, padding increases the maximum precision, length, and scale determined by data analysis by the specified percentage. This allows future changes in the source data. The minimum and default value is `0` (no padding). The maximum value is `1000` (`10×`). `stats` - Outputs additional type-mapping data-analysis statistics as comments in the generated DDL file. Values are: `0` (default) - Additional statistics are suppressed. `1` - Additional statistics are included. Example 1: -typeMap 2,1 The values are `num=2`, `var=1`, and the rest are default. Example 2: -typeMap 0,1,0,0,0,50 The values are `num=0`, `var=1`, `rawlong=0`, `lob=0`, `tstz=0`, `padding=50%`, and `stats=default`.
`-compression level[,padding[,stats[,cfactor[,minrows]]]]`	Controls compression evaluation and clause generation. `level` - Controls the compression type and level. Values are: `0` (default) - Source tables are not analyzed for compression. `1` - Source tables are analyzed for compression using the maximum dictionary pointer size for every column. For qualifying tables, the resulting compression clauses are output in the generated DDL files. `2` - As in option 1, but adjusts the dictionary pointer size based on the actual number of distinct values in the data. `padding` - An integer that specifies a percentage. For example, `50` specifies `50%`. When using `level 1` or `level 2`, increases the estimated number of unique values by padding %. This allows future changes in the source data. The minimum and default value is `0` (no padding). The maximum value is `1000` (`10×`). `stats` - Outputs additional compression-related data-analysis statistics as comments in the generated DDL file. Values are: `0` (default) - Additional statistics are suppressed. `1` - Additional statistics are included. `cfactor` - The minimum compression factor required for a column or table to be considered for compression. Specify the value as the compressed size divided by the uncompressed size, expressed as a percentage. Valid values are greater than `0` and less than or equal to `100`. Smaller values indicate greater compression. The default is `70`. `minrows` - The minimum number of rows a source table must contain to be considered for compression. The minimum allowed value is `1` and the default is `1024`. Example: -compression 1,0,1,60 The values are `level=1`, `padding=0`, `stats=1`, `cfactor=60%`, and `minrows=default`.
`-inlineLimit n`	Controls `INLINE`/`NOT INLINE` storage for variable length types. `n` - `VARCHAR2` and `NVARCHAR2` columns with a maximum defined size greater than `n` bytes are stored `NOT INLINE`; otherwise, they are stored `INLINE`. The minimum allowed value is `0` (always store `NOT INLINE`), the maximum value is `32768`, and the default is `128`.
`-parallel dop`	Specifies the degree of parallelism used when querying the source database in the analysis phase. `dop` - Controls the degree of parallelism used for source-table analysis scans. Valid values range from `0` to `128`; `0` or `1` disables parallel scans. By default, the analysis uses the source database’s default degree of parallelism. This option does not affect parallel data loading.
`-dataLoadParallelism dlpspec`	Specifies values and defaults for parallel data loading into TimesTen. This is the advanced usage option. In most cases, the default settings provide good performance. You should only use this option if you have a good understanding of the way in which parallel data loading is implemented. For details, see the full `help` (`-help full`) or the documentation. Parallel data loading is not supported on Windows.
`-indexes unique \| nonunique \| bitmap \| nodups`	Controls how indexes on source tables are imported into TimesTen. `unique` - Controls the import of unique indexes. Values are: `0` - Unique indexes are not imported. `1` (default) - Unique indexes are imported. `nonunique` - Controls the import of non-unique indexes. Values are: `0` - Non-unique indexes are not imported. `1` (default) - Non-unique indexes are imported. `bitmap` - Controls the import of bitmap indexes. Values are: `0` - Bitmap indexes are not imported. `1` (default) - Bitmap indexes are imported as range indexes. `nodups` - Controls whether duplicate indexes on the same table are suppressed or imported. Values are: `0` - Duplicate indexes are imported. `1` (default) - Duplicate indexes are suppressed. TimesTen returns an error when you attempt to create duplicate indexes (indexes with the same columns and index type) on a table. Use this option to avoid creating duplicate indexes. Example: -indexes 0,1,1,1 The values are `unique=0`, `nonunique=1`, `bitmap=1`, and `nodups=1`.
`-constraints pkey[,fkey]`	Controls how constraints on source tables are imported into TimesTen. `pkey` - Controls the import of primary keys. Values are: `0` - Primary keys are not imported. `1` - Primary keys are imported as unique indexes. `2` (default) - Primary keys are imported as primary keys. `fkey` - Controls the import of foreign keys. Values are: `0` - Foreign keys are not imported. `1` - Foreign keys are imported as non-unique indexes. `2` (default) - Foreign keys are imported as foreign keys. If value `2` is specified for `fkey` then either `1` or `2` must be specified for `pkey`. Example: -constraints 1,1 The values are `pkey=1` and `fkey=1`.
`-outputDir dirpath`	Specifies the location for the output files. `dirpath` - The path of the directory where the output files are written. The default is the current directory.
`-prefix fnprefix`	Controls the naming of the output files. `fnprefix` - A prefix is used for the output filenames. It only contain alphanumeric characters, '`_`', '`-`' and, '`.`'.
`-target unix \| windows`	Specifies the target platform for the output files and scripts. Unix - Generate Unix/Linux compatible files and scripts. Windows - Generate Windows compatible files and scripts. The default is the platform on which the utility runs.
`-onBadType 0 \| 1 \| 2`	Specifies the action to take when the utility encounters an unsupported data type or finds an oversized `JSON` value during variable-length analysis. The utility validates `JSON` overflow only when you enable variable-length analysis by setting `var=1` in the `-typeMap` option. `0` - Processing stops with an error message. `1` - The current table is skipped but processing continues. A warning message is issued. `2` (default) - The current column is skipped but processing continues. A warning message is issued. Native Oracle `JSON` columns map directly to TimesTen `JSON` columns. The maximum TimesTen `JSON` value is `4,194,304` bytes (`4` MiB). Example: `JSON` maximum-length validation is performed when the `var` component of `-typeMap` is set to `1`. -typeMap 1,1 If analysis finds a `JSON` value larger than `4,194,304` bytes, `-onBadType` determines the action: `0` - Stops with an error before generating output files. `1` - Skips the entire table and continues. `2` - Skips only the `JSON` column and continues. If variable-length analysis is disabled, the utility does not detect oversized `JSON` values during the analysis phase, and may fail during data loading.
`-verbosity 0 \| 1`	Controls the volume and type of output messages. `0` - Outputs only error and warning messages. `1` (default) - Outputs informational and progress messages, in addition to errors and warnings.

For example, see Usage Example.