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
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:
After changes are applied, query is wrapped to the right margin:
See the post on how to enable the word wrap feature 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.
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.
With versioning control, Informatica PowerCenter stores the previous versions of objects inside them. You can open in workspace any of the previous versions. To do that right-click on the object and select Versioning / View History:
Now you can see the history of all versioned changes with their dates, users, and comments. Select the version you want to preview with right-click and choose Open in Workspace:
Now the session is opened in the workspace (notice the (v2) prefix that indicates the version):
You can do that as well with other objects in Workflow Manager and Workflow Designer. Objects from versioning history will have the prefixes with the version number:
In Workflow Monitor running and succeeded status bars are by default in similar greenish colors. This can be quite confusing and makes it harder to skim quickly through the statuses of the sessions. To avoid this, you can customize the colors of status bars.
To do that go to Tools / Options and select Gantt Chart tab. Here you can specify two colors for each session (if you choose two different the bar will be a gradient) and color for recovered tasks:
Let’s choose a light green for running sessions to distinguish it from completed sessions:
After changing the colors it’s easier to differentiate the statuses of monitored sessions:
