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