Oracle 5.0 Reference Manual Download

Page: 528 / 3232

Using System Variables

508

•

LOCAL

and

@@local.

are synonyms for

SESSION

and

@@session.

• If no modifier is present,

SET

changes the session variable.

SET

statement can contain multiple variable assignments, separated by commas. If you set several

system variables, the most recent

GLOBAL

SESSION

modifier in the statement is used for following

variables that have no modifier specified.

Examples:

SET sort_buffer_size=10000;

SET @@local.sort_buffer_size=10000;

SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;

SET @@sort_buffer_size=1000000;

SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

The

@@var_name

syntax for system variables is supported for compatibility with some other database

systems.

If you change a session system variable, the value remains in effect until your session ends or until you
change the variable to a different value. The change is not visible to other clients.

If you change a global system variable, the value is remembered and used for new connections until
the server restarts. (To make a global system variable setting permanent, you should set it in an option
file.) The change is visible to any client that accesses that global variable. However, the change affects
the corresponding session variable only for clients that connect after the change. The global variable
change does not affect the session variable for any client that is currently connected (not even that of
the client that issues the

SET GLOBAL

statement).

To prevent incorrect usage, MySQL produces an error if you use

SET GLOBAL

with a variable that

can only be used with

SET SESSION

or if you do not specify

GLOBAL

(or

@@global.

) when setting a

global variable.

To set a

SESSION

variable to the

GLOBAL

value or a

GLOBAL

value to the compiled-in MySQL default

value, use the

DEFAULT

keyword. For example, the following two statements are identical in setting the

session value of

max_join_size

[469]

to the global value:

SET max_join_size=DEFAULT;

SET @@session.max_join_size=@@global.max_join_size;

Not all system variables can be set to

DEFAULT

. In such cases, use of

DEFAULT

results in an error.

You can refer to the values of specific global or session system variables in expressions by using one
of the

-modifiers. For example, you can retrieve values in a

SELECT

statement like this:

SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

When you refer to a system variable in an expression as

@@var_name

(that is, when you do not

specify

@@global.

@@session.

), MySQL returns the session value if it exists and the global value

otherwise. (This differs from

SET @@var_name = value

, which always refers to the session value.)

Note

Some variables displayed by

SHOW VARIABLES

may not be available using

SELECT @@var_name

syntax; an

Unknown system variable

occurs.

As a workaround in such cases, you can use

SHOW VARIABLES LIKE

'var_name'

Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not
to set the value with

SET

at runtime. On the other hand, with

SET

you can assign a variable's value

using an expression, which is not true when you set a variable at server startup. For example, the first
of the following lines is legal at server startup, but the second is not:

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands