Oracle - Database Specific Support

Introduction

In addition to the standard feature set in DbVisualizer this document briefly covers the database specific support in DbVisualizer for the Oracle RDBMS.

Database Objects Tree

The following table shows what objects DbVisualizer handles for an Oracle database. The first column list the structure and the object types. The actions column lists all actions that are associated with the object types and the last column shows all viewers that appear when selecting an object in the tree.

The screenshots illustrates the visual representation of the objects tree.

Object Type Actions Viewers Schemas   Schemas Schema Export Schema, String Search   Tables Import Table Data, Create Table References, Table Comments, Tables Table Export Table, Import Table Data, Create Trigger, Alter Table, Analyze Table1, Comment Table, Copy Table, Create Index, Drop Table, Empty Table, Grant Privilege, Rename Table, Script: CREATE TABLE, Script: DELETE, Script: INSERT, Script: SELECT, Script: UPDATE References, Navigator2, Columns, Columns Comment, Constraints, DDL with Storage4, DDL3, Data, Dependencies, Grants, Indexes, Info, Primary Key, Row Count, Row Id, Triggers Columns   Columns Column Comment Column Column Constraints   Constraints Constraint   Columns, DDL4, Info Column Comment Column Column RefConstraints   Constraints RefConstraint   Columns, DDL4, Info Column Comment Column Column Indexes   Indexes Index Compute Statistics for Index, Drop Index, Rebuild Index, Rename Index Columns, DDL4, Index Column Comment Column Column IndexPartitions   Partitions IndexPartition   Info, Sub-Partitions IndexSubPartition   Info Triggers   Triggers Trigger Compile Trigger, Disable Trigger, Drop Trigger, Enable Trigger, Rename Trigger Trigger Editor, Info Partitions   Partitions Partition   Info, Sub-Partitions SubPartition   Info Views Create View View Comments, Views View Comment View, Compile View, Create Trigger, Drop View, Rename View, Script: CREATE VIEW, Script: DELETE, Script: INSERT, Script: SELECT, Script: UPDATE Columns, Columns Comment, DDL, Data, Grants, Info, Row Count, Triggers Columns   Columns Column Comment Column Column Triggers   Triggers Trigger Compile Trigger, Disable Trigger, Drop Trigger, Enable Trigger, Rename Trigger Trigger Editor, Info Synonyms Create Synonym Synonymed Objects, Synonyms Indexes   Indexes Index Compute Statistics for Index, Drop Index, Rebuild Index, Rename Index Columns, DDL4, Index Sequences Create Sequence Sequences Sequence Drop Sequence, Rename Sequence DDL4, Info MaterializedViews   Materialized Views MaterializedView Comment Materialized View, Drop Materialized View, Refresh Materialized View Columns, DDL4, Data, Indexes, Info, Row Count, Select SQL Indexes   Indexes Index Compute Statistics for Index, Drop Index, Rebuild Index, Rename Index Columns, DDL4, Index Column Comment Column Column Functions Create Function Functions Function Compile Function, Drop Function, Script: CALL Function Editor Procedures Create Procedure Procedures Procedure Compile Procedure, Drop Procedure, Script: CALL Procedure Editor Packages Create Package Packages Package Compile Package, Compile Package and Body, Create Package Body, Drop Package and Package Body Package Specification Editor PackageBody Compile Package Body, Drop Package Body Package Body Editor PackageBodies Create Package Body Package Bodies PackageBody Compile Package Body, Drop Package Body Package Body Editor JavaSources4   Java Sources JavaSource   Info, Source JavaClasses4   Java Classes JavaClass   Info Triggers   Triggers Trigger Compile Trigger, Disable Trigger, Drop Trigger, Enable Trigger, Rename Trigger Trigger Editor, Info ObjectTypes   Object Types ObjectType   Attributes, DDL4, Info, Methods RecycleBin5   Recycle Bin RecycleBinTable Flashback to Before Drop, Purge from Recycle Bin Data, Recycle Bin Table Jobs Create Job Jobs Job Disable Job, Enable Job, Remove Job, Run Job Info Scheduler6 Disable Scheduler, Enable Scheduler, Set Scheduler Attribute Scheduler Global Attributes SchedulerChains Create Scheduler Chain Scheduler Chain Rules, Scheduler Chain Steps, Scheduler Chains, Scheduler Chains Running SchedulerChain Analyze Scheduler Chain, Disable Scheduler Chain, Drop Scheduler Chain, Enable Scheduler Chain, Run Scheduler Chain Scheduler Chain, Scheduler Chain Rules, Scheduler Chain Steps SchedulerChainRules Define Scheduler Chain Rule Scheduler Chain Rules SchedulerChainRule Drop Scheduler Chain Rule Scheduler Chain Rule SchedulerChainSteps Define Scheduler Chain Step Scheduler Chain Steps SchedulerChainStep Alter Scheduler Chain Step, Drop Scheduler Chain Step Scheduler Chain Step SchedulerJobs Create Scheduler Job Scheduler Job Arguments, Scheduler Job Logs, Scheduler Job Run Details, Scheduler Jobs, Scheduler Jobs Running SchedulerJob Alter Running Chain, Copy Scheduler Job, Disable Scheduler Job, Drop Scheduler Job, Enable Scheduler Job, Modify Scheduler Job, Run Scheduler Job, Stop Scheduler Job Scheduler Job, Scheduler Job Logs, Scheduler Job Run Details SchedulerJobArguments Set Scheduler Job Argument Scheduler Job Arguments SchedulerJobArgument   Scheduler Job Argument SchedulerPrograms Create Scheduler Program Scheduler Program Arguments, Scheduler Programs SchedulerProgram Disable Scheduler Program, Drop Scheduler Program, Enable Scheduler Program Scheduler Program SchedulerProgramArguments   Scheduler Program Arguments SchedulerProgramArgument   Scheduler Program Argument SchedulerSchedules Create Scheduler Schedule Scheduler Schedules SchedulerSchedule Drop Scheduler Schedule, Evaluate Scheduler Schedule Scheduler Schedule SchedulerCredentials7 Create Scheduler Credential Scheduler Credentials SchedulerCredential Drop Scheduler Credential, Modify Credential Scheduler Credential DatabaseLinks Create Database Link Database Links DatabaseLink Drop Database Link, Test Database Link DDL, Info InvalidObjects Compile All Invalid Objects Properties   Session Privileges, Session Properties4, Session Roles DBA   DBA Users Create User Users User Change Password, Drop User, Expire Password, Lock User, Unlock User, Script: Clone User DDL8, Info, Roles, System Privileges, Table Privileges, Tablespace Quota Roles   Roles Role   DDL4, Info, Roles, System Privileges, Table Privileges, Users Sessions   Active Sessions, Sessions, Traced Sessions6 Session Disable Trace6, Enable Trace6, Kill Session Active SQL, Session Locks   Blocking Locks, Locks 1, Locks 2 AllRecycleBin5   Recycle Bin AllRecycleBinTable Flashback to Before Drop, Purge from Recycle Bin Data, Recycle Bin Table ResourceManager3 Clear Pending Area, Create Pending Area, Generate monitor SQL, Submit Pending Area, Validate Pending Area   ResourceConsumerGroups Create Resource Consumer Group Resource Consumer Groups ResourceConsumerGroup Remove Resource Consumer Group Resource Consumer Group ResourcePlans Create Resource Plan Resource Plans ResourcePlan Activate Resource Plan, Remove Resource Plan Resource Plan, Resource Plan Directives ResourcePlanDirectives Add Group to Plan Resource Plan Directives ResourcePlanDirective Modify Resource Directive, Remove Group from Plan Resource Plan Directive Storage   ASMDisk5, ASMDiskGroups5, Data Files, Directories, Redo Logs, Rollback Segments, Tablespaces, Temp Files ASMDiskGroups5   ASMDiskGroups ASMDiskGroup   ASMDiskGroup ASMDisk   ASMDisk SegmentAdvisor5     SegmentAdvisorTasks Create Task Segment Advisor Findings, Segment Advisor Logs, Segment Advisor Recommendations, Segment Advisor Tasks SegmentAdvisorTask Cancel Task, Delete Task, Execute Task, Interrupt Task, Reset Task Segment Advisor Findings, Segment Advisor Log, Segment Advisor Recommendations, Segment Advisor Task SegmentAdvisorObjects Create Object Segment Advisor Objects SegmentAdvisorObject Update Object Segment Advisor Object SegmentAdvisorParameters Set Parameter Segment Advisor Parameters SegmentAdvisorParameter   Segment Advisor Parameter Tablespaces   Datafile IO, Objects, Tablespaces, Usage By User PermanentTablespaces Create Tablespace Datafile IO, Objects, Tablespaces, Usage By User PermanentTablespace Add Datafile, Remove Tablespace, Rename Tablespace5, Tablespace Offline, Tablespace Online, Tablespace Read/Write Mode DDL4, Datafile IO, Objects, Tablespace, Usage By User, User Quota PermanentDataFile   Data File, Objects TemporaryTablespaces1   Tablespaces TemporaryTablespace   DDL4, Tablespace, User Quota TemporaryTempFile   Temp File UndoTablespaces4   Datafile IO, Objects, Tablespaces, Usage By User UndoTablespace   DDL4, Datafile IO, Objects, Tablespace, Usage By User UndoDataFile   Data File, Objects TablespaceFiles   Data Files, Temp Files PermanentDataFile   Data File, Objects PermanentTablespace Add Datafile, Remove Tablespace, Rename Tablespace5, Tablespace Offline, Tablespace Online, Tablespace Read/Write Mode DDL4, Datafile IO, Objects, Tablespace, Usage By User, User Quota TemporaryTempFile1   Temp File TemporaryTablespace   DDL4, Tablespace, User Quota UndoDataFile4   Data File, Objects UndoTablespace   DDL4, Datafile IO, Objects, Tablespace, Usage By User RollbackSegments Create Rollback Segment Rollback Segments RollbackSegment   DDL4, Rollback Segment RedoLogs Force Log Switch Redo Log Activity, Redo Logs RedoLog   Redo Logs Directories4 Create Directory Directories Directory Drop Directory, Grant Privileges Directory, Directory Privileges DirectoryPrivilege Revoke Privileges   Scheduler6 Disable Scheduler, Enable Scheduler, Set Scheduler Attribute Scheduler Global Attributes SchedulerJobClasses Create Scheduler Job Class Scheduler Job Classes SchedulerJobClass Drop Scheduler Job Class Scheduler Job Class SchedulerWindows Create Scheduler Window, Purge Window logs Scheduler Window Details, Scheduler Window Log, Scheduler Windows SchedulerWindow Close Scheduler Window, Disable Scheduler Window, Drop Scheduler Window, Enable Scheduler Window, Modify Scheduler Window, Open Scheduler Window Scheduler Window, Scheduler Window Details, Scheduler Window Log SchedulerWindowGroups Create Scheduler Window Group Scheduler Window Groups SchedulerWindowGroup Add Window Group Member, Disable Scheduler Window Group, Drop Window Group, Enable Scheduler Window Group Scheduler Window Group SchedulerWindowGroupMembers   Scheduler Window Group Members SchedulerWindowGroupMember Remove Window Group Member Scheduler Window Statistics     InstanceInfo   Instance Info InstanceSummary   Instance Summary Latches   Latches ObjectCount   Object Count Waits   Waits SGA Flush SGA Object Cache, Poorly performing SQL's, SGA 1) Version 8.1 and later 2) DbVisualizer Personal 3) Version 8 and later 4) Version 9 and later 5) Version 10 and later 6) Version 10.1 and later 7) Version 11 and later 8) Version 7 and later

DBMS_OUTPUT Support The DBMS Output tab (in SQL Commander) for Oracle is used to enable and disable capturing of messages produced by stored procedures, packages, and triggers. These messages are typically inserted in the code for debugging purposes. For SQL*Plus users the corresponding feature is enabled via the set serveroutput on command. To enable display of DBMS messages in DbVisualizer select the DBMS Output tab and press the Enable button.	1
Explain Plan Explain Plan executes your query and records the plan that the database devises to execute it. By examining this plan, you can find out if the database is picking the right indexes and joining your tables in the most efficient manner. The explain plan feature works much the same as executing SQLs to present result sets, you may highlight statements, run a script or load from file. The explain plan results can easily be compared by using the pin feature in combination with window style presentation.	1 2
Data Type support In addition to the standard data type support in DbVisualizer there is also support for the following Oracle specific data types: XMLTYPE TIMESTAMPTZ TIMESTAMPLTZ Automatic conversion of DATE data type to TIMESTAMP (configure in Connection Properties) (For XMLTYPE you need to load the xdb.jar and xmlparserv2.jar in the DbVisualizer Driver Manager. These files are available in the Oracle XML Developers Kit available from Oracle Corp.).

---