Commands

Also see the list of ttIsql Set/Show Attributes.

Boolean commands can accept the values "ON" and "OFF" or "1" and "0".

ttIsql has the commands:

Command	Description
accept variable[NUM[BER]| CHAR |BINARY_FLOAT | BINARY_DOUBLE] [DEF[AULT]default_value] [PROMPT prompt_text | NOPR[OMPT]] [HIDE]	Gets input from a user and DEFINES the variable. If a type is specified then it validates for that type. The default (enclosed in quotes) is assigned if the user just presses enter. The prompt is displayed before waiting for input (or can be suppressed). The HIDE option stops the terminal from displaying the entered text (for passwords). The prompt is displayed before waiting for input, if specified without the HIDE option. The HIDE option stops the terminal from displaying the entered text.
allfunctions [[owner_name_pattern.] table_name_pattern]	Lists, in a single column, the names of all the PL/SQL functions that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists PL/SQL functions matching the pattern in the Oracle database. See the functions command.
allindexes [[owner_name_pattern.] table_name_pattern]	Describes the indexes that it finds on the tables that match the input pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the patterns default to "%". If passthrough is enabled, lists indexes on tables matching the pattern in the Oracle database. See the indexes command.
allpackages [[owner_name_pattern.] table_name_pattern]	Lists, in a single column, the names of all the PL/SQL packages that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the patterns default to "%". If passthrough is enabled, lists PL/SQL packages matching the pattern in the Oracle database. See the packages command.
allprocedures [[owner_name_pattern.] procedure_name_ pattern]	Lists, in a single column, the names of all the PL/SQL procedures that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists PL/SQL procedures matching the pattern in the Oracle database. See the procedures command.
allsequences [[owner_name_pattern.] table_name_pattern]]	Lists, in a single column, the names of all the sequences that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists sequences on tables matching the pattern in the Oracle database. See the sequences command.
allsynonyms [[schema_pattern.] object_pattern]]	Lists, in a single column, the names of all synonyms that match the given pattern. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists synonyms on tables matching the pattern in the Oracle database. See the synonyms command.
alltables [[owner_name_pattern.] table_name_pattern]]	Lists, in a single column, the names of all the tables that match the given pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists tables matching the pattern in the Oracle database. See the tables command.
allviews [[owner_name_pattern.] view_name_pattern]]	Lists, in a single column, the names of all the views that match the specified pattern selected from SYS.ALL_OBJECTS. When a pattern is missing, the pattern defaults to "%". If passthrough is enabled, lists views matching the pattern in the Oracle database.
builtins [builtin_name_ pattern]	Lists, in a single column, the names of all the TimesTen built-in procedures that match the given pattern. When the pattern is missing, the pattern defaults to "%". See the procedures command.
bye exit	Exits ttIsql.
cachegroups [[cache_group_owner_pattern. cache_group_name_pattern]]	Reports information on cache groups defined in the currently connected data source, including the state of any terminated databases that contain autorefresh cache groups. If the optional argument is not specified then information on all cache groups in the current data source is reported.
cachesqlget [ASYNCONOUS_WRITETOUGH | INCREMENTAL_AUTOREFRESH] [[cache_group_owner.]cache_group_name] {INSTALL | UNINSTALL} [filename]	Generates an Oracle SQL*Plus compatible script for the installation or uninstallation of Oracle database objects associated with a readonly cache group, a user managed cache group with incremental autorefresh or an AWT cache group. If INSTALL is specified, the Oracle SQL statement to install the Oracle database objects is generated. If UNINSTALL is specified, the Oracle SQL statement used to remove the Oracle objects is generated. If a cache group is not specified with UNINSTALL, a SQL statement to remove all Oracle database objects in the autorefresh user's account is generated. If the optional filename argument is included, the generated SQL statement is saved to the specified external file. If the external file exists, its contents are destroyed before writing to the file.
cd directory	Changes the current directory. This is the equivalent of the cd command in interactive shells. After changing to the directory directory, the define alias _CWD is be set to this directory. Subsequent commands that rely on relative paths will use this directory as the starting point. Examples of affected commands are spool, run, savehistory, host, and edit
clearhistory	Clears the history buffer. Also see history and savehistory.
clienttimeout [timeeout seconds]	Sets the client timeout value in seconds for the current connection. If no value is specified, displays the current value. See Choose SQL and PL/SQL Timeout Values in Oracle TimesTen In-Memory Database Operations Guide for information about the relationship between the client timeout, SQL timeout, and PL/SQL timeout.
close [connect_id.] command_id] closeall	Closes the prepared command identified by connection name connect_id and command ID command_id. If command_id is not specified, closes the most recent command. If closeall is selected, closes all currently open prepared commands. Use prepare to create the prepared command.
cmdcache [[by {sqlcmdid |querytext|owner}] query_subsstring]	Displays the contents of the TimesTen SQL command cache. Specify the sqlcmdid, querytext or owner column and query substring to search for a specific portion of a SQL query. If no column is specified, searches the querytext column. If passthrough is enabled, the command ID is not passed through to the Oracle database.
commit	Commits the current transaction (durably if Durability=1 for the connection).
commitdurable	Commits the current transaction durably.
compact	Compacts the database.
compare varA VarB	Compares the values of two variables and reports if they are different. The first difference is reported.
connect[connection_string |[[DSN][as]connid [adding] [connection_string | DSN][as connid]	Connects to the database with the specified ODBC connection_string. If no password is supplied in this format, ttIsql prompts for the password. If no user is given, ttIsql attempts to connect using the user name of the current user as indicated by the operating system. If as connid is specified, you can explicitly name the connection. The connid must be only alphanumeric characters, is case sensitive, must start with an alpha character and can only be a maximum of 30 characters in length. The name of connid is automatically supplied to the ConnectionName general connection attribute. If the connect fails, the current connection is set to a special reserved connection named "none," which is never connected to anything. When adding is specified, it refers to creating a new connection to the DSN specified by DSN or by the connection string.
createandloadfromoraquery [owner_name.]table_name [num_threads] query	Takes a table name, the number of threads for parallel load and an Oracle SELECT statement. Creates the table in TimesTen if the table does not exist. Then, loads the table with the query result from the Oracle database. If the command creates the table, the table column names and types are derived from the query result. Notes: The specified TimesTen table cannot be a system table, a synonym, a view, a materialized view or a detail table of a materialized view, a global temporary table or a cache group table. The query cannot have any parameter bindings. Any unsupported column types result in a warning being logged. The output issues a comment for the unsupported column data type. If you do not supply a value for num_threads, defaults to four threads. For details and usage information, see Loading Data from an Oracle Database into a TimesTen Table Without Cache in Oracle TimesTen In-Memory Database Operations Guide. You must rollback or commit after runing this operation. Also see the NOTES section in the description of the built-in procedure ttLoadFromOracle. Required Privileges: Requires INSERT privilege on the table specified. Also, requires the CREATE TABLE privilege if the table does not exist. The Oracle session user must have all required privileges to run the query on the Oracle database.
define name [= value]	Defines a string substitution alias. If no value is provided, ttIsql displays the current definition for the specified name. You must set define on to enable command substitution. See Set/Show Attributes.
describe [[owner_pattern.] name_pattern | procedure_name_pattern |sql_statement | [connect_id.]command_id |*]	List information on tables, synonyms, views, materialized views, sequences, cache groups, PL/SQL functions, PL/SQL procedures, PL/SQL packages and TimesTen built-in procedures in that order when the argument is [owner_pattern.]name_pattern. Otherwise lists the specific objects that match the given pattern. Describes the parameters and results columns when the argument is sql_statement. If passthrough is set to 3, lists information about the same types of objects in the Oracle database. If * is specified, reports the prepared statements for all connections. If the table or materialized view being described is in a TimesTen Scaleout database, this command reports the distribution scheme. When describing cache groups, reports information on cache groups defined in the currently connected data source, including the state of any terminated databases that contain autorefresh cache groups. If the command is describing a sequence in a TimesTen Scaleout database, displays the batch field. The command alias is desc. Use free to release the prepared command.
disconnect [all]	Disconnects from the database. If all is specified, disconnects and closes all connections. When disconnect finishes, the current connection is set to the reserved connection named "none."
dssize [k|m|g|t]	Prints size information in KB, MB, GB or TB. For TimesTen Scaleout, provides the size of the element. The default is MB. The output indicates the unit returned.
e: msg PROMPT msg	Echoes the specified messages, terminated by the end of the line. A semicolon is not required to end the line. Messages are not echoed if verbosity is set to 0.
edit [ file | !history_search_command ]	You can use the ttIsql edit command to edit a file or edit ttIsql commands in a text editor. The ttIsql edit command starts a text editor such as emacs, gedit, or vi. If TimesTen does not find an exact file match for the specified file parameter, it searches for file.sql. If neither file exists, ttIsql starts the editor with the file file. You can edit a SQL statement that is stored in the history list of the current ttIsql session. When calling the ttIsql edit command specify the ! character followed by the number of the command or a search string. If you run the ttIsql edit command with a history_search_command parameter, ttIsql runs the contents of the file after you exit the text editor. The contents of the file are run as a single ttIsql command. If you do not want to run the contents of the file, delete the contents of the file and save the file before you exit the editor. You can only use one parameter at a time. The history_search_command parameter is defined as the ! character followed by the number of the command or a search string. If you do not specify a ! character, the ttIsql edit command interprets the parameter as file. If you do not specify a parameter or specify !!, the last ttIsql command is edited. You can specify the default editor by defining the ttIsql _EDITOR define alias. The following example sets the default editor to vi: Command> DEFINE _EDITOR=vi If you do not define the _EDITOR define alias, ttIsql uses the editor specified by the VISUAL environment variable. If the _EDITOR define alias and the VISUAL environment variables are not set, ttIsql uses the editor specified by the EDITOR environment variable. When _EDITOR, VISUAL, and EDITOR are not set, vi is used for UNIX and Linux systems and notepad.exe is used for Windows. For more details, see Using the ttIsql edit Command in Oracle TimesTen In-Memory Database Operations Guide.
exec [connect_id.] command_id] | PLSQLSTMT	Runs the prepared command command_id o n connection connect_id or runs a PL/SQL statement. The connect_id optionally names a ttIsql connection and command_id is an integer from 1 to 255.If PLSQLSTMT is supplied, ttIsql prepends the statement with BEGIN and appends the statement with END, thus allowing the PL/SQL statement to run. If no argument is supplied, runs the most recent command. Use free to release the prepared command.
execandfetch [connect_id.]command_id]	Runs and fetches all results from prepared command command_id on connection connect_id. If command_id is not specified, runs and fetches all results from the most recent command. Use free to release the prepared command.
explain [plan for] {[ Connid.]ttisqlcmdid | sqlcmdid sqlcmdid | sqlstmt |!history}	Explains the plan for the specified SQL statement, including prepared ttIsql statements, specified in the ttisqlcmdid argument, or the sqlcmdid argument. A digit that is not qualified with the sqlcmdid argument, is interpreted as a ttIsql prepared statement ID. If passthrough is enabled, the command ID is not passed through to the Oracle database.
fetchall [connect_id.]command_id]	Fetches all results from prepared command command_id on connection connect_id. If command_id is not specified, fetches all results from the most recent command. The command must already have been run using exec. Use free to release the prepared command.
fetchnext num_rows [connect_id.]command_id]	Fetches up to num_rows rows from prepared command command_id on connection connect_id. If command_id is not specified, fetches num_rows rows from the most recent command. The command must already have been run using exec. Use free to release the prepared command.
fetchone [connect_id.]command_id]	Fetches one result from prepared command command_id on connection connect_id. If command_id is not specified, fetches one result from the most recent command. The command must already have been run using exec. Use free to release the prepared command.
free [[connect_name.]connect_id.] command_id]	Frees prepared command command_id on connection connect_id. If no command is specified, frees the most recent command. Use prepare to create the prepared command.
functions [object_name_pattern]	Lists, in a single column, the names of PL/SQL functions owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to %. If passthrough is enabled, lists PL/SQL functions matching the pattern in the Oracle database. See the allfunctions command.
grid stmt	Performs that specified statement on a grid database.
grid monitor [optional_monitor_column]	Formats the contents of the SYS.GV$MONITOR table for easy viewing. If the optional_monitor_column is specified, only that column is displayed. This command is not supported in TimesTen Classic.
help [command [command ...]| all | comments | attributes]	Prints brief or detailed help information for commands. If specific commands are given as arguments then detailed help for each command is printed. If you do not know the exact name of a command, try typing just a few characters that may be part of the command name. ttIsql searches and displays help for any commands that include the characters. If all is given as an argument then detailed help for all commands is printed. If comments is given as an argument then information on using ttIsql comments within scripts is printed. If attributes is given as an argument then information on the set/show attributes is printed. If no argument is given then brief help information for all commands is printed.
history [-all] [-h] [-r] [num_commands]	ttIsql implements a csh-like command history. Lists previously run commands. The num_commands parameter specifies the number of commands to list. If the num_commands parameter is omitted then the previous 10 commands are listed by default. The output of this command omits consecutive duplicate commands. Use the -all option to include the consecutive duplicate commands. Use the -h option to omit the command numbers. Use the -r parameter to list the commands in reverse order. The history list stores up to 100 of the most recently run commands. See the savehistory and clearhistorycommand.
host os_command	Runs an operating system command. The command is run in the same console as ttIsql. This command sets the environment variable TT_CONNSTR in the environment of the process it creates. The value of the variable is the connection string of the current connection. To see the exit status of the command, use the define command with _EXIT_STATUS.
if-then-else	The if-then-else command construct enables you to implement conditional branching logic in a ttIsql session. For more details, see Syntax for the IF-THEN-ELSE Command Construct.
indexes [table_name_pattern]	Describes the indexes that it finds on the tables owned by the current user that match the input pattern. When a name pattern is missing, the pattern defaults to %. If passthrough is enabled, lists indexes on tables matching the pattern in the Oracle database. See the allindexes command.
monitor [optional_monitor_column]	Formats the contents of the SYS.MONITOR table for easy viewing. If the optional_monitor_column is specified, only that column is displayed.
packages [object_name_pattern]	Lists, in a single column, the names of PL/SQL packages owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to %. If passthrough is enabled, lists PL/SQL packages matching the pattern in the Oracle database. See the allpackages command.
prepare [[connid.]command_id]SQL_Statement	Prepares the specified SQL statement. If the command_id argument is not specified the command_id is assigned automatically. The command_id argument can take a value between 0 and 255 inclusive. If connid is specified, switches to the given connection ID. The connid must be only alphanumeric characters and are case insensitive. Use free to release the prepared command.
print [variable]	Prints the value of the specified bind variable or all variables if no variable is specified. If the variable is a REF CURSOR, then the results are fetched and printed.
procedures [procedure_name_ pattern]	Lists, in a single column, the names of PL/SQL procedures owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to %. If passthrough is enabled, lists PL/SQL procedures matching the pattern in the Oracle database. See the builtins and allprocedures commands.
quit	Exits ttIsql.
remark msg	Specifies that the message on the line should be treated as a comment. When rem or remark is the first word on the line, ttIsql reads the line and ignores it.
repschemes [[scheme_owner_pattern.] scheme_name_pattern]	Reports information on replication schemes defined in the currently connected data source. This information describes all elements associated with the replication schemes. If the optional argument is not specified then information on all replication schemes defined in the current data source is reported.
retryconnect [0|1]	Disables(0) or enables(1) the wait for connection retry feature. If the connection retry feature is enabled then connection attempts to a data source that initially fail due to a temporary situation are retried until the connection attempt succeeds. For example, if data source recovery is in progress when attempting to connect, the connection retry feature causes the connect command to continue to attempt a connection until the recovery process is complete. If the optional argument is omitted then the connection retry feature is enabled by default.
rollback	Rolls back the current transaction. AutoCommit must be off. This command does not stop TimesTen Cache operations on the Oracle database, including passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating and dynamic loading.
rpad varname desiredlength paddingstring	The RPAD command acts like the SQL function RPAD()with some limitations: The desired length is in bytes, not characters. The padding string is not expanded for string literal escapes, such as unicode escapes. The padding string can contain partial unicode characters or full unicode characters and it may split the padding string in the middle of a multibyte character or surrogate pair. Only variables that are character based (CHAR, VARCHAR) can be padded with the RPAD command.
run filename [arguments]| start filename [arguments...]| @@ filename [arguments...]| @ filename [arguments...]	Reads and runs SQL commands from filename. The run command can be nested up to five levels. The @@ command is identical to the @ command only if the file is specified with an absolute path. When you specify @ with a relative path, the path is relative to the startup directory of ttIsql. When you specify @@, the path is relative to the currently running input file. Therefore @@ is useful when used in a script that must call other scripts. It does not matter what directory the invoker of ttIsql is in when the script is run. See Example Parameters of Command String Substitution for a description of arguments.
savehistory [-all [-h] [-a | -f] outputfile	Writes the history buffer to the specified outputfile. Consecutive duplicate commands are omitted. Use the -all option to include the consecutive duplicate commands. Use the -h option to omit the command numbers. Use -a to append to an existing output file. Use -f to force the overwriting of an existing output file. See the clearhistory and history commands.
sequences [sequence_name_pattern]	Lists, in a single column, the names of sequences owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to %. If passthrough is enabled, lists sequences on tables matching the pattern in the Oracle database. See the allsequences command.
set attribute [value]	Sets the specified set/show attribute to the specified value. If no value is specified, displays the current value of the specified attribute. For a description of accepted attributes, see Set/Show Attributes.
setjoinorder tblNames [...]	Specifies the join order for the optimizer. AutoCommit must be off.
setuseindex index_name,correlation_name, {0 | 1} [;...]	Sets the index hint for the query optimizer.
setvariable variable_name := value	Sets the value of a scalar bind variable or an element of an array bind variable. For example: setvariable myvar := 'TimesTen'; There must be a space on either side of the assignment operator (:=). For more information, see Declaring and Setting Bind Variables in Oracle TimesTen In-Memory Database Operations Guide.
show {all | attribute}	Displays the value for the specified set/show attribute or displays all the attributes. For a description of accepted attributes, see Set/Show Attributes.
showjoinorder {0 | 1}	Enables or disables the storing of join orders. 0 - Disables the storing of join orders 1 - Enables the storing of join orders. Call the ttoptshowjoinorder built-in procedure explicitly to display the join order after SELECT, UPDATE, DELETE or MERGE SQL statements.
sleep [n] [ms]	Suspends operation for n seconds or n milliseconds, if the unit ms is included. If n is not specified, then operation is suspended for 1 second. sleep; sleep 60; sleep 500 ms;
spool filename [option | OFF]	Writes a copy of the terminal output to the file filename. If you do not provide an extension to filename, the file name has the extension .lst. The available options include: CREATE - Creates a new file. APPEND - Appends output to an existing file. REPLACE (default) - Overwrites an existing file. When you specify the value OFF, the spooling behavior is terminated and the output file is closed. If you specify a spool command while one is running, the active spool is closed and a new files is opened.
sqlcolumns [owner_name_pattern.]table_name_pattern	Prints results of an ODBC call to SQLColumns.
sqlgetinfo infotype	Prints results of an ODBC call to SQLGetInfo.
sqlstatistics [[owner_name_pattern.]table_name_pattern]	Prints results of an ODBC call to SQLStatistics.
sqltables[[owner_name_pattern.]table_name_pattern]	Prints results of a call to SQLTables. The pattern is a string containing an underscore ( _ ) to match any single character or a percent sign (%) to match zero or more characters.
statsclear [[owner_name.]table_name]	Clears statistics for specified table (or all tables if no table is specified).
statsestimate [[owner_name.]table_name] {n rows | p percent}	Estimates statistics for specified table (or all tables if no table is specified). If you estimate statistics with an empty table list, statistics on system tables are updated also, if you have privileges to update the system tables.
statsupdate [[owner_name_pattern.] table_name_pattern]	Updates statistics for specified table (or all tables if no table is specified). If tblName is an empty string, statistics are estimated for all the current user's tables in the database.
synonyms [[schema_pattern.] object_pattern]]	Lists, in a single column, the names of synonyms owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to %. If passthrough to an Oracle database is enabled, lists synonyms on tables matching the pattern in the Oracle database. See the allsynonyms command.
tables [table_name_pattern]]	Lists, in a single column, the names of tables owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to %. If passthrough to an Oracle database is enabled, lists tables matching the pattern in the Oracle database. See the alltables command.
tablesize [[owner_name_pattern.] table_name_pattern]]	For each table that matches the pattern, lists the contents of the ALL_TAB_SIZES view. See the ttComputeTabSizes built-in procedure.
undefine name	Undefines a string substitution alias.
unsetjoinorder	Clears join order advice to optimizer. AutoCommit must be off.
unsetuseindex	Clears the index hint for the query optimizer.
use [conn_id]	Displays the list of current connections and their IDs. If connid is specified, switches to the given connection ID. To use the name of the first connection, you can specify con0 for the conn_id, rather than specifying the full original connection name. You cannot explicitly name a connection con0. If the first connection is disconnected, con0 refers to the connection none. If use fails to locate the connection id, the current connection is set to the reserved connection named "none." See the connect command.
variable [variable_name [data_type] [:= value]] The syntax for binding multiple values to an array using the variable command is as follows: variable array_name '[' array_size ']' data_type(n):= '[' value1, ... valuex ']'	Declares a bind variable that can be referenced in a statement or displays the definition of the variable if the type is missing. Type can be one of the following: (n), NUMBER, CHAR(n), NCHAR(n), VARCHAR2(n), NVARCHAR2(n), BLOB, CLOB, NCLOB, or REFCURSOR. If only (n) is supplied, it is assumed to be VARCHAR2 (n). Assigns a value to a single variable or multiple values if the data type is an array. You can assign a value later with the setvariable command. For more information, see Declaring and Setting Bind Variables in Oracle TimesTen In-Memory Database Operations Guide.
version	Reports version information.
views [table_name_pattern]	Lists, in a single column, the names of views owned by the current user that match the given pattern. When a name pattern is missing, the pattern defaults to "%". If passthrough to an Oracle database is enabled, lists views matching the pattern in the Oracle database. See the allviews command.
waitfor expected_result timeoutseconds sqlstatement	Runs the given statement once a second until the query returns the expected result or a timeout occurs. The query must have only one column and must return exactly one row. Any errors in the query terminate the loop.
waitforresult expected_result timeoutseconds searchrow searchcol sqlstatement	Similar to the waitfor command, except that the result can have 1 or more columns. Also, the result can return 0 rows. Runs the given statement once a second until the query returns the expected result or a timeout occurs. The searchrow and searchcol arguments indicate the ordinal position (1..N) of which row or column should be considered. Use '*' in searchrow or searchcol to indicate any row or column of the result set could have the expected value. See the waitfor command.
whenever sqlerror	Provide direction on how to handle errors when in ttIsql. For more details, see Syntax for the WHENEVER SQLERROR Command.
xlabookmarkdelete id	Deletes a persistent XLA bookmark. If a bookmark to delete is not specified then the status of all current XLA bookmarks is reported. Also see ttXlaDeleteBookmark in Oracle TimesTen In-Memory Database C Developer's Guide. Requires ADMIN privilege or object ownership.