GUGGI for ORACLE - Tool For DBAs and Developers

Database Tools
Guggi for Oracle >>
Guggi for DB2 >>
Downloads

Join Guggi Users Group


Articles >>

Version 1.4.7

Impact Solutions LLC

Contact: info@impact-sol.com

Disclaimer: This product does not come with any warranty and is intended for use as is. Impact Solutions LLC and the developers of this product will not be responsible for any direct, indirect, incidental, or any damage of any kind in connection with the use of this product or use of this User Guide. The information contained in this guide is subject to change without any information. This product does not come with any warranty for the use of this product for any particular purpose.

This product is protected by copyright law and international treaties. Unauthorized reproduction or distribution may result in severe civil and criminal penalties.

You can also download a PDF version of Guggi for Oracle User Guide here.

Table of Contents

Introduction.
Installation Instructions.
Analyze Manager
Application Options.
Backup Statistics Manager
Download Binary Data and View Image
Freespace Manager
Guggi SQL Editor
Input / Output Statistics Manager
Invalid Objects Manager
Latches, Locks, Waits and Stats
Lock Manager
Login Window
Object Level Space.
Oracle Parameters.
PL/SQL Editor
PL/SQL Profiler
PL/SQL Trace
Reverse Engineer
Rollback Segment Manager
Schema Browser

Clusters.
Constraints.
Consumer Groups.
Contexts.
Database Links.
Dimensions.
Directories.
External Tables.
Functions.
Indexes.
Index Types.
Java.
Jobs.
Libraries.
Materialized Views.
Operators.
Packages.
Policies.
Procedures.
Profiles.
Queues.
Queue Tables.
Rollback Segments.
Schema / Users.
Sequences.
Summaries.
Synonyms.
Tables
Tablespaces.
Triggers.
Types.
Views

Sessions Manager
SQL Editor
SQL Statistics Manager
Temporary Objects
Upload Binary Data and View Image
User / Schema Role Manager
View Linked Data Manager

Introduction

Guggi for Oracle is a comprehensive, robust and scalable database development, administration, and analysis tool. The tool comes with a variety of features that can be used by developers and DBAs on a regular basis. Use of Guggi for Oracle will lead to reduced development and turn-round time.

The user guide discusses how to use Guggi for Oracle in great detail. There are various tools that are built into Guggi Oracle and each tool is covered in great detail. Users of Guggi for Oracle are encouraged to send their comments, questions, bug reports and enhancement requests at info@impact-sol.com. Users can also join the discussion forum at http://groups.yahoo.com/group/guggiusers

Installation Instructions

Installing Guggi for Oracle is a simple process. The product comes as a zipped file.

Unzip Guggi.zip.
Double-click on Guggi.msi. The installer will be launched.
If the installer detects a previous version of Guggi, it will ask whether you want to remove or repair Guggi. Select remove Guggi.
Once the previous version is removed, double-click on Guggi.msi again and it will install the latest version. This way the installation is always clean.

It is recommended that Oracle's OLEDB, which is also the native driver to connect

to Oracle, be used. OraOleDB is installed when you install Oracle Client on your PC.

You may need to download MDAC ver 2.7 or 2.8 to connect to Oracle using

OLEDB made by Microsoft. MDAC is available from www.microsoft.com

It is recommended that the ODBC driver provided by Oracle be used to connect to Oracle if you want to use the ODBC connection instead of OLEDB connection. Connection as SYSDBA and SYSOPER is supported via ODBC connection using ODBC driver made by Oracle

PLAN_TABLE is provided with the download as a SQL script. Execute the script to create the table. Explain Plan cannot be used until the plan table exists.

Analyze Manager

Analyze Manager is a built-in tool to analyze database objects such as tables and indexes. Statistics of the analyzed objects can also be viewed using Analyze Manager. The tool can be invoked by clicking on or by selecting Analyzing Manager from Tools menu.

View analyze statistics

1. Select a schema.

2. Select Table or Index

Analyze statistics will be displayed for the selected object type in the grid.

Objects can be analyzed as follows:

1. Select the schema where you want to analyze the objects. You must have appropriate privileges to analyze objects in a given schema.

2. Select the type of object (table or index) that need to be analyzed.

3. Select the appropriate Analyze Option. The possible options are Compute Statistics, Delete Statistics, and Estimate Statistics.

4. If you chose Estimate Statistics option, enter the Sample percent to be taken into consideration for analysis.

5. Check the Analyze All Tables checkbox if you want to analyze all tables or indexes. Otherwise select the object from the grid by clicking on the appropriate record in the grid.

6. Click the Execute button to analyze the object(s).

7. The information in the grid will be updated once the objects are analyzed.

Number of records in a given table can be compared with the data shown in the Num Rows column by selecting the table from the grid and clicking on . This comparison is quite useful in determining if the object has been properly analyzed or not.

Application Options

Global settings for the application can be set using the Application Options tool. The tool can be launched by selecting “Application Options” from View menu option. Settings have been categorized under four (4) sections.

1. Startup – Set the variables that will take effect after the application is launched

First window after login – Define the first window that you want to be opened after the login. The choice is between Schema Browser and SQL Editor.
Maximum number of records – Set the number of records displayed when browsing through the table, view or synonym data in the Schema Browser.
Select Application Language – Set the language for the application menu. Current choices are English, French, German and Spanish.
Use DBA% views instead of ALL% views – Set the database dictionary view to be used to display objects in the Schema Browser. Check the box to use DBA% views. You must have DBA privileges to use this option. You can set the option and use it when the application is already launched. This means that you would not need to re-login for the setting to take effect. Just refresh the browser after the setting changes are made.

2. SQL Editor – Set the variables and parameters as they relate to the SQL editor

a. Set Font Name – Set the font for the editor text

b. Set Font size – Set the size of font for editor text

c. SQL window background color – Set the background color of SQL Editor text area

