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
Line numbering is a very helpful feature, especially when you need to go to a code line specified in the error message. The feature is disabled by default in Oracle SQL Developer.
Here’s a handy query to get the values of tablespace size, used and available free tablespace and usage percentage on Oracle database: Continue reading
Sometimes you need just to fetch first few rows from the query results, not the whole results set. Different databases offer functions to limit the rows displayed from the query results. Continue reading
When trying to execute a statement with a single quote (or apostrophe) inside a string in a SQL client:
SELECT * FROM employees WHERE name = 'd'Artagnan'
you get an error:
ORA-00933: SQL command not properly ended
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.
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 scaf - SELECT COUNT(1) 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.
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.
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.