12. DBMS specific features

12.1. PostgreSQL specific features
12.2. Oracle specific features

12.1. PostgreSQL specific features

12.1.1. Checking for un-committed changes

Before a SQL panel (or the application) is closed, SQL Workbench/J will check if the current connection has any un-committed changes (e.g. an INSERT without a COMMIT). This is done by checking the pg_locks system view. The information in this view might not always be 100% correct and can report open transactions even though there are none.

The checking for un-committed changes can be controlled through the connection profile.

12.1.2. Using the COPY API for client side files

WbImport can make use of PostgreSQL's COPY API to send client side files to the server. The SQL statement COPY from stdin does not work when executed using the JDBC driver. But WbImport can make use of the COPY API by using the parameter -usePgCopy

12.2. Oracle specific features

12.2.1. Checking for un-committed changes

Before a SQL panel (or the application) is closed, SQL Workbench/J will check if the current connection has any un-committed changes (e.g. an INSERT without a COMMIT). This is done by checking the V$TRANSACTION system view.

[Note]
By default a regular user does not have SELECT privilege on V$TRANSACTION, please grant the privilege before enabling this feature.

The checking for un-committed changes can be controlled through the connection profile.

12.2.2. SQL*Plus autotrace mode

SQL Workbench/J supports the a mode similar to "autotrace" mode in SQL*Plus. The command to turn on autotrace is the same as in SQL*Plus and supports the same options. For details see the description of the SET command.

The current user needs to have the PLUSTRACE role in order to be able to see statement statistics (which is required by SQL*Plus as well). The PLUSTRACE role grants the SELECT privilege on the system views: V$SESSTAT, V$STATNAME and V$MYSTAT. The role is not required for the traceonly explain option.

As an extension to the Oracle syntax, SQL Workbench/J supports the keyword realplan as a substiture for explain. In that case the execution plan is also displayed but not by using EXPLAIN PLAN but by retrieving the actual execution plan that is available via dbms_xplan.display_cursor(). In order to use that package, the execute SQL will be changed by SQL Workbench/J. It will prepend it with a unique identifier so that the SQL can be found again in Oracle's system views and it will add the gather_plan_statistics hint to the statement in order to get more detailed statistics in the execution plan.

In order to see the "real" execution plan, use set autotrace traceonly realplan instead of set autotrace traceonly explain.

When using statistics together with explain or realplan, SQL Workbench/J will have to retrieve the generated SQL_ID in order to get the execution plan using dbms_xplan.display_cursor(). To use that function the SQL_ID is required which is retrieved from V$SQL using a unique comment that is added to the SQL statement before it is sent to the database. Querying V$SQL based on the column SQL_TEXT is quite an expensive operation and might create unwanted latch contention on the server. If you want to avoid that overhead do not use the statistics option when also displaying the execution plan.

12.2.2.1. Examples

Show statistics without retrieving the actual data:

set autotrace traceonly statistics

Retrieve the data and show statistics

set autotrace on statistics

Display the statistics and the execution plan but do not retrieve the data

set autotrace traceonly explain statistics

Display the statistics and the actual execution plan but do not retrieve the data

set autotrace traceonly realplan statistics

12.2.3. Using SQL*Plus' SHOW command

SQL Workbench/J supports most of the parameters and options the SHOW from SQL*Plus does.

SHOW optionDescription
ERRORSDisplays errors from the last PL/SQL compilation.
PARAMETERS

Displays configuration parameters.

As with SQL*Plus, you need the SELECT privilege on V_$PARAMETER to use the PARAMETERS option.

SGA

Displays memory information.

As with SQL*Plus, you need SELECT privilege on V_$SGA to use the sga (or sgainfo option.

SGAINFODisplays extended memory information not available in SQL*Plus.
RECYCLEBINShows the content of the recyclebin.
USERShows the current user.
AUTOCOMMITShows the state of the autocommit property.
LOGSOURCEDisplays the location of the archive logs.