d. SQL datagrid background color - Set the background color of SQL Editor datagrid

e. Number of SQL statements in buffer – Set the number of statement that the SQL window buffer will hold for the given instance of the SQL editor. The buffer is available within the open instance of the window and is not available across windows if multiple SQL Editor windows are open.

3. Schema Browser - Set the variables and parameters as they relate to the Schema Browser

Set Grid Background Color – Set the background color of the grid displaying the object properties
Grid Back Color – Set the color of the cells of the grid displaying object properties
Object List Background Color – Set the background color of the left hand panel that displays the object list.
Grid Font - Set the font of the data in cells of the grid displaying object properties
Size of Left Pane – Set the size of the tabular panel that displays the object list. Size of the panel cannot be more than 40% of the screen width. If a higher value is set then the panel size will be reset to 40% of the screen width.

4. Connection Setup – Set the connection preferences

Connection Driver – Set the connection driver to use when connecting to Oracle during login. The choices are Microsoft Oledb driver, Oracle Oledb driver or ODBC driver. The setting can be over-ridden at the time of login by manually choosing the setting. Oracle Oledb driver is the preferred setting.
Save password – This sets the option to save the password in the registry. The passwords are retrieved when the user clicks on the list of username/database name displayed in the login window. The user may choose not to save passwords in the registry for security reasons.

Backup Statistics Manager

Details regarding current or previous backups can be viewed using the Backup Statistics Manager. Click on or select “Backup Statistics Manager” from the Database and DBA menu option to access the Backup Statistics Manager.

Using this tool you can view Backup Logs, Details on the last backup and whether it was a hot or a cold backup, and the progress of current backup.

The information on the screen can be refreshed by clicking from the toolbar.

Download Binary Data and View Image

Data in form of binary files such as images, documents, spreadsheets stored in the database as BLOB can be downloaded using the View Image tool . You can view images if the binary file is an image file. Currently, gif, jpg, ico, and bmp image formats are supported by the image viewer.

Binary files can be downloaded using Guggi for Oracle as follows:

1. Select the schema and the table where the BLOB data is stored

2. Select the BLOB column where the binary data is stored. Records from the table will be displayed once the BLOB column is selected.

3. Records can be filtered by clicking on .

4. Click on the Save Image button and enter a file name where the downloaded binary file will be saved.

5. Select the record from the grid displaying data.

6. Binary file will be downloaded and displayed in the Image viewer if it is an image file. If the file is not an image file, it will still be saved on the local disk but will not be displayed in the image viewer.

You can select other records and display more images without having to enter the file name each time. Each time you click on a record, the binary file will be downloaded and saved as the file entered in step 4. It is not necessary to enter a file extension as the image viewer will detect if it is an image file or not. If it is not an image file, you can rename the file (through command prompt or windows explorer) with the appropriate extension after the file has been downloaded.

Freespace Manager

Freespace in the database at the tablespace and datafile level can be viewed using the Freespace Manager. Click on or select “Free Space” from the Database and DBA menu option to access the Freespace Manager.

The tool displays the freespace for every datafile in the top data grid and freespace for every tablespace in the bottom datagrid.

Click on to refresh the information on freespace.

Guggi SQL Editor

Guggi SQL Editor is a stand-alone tool to execute SQL statements. This is an application that runs out of Guggi for Oracle. Guggi SQL Editor can be invoked by clicking on or by selecting Guggi SQL Editor Application from Tools menu option. All the features and functionality of this application are similar to that of the SQL-Editor.

Input / Output Statistics Manager

File and session input / output statistics and system events can be viewed using the Input / Output Statistics Manager. Click on or select “Input / Output Stats” from the Database and DBA menu option to access the Input / Output Statistics Manager.

Click on the File I/O tab to view the File input/out statistics.

Click on the Session I/O tab to view the session output statistics. Sessions can be managed through sessions manager also.

Invalid Objects Manager

Invalid Objects Manager is a built-in tool that presents the users with a single view of invalid objects in a schema and compiles them. The tool can be opened by clicking on from the main toolbar or by selecting Invalid Objects Manager from the Tools menu option.

Invalid objects can be viewed as follows:

1. Select the schema whose invalid objects are to be viewed.

2. All the invalid objects are displayed in the grid.

If you want to view invalid objects across all schemas then check the checkbox “Show objects for all schemas” and click the refresh button

Compile invalid objects

Select the object that you wish to compile from the grid and click on . The object will be compiled otherwise an error message will be displayed. If the object cannot be compiled then you would need to load the object definition in the PL/SQL editor and compile the definition.

Latches, Locks, Waits and Stats

This DBA tool allows DBAs to monitor the wait events, latches, locks and statistics for instance, SGA and sessions. Click on or select “Latches, Locks, Waits and Stats” from the Database and DBA menu option to access the Latches, Locks, Waits and Stats manager.

Click on the appropriate tab to view the wait events or latches or locks. Locks can be managed using the Lock Manager.

To view the detail of the statistics of a particular session, select the session from the grid on the left-hand-side in the Session Statistics section. Session statistics details will be displayed in the right-hand-side grid.

The information displayed by this tool can be refreshed by clicking on in the toolbar.

Details and definitions of various parameters is beyond the scope of this book.

Lock Manager

Locks in the database can be managed using the locking manager. You need to have system privileges to use the Lock manager. Click on or select “Lock Manager” from the Database and DBA menu option to access the Lock Manager.

Lock manager displays the details for the following:

1. All locks

2. DML Locks

3. DDL Locks

4. Waiting and Holding Sessions

You can release a lock by terminating the session. Sessions can be terminated by selecting a record from the display grid and clicking the button. This action will ask the user to confirm whether the session is to be terminated. Click on Yes to end the session or click on No to leave the session as it is. If you are in the Waiters window then the user will be asked if the Holding session is to be terminated. If you click on Yes then the holding session will be terminated, if you click on No then waiting session will be terminated, and if you click Cancel then none of the two sessions will be terminated and the action will be canceled.

