Chat now with support
Chat with Support

Welcome, erwin customers to Quest Support Portal click here for for frequently asked questions regarding servicing your supported assets.

Toad Data Modeler 7.1 - 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 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 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 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

Oracle 12c Release 1

Entity

  • Temporal Validity support for table
    Entity Properties dialog | Inner Script tab
    e.g. PERIOD FOR user_valid_time (start_time, end_time)
  • INDEXING ON/OFF option for partitioned tables, for both whole table and individual partitions. Table Properties tab is used for this option. It is also loaded there during reverse engineering.

  • Several COMPRESS parameters have been renamed.
  • Materialized Zonemap, Clustering, INMEMORY and [NO] ROW LEVEL LOCKING options supported (added in Oracle patch 12.1.0.2)
  • Read permission supported (Oracle patch 12.1.0.2).

Attribute

    • Visible/Invisible option
    • DEFAULT ON NULL expression - On Null checkbox
    • Data Types VARCHAR2(x), NVARCHAR2(x) and RAW(size) can acquire length up to 32767 bytes (formerly only 4000).

    • New Identity tab
    • Set NOKEEP in Attribute Properties | Identity
    • Virtual Column Edition tab where you can define EDITION for virtual column. See the comboboxes Unusable Before Edition and Unusable Beginning Edition.

    Index

    • New option INDEXING PARTIAL/FULL for indexes of partitioned tables - Indexing combobox.
    • For index and its partitions it is possible to set USABLE (besides UNUSABLE) in the text field.

    Keys

    Key Properties dialog | Using Index Properties tab | Index in UNUSABLE state checkbox where you can set USABLE (besides UNUSABLE) option.

    Materialized View

    • UNUSABLE EDITION option can be defined in two new comboboxes Unusable Before Edition and Unusable Beginning Edition on tab Edition.

    • INMEMORY and [NO] ROW LEVEL LOCKING options supported (added in Oracle patch 12.1.0.2)
    • Read permission supported (Oracle patch 12.1.0.2).

    Function, Procedure, Package, User Data Type, Synonym, View, Trigger

    • Edition tab where you can define a function as NONEDITIONABLE in checkbox Noneditionable.
    • White List (ACCESSIBLE BY) supported

    View

    • VISIBLE/INVISIBLE option can be defined for alias items (textually in Aliases box)

    • Select the Functions Executed with View Invoker's Rights (CURRENT_USER) checkbox to define the BEQUEATH CURRENT_USER/DEFINER option to specify whether functions referenced in the view are executed using the view invoker's rights or the view definer's rights.
    • STORE ALL VARRAYS AS LOBS/TABLES option for XMLType views are loaded to Object View or XMLType View Clause box on tab General.
    • Read permission supported (Oracle patch 12.1.0.2).
    • JSON functions now supported (Oracle patch 12.1.0.2).

    Sequence

    • KEEP/NOKEEP select the NOKEEP checkbox.

    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
    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating