четверг, 23 июля 2009 г.

DBMS_SQLTUNE (настройка SQL)


Поиск наилучшего плана – SQL Tuning Advisor


Минимально необходимые привилегии
для непривилегированного пользователя:

CONNECT / AS SYSDBA
GRANT ADVISOR                 TO < USERNAME >;
GRANT SELECTCATALOG_ROLE      TO < USERNAME >;
GRANT EXECUTE on DBMS_SQLTUNE TO < USERNAME >;


Дополнительно

Можно запускать помощник с помощью встроенного скрипта:
$ORACLE_HOME/rdbms/admin/sqltrpt.sql

Основные шаги

Убедитесь, что оригинальный запрос находится в памяти (shared pool)
или AWR репозитории



Создать задание для SQL Tuning Advisor одним из 3х способов

/* Анализ исходного текста запроса */
DECLARE
my_task_name varchar2(30);
my_sqltext   clob;
BEGIN
my_sqltext := q'!!';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
          sql_text => my_sqltext,
         user_name => '< SCHEMA_NAME >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/


/* Анализ запроса по SQL_ID из Cursor Cache */
DECLARE
my_task_name varchar2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
            sql_id => '< SQL_ID >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/



/* Анализ запроса по SQL_ID из AWR репозитория */
DECLARE
my_task_name varchar2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
        begin_snap => < BEGIN_SNAP_ID >,
          end_snap => < END_SNAP_ID >,
            sql_id => '< SQL_ID >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/


Запустить задание на исполнение
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => '< TASK_NAME >');

Просмотр рекомендаций

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 1000
SET PAGESIZE 1000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('< TASK_NAME >') FROM DUAL;



Пример:

exec DBMS_SQLTUNE.DROP_TUNING_TASK('TSK_01');


DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := q'!SELECT * FROM DUAL!';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
                                               user_name => 'ANGORL',
                                                   scope => 'COMPREHENSIVE',
                                              time_limit => 600,
                                               task_name => 'TSK_01',
                                             description => 'test');
END;
/


exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TSK_01' );

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS
  WHERE task_id = (SELECT task_id FROM USER_ADVISOR_TASKS
                     WHERE task_name='TSK_01');

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 10000
SET PAGESIZE 10000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TSK_01') FROM DUAL;



В рекоммендациях может быть предложено принять profile:

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 92.44%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>'TSK_01', task_owner => 'ANGOR', replace => TRUE);

И выведены для сравнения два плана выполнения запроса:

Оригинального

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 5227986114


И с применённым profile

2- Using SQL Profile
--------------------
Plan hash value: 6245447849

 



1. Создайте задачу:


Задача создается для стейтманта из AWR:

DECLARE
  my_task_name  VARCHAR2(100);
BEGIN
  my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 523,
                          end_snap    => 658,
                          sql_id      => '7h35uxf5uhmm1',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '7h35uxf5uhmm1_AWR_tuning_task',
                          description => 'Tuning task for statement 7h35uxf5uhmm1 in AWR');
  DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/


Задача создается по sql_id курсора из кэша:

DECLARE
  my_task_name  VARCHAR2(100);
BEGIN
  my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '7h35uxf5uhmm1',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '7h35uxf5uhmm1_tuning_task',
                          description => 'Tuning task for statement 7h35uxf5uhmm1');
  DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/


Задача создается для операторов из STS:

DECLARE
  my_task_name  VARCHAR2(100);
BEGIN
  my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for an SQL tuning set.');
  DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/


Можно указать SQL-оператор вручную:

DECLARE
  my_sql_text   VARCHAR2(500);
  my_task_name  VARCHAR2(100);
BEGIN
  my_sql_text := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE  NVL(empno, ''0'') = :empno';


  my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => my_sql_text,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'ANGOR',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/

общее время на анализ ограничили до 60 секунд.


2. Выполните задачу:

BEGIN
     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name  => 'my_sql_tuning_task' );
END;
/


3. Получите отчет по настройке:

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SET PAGESIZE 24
select  DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) as recommendations from dual;


Мониторить выполнение задач по настройке SQL можно так:

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'ANGOR';


Приостановить и продолжить выполнение задачи можно так:

EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'my_sql_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'my_sql_tuning_task');


Завершить выполнение задачи можно так:

EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'my_sql_tuning_task');

                        
Сбросить задачу для ее повторного выполнения:

EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'my_sql_tuning_task');




Простой пример:


DECLARE
    my_task_name  VARCHAR2(30);
    my_sql_text  CLOB;
   
BEGIN
    my_sql_text := 'select sysdate
                             from dual';

   
    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                                 sql_text                   => my_sql_text,
--                                 bind_list                  => sql_binds(anydata.ConvertNumber(100)),
                                 user_name             => 'ANGOR',
                                 scope                    => 'COMPREHENSIVE',
                                 time_limit                => 60,
                                 task_name             => 'my_sql_tuning_task',
                                 description             => 'task to tune sql'
                                 );
END;
/



BEGIN
     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name  => 'my_sql_tuning_task' );
END;
/



select  DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) as recommendations from dual;


GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task
Tuning Task Owner  : ANGOR
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/08/2014 16:59:14
Completed at       : 06/08/2014 16:59:16

-------------------------------------------------------------------------------
Schema Name: ANGOR
SQL ID     : 7ahprz34d71wv
SQL Text   : select sysdate
                                          from dual

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

-------------------------------------------------------------------------------



Использование инструмента SQLT (Doc ID 215187.1)

Закрепление существующего плана


Основные шаги

Если исходный запрос находится в памяти (shared pool)
и оптимизированный запрос находится в памяти (shared pool)
Перейдите в директорию sqlt/utl где установлен инструмент SQLT
Запустите скрипт coe_xfr_sql_profile.sql с ролью SYSDBA и предоставьте исходные данные:
SQL_ID исходного запроса [SQL_ID]
PLAN_HASH_VALUE оптимизированного запроса [OPT_PLAN_HASH_VALUE]

SQL> connect / as sysdba
SQL> @coe_xfr_sql_profile.sql  SQL_ID  OPT_PLAN_HASH_VALUE

Будет создан скрипт coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql

Если запрос использует литералы то можно модифицировать параметр
force=>TRUE (в скрипте) для применения плана ко всем запросам с литералами

Запустить скрипт coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql
из под пользователя с ролью SYSDBA для окончательной фиксации плана

SQL> connect / as sysdba
SQL> @coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql

Проверить закрепление нового плана для исходного SQL_ID

select distinct p.name sql_profile_name, s.sql_id, p.category, p.status
from DBA_SQL_PROFILES p,
     DBA_HIST_SQLSTAT s
where p.name = s.sql_profile
  and s.sql_id in ('&SQL_ID');

Проверить использование нового SQL Profile для исходного SQL_ID:

col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
       SQL_ID,
       CHILD_NUMBER,
       PLAN_HASH_VALUE,
       SQL_PROFILE,
       SQL_PATCH,
       SQL_PLAN_BASELINE,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME
from GV$SQL where SQL_ID in ('&SQL_ID');


Пример:


Создаем SQL Profile

Связываем sql_id  с лучшим plan_hash_value

@coe_xfr_sql_profile.sql

SQL> @coe_xfr_sql_profile.sql try7b95kv87a3 4809245315

SQL> @coe_xfr_sql_profile_try7b95kv87a3_4809245315.sql


Смотрим план:

select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='&SQL_ID';

select * from table(dbms_xplan.display_cursor('try7b95kv87a3',null,'ADVANCED ALLSTATS LAST'));


Убеждаемся, что он использует новый PLAN_HASH_VALUE.


Если нет, то делаем flush the HASH VALUE:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'try7b95kv87a3';

ADDRESS          HASH_VALUE
---------------- ----------
00000000A5D6BEE7 8756491057

SQL> exec DBMS_SHARED_POOL.PURGE ('00000000A5D6BEE7, 8756491057', 'C');

PL/SQL procedure successfully completed.

SQL>


SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'try7b95kv87a3';


select name from dba_sql_profiles;








вторник, 21 июля 2009 г.

Параметры инициализации 12.2

Linux

SQL> SET PAGESIZE 10000
SET LINESIZE 10000
select name, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE, value
from v$parameter;
 

NAME                                         ISSES ISSYS_MOD ISINS VALUE
-------------------------------------------------------------------------------- ----- --------- ----- ---------------------------------------------------------------------------------------------------------------
DBFIPS_140                                     FALSE FALSE     FALSE FALSE
O7_DICTIONARY_ACCESSIBILITY                             FALSE FALSE     FALSE FALSE
active_instance_count                                 FALSE FALSE     FALSE
allow_global_dblinks                                 FALSE IMMEDIATE TRUE  FALSE
allow_group_access_to_sga                             FALSE FALSE     FALSE FALSE
approx_for_aggregation                                 TRUE  IMMEDIATE TRUE  FALSE
approx_for_count_distinct                             TRUE  IMMEDIATE TRUE  FALSE
approx_for_percentile                                 TRUE  IMMEDIATE TRUE  none
aq_tm_processes                                  FALSE IMMEDIATE TRUE  1
archive_lag_target                                 FALSE IMMEDIATE TRUE  0
asm_diskgroups                                     FALSE IMMEDIATE TRUE
asm_diskstring                                     TRUE  IMMEDIATE TRUE
asm_io_processes                                 FALSE IMMEDIATE TRUE  20
asm_power_limit                                  TRUE  IMMEDIATE TRUE  1
asm_preferred_read_failure_groups                         FALSE IMMEDIATE TRUE
audit_file_dest                                  FALSE DEFERRED  TRUE  /u01/app/oracle/admin/TESTDB_OMEGA/adump
audit_sys_operations                                 FALSE FALSE     FALSE TRUE
audit_syslog_level                                 FALSE FALSE     FALSE
audit_trail                                     FALSE FALSE     FALSE DB
autotask_max_active_pdbs                             FALSE IMMEDIATE TRUE  2
awr_pdb_autoflush_enabled                             FALSE IMMEDIATE TRUE  FALSE
awr_snapshot_time_offset                             FALSE IMMEDIATE TRUE  0
background_core_dump                                 FALSE IMMEDIATE TRUE  partial
background_dump_dest                                 FALSE IMMEDIATE TRUE  /u01/app/oracle/product/12.2.0.1/rdbms/log
backup_tape_io_slaves                                 FALSE DEFERRED  TRUE  FALSE
bitmap_merge_area_size                                 FALSE FALSE     FALSE 1048576
blank_trimming                                     FALSE FALSE     FALSE FALSE
buffer_pool_keep                                 FALSE FALSE     FALSE
buffer_pool_recycle                                 FALSE FALSE     FALSE
cdb_cluster                                     FALSE FALSE     FALSE FALSE
cdb_cluster_name                                 FALSE FALSE     FALSE TESTDB_OMEGA
cell_offload_compaction                              TRUE  IMMEDIATE TRUE  ADAPTIVE
cell_offload_decryption                              FALSE IMMEDIATE TRUE  TRUE
cell_offload_parameters                              TRUE  IMMEDIATE TRUE
cell_offload_plan_display                             TRUE  IMMEDIATE TRUE  AUTO
cell_offload_processing                              TRUE  IMMEDIATE TRUE  TRUE
cell_offloadgroup_name                                 TRUE  IMMEDIATE TRUE
circuits                                     FALSE IMMEDIATE TRUE
client_result_cache_lag                              FALSE FALSE     FALSE 3000
client_result_cache_size                             FALSE FALSE     FALSE 0
clonedb                                      FALSE FALSE     FALSE FALSE
clonedb_dir                                     FALSE FALSE     FALSE
cluster_database                                 FALSE FALSE     FALSE TRUE
cluster_database_instances                             FALSE FALSE     FALSE 1
cluster_interconnects                                 FALSE FALSE     FALSE
commit_logging                                     TRUE  IMMEDIATE TRUE
commit_point_strength                                 FALSE FALSE     FALSE 1
commit_wait                                     TRUE  IMMEDIATE TRUE
commit_write                                     TRUE  IMMEDIATE TRUE
common_user_prefix                                 FALSE FALSE     FALSE C##
compatible                                     FALSE FALSE     FALSE 12.2.0
connection_brokers                                 FALSE IMMEDIATE TRUE  ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))
containers_parallel_degree                             TRUE  IMMEDIATE TRUE  65535
control_file_record_keep_time                             FALSE IMMEDIATE TRUE  7
control_files                                     FALSE FALSE     FALSE +DATA/TESTDB_OMEGA/CONTROLFILE/current.298.972512951, +DATA/TESTDB_OMEGA/CONTROLFILE/current.297.972512953
control_management_pack_access                             FALSE IMMEDIATE TRUE  DIAGNOSTIC+TUNING
core_dump_dest                                     FALSE IMMEDIATE TRUE  /u01/app/oracle/diag/rdbms/testdb_omega/TESTDB1/cdump
cpu_count                                     FALSE IMMEDIATE TRUE  2
create_bitmap_area_size                              FALSE FALSE     FALSE 8388608
create_stored_outlines                                 TRUE  IMMEDIATE TRUE
cursor_bind_capture_destination                          TRUE  IMMEDIATE TRUE  memory+disk
cursor_invalidation                                 TRUE  IMMEDIATE TRUE  IMMEDIATE
cursor_sharing                                     TRUE  IMMEDIATE TRUE  EXACT
cursor_space_for_time                                 FALSE FALSE     FALSE FALSE
data_guard_sync_latency                              FALSE IMMEDIATE TRUE  0
data_transfer_cache_size                             FALSE IMMEDIATE TRUE  0
db_16k_cache_size                                 FALSE IMMEDIATE TRUE  0
db_2k_cache_size                                 FALSE IMMEDIATE TRUE  0
db_32k_cache_size                                 FALSE IMMEDIATE TRUE  0
db_4k_cache_size                                 FALSE IMMEDIATE TRUE  0
db_8k_cache_size                                 FALSE IMMEDIATE TRUE  0
db_big_table_cache_percent_target                         FALSE IMMEDIATE TRUE  0
db_block_buffers                                 FALSE FALSE     FALSE 0
db_block_checking                                 FALSE IMMEDIATE TRUE  FALSE
db_block_checksum                                 FALSE IMMEDIATE TRUE  TYPICAL
db_block_size                                     FALSE FALSE     FALSE 8192
db_cache_advice                                  FALSE IMMEDIATE TRUE  ON
db_cache_size                                     FALSE IMMEDIATE TRUE  0
db_create_file_dest                                 TRUE  IMMEDIATE TRUE  +DATA
db_create_online_log_dest_1                             TRUE  IMMEDIATE TRUE
db_create_online_log_dest_2                             TRUE  IMMEDIATE TRUE
db_create_online_log_dest_3                             TRUE  IMMEDIATE TRUE
db_create_online_log_dest_4                             TRUE  IMMEDIATE TRUE
db_create_online_log_dest_5                             TRUE  IMMEDIATE TRUE
db_domain                                     FALSE FALSE     FALSE
db_file_multiblock_read_count                             TRUE  IMMEDIATE TRUE  128
db_file_name_convert                                 TRUE  FALSE     FALSE
db_files                                     FALSE FALSE     FALSE 200
db_flash_cache_file                                 FALSE IMMEDIATE TRUE
db_flash_cache_size                                 FALSE IMMEDIATE TRUE  0
db_flashback_retention_target                             FALSE IMMEDIATE FALSE 1440
db_index_compression_inheritance                         TRUE  IMMEDIATE TRUE  NONE
db_keep_cache_size                                 FALSE IMMEDIATE TRUE  0
db_lost_write_protect                                 FALSE IMMEDIATE TRUE  NONE
db_name                                      FALSE FALSE     FALSE TESTDB
db_performance_profile                                 FALSE FALSE     FALSE
db_recovery_file_dest                                 FALSE IMMEDIATE FALSE +DATA
db_recovery_file_dest_size                             FALSE IMMEDIATE FALSE 4294967296
db_recycle_cache_size                                 FALSE IMMEDIATE TRUE  0
db_securefile                                     TRUE  IMMEDIATE TRUE  PREFERRED
db_ultra_safe                                     FALSE FALSE     FALSE OFF
db_unique_name                                     FALSE FALSE     FALSE TESTDB_OMEGA
db_unrecoverable_scn_tracking                             TRUE  IMMEDIATE TRUE  TRUE
db_writer_processes                                 FALSE FALSE     FALSE 1
dbwr_io_slaves                                     FALSE FALSE     FALSE 0
ddl_lock_timeout                                 TRUE  IMMEDIATE TRUE  0
default_sharing                                  TRUE  IMMEDIATE TRUE  metadata
deferred_segment_creation                             TRUE  IMMEDIATE TRUE  TRUE
dg_broker_config_file1                                 FALSE IMMEDIATE TRUE  /u01/app/oracle/product/12.2.0.1/dbs/dr1TESTDB_OMEGA.dat
dg_broker_config_file2                                 FALSE IMMEDIATE TRUE  /u01/app/oracle/product/12.2.0.1/dbs/dr2TESTDB_OMEGA.dat
dg_broker_start                                  FALSE IMMEDIATE FALSE FALSE
diagnostic_dest                                  FALSE IMMEDIATE TRUE  /u01/app/oracle
disable_pdb_feature                                 FALSE IMMEDIATE TRUE  0
disk_asynch_io                                     FALSE FALSE     FALSE TRUE
dispatchers                                     FALSE IMMEDIATE TRUE  (PROTOCOL=TCP) (SERVICE=TESTDBXDB)
distributed_lock_timeout                             FALSE FALSE     FALSE 60
dml_locks                                     FALSE FALSE     FALSE 2076
dnfs_batch_size                                  FALSE FALSE     FALSE 4096
dst_upgrade_insert_conv                              TRUE  IMMEDIATE TRUE  TRUE
enable_automatic_maintenance_pdb                         FALSE IMMEDIATE TRUE  TRUE
enable_ddl_logging                                 TRUE  IMMEDIATE TRUE  FALSE
enable_dnfs_dispatcher                                 FALSE FALSE     FALSE FALSE
enable_goldengate_replication                             FALSE IMMEDIATE FALSE FALSE
enable_pluggable_database                             FALSE FALSE     FALSE FALSE
enabled_PDBs_on_standby                              FALSE IMMEDIATE TRUE  *
encrypt_new_tablespaces                              FALSE IMMEDIATE TRUE  CLOUD_ONLY
event                                         FALSE FALSE     FALSE
exafusion_enabled                                 FALSE FALSE     FALSE 1
external_keystore_credential_location                         FALSE FALSE     FALSE
fal_client                                     FALSE IMMEDIATE TRUE
fal_server                                     FALSE IMMEDIATE TRUE
fast_start_io_target                                 FALSE IMMEDIATE TRUE  0
fast_start_mttr_target                                 FALSE IMMEDIATE TRUE  0
fast_start_parallel_rollback                             FALSE IMMEDIATE TRUE  LOW
file_mapping                                     FALSE IMMEDIATE TRUE  FALSE
fileio_network_adapters                              FALSE FALSE     FALSE
filesystemio_options                                 FALSE FALSE     FALSE none
fixed_date                                     FALSE IMMEDIATE TRUE
gcs_server_processes                                 FALSE FALSE     FALSE 1
global_names                                     TRUE  IMMEDIATE TRUE  FALSE
global_txn_processes                                 FALSE IMMEDIATE TRUE  1
hash_area_size                                     TRUE  FALSE     FALSE 131072
heat_map                                     TRUE  IMMEDIATE TRUE  OFF
hi_shared_memory_address                             FALSE FALSE     FALSE 0
hs_autoregister                                  FALSE IMMEDIATE TRUE  TRUE
ifile                                         FALSE FALSE     FALSE
inmemory_adg_enabled                                 FALSE IMMEDIATE TRUE  TRUE
inmemory_clause_default                              TRUE  IMMEDIATE TRUE
inmemory_expressions_usage                             FALSE IMMEDIATE TRUE  ENABLE
inmemory_force                                     FALSE IMMEDIATE TRUE  DEFAULT
inmemory_max_populate_servers                             FALSE IMMEDIATE TRUE  0
inmemory_query                                     TRUE  IMMEDIATE TRUE  ENABLE
inmemory_size                                     FALSE IMMEDIATE TRUE  0
inmemory_trickle_repopulate_servers_percent                     FALSE IMMEDIATE TRUE  1
inmemory_virtual_columns                             FALSE IMMEDIATE TRUE  MANUAL
instance_abort_delay_time                             FALSE IMMEDIATE TRUE  0
instance_groups                                  FALSE FALSE     FALSE
instance_mode                                     FALSE FALSE     FALSE READ-WRITE
instance_name                                     FALSE FALSE     FALSE TESTDB1
instance_number                                  FALSE FALSE     FALSE 1
instance_type                                     FALSE FALSE     FALSE RDBMS
instant_restore                                  FALSE FALSE     FALSE FALSE
java_jit_enabled                                 TRUE  IMMEDIATE TRUE  TRUE
java_max_sessionspace_size                             FALSE FALSE     FALSE 0
java_pool_size                                     FALSE IMMEDIATE TRUE  0
java_restrict                                     FALSE FALSE     FALSE none
java_soft_sessionspace_limit                             FALSE FALSE     FALSE 0
job_queue_processes                                 FALSE IMMEDIATE TRUE  4000
large_pool_size                                  FALSE IMMEDIATE TRUE  0
ldap_directory_access                                 FALSE IMMEDIATE TRUE  NONE
ldap_directory_sysauth                                 FALSE FALSE     FALSE no
license_max_sessions                                 FALSE IMMEDIATE TRUE  0
license_max_users                                 FALSE IMMEDIATE TRUE  0
license_sessions_warning                             FALSE IMMEDIATE TRUE  0
listener_networks                                 FALSE IMMEDIATE TRUE
local_listener                                     FALSE IMMEDIATE TRUE    (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.101)(PORT=1521))
lock_name_space                                  FALSE FALSE     FALSE
lock_sga                                     FALSE FALSE     FALSE FALSE
log_archive_config                                 FALSE IMMEDIATE TRUE
log_archive_dest                                 FALSE IMMEDIATE TRUE
log_archive_dest_1                                 TRUE  IMMEDIATE TRUE
log_archive_dest_10                                 TRUE  IMMEDIATE TRUE
log_archive_dest_11                                 TRUE  IMMEDIATE TRUE
log_archive_dest_12                                 TRUE  IMMEDIATE TRUE
log_archive_dest_13                                 TRUE  IMMEDIATE TRUE
log_archive_dest_14                                 TRUE  IMMEDIATE TRUE
log_archive_dest_15                                 TRUE  IMMEDIATE TRUE
log_archive_dest_16                                 TRUE  IMMEDIATE TRUE
log_archive_dest_17                                 TRUE  IMMEDIATE TRUE
log_archive_dest_18                                 TRUE  IMMEDIATE TRUE
log_archive_dest_19                                 TRUE  IMMEDIATE TRUE
log_archive_dest_2                                 TRUE  IMMEDIATE TRUE
log_archive_dest_20                                 TRUE  IMMEDIATE TRUE
log_archive_dest_21                                 TRUE  IMMEDIATE TRUE
log_archive_dest_22                                 TRUE  IMMEDIATE TRUE
log_archive_dest_23                                 TRUE  IMMEDIATE TRUE
log_archive_dest_24                                 TRUE  IMMEDIATE TRUE
log_archive_dest_25                                 TRUE  IMMEDIATE TRUE
log_archive_dest_26                                 TRUE  IMMEDIATE TRUE
log_archive_dest_27                                 TRUE  IMMEDIATE TRUE
log_archive_dest_28                                 TRUE  IMMEDIATE TRUE
log_archive_dest_29                                 TRUE  IMMEDIATE TRUE
log_archive_dest_3                                 TRUE  IMMEDIATE TRUE
log_archive_dest_30                                 TRUE  IMMEDIATE TRUE
log_archive_dest_31                                 TRUE  IMMEDIATE TRUE
log_archive_dest_4                                 TRUE  IMMEDIATE TRUE
log_archive_dest_5                                 TRUE  IMMEDIATE TRUE
log_archive_dest_6                                 TRUE  IMMEDIATE TRUE
log_archive_dest_7                                 TRUE  IMMEDIATE TRUE
log_archive_dest_8                                 TRUE  IMMEDIATE TRUE
log_archive_dest_9                                 TRUE  IMMEDIATE TRUE
log_archive_dest_state_1                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_10                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_11                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_12                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_13                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_14                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_15                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_16                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_17                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_18                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_19                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_2                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_20                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_21                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_22                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_23                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_24                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_25                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_26                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_27                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_28                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_29                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_3                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_30                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_31                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_4                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_5                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_6                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_7                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_8                             TRUE  IMMEDIATE TRUE  enable
log_archive_dest_state_9                             TRUE  IMMEDIATE TRUE  enable
log_archive_duplex_dest                              FALSE IMMEDIATE TRUE
log_archive_format                                 FALSE FALSE     FALSE %t_%s_%r.dbf
log_archive_max_processes                             FALSE IMMEDIATE TRUE  4
log_archive_min_succeed_dest                             TRUE  IMMEDIATE TRUE  1
log_archive_start                                 FALSE FALSE     FALSE FALSE
log_archive_trace                                 FALSE IMMEDIATE TRUE  0
log_buffer                                     FALSE FALSE     FALSE 7806976
log_checkpoint_interval                              FALSE IMMEDIATE TRUE  0
log_checkpoint_timeout                                 FALSE IMMEDIATE TRUE  1800
log_checkpoints_to_alert                             FALSE IMMEDIATE TRUE  FALSE
log_file_name_convert                                 FALSE FALSE     FALSE
long_module_action                                 FALSE IMMEDIATE TRUE  TRUE
max_datapump_jobs_per_pdb                             FALSE IMMEDIATE TRUE  100
max_dispatchers                                  FALSE IMMEDIATE TRUE
max_dump_file_size                                 TRUE  IMMEDIATE TRUE  unlimited
max_idle_time                                     FALSE IMMEDIATE TRUE  0
max_iops                                     FALSE IMMEDIATE TRUE  0
max_mbps                                     FALSE IMMEDIATE TRUE  0
max_pdbs                                     FALSE IMMEDIATE FALSE 4098
max_shared_servers                                 FALSE IMMEDIATE TRUE
max_string_size                                  FALSE IMMEDIATE FALSE STANDARD
memory_max_target                                 FALSE FALSE     FALSE 0
memory_target                                     FALSE IMMEDIATE TRUE  0
nls_calendar                                     TRUE  FALSE     FALSE
nls_comp                                     TRUE  FALSE     FALSE BINARY
nls_currency                                     TRUE  FALSE     FALSE
nls_date_format                                  TRUE  FALSE     FALSE
nls_date_language                                 TRUE  FALSE     FALSE
nls_dual_currency                                 TRUE  FALSE     FALSE
nls_iso_currency                                 TRUE  FALSE     FALSE
nls_language                                     TRUE  FALSE     FALSE AMERICAN
nls_length_semantics                                 TRUE  IMMEDIATE TRUE  BYTE
nls_nchar_conv_excp                                 TRUE  IMMEDIATE TRUE  FALSE
nls_numeric_characters                                 TRUE  FALSE     FALSE
nls_sort                                     TRUE  FALSE     FALSE
nls_territory                                     TRUE  FALSE     FALSE AMERICA
nls_time_format                                  TRUE  FALSE     FALSE
nls_time_tz_format                                 TRUE  FALSE     FALSE
nls_timestamp_format                                 TRUE  FALSE     FALSE
nls_timestamp_tz_format                              TRUE  FALSE     FALSE
noncdb_compatible                                 FALSE FALSE     FALSE FALSE
object_cache_max_size_percent                             TRUE  DEFERRED  TRUE  10
object_cache_optimal_size                             TRUE  DEFERRED  TRUE  10240000
ofs_threads                                     FALSE IMMEDIATE TRUE  4
olap_page_pool_size                                 TRUE  DEFERRED  TRUE  0
one_step_plugin_for_pdb_with_tde                         FALSE IMMEDIATE TRUE  FALSE
open_cursors                                     FALSE IMMEDIATE TRUE  300
open_links                                     FALSE FALSE     FALSE 4
open_links_per_instance                              FALSE FALSE     FALSE 4
optimizer_adaptive_plans                             TRUE  IMMEDIATE TRUE  TRUE
optimizer_adaptive_reporting_only                         TRUE  IMMEDIATE TRUE  FALSE
optimizer_adaptive_statistics                             TRUE  IMMEDIATE TRUE  FALSE
optimizer_capture_sql_plan_baselines                         TRUE  IMMEDIATE TRUE  FALSE
optimizer_dynamic_sampling                             TRUE  IMMEDIATE TRUE  2
optimizer_features_enable                             TRUE  IMMEDIATE TRUE  12.2.0.1
optimizer_index_caching                              TRUE  IMMEDIATE TRUE  0
optimizer_index_cost_adj                             TRUE  IMMEDIATE TRUE  100
optimizer_inmemory_aware                             TRUE  IMMEDIATE TRUE  TRUE
optimizer_mode                                     TRUE  IMMEDIATE TRUE  ALL_ROWS
optimizer_secure_view_merging                             FALSE IMMEDIATE TRUE  TRUE
optimizer_use_invisible_indexes                          TRUE  IMMEDIATE TRUE  FALSE
optimizer_use_pending_statistics                         TRUE  IMMEDIATE TRUE  FALSE
optimizer_use_sql_plan_baselines                         TRUE  IMMEDIATE TRUE  TRUE
os_authent_prefix                                 FALSE FALSE     FALSE ops$
os_roles                                     FALSE FALSE     FALSE FALSE
outbound_dblink_protocols                             FALSE IMMEDIATE TRUE  ALL
parallel_adaptive_multi_user                             FALSE IMMEDIATE TRUE  FALSE
parallel_degree_limit                                 TRUE  IMMEDIATE TRUE  CPU
parallel_degree_policy                                 TRUE  IMMEDIATE TRUE  MANUAL
parallel_execution_message_size                          FALSE FALSE     FALSE 16384
parallel_force_local                                 TRUE  IMMEDIATE TRUE  FALSE
parallel_instance_group                              TRUE  IMMEDIATE TRUE
parallel_max_servers                                 FALSE IMMEDIATE TRUE  80
parallel_min_percent                                 TRUE  FALSE     FALSE 0
parallel_min_servers                                 FALSE IMMEDIATE TRUE  8
parallel_min_time_threshold                             TRUE  IMMEDIATE TRUE  AUTO
parallel_servers_target                              FALSE IMMEDIATE TRUE  32
parallel_threads_per_cpu                             FALSE IMMEDIATE TRUE  2
pdb_file_name_convert                                 TRUE  IMMEDIATE TRUE
pdb_lockdown                                     TRUE  IMMEDIATE TRUE
pdb_os_credential                                 FALSE FALSE     FALSE
permit_92_wrap_format                                 FALSE FALSE     FALSE TRUE
pga_aggregate_limit                                 FALSE IMMEDIATE TRUE  2147483648
pga_aggregate_target                                 FALSE IMMEDIATE TRUE  598736896
plscope_settings                                 TRUE  IMMEDIATE TRUE  IDENTIFIERS:NONE
plsql_ccflags                                     TRUE  IMMEDIATE TRUE
plsql_code_type                                  TRUE  IMMEDIATE TRUE  INTERPRETED
plsql_debug                                     TRUE  IMMEDIATE TRUE  FALSE
plsql_optimize_level                                 TRUE  IMMEDIATE TRUE  2
plsql_v2_compatibility                                 TRUE  IMMEDIATE TRUE  FALSE
plsql_warnings                                     TRUE  IMMEDIATE TRUE  DISABLE:ALL
pre_page_sga                                     FALSE FALSE     FALSE TRUE
processes                                     FALSE FALSE     FALSE 300
processor_group_name                                 FALSE FALSE     FALSE
query_rewrite_enabled                                 TRUE  IMMEDIATE TRUE  TRUE
query_rewrite_integrity                              TRUE  IMMEDIATE TRUE  enforced
rdbms_server_dn                                  FALSE FALSE     FALSE
read_only_open_delayed                                 FALSE FALSE     FALSE FALSE
recovery_parallelism                                 FALSE FALSE     FALSE 0
recyclebin                                     TRUE  DEFERRED  TRUE  on
redo_transport_user                                 FALSE IMMEDIATE TRUE
remote_dependencies_mode                             TRUE  IMMEDIATE TRUE  TIMESTAMP
remote_listener                                  FALSE IMMEDIATE TRUE    scan12c:1521
remote_login_passwordfile                             FALSE FALSE     FALSE EXCLUSIVE
remote_os_authent                                 FALSE FALSE     FALSE FALSE
remote_os_roles                                  FALSE FALSE     FALSE FALSE
remote_recovery_file_dest                             FALSE IMMEDIATE FALSE
replication_dependency_tracking                          FALSE FALSE     FALSE TRUE
resource_limit                                     FALSE IMMEDIATE TRUE  TRUE
resource_manage_goldengate                             FALSE IMMEDIATE TRUE  FALSE
resource_manager_cpu_allocation                          FALSE IMMEDIATE TRUE  2
resource_manager_plan                                 FALSE IMMEDIATE TRUE  SCHEDULER[0x4AC5]:DEFAULT_MAINTENANCE_PLAN
result_cache_max_result                              FALSE IMMEDIATE TRUE  5
result_cache_max_size                                 FALSE IMMEDIATE TRUE  8978432
result_cache_mode                                 TRUE  IMMEDIATE TRUE  MANUAL
result_cache_remote_expiration                             TRUE  IMMEDIATE TRUE  0
resumable_timeout                                 TRUE  IMMEDIATE TRUE  0
rollback_segments                                 FALSE FALSE     FALSE
sec_case_sensitive_logon                             FALSE IMMEDIATE TRUE  TRUE
sec_max_failed_login_attempts                             FALSE FALSE     FALSE 3
sec_protocol_error_further_action                         FALSE IMMEDIATE TRUE  (DROP,3)
sec_protocol_error_trace_action                          FALSE IMMEDIATE TRUE  TRACE
sec_return_server_release_banner                         FALSE FALSE     FALSE FALSE
serial_reuse                                     FALSE FALSE     FALSE disable
service_names                                     FALSE IMMEDIATE TRUE  TESTDB_OMEGA
session_cached_cursors                                 TRUE  DEFERRED  TRUE  50
session_max_open_files                                 FALSE FALSE     FALSE 10
sessions                                     FALSE IMMEDIATE TRUE  472
sga_max_size                                     FALSE FALSE     FALSE 1795162112
sga_min_size                                     FALSE IMMEDIATE TRUE  0
sga_target                                     FALSE IMMEDIATE TRUE  1795162112
shadow_core_dump                                 FALSE IMMEDIATE TRUE  partial
shared_memory_address                                 FALSE FALSE     FALSE 0
shared_pool_reserved_size                             FALSE FALSE     FALSE 21810380
shared_pool_size                                 FALSE IMMEDIATE TRUE  0
shared_server_sessions                                 FALSE IMMEDIATE TRUE
shared_servers                                     FALSE IMMEDIATE TRUE  1
shrd_dupl_table_refresh_rate                             FALSE IMMEDIATE TRUE  60
skip_unusable_indexes                                 TRUE  IMMEDIATE TRUE  TRUE
smtp_out_server                                  TRUE  IMMEDIATE TRUE
sort_area_retained_size                              TRUE  DEFERRED  TRUE  0
sort_area_size                                     TRUE  DEFERRED  TRUE  65536
spatial_vector_acceleration                             TRUE  IMMEDIATE TRUE  FALSE
spfile                                         FALSE IMMEDIATE TRUE  +DATA/TESTDB_OMEGA/PARAMETERFILE/spfile.305.972520367
sql92_security                                     FALSE FALSE     FALSE TRUE
sql_trace                                     TRUE  IMMEDIATE TRUE  FALSE
sqltune_category                                 TRUE  IMMEDIATE TRUE  DEFAULT
standby_archive_dest                                 FALSE IMMEDIATE TRUE  ?#/dbs/arch
standby_db_preserve_states                             FALSE FALSE     FALSE NONE
standby_file_management                              FALSE IMMEDIATE TRUE  MANUAL
star_transformation_enabled                             TRUE  IMMEDIATE TRUE  FALSE
statistics_level                                 TRUE  IMMEDIATE TRUE  TYPICAL
streams_pool_size                                 FALSE IMMEDIATE TRUE  0
tape_asynch_io                                     FALSE FALSE     FALSE TRUE
target_pdbs                                     FALSE FALSE     FALSE 0
temp_undo_enabled                                 TRUE  IMMEDIATE TRUE  FALSE
thread                                         FALSE IMMEDIATE TRUE  1
threaded_execution                                 FALSE FALSE     FALSE FALSE
timed_os_statistics                                 TRUE  IMMEDIATE TRUE  0
timed_statistics                                 TRUE  IMMEDIATE TRUE  TRUE
trace_enabled                                     FALSE IMMEDIATE TRUE  TRUE
tracefile_identifier                                 TRUE  FALSE     FALSE
transactions                                     FALSE FALSE     FALSE 519
transactions_per_rollback_segment                         FALSE FALSE     FALSE 5
undo_management                                  FALSE FALSE     FALSE AUTO
undo_retention                                     FALSE IMMEDIATE TRUE  900
undo_tablespace                                  FALSE IMMEDIATE TRUE  UNDOTBS1
unified_audit_sga_queue_size                             FALSE FALSE     FALSE 1048576
uniform_log_timestamp_format                             FALSE IMMEDIATE TRUE  TRUE
use_dedicated_broker                                 FALSE IMMEDIATE TRUE  FALSE
use_large_pages                                  FALSE FALSE     FALSE TRUE
user_dump_dest                                     FALSE IMMEDIATE TRUE  /u01/app/oracle/product/12.2.0.1/rdbms/log
utl_file_dir                                     FALSE FALSE     FALSE
workarea_size_policy                                 TRUE  IMMEDIATE TRUE  AUTO
xml_db_events                                     TRUE  IMMEDIATE TRUE  enable

417 rows selected.

SQL>




В кластеризованной среде , в которой применяется Oracle RAC , все экземпляры совместно используют тот же самый файл SPFILE

Пример такого файла для кластера:

TESTDB2.__data_transfer_cache_size=0
TESTDB1.__data_transfer_cache_size=0
TESTDB2.__db_cache_size=1191182336
TESTDB1.__db_cache_size=1207959552
TESTDB2.__inmemory_ext_roarea=0
TESTDB1.__inmemory_ext_roarea=0
TESTDB2.__inmemory_ext_rwarea=0
TESTDB1.__inmemory_ext_rwarea=0
TESTDB2.__java_pool_size=16777216
TESTDB1.__java_pool_size=16777216
TESTDB2.__large_pool_size=33554432
TESTDB1.__large_pool_size=33554432
TESTDB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTDB2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTDB2.__pga_aggregate_target=603979776
TESTDB1.__pga_aggregate_target=603979776
TESTDB2.__sga_target=1795162112
TESTDB1.__sga_target=1795162112
TESTDB2.__shared_io_pool_size=83886080
TESTDB1.__shared_io_pool_size=83886080
TESTDB2.__shared_pool_size=452984832
TESTDB1.__shared_pool_size=436207616
TESTDB2.__streams_pool_size=0
TESTDB1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/TESTDB_OMEGA/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.2.0'
*.control_files='+DATA/TESTDB_OMEGA/CONTROLFILE/current.298.972512951','+DATA/TESTDB_OMEGA/CONTROLFILE/current.297.972512953'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='TESTDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4294967296
*.db_unique_name='TESTDB_OMEGA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'
family:dw_helper.instance_mode='read-only'
TESTDB1.instance_number=1
TESTDB2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=571m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=1712m
TESTDB2.thread=2
TESTDB1.thread=1
*.undo_tablespace='UNDOTBS1'
TESTDB1.undo_tablespace='UNDOTBS1'
TESTDB2.undo_tablespace='UNDOTBS2'

Имена параметров, общих для всех экземпляров в кластере, будут начинаться с символов *.
Имена параметров, характерных для отдельного экземпляра, вроде INSTANCE_NUMBER и THREAD, должны быть предварены именем экземпляра
(идентификатором Oracle SID).


Специальные параметры кластера:

instance_number
instance_group
thread
cluster_database
cluster_database_instances
cluster_interconnects
remote_listener
local_listener
parallel_instance
max_commit_propagation_delay




Параметры имеющие одинаковое значение для всех инстансов:

active_instance_count
archive_lag_target
compatible
cluster_database
cluster_database_instances
cluster_interconnects     
control_files
db_block_size
db_domain
db_files
db_name
db_recovery_file_dest
db_recovery_file_dest_size
db_unique_name
dml_locks                     -- when 0
instance_type                -- rdbms or asm
max_commit_propagation_delay
parallel_max_servers
remote_login_password_file
trace_enabled
undo_management


Параметры модифицируемые на уровне базы данных:

-- string
select 'alter system set '|| name ||' = '''||value||''' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (2,4) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '''||value||''' scope=spfile sid=''*'''||';' sql_cmd  from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (2,4) and value is not null and substr(name,1,1)='_'
union all
-- integer
select 'alter system set '|| name ||' = '||value||' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (3,6) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '||value||' scope=spfile sid=''*'''||';' sql_cmd  from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (3,6) and value is not null and substr(name,1,1)='_'
union all
-- boolean
select 'alter system set '|| name ||' = '||value||' scope=spfile sid=''*'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (1) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '||value||' scope=spfile sid=''*'''||';' sql_cmd  from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'FALSE' and type in (1) and value is not null and substr(name,1,1)='_'
order by 1;

alter system set DBFIPS_140 = FALSE scope=spfile sid='*';
alter system set O7_DICTIONARY_ACCESSIBILITY = FALSE scope=spfile sid='*';
alter system set allow_group_access_to_sga = FALSE scope=spfile sid='*';
alter system set audit_sys_operations = TRUE scope=spfile sid='*';
alter system set audit_trail = 'DB' scope=spfile sid='*';
alter system set bitmap_merge_area_size = 1048576 scope=spfile sid='*';
alter system set blank_trimming = FALSE scope=spfile sid='*';
alter system set cdb_cluster = FALSE scope=spfile sid='*';
alter system set cdb_cluster_name = 'TESTDB_OMEGA' scope=spfile sid='*';
alter system set client_result_cache_lag = 3000 scope=spfile sid='*';
alter system set client_result_cache_size = 0 scope=spfile sid='*';
alter system set clonedb = FALSE scope=spfile sid='*';
alter system set cluster_database = TRUE scope=spfile sid='*';
alter system set cluster_database_instances = 1 scope=spfile sid='*';
alter system set commit_point_strength = 1 scope=spfile sid='*';
alter system set common_user_prefix = 'C##' scope=spfile sid='*';
alter system set compatible = '12.2.0' scope=spfile sid='*';
alter system set control_files = '+DATA/TESTDB_OMEGA/CONTROLFILE/current.298.972512951, +DATA/TESTDB_OMEGA/CONTROLFILE/current.297.972512953' scope=spfile sid='*';
alter system set create_bitmap_area_size = 8388608 scope=spfile sid='*';
alter system set cursor_space_for_time = FALSE scope=spfile sid='*';
alter system set db_block_buffers = 0 scope=spfile sid='*';
alter system set db_block_size = 8192 scope=spfile sid='*';
alter system set db_files = 200 scope=spfile sid='*';
alter system set db_flashback_retention_target = 1440 scope=spfile sid='*';
alter system set db_name = 'TESTDB' scope=spfile sid='*';
alter system set db_recovery_file_dest = '+DATA' scope=spfile sid='*';
alter system set db_recovery_file_dest_size = 4294967296 scope=spfile sid='*';
alter system set db_ultra_safe = 'OFF' scope=spfile sid='*';
alter system set db_unique_name = 'TESTDB_OMEGA' scope=spfile sid='*';
alter system set db_writer_processes = 1 scope=spfile sid='*';
alter system set dbwr_io_slaves = 0 scope=spfile sid='*';
alter system set dg_broker_start = FALSE scope=spfile sid='*';
alter system set disk_asynch_io = TRUE scope=spfile sid='*';
alter system set distributed_lock_timeout = 60 scope=spfile sid='*';
alter system set dml_locks = 2076 scope=spfile sid='*';
alter system set dnfs_batch_size = 4096 scope=spfile sid='*';
alter system set enable_dnfs_dispatcher = FALSE scope=spfile sid='*';
alter system set enable_goldengate_replication = FALSE scope=spfile sid='*';
alter system set enable_pluggable_database = FALSE scope=spfile sid='*';
alter system set exafusion_enabled = 1 scope=spfile sid='*';
alter system set filesystemio_options = 'none' scope=spfile sid='*';
alter system set gcs_server_processes = 1 scope=spfile sid='*';
alter system set hash_area_size = 131072 scope=spfile sid='*';
alter system set hi_shared_memory_address = 0 scope=spfile sid='*';
alter system set instance_mode = 'READ-WRITE' scope=spfile sid='*';
alter system set instance_name = 'TESTDB1' scope=spfile sid='*';
alter system set instance_number = 1 scope=spfile sid='*';
alter system set instance_type = 'RDBMS' scope=spfile sid='*';
alter system set instant_restore = FALSE scope=spfile sid='*';
alter system set java_max_sessionspace_size = 0 scope=spfile sid='*';
alter system set java_restrict = 'none' scope=spfile sid='*';
alter system set java_soft_sessionspace_limit = 0 scope=spfile sid='*';
alter system set ldap_directory_sysauth = 'no' scope=spfile sid='*';
alter system set lock_sga = FALSE scope=spfile sid='*';
alter system set log_archive_format = '%t_%s_%r.dbf' scope=spfile sid='*';
alter system set log_archive_start = FALSE scope=spfile sid='*';
alter system set log_buffer = 7806976 scope=spfile sid='*';
alter system set max_pdbs = 4098 scope=spfile sid='*';
alter system set max_string_size = 'STANDARD' scope=spfile sid='*';
alter system set memory_max_target = 0 scope=spfile sid='*';
alter system set nls_comp = 'BINARY' scope=spfile sid='*';
alter system set nls_language = 'AMERICAN' scope=spfile sid='*';
alter system set nls_territory = 'AMERICA' scope=spfile sid='*';
alter system set noncdb_compatible = FALSE scope=spfile sid='*';
alter system set open_links = 4 scope=spfile sid='*';
alter system set open_links_per_instance = 4 scope=spfile sid='*';
alter system set os_authent_prefix = 'ops$' scope=spfile sid='*';
alter system set os_roles = FALSE scope=spfile sid='*';
alter system set parallel_execution_message_size = 16384 scope=spfile sid='*';
alter system set parallel_min_percent = 0 scope=spfile sid='*';
alter system set permit_92_wrap_format = TRUE scope=spfile sid='*';
alter system set pre_page_sga = TRUE scope=spfile sid='*';
alter system set processes = 300 scope=spfile sid='*';
alter system set read_only_open_delayed = FALSE scope=spfile sid='*';
alter system set recovery_parallelism = 0 scope=spfile sid='*';
alter system set remote_login_passwordfile = 'EXCLUSIVE' scope=spfile sid='*';
alter system set remote_os_authent = FALSE scope=spfile sid='*';
alter system set remote_os_roles = FALSE scope=spfile sid='*';
alter system set replication_dependency_tracking = TRUE scope=spfile sid='*';
alter system set sec_max_failed_login_attempts = 3 scope=spfile sid='*';
alter system set sec_return_server_release_banner = FALSE scope=spfile sid='*';
alter system set serial_reuse = 'disable' scope=spfile sid='*';
alter system set session_max_open_files = 10 scope=spfile sid='*';
alter system set sga_max_size = 1795162112 scope=spfile sid='*';
alter system set shared_memory_address = 0 scope=spfile sid='*';
alter system set shared_pool_reserved_size = 21810380 scope=spfile sid='*';
alter system set sql92_security = TRUE scope=spfile sid='*';
alter system set standby_db_preserve_states = 'NONE' scope=spfile sid='*';
alter system set tape_asynch_io = TRUE scope=spfile sid='*';
alter system set target_pdbs = 0 scope=spfile sid='*';
alter system set threaded_execution = FALSE scope=spfile sid='*';
alter system set transactions = 519 scope=spfile sid='*';
alter system set transactions_per_rollback_segment = 5 scope=spfile sid='*';
alter system set undo_management = 'AUTO' scope=spfile sid='*';
alter system set unified_audit_sga_queue_size = 1048576 scope=spfile sid='*';
alter system set use_large_pages = 'TRUE' scope=spfile sid='*';

96 rows selected.

SQL>



Параметры имеющие уникальные значения для всех инстансов:

instance_number
instance_name
thread
undo_tablespace/rollback_segments


Параметры модифицируемые на уровне экземпляра:

-- string
select 'alter system set '|| name ||' = '''||value||''' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (2,4) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '''||value||''' scope=spfile sid=''TESTDB1'''||';' sql_cmd  from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (2,4) and value is not null and substr(name,1,1)='_'
union all
-- integer
select 'alter system set '|| name ||' = '||value||' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (3,6) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '||value||' scope=spfile sid=''TESTDB1'''||';' sql_cmd  from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (3,6) and value is not null and substr(name,1,1)='_'
union all
-- boolean
select 'alter system set '|| name ||' = '||value||' scope=spfile sid=''TESTDB1'''||';' sql_cmd from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (1) and value is not null and substr(name,1,1)<>'_'
union all
select 'alter system set "'|| name ||'" = '||value||' scope=spfile sid=''TESTDB1'''||';' sql_cmd  from SYS.V_$PARAMETER where ISINSTANCE_MODIFIABLE = 'TRUE' and type in (1) and value is not null and substr(name,1,1)='_'
order by 1;