Click on to refresh the lock information.

Login Window

Click the on the main toolbar. Alternatively, go to File menu option and select New Connection.
A login window opens up. Enter the user name (database schema name), password and database (TNS Name) in the appropriate boxes. You can also select the database and login from the grid on the right hand-side by clicking on the row. Passwords by default are saved in the registry.
Select the driver to connect to the database. Select from OLEDB connection (Microsoft Driver for Oracle or Oracle Driver for Oracle) or ODBC Connection (DSN Connection). All three methods require the use of TNS name. You can connect as SYSDBA or SYSOPER also. Currently, SYSDBA and SYSOPER connections are supported via ODBC. Oracle ODBC for Oracle version 9.2.0.3 higher is required.
Click OK.
The schema browser window will open up. The user name and database name will be displayed in the window caption. This will tell you which database you are currently logged into. A button will be added at the bottom of the window. Depending on the driver used to connect to Oracle, the connection button will display.

for Oracle OleDb driver

for Microsoft OleDb driver for Oracle
for ODBC DSN connection
You can swap between connections by clicking on the appropriate button.

Double-click on the Login / Database combination on the right hand side and you will be connected to the database. Or click once on the combination and then click OK to connect to the database. Password will be retrieved from the registry.

Object Level Space

Space occupied by various database objects such as tables, indexes, materialized views can be viewed using the Space by Object tool. Select “Space by object” from the Database and DBA menu option to access the tool.

To view free space

1. Select a schema from the dropdown box.

2. Space occupied by various objects will be displayed in the grid.

3. To view space taken by all the objects in the database across all schemas, check the “Show objects for all schemas” checkbox, and click the refresh button .

In order to free up space, select the object and either truncate it (in case of a table), or drop it (in case of index, materialized view, or table).

To drop object

Select the object to be dropped and click on .

To truncate table

Select the object to be truncated and click on .

To refresh the information displayed on the screen

Click on the refresh button

Oracle Parameters

Oracle parameters set in the current instance of the database can be displayed. The parameters can be viewed by selecting Oracle Parameters from Database and DBA menu option.

Oracle parameters display can be refreshed by clicking on .

The window can be closed by clicking on

PL/SQL Editor

The PL/SQL Editor is used to develop objects such as functions, procedures, packages, types, triggers etc. The tool can be invoked using one of the two options:

1. Click on in the main toolbar.

2. Select PL/SQL Editor from the Tools menu option.

3. Select PL/SQL Code from Create Object menu option

Following features can be accessed by using the buttons on the toolbar

1. Click on to compile the code

2. Click on to convert to upper case or select “Change to upper case” from SQL and PL/SQL menu

3. Click on to convert to lower case or select “Change to lower case” from SQL and PL/SQL menu

4. Click on to convert to initcap or select “Change to init cap” from SQL and PL/SQL menu

5. Click on to search for the text

6. Click on to comment the code or select “Comment” from SQL and PL/SQL menu

7. Click on to uncomment the code or select “Uncomment” from SQL and PL/SQL menu

8. Click on to undo changes (alternatively press Ctrl-Z). – under development

9. Click on to redo changes (alternatively press Ctrl-Y). – under development

10. Click on to display a list of schemas and tables. Click on the table and it will be added to the PL/SQL code

11. Click on to display a list of schemas, tables and columns. Click on the column and it will be added to the PL/SQL code

12. Click on to close the PL/SQL editor

How to Use PL/SQL Editor

It is very simple and straight-forward to compile PL/SQL code in the PL/SQL editor. Just write the code in the editor and hit the compile button. The status of the compiled code will be displayed below as “VALID” or “INVALID”. If the status is “INVALID” then click on to scroll through (forward) the errors. You will be taken to the line where Oracle finds the error. You can click to scroll backward in the error list. After fixing the error click on compile again and you will see the status change to “VALID” assuming all the errors have been fixed.

The editor comes with some of the most advanced editing features. Some of the highlights of the PL/SQL editor are:

1. Syntax highlighting is enabled in the editor.

2. Bracket / brace matching

2. Built-in intellisense will display the columns of a table , procedures of a package etc.

3. The editor has multi-level redo and undo.

4. You can indent the text by selecting the text and pressing the Tab key.

4. Press Ctrl-Q on an object in the code and its details will be displayed in a separate window. Alternatively, you can select “Display Object Details” from SQL and PL/SQL menu option.

5. Click on in the main toolbar to load a file into the editor or select Open File from File menu or press Ctrl+O

6. Click on to save the file on the local disk or select Save File As from File menu or press Ctrl+S.

7. Click on to print the code or select Print from File menu or press Ctrl+P

8. Click on to copy text or select Copy from Edit menu or press Ctrl+C

9. Click on to paste text or select Paste from Edit menu or press Ctrl+V

10. Click on to cut the text or select Cut from Edit menu or press Ctrl+X

11. Click on to initiate search or select Find from Edit menu or press Ctrl+F

12. Select Replace from Edit menu or press Ctrl+H to replace text

Short-Cut Keys in PL/SQL Editor

Alt c : Append copy

Alt Delete : Delete line

Alt l : Convert the case of the word to lower case

Alt s: Swap the case of the word

Alt u : Convert the case of the word to upper case

Alt x : Append cut

Ctrl ] : Find matching bracket

Ctrl a : Select All

Ctrl Alt u : Swap the case of selected text

Ctrl Backspace : Delete till the start of the word

Ctrl c : Copy selected text

Ctrl d : Insert date

Ctrl Delete : Delete till the end of the word (opposite of Ctrl Backspace)

Ctrl Down : Scroll view up

Ctrl End : Go to End of Text

Ctrl f : Find dialog

Ctrl f3 Find next word

Ctrl g : Go to line

Ctrl h : Replace dialog

Ctrl Home : Go to beginning to text (opposite of Ctrl End)

Ctrl left arrow key: Go to previous word

Ctrl p : print

Ctrl pagedown : Scroll view left

Ctrl pageup : Scroll view right

Ctrl right arrow key : Go to next word

Ctrl s : Save file

Ctrl u: Convert the selection to lower case

Ctrl up : Scroll view down

Ctrl v : Paste

Ctrl x : Cut

Ctrl y : Redo

Ctrl z : Undo

F3 : Find next

Shift Alt t : Swap lines

Shift Ctrl ] : Find matching bracket select

Shift Ctrl c : Copy line

Shift Ctrl d : Insert time

Shift Ctrl delete : Delete word

Shift Ctrl End : Highlight the text from the cursor position till the end of the entire text

Shift Ctrl F3 : Find previous word

Shift Ctrl Home : Highlight the text from the cursor position till the beginning of text

Shift Ctrl L : Mark line

Shift Ctrl Left arrowkey : Highlight the word from the cursor position till the beginning of the word

Shift Ctrl Right arrowkey : Highlight the word from the cursor position till the beginning of the word

Shift Ctrl u : Change the selected text to upper case

Shift Ctrl w : Mark the word

Shift Ctrl x : Cut line

Shift Delete : Delete from the cursor position till the end of line

Shift Down : Highlight the text from the cursor position till the next line

Shift End : Highlight the text from the cursor position till the end of line

Shift F3 : Find previous

Shift Home : Highlight the text from the cursor position till the beginning of line

Shift Insert : Paste

Shift Left : Highlight the text from the cursor position till the previous character

Shift Pagedown : Highlight the text from the cursor position till one page down

Shift Pageup : Highlight the text from the cursor position till one page up

Shift Right : Highlight the text from the cursor position till the next character

Shift Tab : Tab in reverse

Shift Up : Highlight the text from the cursor position till the previous line

PL/SQL Profiler

Inefficiencies in the PL/SQL code can be identified by running the PL/SQL profiler against the package, procedure or function (PL/SQL block). PL/SQL Profiler can be invoked by clicking on or by selecting PL/SQL Profiler from Tools menu.

Steps to run the PL/SQL Profiler

1. Type the script to execute the procedure in the window named Test Script. A sample script may look like

Begin

Test_procedure (‘input var’);

End;

2. Enter a name of the profiler run in the box named Profiler Run Name.

3. Click to start the profiler.

4. Once the profiler run is done and the statistics have been collected, the run name will appear in the tab / grid below called “Runs”.

Analyze Profiler Runs

1. Several profiler runs are displayed in the tab called “Runs”.

2. Select a run by clicking on the record in the Run grid. The name of the run appears in the “Profiler Run Name” box.

3. Click on the “Results Summary” tab. Detailed statistics of the run are displayed in the grid.

4. Select a particular line in the “Results Summary” grid and a new tab displaying the code will open up above. The line whose statistics have been collected will be displayed in the new tab.

Delete a Run

1. Select the run in the “Runs” tab.

2. Click on the to delete the run.

3. Click on to make the delete permanent.

In order to use PL/SQL profiler, profiler tables and the dbms_profiler package must already exist in the database.

PL/SQL Trace

Trace information can be collected for execution of PL/SQL code. Combined with PL/SQL profiler this information can give useful insight into the performance bottlenecks in the PL/SQL code. PL/SQL trace can be invoked by selecting PL/SQL Trace from Tools menu or clicking from the main toolbar.

Steps to run the PL/SQL Trace

1. Type the script to execute the procedure in the window named Test Script. A sample script may look like

Begin

Test_procedure (‘input var’);

End;

2. Click on to start the trace.

4. Once the trace run is done and the statistics have been collected, the run will appear in the tab / grid below called “Runs”.

Analyze Trace

1. Several trace runs are displayed in the tab called “Runs”.

2. Select a run by clicking on the record in the Run grid. The name of the run ID appears in the “Run ID” box.

3. Click on the “Results Summary” tab. Trace of the run are displayed in the grid.

Delete a Run

1. Select the run in the “Runs” tab.

2. Click on to delete the run.

3. Click on to make the delete permanent.

In order to use PL/SQL trace, trace tables and the dbms_trace package must already exist in the database.

Reverse Engineer

DDL of database objects can be reverse engineered and saved into a file. The file can later be executed in another schema to create the objects. Reverse engineering tool can be accessed by clicking on in the main toolbar or by selecting “Reverse Engineer” from Tools menu.

1. Select schema from where you want to reverse engineer the objects.

2. Select the database object types to be reverse engineered. You can select all the object types or some of the object types.

3. Select a file where the reverse engineered DDL will be saved.

Future Enhancements

1. Reverse engineer each object type into its own file

2. Add more objects

3. Add the option to not reverse engineer storage parameters.

4. Reverse engineer DDL for individual objects.

5. Display reverse engineered objects as an ERD.

Rollback Segment Manager

Rollback segments in the database can be analyzed using the Rollback Segment Manager tool. The tool can be opened by clicking on in the toolbar or by selecting “Rollback Segment Manager” from Tools menu option.

Details about rollback segments such as hit ratio, size, high water mark, number of times extended or wrapped or shrank etc. can be viewed. Further details about the sessions currently being supported by the rollback segment can be viewed selecting the rollback segment from the grid. The rollback segment can be selected by clicking on the appropriate record in the grid. Any SQL currently being executed on the rollback segment will also be displayed.

The screen can be refreshed by clicking on the button.

A new rollback segment can be created by clicking on the button. Create Rollback Segment window will open and you can enter appropriate parameters to create the rollback segment.

Schema Browser

Schema browser can be used to browse through several database objects. Details of the features and functionalities available for various objects in the Schema Browser are given below.

The Schema Browser can be launched by any one of the following methods:

1. Click on the main tool bar

2. Select Schema Browser from the Tools menu

