ttSchema
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 |
---|---|
|
An ODBC connection string that specifies a database location, driver, and optionally other connection attribute settings. |
|
Compatibility mode. Limits the use of TimesTen-specific and release-specific keywords and extensions. This may be useful if the The |
|
Specifies an ODBC data source name of the database from which to get a schema. |
|
Uses fully qualified data type names. |
|
Prints a usage message and exits. |
|
One per-line listing of objects in the database. |
|
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 |
|
Limits the scope of the output to a specified pattern of database object(s). If the user running the utility has the When the list of objects at the end of the command line is empty, |
|
Controls whether If If |
|
If If |
|
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:
|
|
Prints the release number of |
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.
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. ThettSchema
utility prints out the partition numbers for the columns that are not in the initial partition. The initial partition is0
. Partition1
, as printed byttSchema
, is secondary partition1
, 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.