Chat now with support
Chat with Support

Toad Data Modeler 7.3 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Create New Project Reports Reverse Engineering Scripting and Customization About Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Amazon Redshift 1.0 IBM DB2 LUW 9.5 IBM DB2 LUW 9.7 IBM DB2 LUW 10.1 IBM DB2 LUW 10.5 IBM DB2 LUW 11.1 IBM DB2 z/OS 10 IBM DB2 z/OS 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2007/2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 Microsoft SQL Server 2019 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Oracle 19c PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 PostgreSQL 11 PostgreSQL 12 SQLite 3.7 Sybase ASE 15.5 Sybase ASE 15.7 SAP ASE 16.0 Sybase IQ 15.2 Sybase SQL Anywhere 11 SAP SQL Anywhere 17 Teradata 13 Vertica Database 8.0
Copyright Legal Notices

Reverse Engineering

Available Data Providers are:

  • Connection via ADO
  • Native Connection
  • Connection via TCP/IP

Connection via ADO:

Native Connection:

Connection via TCP/IP

Note:

  1. If the following error message occurs in the Log area: "Unable to reverse users, roles and permissions. You haven't assigned the SELECT_CATALOG_ROLE role!", it means you have not all the necessary rights to load users, roles and permissions.
  2. To load these items successfully, you need to have the  SELECT_CATALOG_ROLE role assigned or need to set a right for user to SELECT tables DBA_USERS, DBA_ROLES, DBA_ROLE_PRIVS, DBA_TAB_PRIVS.
  • Missing access to system table ALL_TABLES.
  • Missing privilege SELECT on system table ALL_TABLES.

Note: Connection via TCP/IP does not support Oracle native encryption.

Script Generation

Oracle 12c Release 2

  • Options are available in Options | Model | Physical Model | Oracle | Oracle 12c Release 2
  • Identifiers now can be 128 characters long (exception - Tablespaces)

Support for Collation

  • New property COLLATION can now be used for tables (column collation), views, materialized views, users, packages, procedures, functions and types
  • List of Collations displays objects

Tables

Table Properties and Physical Properties Tab

  • In Entity Properties | Physical Properties | Segment Creation selectNot Specifies, Deferred or Immediate

The following properties’ definitions need to be entered manually into the text field and are loaded automatically during Reverse Engineering:

  • FOR SERVICE property of INMEMORY tables - can be defined for the whole tables, for their partitions and subpartitions
  • Inmemory_column_clause for NO INMEMORY tables - you now can specify it to enable or disable specific table columns for the IM column store, and specify the data compression method for specific columns
  • READ ONLY or READ WRITE property - can be defined for the whole tables, for their partitions and subpartitions
  • New In-Memory Column Store policy
  • Compression policy - new option COLUMN STORE COMPRESS FOR QUERY ROW AFTER ilm_time_period OF NO MODIFICATION
  • New properties COMPRESSION and INDEXING can be used for subpartition templates
  • Automatic list partitions can now be used for partitions
  • Multi-Column List Partitioning can now be used for partitions and subpartitions
  • List of value lists can be specified for multi-column list partitions

External Tables

  • Now you can use NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY constraints for external tables
  • Can use ENCRYPT property, Virtual Column, range/list partitions and subpartitions
  • Do not have to use DEFAULT and LOCATION in their definitions
  • In Entity Properties | External Table Properties you can use one of the following drivers:
    • ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE

Misc.

  • New Encrypt Algorithms are used for columns

Indexes

Index Properties Tab

The following property definition needs to be entered manually into the text field and is loaded automatically during Reverse Engineering:

  • New Compress parameters COMPRESS ADVANCED and COMPRESS ADVANCED HIGH - can be defined for an index and its individual partitions (global and local)

Misc.

  • New option DEFERRED INVALIDATION is available. Default is IMMEDIATE INVALIDATION

  • Column COLLATION is recognized during Reverse Engineering

Keys

Using Index Properties Tab

The following property definition needs to be entered manually into the text field and is loaded automatically during Reverse Engineering:

  • New Compress parameters COMPRESS ADVANCED and COMPRESS ADVANCED HIGH

Materialized Views

  • New property FOR SERVICE - can be defined for the whole tables, for their partitions and subpartitions; the definition needs to be entered manually into the text field (Physical Properties Tab) and is loaded automatically during Reverse Engineering:
  • New Encrypt Algorithms - can be set in the Column Aliases field
  • New ON STATEMENT mode:
    • Refresh type needs to be FAST
    • The materialized view’s defining query needs to include the ROWID column of the fact table.
    • ON STATEMENT mode cannot be converted to a different mode using ALTER MATERIALIZED VIEW but instead DROP/CREATE is performed during Change Script Generation

  • New property ENABLE ON QUERY COMPUTATION - by default: DISABLE ON QUERY COMPUTATION
    • If enabled, the refresh mode COMMIT cannot be used

User

The following properties’ definitions need to be entered manually into the text field (as CREATE USER sentence) and are loaded automatically during Reverse Engineering:

  • New property HTTP DIGEST ENABLE
  • New property LOCAL TEMPORARY TABLESPACE

Reverse Engineering

Available Data Providers are:

  • Connection via ADO
  • Native Connection
  • Connection via TCP/IP

Connection via ADO:

Native Connection:

Connection via TCP/IP

Note:

  1. If the following error message occurs in the Log area: "Unable to reverse users, roles and permissions. You haven't assigned the SELECT_CATALOG_ROLE role!", it means you have not all the necessary rights to load users, roles and permissions.
  2. To load these items successfully, you need to have the  SELECT_CATALOG_ROLE role assigned or need to set a right for user to SELECT tables DBA_USERS, DBA_ROLES, DBA_ROLE_PRIVS, DBA_TAB_PRIVS.
  • Missing access to system table ALL_TABLES.
  • Missing privilege SELECT on system table ALL_TABLES.

Note: Connection via TCP/IP does not support Oracle native encryption.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating