Database Reference
In-Depth Information
Usage Notes
When there is no environment variable that matches the name passed in parameter
var
, an
empty string is returned in
val
. If the maximum length of
val
is insufficient to hold the value of
the environment variable, the error “ORA- 06502: PL/SQL: numeric or value error: character
string buffer too small” is thrown. To avoid this, I recommend that you always declare
val
as large
as possible. In SQL*Plus the maximum size is
VARCHAR2(4000)
as opposed to
VARCHAR2(32767)
in
PL/SQL. Environment variable names on UNIX systems are case-sensitive, whereas they are
not case-sensitive on Windows systems.
Examples
The following PL/SQL code may be used to retrieve the value of the environment variable
ORACLE_HOME
in SQL*Plus:
SQL> SET AUTOPRINT ON
SQL> VARIABLE val VARCHAR2(4000)
SQL> BEGIN
dbms_system.get_env('ORACLE_HOME', :val);
END;
/
PL/SQL procedure successfully completed.
VAL
-------------------------------------------------
/opt/oracle/product/10.2
KCFRMS Procedure
This procedure resets the maximum wait time for each event (
V$SESSION_EVENT.MAX_WAIT
), the
maximum read time for a data file (
V$FILESTAT.MAXIORTM
), and the maximum write time for a
data file (
V$FILESTAT. MAXIOWTM
) to zero. This procedure might be useful in an environment
where peaks in file access (
V$FILESTAT
) or wait events (
V$SESSION_EVENT
) are observed. By saving
the values from these V$ views just before calling
DBMS_SYSTEM.KCFRMS
on an hourly basis, it
would be possible to determine at which times during the day and to what extent peaks occur.
Syntax
DBMS_SYSTEM.KCFRMS();
Usage Notes
The values are set to zero for all sessions in
V$SESSION_EVENT
, not solely the session that calls the
procedure
DBMS_SYSTEM.KCFRMS
.
Examples
Following are some sample rows of
V$SESSION_EVENT
before calling
DBMS_SYSTEM.KCFRMS
(all
timings are in seconds; the value of the column
MAX_WAIT
is natively in centiseconds):
