50 SQL Experts Reveal Their Top 3 Tools for SQL Development and Administration

There are a large number of tools available for Oracle SQL developers and DBAs to use. It’s hard to know which tools are useful, will help you get the job done, and are widely used so you can get support easily.

However, there are people who have a lot of experience working with SQL, and I’ve asked 50 of them to share their answers to this question:

If you could only use 3 tools as an SQL developer or DBA, what would they be?

Let me tell you, the insights I received from these 50 database experts were nothing short of amazing. I’ve listed them all below, along with a summary of these tools at the end.

Adrian Billington: SQL*Plus, Komodo Edit/Notepad++/vi, SQL Developer

Website: oracle-developer.net

My three tools are SQL*Plus for running scripts/traces/explains etc, Komodo Edit/Notepad++/vi for development (depends on what O/S I’m working with at the time) and SQL Developer (general querying/browsing, including some customised reports).

 

Alexander Anokhin: SQL Developer, SQL*Plus, PL/SQL Developer

Website: alexanderanokhin.com

Well, I normally use SQL Developer, SQL*Plus, and PL/SQL Developer is also a very good tool.

 

Biju Thomas: SQL Developer, Cloud Control, EDB360

Website: http://bijoos.com/oraclenotes/

Twitter: @biju_thomas

My focus now-a-days is troubleshooting performance problems and database advisory. The top three tools I use frequently are

1. SQL Developer
2. Oracle Enterprise Manager Cloud Control
3. EDB360 (by Carlos Sierra)

 

Bobby Durrett: SQL*Plus, Textpad, Toad

Website: www.bobbydurrettdba.com/

Twitter: @bobbydurrettdba

I think mine are sqlplus, Textpad, Toad.

I’m doing stuff with Python now but if I could only pick 3 those are the most important to my work today.

 

Carlos de Leon: SQL Developer, SQL*Plus, LiveSQL

Website: sql.standout-dev.com

Twitter: @carlosdlg

These days I mostly use SQL Developer.  It is just too feature-rich and easy to use, that it became my tool of choice in most situations.

I still feel the need to go back to SQL*Plus from time to time, though.

And the third tool I’m really happy to have access to is Live SQL. I love how easy it is to test things there at any time, from any device.

So, in summary, these would be my 3 preferred tools:

• SQL Developer
• SQL*Plus
• Live SQL

 

Carlos Sierra: SQLd360, SQL Developer, AWR views

Website: carlos-sierra.net

Twitter: @csierra_usa ‏

2. SQLd360 by Mauro Pagano
3. SQL Developer
4. AWR views such as dba_hist_sqlstat, dba_hist_active_sess_history and dba_hist_sql_plan

 

Cary Millsap: Method R Workbench, SQL Developer, SQLcl

Website: carymillsap.blogspot.com

Twitter: @carymillsap

It’s kind of like asking a dad to judge his daughter’s beauty pageant, though. Of course, I’m going to say the Method R Workbench. It is the only Oracle-related software I use every day.

Second place would be Oracle SQL Developer. Third would be SQLcl.

 

Chris Antognini: SQL Developer, Lighty, TVD$XTAT

Website: antognini.ch

Twitter: @chrisantognini

FWIW, I have only three tools that I regularly use in addition to the “standard binaries” (i.e. Oracle Database and Enterprise Manager):

• SQL Developer to interact with databases through SQL when SQL*Plus isn’t enough or good
• Lighty for Oracle to assess performance-related issues when Enterprise Manager and/or the Diagnostic pack isn’t available
• TVD$XTAT, my own profiler, to analyze SQL trace files beyond TKPROF

That would be the short answer.

 

Chris Buckel: Enterprise Manager/Grid Control/Cloud Control, SQL*Plus, RichMon 4 Oracle

Website: flashdba.com

Twitter: @flashdba

Gradually, over the last five years, I’ve moved further and further away from my past as a database engineer and consultant. Today, I work in sales, which means I draw boxes on whiteboards and then leave the room long before anybody has to implement what I’ve drawn. By the time some poor Oracle guy (me, ten years ago) gets around to writing the code, I am on a luxury yacht, counting my money and laughing maniacally. Muuuuuhahahahaha.

But, for a long time, I was a DBA and database troubleshooter – including a period when I worked for Oracle Corporation as an Oracle RAC and/or Performance Tuning subject matter expert. I would frequently be parachuted into some crisis situation at a company I’d never visited before (as a supposed “expert” from the vendor) and told to “FIX IT” before whatever was broken made the news. During these lonely experiences, there were certain tools that I could not live without:

3- Enterprise Manager / Grid Control / Cloud Control / whatever it’s rebranded as today

Ever since Kyle Hailey and colleagues redesigned the performance pages of OEM 10g, this tool has been a fantastic visual way to find out just what the hell is going on in a live database. While the Wait Interface was the feature that elevated the Oracle Database to the next level of performance tuning, it was the OEM performance pages that really exposed it to a legion of desperate DBAs.

2- SQL*Plus

I know, obvious. But the thing about being sent to an unknown company with an unknown Oracle setup is that you just don’t know what tools will be installed (or licensed, for that matter). And you almost always won’t be allowed to install anything new on a production environment. So SQL*Plus, by virtue of being ubiquitous, is often your only friend. But it is so powerful, so universal and so customisable that it’s the best friend you can ask for. SQL*Plus is almost unlimited in its functionality – and in the hands of a virtuoso conductor, it really can sing. I still get a kick out of seeing what Tanel Poder can do in SQL*Plus – the guy is a genius.

1 – RichMon 4 Oracle

This won’t be a popular choice, because only a limited number of people have been given a copy of this tool by its author, Richard Wright. But I was lucky enough to be one of those people and, my word, I relied on it every day. RichMon has the ability to graphically display information from the wait interface in real-time, which is brilliant. But, even better, it can be executed from a USB key without the need to install it. And – as previously discussed – when you go to a customer site and have no access to OEM or any other graphical tool, but you aren’t allowed to install any software, RichMon really used to come into its own.

Sadly, Richard (who works for Oracle but developed RichMon as a personal project) felt the need to limit access to the tool because of the feeling that customers might consider it an alternative to Oracle’s official (and therefore licensable) product, Oracle Enterprise Manager.

 

Connor McDonald: SQL*Plus, SQL Developer, Application Express

Website: connor-mcdonald.com

Twitter: @connor_mc_d

1) SQL Plus

Why:
I spent more than a decade working with clients on-site, and it is the single tool that is guaranteed to be there for every single client on every single version of Oracle on every single platform.
As a result, I’m fluent with it and have hundreds of “helper scripts” to make using it a breeze. I’m now dabbling with SQLcl because it seems the logical upgrade.

2) SQL Developer

Why:
Browsing the database is best done with a GUI, and the integration with Data Modeller and other database components (REST etc) make it an obvious choice. Why people still choose to pay for such tools absolutely astounds me.

3) Application Express

Why:
I mean seriously…just wow. It scares me that you see IT projects run into years building components on 37 disparate technologies with 90% of the time spent on integration when the same thing could be cranked out in a couple of days in Application Express. I’ve lost track of the number of times I’ve seen apps written in Application Express that the customer almost views with skepticism, simply because they cannot believe such incredible results could have been delivered in the short time frames.

 

 

Daniel Fink: SQL*Plus, VI/Textpad, Admitting “I don’t know”

Website: kairosdatasolutions.com

My 3 favourite tools for SQL development as an Oracle DBA. So here we go… after a bit of background.

My first Oracle experience as a production support analyst (for lack of a better word) on Oracle 7.0 Oracle Parallel Server (precursor to RAC) on OpenVMS. OPS was not a good product, more like POS (and that does not stand for Point Of Sale, but rather Piece Of Sh…). Loved OpenVMS as I had cut my programming and support teeth on VMS. The database we were supporting was used more as a record store than anything else. The data was stored in RAW format (no fields…one big data string) and the ‘indexes’ were also Oracle tables storing the Index entries in RAW format (again….one big data string). For the last 25 years, I have worked on a variety of systems, OpenVMS, Unix (various flavours), Linux, even Windows. As a DBA contractor/consultant, I had to be prepared to work on the technology least common denominator.

As a Bitter Twisted Old Command-Line Oracle DBA here are my 3 favourite tools.

SQL*Plus

