Oracle: Verificando os parametros do Banco

From Wiki
Revision as of 14:06, 15 July 2008 by Ebasso (talk | contribs)

Para verificar os parâmetros faça o seguinte (no Oracle XE)

1) Clique em Início > Administração > Sobre o BD 
2) Selecione apenas Parâmetros e clique no botão Ir

Através de SQL

Para verificar os parâmetros faça o seguinte (no SQL Plus ou Comandos SQL do APEX)

SELECT name,value,isdefault,description FROM v$parameter
order by name;

A listagem com os parâmetros é mostrada, exemplo:

NAME                  VALUE    ISDEFAULT    DESCRIPTION

active_instance_count	 - 	TRUE	number of active instances in the cluster database
aq_tm_processes	0	TRUE	number of AQ Time Managers to start
archive_lag_target	0	TRUE	Maximum number of seconds of redos the standby could lose
asm_diskgroups	- 	TRUE	disk groups to mount automatically
asm_diskstring	- 	TRUE	disk set locations for discovery
asm_power_limit	1	TRUE	number of processes for disk rebalancing
audit_file_dest	/usr/lib/oracle/xe/app/oracle/admin/XE/adump	FALSE	Directory in which auditing files are to reside
audit_syslog_level	- 	TRUE	Syslog facility and level
audit_sys_operations	FALSE	TRUE	enable sys auditing
audit_trail	NONE	TRUE	enable system auditing
background_core_dump	partial	TRUE	Core Size for Background Processes
background_dump_dest	/usr/lib/oracle/xe/app/oracle/admin/XE/bdump	FALSE	Detached process dump directory
backup_tape_io_slaves	FALSE	TRUE	BACKUP Tape I/O slaves
bitmap_merge_area_size	1048576	TRUE	maximum memory allow for BITMAP MERGE
blank_trimming	FALSE	TRUE	blank trimming semantics parameter
buffer_pool_keep	- 	TRUE	Number of database blocks/latches in keep buffer pool
buffer_pool_recycle	- 	TRUE	Number of database blocks/latches in recycle buffer pool
circuits	- 	TRUE	max number of circuits
cluster_database	FALSE	TRUE	if TRUE startup in cluster database mode
cluster_database_instances	1	TRUE	number of instances to use for sizing cluster db SGA structures
cluster_interconnects	- 	TRUE	interconnects for RAC use
commit_point_strength	1	TRUE	Bias this node has toward not preparing in a two-phase commit
commit_write	- 	TRUE	transaction commit log write behaviour
compatible	10.2.0.1.0	FALSE	Database will be completely compatible with this software version
control_file_record_keep_time	7	TRUE	control file record keep time in days
control_files	/usr/lib/oracle/xe/oradata/XE/control.dbf	FALSE	control file names list
core_dump_dest	/usr/lib/oracle/xe/app/oracle/admin/XE/cdump	FALSE	Core dump directory
cpu_count	1	TRUE	number of CPUs for this instance
create_bitmap_area_size	8388608	TRUE	size of create bitmap buffer for bitmap index
create_stored_outlines	- 	TRUE	create stored outlines for DML statements
cursor_sharing	EXACT	TRUE	cursor sharing mode
cursor_space_for_time	FALSE	TRUE	use more memory in order to get faster execution
db_block_buffers	0	TRUE	Number of database blocks cached in memory
db_block_checking	FALSE	TRUE	header checking and data and index block checking
db_block_checksum	TRUE	TRUE	store checksum in db blocks and check during reads
db_block_size	8192	TRUE	Size of database block in bytes
db_cache_advice	ON	TRUE	Buffer cache sizing advisory
db_cache_size	0	TRUE	Size of DEFAULT buffer pool for standard block size buffers
db_create_file_dest	- 	TRUE	default database location
db_create_online_log_dest_1	- 	TRUE	online log/controlfile destination #1
db_create_online_log_dest_2	- 	TRUE	online log/controlfile destination #2
db_create_online_log_dest_3	- 	TRUE	online log/controlfile destination #3
db_create_online_log_dest_4	- 	TRUE	online log/controlfile destination #4
db_create_online_log_dest_5	- 	TRUE	online log/controlfile destination #5
db_domain	- 	TRUE	directory part of global database name stored with CREATE DATABASE
db_file_multiblock_read_count	128	TRUE	db block to be read each IO
db_file_name_convert	- 	TRUE	datafile name convert patterns and strings for standby/clone db
db_files	200	TRUE	max allowable # db files
db_flashback_retention_target	1440	TRUE	Maximum Flashback Database log retention time in minutes.
db_keep_cache_size	0	TRUE	Size of KEEP buffer pool for standard block size buffers
db_name	XE	FALSE	database name specified in CREATE DATABASE
db_recovery_file_dest	/usr/lib/oracle/xe/app/oracle/flash_recovery_area	FALSE	default database recovery file location
db_recovery_file_dest_size	10737418240	FALSE	database recovery files size limit
db_recycle_cache_size	0	TRUE	Size of RECYCLE buffer pool for standard block size buffers
db_unique_name	XE	TRUE	Database Unique Name
dbwr_io_slaves	0	TRUE	DBWR I/O slaves
db_writer_processes	1	TRUE	number of background database writer processes to start
db_16k_cache_size	0	TRUE	Size of cache for 16K buffers
db_2k_cache_size	0	TRUE	Size of cache for 2K buffers
db_32k_cache_size	0	TRUE	Size of cache for 32K buffers
db_4k_cache_size	0	TRUE	Size of cache for 4K buffers
db_8k_cache_size	0	TRUE	Size of cache for 8K buffers
ddl_wait_for_locks	FALSE	TRUE	Disable NOWAIT DML lock acquisitions
dg_broker_config_file1	/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/dr1XE.dat	TRUE	data guard broker configuration file #1
dg_broker_config_file2	/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/dr2XE.dat	TRUE	data guard broker configuration file #2
dg_broker_start	FALSE	TRUE	start Data Guard broker framework (DMON process)
disk_asynch_io	TRUE	TRUE	Use asynch I/O for random access devices
dispatchers	(PROTOCOL=TCP) (SERVICE=XEXDB)	FALSE	specifications of dispatchers
distributed_lock_timeout	60	TRUE	number of seconds a distributed transaction waits for a lock
dml_locks	212	TRUE	dml locks - one for each table modified in a transaction
drs_start	FALSE	TRUE	start DG Broker monitor (DMON process)
event	- 	TRUE	debug event control - default null string
fal_client	- 	TRUE	FAL client
fal_server	- 	TRUE	FAL server list
fast_start_io_target	0	TRUE	Upper bound on recovery reads
fast_start_mttr_target	0	TRUE	MTTR target of forward crash recovery in seconds
fast_start_parallel_rollback	LOW	TRUE	max number of parallel recovery slaves that may be used
fileio_network_adapters	- 	TRUE	Network Adapters for File I/O
file_mapping	FALSE	TRUE	enable file mapping
filesystemio_options	none	TRUE	IO operations on filesystem files
fixed_date	- 	TRUE	fixed SYSDATE value
gc_files_to_locks	- 	TRUE	mapping between file numbers and global cache locks
gcs_server_processes	0	TRUE	number of background gcs server processes to start
global_context_pool_size	- 	TRUE	Global Application Context Pool Size in Bytes
global_names	FALSE	TRUE	enforce that database links have same name as remote database
hash_area_size	131072	TRUE	size of in-memory hash work area
hi_shared_memory_address	0	TRUE	SGA starting address (high order 32-bits on 64-bit platforms)
hs_autoregister	TRUE	TRUE	enable automatic server DD updates in HS agent self-registration
ifile	- 	TRUE	include file in init.ora
instance_groups	- 	TRUE	list of instance group names
instance_name	XE	TRUE	instance name supported by the instance
instance_number	0	TRUE	instance number
instance_type	RDBMS	TRUE	type of instance to be executed
java_max_sessionspace_size	0	TRUE	max allowed size in bytes of a Java sessionspace
java_pool_size	0	TRUE	size in bytes of java pool
java_soft_sessionspace_limit	0	TRUE	warning limit on size in bytes of a Java sessionspace
job_queue_processes	4	FALSE	number of job queue slave processes
large_pool_size	0	TRUE	size in bytes of large pool
ldap_directory_access	NONE	TRUE	RDBMS's LDAP access option
license_max_sessions	0	TRUE	maximum number of non-system user sessions allowed
license_max_users	0	TRUE	maximum number of named users that can be created in the database
license_sessions_warning	0	TRUE	warning level for number of non-system user sessions
local_listener	- 	TRUE	local listener
lock_name_space	- 	TRUE	lock name space used for generating lock names for standby/clone database
lock_sga	FALSE	TRUE	Lock entire SGA in physical memory
log_archive_config	- 	TRUE	log archive config parameter
log_archive_dest	- 	TRUE	archival destination text string
log_archive_dest_state_1	enable	TRUE	archival destination #1 state text string
log_archive_dest_state_10	enable	TRUE	archival destination #10 state text string
log_archive_dest_state_2	enable	TRUE	archival destination #2 state text string
log_archive_dest_state_3	enable	TRUE	archival destination #3 state text string
log_archive_dest_state_4	enable	TRUE	archival destination #4 state text string
log_archive_dest_state_5	enable	TRUE	archival destination #5 state text string
log_archive_dest_state_6	enable	TRUE	archival destination #6 state text string
log_archive_dest_state_7	enable	TRUE	archival destination #7 state text string
log_archive_dest_state_8	enable	TRUE	archival destination #8 state text string
log_archive_dest_state_9	enable	TRUE	archival destination #9 state text string
log_archive_dest_1	- 	TRUE	archival destination #1 text string
log_archive_dest_10	- 	TRUE	archival destination #10 text string
log_archive_dest_2	- 	TRUE	archival destination #2 text string
log_archive_dest_3	- 	TRUE	archival destination #3 text string
log_archive_dest_4	- 	TRUE	archival destination #4 text string
log_archive_dest_5	- 	TRUE	archival destination #5 text string
log_archive_dest_6	- 	TRUE	archival destination #6 text string
log_archive_dest_7	- 	TRUE	archival destination #7 text string
log_archive_dest_8	- 	TRUE	archival destination #8 text string
log_archive_dest_9	- 	TRUE	archival destination #9 text string
log_archive_duplex_dest	- 	TRUE	duplex archival destination text string
log_archive_format	%t_%s_%r.dbf	TRUE	archival destination format
log_archive_local_first	TRUE	TRUE	Establish EXPEDITE attribute default value
log_archive_max_processes	2	TRUE	maximum number of active ARCH processes
log_archive_min_succeed_dest	1	TRUE	minimum number of archive destinations that must succeed
log_archive_start	FALSE	TRUE	start archival process on SGA initialization
log_archive_trace	0	TRUE	Establish archivelog operation tracing level
log_buffer	2887168	TRUE	redo circular buffer size
log_checkpoint_interval	0	TRUE	# redo blocks checkpoint threshold
log_checkpoints_to_alert	FALSE	TRUE	log checkpoint begin/end to alert file
log_checkpoint_timeout	1800	TRUE	Maximum time interval between checkpoints in seconds
log_file_name_convert	- 	TRUE	logfile name convert patterns and strings for standby/clone db
logmnr_max_persistent_sessions	1	TRUE	maximum number of threads to mine
max_commit_propagation_delay	0	TRUE	Max age of new snapshot in .01 seconds
max_dispatchers	- 	TRUE	max number of dispatchers
max_dump_file_size	UNLIMITED	TRUE	Maximum size (blocks) of dump file
max_enabled_roles	150	TRUE	max number of roles a user can have enabled
max_shared_servers	- 	TRUE	max number of shared servers
nls_calendar	- 	TRUE	NLS calendar system name
nls_comp	- 	TRUE	NLS comparison
nls_currency	- 	TRUE	NLS local currency symbol
nls_date_format	- 	TRUE	NLS Oracle date format
nls_date_language	- 	TRUE	NLS date language name
nls_dual_currency	- 	TRUE	Dual currency symbol
nls_iso_currency	- 	TRUE	NLS ISO currency territory name
nls_language	AMERICAN	TRUE	NLS language name
nls_length_semantics	BYTE	TRUE	create columns using byte or char semantics by default
nls_nchar_conv_excp	FALSE	TRUE	NLS raise an exception instead of allowing implicit conversion
nls_numeric_characters	- 	TRUE	NLS numeric characters
nls_sort	- 	TRUE	NLS linguistic definition name
nls_territory	AMERICA	TRUE	NLS territory name
nls_time_format	- 	TRUE	time format
nls_timestamp_format	- 	TRUE	time stamp format
nls_timestamp_tz_format	- 	TRUE	timestampe with timezone format
nls_time_tz_format	- 	TRUE	time with timezone format
object_cache_max_size_percent	10	TRUE	percentage of maximum size over optimal of the user session's object cache
object_cache_optimal_size	102400	TRUE	optimal size of the user session's object cache in bytes
olap_page_pool_size	0	TRUE	size of the olap page pool in bytes
open_cursors	300	FALSE	max # cursors per session
open_links	4	TRUE	max # open links per session
open_links_per_instance	4	TRUE	max # open links per instance
optimizer_dynamic_sampling	2	TRUE	optimizer dynamic sampling
optimizer_features_enable	10.2.0.1	TRUE	optimizer plan compatibility parameter
optimizer_index_caching	0	TRUE	optimizer percent index caching
optimizer_index_cost_adj	100	TRUE	optimizer index cost adjustment
optimizer_mode	ALL_ROWS	TRUE	optimizer mode
optimizer_secure_view_merging	TRUE	TRUE	optimizer secure view merging and predicate pushdown/movearound
os_authent_prefix	- 	FALSE	prefix for auto-logon accounts
os_roles	FALSE	TRUE	retrieve roles from the operating system
O7_DICTIONARY_ACCESSIBILITY	FALSE	TRUE	Version 7 Dictionary Accessibility Support
parallel_adaptive_multi_user	TRUE	TRUE	enable adaptive setting of degree for multiple user streams
parallel_automatic_tuning	FALSE	TRUE	enable intelligent defaults for parallel execution parameters
parallel_execution_message_size	2148	TRUE	message buffer size for parallel execution
parallel_instance_group	- 	TRUE	instance group to use for all parallel operations
parallel_max_servers	0	TRUE	maximum parallel query servers per instance
parallel_min_percent	0	TRUE	minimum percent of threads required for parallel query
parallel_min_servers	0	TRUE	minimum parallel query servers per instance
parallel_server	FALSE	TRUE	if TRUE startup in parallel server mode
parallel_server_instances	1	TRUE	number of instances to use for sizing OPS SGA structures
parallel_threads_per_cpu	2	TRUE	number of parallel execution threads per CPU
pga_aggregate_target	42205184	FALSE	Target size for the aggregate PGA memory consumed by the instance
plsql_ccflags	- 	TRUE	PL/SQL ccflags
plsql_code_type	INTERPRETED	TRUE	PL/SQL code-type
plsql_compiler_flags	INTERPRETED, NON_DEBUG	TRUE	PL/SQL compiler flags
plsql_debug	FALSE	TRUE	PL/SQL debug
plsql_native_library_dir	- 	TRUE	plsql native library dir
plsql_native_library_subdir_count	0	TRUE	plsql native library number of subdirectories
plsql_optimize_level	2	TRUE	PL/SQL optimize level
plsql_v2_compatibility	FALSE	TRUE	PL/SQL version 2.x compatibility flag
plsql_warnings	DISABLE:ALL	TRUE	PL/SQL compiler warnings settings
pre_page_sga	FALSE	TRUE	pre-page sga for process
processes	40	TRUE	user processes
query_rewrite_enabled	TRUE	TRUE	allow rewrite of queries using materialized views if enabled
query_rewrite_integrity	enforced	TRUE	perform rewrite using materialized views with desired integrity
rdbms_server_dn	- 	TRUE	RDBMS's Distinguished Name
read_only_open_delayed	FALSE	TRUE	if TRUE delay opening of read only files until first access
recovery_parallelism	0	TRUE	number of server processes to use for parallel recovery
recyclebin	on	TRUE	recyclebin processing
remote_archive_enable	true	TRUE	remote archival enable setting
remote_dependencies_mode	TIMESTAMP	TRUE	remote-procedure-call dependencies mode parameter
remote_listener	- 	TRUE	remote listener
remote_login_passwordfile	EXCLUSIVE	FALSE	password file usage parameter
remote_os_authent	FALSE	TRUE	allow non-secure remote clients to use auto-logon accounts
remote_os_roles	FALSE	TRUE	allow non-secure remote clients to use os roles
replication_dependency_tracking	TRUE	TRUE	tracking dependency for Replication parallel propagation
resource_limit	FALSE	TRUE	master switch for resource limit
resource_manager_plan	- 	TRUE	resource mgr top plan
resumable_timeout	0	TRUE	set resumable_timeout
rollback_segments	- 	TRUE	undo segment list
serial_reuse	disable	TRUE	reuse the frame segments
service_names	XE	TRUE	service names supported by the instance
session_cached_cursors	20	TRUE	Number of cursors to cache in a session.
session_max_open_files	10	TRUE	maximum number of open files allowed per session
sessions	49	FALSE	user and system sessions
sga_max_size	146800640	TRUE	max total SGA size
sga_target	146800640	FALSE	Target size of SGA
shadow_core_dump	partial	TRUE	Core Size for Shadow Processes
shared_memory_address	0	TRUE	SGA starting address (low order 32-bits on 64-bit platforms)
shared_pool_reserved_size	2936012	TRUE	size in bytes of reserved area of shared pool
shared_pool_size	0	TRUE	size in bytes of shared pool
shared_servers	4	FALSE	number of shared servers to start up
shared_server_sessions	- 	TRUE	max number of shared server sessions
skip_unusable_indexes	TRUE	TRUE	skip unusable indexes if set to TRUE
smtp_out_server	- 	TRUE	utl_smtp server and port configuration parameter
sort_area_retained_size	0	TRUE	size of in-memory sort work area retained between fetch calls
sort_area_size	65536	TRUE	size of in-memory sort work area
spfile	/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/spfileXE.ora	TRUE	server parameter file
sql_trace	FALSE	TRUE	enable SQL trace
sqltune_category	DEFAULT	TRUE	Category qualifier for applying hintsets
sql_version	NATIVE	TRUE	sql language version parameter for compatibility issues
sql92_security	FALSE	TRUE	require select privilege for searched update/delete
standby_archive_dest	?/dbs/arch	TRUE	standby database archivelog destination text string
standby_file_management	MANUAL	TRUE	if auto then files are created/dropped automatically on standby
star_transformation_enabled	FALSE	TRUE	enable the use of star transformation
statistics_level	TYPICAL	TRUE	statistics level
streams_pool_size	0	TRUE	size in bytes of the streams pool
tape_asynch_io	TRUE	TRUE	Use asynch I/O requests for tape devices
thread	0	TRUE	Redo thread to mount
timed_os_statistics	0	TRUE	internal os statistic gathering interval in seconds
timed_statistics	TRUE	TRUE	maintain internal timing statistics
trace_enabled	TRUE	TRUE	enable KST tracing
tracefile_identifier	- 	TRUE	trace file custom identifier
transactions	53	TRUE	max. number of concurrent active transactions
transactions_per_rollback_segment	5	TRUE	number of active transactions per rollback segment
undo_management	AUTO	FALSE	instance runs in SMU mode if TRUE, else in RBU mode
undo_retention	900	TRUE	undo retention in seconds
undo_tablespace	UNDO	FALSE	use/switch undo tablespace
use_indirect_data_buffers	FALSE	TRUE	Enable indirect data buffers (very large SGA on 32-bit platforms)
user_dump_dest	/usr/lib/oracle/xe/app/oracle/admin/XE/udump	FALSE	User process dump directory
utl_file_dir	- 	TRUE	utl_file accessible directories list
workarea_size_policy	AUTO	TRUE	policy used to size SQL working areas (MANUAL/AUTO)