SYSTEM AND SESSION

Table of Contents

ALTER SYSTEM

This section explains about ALTER SYSTEM commands.

ALTER SYSTEM KILL SESSION SessionID;

It ends the specified session with SessionID. Only SYS user can perform this syntax, but it is not possible to KILL its own session.

ALTER SYSTEM CANCEL SESSION SessionID;

It cancels the specified session with SessionID. It cancels the currently executing operation rather than disconnecting it, and returns the error code to the user notifying the operation has been canceled. As with the KILL statement, it is not possible to cancel the session if its own system is connected.

ALTER SYSTEM CHECK DISK_USAGE;

It revises the value of DC_TABLE_FILE_SIZE that shows a disk usage of log table in V$STORAGE. When the process failure or power failure occurs, the disk usage may show incorrect. With this command, it reads the exact value from the file system, however, it is better not to use it since it can cause a considerable overload to file system.

ALTER SYSTEM INSTALL LICENSE;

It installs the license file in the default location "$MACHBASE_HOME/conf/license.dat". It checks whether the license file is legitimate before installing it.

ALTER SYSTEM INSTALL LICENSE='PATH';

It installs the license file in a specified location. An error occurs when the location doesn't exist or the license file it not legitimate. The absolute path must be inserted. It checks whether the license file is legitimate before installing it.

ALTER SESSION

This section explains about ALTER SESSION commands.

ALTER SESSION SET SQL_LOGGING=Flag;

It determines whether to leave messages in the trace log of the session. The messages are bit flags and can use the following values.
1: It logs any error that occurs in Parsing, Validation and Optimization stages.
2: It logs the results of conducting DDL statements.

For example, if the value of flag is 2, it only logs DDL. If the value is 3, it logs both error and DDL. Below is an example of changing the logging flag of the session and leaving logging errors.

[mach@localhost bin]$ machsql
=================================================================
     Machbase Client Query Utility
     Release Version 3.0.0
     Copyright 2014, Machbase Inc. or its subsidiaries.
     All Rights Reserved.
=================================================================
Machbase server address (Default:127.0.0.1): 
Machbase user ID (Default:SYS)
Machbase user password: 
MACHBASE_CONNECT_MODE=INET, PORT=5656
Mach> alter session set SQL_LOGGING=1;
Altered successfully.
Mach> exit

ALTER SESSION SET DEFAULT_DATE_FORMAT='String';

It sets the default date format of the session. When the server is running, the value of global property (DEFAULT_DATE_FORMAT), is set to the session property and then, the value will be "YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn". To modify this date format for the session, use "ALTER SESSION SET DEFAULT_DATE_FORMAT='String';" command. Each session in v$session has the default date format.

Syntax:

ALTER SESSION SET DEFAULT_DATE_FORMAT='YYYY-MM-DD';

Example:

Mach> CREATE TABLE time_table (time datetime);
Created successfully.

Mach> SELECT DEFAULT_DATE_FORMAT from v$session;
default_date_format                                                               
-----------------------------------------------
YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn                                                 
[1] row(s) selected.

Mach> INSERT INTO time_table VALUES(TO_DATE('2016-11-11'));
[ERR-00300 : Invalid date format or input string.([2016-11-11]:[%Y-%m-%d %H:%M:%S %0:%1:%2])]

Mach> ALTER SESSION SET DEFAULT_DATE_FORMAT='YYYY-MM-DD';
Altered successfully.

Mach> SELECT DEFAULT_DATE_FORMAT from v$session;

default_date_format                                                               
----------------------------------------------
YYYY-MM-DD                                                                        
[1] row(s) selected.

Mach> INSERT INTO time_table VALUES(TO_DATE('2016-11-11'));
1 row(s) inserted.

Mach> SELECT * FROM time_table;

TIME                               
----------------------------------
2016-11-11

[1] row(s) selected.

ALTER SESSION SET SHOW_HIDDEN_COLS=Value;

It determines whether to display the hidden column (_arrival_time) when executing SELECT statement in the session. When the server is running, the vlaue of global property (SHOW_HIDDEN_COLS) is set to 0 which does not display arrival times. If it is necessary to display "arrival time", change the value to 1.
In v$session, there is SHOW_HIDDEN_COLS value that is set in each session, and you can check it.

