ttSchema

The ttSchema utility prints out the schema or selected objects of a database.

This utility can list the following schema objects that are found in SQL CREATE statements:

• Tables

• Indexes

• Cache group definitions

• Sequences

• Views

• Column definitions, including partition information

• PL/SQL program units

• Users and user information

Note:

ttSchema does not export passwords.

You can control the level of detail in the listing and the objects listed through options. The output is determined by the privileges of the utility user and represents a point-in-time snapshot of the state of a database rather than a history of how it arrived at its current state, perhaps through ALTER statements.

An entire database, including data, cannot be completely reconstructed from the output of ttSchema. The ttIsql utility can play back the output of ttSchema utility to rebuild the full schema of a database.

On UNIX and Linux systems, this utility is supported for TimesTen Data Manager DSNs. For TimesTen Client DSNs, use the utility ttSchemaCS.

Required Privilege

This utility requires only the privileges needed to perform DESCRIBE operations on database objects.

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

Usage in TimesTen Scaleout and TimesTen Classic

This utility is supported in both TimesTen Classic and TimesTen Scaleout.

Syntax

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

Options

ttSchema has the options:

Option	Description
-connStr connection_string	An ODBC connection string that specifies a database location, driver, and optionally other connection attribute settings.
-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 the Oracle database. The -c option prevents the INLINE and NOT INLINE keywords from being output.
DSN	Specifies an ODBC data source name of the database from which to get a schema.
-fixedTypes	Uses fully qualified data type names.
-h -help -?	Prints a usage message and exits.
-l	One per-line listing of objects in the database.
-list {all | tables | views | sequences | cachegroups | repschemes |synonyms | plsql |userinfo}[,...]	This argument is a comma-delimited (no space after comma) list of object types that the user wants to include in the output. Default is -list all. This information is accessible only to users with ADMIN privilege. No user information is displayed if a user without ADMIN privilege runs-list all. If a user without ADMIN privilege runs -list userinfo, an error message stating that this information is unavailable to unprivileged users is returned.
[owner.]object_name	Limits the scope of the output to a specified pattern of database object(s). If the user running the utility has the ADMIN privilege, ttSchema prints information about all objects; otherwise, ttSchema prints only the information of the objects owned by the user. When the list of objects at the end of the command line is empty, ttSchema prints out all the objects of each type in the database, restricted only by the user's privileges.
-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 before 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 | -[no]plsqlCreateOrReplace	If -plsqlCreate is specified, ttSchema emits CREATE PROCEDURE, CREATE PACKAGE or CREATE FUNCTION statements for PL/SQL program units. If -plsqlCreateOrReplace (default) is specified, ttSchema emits CREATE or REPLACE statements.
-st | -systemTables	By default, the ttSchema utility only prints non-system objects. When used by a user with ADMIN privilege, -st prints out all the system-created objects, including system tables. If -st is omitted, system-created objects are omitted. When used by a user without ADMIN privilege, -st prints out no results unless the user specifies a system-created object, for example: ttSchema -st -dsn <DSN> SYS.%
-V | -version	Prints the release number of ttSchema and exits.

Impact on User Passwords

Suppose internal users are stored in the database and you use ttSchema to export the data and metadata out of your database. In that case the password associated with each user is not exported. Instead, ttSchema writes a CREATE USER statement for each internal user in the database and assigns this user a random password. The ACCOUNT LOCK and PASSWORD EXPIRE clauses of the CREATE USER statement are also used by ttSchema to lock each user's account and expire each user's password. If the caller of ttSchema imports this CREATE USER SQL into a new database or an existing database then the user's account is locked and the password is expired. The instance administrator must unlock the user's account and provide a new password. Additionally, if the user attempts to connect to the database with the original password or with the password created by ttSchema's CREATE USER statement, the connection fails with an account-locked error.

In this example, the instance administrator creates two users and uses ttSchema to export the data and metadata from the database into the schema.sql file.

Command> CREATE USER sampleuser identified by sampleuser;

User created.

Command> CREATE USER sampleuser2 identified by sampleuser2;

User created.

Command> GRANT CONNECT TO sampleuser,sampleuser2;
Command> exit
Disconnecting...
Done.
% ttSchema -DSN mydatabase > schema.sql
% cat schema.sql  
-- Database is in Oracle type mode
create user SAMPLEUSER identified by 'DISABLED:m}Lc#MhP_Sh<X~M[p~z1' profile "DEFAULT" password expire account lock;

grant CREATE SESSION to SAMPLEUSER;

create user SAMPLEUSER2 identified by 'DISABLED:fA~d2[XM/kjNp#?MYJ4E' profile "DEFAULT" password expire account lock;

grant CREATE SESSION to SAMPLEUSER2;

The example drops the original users and then runs the schema.sql file in ttIsql. The connection fails when the user sampleuser attempts to connect to the database. The user's account is locked.

% ttIsql mydatabase

Copyright (c) 1996, 2021, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=access1";
Connection successful: DSN=access1;UID=instanceadmin;DataStore=/scratch/sampleuser/mydatabase;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;LogBufMB=1024;PermSize=500;TempSize=300;
(Default setting AutoCommit=1)
Command> DROP USER sampleuser;
User dropped.
Command> DROP USER sampleuser2;
User dropped.
Command> @schema
-- Database is in Oracle type mode
create user SAMPLEUSER identified by 'DISABLED:m}Lc#MhP_Sh<X~M[p~z1' profile "DEFAULT" password expire account lock;