3. Right-click on the connection button at the bottom of the application and select Schema Browser. There should be at least one active window for the selected connection for the Schema Browser to open.

Following features can be accessed by using the buttons on the toolbar

1. Click on to refresh the contents of the Schema Browser

2. Click on to see additional objects

3. Click on to close Schema Browser

4. Click on to hide the Objects Pane and maximize the Details Pane (Right Hand Side pane).

5. Click on to display the Objects Pane again.

6. Click on to view the relationship of the selected table with other tables in the same schema in an Entity Relationship Diagram (ERD) format.

Clusters

A list of all the clusters contained in the schema is displayed.

These features can be accessed by using the buttons on the toolbar

1. Create a cluster

2. Drop a cluster

Following information can be viewed for a given cluster

1. Details of the cluster

Constraints

A list of all the constraints contained in the schema is displayed.

These features can be accessed by using the buttons on the toolbar

1. Create a constraint

2. Drop a constraint

3. Enable constraint

4. Disable a constraint

5. Enable all constraints

6. Disable all constraints

Following information can be viewed for a given cluster

1. Details of the constraint

2. Columns of the constraint

Consumer Groups

A list of all the Consumer Groups defined in the database is displayed.

These features can be accessed by using the buttons on the toolbar

1. Drop a Consumer Group

2. Create a Consumer Group

Following information can be viewed for a given Consumer Group

1. Details of the Consumer Group

2. Grants

Contexts

A list of all the contexts defined in the database is displayed.

Following information can be viewed for a given Context

1. Details of the Context

Database Links

A list of all the database links contained in the schema or in the database is displayed.

These features can be accessed by using the buttons on the toolbar

Create a database link
Drop a database link

Following information can be viewed for a given database link

Details such as owner, connect user, target etc

Dimensions

A list of all the Dimensions contained in the selected schema is displayed.

Following information can be viewed for a given Dimension

1. Details of the Dimension

2. Levels in the Dimension

3. Hierarchies in the Dimension

Directories

A list of all the directories contained in the schema is displayed.

These features can be accessed by using the buttons on the toolbar

Create a directory
Drop a directory

Following information can be viewed for a given Directory

Details such as directory path, owner

External Tables

A list of all the External Tables contained in the selected schema is displayed.

Following information can be viewed for a given External Table

1. Details of the External Table

2. File location of the External Table

Functions

A list of all the functions contained in the selected schema is displayed. If the function is valid then a green filled circle appears before the function name. If the function name is invalid then a red filled circle appears before the function name.

These features can be accessed by using the buttons on the toolbar after selecting the view

Create a function
Drop a function
Load the function code in the PL/SQL editor window
Recompile a function

Following information can be viewed for a given function

PL/SQL code
Objects used by the function
Grant on the function

Indexes

A list of all the indexes contained in the selected schema is displayed. If the index is partitioned then appears before the index name.

These features can be accessed by using the buttons on the toolbar

Create a new index
Drop an index
Rebuild an index

Following information can be viewed for a given index

Details about the function
Statistics
Partitions (for partitioned indexes)
DDL Script

Index Types

A list of all the Index Types defined in the database is displayed.

Following information can be viewed for a given Index Type

1. Details of the Index Type

2. Operator

Java

A list of all the Java Classes, Java Data, Java Resource, Java Source contained in the selected schema is displayed.

These features can be accessed by using the buttons on the toolbar

1. Filter on Java Class, Data, Resource or Source

2. Drop Java Class, Data, Resource or Source

Following information can be viewed for Java Class, Data, Resource or Source

1. Details

Jobs

A list of all the Jobs defined in the database is displayed.

Following information can be viewed for a given Job

1. Details of the Job

2. Currently running Job

Libraries

A list of all the libraries contained in the schema is displayed.

These features can be accessed by using the buttons on the toolbar

Create a library
Drop a library

Following information can be viewed for a given Operator

1. Details of the library such as file spec, status etc

Materialized Views

A list of all the materialized views contained in the schema is displayed.

These features can be accessed by using the buttons on the toolbar

1. Create a materialized view

2. Drop a materialized vie

Following information can be viewed for a given materialized view

Columns in the view
Definition of the view
Data in the view
Grants on a view
Objects used by the view
Objects that use the view

Operators

A list of all the operators contained in the schema is displayed.

Following information can be viewed for a given Operator

Binding and Arguments details

Packages

A list of all the packages contained in the schema is displayed. If the package is valid then appears before the procedure name. If the package is invalid then appears before the procedure name.

These features can be accessed by using the buttons on the toolbar

Create a package
Drop a package
Execute a package
Load the code in the PL/SQL editor
Recompile a package
Recompile all packages

Following information can be viewed for a given package

PL/SQL code – package specification and package body. Search the procedure/function in the package
Objects used by the package
Objects that use the package
Grants

Policies

A list of all the policies defined in the database is displayed.

Following information can be viewed for a given Policy

1. Details of the policy

Procedures

A list of all the procedures contained in the schema is displayed. If the procedure is valid then appears before the procedure name. If the procedure is invalid then appears before the procedure name.

These features can be accessed by using the buttons on the toolbar

Create a procedure
Drop a procedure
Execute a procedure
Load the code in PL/SQL editor
Recompile a procedure
Recompile all procedures

Following information can be viewed for a given procedure

PL/SQL code
Objects used by the procedure
Objects that use the procedure
Grants

Profiles

A list of all the Profiles defined in the database is displayed.

Following information can be viewed for a given Profile

1. Details of the Profile

Queues

A list of all the queues contained in the schema is displayed.

These features can be accessed by using the buttons on the toolbar

1. Create a queue

2. Drop a queue

3. Start enqueue

4. Stop enqueue

5. Start dequeue

6. Stop dequeue

Following information can be viewed for a given Queue

Details of the queue
Details of the corresponding queue tables
Statistics on the queue

Queue Tables

A list of all the Queue Tables contained in the selected schema is displayed.

