You Asked
There are many things the can be changed in a session by ALTER session set ...
There are times when we, DBAs, are dealing with applications tjat were developed by outsourced companies and don't have access to the code. We need to find out of the application changes anything on the fly within a user's session.
Is/are there a view(s) or V$table that will show all the settings made by ALTER session ?
and Tom said...
Not in 9i, but in 10g - we can get some of them.
I've chopped out ones I didn't feel relevant (like log_archive* things) from the following, but this shows what you could expect to see in 10g, <<<<====== indicates you "can" see this parameter from another session:
ops$tkyte@ORA10GR2> select a.name,
2 case when b.name is not null then '<<<<=====' end,
3 a.description
4 from ( select name , description
5 from v$parameter
6 where isses_modifiable = 'TRUE' ) a,
7 ( select distinct name
8 from v$ses_optimizer_env ) b
9 where a.name = b.name (+);
NAME CASEWHENB DESCRIPTION
------------------------------ --------- ------------------------------
tracefile_identifier trace file custom identifier
timed_statistics maintain internal timing stati
stics
timed_os_statistics internal os statistic gatherin
g interval in seconds
nls_language NLS language name
nls_territory NLS territory name
nls_sort NLS linguistic definition name
nls_date_language NLS date language name
nls_date_format NLS Oracle date format
nls_currency NLS local currency symbol
nls_numeric_characters NLS numeric characters
nls_iso_currency NLS ISO currency territory nam
e
nls_calendar NLS calendar system name
nls_time_format time format
nls_timestamp_format time stamp format
nls_time_tz_format time with timezone format
nls_timestamp_tz_format timestampe with timezone forma
t
nls_dual_currency Dual currency symbol
nls_comp NLS comparison
nls_length_semantics create columns using byte or c
har semantics by default
nls_nchar_conv_excp NLS raise an exception instead
of allowing implicit conversi
on
db_file_multiblock_read_count db block to be read each IO
ddl_wait_for_locks Disable NOWAIT DML lock acquis
itions
resumable_timeout set resumable_timeout
db_block_checking header checking and data and i
ndex block checking
recyclebin recyclebin processing
create_stored_outlines create stored outlines for DML
statements
global_names enforce that database links ha
ve same name as remote databas
e
session_cached_cursors Number of cursors to cache in
a session.
parallel_min_percent minimum percent of threads req
uired for parallel query
cursor_sharing <<<<===== cursor sharing mode
parallel_instance_group instance group to use for all
parallel operations
hash_area_size <<<<===== size of in-memory hash work ar
ea
max_dump_file_size Maximum size (blocks) of dump
file
object_cache_optimal_size optimal size of the user sessi
on's object cache in bytes
object_cache_max_size_percent percentage of maximum size ove
r optimal of the user session'
s object cache
commit_write transaction commit log write b
ehaviour
optimizer_features_enable <<<<===== optimizer plan compatibility p
arameter
sort_area_size <<<<===== size of in-memory sort work ar
ea
sort_area_retained_size <<<<===== size of in-memory sort work ar
ea retained between fetch call
s
sql_trace enable SQL trace
optimizer_mode <<<<===== optimizer mode
star_transformation_enabled <<<<===== enable the use of star transfo
rmation
optimizer_index_cost_adj <<<<===== optimizer index cost adjustmen
t
optimizer_index_caching <<<<===== optimizer percent index cachin
g
query_rewrite_enabled <<<<===== allow rewrite of queries using
materialized views if enabled
query_rewrite_integrity <<<<===== perform rewrite using material
ized views with desired integr
ity
sql_version sql language version parameter
for compatibility issues
workarea_size_policy <<<<===== policy used to size SQL workin
g areas (MANUAL/AUTO)
optimizer_dynamic_sampling <<<<===== optimizer dynamic sampling
statistics_level <<<<===== statistics level
skip_unusable_indexes <<<<===== skip unusable indexes if set t
o TRUE
olap_page_pool_size size of the olap page pool in
bytes
asm_power_limit number of processes for disk r
ebalancing
sqltune_category Category qualifier for applyin
g hintsets
91 rows selected.
Is this answer out of date? If it is, please let us know via a Comment