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.
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
If username, password or both are empty in a connection profile, SQL Workbench/J will
try to use the information stored in the password file file
or the environment variables
(PGPASS
, PGUSER
) the same way as libpq uses them.
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.
![]() | |
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.
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.
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
SQL Workbench/J supports most of the parameters and options the SHOW
from SQL*Plus does.
SHOW option | Description |
---|---|
ERRORS | Displays errors from the last PL/SQL compilation. |
PARAMETERS |
Displays configuration parameters.
Unlike SQL*Plus you can supply multiple parameters separated with a comma: As with SQL*Plus, you need the |
SGA |
Displays memory information.
As with SQL*Plus, you need |
SGAINFO | Displays extended memory information not available in SQL*Plus. |
RECYCLEBIN | Shows the content of the recyclebin. |
USER | Shows the current user. |
AUTOCOMMIT | Shows the state of the autocommit property. |
LOGSOURCE | Displays the location of the archive logs. |