Thursday, July 21, 2016

24 Basic Tips on Using SQL Developer

I have been using SQL Developer since it required some goodwill to work. Some of the motivation came from an annoyance with Toad sessions in the database. I remember that I back then googled "how to block Toad users from the database".  

Anyway, these days most developers around me are using SQL Developer. But quite a few are learning only the least to get their job done. The following list is what I use often, and some of it results in "How did you do that?" when they are looking over my shoulder. The shortcuts here refer to the Windows version since that is what I'm currently using at work. You can easily remap them from Preferences.

  1. Use templates from preferences to create shortcuts to frequently used SQL statements, or fraction of it. You'll find it under Database - SQL Editor Code Templates
  2. Speaking of preferences, there are lots of stuff to tweak, if you are looking for something special use the search field in the upper left corner
  3. Templates are useful, so are snippets which can be anything from functions and hints to small scripts. Check out View -> Snippets, and when you see something cool, just drag it into the editor window.
  4. Drag the table from the list of tables in the Connections window to the worksheet, and it will offer to generate SQL for you (INSERT, SELECT, etc).
  5. Copy rows from Query Result with Shift-Ctrl-C to get the column headers. Paste it directly into Excel or in Outlook Email. In the latter you can use the "Convert to text" function to get a nice looking table in a few steps.
  6. Attach shortcuts to worksheets with Alt-Shift-number (1-9). Switch between them with Alt-number
  7. If you have used one of those tools that requires you to mark the statement you want to execute, you don't have to do this with SQL Dev; with the cursor somewhere on the statement just hit Ctrl-Enter.
  8. To convert text to and from upper, lower, or initcap, mark the text and hit Ctrl-' (this one was not set on my Mac version, if that is your case, go to Preferences, search for shortcut, and in that window search for 'other'). Repeating it will cycle through upper, lower, and initcap.
  9. How to insert a list you receive into a table: Say someone sends you a list of 10 rows in an email, go to the Data tab for the table and add 10 rows. Copy the rows from the email and put the cursor on the first row in the data tab, and paste them in with Ctrl-V. SQL Dev will paste them in as you expect in Excel. This even works with two or more columns if the columns are separated with tabs. 
  10. Using Subversion (or Git) from SQL Dev saves you a mental context switch compared to using an external client. By opening SQL scripts in SQL Developer, edit, save, and commit them (to the source control system) your work flows better.
  11. Reformat code with Ctrl-F7. Makes it easier to read when you have copied and pasted generated code from somewhere.
  12. Get instant info about a table. Put the cursor on the table name in the worksheet and hit Shift-F4. A window pops up with lots of vital information, and it is quicker than to search for it in the hierarchy. 
  13. Hit Ctrl-G to go to a specific line in your editor window. 
  14. After you have become more productive and live your life in SQL Dev, it may need more memory.  See this post for details: In short, uncomment the line in product.conf that starts with AddVMOption, and change the value from 800m (default) to say 1024m. 
  15. Save query output for future reference by using the red pin in the Query result window. You can go back and see what SQL produced it by clicking the blue "SQL".
  16. When you look at the indexes for a table in the Pop-up info box (Shift-F4), you can open the declaration for the index by right-clicking on a row for an index, and choose Go To...
  17. Right-click on an editor tab and choose Float. It will move the worksheet tab to a separate window. Then you can click on other tabs and drag them to that window and group connections that belong together.
  18. Change the DATE-format in the Preferences, the default format is usually not enough for me, so I add HH24:MI:SS 
  19. DBAs and others often work on many tasks at the same time, some scripts or SQL commands take long time to execute. Instead of checking back once in a while for its completion open the Task Progress from the View menu. It will show tasks that are running, completed or failed. Click the icon on the right side in order to remove finished tasks.
  20. SQL History (F8) is useful, even as backup of previous complex SQL that you have been working on, but didn't save before the crash...
  21. Use Connection Color when setting up a connection towards a production database. I use red and it will add a discrete line around the edge to remind me not to do something stupid. This is much better than having the entire background bleeding when doing serious business.
  22. Hit F10 to get the execution plan for the statement where your cursor is.
  23. If you need to get to your database through a jump server, see my blogpost about it.
  24. Code completion is very useful, but sometimes it doesn't work. There is a limit to how much processing and parsing SQL Developer can do before your patience runs out. It helps to at least finish previous statement with a semi-colon or something that clearly marks the start of next statement, and write correct SQL.
There is a lot smartness built into the tool, sometimes a bit hidden; to not get in your way. If you think SQL Developer can't do something, just try it out. So far I have never received any obscene error messages.

Monday, May 23, 2016

Open Session Error when connecting to Hive from SQL Developer

This blog post from Oracle explains well how to connect to Hive from SQL Developer. In short, you need to download the JDBC driver from Cloudera and make sure you use the correct version, version 4 and not 4.1 Three zip files are included, use the one that starts with Cloudera_HiveJDBC4_2.*.

