Database Reference
In-Depth Information
BUCKET 76429:
LIBRARY OBJECT HANDLE: handle=6c15e8e8 mutex=6C15E99C(1)
name=
ALTER USER hr IDENTIFIED BY ******
There might be other similar exploits, such that you may feel that granting
ALTER
SESSION
is too risky. The free instrumentation library for ORACLE (ILO) from Hotsos
2
contains the package
HOTSOS_ILO_TIMER
, which may be used to enable and disable tracing of database calls, wait
events, and bind variables without providing access to events or dumps. Be warned, however,
that you should revoke execute privilege from PUBLIC on
HOTSOS_SYSUTIL
, which is installed
with ILO. Otherwise any database user could write to the alert log with the procedure
WRITE_
TO_ALERT
. Of course, you may also build your own wrapper around
DBMS_SUPPORT
or
DBMS_MONITOR
,
but ILO also provides interesting functionality for application instrumentation with module,
action, and client identifier (see Chapter 28).
ALTER SYSTEM SET EVENTS
ALTER
SYSTEM
SET
EVENTS
is the instance level counterpart to
ALTER
SESSION
SET
EVENTS
. It sets
events for all future database sessions. Events set in this way do not persist across instance
restarts. If an event must be set each time an instance starts, use the parameter
EVENT
(see page 32).
Here's a scenario for using an event at instance level, which you will hopefully never have
to face. Let's assume there are several block corruptions in a table segment. Unfortunately, the
database is in no archive log mode, so neither data file nor block level recovery are an option.
Importing the last export dump would result in data loss. The following undocumented event
enables skipping of corrupt blocks during a full table scan:
3
$ oerr ora 10231
10231, 00000, "skip corrupted blocks on _table_scans_"
// *Cause:
// *Action: such blocks are skipped in table scans, and listed in trace files
You may be able to salvage most of the changes since the last export was done by a table-
level export with this event. Yet, there is no way to instruct the export utility
exp
to set an event
after it has connected. This is where
ALTER
SYSTEM
SET
EVENTS
comes in handy. By using
DBMS_
SYSTEM.READ_EV
, we may confirm that events thus set really are picked up by new database
sessions.
SQL> CONNECT / AS SYSDBA
Connected.
SQL> VARIABLE lev NUMBER
SQL> SET AUTOPRINT ON
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever, level 10';
System altered.
SQL> EXECUTE sys.dbms_system.read_ev(10231, :lev)
2.
The documented procedure
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
provides the same functionality in
Oracle9
i
and subsequent releases.
3.