Every Oracle installation I have worked on always had SQL*Plus command line. The scripts I wrote for Windows worked on Linux which worked on Unix (and even my personal Mac). Some clients/employers used TOAD, some used SQL Developer, ALL had some version of SQL*Plus. With a little imagination and ingenuity, I was able to write scripts that would write scripts, create and call dynamically named scripts, generate output files with schema.table_report_YYYYMMDD.out. I still frequently use scripts that I wrote over 10 years ago (granted they are updated a bit for new versions as need be… sometimes they are the same for Oracle 9 as Oracle 12.

It’s the one tool in my toolbox that I can depend on being useful EVERYWHERE I go with Oracle (at least so far…). It isn’t pretty, can’t write output in native XLS format (but CSV is a piece of cake!), and, let’s face it, SQL*Plus HTML markup leaves A LOT to be desired. But it works. It’s the counterpart to my 2002 Toyota 4Runner. Not pretty, no amazing bells and whistles, but it goes EVERYWHERE I need it to go.

And when the only option is a command line at the server because the network is down and you are sitting in a server room at 4 AM, SQL*Plus will be there.

vi/TextPad

What goes best with a command line? A good old fashioned text editor of course! I know there are fancy development tools out there, but vi (Unix) or TextPad (Windows & worth every penny I pay for it) enable me to write Q&D scripts (quick and dirty) and scripts to write and run other scripts. Notepad++, TextWrangler – all good options.

BUT… I have spent years learning TextPad (love the search and replace, can you imagine how good I could be if I could ever master regular expressions?) so it is very comfortable and I can be very productive. I love the grep-type function to search files in Windows for strings…especially PuTTY logs when I forget the exact command I need to repeat from a week ago.

See previous comments about my 4Runner…

“I don’t know”

A fellow DBA and one of the smartest people I know once told me that the key to learning was to admit that “I don’t know”. If you are not aware of the bits of knowledge you lack and are not willing to accept your temporary ignorance, you won’t learn and grow. Never be afraid to embrace your lack of knowledge and then go learn.

One of the things I loved about training classes was learning something new. Not as a student… but as the teacher. I loved it when a student asked a question I could not answer. It stopped me dead in my tracks and I said “I don’t know”.  An overnight search of the documentation (long before Google) on my laptop later…I found that the answer to “How do you clear the screen in SQL*Plus?” was “CLEAR SCREEN” (or “cl scr” for those who grew up with VMS).

Admit what you don’t know, use your curiosity tool and fill in that knowledge gap.

 

David Fitzjarrell: OEM, SQL*Plus, Tkprof

Website: dfitzjarrell.wordpress.com

Twitter: @ddfdba

Here is my list:

• OEM
• SQL*Plus
• Tkprof

 

Frits Hoogland: SQL*Plus, Ansible, Git

Website: fritshoogland.wordpress.com

Twitter: @fritshoogland

I think I am by no means a regular DBA (anymore). My main work is jumping into problems when they escalated all the way up to the team I am in.

In fact, nowadays I do most of my work ‘by proxy’, which means I don’t even get access, I just ask for a summary of the problem and based on the summary I ask log file content and SQL to be run.

I also do little to no database side (meaning PL/SQL) programming, this is an important fact because doing that kind of programming would require tools that are better suited for it than the tool that I am going to mention.

So what I think are vitally important tools for a DBA are:

• sqlplus
• ansible
• git

Sqlplus because it’s available everywhere.

Ansible because it allows to automate repetitive tasks, and execute them in parallel on as many hosts as you like

Git because that allows you to store any important changing set of files and understand changes. not only your own scripts, but also the infrastructure setup scripts (ansible), and anything else.

 

Garth Harbach: SQL*Plus, SQL Developer, mRemoteNG

Website: snapdba.com

Twitter: @SnapDBA

1) SQL*Plus

I use this more than any other tool. It’s so quick and simple to use, yet also very powerful when used with the collection of scripts I’ve built up over the years. You don’t have to worry about licensing and it’s guaranteed to be available wherever you’re working! It’s also customisable (e.g. login.sql), but remains universal so you always know what you’re going to get with it. I’ve started using SQLcl a bit more recently, but still find myself going back to SQL*Plus out of habit!

2) SQL Developer

This is sort of my fallback to the above when SQL*Plus can’t quite cut the mustard! I tend to use SQL Developer when I know something is going to be easier to produce or visualise than the good old command line. If I need to debug code, run custom reports (which I love BTW), or simply browse through database objects etc. then SQL Developer is the tool for me. It’s feature-rich with some fantastic and ever-expanding capabilities – half of which I haven’t explored yet.

3) mRemoteNG

This third one was a tricky one. I almost went for Oracle Enterprise Manager Cloud Control as I find that an invaluable GUI. In particular the SQL Monitoring pages, and ease in getting to the root cause of performance problems, with tools like Emergency Monitoring and Real-Time ADDM, BUT…if you don’t have access to the Diagnostic & Tuning packs, it’s not so useful, and there is no guarantee it’s available to use wherever you go.

So… I went with mRemoteNG instead – technically it’s not a database tool, it’s a tabbed remote connection manager that I’ve used it for years now. It’s free. It supports RDP, SSH, VNC plus other protocols, and once you’re connected, you’ve access to every other tool on that host :) Whether that be SQL Server Management Studio via an RDP session, SQL*Plus on an Oracle Linux machine from an SSH (PuTTY) connection, or reconnecting to a VNC session to finish off an upgrade. The External Tools option allows for further customisation, such as calls to WinSCP or copying of passwords. Granted for SSH alone, it’s not quite as good as SuperPuTTY, but for supporting a variety of databases on multiple platforms it’s the only tool I use now.

 

Hemant K Chitale: SQL*Plus, SQL Developer, OEM Grid Control/Cloud Control

Website: hemantoracledba.blogspot.com

Twitter: @HemantKChitale‏

Honestly, my preference is SQLPlus.  I haven’t yet used SQLCL.

Even for PLSQL code, I use notepad or vi with SQLPlus!

My second choice, which I have installed and rarely use, is SQL Developer.

I find that the L1 Database Support team is comfortable with OEM GC/CC. I use it only when I have to send a screenshot of a chart to someone!

 

Ilmar Kerm: Ansible, Jenkins, Bitbucket/Git

Website: ilmarkerm.eu

Currently, for my DBA job the following tools are essential:

• Ansible
• Jenkins
• Bitbucket/git

Automation is key, the environment is growing and it would be difficult to manage it without automation. We are trying to follow Infrastructure as a code principle, so all changes to our Oracle infrastructure are only done in Ansible playbook files, committed to git and then automatically executed by Jenkins.

 

Jeff Moss: SQL Developer, SQLcl, Enterprise Manager

Website: oramoss.com

I guess it depends on what you mean by SQL Tools.

