Sql180 Developer

• Download demo - 1,022.29 KB
• Download source - 1.57 MB

Introduction

This article will introduce you to a graphic tool called "Sql180 Developer", which is intended to be used by Oracle developers and Oracle DBAs. If you are an Oracle developer, you can easily write and compile PL/SQL code with this code or just work and develop in an Oracle environment. If you are a DBA or an Oracle security specialist, you can manage the DB, monitor it and its performance and also check security issues and problems about your Oracle Database.

Background

The development of this project was triggered due to our college studies and from our experience as DBAs at the place we work. We searched extensively for a simple and easy tool for developing, specially for managing and monitoring our databases at our facility. We found some tools but most of them were complex and not relevant, so we decided do develop our own tool.

Brief Description

Sql180 Developer enables you to do the following tasks:

• Editing
• Compiling
• Correcting
• Optimizing
• Querying

This software also provides several other tools that can be helpful during everyday PL/SQL development or DBA's routine activities.

First of all, the software has a "SQL Window" that provides you the ability to execute any Oracle command (DML, DDL, SELECT). The data retrieved from SELECT statements is shown with the DataGridView object. You can also show the explain plan of your SELECT statement in order to optimize your code. We have also implemented a command-line tool called "Command Window" that imitates the way SQL*PLUS works with the 'SQL>' prompt. Both "SQL window" and "Command window" can save a history of your commands that were executed and can manage Oracle transactions. All the fetching of Oracle data and the handling of Oracle connections and transactions were implemented by the ODP.NET Framework that provided us the classes of OracleCommand, OracleConnection, OracleDataReader, etc. Here, for example, is the "SQL Window":

We also implemented a PL/SQL editor that parses the text you enter and colors Oracle keywords and comments. With the editor, you can of course compile and store the PL/SQL code in your DB, or check for compilation errors that are marked on the text itself with yellow color. If the text is a SELECT statement, you can also lay out the SELECT command. All these features were implemented by extending the RichTextBox class. For example, this is an editor that shows the code of an Oracle trigger: 

The Object browser - This is a very useful tool located at the left of the main screen which allows you to browse all Oracle objects stored in the DB. It has the following filters: "my objects" to show the current logged user's objects, "all objects" to show all objects that the current logged user can see, "invalid objects", and "all-non system objects". With the browser, you can also manipulate any object you want. You can view, edit, compile, rename, etc. (by right clicking on the relevant object).

The Objects creation wizards - Our software also has wizards for creating, editing and viewing the common Oracle objects (tables, views, synonyms, jobs, users, roles, DB links, etc.) You can create a new object by pressing the New menu or by right clicking the Object browser. All object wizards have a tab called "DDL" that can show you the exact real Oracle commands of the object creation/altering, that will be executed when you press the "OK" button. For example, here is a screenshot of the wizard for creating a new table. It has the following tabs - general, storage, constraints and indexes (You can also see the Object Browser on the left):

Another important advantage is the Tools menu that provides you with some more benefits for common tasks of the DBA. It has the session screen that helps the DBA to see the activity on her/his database, and manipulate the logged sessions. There is also the Object Finder that can help you find any object in a quick and easy way. There is the Object Compiler that can find all invalid objects of your current schema and compile them for you with one click. You can also compute statistics on your schema's objects or even on all DBs (if you have the right permissions, of course...).

The highlight of our software is, in my opinion, the advanced DBA & Security options that really improve the DBA's work in real life! These options are provided by the DBA and Security menus on the main screen. Both options are enabled only if you logged to the database with the SYSDBA/SYSOPER privilege.

At the DBA menu - you can find the "General" screen. This screen displays global system 'health' parameters and general details of your database. On this screen, you can examine, by the different shown ratios, how much sorting, I/O, parsing, or CPU activity is performed on your DB. In one click, you can find out what is wrong with your DB performance!

You can also check the memory of your Oracle instance by showing the current SQL area - there you can find the highest loading activity on your database, and from where it is coming. The DBA menu also provides you the ability to investigate your tables and indexes: It can check for you if there are any unnecessary indexes that cause bad performance, columns with foreign keys that have no index on them, tables without indexes at all, and so on. It also recommends you to build indexes that can improve your system performance. The DBA menu can also provide you reports - like the "free space" report (This report checks for each tablespace about how much free space is left for it).

Here is a screen shot of one of the performance screens that shows you with colors of red, yellow or green as to what the state of your DB is.

The last thing is the Security menu, that helps you to investigate security breaches and issues that occur in your system. It can show you, for example, users that have wick passwords (that can be cracked easily), or users with default profiles (which is also bad). It can also check for very powerful privileges and dangerous packages stored in the DB, and who has them, or has privileges to them. It also checks the auditing state of your system. The following screenshot is an example for the checking of dangerous or public packages:

Assumptions 

1. Our project has been tested on an Oracle 10g environment. In order to use this code, you must have a database in this version to connect to and also an Oracle Client installed on the computer which runs our software. 
2. The code was written with .NET Framework 2.0, and tested on Windows XP and Windows Vista.

Known Issues

1. There is a bug - When you query from a table, then modify the table metadata (add/remove columns), and then invoke the same case sensitive query, the query will ignore the metadata changes (it will provide the previous metadata from a cache).
2. The command window executes Oracle DDL/DML commands perfectly, but for SELECT statements, it will not show the data retrieved. You can use the SQL window for that. 

History

• 25^th October, 2008: Initial post