Get to Know 8.5

This release of Toad extends the 10g support introduced in the previous Toad Release. Version 8.0 offered support for Oracle 10g database and Standard client (Toad 8.0 has Instant client from the Oracle installer and does not support the stand-alone Instant Client), a new Recycle Bin tab in the Schema Browser along with browser filter options to exclude recycle bin objects from lists, Global hash partitioned indexes, and a drop table PURGE option. Version 8.5 now also offers support for the following

• Data Pump - Oracle's Next Generation Import/Export Utility
• Stand-Alone Instant Client
• Segment Advisor
• Undo Advisor
• Index monitoring (implemented to support 9i also)
• 10g Scheduler Tabs added for Schema Browser
• More 10g datatype and SQL Statement Support

The Oracle data pump is an import/export utility added in Oracle 10g. It is significantly faster and more efficient at loading large volumes of data than the standard import/export utilities. To support the Data Pump, Toad now offers 3 new screens to users with the Toad for Oracle Professional or DBA Modules:

• Data Pump Import Wizard
• Data Pump Export Wizard
• Data Pump Job Manager

The Oracle client detection code has been updated. TOAD should now support all homes including the 10g instant clients. In addition, the Login Window has also undergone some changes.

• 'SQL*Net compatible Net8' and 'Force SQL*Net (ignore Net8)' options have been removed. They were previously available as workarounds to known issues. The known issues have been resolved :)
• Database dropdown supports multiple aliases for tnsnames.ora entries.
• The default home that is selected by default in the dropdown at Toad startup is determined in the following order... 1) If an oracle client DLL is specified as a command line parameter when launching TOAD (ex. TOAD.exe OCIDLL=C:\Oracle\Ora92\bin\oci.dll) See Toad help regarding this feature. 2) The Toad Default Home 3) The Oracle default home as determined by Oracle's Home Selector.
  
• Oracle Variable (LOCAL) is now supported. If LOCAL and ORACLE_SID are set for a home, LOCAL is used first.

Available with the DBA Module, the Segment Advisor takes advantage of Oracle 10g's new ability to shrink a segment within a tablespace. Tables, indexes, and partitions can be analyzed to determine whether they would be likely candidates for shrinking. Read the Oracle White Paper.

With the Segment Advisor, you first select the objects you want analyzed. You can filter by Owner, Object Type, or Tablespace.



Toad compiles the objects to be analyzed then submits a script to Oracle. Once the results are ready, you can view your findings. In this case, one table has been found to be a likely candidate for shrinking. Toad even creates a script to implement the recommendations.

The Undo Advisor provides advice and helps to automate the establishment of the database undo environment. The advisor can inform you about the health of the current undo configuration, either overall or within a given time range. From this baseline, you can then:

• Alter the Undo tablespace
• Switch to another Undo tablespace
• Adjust the database's retention time
• Create projections of required undo space given a retention time that you define

We can see in this graphic that our Undo Space has more than enough space.

Oracle introduced an Index Monitoring service for the 9i release. Toad now has an interface to this feature for 9i and 10g. This feature is available in the DBA Module. The user can select indexes for monitoring. The user can then determine if and when a query used said indexes to resolve the query results. From this screen, you can also see the DDL to generate your index or even drop said index if it is found to be unnecessary.

 10g Scheduler Tabs added for Schema Browser

Toad has added full support for the 10g Scheduler. To create a job class, define a program, create a schedule class, or check on the run logs of your scheduled jobs, you can open the Schema Browser to the appropriate tab and get going. Here is what Toad 8.5's DBA Module offers:

• Scheduler Job Classes
• Scheduler Jobs
• Scheduler Schedules
• Scheduler Window Groups
• Scheduler Windows

• Rename Tablespace - Tablespaces can be renamed by Right-clicking in the schema browser.
• Default Tablespace - The schema browser indicates default temporary and default permanent tablespaces, and they can be changed by right-clicking.
• Tablespace Groups - Tablespace groups are supported in the create/alter tablespace windows, and in the create/alter user windows.
• Online Segment Shrink - Supported in the following screens: Alter table, Alter snapshot, Alter snapshot log, Alter index
• Recycle Bin - New schema browser tab with full support for recycle bin operations including flashback table and purging of recycle bin.
• New Data Types DOUBLE, FLOAT, BINARY_DOUBLE, BINARY_FLOAT - Supported in data grids and create/alter screens.
• Big File Tablespaces - Supported in create/alter tablespace screen.
• Global Partitioned Indexes - Hash Partitioning
• New Row Timestamp pseudo-column - Supported in data grids

Oracle RAC Support

