ALTER SYSTEM

/*
Существуют 3 способа изменения значений динамических параметров: 
ALTER SESSION
ALTER SYSTEM 
ALTER SYSTEM...DEFERRED (на все сеансы)
*/

--Следующие команды выполняют замораживание и размораживание базы данных:
ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;

--Для приостановки и возобновления базы данных:
ALTER SYSTEM SUSPEND;
ALTER SYSTEM RESUME;

--MEMORY_TARGET (Oracle 11g)
ALTER SYSTEM SET MEMORY_TARGET=3G SCOPE=BOTH;
ALTER SYSTEM SET MEMORY_MAX_TARGET = 3G SCOPE = SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
--SGA и PGA (Oracle 10g)
ALTER SYSTEM SET SGA_MAX_SIZE=2048M SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=1700M SCOPE=BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M SCOPE=BOTH;
--FRA
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE = BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'F:\FRA' SCOPE = BOTH;

ALTER SYSTEM SET PROCESSES=2000 SCOPE=BOTH;
ALTER SYSTEM SET PROCESSES=2000 SCOPE=SPFILE;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1000 SCOPE=BOTH;  -- max 1000

ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=BOTH;

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='10.2.0.4' SCOPE = BOTH;
ALTER SYSTEM SET OPTIMIZER_SECURE_VIEW_MERGING=TRUE SCOPE = BOTH;

-- Параметры
select * from v$parameter t where t.NAME='job_queue_processes'