Oracle Sessions
Version 11.2.0.2
 
Alter Session
Advise Clause
Sends advice to a remote database to force a distributed transaction.		 ALTER SESSION ADVISE <COMMIT | ROLLBACK | NOTHING>;
ALTER SESSION ADVISE COMMIT;
Close Database Link ALTER SESSION CLOSE DATABASE LINK <link_name>;
ALTER SESSION CLOSE DATABASE LINK remote_db;
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;
Disable Guard
Override ALTER DATABASE GUARD for the current session		 ALTER SESSION DISABLE GUARD;
ALTER SESSION DISABLE GUARD;
Enable Guard
Re-enables ALTER DATABASE GUARD for the current session		 ALTER SESSION ENABLE GUARD;
ALTER SESSION ENABLE GUARD;
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;
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};
TBD
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;
DDL Wait For Locks ALTER SESSION SET DDL_WAIT_FOR_LOCKS = ?
TBD
Global Names ALTER SESSION SET GLOBAL_NAME {TRUE | FALSE};
ALTER SESSION SET GLOBAL_NAME 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');
Change Schema ALTER SESSION SET CURRENT_SCHEMA = <schema_name>;
conn uwclass/uwclass

SELECT username, schemaname
FROM gv$session;

ALTER SESSION SET CURRENT_SCHEMA = ABC;

SELECT username, schemaname
FROM gv$session;
Change default to sematics 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;
 
Globalization Support
Calendar ALTER SESSION SET NLS_CALENDAR = "calendar_system"
  • Arabic Hijrah
  • English Hijrah
  • Gregorian
  • Japanese Imperial
  • Persian
  • ROC Official (Republic of China)
  • Thai Buddha
TBD
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

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>;

and 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;
 
Select Statements Sample Clause
Enable Parallel Execution for DDL statements ALTER SESSION < ENABLE | DISABLE | FORCE> PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DDL;
Force Parallel Execution for DDL statements ALTER SESSION FORCE PARALLEL DDL;
ALTER SESSION FORCE PARALLEL DDL;
Enable Parallel Execution for DML Statements ALTER SESSION < ENABLE | DISABLE | FORCE> PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DML;
Enable Parallel Execution for Queries ALTER SESSION <ENABLE | DISABLE | FORCE> PARALLEL QUERY;
ALTER SESSION FORCE PARALLEL QUERY;
 
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
DBMS_RESUMABLE
DBMS_SESSION
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan