Table of Contents
This chapter describes commands for managing transactions and sessions in detail.
Transaction and session management commands are listed in alphabetical order. A detailed description, syntax, and examples are provided for each command. The syntax diagrams follow the format of “Chapter 3. SQL Operations”. Key words and syntax components will be described in a separate table.
ALTER SESSION adjusts the session environment of the executor. The initialization or session environment variables that are changed by ALTER SESSION are valid until the executor disconnects.
Note
Refer to “9.2. ALTER SYSTEM” to adjust the entire system environment instead of only the current session.
A detailed description of ALTER SESSION follows:
-
Syntax
-
Privileges
No privileges are required.
-
Components
-
alter_session
Component Description CLOSE DATABASE LINK Closes the link of the specified database. This recovers resources that were being used by the open database link.
If a link is not closed by specifying CLOSE DATABASE LINK, the link remains open until the session is disconnected.
dblink_name Specifies the database link name. SET alter_session_params Changes the value of an initialization or session environment variable. The new value is only valid while the session remains open. -
alter_session_params
Component Description CURRENT_SCHEMA Changes the current session's schema. If a schema is not specified in a query, it will first search in the newly specified schema. Changing the schema does not change the current user or permissions.
ISOLATION LEVEL
Specifies the transaction isolation level created by the current session.
The available isolation levels are as follows:
-
SERIALIZABLE
-
READ COMMITED (The default value)
-
READ ONLY
SERIALIZABLE The transactions participating in a session run in the serializable transaction isolation mode specified in SQL-92.
If a serializable transaction tries to run a DML statement which changes rows currently being changed by another transaction, an error occurs.
READ COMMITTED If a transaction attempts to change rows that are locked by another transaction, the DML statement will wait until the lock is released. READ ONLY Normally functions the same as the SERIALIZABLE mode, and returns an error when a user attempts to alter data. TIME_ZONE Alters the time zone of the current session.
'[+|-]hh:mi' indicates the offset of the time zone expressed with an hour value and a minute value. A value between -12:00 and 14:00 can be used.
If LOCAL has been set, the time zone of the original session is modified to that of the current session. The TZNAME column in the VT_TIMEZONE_NAMES table contains a list of local names supported in the database.
param_assignment_clause Specifies a session initialization parameter. Parameters can be checked with the VT_PARAMETER table or the VT_PARAMETER synonym.
A user can only use the ALTER SESSION statement to change initialization parameters that have a Dynamic or SESSION class. Dynamic initialization parameters can be changed after the server has been activated. Because Static initialization parameters are configured in the file $TB_SID.tipthe changes will applied the next time the server is started.
SESSION can be changed by sessions. SYSTEM cannot be configured by sessions as it represents parameters that are shared by the entire database.
-
-
param_assignment_clause
Component Description param_name Specifies a session initialization parameter name. param_value Specifies a session initialization parameter value.
-
-
Example
The following example changes a session environment using ALTER SESSION:
ALTER SESSION CLOSE DATABASE LINK remote; ALTER SESSION SET CURRENT_SCHEMA = tibero; ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE; ALTER SESSION SET CURSOR_SHARING = EXACT;
ALTER SYSTEM manipulates aspects of the system such as the execution of checkpoint jobs or stopping a session, or changes system properties including the dynamic change of initialization parameters.
A detailed description of ALTER SYSTEM follows:
-
Syntax
-
Privileges
The ALTER SYSTEM system privilege is required.
-
Components
-
alter_system
Component Description CHECKPOINT Allows Tibero to execute checkpoint jobs. When a checkpoint job is executed, all updated disk blocks in the buffer cache are applied to the disk.
A checkpoint is not related to transaction commits. Disk blocks changed by uncommitted transactions are applied to the disk. Other transactions cannot see the uncommitted blocks.
FLUSH SHARED_POOL Empties the shared pool.
The shared pool contains data dictionary information. Information that is currently in use is not removed.
FLUSH BUFFER_CACHE Records the dirty blocks of the buffer cache onto a disk. Currently, this operation only records the dirty blocks onto a disk, it does not invalidate the blocks in the buffer cache. FLUSH REDO_BUFFER Forces a redo log to be flushed.
A redo log is periodically flushed even if transactions have not been committed, so executing this command is not necessary for a redo log. This command can be executed to explicitly show that the redo log has been flushed.
SWITCH LOGFILE Switches log groups. All content in a log buffer is saved to the current log group, and redo logs that are created after the switch occurs will be saved to the next log group.
Refer to the "Tibero Administrator's Guide" for more information about log files.
kill_session_clause Stops the session. This does not wait for the transaction to finish but processes as if the connection was disconnected. security_clause Opens or closes a security wallet, which is used for security functions like column encryption. ARCHIVE LOG archive_log_clause Creates archive logs. SET param_assignment_clause Configures system initialization parameters.
This is the same as the param_assignment_clause of ALTER SESSION. The difference is that this clause changes initialization parameters that have dynamic and system classes.
-
kill_session_clause
Component Description sess_id
The ID of a session to be disconnected.
IDs can be checked via VT_SESSION. An error occurs if the session does not exist.
serial_no The serial number of a session to be disconnected.
IDs can be checked via VT_SESSION. An error occurs if the session does not exist.
-
security_clause
Component Description OPEN IDENTIFIED BY "password"
Opens a security wallet.
Enter the security wallet password. password should be the same as the one entered when creating the security wallet. Create a security wallet using the program tbwallet_gen in the $TB_HOME/bin directory.
CLOSE Closes a security wallet.
-
archive_log_clause
Component Description ALL Archives all Redo logs not yet archived. Redo logs currently being used are excluded.
For more information about log files and archives, refer to "Tibero Administrator's Guide".
-
-
Examples
The following example changes system properties using ALTER SYSTEM:
ALTER SYSTEM CHECKPOINT; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM KILL SESSION (1, 1); ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password"; ALTER SYSTEM SET ENCRYPTION WALLET CLOSE; ALTER SYSTEM SET DBMS_LOG_DEST = '/tmp/log';
The following example shows an example where an error occurs because the session does not exist when KILL SESSION is used:
SQL> ALTER SYSTEM KILL SESSION (1, 1); TBR-7204: Requested session may be closed already or not exist.
COMMIT finishes the current transaction and applies the updates of the transaction to the database. At the same time, it deletes all savepoints and unlocks any locked rows.
A detailed description of COMMIT follows:
-
Syntax
-
Components
Component Description WORK Only exists for compatibility with SQL standards. Has no effect. FORCE Forcibly commits a transaction. CORRUPT_XID Forcibly commits a transaction that failed to recover.
Transactions that failed to recover can be searched with the dynamic view V$CORRUPT_XID. A forcibly committed transaction is not recovered, and cannot ensure data integrity.
CORRUPT_XID_ALL Forcibly commits all failed transactions when they fail to recover transactions. Data integrity cannot be ensured because a forcibly committed transaction is not recovered. tx_id_literal Processes a transaction that was delayed in XA. Enter a value for the column LOCAL_TRAN_ID from the view DBA_2PC_PENDING.
Note that single quotations (' ') are used.
-
Examples
The following example executes COMMIT:
COMMIT; COMMIT WORK; COMMIT FORCE '2.16.18';If a transaction that does not exist is specified, the following error may occur:
SQL> COMMIT FORCE '2.16.18'; TBR-21022: No prepared transaction found with transaction id 2.16.18.
ROLLBACK terminates the current transaction, cancels all updates made by the transaction, deletes all savepoints, and cancels all row locks.
A detailed description of ROLLBACK follows:
-
Syntax
-
Components
Component Description WORK This syntax is for compatibility with SQL standards. Has no effect. SAVEPOINT Used to roll back to a certain savepoint. If an unspecified savepoint is specified, an error may occur.
Savepoint names are not case sensitive.
savept_name Specifies a savepoint name. FORCE Used to forcibly roll back a certain transaction. tx_id_literal Used to process a transaction that was delayed in XA. Use a value from the column LOCAL_TRAN_ID of the view DBA_2PC_PENDING.
Note that single quotations (' ') are used.
-
Examples
The following example specifies a savepoint with SAVEPOINT and rolls back to the specified point using ROLLBACK:
SQL> INSERT INTO T VALUES(1); 1 row inserted. SQL> COMMIT; Commit completed. SQL> SELECT * FROM T; A --------- 1 1 selected. SQL> UPDATE T SET A=2; 1 updated. SQL> SAVEPOINT SP1; Savepoint created. SQL> UPDATE T SET A=3; 1 updated. SQL> SELECT * FROM T; A --------- 3 1 selected. SQL> ROLLBACK TO SAVEPOINT SP1; Rollback succeeded. SQL> SELECT * FROM T; A --------- 2 1 selected. SQL> ROLLBACK; Rollback succeeded. SQL> SELECT * FROM T; A --------- 1 1 selected. SQL> ROLLBACK TO SAVEPOINT SP1; TBR-21008 : No such savepoint: 'SP1'The following error will occur when a transaction that does not exist is specified:
SQL> ROLLBACK FORCE '2.16.18'; TBR-21022: No prepared transaction found with transaction id 2.16.18.
SAVEPOINT specifies a savepoint for the current transaction. To execute a partial rollback, savepoints must be specified in advance. Savepoints do not affect a transaction's execution.
A detailed description of SAVEPOINT follows:
-
Syntax
-
Components
Component Description savept_name A savepoint name should be unique in the current transaction. If the same savepoint name is specified as an existing one, the existing savepoint is deleted.
Savepoint names are not case sensitive.
-
Examples
The following example specifies a savepoint using SAVEPOINT:
SAVEPOINT sp1;
SET ROLE activates or inactivates a role granted to a user.
Note
1. In order to create, change, or delete a role, refer to “7.37. CREATE ROLE”, “7.11. ALTER ROLE”, or “7.58. DROP ROLE” respectively.
2. In order to grant or revoke a role, refer to “7.70. GRANT” or “7.75. REVOKE” respectively.
A detailed description of SET ROLE follows:
-
Syntax
-
Privileges
To use SET ROLE, the user requires access to the role to be activated or inactivated.
-
Components
Component Description role_name Lists all roles to be activated among the granted roles. Any roles excluded from this clause are automatically inactivated. IDENTIFIED BY If a password is set for a role, the password should be entered when the role is used.
Refer to ALTER ROLE and CREATE ROLE for details on how to set a password for a role.
ALL Specifies that all granted roles will be activated. If a role requires a password, ALL cannot be used unless the role is excluded by the EXCEPT clause. EXCEPT When using the ALL clause to activate all granted roles, this clause specifies the roles to be excluded. In other words, all roles except those in the EXCEPT clause are activated. -
Examples
The following example grants roles to a user and activates the roles using SET ROLE:
SQL> CONN sys/tibero Connected. SQL> CREATE USER u1 IDENTIFIED BY xxx; -- Creates a user u1. User created. SQL> GRANT CREATE SESSION TO u1; Granted. SQL> CREATE ROLE a; -- Creates a role. Role created. SQL> CREATE ROLE b; Role created. SQL> CREATE ROLE c; Role created. SQL> CREATE ROLE d IDENTIFIED BY aaa; --Some roles use a password. Role created. SQL> CREATE ROLE e IDENTIFIED BY bbb; Role created. SQL> GRANT a, b, c, d, e TO u1; -- Grants roles to user U1. Granted. SQL> CONN u1/xxx Connected. SQL> SET ROLE a, b, c; Set. SQL> SELECT * FROM session_roles; ROLE ------------------------------ A B C 3 rows selected. SQL> SET ROLE a, b, c, d; TBR-7181: need password to enable the role SQL> SET ROLE c, d IDENTIFIED BY aaa, e IDENTIFIED BY bbb; Set. SQL> SELECT * FROM session_roles; ROLE ------------------------------ C D E 3 rows selectedIn the example above, roles A, B, C, D, and E are created. Roles D and E have passwords. The roles A, B, and C that do not have a password can be activated without restriction, but roles D and E can only be activated when the password is entered using the IDENTIFIED BY clause.
In the last SET ROLE command, only the roles C, D, and E are activated, and the roles A and B are not. Therefore, the roles A and B that were previously activated are now inactivated.
The following example illustrates the use of EXCEPT:
SQL> SET ROLE ALL; TBR-7181: need password to enable the role SQL> SET ROLE ALL EXCEPT d, e; Set. SQL> SELECT * FROM session_roles; ROLE ------------------------------ A B C 3 rows selected.
In the example above, roles D and E have passwords. A password cannot be entered in the ALL clause, so roles D and E cannot be activated using the ALL clause. Roles that have passwords can be excluded using the EXCEPT clause, and the remaining roles can be activated using the ALL clause.
SET TRANSACTION specifies an isolation level or a name for the current transaction. This command should be the first statement when beginning a transaction.
A detailed description of SET TRANSACTION follows:
-
Syntax
-
Components
Component Description ISOLATION LEVEL
or
ISOLATION_LEVEL
Specifies the isolation level of the transaction.
The isolations levels are as follows:
-
SERIALIZABLE
-
READ COMMITED
-
READ ONLY
The default value is READ COMMITTED.
SERIALIZABLE All transactions participating in a session work in the serializable transaction isolation mode specified in SQL-92.
When a transaction operating in SERIALIZABLE mode attempts to change a row which has already been committed by another row, the row won't be updated and error will occur.
READ COMMITTED In this mode, if a transaction has changed a row and not yet committed it, if another transaction tries to change the row using a DML statement, the second transaction will wait due to the lock on the row. READ ONLY Normally works the same as the READ COMMITED mode, and attempting to modify data causes an error. NAME Specifies a transaction name. Transaction names can be searched via V$TRANSACTION.
The default value is NULL.
transaction_name The transaction name to be specified. -
-
Examples
The following example sets the current transaction to READ COMMITTED and searches the V$TRANSACTION dynamic view:
SQL> SET TRANSACTION ISOLATION_LEVEL READ COMMITTED; Set. SQL> SELECT name, state FROM v$transaction; NAME STATE -------------------- ---------- 3 1 row selected. SQL> SET TRANSACTION NAME 'tx1'; TBR-7191 : Unable to execute SET TRANSACTION: transaction has already started.As shown above, if SET TRANSACTION is not the first command, an error will occur.
A transaction name can be specified as follows.
SQL> SELECT name, state FROM v$transaction; NAME STATE -------------------- ---------- 0 row selected. SQL> SET TRANSACTION NAME 'tx1'; Set. SQL> SELECT name, state FROM v$transaction; NAME STATE -------------------- ---------- tx1 3 1 row selected.