Syntax:

ALTER SESSION SET SHOW_HIDDEN_COLS = column_value;

Example:

Mach> SELECT * FROM  v$session;
ID                   CLOSED      USER_ID     LOGIN_TIME                      SQL_LOGGING SHOW_HIDDEN_COLS
-----------------------------------------------------------------------------------------------------------------
DEFAULT_DATE_FORMAT                                                               HASH_BUCKET_SIZE
------------------------------------------------------------------------------------------------------
1                    0           1           2015-04-29 17:23:56 248:263:000 3           0
YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn                                                 20011
[1] row(s) selected.                             
Mach> ALTER SESSION SET SHOW_HIDDEN_COLS=1;
Altered successfully.
Mach> SELECT * FROM v$session;
_ARRIVAL_TIME                   ID                   CLOSED      USER_ID     LOGIN_TIME                      SQL_LOGGING
--------------------------------------------------------------------------------------------------------------------------------
SHOW_HIDDEN_COLS DEFAULT_DATE_FORMAT                                                               HASH_BUCKET_SIZE
------------------------------------------------------------------------------------------------------------------------
1970-01-01 09:00:00 000:000:000 1                    0           1           2015-04-29 17:23:56 248:263:000 3
1           YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn                                                 20011
[1] row(s) selected.

ALTER SESSION SET FEEDBACK_APPEND_ERROR=flag

It determines whether to send append error messages of the session to the client program. The values above can be used as the error message.

0: Send the error message
1: Do not send the error message

Syntax:

ALTER SESSION SET FEEDBACK_APPEND_ERROR=flag;

Example:

mach> ALTER SESSION SET FEEDBACK_APPEND_ERROR=0;
Altered successfully.

ALTER SESSION SET HASH_BUCKET_SIZE=Value;

This statement is used to specify the size of a hash table that GROUP BY and DISTINCT are employing for their operations. If the value is set too big, each hash operation uses too much memory, and if the value is set too small, it degrades the query performance as the hash bucket conflict occurs. It is recommended to set the number at the number of GROUP * 1.5 or 3.0.

Syntax:

ALTER SESSION SET HASH_BUCKET_SIZE=value;

Example:

Mach> ALTER SESSION SET HASH_BUCKET_SIZE=65536;
Altered successfully.

Mach> SELECT * FROM v$session;
_ARRIVAL_TIME                   ID                   CLOSED      USER_ID     LOGIN_TIME                      SQL_LOGGING
--------------------------------------------------------------------------------------------------------------------------------
SHOW_HIDDEN_COLS DEFAULT_DATE_FORMAT                                                               HASH_BUCKET_SIZE
------------------------------------------------------------------------------------------------------------------------
1970-01-01 09:00:00 000:000:000 1                    0           1           2015-04-29 17:23:56 248:263:000 3
1           YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn                                                 65536
[1] row(s) selected.

ALTER SESSION SET MAX_QPX_MEM=Value;

It is used to specify the maximum size of memory available for allocation when executing a SQL statement, which includes the GROUP BY, DISTINCT and ORDER BY. If an attempt is made to allocate the larger size than the maximum value, the system cancels the operation of SQL statement and process it as an error. In addition, it records the error code and error message that contain the query statement in machbase.trc file.

Syntax:

ALTER SESSION SET MAX_QPX_MEM=Value;

Example:

Mach> ALTER SESSION SET MAX_QPX_MEM=1073741824;
Altered successfully.

Mach> SELECT * FROM v$session;
ID                   CLOSED      USER_ID     LOGIN_TIME                      SQL_LOGGING SHOW_HIDDEN_COLS FEEDBACK_APPEND_ERROR
----------------------------------------------------------------------------------------------------------------------------------------
DEFAULT_DATE_FORMAT                                                               HASH_BUCKET_SIZE MAX_QPX_MEM
----------------------------------------------------------------------------------------------------------------------------
324                  0           1           2015-07-14 10:53:46 124:627:000 11          0           0
YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn                                                 20011       1073741824
[1] row(s) selected.
Mach>

results matching ""

    No results matching ""