Toad has added support for ORACLE RAC in 8 different screens. The first thing an Oracle RAC user will notice is that if the active connection is a RAC session, the caption displays TOAD: RAC[n] where n is the session id. This is so if the connection bar isn't open the user can still easily determine that their active connection is a RAC connection. More importantly, Toad will know to query the GV$ tables, sum stats across all the RAC instances, and provide information for all instances across your RAC database. Here are the highlights:

• Connection Bar - Visual indicator that it's a RAC connection, added [n] after connection string, where n is RAC instance id
  
• Session Browser - If connection is to RAC database
  
  • RHM group by - add option for INST_ID
  • RHM visible columns - add option for INST_ID
  • top/left section of screen - select from GV$ tables and add display of column for INST_ID
  • bottom/right section of screen - select from GV$ tables and add INST_ID to WHERE clause join conditions
  • for kill session, start trace, and stop trace -
    • if INST_ID = connection_object.current_instance then process normally
    • else, do the following - create a connection as current_user/current_password@different_instance,process the command, drop the connection
• Oracle Parameters -box option to show instances in one grid versus tabbed grids (default is unchecked)
• SGA Trace - select from GV$ tables, FLUSH SGA will create connection to desired instance if necessary
• Database Browser - select from GV$ tables
• Top Session - select from GV$ tables and add display of column for INST_ID
• DB Monitor - select from GV$ tables
• DB Probe - select from GV$ tables

Redesigned SQL Editor

The SQL Editor has undergone more changes for version 8.5 than ever before. You can now save layouts via the Desktop Feature, run your scripts thru our new Script Engine, experience true Multi-Threading support when running queries, and much, much more.

The SQL Editor has been merged with the Script Debugger Screen (available with the Professional Edition). Since the Script Editor and the Script Debugger had common keyboard shortcuts, i.e.

• F5 in the Script Editor (SE) would "Execute as Script" and
• F5 in the Script Debugger would set a breakpoint,

With the Script Debugger functionality brought over, the Script Editor becomes MUCH more powerful. For example, you can have multiple editor tabs (just like in the Procedure Editor) - each with its own Results Sets in the lower panel of the editor. You can also run multiple statements and get multiple grids back using the new execute button .

Click here to see a diagram of our editor with 2 tabs opened. The first just has 4 SQL queries in it. Our new code navigator is able to parse the contents of the editor. Doing a Mouse Over on a navigation item will popup a hint for the statement. Clicking on the object will navigate you to said statement in the editor. When this statement is executed as a script, we will have 2 sets of Script Output: 1) Normal Text Script Output and 2) Grid Output - 1 Grid for Each SQL Statement.

The Output panels have grown almost exponentially; you will especially notice a difference if you are upgrading from version 7.6 or lower. With all the new tabs (Script Debugger, Query Viewer, CodeXpert), you will probably find it useful to change your Visual Style in options (View -> Options -> Toolbars/Menus -> Visual Style) to 'Enhanced'. Click here to see a diagram of the data panel.

The CodeXpert is a utility that analyzes PL/SQL and SQL against a set of rules for best practices. These rules are stored in a rule set and can be either user-defined or pre-defined. The CodeXpert window opens as a tab within the Output Frame. The window consists of the CodeXpert toolbars, the Results tab, the Summary Report tab, the CRUD Matrix tab (new for this release), and the Metrics Report tab (used to be contained within the summary report). The Code Xpert was previously only available in the Procedure Editor. It is now also in the SQL Editor. CodeXpert is available to users of Toad Professional. In addition, TOAD Xpert users will have an additional checkbox to scan the SQL code for inefficient statements that are either problematic or complex - and which should be reviewed further in the SQL Tuning interface.

Since the Script Debugger has been merged into the SQL Editor, its output panel has been added to the SQL Editor output Panel area. There have also been a few enhancements over the previous version of the Debugger. You can now Trace Into SQL*Plus scripts that are called from your main script. As such, we have new Script Debugger Sub-Panels to show where you have Breakpoints and your place in the Call Stack. Click here to see a diagram.

Query Viewer Tab

The Query Viewer displays currently running background queries, the database where they are running, their current execution time and the SQL. From this window you can cancel them, or display the SQL Editor page where they are running and view them in detail. This output panel tab will ONLY show the queries that have run for the active editor tab. Toad has a main Query Viewer window (under the View menu) which will show you ALL the user queries ran by Toad.

Toad users will also notice right away the abundance of changes to their SE toolbars and the addition of new result panel tabs. Click here to see a diagram.

If your editor toolbar configuration appears differently, or if you are missing several toolbars, R-Click on the Toolbar. You will also notice that the 3 SE Execute Statement Buttons are not intact. The Execute as Script button has been removed from the main SE toolbar. While this may appear to be a minor change on the surface, the reality is that Toad's Scripting Engine has been completely replaced. The new engine is much closer to providing 100% SQL*Plus support.

