Oracle Session
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose This page is a collection source for information related to session monitoring and the ALTER SESSION command.
Dependencies
APEX_WORKSPACE_SESSIONS V$SESSION V$SESSION_EVENT
GV$SESSION V$SESSION_BLOCKERS V$SESSION_LONGOPS
V$PX_SESSION V$SESSION_CONNECT_INFO V$SESSION_WAIT
 
ADVISE
Advise Clause
Sends advice to a remote database to force a distributed transaction.
ALTER SESSION ADVISE <COMMIT | ROLLBACK | NOTHING>;
ALTER SESSION ADVISE COMMIT;
 
COMMIT
Disable Commit In Procedure ALTER SESSION DISABLE COMMIT IN PROCEDURE;
ALTER SESSION DISABLE COMMIT IN PROCEDURE;
Enable Commit In Procedure ALTER SESSION ENABLE COMMIT IN PROCEDURE;
ALTER SESSION ENABLE COMMIT IN PROCEDURE;
 
DATABASE LINK
Close Database Link ALTER SESSION CLOSE DATABASE LINK <link_name>;
ALTER SESSION CLOSE DATABASE LINK remote_db;
 
DATAGUARD
Disable Data Guard
Override ALTER DATABASE GUARD for the current session
ALTER SESSION DISABLE GUARD;
ALTER SESSION DISABLE GUARD;
Enable Data Guard
Re-enables ALTER DATABASE GUARD for the current session
ALTER SESSION ENABLE GUARD;
ALTER SESSION ENABLE GUARD;
 
RESUMABLE TRANSACTIONS
Disable Resumable Space Allocation for the Session ALTER SESSION DISABLE RESUMABLE;
ALTER SESSION DISABLE RESUMABLE;
Enable Resumable Space Allocation for the Session ALTER SESSION ENABLE RESUMABLE [TIMEOUT <integer> NAME <string>];
ALTER SESSION ENABLE RESUMABLE;
 
SET CLAUSE
ASM Power Limit ALTER SESSION SET ASM_POWER_LIMIT = {value 0 to 11 DEFAULT 1};
ALTER SESSION SET ASM_POWER_LIMIT = 4;
COMMIT WRITE ALTER SESSION SET COMMIT_WRITE = '{IMMEDIATE|BATCH}, {WAIT|NOWAIT}';
ALTER SESSION SET COMMIT_WRITE BATCH NOWAIT;
Create Stored Outlines ALTER SESSION SET CREATE_STORED_OUTLINES = {TRUE|FALSE|CATEGORY_NAME};
ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;
Change Current Schema ALTER SESSION SET CURRENT_SCHEMA = <schema_name>;
conn uwclass/uwclass@pdbdev

SELECT username, schemaname
FROM gv$session;

ALTER SESSION SET CURRENT_SCHEMA = ABC;

SELECT username, schemaname
FROM gv$session;
Cursor Sharing ALTER SESSION SET CURSOR_SHARING = {SIMILAR | EXACT | FORCE};
ALTER SESSION SET CURSOR_SHARING SIMILAR;
Block Checking ALTER SESSION SET DB_BLOCK_CHECKING = {OFF | LOW | MEDIUM | FULL};
ALTER SESSION SET DB_BLOCK_CHECKING = FULL;
Create File Destination ALTER SESSION SET DB_CREATE_FILE_DEST = {directory | disk group};
ALTER SESSION SET DB_CREATE_FILE_DEST = '/app/oracle/oradata';
Create Online Log Destination ALTER SESSION SET DB_CREATE_ONLINE_LOG_DEST_[1 | 2 | 3 | 4 | 5] = {directory | disk group};
ALTER SESSION SET DB_CREATE_ONLINE_LOG_DEST_2 = '/app/oracle/logs';
File Multiblock Read Count ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = {platform dependent};
show parameter multiblock

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256;

show parameter multiblock
File Name Conversion ALTER SESSION SET DB_FILE_NAME_CONVERT = 'string1', 'string2', 'string3', 'string4', ...
ALTER SESSION SET DB_FILE_NAME_CONVERT = '/dbs/t1/','/dbs/t1/s_','dbs/t2/ ','dbs/t2/s_'
DDL Wait For Locks ALTER SESSION SET DDL_LOCKS_TIMEOUT = <seconds>;
ALTER SESSION SET DDL_LOCKS_TIMEOUT = 10;
Global Names ALTER SESSION SET GLOBAL_NAMES {TRUE | FALSE};
ALTER SESSION SET GLOBAL_NAMES = TRUE;
Hash Area Size ALTER SESSION SET HASH_AREA_SIZE = {Derived: 2 * SORT_AREA_SIZE};
ALTER SESSION SET HASH_AREA_SIZE = (
  SELECT value*2.5
  FROM gv$parameter
  WHERE name = 'sort_area_size');
NLS Semantics ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
conn uwclass/uwclass@pdbdev

SELECT *
FROM gv_$nls_parameters;

ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

SELECT *
FROM gv_$nls_parameters;

ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE;

SELECT *
FROM gv_$nls_parameters;
Oracle Script ALTER SESSION SET _ORACLE_SCRIPT = {FALSE | TRUE};
ALTER SESSION SET "_oracle_script"=TRUE;
ALTER PLUGGABLE DATABASE pdb$seed CLOSE;
ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE;

SELECT name, open_mode
FROM v$pdbs;

ALTER PLUGGABLE DATABASE pdb$seed CLOSE;
ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
ALTER SESSION SET "_oracle_script"=FALSE;
Row Archival Visibility ALTER SESSION SET ROW ARCHIVAL VISIBILITY = <ACTIVE | ALL>;
Follow the In-Database Archiving link at page bottom
Parallel Execution ALTER SESSION <ENABLE | DISABLE | FORCE> PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DDL;
Parallel Execution for DDL statements ALTER SESSION FORCE PARALLEL DDL;
ALTER SESSION FORCE PARALLEL DDL;
Parallel Execution for DML Statements ALTER SESSION < ENABLE | DISABLE | FORCE> PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DML;
Parallel Execution for Queries ALTER SESSION <ENABLE | DISABLE | FORCE> PARALLEL QUERY;
ALTER SESSION FORCE PARALLEL QUERY;
 
GLOBALIZATION
Calendar ALTER SESSION SET NLS_CALENDAR = "calendar_system"
  • Arabic Hijrah
  • English Hijrah
  • Gregorian
  • Japanese Imperial
  • Persian
  • ROC Official (Republic of China)
  • Thai Buddha
SELECT SYSDATE FROM dual;

SELECT *
FROM nls_session_parameters
WHERE parameter LIKE '%CALENDAR%';

ALTER SESSION SET NLS_CALENDAR='Thai Buddha';

SELECT *
FROM nls_session_parameters
WHERE parameter LIKE '%CALENDAR%';

SELECT SYSDATE FROM dual;

ALTER SESSION SET NLS_CALENDAR='Gregorian';

SELECT *
FROM nls_session_parameters
WHERE parameter LIKE '%CALENDAR%';

SELECT SYSDATE FROM dual;
Calendar ALTER SESSION SET NLS_COMP = {BINARY | LINGUISTIC | ANSI};
ALTER SESSION SET NLS_COMP = ANSI;
Currency ALTER SESSION SET NLS_CURRENCY = <currency_symbol>;
ALTER SESSION SET NLS_CURRENCY = 'FF' -- French Franc
ALTER SESSION SET NLS_CURRENCY = 'DM' -- Deutsche Mark
Change Date Display ALTER SESSION SET CURRENT_SCHEMA = <schema_name>;
conn uwclass/uwclass@pdbdev

SELECT created
FROM user_objects
WHERE rownum = 1;

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

SELECT created
FROM user_objects
WHERE rownum = 1;
Change Sort ALTER SESSION SET NLS_SORT = <BINARY_AI | BINARY_CI>;
-- can also be sued with numerous language specific values such as XSpanish
CREATE TABLE test (col VARCHAR2(3));
INSERT INTO test VALUES('Z');
INSERT INTO test VALUES('A');
INSERT INTO test VALUES('ä');
INSERT INTO test VALUES('a');
COMMIT;

SELECT * FROM test col ORDER BY col;

-- accent and case insensitive sort
ALTER SESSION SET NLS_SORT = binary_ai;

SELECT * FROM test col ORDER BY col;

-- case insensitive sort
ALTER SESSION SET NLS_SORT = binary_ci;

SELECT * FROM test col ORDER BY col;
 
SQL Statements
Tracing Session State
SYS UWCLASS
  SELECT sid FROM v$mystat WHERE rownum = 1;
SELECT pname_qksceserow, pvalue_qksceserow
FROM x$qksceses
WHERE pname_qksceserow LIKE '%invisible%'
AND sid_qksceserow = 170;
 
  ALTER SESSION SET "optimizer_use_invisible_indexes" = TRUE;
SELECT pname_qksceserow, pvalue_qksceserow
FROM x$qksceses
WHERE pname_qksceserow LIKE '%invisible%'
AND sid_qksceserow = 170;
 
  ALTER SESSION SET "optimizer_use_invisible_indexes" = FALSE;
Session Memory set serveroutput on

DECLARE
 CURSOR cur IS
 SELECT sn.name, ss.value
 FROM v$session vs, v$sesstat ss, v$statname sn
 WHERE vs.audsid = USERENV('SESSIONID')
 AND ss.statistic# = sn.statistic#
 AND vs.sid = ss.sid
 AND sn.name IN ('session uga memory', 'session pga memory');
BEGIN
  FOR rec IN cur
  LOOP
    dbms_output.put_line(rec.name || ':' || TO_CHAR(rec.value));
  END LOOP;
END show_memory;
/

Related Topics
Active Session History
DBMS_RESUMABLE
DBMS_SESSION
DBMS_SESSION_STATE
In-Database Archiving
Killing Sessions
SYS_CONTEXT Function
Users
What's New In 18cR3
What's New In 19cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx