alter session in Oracle

alter session set init-param

Yet to be finished...

alter session set current_schema

conn rene/rene
create table some_table (
  data varchar2(30)
);
insert into some_table values ('This table belongs to Rene');
grant select on some_table to bi;

conn bi/pw_bi
create table some_table (
  data varchar2(30)
);
insert into some_table values ('This table belongs to bi');

select * from some_table;

DATA
------------------------------
This table belongs to bi

With altering current_schema, the default schema to find objects is changed:

alter session set current_schema=rene;
select * from some_table;

DATA
------------------------------
This table belongs to Rene

alter session set events

This statement sets a diagnostic event.

The following snippet dumps an Index Tree

column object_id new_value ObjectId

select
  object_id
from
  sys.dba_objects
where
  owner = upper('&Owner') and
  object_name = upper('&IndexName')
/

prompt
prompt alter session set events 'immediate trace name treedump level &ObjectId'
set feedback on

alter session set events 'immediate trace name treedump level &ObjectId';

List of diagnostic events.

where is the result dumped?

nls_date_format

The following statement sets the way how a date is represented as a string.

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Caveat: A usual error is to confuse months (being represented by mm) and minutes (being represented by mi).

nls_territory

See to_char.

alter session set optimizer_mode

optimizer_goal seems to be an alias for optimizer_mode (??)

As of 9i, the mode of the optimizer can be set to ALL_ROWS, FIRST_ROWS, RULE, FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000.

alter session set optimizer_mode=<mode>

alter session set plsql_debug

This instructs the compiler to generated debug information for the remainder of the session. It does not recompile any existing PL/SQL.

alter session set skip_unusable_indexes true | false

Yet to be finished...

See also the +append hint in insert statements.

alter session set sql_trace

alter session set sql_trace=true

Setting sql_trace=true is a prerequisite for using tk prof.

alter session set timed_statistics

Setting timed_statistics=true might be usefule when using tk prof.

alter session set tracefile_identifier=...

The default name for a trace files is PID_ora_TRACEID. The value of tracefile_identifier specifies the traceid for the trace file.

After setting the trace file identifier, it can be queried through the traceid column in v$process.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback