Oracle: Verificando os parametros do Banco: Difference between revisions
mNo edit summary |
m (→Através de SQL) |
||
Line 7: | Line 7: | ||
== Através de SQL== | == Através de SQL== | ||
Para verificar os parâmetros faça o seguinte (no SQL Plus ou Comandos SQL do APEX) | Para verificar os parâmetros faça o seguinte (no SQL Plus ou Comandos SQL do APEX) | ||
SHOW PARAMETER | |||
ou | |||
SELECT name,value,isdefault,description FROM v$parameter | SELECT name,value,isdefault,description FROM v$parameter |
Revision as of 14:30, 15 July 2008
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)
SHOW PARAMETER
ou
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)