Oracle Session
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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

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

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-2014 Daniel A. Morgan All Rights Reserved