Anyway, after adding the jar files to SQL Developer, and restarting it,  the connection failed with:
Error setting/closing session: Open Session Error
Not really informative for anyone used to ORA error codes.


I tested the connection locally with the beeline command that is included with Hive. It worked OK when connecting with the URL jdbc:hive2://, but when I connected with URL jdbc:hive2://rio:10000 (rio is the hostname), it returned the error:

hadoop is not allowed to impersonate anonymous 

I tried options -n and -p to connect as user hadoop, but the error message was similar:

hadoop is not allowed to impersonate hadoop
Turned out that I needed to change the configuration of Hadoop itself, and added the following to  core-site.xml:

Yes, this is lax security, but OK in my lab. After a restart of hdfs etc, SQL Developer connected successfully to Hive. Proxy user in Hadoop is explained here.

Running simple queries from Hive, either from beeline or SQL Developer is slow, which makes you think twice before you submit a query, unless you are going for a cup of coffee.  That is a good thing, I guess.

Tuesday, April 19, 2016

Undo old mistakes with online table redefinition

There are times when you as a DBA wished you were involved before someone designed a data model. After release to production correcting mistakes is usually complicated and with risks.

Update 2016-04-20: Fixed errors in code.

There is an exception to this. Online table redefinition lets you change the structure of a table while everything is up and running. I have used it lately to partition large tables. During the process I also added compression, and moved some of the partitions to another tablespace that I have set read-only, in order to reduce backup time as well. LOBs in one table were moved to SecureFiles with deduplication.

Redefining tables online is explained in the Administrator's Guide, chapter 20 (11g and 12c). The PL/SQL package DBMS_REDEFINITION is used along SQL to create a new table with the desired structure.

The whole process can be quite simple, depending on how much you want to change, though it may require some time. Of course, in development, you can repeat the whole process in order to improve on the design in case you are not satisfied. In theory this can also be achieved in production, but I do recommend that you get tired of fixing this in development before you move on to production.

The example is made simple to get an overview, check the documentation for more details.

I did the whole process as user SYSTEM, but it may be executed as the owner of the table provided execute privilege on the package has been granted along with CREATE TABLE and CREATE MVIEW.

  1. Verify that your table can be redefined this way: 


    The last parameter is for tables without a primary key (PK) defined, the default value here is dbms_redefinition.cons_use_pk that applies to tables with a PK. In other words, if your table has a PK (as it should have), skip this last parameter here and in step 3. If no error is returned you are good to go.
  2. Create an empty interim table with the structure you want. Spend some time here in order to avoid new issues. I used SQL Developer to generate the SQL for the original table and edited  it to create the new table.  This is rather simple if all you want is to introduce partitioning or similar that doesn't change the columns and datatypes. Also note, remove all constraints on this interim table, they will be copied later. 
  3.  Start the process with:


    The 4th parameter is null in this example, but is used when you want to reorder columns and need to use column mapping between the two tables. This statement starts the copying of rows from the original to the interim table, and will take some time depending on the amount of data. You may speed things up by enable parallel DML and query with:


  4. Next step is to have Oracle copy all dependent objects of the table. That is, indexes, constraints, triggers, privileges, stats,  and more.

      l_err pls_integer;
      , int_table=>'BIG_TABLE_TMP',num_errors=>l_err);

    This procedure actually has options to skip certain object types like indexes, or triggers, etc. Check the documentation for details. Again, the amount of time this takes depends on the size of it all. But it is kind of cool that the Oracle database is working hard for you while you read Oracle blogs. If this procedure fails, it may be that you created the interim table (BIG_TABLE_TMP) with a NOT NULL constraint; easy to leave in there. Just remove the constraint and repeat this step; no need to start over from scratch.
  5. Next step is to finish it all with:

    When this completes the dictionary has been updated and the table with name BIG_TABLE will have the structure you created in BIG_TABLE_TMP, and vice versa. You can now drop the BIG_TABLE_TMP table.

As usual with online operations there are some locks involved when the data dictionary is updated, so you may chose to finish the operation off peak time. Also in order to shorten the time spent in step 5 you may use DBMS_REDEFINITION.SYNC_INTERIM_TABLE   between step 4 and 5 to refresh the interim table first. Lastly, if you want to abort the procedure between step 3 and 5, just execute the ABORT_REDEF_TABLE procedure.

This feature has been around for some time, and is very stable. If you have SQL not performing well because a table grew too fast, or you have some other structural problems, this feature is really a low-hanging fruit. The idea behind here is that you don't touch the original table, but let Oracle do it for you when everything is ready (in step 5).

As always, keep it simple, life as a DBA has more than enough of excitements.

Wednesday, February 17, 2016

SQL Developer and SSH for DBAs

In version 4.0.3 SSH was introduced in SQL Developer; here's a post from Jeff Smith about it. Now this feature has become even easier to use with SSH configuration made independent of the database connections. 

