CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Sessions
Version 11.1
 
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_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;
 
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

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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [41 users online]    © 2010 psoug.org