Oracle Sessions and Session Commands

Oracle Sessions Version 11.2.0.3

General Information

Data Dictionary Objects

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

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;

Enable Commit In Procedure

ALTER SESSION ENABLE COMMIT IN PROCEDURE;

Disable Data Guard
Override ALTER DATABASE GUARD for the current session

ALTER SESSION DISABLE GUARD;

Enable Data Guard
Re-enables ALTER DATABASE GUARD for the current session

ALTER SESSION ENABLE GUARD;

Disable Resumable Space Allocation for the Session

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

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

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

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

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;

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