ALTER SESSION

Modify the current user session.

Syntax:

   ALTER SESSION ADVISE {COMMIT | ROLLBACK | NOTHING}
   ALTER SESSION CLOSE DATABASE LINK link_name
   ALTER SESSION {ENABLE | DISABLE} COMMIT IN PROCEDURE
   ALTER SESSION {ENABLE | DISABLE | FORCE} PARALLEL {DML|DDL|QUERY} [PARALLEL int]
   ALTER SESSION ENABLE RESUMABLE {TIMEOUT int} {NAME string}
   ALTER SESSION DISABLE RESUMABLE
   ALTER SESSION SET option(s) [COMMENT='text']

Options:
   CONSTRAINT[S] {IMMEDIATE|DEFERRED|DEFAULT}
   CREATE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' }
   CURRENT_SCHEMA = schema
   CURSOR_SHARING = {FORCE | EXACT}

   DB_BLOCK_CHECKING = {TRUE | FALSE}
   DB_CREATE_FILE_DEST = directory
   DB_CREATE_ONLINE_LOG_DEST_N = directory
   DB_FILE_MULTIBLOCK_READ_COUNT = int

   ERROR_ON_OVERLAP_TIME = {TRUE | FALSE}

   FILESYSTEMIO_OPTIONS = {none | setall | directIO | asynch}
   FLAGGER = {ENTRY | INTERMEDIATE | FULL | OFF}

   GLOBAL_NAMES = {TRUE | FALSE}

   HASH_AREA_SIZE = int
   HASH_JOIN_ENABLED = {TRUE | FALSE}

   INSTANCE = int
   ISOLATION_LEVEL = {SERIALIZABLE | READ_COMMITTED}

   LOG_ARCHIVE_DEST_n (read the Oracle8i Reference for more on this)
   LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER} 
   LOG_ARCHIVE_MIN_SUCCEED_DEST = int

   MAX_DUMP_FILE_SIZE = { size | UNLIMITED } 

   NLS_CALENDAR = 'text'
   NLS_COMP = 'text' 
   NLS_CURRENCY = 'text'
   NLS_DATE_FORMAT = 'fmt'
   NLS_DATE_LANGUAGE = language
   NLS_DUAL_CURRENCY = 'text'
   NLS_ISO_CURRENCY = territory
   NLS_LANGUAGE = language
   NLS_LENGTH_SEMANTICS = {CHAR | BYTE}
   NLS_NCHAR_CONV_EXCP = {TRUE | FALSE}
   NLS_NUMERIC_CHARACTERS = 'text'
   NLS_SORT = {sort | BINARY}
   NLS_TERRITORY = territory
   NLS_TIMESTAMP_FORMAT = "datetime_format"
   NLS_TIMESTAMP_TZ_FORMAT = "datetime_format"

   OBJECT_CACHE_MAX_SIZE_PERCENT = int
   OBJECT_CACHE_OPTIMAL_SIZE = int
   OLAP_PAGE_POOL_SIZE = int
   OPTIMIZER_DYNAMIC_SAMPLING = int
   OPTIMIZER_INDEX_CACHING = int
   OPTIMIZER_INDEX_COST_ADJ = int
   OPTIMIZER_MAX_PERMUTATIONS = int
   OPTIMIZER_MODE = {ALL_ROWS | FIRST_ROWS | RULE | CHOOSE}
   ORACLE_TRACE_ENABLE = {TRUE | FALSE}

   PARALLEL_INSTANCE_GROUP = ' text '
   PARALLEL_MIN_PERCENT = int
   PARTITION_VIEW_ENABLED = {TRUE | FALSE}
   PLSQL_COMPILER_FLAGS = {[DEBUG | NON_DEBUG], [INTERPRETED | NATIVE] } 
   PLSQL_DEBUG = {TRUE | FALSE}

   QUERY_REWRITE_ENABLED = {FORCE | TRUE | FALSE}
   QUERY_REWRITE_INTEGRITY = {enforced | trusted | stale_tolerated}

   REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}

   SESSION_CACHED_CURSORS = int
   SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
   SORT_AREA_RETAINED_SIZE = int
   SORT_AREA_SIZE = integer
   SORT_MULTIBLOCK_READ_COUNT = int
   SQL_TRACE = {TRUE | FALSE}
   STAR_TRANSFORMATION_ENABLED = {TRUE | FALSE | TEMP_DISABLE}
   STATISTICS_LEVEL = {ALL | TYPICAL | BASIC}

   TIMED_OS_STATISTICS = int
   TIMED_STATISTICS = {TRUE | FALSE}
   TRACE_ENABLED = {TRUE | FALSE}

   UNDO_SUPRESS_ERRORS = {TRUE | FALSE}
   USE_PRIVATE_OUTLINES = {TRUE | FALSE| 'category_name' }
   USE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' }

   WORKSIZE_AREA_POLICY = {AUTO | MANUAL}

Parameters shown in Green&Bold are Session Parameters, 
generally these are not reflected in V$PARAMETER
(Some sessions paramaters are synonymous with parameter settings in init.ora)

DEPRACATED OPTIONS…

Version 8 Options - obsolete in v9
   FAST_START_IO_TARGET = int
   HASH_MULTIBLOCK_IO_COUNT = int
   LABEL = {'text' | DBLOW | DBHIGH | OSLABEL}
   OPTIMIZER_PERCENT_PARALLEL = int
   PARALLEL_BROADCAST_ENABLED = {TRUE | FALSE}
   PLSQL_V2_COMPATIBILITY = {TRUE | FALSE}

Version 7 Options - obsolete in v8
   CLOSE_CACHED_OPEN_CURSORS = {TRUE | FALSE}
   NLS_ISO_CURRENCY = territory
   MLS_LABEL_FORMAT = 'fmt'
   OPTIMISER_GOAL - is now OPTIMISER_MODE
   SCHEMA=schema_name

"Knowledge speaks, but wisdom listens." - Jimi Hendrix

Related Oracle Commands:

ALTER SYSTEM ENABLE RESTRICTED SESSION
ALTER SYSTEM KILL SESSION
ALTER SYSTEM SET LICENSE_MAX_SESSIONS
SET TRANSACTION USE ROLLBACK SEGMENT
DBMS_SESSION
COMMIT
DROP DATABASE LINK
ROLLBACK

Related Views:

 DBA_PENDING_TRANSACTIONS
                                           USER_RESOURCE_LIMITS
 V$SESSION
 V$SESSION_CONNECT_INFO
 V$SESSION_CURSOR_CACHE
 V$SESSION_EVENT
 V$SESSION_LONGOPS
 V$SESSION_OBJECT_CACHE
 V$SESSION_WAIT
 V$SESSTAT
 V$SESS_IO

 
Copyright © 1999-2024 SS64.com
Some rights reserved