In my eyes, SQL Developer is brilliant and the new SQLcl is also excellent. To go with that, Enterprise Manager would probably round off my 3.

SQL Developer provides, for free, a very effective IDE that allows me to do my job really quickly and efficiently, whether it be design, development or DBA activities.

SQLCl improves on the venerable SQL*Plus by adding many cool features including loading CSV files with a  simple load statement and inline editing.

Enterprise Manager provides a wealth of features to easily run, upgrade, tune and manage the Oracle estate all through an intuitive interface – although I’m a scripts guy I do find EM to be a very useful tool particularly in the tuning department.

 

Jeff Smith: Oracle’s Tools (SQL Developer, SQLcl, SQL*Plus)

Website: thatjeffsmith.com

Twitter: @thatjeffsmith

Why I think our users use our tools…

• they’re everywhere, 5M+ users worldwide. Not hard to find someone with experience, not hard to get experience either
• they’re free, but also supported/maintained by Oracle
• we have a GUI for desktop folks, a CLI for shell hackers, and an upcoming web version for folks w/nothing installed by Chrome and an internet connection
• we spend a lot of time and energy making them useful and making ourselves available to our customers – if there’s a problem, we want to know about it so we can fix it

 

Joel Kallman: SQLcl, SQL Developer, Quick SQL

Website: joelkallman.blogspot.com

Twitter: @joelkallman

1. SQLcl – I always use command-line tools first. So SQLcl gets my first vote (along with SQL*Plus). It’s fast, it’s easy, it’s quick, it’s small and it’s available everywhere. With SQLcl, it’s very easy to generate the DDL for an object, generate HTML, JSON output or INSERT statements from table data, and you can even export an APEX application!

2. SQL Developer – SQL Developer is really a wonderful tool with a wealth of features. I often use the Data Modeler against an existing schema or DDL file so I can easily visualize relationships. Importing data via native Excel is a breeze. I always use it when I’m developing queries in an iterative fashion, as it’s easy to step through your history, having multiple statements in a window, and selectively execute only portions.

3. Quick SQL –  This is a service that I use all the time when helping internal customers start new applications. When we begin, I always start with Quick SQL to quickly write down the mental model of what they think they want to maintain via data. It’s easy to express relationships and add features to my data model with no coding. What I get out of it is a well-formed, complete set of DDL, complete with referential integrity (and indexed foreign keys!) which I can use to begin writing an application against. For opportunistic applications, it’s a must-have.

 

John Hallas: SQL Server Management Studio, SQL Developer, Toad

Website: jhdba.wordpress.com

SQLServer – Management Studio

Oracle – SQL Developer

Toad as a 3rd choice but SQL Developer would be preferable

 

Jorge Rimblas: APEX, SQL Developer, Sublime Text, SQLcl

Website: rimblas.com/blog

Twitter: @rimblas

 

Kamran Aghayev: SQL Developer, Toad, Cloud Control

Website: kamranagayev.com

Twitter: @kamranagayev

I’m using SQL Developer and Toad mostly. For checking the performance of the database, Oracle Cloud Control is best.

 

Kellyn Pot’Vin-Gorman: Enterprise Manager Cloud Control 13c, AWR and ASH, AWR Warehouse

Website: dbakevlar.com

Twitter: @DBAKevlar

1. Enterprise Manager Cloud Control 13c- This is a full infrastructure tool, not just a database tool and as the DBA knows, the database is guilty until proven innocent. With tools that allow for management of the database, deep performance and detail research, along with automation of jobs and tasks, it is still the go-to tool for most DBAs to monitor, manage and alert on their full environments.

2. AWR and ASH- When someone tells me they are having a database-wide challenge, I’m going to request an AWR report first. I can quickly identify environment issues, as well as tell what can be safely ignored. Oracle experts took extensive time building out a product that goes above and beyond what Statspack ever did, providing important data to tune for time so you’re not wasting time.

3. The AWR Warehouse- Rarely are performance problems simple and there are times where canned reports won’t do. DBAs also don’t want to be the performance hit on the database attempting to retain and query historical AWR/ASH data. Having a centralized repository that is available to the DBA to answer specific questions about problems or to comparisons of a previous year to the current year or to see how a system has evolved in resource usage over time is incredibly powerful. This is what the AWR Warehouse provides the business and if you already monitoring with Enterprise Manager, this is pretty much a free product from Oracle. The warehouse database comes with a limited EE license, you just need to install the 12c database on its own host and configure it via Enterprise Manager 12.1.05 or 13c.

 

Kris Rice: SQL Developer, SQLcl

Website: krisrice.io

Twitter: @krisrice

I’m obviously biased so…

The clear best tool is SQL Developer which has over 5m users which include both developers and DBA. The DBA area of the tool has been greatly enhanced in the last few versions adding one of the best visual dashboard overviews I’ve seen around.

The other SQLcl. Since DBAs typically live on a terminal a full GUI like SQL Developer isn’t always around. For that, we factored the best parts into a command line which will act as they are used to with sqlplus. However, it adds in a lot of comfort features like code insight, code formatting, customization for new commands, and lots more.

 

Kim Berg Hansen

Website: kibeha.dk

Twitter: @kibeha

As a SQL developer basically I am almost entirely using a single tool, which is either Toad or Oracle SQL Developer (or once in a while PL/SQL Developer.)

It doesn’t matter too much which – the main point is I need a GUI to write my SQL and PL/SQL statements, get output, view access plans, debug, etc.

Add to that some use of SQL*Plus when creating scripts with the SQL developed in the GUI tools.

I’m a fairly old-fashioned developer that mostly codes by typing and very little by mouse-click and drag-drop, so it is seldom I need anything more ;-)

 

Kubilay Tsilkara: SQL Developer, SchemaSpy, Oracle Application Express

Website: kubilaykara.blogspot.com

I am glad to give my input and I think the top 3 tools as a dev or dba for me will be

1. Oracle SQL Developer – Is a brilliant IDE tool for ad-how querying and browsing data in schemas inside databases, especially Oracle

2. SchemaSpy – I have been using this long time it is a database documentation tool, an excellent tool for extracting data models (ERD) into HTML and browsing the metadata in any relational database.

3. Oracle APEX – This is my favourite platform for developing database-centric applications. It is a platform I have been following and developing since it’s early HTMLDB days. I also used it for quick prototyping and data visualisation and quick integrations with other APIs using APEX_JSON and other libraries it has.

Besides all the above I am also a keen Python programmer and I use PyCharm quite a lot.

 

Kyle Hailey: Command line, DB Optimizer

Website: datavirtualizer.com

Twitter: @kylehhailey

Well, I don’t use tools for SQL development other than command line as I don’t write that much these days.

But for SQL optimization the best tool out there and the only one that does something called Visual SQL Tuning (VST) is DB Optimizer from Idera (formerly Embarcadero):

 

Laurent Schneider: Toad, SQL Developer, SQL*Plus

Website: laurentschneider.com

Twitter: @laurentsch

Toad or SQL Developer and SQL*Plus, the latest being my favourite.

 

Liron Amitzi: SQL*Plus, SQL Developer, AWR

Website: amitzil.wordpress.com

Twitter: @amitzil

1. SQL*Plus
This tool is old and has a basic command-line interface, but it’s always available and is the simplest and quickest to use. I use it all the time, especially when connecting using SSH to some Linux/Unix servers.

2. SQL Developer
These days, when SQL Developer is such a strong tool and free, I find myself using it more and more. For the quick and dirty stuff I use SQL*Plus, but SQL Developer is great when I need to write complex SQL or PL/SQL code, query large tables or start navigating the objects in the database.

3. AWR
Not a development tool, but for the DBA (assuming you have the license for it) AWR is really one of the best tools. When analyzing database performance, AWR contains so much valuable information, that the task becomes analyzing the data and understand what’s going on instead of digging for the data itself.

 

Luca Canali: SQL*Plus, SQL Developer, Spark SQL

Website: externaltable.blogspot.com

Twitter: @lucacanalidb

I am quite glad there are more than 3 tools for the DBA/sql developer job ;)

However, this sounds like a fun game to play:

Something old: I’d start “old school” mentioning Oracle’s SQL*Plus command line together with a set of familiar scripts for the DBA job (in my case this would be https://externaltable.blogspot.com/2012/09/on-command-line-dba.html)  + with a shout-out to the fantastic Snapper tool by Tanel Poder.

Something mainstream: Oracle’s SQL developer for when I need to work on Oracle and I can be more productive with a graphical tool and cannot use/do not have a script. I guess Oracle OEM can be added here too for the same reasons (although we can see OEM more as part of the Oracle product these days, rather than a separated tool?)

Something new: Spark SQL and Spark APIs for dataframes in general, because this is where I increasingly see a lot of value and community focus for high throughput scale-out computing (notably including SQL and data warehousing)

I wanted to mention Kevin Closson’s SLOB and also the efforts of Tanel and myself on developing PerfSheet (Oracle AWR data visualization),.. but it looks like I have overrun the constraint of limiting this list to 3 items ;)

 

Marko Sutic: SQL*Plus, SQL Developer, Cloud Control

Website: msutic.blogspot.com

Twitter: @markosutic

Tools that I use every day in my daily DBA job:

1 – SqlPlus

I have a big repository of scripts which I use for troubleshooting, monitoring, maintenance, etc.

For me, it is much quicker to perform tasks in the command line, then using GUI.

2 – SQL Developer

SqlDeveloper is my preferred tool when I need to write or debug PL code.

Also, I have custom reports for DBA tasks which I use daily.

I know that SqlDeveloper has many features but I rarely use more than few of them.

3 – Oracle Cloud Control

I’m using Cloud Control for monitoring, troubleshooting, reporting and alarming.

These tools are great and make my life as a DBA much easier.

 

Markus Winand: SQL*Plus, DBMS_XPLAN.DISPLAY_CURSOR, SQL Trace & Tkprof

Website: use-the-index-luke.com

Twitter: @markuswinand

To keep it short, I’ll just write you something. Hope it’s helpful.