DBAs typically have a jump server they connect to in order to login further to the database servers. With the new implementation (I'm using version 4.1.3 at the moment) you setup one SSH host, and for every listener you want to connect to you configure a tunnel through the jump server.

Go to View -> SSH to show the window for SSH configuration. Add a host with a right-click on "SSH Hosts". To begin with it is sufficient to add just a descriptive name, a hostname or IP address, and your username. Then with a right-click on the host select "New Local Port Forward" as shown here:

The name should be the database name if you use dedicated listeners for every database, or a descriptive name of the listener if it is used by many. If you are using RAC, don't use the hostname for the SCAN listener, but one of the vip-addresses. Also use the default "Automatically assign local port", because there is some nice magic behind the scene here.

Where I work I typically have many connections to the same database, each with a different Oracle user. With the new SSH feature in SQL Developer it gets very easy to setup. For every new connection select Connection Type SSH and you pick from the Port Forward list as shown here:

(By the way, did you notice I changed the Connection Color? One of my favourite features when dealing with production databases).

That's it! The point here is that you reuse each port forward aka tunnel as often as you need, you no longer need to configure the same SSH tunnel for each database connection. And if the listener is moved you change it only once. Exactly what port is used locally is taken care of, which makes this much easier to setup and use than creating the tunnels in Putty or SSH command line since you don't have to remember the local port. 

Tuesday, December 29, 2015

JRuby in SQL Developer Data Modeler on Mac

In SQL Developer Data Modeler you have Libraries under the meny Tools -> Design Rules and Transformations. But when you select that one you may have seen this error message:

Notice the second line with Jruby lib is red. This is because the JRuby is not installed (or cannot be found) on your machine.

I found the solution to this in Heli's book on SQL Developer Data Modeler, but thought I could add the easy way to solve this on your Mac. You need to download an extension to your JDK installation. A jar-file can be downloaded from (There are two main versions, I decided to go for the the 1.X one). Look for the jar-file, currently I downloaded jruby-complete-1.7.23.jar.

You need to figure out where to put this jar file. In SQL Developer Data Modeler, check your java.library.path setting by going to Help -> About. Then click on the Properties tab. There are a lot of them, so in stead of scrolling down, start writing in the search field "java.l" as shown:

In my case, one of the directories in the search path is /Users/oisene/Library/Java/Extensions. It did not exist on my system, so I created it and copied the downloaded Jruby jar file into this directory. After a restart of SQL Developer Data Modeler you can go to Tools -> Design Rules and Transformations -> Libraries to verify that you no have the error message and the line with Jruby lib is black.

Monday, November 23, 2015


This post is meant to be found by people googling the error message ORA-01105 and ORA-19808 on RAC. The following error messages may be seen after starting an instance:

ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch

When this happened to me I ran this command on both instances:
show parameter recovery

it showed there was a mismatch for the parameter db_recovery_file_dest_size. Probably I forgot to add "sid=*" to the alter system command one time when I increased the limit. Since one instance was up I could update the parameter for both instances with:

alter system set db_recovery_file_dest_size=700G sid='*';

The instance that was started must be shut down and started again.

This means that this parameter has to be the same across the instances during startup, but Oracle allows you to set them different with alter system.

As shown here you can correct parameters in spfile for an instance that won't start from a running instance. This is a favourite feature, and whenever I have to change memory parameters that requires restart of the instance, I keep one instance running so I can correct the settings from there if needed.

Sunday, November 8, 2015

My take away from OOW15

A big conference like Oracle Open World is an excellent opportunity to learn more. But with all the books, blog posts, and on-demand learning on Internet, I really did not have to go to OOW this year to keep myself busy learning. Of course, I learned a lot, but for me, the biggest reward is the inspiration and ideas for future projects, as well as meeting friends in the community including Oracle employees who take part in it.

Regarding inspiration I think that the attention given to SQL and PL/SQL is awesome. This is much more than the code itself. It is about the ability to do more and more complex analysis for each new version of the database. You can do it with minimal amount of code, code that executes very close to the data.

It is also about Application Express that is a quick and responsive web interface for your data; web pages generated in the database with the ability to manipulate and report on the data. I am a DBA that keeps telling other DBAs that if they can choose APEX for a project they should do so. Anything else will be more complex with more layers (even with Oracle REST Data Service as a proxy).  It is built on technology the DBA understands and can control.

In the era of Big Data and Cloud computing I think the most fascinating stuff is data mining. It has been around for years, but thanks to an renewed focus, data mining algorithms are cool again. Oracle Advanced Analytics is more than complex algorithms. You need to explore the data, and the more you know SQL in general, and analytical functions in particular, the better you are prepared for such a project.

I'm getting the impression that beside the Oracle database, preferably running on Exadata, I need nothing more than perhaps a couple of tiny applications servers in front, and network, of course.