Following information can be viewed for a given Queue Table

1. Details of the Queue Table

Rollback Segments

A list of all the rollback segments contained in the database is displayed.

These features can be accessed by using the buttons on the toolbar

Create a rollback segment
Drop a rollback segment
Bring the rollback segment ONLINE
Take the rollback segment OFFLINE

Following information can be viewed for a given rollback segment

Details about the rollback segment such as initial extent, tablespace name etc.

Schema / Users

Following information can be viewed for a given schema/user

Details such as account status, default and temporary tablespace, number of objects owned by the schema
Roles granted to the schema
Number of objects owned by the schema. Both graphical and numerical representations are made.

Sequences

A list of all sequences contained in the selected schema is displayed.

These features can be accessed by using the buttons on the toolbar

Create a sequence
Drop a sequence

Following information can be viewed for a given sequence

Details such as owner, minimum value, maximum value etc.
Grants

Summaries

A list of all the Summaries contained in the selected schema is displayed.

Following information can be viewed for a given Summary

1. Details of the Summary

2. Aggregates

3. Detail Tables

Synonyms

A list of all the synonyms contained in the selected schema is displayed. If the synonym is PUBLIC then appears before the synonym name. If it is private then there is no synonym.

These features can be accessed by using the buttons on the toolbar

Create a synonym
Drop a synonym
Filter the list of synonyms

Following information can be viewed for a given synonym

Details of the synonym such as underlying objects, owner, object type etc.
Data in the synonym if the synonym is build against a table or view
Code (DDL or PL/SQL) for the object represented by the synonym.

Tables

A list of all the tables contained in the selected schema is displayed. Various symbols are used to denote if the selected table is a partitioned or an IOT table.

1. Denotes a partitioned table

2. Denotes an IOT table

These features can be accessed by using the buttons on the toolbar after selecting the table

Create table
Drop table
Truncate table
Create index on a table
Analyze table
Add a constraint on a table
Rename a table
Clear filter on table

Following information can be viewed for a given table

List of columns and datatypes
Indexes on a table
Constraints on a table
Data in a table. Number of records to be viewed can be adjusted by using Set Records option under Tools menu in the menu bar

Filter Data
Export data to csv, tab, Excel, Insert and user defined delimited format
Commit changes made to the data in the table
Rollback changes made to the data in the table
Add a record to the table
Delete a record from the table
Count the number of records in the table. If a filter condition is applied, you can count the number of filtered records
Select the columns to be displayed
Single record view
View and edit data in the text columns

DDL Script to create the table with storage parameters
Partitions of the table
Sub-partitions of the table
Referential integrity constraints on the table
Triggers on the table
Grants on the table
Statistics on the table
Various objects using the table
Histograms on the table

Tablespaces

A list of all the tablespaces contained in the database is displayed. If the tablespace is permanent then appears before the tablespace name. If the tablespace is temporary then appears before the tablespace name. If the tablespace is undo tablespace then appears before the tablespace name.

These features can be accessed by using the buttons on the toolbar

Drop a tablespace
Create a tablespace (feature not implemented yet)
Rename a tablespace
Coalesce a tablespace (in case of honeycomb fragmentation)
List the objects owned by the tablespace

Following information can be viewed for a given tablespace

Details such as storage parameters, status etc.
Space characteristics such as used space, free space, maximum free space etc.

Also, refer to Free Space and Space by Object tools under DBA menu.

Triggers

A list of all the triggers contained in the selected schema is displayed.

These features can be accessed by using the buttons on the toolbar

Drop a trigger
Load the trigger code in the PL/SQL editor
Enable a trigger
Disable a trigger
Enable all triggers
Disable all triggers

Following information can be viewed for a given trigger

PL/SQL code
Details
Grant

Types

A list of all the types contained in the schema is displayed. If the type is valid then appears before the type name. If the type is invalid then appears before the type name.

These features can be accessed by using the buttons on the toolbar

Create Type
Drop Type
Load code in PL/SQL editor
Recompile Type

Following information can be viewed for a given Type

PL/SQL Code
Grant

Views

A list of all the views contained in the selected schema is displayed. If the view is valid then appears before the view name. If the view name is invalid then appears before the view name.

These features can be accessed by using the buttons on the toolbar after selecting the view

Drop a view
Recompile a selected view
Recompile all views

Following information can be viewed for a given view

1 List of columns and datatypes

2 Script to create the view

3 Data in a view. Number of records to be viewed can be adjusted by using Set Records option under Database menu in the menu bar

· Filter the data in the view

· Export data in the view

· Single record view of the data

4 Grants on the view

5 Objects used by the view

6 Various objects using the view

Sessions Manager

Sessions manager displays the session information, active SQL, Explain Plan and SQL statistics. The tool can be used to see active sessions, percentage of the SQL completed, database and operating system user along with several session related parameters. Sessions manager can be invoked by clicking on or by selecting Sessions Manager from Tools menu option.

To view SQL

1. Identify the session in the grid.

2. Click on the record in the grid.

3. Select the “SQL” tab.

To View Explain Plan

Once the SQL has been identified, click on the “Explain Plan” tab and the explain plan of current SQL will be displayed. The plan table needs to exist in the schema from where the sessions manager has been opened or there should be a public synonym for the plan table. If the plan table does not exist, an error message “table does not exist” will be displayed.

Session Details tab shows the percentage complete of the database transaction being performed at that instant. The database transaction can be viewed in the Message column.

Number of active, inactive sessions can be viewed by clicking on the Graphics tab. Number of sessions by osuser or oracle user can also be viewed.

If you have DBA privileges, a session can be killed by selecting the session from the grid and clicking on . Guggi for Oracle would ask you to confirm before terminating the session.

Data can be selectively displayed by filtering on some of the columns such as Status, Type, UserName, Osuser etc.

Session information can be refreshed by clicking on .

SQL Editor