Not even sure if those qualify as “tools” ;)

• SQL*Plus. I generally don’t trust GUIs. Haven’t looked into SQLcli yet, but plan to!
• DBMS_XPLAN.DSIPLAY_CURSOR with statistics_level = all;
• SQL trace (10046) and tkprof

For a large part of my work, the first two are enough. A notable part of my job is to reverse engineer the client’s software without binding resources at the client, that’s where SQL tracing comes in.

On the one hand, I’m old fashioned, but I’m also limiting myself to “tools” that are commonly available. Not everybody has Enterprise Edition.

 

Martin Berger: Method R Workbench, Lighty, SQLcl

Website: berxblog.blogspot.com

Twitter: @martinberx

You are asking a very interesting question.

I can only answer from my DBA perspective as I’m no Developer.

Limiting the answer to 3 is hard – you will see my problem in point 3.

1) Method-R workbench – the most advanced collection of tools to analyse SQL-traces. It’s unique in the market.

2) Lighty – a lightweight and fast tool to observe ongoing or past performance of databases. Fewer features than EM, but more performant and intuitive.

3) I need to do a lot more tasks, often write scripts, analyse files, etc. So there are a lot of small shell and awk and perl and SQL and PL/SQL and scripts I need for my job. If I would need to replace them all by only one (the 3rd) tool, I’d pick SQLcl. It can run scripts (even different languages). I would need to learn how to use it efficiently – and rewrite my scripts. But it would be my swiss knife to solve really any Oracle-related issue.

 

Martin Decker: SQL Developer, SQL*Plus, Tkprof

Website: ora-solutions.net

I’m not actually a developer, more an architect/consultant, but I would definitely use SQL Developer, SQL*Plus and tkprof.

 

Mauro Pagano: SQL*Plus, SQLd360, SQL Developer

Website: mauro-pagano.com

Twitter: @Mautro

Mmm then I would probably go with SQL*Plus, SQLd360 (which is a collection of many other “tools” if we use your definition) and SQL Developer. I also rely heavily on a text editor (Sublime) but maybe that isn’t what should go in that list.

 

Mohamed Houri: SQL*Plus, SQL Developer, Toad

Website: hourim.wordpress.com

Twitter: mohamedhouri

I have several SQL scripts with which I

• get historical execution statistics of a particular SQL_ID
• generate AWR reports
• get execution plan from memory
• get execution plan non-sharing reason
• get top wait event and top SQL_ID from ASH
• get the size of a particular object
• get the entire size of the database per tablespace
• etc….

For all those scripts I am always using SQL*Plus.

Otherwise, I am also using extensively SQL Developer for the last decade or so. I used to use Toad before which is probably a good tool as well.

 

Nuno Pinto do Souto: SQL Developer

Website: dbasrus.blogspot.com

Twitter: @dbareactions

SQL developer and that’s it!

 

 

Pete Finnigan: SQL*Plus, TextPad, UltraEdit

Website: petefinnigan.com

Twitter: @petefinnigan‏

Whilst I focus on Oracle security and I am not a DBA I do write SQL and PL/SQL scripts on a daily basis in the line of my work. I have to say that whilst GUI tools such as SQL*Developer and Toad are nice; I am old school and prefer scripted command-line tools; so SQL*Plus and TextPad (Windows) and UltraEdit (Mac) are still my tools of choice to develop my Oracle scripts but SQL*Plus is my tool of choice.

I always tell people in my training courses that Gui tools are nice and you can use the click and open trees but for something complex that you want to see, you maybe have to have multiple trees open and it’s hard to see the complete picture of what you want to learn. I prefer scripts and you can format and see the output of what I want in just one place. I like the power of scripting over point and click.

 

Pete Sharman: Enterprise Manager, SQL*Plus, Srvctl, ManageEngine Applications Manager

Website: petewhodidnottweet.com

Twitter: @sharmanpete

Not sure how you’re defining “tools”, but in my day to day life I really only use four (rather than 3):

1. Enterprise Manager – just ‘cause, having been a product manager for 5 years 😊
2. SQL*Plus – because it’s guaranteed to be there.
3. Srvctl – because we manage RAC databases for customers
4. ManageEngine Applications Manager – a lot of the customers we support are in environments where EM is either not supported (think SE), not sufficiently licensed (you really have to have D&T for EM to reach its full potential and there are still a lot of customers that don’t have it), or because it’s a better tool for managing more than just Oracle.

I’d love to add SQL Developer and SQLcl to the mix as well, but that just makes too many tools to maintain and look after.

 

Rob van Wijk: SQL*Plus, PL/SQL Developer, SQL Developer

Website: rwijk.blogspot.com

Twitter: @rwijk

After years of using GUI tools, the one the client I worked for paid for, mostly Toad, I switched to using sqlplus exclusively in 2006.

The reason is I find spending time where they hid that damn option you’re looking for, a waste of my time. And I find looking at the documentation and learn about all parameters and options a valuable way to spend my time. Sqlplus enforces the latter.

Another reason is that I found out Toad was changing the optimizer environment, which hampered my tuning work.