The Script Debugger Toolbar has all script execution and debugging buttons on it. Script Debugging is only available if you have the Professional/Xpert/Suite editions of Toad, but Execute as Script will still be available.

A popular feature from EZSQL has been added to the SE for version 8.5. The Current Schema toolbar, which has a dropdown box listing schemas on that connection. It defaults to the Schema that you are logged in as. If you change that schema, an ALTER SESSION SET current_schema command is issued prior to execution and again after execution to restore the current_schema to that you are logged in as. This is only available if you have the ALTER SESSION privilege. This is currently not available for Execute as Script.

The SQL Recall Window is now a dockable form on your Toad desktop. As such it can be pinned or hidden, depending on your preferences. This window now contains ALL of your saved SQL statements that were previously handled separately:

• Previously Executed SQL
• Personal SQL
• Named SQL

The SQL Recall Window has its own toolbar on the SQL Editor Desktop. The SQL Recall window can be accessed directly, or named SQL's can be automatically sent to your editor.

You can see right away that the window is MUCH more powerful than its 3 separate predecessors. You can organize the SQLs by type or connection. You can filter by keyword or by db connection or type. Saved SQL's can be named by typing their name into the panel directly (assuming you have the 'Editable' button toggled.) You can send your SQL to the clipboard, file, or your editor. All statements (to a maximum set in the SQL Options) are saved between sessions of Toad in the file Toad\temps\savedsql.xml. To access these SQL's directly: F8 - open the recall window Use the named sql dropdown from the toolbar or + N

Citrix Support

Citrix support is enabled with the appropriate installation option - "Citrix/Terminal Server" on the "Select Installation Type" dialog. Toad requires write access to the server registry during installation and read access during run-time to achieve Citrix support. .

Enabling Citrix support of Toad on a machine will cause all copies of Toad (versions 8.5 and higher) to run with the Citrix support. In other words, you will ill not be able to use Toad in a standalone environment on the Citrix server.

Toad is installed on the Citrix server, and individual user settings are maintained on each client machine. Individual user settings and properties files are maintained in the user's settings file: C:\Documents and Settings\user_name\Application Data\Quest Software\Toad

Secure Shell Support

Export File Browser

The Export File Browser is a new window for 8.5 for users with the DBA or Professional modules. It can be found under the DBA menu, under the "Data Export and Import" submenu.

This window lets you browse Oracle Export files. You can see what objects are in them, them, get DDL for one or multiple objects (or for a whole schema). You can also look at the data of any table (or partition of a table) in a grid, and use Toad's grid...save as, or grid...print functions.

Using Toad's Export File Browser, you can browse easily through export files and view file information contained within, such as:

Schemas DDL Data Code Functions Object type bodies Packages Package bodies Procedures Triggers Tuning and Configuration Refresh Groups

Just-In-Time Debugging

When performing normal debugging, Toad does two things – in the first Oracle session it starts an execution of the procedure it is about to debug, and in the second session it traps the first session into a debugger. Just in Time Debugging allows the user to debug PL/SQL code that is written and run from any client-server application including Visual Basic, Delphi, PowerBuilder, Developer/2000, etc. The external application does not need to exist on the same machine.

This feature is extremely useful when the client-server application calls a stored program with complex parameters, such as cursors, that are not easily simulated from Toad. Rather than trying to simulate the complex environment within Toad, you can simply connect to the external application and then debug the code in its native environment.

Anonymous Block

-- run this in a SQL*Plus session
-- first block gets the debug session ready
DECLARE
  id VARCHAR2(255);
BEGIN
  id := DBMS_DEBUG.INITIALIZE('TOAD_EXAMPLE');
  DBMS_DEBUG.DEBUG_ON;
END;
/

--this fires off your stored pl/sql object so it can be debugged.
DECLARE
    l_cursor case3_pkg.t_rc;
BEGIN
 OPEN l_cursor FOR SELECT * FROM scott.emp WHERE empno = 7369;
 case3_pkg.receive_cursor(l_cursor);
 case3_pkg.create_cursor(l_cursor);
 case3_pkg.receive_cursor(l_cursor);
 DBMS_DEBUG.DEBUG_OFF;
END;
/

Code

CREATE OR REPLACE PACKAGE case3_pkg
AS
   TYPE t_rc IS REF CURSOR;
   PROCEDURE create_cursor( p_cursor IN OUT t_rc );
   PROCEDURE receive_cursor( p_cursor IN t_rc );
END;
/

