Oraclescope setting initialization parameters take effect immediately

I. Test Requirement Background 1. Have you asked me if I can take effect immediately after modifying the initialization parameters? I did a test immediately and concluded that yes. 2. in linux, Oracle

I. Test Requirement Background 1. Have you asked me if I can take effect immediately after modifying the initialization parameters? I did a test immediately and concluded that yes. 2. in linux, Oracle

I. background of testing requirements
1. Have you asked me if the initialization parameter can take effect immediately after modification? I did a test immediately and concluded that yes.

2. in linux, Oracle data blocks are 8 KB by default and support a maximum of 32 GB Data Files. To use 64 GB Data blocks, you need to modify the initialization parameter db_16k_cache_size.

3. By the way, I reviewed and summarized the basic concepts related to tablespace:

1) You can set different block sizes in the same database to set data files of different sizes. Sometimes the business needs this.

2) The same tablespace can only use the same block.

3) For a database started with spfile, you can use scope = both to make the parameter take effect immediately in the memory. However, you must note that the database started with pfile does not work.

Ii. Test process

SQL> show parameter block

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_block_buffers integer 0
Db_block_checking string FALSE
Db_block_checksum string TYPICAL
Db_block_size integer 8192
Db_file_multiblock_read_count integer 128

SQL> show parameter db_16k

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_16k_cache_size big integer 0

SQL> alter system set db_16k_cache_size = 34603008 scope = spfile;

System altered.

SQL>
SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40 GB;
Alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40 GB
*
ERROR at line 1:
ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocks

SQL> alter system set db_16k_cache_size = 34603008 scope = both;

System altered.

SQL> alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40G blocksize 16 k;
Alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40G blocksize 16 k
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> create tablespace aaa datafile '/oracle/oradata/aa. dbf' size 40G blocksize 16 k;

Tablespace created.

SQL> host du-sh/oracle/oradata/aa. dbf

41G/oracle/oradata/aa. dbf

Iii. scope Parameters

SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows:
■ For dynamic parameters, the change is usually tive at the next startup and is persistent.
■ For static parameters, the behavior. is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.
(This change is written to the initialization parameter file and will take effect the next time it is started. Dynamic parameters are the same as static parameters. It is also the only method available for static parameters .)
Only databases started with spfile can be used. Otherwise, an error is reported.
SCOPE = MEMORY
The change is applied in memory only. The effect is as follows:
■ For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
■ For static parameters, this specification is not allowed.
(Modification only in Memory takes effect immediately, but will not take effect after restart because it is not written to the initialization parameter file. Static parameters are not allowed .)
Used for databases not started by spfile. If spfile is used at the next startup, it becomes invalid.

SCOPE = BOTH
The change is applied in both the server parameter file and memory. The effect is as follows:
■ For dynamic parameters, the effect is immediate and persistent.
■ For static parameters, this specification is not allowed.
(It is written to the initialization parameter file and modified in the memory, and takes effect immediately. It also applies only to dynamic parameters, but not static parameters .)
Only databases started with spfile can be used. Otherwise, an error is reported.

Related reading:

Summary of Oracle scope/sid/deferred/reset