ORA-02248 invalid option for ALTER SESSION 오류는 ALTER SESSION 명령문을 실행할 때, 유효하지 않은 옵션을 설정하는 경우에 발생합니다. ALTER SESSION 옵션은 오라클 버전마다 다르기 때문에, 특정 버전에서 실행이 되던 명령어가 다른 버전에서 오류가 발생하기도 합니다.
ORA-02248: invalid option for ALTER SESSION 해결하기
다음은 해당 오류 예제 자료이며, 테스트 환경은 오라클 11g입니다. 해당 오라클 스크립트 옵션은 11g 버전에 존재하지 않기에 "ORA-02248: invalid option for ALTER SESSION" 오류가 발생합니다. 참고로 해당 옵션은 오라클 클라우드 버전에서는 가능합니다.
ALTER SESSION SET "_oracle_script" = TRUE;
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION다음 세션 변경 옵션은 정상적으로 실행이 되는 예시입니다.
ALTER SESSION SET timed_statistics = TRUE;
Session altered.시스템 파라미터 뷰에서 가능한 옵션을 확인할 수 있습니다. 테스트 환경으로 구성한 11g에서는 340개 정도의 옵션을 확인할 수 있으며 내용은 다음과 같습니다.
SELECT NAME, DESCRIPTION
FROM V$PARAMETER
ORDER BY NUM;
--NAME
--DESCRIPTION
lock_name_space
lock name space used for generating lock names for standby/clone database
processes
user processes
sessions
user and system sessions
timed_statistics
maintain internal timing statistics
timed_os_statistics
internal os statistic gathering interval in seconds
resource_limit
master switch for resource limit
license_max_sessions
maximum number of non-system user sessions allowed
license_sessions_warning
warning level for number of non-system user sessions
cpu_count
number of CPUs for this instance
instance_groups
list of instance group names
event
debug event control - default null string
sga_max_size
max total SGA size
use_large_pages
Use hugepages if available (TRUE/FALSE/ONLY)
pre_page_sga
pre-page sga for process
shared_memory_address
SGA starting address (low order 32-bits on 64-bit platforms)
hi_shared_memory_address
SGA starting address (high order 32-bits on 64-bit platforms)
use_indirect_data_buffers
Enable indirect data buffers (very large SGA on 32-bit platforms)
lock_sga
Lock entire SGA in physical memory
shared_pool_size
size in bytes of shared pool
large_pool_size
size in bytes of large pool
java_pool_size
size in bytes of java pool
streams_pool_size
size in bytes of the streams pool
shared_pool_reserved_size
size in bytes of reserved area of shared pool
java_soft_sessionspace_limit
warning limit on size in bytes of a Java sessionspace
java_max_sessionspace_size
max allowed size in bytes of a Java sessionspace
spfile
server parameter file
instance_type
type of instance to be executed
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 name
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
timestamp with timezone format
nls_dual_currency
Dual currency symbol
nls_comp
NLS comparison
nls_length_semantics
create columns using byte or char semantics by default
nls_nchar_conv_excp
NLS raise an exception instead of allowing implicit conversion
fileio_network_adapters
Network Adapters for File I/O
filesystemio_options
IO operations on filesystem files
disk_asynch_io
Use asynch I/O for random access devices
tape_asynch_io
Use asynch I/O requests for tape devices
dbwr_io_slaves
DBWR I/O slaves
backup_tape_io_slaves
BACKUP Tape I/O slaves
resource_manager_cpu_allocation
Resource Manager CPU allocation
resource_manager_plan
resource mgr top plan
cluster_interconnects
interconnects for RAC use
file_mapping
enable file mapping
gcs_server_processes
number of background gcs server processes to start
active_instance_count
number of active instances in the cluster database
sga_target
Target size of SGA
memory_target
Target size of Oracle SGA and PGA memory
memory_max_target
Max size for Memory Target
control_files
control file names list
db_file_name_convert
datafile name convert patterns and strings for standby/clone db
log_file_name_convert
logfile name convert patterns and strings for standby/clone db
control_file_record_keep_time
control file record keep time in days
db_block_buffers
Number of database blocks cached in memory
db_block_checksum
store checksum in db blocks and check during reads
db_ultra_safe
Sets defaults for other parameters that control protection levels
db_block_size
Size of database block in bytes
db_cache_size
Size of DEFAULT buffer pool for standard block size buffers
db_2k_cache_size
Size of cache for 2K buffers
db_4k_cache_size
Size of cache for 4K buffers
db_8k_cache_size
Size of cache for 8K buffers
db_16k_cache_size
Size of cache for 16K buffers
db_32k_cache_size
Size of cache for 32K buffers
db_keep_cache_size
Size of KEEP buffer pool for standard block size buffers
db_recycle_cache_size
Size of RECYCLE buffer pool for standard block size buffers
db_writer_processes
number of background database writer processes to start
buffer_pool_keep
Number of database blocks/latches in keep buffer pool
buffer_pool_recycle
Number of database blocks/latches in recycle buffer pool
db_flash_cache_file
flash cache file for default block size
db_flash_cache_size
flash cache size for db_flash_cache_file
db_cache_advice
Buffer cache sizing advisory
compatible
Database will be completely compatible with this software version
log_archive_dest_1
archival destination #1 text string
log_archive_dest_2
archival destination #2 text string
log_archive_dest_3
archival destination #3 text string
log_archive_dest_4
archival destination #4 text string
log_archive_dest_5
archival destination #5 text string
log_archive_dest_6
archival destination #6 text string
log_archive_dest_7
archival destination #7 text string
log_archive_dest_8
archival destination #8 text string
log_archive_dest_9
archival destination #9 text string
log_archive_dest_10
archival destination #10 text string
log_archive_dest_11
archival destination #11 text string
log_archive_dest_12
archival destination #12 text string
log_archive_dest_13
archival destination #13 text string
log_archive_dest_14
archival destination #14 text string
log_archive_dest_15
archival destination #15 text string
log_archive_dest_16
archival destination #16 text string
log_archive_dest_17
archival destination #17 text string
log_archive_dest_18
archival destination #18 text string
log_archive_dest_19
archival destination #19 text string
log_archive_dest_20
archival destination #20 text string
log_archive_dest_21
archival destination #21 text string
log_archive_dest_22
archival destination #22 text string
log_archive_dest_23
archival destination #23 text string
log_archive_dest_24
archival destination #24 text string
log_archive_dest_25
archival destination #25 text string
log_archive_dest_26
archival destination #26 text string
log_archive_dest_27
archival destination #27 text string
log_archive_dest_28
archival destination #28 text string
log_archive_dest_29
archival destination #29 text string
log_archive_dest_30
archival destination #30 text string
log_archive_dest_31
archival destination #31 text string
log_archive_dest_state_1
archival destination #1 state text string
log_archive_dest_state_2
archival destination #2 state text string
log_archive_dest_state_3
archival destination #3 state text string
log_archive_dest_state_4
archival destination #4 state text string
log_archive_dest_state_5
archival destination #5 state text string
log_archive_dest_state_6
archival destination #6 state text string
log_archive_dest_state_7
archival destination #7 state text string
log_archive_dest_state_8
archival destination #8 state text string
log_archive_dest_state_9
archival destination #9 state text string
log_archive_dest_state_10
archival destination #10 state text string
log_archive_dest_state_11
archival destination #11 state text string
log_archive_dest_state_12
archival destination #12 state text string
log_archive_dest_state_13
archival destination #13 state text string
log_archive_dest_state_14
archival destination #14 state text string
log_archive_dest_state_15
archival destination #15 state text string
log_archive_dest_state_16
archival destination #16 state text string
log_archive_dest_state_17
archival destination #17 state text string
log_archive_dest_state_18
archival destination #18 state text string
log_archive_dest_state_19
archival destination #19 state text string
log_archive_dest_state_20
archival destination #20 state text string
log_archive_dest_state_21
archival destination #21 state text string
log_archive_dest_state_22
archival destination #22 state text string
log_archive_dest_state_23
archival destination #23 state text string
log_archive_dest_state_24
archival destination #24 state text string
log_archive_dest_state_25
archival destination #25 state text string
log_archive_dest_state_26
archival destination #26 state text string
log_archive_dest_state_27
archival destination #27 state text string
log_archive_dest_state_28
archival destination #28 state text string
log_archive_dest_state_29
archival destination #29 state text string
log_archive_dest_state_30
archival destination #30 state text string
log_archive_dest_state_31
archival destination #31 state text string
log_archive_start
start archival process on SGA initialization
log_archive_dest
archival destination text string
log_archive_duplex_dest
duplex archival destination text string
log_archive_min_succeed_dest
minimum number of archive destinations that must succeed
standby_archive_dest
standby database archivelog destination text string
fal_client
FAL client
fal_server
FAL server list
log_archive_trace
Establish archivelog operation tracing level
log_archive_config
log archive config parameter
log_archive_local_first
Establish EXPEDITE attribute default value
log_archive_format
archival destination format
redo_transport_user
Data Guard transport user when using password file
log_archive_max_processes
maximum number of active ARCH processes
log_buffer
redo circular buffer size
log_checkpoint_interval
# redo blocks checkpoint threshold
log_checkpoint_timeout
Maximum time interval between checkpoints in seconds
archive_lag_target
Maximum number of seconds of redos the standby could lose
db_files
max allowable # db files
db_file_multiblock_read_count
db block to be read each IO
read_only_open_delayed
if TRUE delay opening of read only files until first access
cluster_database
if TRUE startup in cluster database mode
parallel_server
if TRUE startup in parallel server mode
parallel_server_instances
number of instances to use for sizing OPS SGA structures
cluster_database_instances
number of instances to use for sizing cluster db SGA structures
db_create_file_dest
default database location
db_create_online_log_dest_1
online log/controlfile destination #1
db_create_online_log_dest_2
online log/controlfile destination #2
db_create_online_log_dest_3
online log/controlfile destination #3
db_create_online_log_dest_4
online log/controlfile destination #4
db_create_online_log_dest_5
online log/controlfile destination #5
db_recovery_file_dest
default database recovery file location
db_recovery_file_dest_size
database recovery files size limit
standby_file_management
if auto then files are created/dropped automatically on standby
db_unrecoverable_scn_tracking
Track nologging SCN in controlfile
thread
Redo thread to mount
fast_start_io_target
Upper bound on recovery reads
fast_start_mttr_target
MTTR target in seconds
log_checkpoints_to_alert
log checkpoint begin/end to alert file
db_lost_write_protect
enable lost write detection
recovery_parallelism
number of server processes to use for parallel recovery
db_flashback_retention_target
Maximum Flashback Database log retention time in minutes.
dml_locks
dml locks - one for each table modified in a transaction
replication_dependency_tracking
tracking dependency for Replication parallel propagation
transactions
max. number of concurrent active transactions
transactions_per_rollback_segment
number of active transactions per rollback segment
rollback_segments
undo segment list
undo_management
instance runs in SMU mode if TRUE, else in RBU mode
undo_tablespace
use/switch undo tablespace
undo_retention
undo retention in seconds
fast_start_parallel_rollback
max number of parallel recovery slaves that may be used
resumable_timeout
set resumable_timeout
instance_number
instance number
db_block_checking
header checking and data and index block checking
recyclebin
recyclebin processing
db_securefile
permit securefile storage during lob creation
create_stored_outlines
create stored outlines for DML statements
serial_reuse
reuse the frame segments
ldap_directory_access
RDBMS's LDAP access option
ldap_directory_sysauth
OID usage parameter
os_roles
retrieve roles from the operating system
rdbms_server_dn
RDBMS's Distinguished Name
max_enabled_roles
max number of roles a user can have enabled
remote_os_authent
allow non-secure remote clients to use auto-logon accounts
remote_os_roles
allow non-secure remote clients to use os roles
sec_case_sensitive_logon
case sensitive password enabled for logon
O7_DICTIONARY_ACCESSIBILITY
Version 7 Dictionary Accessibility Support
remote_login_passwordfile
password file usage parameter
license_max_users
maximum number of named users that can be created in the database
audit_sys_operations
enable sys auditing
global_context_pool_size
Global Application Context Pool Size in Bytes
db_domain
directory part of global database name stored with CREATE DATABASE
global_names
enforce that database links have same name as remote database
distributed_lock_timeout
number of seconds a distributed transaction waits for a lock
commit_point_strength
Bias this node has toward not preparing in a two-phase commit
global_txn_processes
number of background global transaction processes to start
instance_name
instance name supported by the instance
service_names
service names supported by the instance
dispatchers
specifications of dispatchers
shared_servers
number of shared servers to start up
max_shared_servers
max number of shared servers
max_dispatchers
max number of dispatchers
circuits
max number of circuits
shared_server_sessions
max number of shared server sessions
local_listener
local listener
remote_listener
remote listener
listener_networks
listener registration networks
cursor_space_for_time
use more memory in order to get faster execution
session_cached_cursors
Number of cursors to cache in a session.
remote_dependencies_mode
remote-procedure-call dependencies mode parameter
utl_file_dir
utl_file accessible directories list
smtp_out_server
utl_smtp server and port configuration parameter
plsql_v2_compatibility
PL/SQL version 2.x compatibility flag
plsql_warnings
PL/SQL compiler warnings settings
plsql_code_type
PL/SQL code-type
plsql_debug
PL/SQL debug
plsql_optimize_level
PL/SQL optimize level
plsql_ccflags
PL/SQL ccflags
plscope_settings
plscope_settings controls the compile time collection, cross reference, and storage of PL/SQL source code identifier data
permit_92_wrap_format
allow 9.2 or older wrap format in PL/SQL
java_jit_enabled
Java VM JIT enabled
job_queue_processes
maximum number of job queue slave processes
parallel_min_percent
minimum percent of threads required for parallel query
create_bitmap_area_size
size of create bitmap buffer for bitmap index
bitmap_merge_area_size
maximum memory allow for BITMAP MERGE
cursor_sharing
cursor sharing mode
result_cache_mode
result cache operator usage mode
parallel_min_servers
minimum parallel query servers per instance
parallel_max_servers
maximum parallel query servers per instance
parallel_instance_group
instance group to use for all parallel operations
parallel_execution_message_size
message buffer size for parallel execution
hash_area_size
size of in-memory hash work area
result_cache_max_size
maximum amount of memory to be used by the cache
result_cache_max_result
maximum result size as percent of cache size
result_cache_remote_expiration
maximum life time (min) for any result using a remote object
audit_file_dest
Directory in which auditing files are to reside
shadow_core_dump
Core Size for Shadow Processes
background_core_dump
Core Size for Background Processes
background_dump_dest
Detached process dump directory
user_dump_dest
User process dump directory
core_dump_dest
Core dump directory
object_cache_optimal_size
optimal size of the user session's object cache in bytes
object_cache_max_size_percent
percentage of maximum size over optimal of the user session's object cache
session_max_open_files
maximum number of open files allowed per session
open_links
max # open links per session
open_links_per_instance
max # open links per instance
commit_write
transaction commit log write behaviour
commit_wait
transaction commit log wait behaviour
commit_logging
transaction commit log write behaviour
optimizer_features_enable
optimizer plan compatibility parameter
fixed_date
fixed SYSDATE value
audit_trail
enable system auditing
sort_area_size
size of in-memory sort work area
sort_area_retained_size
size of in-memory sort work area retained between fetch calls
cell_offload_processing
enable SQL processing offload to cells
cell_offload_decryption
enable SQL processing offload of encrypted data to cells
cell_offload_parameters
Additional cell offload parameters
cell_offload_compaction
Cell packet compaction strategy
cell_offload_plan_display
Cell offload explain plan display
db_name
database name specified in CREATE DATABASE
db_unique_name
Database Unique Name
open_cursors
max # cursors per session
ifile
include file in init.ora
sql_trace
enable SQL trace
os_authent_prefix
prefix for auto-logon accounts
optimizer_mode
optimizer mode
sql92_security
require select privilege for searched update/delete
blank_trimming
blank trimming semantics parameter
star_transformation_enabled
enable the use of star transformation
parallel_degree_policy
policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO)
parallel_adaptive_multi_user
enable adaptive setting of degree for multiple user streams
parallel_threads_per_cpu
number of parallel execution threads per CPU
parallel_automatic_tuning
enable intelligent defaults for parallel execution parameters
parallel_io_cap_enabled
enable capping DOP by IO bandwidth
optimizer_index_cost_adj
optimizer index cost adjustment
optimizer_index_caching
optimizer percent index caching
query_rewrite_enabled
allow rewrite of queries using materialized views if enabled
query_rewrite_integrity
perform rewrite using materialized views with desired integrity
pga_aggregate_target
Target size for the aggregate PGA memory consumed by the instance
workarea_size_policy
policy used to size SQL working areas (MANUAL/AUTO)
optimizer_dynamic_sampling
optimizer dynamic sampling
statistics_level
statistics level
cursor_bind_capture_destination
Allowed destination for captured bind variables
skip_unusable_indexes
skip unusable indexes if set to TRUE
optimizer_secure_view_merging
optimizer secure view merging and predicate pushdown/movearound
ddl_lock_timeout
timeout to restrict the time that ddls wait for dml lock
deferred_segment_creation
defer segment creation to first insert
optimizer_use_pending_statistics
Control whether to use optimizer pending statistics
optimizer_capture_sql_plan_baselines
automatic capture of SQL plan baselines for repeatable statements
optimizer_use_sql_plan_baselines
use of SQL plan baselines for captured sql statements
parallel_min_time_threshold
threshold above which a plan is a candidate for parallelization (in seconds)
parallel_degree_limit
limit placed on degree of parallelism
parallel_force_local
force single instance execution
optimizer_use_invisible_indexes
Usage of invisible indexes (TRUE/FALSE)
dst_upgrade_insert_conv
Enables/Disables internal conversions during DST upgrade
parallel_servers_target
instance target in terms of number of parallel servers
sec_protocol_error_trace_action
TTC protocol error action
sec_protocol_error_further_action
TTC protocol error continue action
sec_max_failed_login_attempts
maximum number of failed login attempts on a connection
sec_return_server_release_banner
whether the server retruns the complete version information
enable_ddl_logging
enable ddl logging
client_result_cache_size
client result cache max size in bytes
client_result_cache_lag
client result cache maximum lag in milliseconds
aq_tm_processes
number of AQ Time Managers to start
hs_autoregister
enable automatic server DD updates in HS agent self-registration
xml_db_events
are XML DB events enabled
dg_broker_start
start Data Guard broker (DMON process)
dg_broker_config_file1
data guard broker configuration file #1
dg_broker_config_file2
data guard broker configuration file #2
olap_page_pool_size
size of the olap page pool in bytes
asm_diskstring
disk set locations for discovery
asm_preferred_read_failure_groups
preferred read failure groups
asm_diskgroups
disk groups to mount automatically
asm_power_limit
number of parallel relocations for disk rebalancing
control_management_pack_access
declares which manageability packs are enabled
sqltune_category
Category qualifier for applying hintsets
diagnostic_dest
diagnostic base directory
tracefile_identifier
trace file custom identifier
max_dump_file_size
Maximum size (in bytes) of dump file
trace_enabled
enable in memory tracing
343 rows selected.