ttSchema

Description

Prints out the schema, or selected objects, of a data store. The utility can list the following schema objects that are found in SQL CREATE statements:

Tables
Indexes
Cache group definitions
Sequences
Views
Materialized view logs
Column definitions, including partition information
PL/SQL program units

The level of detail in the listing and the objects listed are controlled by options. The output represents a point in time snapshot of the state of a data store rather than a history of how the data store came to arrive at its current state, perhaps through ALTER statements. An entire data store, including data, cannot be completely reconstructed from the output of ttSchema. The output of ttSchema can be played back by the ttIsql utility in order to rebuild the full schema of a data store.

Required privilege

This utility requires no privileges beyond those needed to perform describe operations on data store objects.

This utility prints information only about the objects owned by the user executing the utility, and those objects for which the owner has SELECT privileges. If the owner executing the utility has ADMIN privilege, ttSchema prints information about all objects.

Syntax

ttSchema {-h | -help | -?}
ttSchema {-V | -version}
ttSchema [-l] [-c] [-fixedTypes] [-st | -systemTables]
         [ -list {all | tables | views | sequences |
         cachegroups | repschemes | plsql} [,...] ] 
         [-plsqlAttrs | -noplsqlAttrs]
         [-plsqlCreate |-plssqlCreateOrReplace]
         {-connStr connection_string | DSN }
         [[owner.]object_name][...]

Options

ttSchema has the options:

Option	Description
`-connStr` `connection_string`	An ODBC connection string containing the name of the data store, the server name and DSN (if necessary) and any relevant connection attributes.
`-c`	Compatibility mode. Limits the use of TimesTen-specific and release-specific keywords and extensions. This may be useful if the ttSchema output is being used as input to an older TimesTen release, or to some other database system, such as Oracle. For this release, `-c` prevents the INLINE and NOT INLINE keywords from being output.
`DSN`	Specifies an ODBC data source name of the data store from which to get a schema.
`-fixedTypes`	Uses fully qualified data type names regardless of the current TypeMode value.
`-h` `-help -?`	Prints a usage message and exits.
`-l`	One per-line listing of objects in the data store.
`-list {all \| tables \| views \| sequences \| cachegroups \| repschemes \| plsql}[,...]`	A comma-separated list of objects to generate. Lists only those types of objects specified. Default is `-list all.` `-list views` also displays information about materialized view logs.
`[owner.]object_name`	Limits the scope of the output to specified data store object(s).
`-plsqlAttrs \|-noplsqlAttrs`	Controls whether ttSchema emits ALTER SESSION statements with CREATE statements for PL/SQL program units. If `-plsqlAttrs` is specified, ttSchema emits ALTER SESSION statements to set these attributes prior to emitting a CREATE statement. This output from ttSchema can be fed back into ttIsql (or sqlplus) to create the same procedures, with the same compiler options as were specified in the original database. (default) If `-noplsqlAttrs` is specified, only the CREATE statement is generated.
`-plsqlCreate \| -plsqlCreateOrReplace`	If `-plsqlCreate` is specified, ttSchema emits CREATE PROCEDURE, CREATE PACKAGE or CREATE FUNCTION statements for PL/SQL program units. If `-plsqlCreateOrReplace` is specified, ttSchema emits CREATE OR REPLACE statements. (default)
`-st \| -systemTables`	Include system tables. System tables are omitted by default.
`-V \| -version`	Prints the release number of ttSchema and exits.

Examples

Objects in the orderdsn data store are created with these SQL statements:

CREATE TABLE ttuser.customer (
cust_num            INTEGER NOT NULL PRIMARY KEY,
  region            CHAR(2) NOT NULL,
  name              VARCHAR2(80), 
  address           VARCHAR2(255) NOT NULL);

CREATE SEQUENCE ttuser.custid MINVALUE 1 MAXVALUE 1000000;

CREATE TABLE ttuser.orders (
  ord_num INTEGER NOT NULL PRIMARY KEY,
  cust_num INTEGER NOT NULL,
  when_placed  TIMESTAMP NOT NULL,
  when_shipped TIMESTAMP,
  FOREIGN KEY(cust_num) REFERENCES ttuser.customer (cust_num));

CREATE MATERIALIZED VIEW ttuser.order_summary AS
  SELECT cust.name, ord.ord_num, count(*) ord_count
  FROM ttuser.orders ord, ttuser.customer cust
  WHERE ord.cust_num = cust.cust_num
    GROUP BY cust.name, ord.ord_num;

Example 3-4 ttSchema for the data store

Return the schema for the orderdsn data store.

% ttSchema orderdsn
-- Database is in Oracle type mode
create table TTUSER.CUSTOMER (
        CUST_NUM NUMBER(38) NOT NULL,
        REGION   CHAR(2 BYTE) NOT NULL,
        "NAME"   VARCHAR2(80 BYTE) INLINE NOT NULL,
        ADDRESS  VARCHAR2(255 BYTE) NOT INLINE NOT NULL,
    primary key (CUST_NUM));
 
create table TTUSER.ORDERS (
        ORD_NUM      NUMBER(38) NOT NULL,
        CUST_NUM     NUMBER(38) NOT NULL,
        WHEN_PLACED  TIMESTAMP(6) NOT NULL,
        WHEN_SHIPPED TIMESTAMP(6),
    primary key (ORD_NUM),
    foreign key (CUST_NUM) references TTUSER.CUSTOMER (CUST_NUM));
 
create sequence TTUSER.CUSTID
    increment by 1
    minvalue 1
    maxvalue 1000000
    start with 1
    cache 20;
 
create materialized view TTUSER.ORDER_SUMMERY as
    SELECT CUST.NAME "NAME", ORD.ORD_NUM "ORD_NUM", COUNT(*) "ORD_COUNT" FROM TTUSER.ORDERS ORD, TTUSER.CUSTOMER CUST WHERE ORD.CUST_NUM = CUST.CUST_NUM GROUP BY CUST.NAME, ORD.ORD_NUM ;

Example 3-5 Listing sequences

Return the sequences for the orderdsn data store.

% ttSchema -list sequences orderdsn
-- Database is in Oracle type modecreate sequence TTUSER.CUSTID    increment by 1    minvalue 1    maxvalue 1000000    start with 1    cache 20;

Example 3-6 Specifying an object

Return the schema information for the orders table in the orderdsn data store.

% ttSchema orderdsn ttuser.orders
-- Database is in Oracle type mode
Warning: tables may not be printed in an order that can satisfy foreign key reference constraints
create table TTUSER.ORDERS (
        ORD_NUM      NUMBER(38) NOT NULL,
        CUST_NUM     NUMBER(38) NOT NULL,
        WHEN_PLACED  TIMESTAMP(6) NOT NULL,
        WHEN_SHIPPED TIMESTAMP(6),
    primary key (ORD_NUM),
    foreign key (CUST_NUM) references TTUSER.CUSTOMER (CUST_NUM));

Example 3-7 Specifying fixed data types

Return the schema information for the orderdsn data store, using fixed data type names.

% ttSchema -fixedTypes orderdsn
-- Database is in Oracle type mode
create table TTUSER.CUSTOMER (
        CUST_NUM NUMBER(38) NOT NULL,
        REGION   ORA_CHAR(2 BYTE) NOT NULL,
        "NAME"   ORA_VARCHAR2(80 BYTE) INLINE NOT NULL,
        ADDRESS  ORA_VARCHAR2(255 BYTE) NOT INLINE NOT NULL,
    primary key (CUST_NUM));
 
create table TTUSER.ORDERS (
        ORD_NUM      NUMBER(38) NOT NULL,
        CUST_NUM     NUMBER(38) NOT NULL,
        WHEN_PLACED  ORA_TIMESTAMP(6) NOT NULL,
        WHEN_SHIPPED ORA_TIMESTAMP(6),
    primary key (ORD_NUM),
    foreign key (CUST_NUM) references TTUSER.CUSTOMER (CUST_NUM));
 
create sequence TTUSER.CUSTID
    increment by 1
    minvalue 1
    maxvalue 1000000
    start with 1
    cache 20;
 
create materialized view TTUSER.ORDER_SUMMERY as
    SELECT CUST.NAME "NAME", ORD.ORD_NUM "ORD_NUM", COUNT(*) "ORD_COUNT" FROM TTUSER.ORDERS ORD, TTUSER.CUSTOMER CUST WHERE ORD.CUST_NUM = CUST.CUST_NUM GROUP BY CUST.NAME, ORD.ORD_NUM ;

Notes

The SQL generated does not produce a history of transformations through ALTER statements, nor does it preserve table partitions, although the output gives information on table partitions in the form of SQL comments.

Output is not guaranteed to be compatible with DDL recognized by previous releases of TimesTen.