ThatJeffSmith

Elevenses

Everyone likes to read into numbers and significant dates. Numerology (wiki) attributes a connection from the world of numbers to the physical world. Even though I don’t believe in such things, I can’t help but take advantage of the opportunity to focus on the number ‘11.’

It’s eleventh day of 2011.

Toad’s Development Manager is celebrating his 11th year on the development team.

Toad for Oracle v11 is currently in development (beta) and is due to be released in a few months.

Production instance of Oracle RDBMS are moving to 11gR2.

Neil Kodner (twitter | blog) asked if we (Quest) had a convenient listing of 11g exploitations in Toad.

11g Feature Exploitation in Toad for Oracle

Toad strives to offer production support for the current version of Oracle as new versions are released. The first version of Toad to support 11g was v9.5. However, the first version of Toad to exploit an 11g feature would be v9.6.

In v9.6, Toad introduced support for Flashback Data Archives (Oracle Docs).

You can create and manage these in Toad’s Schema Browser provided your license includes the DB Admin module. You can also assign an archive, once created, to a table in the CREATE and ALTER dialogs.

Since this is a blog and not meant to be a 30 page technical brief (sorry Neil), then I won’t go into detail for all of the 11g features exposed in Toad. Here are a few that you will definitely want to be aware of:

  • Case Sensitive Password Support – details below
  • 11g Instant Client and Full Client Support
  • 11g syntax support with the Formatter
  • Hierarchical Profiler (Oracle Docs)
  • ADDM/AWR Baseline Templates (requires Diagnostic pack!)
  • Automatic SQL Tuning Task Configuration management (requires Tuning pack!)
  • ASM 11g parameters: Allocation Unit Size, ASM Compatibility, RDBMS Compatibility, and Disk Repair Time
  • Plan Baselines (requires Xpert Edition of Toad) (blog | Oracle Docs)
  • Reference Partitioning (blog | Oracle Docs)
  • Virtual Table/Index Creation (blog | Oracle Docs)
  • Compound & CrossEdition Triggers (Oracle Docs)
  • Editioning Clause for Views (Oracle Docs)
  • Virtual Columns and Reference Partitioning in Toad and 11g


    More on Passwords
    When scripting out users in the Schema Browser, you need to make a decision on how to treat passwords. Since Oracle no longer puts the password in dba_users, you have to check this if you want Toad to try to find it in SYS.USER$.

    Which gives you this:

    The part before the semicolon is the 11g part. The part after the semicolon is the 10g part. You could use just the 10g part to create the user with the same PW on 10g (except it would be non-case-sensitive)

    CREATE USER COMP1
      IDENTIFIED BY VALUES 'S:0FE73326603173AAFBB4AB09A2C2A6DE884D505A1AFEB9226637FB72BD69;63F8D037E58B124F'
      DEFAULT TABLESPACE USERS
      TEMPORARY TABLESPACE TEMP
      PROFILE DEFAULT
      ACCOUNT UNLOCK;
      -- 1 Role for COMP1 
      GRANT DBA TO COMP1;
      ALTER USER COMP1 DEFAULT ROLE ALL;
      -- 1 System Privilege for COMP1 
      GRANT UNLIMITED TABLESPACE TO COMP1;
      -- 1 Object Privilege for COMP1 
        GRANT READ, WRITE ON DIRECTORY SYS.DATA_FILE_DIR TO COMP1;