SQL Editor is a powerful tool to execute SQL statements against Oracle databases. You can open a SQL Editor window by clicking on in the main toolbar, or by selecting SQL Window from the Tools menu option. The SQL Editor can also be opened by clicking the right key on the appropriate connection-button at the bottom of the application.

In the SQL Editor, you can run the SQL and view the resulting data set in a grid, look at the explain plan for the SQL, view the dbms output, and look at the SQL statistics. You can view the SQL data output in a grid format or as a single record view.

Following features are available in SQL Editor

1. Execute SQL: By clicking on , or by pressing the F9 key, you can execute the selected SQL statement. If there is only one statement in the window then that statement will be executed. If the SQL statement is selected (highlighted), then it will be executed and other statements will be ignored. If there are more than one SQL statement but none is selected, and the execution command (F9 key) is issued then an error will be generated saying invalid SQL statement. Depending on which tab is selected, SQL execution will generate data, explain plan, dbms output or SQL statistics.

2. Execute Current SQL: You can execute the current SQL without highlighting it by placing the cursor somewhere on the SQL statement and clicking or by pressing Ctrl+Shift+F9. You do not need to highlight the current SQL statement. Simply place the cursor on the SQL statement and it will be executed. The beginning and the end of the SQL statement should be separated from other SQL statements.

3. Execute multiple SQL statements: Multiple SQL statements can be executed in a serial manner using SQL Editor. Statements should be separated by a semi-colon. SQL Editor will parse through the SQL statements and execute them one-by-one. You can execute multiple SQL statements by clicking on or pressing Shift+F9. Also, a SQL file can be loaded and executed at the same time by clicking on “Load and Run Script” from the “SQL and PL/SQL” menu option. Create / Insert / Update / Delete statements can be executed in serial manner. All the errors generated during SQL execution are collected and displayed in a message box.

4. Export data: Data can be exported in a comma separated value (csv) file, Excel file, Tab Delimited file, as insert statements, or other user defined delimiter. Click the to start the export process. Select the option you want to export the data as and click OK. If you selected ASCII (csv), Tab delimited or Other delimiter then you will be prompted to enter a file name where the output will be stored. If you selected Excel(xls) then the data will be exported to an Excel file and the Excel workbook will open up once the data has been exported. The user can then save the Excel file. Guggi for Oracle will not save the Excel file.

5. Formatting Options

a. Center the text: Click on and the line where the cursor is located will be placed in the center of the window.

b. Left justification: Click on and the line where the cursor is located will be left justified.

c. Right justification: Click on and the line where the cursor is located will be right justified.

d. Background Color: Background color of the SQL Text window where SQL statements are typed can be set by clicking on .

e. Font Setting: Font of the SQL text can be set by selecting “Font” option from “SQL and PL/SQL” menu option.

f. Upper Case: Selected text can be changed to upper case by clicking on

g. Lower Case: Selected text can be changed to lower case by clicking on

h. Init Cap: Selected text can be changed so that each word starts with an upper case by clicking on

6. Commit: You can commit a DML command by clicking on

7. Rollback: You can rollback a DML statement by clicking on .

8. Comment Text: Text can be commented out using the “--” syntax by selecting the text and clicking on

9. Uncomment Text: Text can be un-commented by selecting the commented text and clicking on

10. Undo: Previous changes can be undone by clicking on .

11. Redo: Previous changed can be re-done by clicking on .

12. Display Schema Tables: Schema tables can be displayed by clicking on . Click on the table in the selected schema and its name will be copied to the SQL Text editor.

13. Display Schema Tables and Columns: Schema table columns can be displayed by clicking on . Click on the column in the selected schema and table combination and its name will be copied to the SQL Text editor.

14. Recall SQL Statements History: Click on and all the statements in the SQL history will be displayed in a separate window. The statements will be ordered by date and time of their execution with most recent displayed first. A SQL statement is added to the SQL history every time it is executed. You can copy the SQL statement to the SQL Text Editor by clicking on it.

15. Retrieve previous / next SQL statements in the session: SQL statements executed in the session can be retrieved by clicking and buttons. 100 statements are stored in the session buffer. Every SQL statement executed is also stored in the SQL history file.

16. Single Record View: Click on in the Data Tab just above the grid, and the data from the grid will be displayed in a single record view. You can scroll forward or backward using the single record view.

17. Scroll through result set: Data returned by the query can be scrolled through by clicking on . Clicking on will return all the records. Clicking on will cancel retrieval of records.

Other Features

1. Intellisense: SQL Editor has been designed to display objects and their attributes such as columns of a table or a view. When you press the dot key “.”, the editor determines the name of the object and grabs the details. You can select the column name or the object name by selecting the object from the popup and pressing the tab key. You can close the popup window by pressing the escape key.

2. Object Details: You can get the details of an object by placing the cursor on the object and pressing Ctrl-Q. A window with details of the object will open up.

3. DBMS Output buffer: DBMS output buffer can be set by entering the appropriate value in the Buffer Size box and then either pressing the Enter key or clicking outside the box.

4. Standard Editing Features: Text can be copied, cut, selected, tabbed, deleted, pasted and printed. Text can be saved into a file, or a file can be opened and loaded into the SQL text editor.

How to …

1. View the dbms output: Execute a PL/SQL statement that has a print output statement in the code. For example,

BEGIN

Dbms_output.put_line (‘This is a test’);

END;

The above anonymous PL/SQL block will print “This is a test” to the output screen. You can also execute a stored procedure that has a dbms_output. For example,

BEGIN

proc_with_dbms_output;

END;

If there are no embedded dbms_output statements then there will be no output to the screen.

SQL Statistics Manager

SQL statistics manager is a useful tool to monitor the worst performing SQL or “Hot” tables. Quite often, DBAs have to determine why the database is not performing fast enough, or may have to redesign tables or re-assess the disk and tablespace layout strategy, or analyze worst performing SQL in terms of disk reads, buffer reads, sorts or executions. SQL Statistics Manager identifies such problematic SQL for further action. The tool can be invoked by clicking on or by selecting SQL Stats Manager from Tools menu option.

