Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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;
/