Only very occasionally, when I want to do some extensive browsing through a schema, I start up a tool like (PL/)SQL Developer.

I know sqlcl is a worthy successor, but I guess I’m still too happy with sqlplus.

 

Sabine Heimsath: SQL Developer, Application Express, SQLcl

Website: sqldeveloper.de

Twitter: @oraesque

SQL Developer

Once you get to know this tool and all its little timesavers you don’t want to miss it!

Auto-reconnect was a big milestone which improved usability a lot, and my current favourites are

• splitting worksheets and using different connections in the split panes
• dragging values from the result set into worksheets and table filters (because it gives you a comma-separated list)
• multi-cursor editing
• extended paste (Ctrl-Shift-V)
• user-defined reports on metadata views with hyperlinks (to navigate between database objects)
• SQL history, file history, statements log (which includes all statements that SQL Dev sent to the database)

If you are looking for handy tips: https://twitter.com/hashtag/SQLDev

Oracle APEX

Oracle Application Express is a great tool which lets you create database applications within a browser. It comes free with your database! You can use the whole range of SQL and PL/SQL and make use of built-in features like Interactive Grid and Jet Charts.

If you want to try it out, you can go straight to https://apex.oracle.com and get your own workspace. The site offers lots of material for beginners. And should you get stuck – the APEX community is amazingly supportive and loves to share!

SQLcl

If you like SQL*Plus try SQLcl (SQL Developer command line) – I like to think of it as SQL*Plus+

The team claims that “everything you have loved about SQL*Plus for the past 30 years is still available in SQLcl”. Besides, it offers many small features that make your life easier, like aliases, extended formatting options and scripting (JavaScript is included by default, but you can also add other script languages) which for instance lets you import and exports blobs from the client quite easily, commands like INFO (an extended DESC) and REPEAT, command history etc.

Get it here: http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html

 

Sayan Malakshinov: SQL*Plus, SQL Developer, SQLcl, ASHViewer

Website: orasql.org

Twitter: @xtner

My top 3 tools now:

1. Old good sql*plus with lots of scripts (including such great tools like Session Snapper and Latchprofx by Tanel Poder). Sqlplus saves a huge amount of time in case of troubleshooting. It’s very fast and I don’t need to touch the mouse and spend seconds even for switching between databases.
2. SQL Developer, SQLcl. I like to use SQL Developer for longer tasks or for code analysis, and from time to time I’m trying to start using modern SQLcl instead of sqlplus.
3. ASHViewer. It’s a great portable tool for the cases when you need to visualize database load.

 

Stefan Panek: SQL Developer, Toad

Website: stefanpanek.wordpress.com

Twitter: @Stefan_Panek

My favourite tools as Oracle DBA are more or less :

SQLDeveleoper

• It’s free without any cost
• Very good integrated for Oracle
• A lot of extensions
• A large community which could help
• And lot more

Quest Toad

• Is one of the very good DBA Tools
• A lot of extensions
• Cool tools around
• And more

Yes so the tool which I use more or less is SQLDeveoper

 

Steven Feuerstein: SQL Developer, SQLcl, ORDS

Website: stevenfeuersteinonplsql.blogspot.com

Twitter: @sfonplsql

SQL Developer: the go-to IDE for Oracle Database development. This product has matured rapidly and impressively. It is packed with productivity enhancers and useful interfaces to all sorts of database features.

SQLcl: the command-line “version” of SQL Developer. A critical tool for scripting database operations, for use in CI/CD, migrations, upgrades, automation and more. Recently open-sourced!

ORDS – Oracle REST Data Services: Modern application development is increasingly built on REST APIs and JSON. ORDS makes it easy for you to securely provide REST APIs to your data, your PL/SQL data APIs, and the database itself.

 

Tim Hall: SQLcl, VirtualBox, Cloud Control

Website: oracle-base.com

Twitter: @oraclebase

1) SQLcl / SQL*Plus: If you had asked me this a few months ago I would have said SQL*Plus, but recently I switched across to using SQLcl instead. Regardless of which you pick, I do most things from the command line. Over the last couple of decades, I’ve collected a bunch of scripts that allow me to do most things I want to do really quickly, without having to rely on other tools. This is the most efficient way for me to work, but I can understand newer people needing to rely on other tools.

2) VirtualBox: The best way to learn anything is to do it. It’s not always easy to learn new stuff on “real kit”. I use VirtualBox for all my own testing. Using tools like Vagrant allow you to quickly spin up VirtualBox VMs for different environments, allowing you to test various scenarios. I find myself using Docker more and more these days, but I actually run Docker inside a VirtualBox VM too. Desktop virtualization has been a really import part of my learning process over the years.

3) Cloud Control: If you have Enterprise Edition and the Diagnostics and Tuning Option, you can use the performance pages in Cloud Control. These are really cool for quickly identifying problems and drilling down into them. No tool is perfect, but I’m sure I get to the root cause of performance issues quicker because of Cloud Control.

Honourable Mentions: Oracle REST Data Services (ORDS),  SQL Developer, MobaXterm, Git etc…

 

Tony Hasler: Toad, PL/SQL Developer, SQL Developer

Website: tonyhasler.wordpress.com

Twitter: @tony_hasler

To be honest my experience is limited to just a few tools and my answer would differ depending on

a) Am I working as a DBA or a developer

b) Whether SQL*Plus needs to be included in the list or whether it is assumed that it is always available.

c) Whether price is a factor or not.

If I had access to a full commercial version of TOAD with all the options I would not need any other tool. I wouldn’t necessarily even need all the options depending on my role.

PL/SQL developer is typically much cheaper and is perfectly adequate for a typical developer but pretty useless for a DBA.

SQL developer, of course, comes at no additional cost to the database license. Until recently the drawbacks presented a serious impact to productivity – both for programers and DBAs. in recent releases, however, things like code formatting can now be declared “fit for purpose” if not quite as mature as TOAD; for those strapped for cash, this may now be the best choice.

I have never been employed as a data modeller so cannot comment on tools aimed at that role.

As far as SQL tuning tools are concerned I have never found any of them to be useful. You would be best advised to spend your money on my book instead!

 

Toon Koppelaars: SSH, VI, SQL*Plus

Website: harmfultriggers.blogspot.com

Twitter: @ToonKoppelaars

• ssh
• vi
• sqlplus

 

Uwe Küchler: SQL Developer, AMON, SQLHC

Website: oraculix.com

Twitter: @oraculix

As a consultant DBA who is rooted in development, I tend to use a tool mixture that covers both worlds:

1. SQL Developer

• Has become a powerful alternative to costly tools
• Its custom reporting feature is great to fill gaps not yet covered by base functionality. E.g., I use it to visualize Statspack metrics.
• It’s not only for developers: the DBA module offers a lot of essential functions for daily use.
• Last but not least: it’s free of charge!

2. AMON

• One of the most underrated DBA tools I know of.
• UI feels like “top” on Linux/Unix, but with a lot more drill-down capabilities
• Works in text mode and is therefore perfectly suited for TTY-only environments or narrow-bandwidth connections
• Maintained by an Oracle Support employee
• Last but not least: it’s free of charge!

3. sqlhc

• Gathers all available information around a given SQL_ID for (mostly, but not limited to) performance troubleshooting
• Maintained by Oracle Support; originally written by Carlos Sierra who offers his own similar tool “sqld360” now.
• Saves the troubleshooter a lot of manual research time
• Lightweight, nothing to install.
• Gives hints to possible known bugs, e.g. in histogram generation
• All information nicely HTML-formatted
• Last but not least: it’s free of charge!

 

Vivek Sharma: SQL*Plus, SQL Developer, Enterprise Manager

Website: viveklsharma.wordpress.com

As a Database expert, I usually love working on SQLPlus, writing my own scripts and running against an Oracle Database. Off lately, I started using SQL Developer for some ADWC related stuff. TOAD had been very popular as well, though have not used it much.

For DBA’s, I still feel Enterprise Manager is the most preferred GUI for database monitoring and analysis.

 

Yalim Gerger: SQL Navigator

Website: gergerconsulting.blogspot.com

Twitter: @yalimgerger

I’d only use SQL Navigator. I don’t have a 2nd or 3rd tool. I appreciate all the creators of other tools but as a PL/SQL developer, I’ve always felt very comfortable with SQL Navigator and only with SQL Navigator. SQL Navigator is by no means perfect, and working with it has its own challenges. However, I always thought that its designers have a great understanding of a PL/SQL developer’s needs and priorities.

 

Summary

This chart shows each tool that was recommended, and how many recommendations it got.

Here’s the data in table format

Tol	Count
SQL Developer	35
SQL*Plus	27
Enterprise Manager/Cloud Control/Grid Control	11
SQLcl	11
Toad	8
APEX	4
AWR	3
PL/SQL Developer	3
Textpad	3
Tkprof	3
VI	3
Ansible	2
Git	2
Lighty	2
Method R Workbench	2
SQLd360	2
Admitting “I don’t know”	1
AMON	1
ASHViewer	1
AWR Warehouse	1
Bitbucket	1
Command line	1
DB Optimiser	1
DBMS_XPLAN.DSIPLAY_CURSOR with statistics_level = all;	1
EDB360	1
Jenkins	1
Komodo Edit	1
Live SQL	1
ManageEngine Applications Manager	1
mRemoteNG	1
Notepad++	1
ORDS	1
Quick SQL	1
RichMon 4 Oracle	1
SchemaSpy	1
Spark SQL	1
SQL Navigator	1
SQL Server Management Studio	1
SQL trace (10046)	1
SQLHC	1
Srvctl	1
SSH	1
Sublime Text	1
TVD$XTAT	1
UltraEdit	1
Virtual Box	1

 

Conclusion

What an amazing collection of useful advice! Thanks once again to the contributors to this article!

Now I want to turn it over to you.

What 3 tools would you recommend as an SQL developer or DBA?

Let me know by leaving a comment below.

Contributors: If there are any errors or changes you’d like to make, please let me know and I’ll update the article.