Database Reference
In-Depth Information
disconnection. However, in terms of session reestablishment, the implications are quite different.
When
IMMEDIATE
is specified, alone or in conjunction with
POST_TRANSACTION
, TAF does not take
effect, whereas it does when merely
POST_TRANSACTION
is used. By the way, TAF will also not
intervene when
ALTER SYSTEM KILL SESSION
is issued or the client's server process is termi-
nated with the UNIX command
kill
-TERM
.
1
SELECT Failover
As stated before, the requirements for TAF are a properly configured Net service name (e.g., in
tnsnames.ora
) and a database client built on top of OCI. To avoid boring you by demonstrating
TAF with SQL*Plus, I have chosen to use Perl DBI (see Chapter 22), which is implemented with
OCI. The screen output depicted next is from the Perl DBI program
dbb.pl
,
2
which is capable
of executing arbitrary SQL statements and PL/SQL blocks. I wrote it after many years of annoy-
ance due to unreadable output from SQL*Plus and time spent making obfuscated query results
halfway readable with
COLUMN
name
FORMAT
commands.
The program
dbb.pl
reads from standard input until it finds a slash (
/
) by itself at the
beginning of a line. At this point, it prepares and executes the statement entered. If it detects a
SELECT
statement by checking that the DBI handle attribute
NUM_OF_FIELDS
is larger than zero,
it fetches and displays the rows with automatically adjusted column widths! The column width
is automatically made just wide enough to accommodate the larger of either column heading
or column value, leading to easily readable query results. This is what makes the program a big
time saver compared to SQL*Plus, which does not have such a feature. At this stage, this is
pretty much all
dbb.pl
can do.
For the following test, I slightly modified a routine called by
dbb.pl
, which iteratively
fetches all rows in such a way that it pauses after each fetch. Thus, it is guaranteed that discon-
nection can be requested while the program is in the fetch loop. To avoid buffering effects due
to bulk fetch (a.k.a. array fetch, i.e., retrieval of more than a single row with each fetch call), it is
necessary that the number of rows in the table is larger than the fetch array size. If this issue
is disregarded, it may happen that all the rows have already been retrieved into a client-side
buffer. The client then merely reads from the client-side buffer, without interacting with the
DBMS instance, such that the
SELECT
statement does not need to be restarted. When testing
TAF
SELECT
failover with SQL*Plus, I recommend the following settings to avoid buffering:
SQL> SET ARRAYSIZE 1
SQL> SET PAGESIZE 1
SQL> SET PAUSE "Hit enter to continue ..."
SQL*Plus will then
parse and execute the statement
fetch a single row at a time (
FETCH
...
r=1
would be seen in a SQL trace file; see Chapter 24)
delay display of the first row until the user hits enter
pause after each row until the user hits enter
The commands
kill
-TERM
and
kill
-9
are equivalent.
TERM
is the abbreviated name of the signal with
number nine. A list of all signals is in the C language include file
/usr/include/sys/signal.h
.
1.
The Perl program
dbb.pl
(database browser) is included in the source code depot of Chapter 22.
2.