User created.

grant CREATE SESSION to SAMPLEUSER;
create user SAMPLEUSER2 identified by 'DISABLED:fA~d2[XM/kjNp#?MYJ4E' profile "DEFAULT" password expire account lock;

User created.

grant CREATE SESSION to SAMPLEUSER2;

Command> connect adding "UID=sampleuser;PWD=sampleuser";
15179: the account is locked
The command failed.
none: Command> connect adding "UID=sampleuser;PWD=DISABLED:m}Lc#MhP_Sh<X~M[p~z1";
15179: the account is locked
The command failed.

The instance administrator uses the ALTER USER statement to unlock the sampleuser account. The user sampleuser can then connect to the database to run theschema.sql file.

none: Command> use mydatabase
mydatabase: Command> ALTER USER sampleuser identified by sampleuser account unlock;

User altered.

mydatabase: Command> connect adding "UID=sampleuser;PWD=sampleuser";
Connection successful: DSN=access1;UID=sampleuser;DataStore=/scratch/sampleuser/mydatabase;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;LogBufMB=1024;PermSize=500;TempSize=300;
(Default setting AutoCommit=1)

Examples

This example creates sampleuser3 objects.

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

CREATE SEQUENCE sampleuser3.custid MINVALUE 1 MAXVALUE 1000000;

CREATE TABLE sampleuser3.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 sampleuser3.customer (cust_num));

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

This example returns the schema for the orderdsn database. The user sampleuser has the ADMIN privilege.

% ttSchema "DSN=orderdsn;UID=sampleuser;PWD=sampleuser";
-- Database is in Oracle type mode
create table SAMPLEUSER3.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 SAMPLEUSER3.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 SAMPLEUSER3.CUSTOMER (CUST_NUM));
 
create sequence SAMPLEUSER3.CUSTID
    increment by 1
    minvalue 1
    maxvalue 1000000
    start with 1
    cache 20;
 
create materialized view SAMPLEUSER3.ORDER_SUMMARY as
    SELECT CUST.NAME "NAME", ORD.ORD_NUM "ORD_NUM", COUNT(*) "ORD_COUNT" 
    FROM SAMPLEUSER3.ORDERS ORD, SAMPLEUSER3.CUSTOMER CUST WHERE ORD.CUST_NUM = 
    CUST.CUST_NUM GROUP BY CUST.NAME, ORD.ORD_NUM ;

Listing Specific Objects

This example returns only the materialized views and sequences for the orderdsn database.

% ttSchema -list views,sequences orderdsn
-- Database is in Oracle type mode
create sequence SAMPLEUSER3.CUSTID
    increment by 1
    minvalue 1
    maxvalue 1000000
    start with 1
    cache 20;
 
create materialized view SAMPLEUSER3.ORDER_SUMMARY as
    SELECT CUST.NAME "NAME", ORD.ORD_NUM "ORD_NUM", COUNT(*) "ORD_COUNT" 
    FROM SAMPLEUSER3.ORDERS ORD, SAMPLEUSER3.CUSTOMER CUST WHERE ORD.CUST_NUM = 
    CUST.CUST_NUM GROUP BY CUST.NAME, ORD.ORD_NUM ;

Specifying an Object

The following example returns the schema information for the orders table in the orderdsn database.

% ttSchema orderdsn sampleuser3.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 SAMPLEUSER3.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 SAMPLEUSER3.CUSTOMER (CUST_NUM));

Specifying Fixed Data Types

The following example returns the schema information for the orderdsn database using fixed data type names.

% ttSchema -fixedTypes orderdsn
-- Database is in Oracle type mode
create table SAMPLEUSER3.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 SAMPLEUSER3.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 SAMPLEUSER3.CUSTOMER (CUST_NUM));
 
create sequence SAMPLEUSER3.CUSTID
    increment by 1
    minvalue 1
    maxvalue 1000000
    start with 1
    cache 20;
 
create materialized view SAMPLEUSER3.ORDER_SUMMARY as
    SELECT CUST.NAME "NAME", ORD.ORD_NUM "ORD_NUM", 
    COUNT(*) "ORD_COUNT" FROM SAMPLEUSER3.ORDERS ORD, SAMPLEUSER3.CUSTOMER CUST 
    WHERE ORD.CUST_NUM = CUST.CUST_NUM
    GROUP BY CUST.NAME, ORD.ORD_NUM ;

Notes

• The generated SQL does not produce a history of transformations through ALTER statements nor preserve table partitions. However, the output gives information on table partitions in the form of SQL comments. The ttSchema utility prints out the partition numbers for the columns that are not in the initial partition. The initial partition is 0. Partition 1, as printed by ttSchema, is secondary partition 1, not the initial partition. For more details on partitions, see Understanding Partitions when Using ALTER TABLE in Oracle TimesTen In-Memory Database SQL Reference.

• The connection attribute PassThrough with a nonzero value is not supported with this utility and returns an error.

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

• You should not run DDL SQL commands while running ttSchema to avoid lock contention issues for your application.