Check locked objects in Oracle database

How to check which objects are locked in Oracle database?

Sometimes its necessary to check which tables are locked on the database (be it by running ETL sessions or other database users). Information on current locks on the Oracle database objects can be found in the V$LOCKED_OBJECT view. Here’s a SQL query to check the locked objects on Oracle database: Continue reading

Enable word wrap in Toad

How to enable word wrap in Toad?

To do that we will need to set the right margin position and enable the word wrap in Toad options.

Go to View / Toad Options:


First go to Editor / Display to modify the position of right margin. Set Visible right margin to the position you want.


Select Editor / Behavior and check the Word wrap option:


Query that was going outside the margin before the changes:WordWrapToad1

After changes are applied, query is wrapped to the right margin:WordWrapToad2

See the post on how to enable the word wrap feature in Oracle SQL Developer.

Enable word wrap in Oracle SQL Developer

How to enable word wrap in Oracle SQL Developer?

Actually there is no such option in Oracle SQL Developer as of version 4.02 (this may be changed in further releases). To enable similar feature you’ll need to set a line break at some line width and format the text using CTRL + F7 key combination. Here’s how to do it:

To set maximum line width in characters go to Tools / Preferences:


Choose Database / SQL Formatter / Oracle Formatting. Choose a profile to edit (default is Old Preferences) and click Edit:


Set Max Line Width up to as many characters you want (here 80):


Query is still unformatted and some lines are outside the grey right margin line set at 80 characters:


Press CTRL and F7 to format the query. In result the text will be wrapped to the right margin:


Note that as of Oracle SQL Developer 4.02 the right margin line will be always set at 80 characters line width, even if you change that. So if you set Max Line Width at more than 80, the query will be formatted and wrapped up to that width, but the right margin line will be visible after 80 characters.

See the post on how to enable the word wrap feature in Toad.

Auto replace shortcuts in Oracle SQL Developer

How to enable auto replace shortcuts in Oracle SQL Developer?

In the Auto replace shortcuts in Toad post I showed you how to add auto-replace shortcuts in a Toad client. You can acheive the same in Oracle SQL Developer – here that feature is called SQL Editor Code Templates.


How to add a code template shortcut in SQL Developer?

First, go to Tools / Preferences:


Choose Database / SQL Editor Code Templates and click on Add Template button. For example add these two code templates:

saf - SELECT * FROM 

Now to use the auto replace code template type the shortcut and press CTRL and SPACEBAR keys. Shortcut will be replaced with a pre-defined statement.

Auto replace shortcuts in Toad

A very handy tip for Toad for Oracle client: you can create shortcuts for the most used SQL statements or whole queries by adding them to Auto Replace list. Thanks to that feature when you’ll type the shortcut in the editor’s area Toad will automatically replace it with a pre-defined phrase.


How to add an auto replace shortcut in Toad?

Go to View / Toad Options:


Choose Editor / Behavior and click on Auto replace button.


Let’s add three shortcuts for auto-replace:

 scaf - select count(*) from
 saf - select * from
 wer - where


Now after typing the shortcut Toad replaces it automatically with the whole phrase. You can also add shortcuts for whole SQL queries you use often. Keep in mind to create such shortcuts that won’t coincide with the names of objects like tables or columns.

You can achieve the same effect in SQL Developer client – I described it in the Auto replace shortcuts in Oracle SQL Developer post.