--make sure you have a SCOTT.EMP env setup
CREATE OR REPLACE PACKAGE BODY case3_pkg
AS
 PROCEDURE create_cursor( p_cursor IN OUT t_rc )
 IS
 BEGIN
-- make sure to set a BREAKPOINT here
  OPEN p_cursor FOR
   SELECT *
   FROM scott.emp
   WHERE empno = 7499;
 END;

PROCEDURE receive_cursor( p_cursor IN t_rc )
IS
 l_rec scott.emp%ROWTYPE;
 BEGIN
  FETCH p_cursor INTO l_rec;
  DBMS_OUTPUT.PUT_LINE( 'row = ' || l_rec.empno );
  CLOSE p_cursor;
 END;
END;
/

To initialize Debug mode, the external application must execute three commands:

• ALTER SESSION SET PLSQL_DEBUG=TRUE
• id := dbms_debug.initialize('TOAD')
• dbms_debug.debug_on;

Attach External Session is found under Toad's Debug Menu

To debug your Package Body code that gets executed from a calling program, first compile the Package with DEBUG info. Then set a breakpoint within the Code. Execute the Anonymous block from a separate program/session, then Attach the External Session from the Procedure Editor.

Integrated Windows Task Scheduler Interface

Schedule any DDL task with Toad's new Task Scheduler Interface! Have an index that needs altered? Go thru the Alter Index GUI and hit the 'Schedule' Button.' Need to create a table but aren't able to run the command during Production hours? Schedule it to run after you leave work!

Setup the desired Object Changes



Now hit the Schedule button!

Under the File Menu, choose 'Task Scheduler'. You can see which jobs you have scheduled via Toad, when they've ran, what they are doing, cancel, re-run a job, and more!

Toad for Oracle uses Quest ScriptRunner to execute your scheduled DDL Statements. Toad can also schedule DataPump runs, Segment shrinks, and much more. Look for the 'Schedule' button on your favorite screens.

Auditing

The United States Senate approved legislation sponsored by Banking Committee Chairman Paul Sarbaines to tighten regulation of independent auditors and make company officers more accountable for their conduct. This requirement has been put on companies to comply with legislation and in fact some companies cannot purchase software that does not provide these auditing capabilities.

Oracle has had auditing capabilities for several releases. Toad now has an interface to Oracle’s auditing administration and audit trails:

• DBA > Auditing > Audit SQL/SYS Privs - Allows the DBA to configure the monitoring audit options for SQL Statement Objects, Reserved Words and System Privileges in the database. This functionality requires the optional DBA module.
• Database > Auditing > Audit Objects - The Audit Objects window displays the audit monitoring options for selected database objects. You can enter and modify monitoring levels for each individual object or for groups of objects. This functionality requires the optional DBA module.

This screen has two display views. The ‘Audit Options’ view displays ALL of the SQL statements and SYS Priv statements that may be audited. The ‘Audit Trail’ view displays the auditing data recorded. Before an Audit Trail can be compiled, Auditing must be enabled on the database. Also, the Auditing screens will require the ‘AUDIT SYSTEM’ privilege and the ‘AUDIT ANY’ system privilege.

Use the Schema dropdown control to determine if the auditing options you enable will be applied to a single schema or to the entire database. Once you have chosen the statement you want to enable auditing on, double-click on the ‘Value’ cell in the data grid. This will open the ‘View/Edit Audit Option’.

Once you have configured your auditing options and you have enabled auditing on your instance, you are ready to view the audit trail. The audit trail can be exported using the Grid > Save As command. Users may find the Excel Instance option the most user friendly. It will export the entire audit trail to a new Excel spreadsheet, and will even bring over the column labels if desired.

This screen allows the DBA to set the Auditing options for all the objects in the database:

• Directories
• Libraries
• Object Types
• Procedures/Functions/Packages
• Sequences
• Snapshosts/Materialized Views
• Tables
• Views.

You can select the objects by type, by user, or by a combination of both. Once you have the objects loaded into the grid, you can set the Audit options. You can set the auditing options across all the objects, or you can pick and choose the options in the grid array for each object. Once you have set your options, use the ‘Apply Changes’ button to post the audit settings to the database.

To view the Audit Trail for the objects you have enabled auditing on, navigate to said object in the Schema Browser. You will find a ‘Auditing’ tab on the Detail panel. Again, this tab will have two views: 1)Audit Options, 2)Audit Trail. If you select the ‘Audit Trail’ view you will be able to access the audit trail for the object selected on the Left Hand Side of the Schema Browser. If you select the ‘Audit Options’ tab, you will be able to configure the auditing options for the individual object versus using the Audit Objects screen and loading the object manually.

For questions or comments on this site: webmaster@toadsoft.com
All content Copyright © 2006 Quest Software, Inc. All rights reserved.