[基础知识] spfile 操作

-- 修改 spfile
alter system set
    parameter = value
    <comment = 'text'>
    <deferred>
    <scope = both|spfile|memory>
    <sid = 'sid|*'>；

-- deferred 延迟设置
select name from v$parameter where issys_modifiable = 'DEFERRED';

NAME
----------------------------------------
backup_tape_io_slaves
audit_file_dest
object_cache_optimal_size
object_cache_max_size_percent
sort_area_size
sort_area_retained_size
olap_page_pool_size

7 rows selected.

-- 取消 spfile 设置，恢复默认值
-- 非 rac 环境，可以用 sid = '*'
alter sysetm reset
    parameter <scope = memory|spfile|both> sid = 'sid|*';

-- spfile 中不存在 sid = demo 的这一项
go@DEMO> alter system reset sga_target scope = spfile sid = 'demo';
alter system reset sga_target scope = spfile sid = 'demo'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

-- 必须指定 spfile
go@DEMO> alter system reset sga_target sid = '*';
alter system reset sga_target sid = '*'
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance demo

-- 指定 spfile 和 sid
go@DEMO> alter system reset sga_target scope = spfile sid = '*';

System altered.