ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
This page is a collection source for information related to session monitoring and the ALTER SESSION command.
Dependencies
GV$SESSION
V$SESSIONS_COUNT
V$SESSION_OBJECT_CACHE
V$PX_SESSION
V$SESSION_CURSOR_CACHE
V$SESSION_WAIT
V$SESSION
V$SESSION_EVENT
V$SESSION_WAIT_CLASS
V$SESSION_BLOCKERS
V$SESSION_LONGOPS
V$SESSION_WAIT_HISTORY
V$SESSION_CONNECT_INFO
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;
DATA GUARD
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;
/
