Oracle Database Management Tool - New and Improved

FINISHED!!!!

Over the past couple of months I have been working on an Oracle Management tool to help manage Oracle Database Servers in a Windows environment. The tool morphed into a decent sized application with over 10,000 executable lines of code. It is written entirely in C# utilizing the ADO.NET classes for data access.





Upon opening the application a login screen is presented. The database connections are populated automatically from the tnsnames file being used by Oracle.


The application has the concept of roles which are determined based on what system privileges a user has on the Oracle Database server. These roles determine what features are available in the management tool.


Below are the features of the application:

Query Editor

This area lets a user execute standard SQL queries against any user with parameters. The results come back as an output window (number of records affect, returned, updated, etc) and select statements return a data grid.










The ability to add parameters is also available. Parameters are detected automatically using the : or @ designated syntax.





Create User



Here one can create a user very quickly by typing in the user's name, password, selecting a tablespace and give the user roles. The radio buttons on the left are the most common used in our environment and automatically select the related oracle system privileges. The privileges are loaded from the oracle database dynamically. There is validation for the username and password (existing, length, character usage).



Edit User

Edit user is similar to create user. Here one can change a user's password, default tablespace, roles, and view the current roles / privileges assigned to a user.





















Delete User

Delete user allows one to remove one or more users at a time. Due to the cascading options with oracle users this can be time consuming so the ability to select - click delete - and leave to complete is extremely useful as a time saver.










Import Dump File

Importing dump files for Oracle can be time consuming and painful. There are many requirements to importing a dump file and syntax rules for command line options that must be followed. To make this process easy now one must create a user via the Create User feature, browse to a dump file and click import.

This feature is only available when the application is run directly on the Oracle Server as it requires the Import Utility provided from oracle (imp.exe).

There are certain checks in place to ensure this will work without errors occurring. There is also a log file created. It is scanned after the import has completed and prompts a user to open it if errors are detected. It will not let you import if there are tablespace conflicts and will notify you of what needs to be changed.


Export Dump File

Exporting a dump file also must be run directly on the database server. This option exports the dump file with standard options enabled and then opens up the file location as to where the dump file / log file were created.






TableSpace Management

The tablespace management section lets you create tablespaces, view details about the tablespaces, and add datafiles to the tablespaces.

Create Tablespace

This feature makes creating tablespaces a breeze. This isn't a widely used feature but will be helpful for development groups. Viewing tablespace data and adding datafiles also make tablespace management simple but effective. Note that all naming for data files is automatic and conforms to oracle standards.





System Information


System information provides a basic snapshot of the host operating system as well as the database server OS environmetn information. The parameters sections (Get Buttons) return parameters from the Oracle Database server.








Help

There is a help file in .mht format that describes each feature in detail as well as provide common Oracle best practices, troubleshooting methodology, and Oracle Database logical design.




Retrospective

This was my first major development project. While I bounced some conceptual ideas off some of my colleagues I did not seek any assistance in writing the code. This was a great learning experience for me. I am now extremely comfortable using C# / Winforms / ADO.NET. I gave a demo of my product to a development team (a group of highly experienced developers). They were all pleased with the tool and provided great feedback for me to use moving forward. The hardest part for me during the development of this tool was to find the time to do it. I started the tool on November 22nd and finished it January 28th. Not too bad considering I took two weeks off in December.


My next project:

Create a new tool the facilitates an automatic daily creation of multiple databases across multiple database platforms for testing and development purposes. I already wrote one of these programs a few months ago but it needs additional features. After looking at the code I wrote then - I realized it would be better to start fresh and create an application worthy of upgrading and progressing. This tool is about 30 percent complete and has the following major sections:

• Customized data model (database) created using Visual Studio Database Edition (Data Dude) with 16 separate tables all with strict foreign and unique key constraints
• UI Management tool for setting up database creation plans
• Console Application to execute the database creation plans
• Unit Tests (of course we can't forget these!!!!)

Once finished - I will post a snapshot of the UI and a retrospective of this process.