alter system set allow_global_dblinks = FALSE scope=spfile sid='TESTDB1';
alter system set approx_for_aggregation = FALSE scope=spfile sid='TESTDB1';
alter system set approx_for_count_distinct = FALSE scope=spfile sid='TESTDB1';
alter system set approx_for_percentile = 'none' scope=spfile sid='TESTDB1';
alter system set aq_tm_processes = 1 scope=spfile sid='TESTDB1';
alter system set archive_lag_target = 0 scope=spfile sid='TESTDB1';
alter system set asm_io_processes = 20 scope=spfile sid='TESTDB1';
alter system set asm_power_limit = 1 scope=spfile sid='TESTDB1';
alter system set audit_file_dest = '/u01/app/oracle/admin/TESTDB_OMEGA/adump' scope=spfile sid='TESTDB1';
alter system set autotask_max_active_pdbs = 2 scope=spfile sid='TESTDB1';
alter system set awr_pdb_autoflush_enabled = FALSE scope=spfile sid='TESTDB1';
alter system set awr_snapshot_time_offset = 0 scope=spfile sid='TESTDB1';
alter system set background_core_dump = 'partial' scope=spfile sid='TESTDB1';
alter system set background_dump_dest = '/u01/app/oracle/product/12.2.0.1/rdbms/log' scope=spfile sid='TESTDB1';
alter system set backup_tape_io_slaves = FALSE scope=spfile sid='TESTDB1';
alter system set cell_offload_compaction = 'ADAPTIVE' scope=spfile sid='TESTDB1';
alter system set cell_offload_decryption = TRUE scope=spfile sid='TESTDB1';
alter system set cell_offload_plan_display = 'AUTO' scope=spfile sid='TESTDB1';
alter system set cell_offload_processing = TRUE scope=spfile sid='TESTDB1';
alter system set connection_brokers = '((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))' scope=spfile sid='TESTDB1';
alter system set containers_parallel_degree = 65535 scope=spfile sid='TESTDB1';
alter system set control_file_record_keep_time = 7 scope=spfile sid='TESTDB1';
alter system set control_management_pack_access = 'DIAGNOSTIC+TUNING' scope=spfile sid='TESTDB1';
alter system set core_dump_dest = '/u01/app/oracle/diag/rdbms/testdb_omega/TESTDB1/cdump' scope=spfile sid='TESTDB1';
alter system set cpu_count = 2 scope=spfile sid='TESTDB1';
alter system set cursor_bind_capture_destination = 'memory+disk' scope=spfile sid='TESTDB1';
alter system set cursor_invalidation = 'IMMEDIATE' scope=spfile sid='TESTDB1';
alter system set cursor_sharing = 'EXACT' scope=spfile sid='TESTDB1';
alter system set data_guard_sync_latency = 0 scope=spfile sid='TESTDB1';
alter system set data_transfer_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_16k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_2k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_32k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_4k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_8k_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_big_table_cache_percent_target = '0' scope=spfile sid='TESTDB1';
alter system set db_block_checking = 'FALSE' scope=spfile sid='TESTDB1';
alter system set db_block_checksum = 'TYPICAL' scope=spfile sid='TESTDB1';
alter system set db_cache_advice = 'ON' scope=spfile sid='TESTDB1';
alter system set db_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_create_file_dest = '+DATA' scope=spfile sid='TESTDB1';
alter system set db_file_multiblock_read_count = 128 scope=spfile sid='TESTDB1';
alter system set db_flash_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_index_compression_inheritance = 'NONE' scope=spfile sid='TESTDB1';
alter system set db_keep_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_lost_write_protect = 'NONE' scope=spfile sid='TESTDB1';
alter system set db_recycle_cache_size = 0 scope=spfile sid='TESTDB1';
alter system set db_securefile = 'PREFERRED' scope=spfile sid='TESTDB1';
alter system set db_unrecoverable_scn_tracking = TRUE scope=spfile sid='TESTDB1';
alter system set ddl_lock_timeout = 0 scope=spfile sid='TESTDB1';
alter system set default_sharing = 'metadata' scope=spfile sid='TESTDB1';
alter system set deferred_segment_creation = TRUE scope=spfile sid='TESTDB1';
alter system set dg_broker_config_file1 = '/u01/app/oracle/product/12.2.0.1/dbs/dr1TESTDB_OMEGA.dat' scope=spfile sid='TESTDB1';
alter system set dg_broker_config_file2 = '/u01/app/oracle/product/12.2.0.1/dbs/dr2TESTDB_OMEGA.dat' scope=spfile sid='TESTDB1';
alter system set diagnostic_dest = '/u01/app/oracle' scope=spfile sid='TESTDB1';
alter system set disable_pdb_feature = 0 scope=spfile sid='TESTDB1';
alter system set dispatchers = '(PROTOCOL=TCP) (SERVICE=TESTDBXDB)' scope=spfile sid='TESTDB1';
alter system set dst_upgrade_insert_conv = TRUE scope=spfile sid='TESTDB1';
alter system set enable_automatic_maintenance_pdb = TRUE scope=spfile sid='TESTDB1';
alter system set enable_ddl_logging = FALSE scope=spfile sid='TESTDB1';
alter system set enabled_PDBs_on_standby = '*' scope=spfile sid='TESTDB1';
alter system set encrypt_new_tablespaces = 'CLOUD_ONLY' scope=spfile sid='TESTDB1';
alter system set fast_start_io_target = 0 scope=spfile sid='TESTDB1';
alter system set fast_start_mttr_target = 0 scope=spfile sid='TESTDB1';
alter system set fast_start_parallel_rollback = 'LOW' scope=spfile sid='TESTDB1';
alter system set file_mapping = FALSE scope=spfile sid='TESTDB1';
alter system set global_names = FALSE scope=spfile sid='TESTDB1';
alter system set global_txn_processes = 1 scope=spfile sid='TESTDB1';
alter system set heat_map = 'OFF' scope=spfile sid='TESTDB1';
alter system set hs_autoregister = TRUE scope=spfile sid='TESTDB1';
alter system set inmemory_adg_enabled = TRUE scope=spfile sid='TESTDB1';
alter system set inmemory_expressions_usage = 'ENABLE' scope=spfile sid='TESTDB1';
alter system set inmemory_force = 'DEFAULT' scope=spfile sid='TESTDB1';
alter system set inmemory_max_populate_servers = 0 scope=spfile sid='TESTDB1';
alter system set inmemory_query = 'ENABLE' scope=spfile sid='TESTDB1';
alter system set inmemory_size = 0 scope=spfile sid='TESTDB1';
alter system set inmemory_trickle_repopulate_servers_percent = 1 scope=spfile sid='TESTDB1';
alter system set inmemory_virtual_columns = 'MANUAL' scope=spfile sid='TESTDB1';
alter system set instance_abort_delay_time = 0 scope=spfile sid='TESTDB1';
alter system set java_jit_enabled = TRUE scope=spfile sid='TESTDB1';
alter system set java_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set job_queue_processes = 4000 scope=spfile sid='TESTDB1';
alter system set large_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set ldap_directory_access = 'NONE' scope=spfile sid='TESTDB1';
alter system set license_max_sessions = 0 scope=spfile sid='TESTDB1';
alter system set license_max_users = 0 scope=spfile sid='TESTDB1';
alter system set license_sessions_warning = 0 scope=spfile sid='TESTDB1';
alter system set local_listener = ' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.101)(PORT=1521))' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_1 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_10 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_11 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_12 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_13 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_14 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_15 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_16 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_17 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_18 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_19 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_2 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_20 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_21 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_22 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_23 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_24 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_25 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_26 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_27 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_28 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_29 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_3 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_30 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_31 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_4 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_5 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_6 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_7 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_8 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_dest_state_9 = 'enable' scope=spfile sid='TESTDB1';
alter system set log_archive_max_processes = 4 scope=spfile sid='TESTDB1';
alter system set log_archive_min_succeed_dest = 1 scope=spfile sid='TESTDB1';
alter system set log_archive_trace = 0 scope=spfile sid='TESTDB1';
alter system set log_checkpoint_interval = 0 scope=spfile sid='TESTDB1';
alter system set log_checkpoint_timeout = 1800 scope=spfile sid='TESTDB1';
alter system set log_checkpoints_to_alert = FALSE scope=spfile sid='TESTDB1';
alter system set long_module_action = TRUE scope=spfile sid='TESTDB1';
alter system set max_datapump_jobs_per_pdb = 100 scope=spfile sid='TESTDB1';
alter system set max_dump_file_size = 'unlimited' scope=spfile sid='TESTDB1';
alter system set max_idle_time = 0 scope=spfile sid='TESTDB1';
alter system set max_iops = 0 scope=spfile sid='TESTDB1';
alter system set max_mbps = 0 scope=spfile sid='TESTDB1';
alter system set memory_target = 0 scope=spfile sid='TESTDB1';
alter system set nls_length_semantics = 'BYTE' scope=spfile sid='TESTDB1';
alter system set nls_nchar_conv_excp = 'FALSE' scope=spfile sid='TESTDB1';
alter system set object_cache_max_size_percent = 10 scope=spfile sid='TESTDB1';
alter system set object_cache_optimal_size = 10240000 scope=spfile sid='TESTDB1';
alter system set ofs_threads = 4 scope=spfile sid='TESTDB1';
alter system set olap_page_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set one_step_plugin_for_pdb_with_tde = FALSE scope=spfile sid='TESTDB1';
alter system set open_cursors = 300 scope=spfile sid='TESTDB1';
alter system set optimizer_adaptive_plans = TRUE scope=spfile sid='TESTDB1';
alter system set optimizer_adaptive_reporting_only = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_adaptive_statistics = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_capture_sql_plan_baselines = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_dynamic_sampling = 2 scope=spfile sid='TESTDB1';
alter system set optimizer_features_enable = '12.2.0.1' scope=spfile sid='TESTDB1';
alter system set optimizer_index_caching = 0 scope=spfile sid='TESTDB1';
alter system set optimizer_index_cost_adj = 100 scope=spfile sid='TESTDB1';
alter system set optimizer_inmemory_aware = TRUE scope=spfile sid='TESTDB1';
alter system set optimizer_mode = 'ALL_ROWS' scope=spfile sid='TESTDB1';
alter system set optimizer_secure_view_merging = TRUE scope=spfile sid='TESTDB1';
alter system set optimizer_use_invisible_indexes = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_use_pending_statistics = FALSE scope=spfile sid='TESTDB1';
alter system set optimizer_use_sql_plan_baselines = TRUE scope=spfile sid='TESTDB1';
alter system set outbound_dblink_protocols = 'ALL' scope=spfile sid='TESTDB1';
alter system set parallel_adaptive_multi_user = FALSE scope=spfile sid='TESTDB1';
alter system set parallel_degree_limit = 'CPU' scope=spfile sid='TESTDB1';
alter system set parallel_degree_policy = 'MANUAL' scope=spfile sid='TESTDB1';
alter system set parallel_force_local = FALSE scope=spfile sid='TESTDB1';
alter system set parallel_max_servers = 80 scope=spfile sid='TESTDB1';
alter system set parallel_min_servers = 8 scope=spfile sid='TESTDB1';
alter system set parallel_min_time_threshold = 'AUTO' scope=spfile sid='TESTDB1';
alter system set parallel_servers_target = 32 scope=spfile sid='TESTDB1';
alter system set parallel_threads_per_cpu = 2 scope=spfile sid='TESTDB1';
alter system set pga_aggregate_limit = 2147483648 scope=spfile sid='TESTDB1';
alter system set pga_aggregate_target = 598736896 scope=spfile sid='TESTDB1';
alter system set plscope_settings = 'IDENTIFIERS:NONE' scope=spfile sid='TESTDB1';
alter system set plsql_code_type = 'INTERPRETED' scope=spfile sid='TESTDB1';
alter system set plsql_debug = FALSE scope=spfile sid='TESTDB1';
alter system set plsql_optimize_level = 2 scope=spfile sid='TESTDB1';
alter system set plsql_v2_compatibility = FALSE scope=spfile sid='TESTDB1';
alter system set plsql_warnings = 'DISABLE:ALL' scope=spfile sid='TESTDB1';
alter system set query_rewrite_enabled = 'TRUE' scope=spfile sid='TESTDB1';
alter system set query_rewrite_integrity = 'enforced' scope=spfile sid='TESTDB1';
alter system set recyclebin = 'on' scope=spfile sid='TESTDB1';
alter system set remote_dependencies_mode = 'TIMESTAMP' scope=spfile sid='TESTDB1';
alter system set remote_listener = ' scan12c:1521' scope=spfile sid='TESTDB1';
alter system set resource_limit = TRUE scope=spfile sid='TESTDB1';
alter system set resource_manage_goldengate = FALSE scope=spfile sid='TESTDB1';
alter system set resource_manager_cpu_allocation = 2 scope=spfile sid='TESTDB1';
alter system set resource_manager_plan = 'SCHEDULER[0x4AC5]:DEFAULT_MAINTENANCE_PLAN' scope=spfile sid='TESTDB1';
alter system set result_cache_max_result = 5 scope=spfile sid='TESTDB1';
alter system set result_cache_max_size = 8978432 scope=spfile sid='TESTDB1';
alter system set result_cache_mode = 'MANUAL' scope=spfile sid='TESTDB1';
alter system set result_cache_remote_expiration = 0 scope=spfile sid='TESTDB1';
alter system set resumable_timeout = 0 scope=spfile sid='TESTDB1';
alter system set sec_case_sensitive_logon = TRUE scope=spfile sid='TESTDB1';
alter system set sec_protocol_error_further_action = '(DROP,3)' scope=spfile sid='TESTDB1';
alter system set sec_protocol_error_trace_action = 'TRACE' scope=spfile sid='TESTDB1';
alter system set service_names = 'TESTDB_OMEGA' scope=spfile sid='TESTDB1';
alter system set session_cached_cursors = 50 scope=spfile sid='TESTDB1';
alter system set sessions = 472 scope=spfile sid='TESTDB1';
alter system set sga_min_size = 0 scope=spfile sid='TESTDB1';
alter system set sga_target = 1795162112 scope=spfile sid='TESTDB1';
alter system set shadow_core_dump = 'partial' scope=spfile sid='TESTDB1';
alter system set shared_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set shared_servers = 1 scope=spfile sid='TESTDB1';
alter system set shrd_dupl_table_refresh_rate = 60 scope=spfile sid='TESTDB1';
alter system set skip_unusable_indexes = TRUE scope=spfile sid='TESTDB1';
alter system set sort_area_retained_size = 0 scope=spfile sid='TESTDB1';
alter system set sort_area_size = 65536 scope=spfile sid='TESTDB1';
alter system set spatial_vector_acceleration = FALSE scope=spfile sid='TESTDB1';
alter system set spfile = '+DATA/TESTDB_OMEGA/PARAMETERFILE/spfile.305.972520367' scope=spfile sid='TESTDB1';
alter system set sql_trace = FALSE scope=spfile sid='TESTDB1';
alter system set sqltune_category = 'DEFAULT' scope=spfile sid='TESTDB1';
alter system set standby_archive_dest = '?#/dbs/arch' scope=spfile sid='TESTDB1';
alter system set standby_file_management = 'MANUAL' scope=spfile sid='TESTDB1';
alter system set star_transformation_enabled = 'FALSE' scope=spfile sid='TESTDB1';
alter system set statistics_level = 'TYPICAL' scope=spfile sid='TESTDB1';
alter system set streams_pool_size = 0 scope=spfile sid='TESTDB1';
alter system set temp_undo_enabled = FALSE scope=spfile sid='TESTDB1';
alter system set thread = 1 scope=spfile sid='TESTDB1';
alter system set timed_os_statistics = 0 scope=spfile sid='TESTDB1';
alter system set timed_statistics = TRUE scope=spfile sid='TESTDB1';
alter system set trace_enabled = TRUE scope=spfile sid='TESTDB1';
alter system set undo_retention = 900 scope=spfile sid='TESTDB1';
alter system set undo_tablespace = 'UNDOTBS1' scope=spfile sid='TESTDB1';
alter system set uniform_log_timestamp_format = TRUE scope=spfile sid='TESTDB1';
alter system set use_dedicated_broker = FALSE scope=spfile sid='TESTDB1';
alter system set user_dump_dest = '/u01/app/oracle/product/12.2.0.1/rdbms/log' scope=spfile sid='TESTDB1';
alter system set workarea_size_policy = 'AUTO' scope=spfile sid='TESTDB1';
alter system set xml_db_events = 'enable' scope=spfile sid='TESTDB1';


222 rows selected.

SQL>