GUGGI for ORACLE User Guide
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

-
Sessions Manager

-
SQL Editor

-
SQL Statistics Manager

-
Temporary Objects

-
Upload Binary Data and View Image

-
User / Schema Role Manager

-
View Linked Data Manager

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
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 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.
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.
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.
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 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 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.
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 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.
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.
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.
- 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.
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 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
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
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.
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.
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 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 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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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.
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
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
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.
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
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.
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
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
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 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 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 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.
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.
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.
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
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.