Select the Schema from the drop-down menu that needs to be analyzed for worst performing SQL. If you want to do analyses by considering all the schemas then leave the schema drop down box blank.

Select the task that you want perform. Options available are

a. View All SQL

b. Determine Hot Tables

c. Get Top 20 SQL by Buffer Gets

d. Get Top 20 SQL by Disk Reads

e. Get Top 20 SQL by Executions

f. Get Top 20 SQL by Rows processed

g. Get Top 20 SQL by Sorts

SQL statement is displayed in the box called SQL String. SQL statistics are also displayed in the Stats box.

Temporary Objects

Details about the SQL running in the temporary tablespaces can be viewed by using Temporary Objects Manager. Segment type or the current operation, temp tablespace size occupied by the current job etc. can be viewed using Temporary Objects manager. This feature is available only for Oracle 9.2 and higher versions.

Click on to launch temporary objects viewer, or Goto “Database and DBA” in the menubar and select “Temporary Objects”.

Click on a record in the grid. The SQL statement will be displayed below in the tab labeled as “SQL”.

Click on Explain Plan tab and the execution plan of the SQL will be displayed.

Click on button on the toolbar and the grid displaying jobs being executed on the Temporary tablespaces will be refreshed.

Upload Binary Data and View Image

Binary data can be uploaded as BLOB data type using Guggi for Oracle. Binary files such as images, Microsoft Word documents, spreadsheets etc. can be saved in the database as BLOB objects. Binary files can be uploaded using the Upload Binary Data tool

1. Select the schema and the table where the BLOB data (binary file) will be stored

2. Select the BLOB column in the selected table.

3. Select the binary file from the local disk. Check the check box if the binary file is an image file and you want to view the image.

4. Select the record from the data grid where the binary file will be saved. You can filter the records by clicking on the Filter Data

5. Once the record where the binary file will be saved is identified, click the save button and the file will be uploaded to the database. Once the file has been uploaded, a message will be displayed stating that the file has been successfully saved.

You can save as many files as you want by repeating the above steps.

User / Schema Role Manager

Database level security can be managed using the User / Schema Role Manager. The tool can be launched by clicking on from the main toolbar or selecting “User/Schema Role Manager” from Tools menu. The user would need DBA privileges to use this tool.

The user can set security privileges around a schema or a role. If the user/schema option is selected from the dropdown box, then 4 tabs are displayed on the right hand side. These tabs are:

General – sets the user’s password, default tablespace, temp tablespace etc. The fact that the user is database password authenticated, external user or global user can be set in this tab.
Privileges - System and object level privileges can be assigned or revoked using this tab. The feature to set object level privileges is under development.
Roles - Roles can be assigned and revoked using this tab.
Quota – Tablespace level quota can be assigned to users using this tab.

If the role option is selected from the dropdown box, then 3 tabs are displayed on the right hand side. These tabs are same as above except that tab for Quota is not available.

How to use the User/Schema Role Manager

Select a user or role from the left hand side list. Depending on whether the user is set up as database authenticated user, external user or global user, appropriate data would be displayed on the right hand side under the General tab.

2. To change password: Enter the old and new password and click on

to execute the

change. This is done under the General tab.

3. To change default tablespaces: Assign the default and temporary tablespaces and click

on to assign them to the user. This is done under the General tab.

4. To set “Password to Expire” and “Lock the account”: Select the appropriate checkboxes and

click on to confirm the settings. This is done under the General tab.

5. To assign a system level privilege: Select the system privilege under the Privileges – System

tab, and click on to confirm the changes.

6. To revoke a system level privilege: When the user is selected, all its system privileges get

selected under the Privileges (System) tab. Uncheck a privilege and click on to revoke

the privilege.

7. To assign a Role: Select the role under Roles (Role Default) tab, and click on to confirm

the changes.

8. To revoke a role: When the user is selected, all its assigned roles get selected under the

Roles (Roles Default and Roles Admin) tab. Uncheck a role from Roles Default and click

on to revoke the role.

9. To assign tablespace quota : This option is available only for User/Schema and not for Roles.

Highlight the tablespace in the grid under the Quota tab. Select “Enter Quota” and enter

value in KB, “Unlimited Space” or “None” and click on to make changes to the

tablespace quota assigned to the selected user.

Repeat steps 1-8 to make changes to the Role instead of User / Schema.

Toolbar Buttons:

Click to make security level changes to a given user
Click to close the window

View Linked Data Manager

In a relational database, tables are often related to each other via primary key and foreign key relationships. This is done to maintain data integrity in the database. Linked data between such tables can be viewed using View Linked Data Manager. The tool can be invoked by clicking on

To view Linked Data

1. Select the schema from the drop down box.

2. Select a table from the list of tables

3. A data grid will become visible that will display all the records from the selected parent table. Records to be displayed from the parent table can be filtered by using the record filter. Records can be filtered by clicking on the and entering appropriate conditions.

4. Select a record from the grid. If the parent table has only one child table then a second data grid will be visible displaying the records from the child table. Only those records that have a foreign key value equal to the primary key value in the parent table will be displayed. If the parent table has multiple child tables, a list of children tables will be displayed. Select a table from the list and its records will be displayed in the second data grid.

5. Select a record from the child data grid and data from its child (grand-child table of the main parent table) table will be displayed. If there are multiple children tables then the user will be prompted to select a child table.

6. You can view up to 4 levels of child tables for the main parent table selected in Step 2.

All the grids will be cleared when the parent table is selected again. This feature is useful in quickly looking at linked or related data without having to know the names of columns, relationships, or writing SQL.

Oracle is a registered trademark of Oracle Corporation. DB2 is a registered trademark of IBM Corporation.