Scope setting initialization parameters take effect immediately

Scope sets the initialization parameters to take effect immediately. 1. Test Requirement background. 1. Have you asked me if the initialization parameters 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 of data files, if you want to use 64 GB of data, you need to modify the initialization parameter db_16k_cache_size. 3. By the way, we have 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. 2) The same tablespace can only use the same block3) spfile to start the database. You can use scope = both to make the parameter take effect immediately in the memory, but note that the pfile cannot start the database. Ii. Test process

SQL> show parameter blockNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_block_buffers                     integer     0db_block_checking                    string      FALSEdb_block_checksum                    string      TYPICALdb_block_size                        integer     8192db_file_multiblock_read_count        integer     128SQL> show parameter db_16kNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_16k_cache_size                    big integer 0SQL> 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 40G;alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40G*ERROR at line 1:ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocksSQL> 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 16k;alter tablespace users add datafile '/oracle/oradata/user02.dbf' size 40G blocksize 16k                                                                          *ERROR at line 1:ORA-00933: SQL command not properly endedSQL> create tablespace aaa datafile '/oracle/oradata/aa.dbf' size 40G blocksize 16k;Tablespace created.SQL> SQL> SQL> host du -sh /oracle/oradata/aa.dbf41G     /oracle/oradata/aa.dbf

 

Iii. scope parameter Details SCOPE = SPFILE The change is applied in the server parameter file only. the effect is as follows: ■ For dynamic parameters, the change is valid 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, the error SCOPE = MEMORYThe 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 = BOTHThe 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 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.