Your database instance is started with a PFILE.
Examine these parameters:
You want to increase the size of the buffer cache.
Free memory is available to increase the size of the buffer cache.
You execute the command:
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M;
What is the outcome?
When the database instance is started with a PFILE, the instance uses a static text file for its initialization parameters. Alterations made to the system configuration using the ALTER SYSTEM command are applied to the current running instance's memory. However, these changes are not written back to the PFILE. Therefore, the alteration will only persist for the duration of the current instance's uptime and will not be retained after a restart. This necessitates manual updating of the PFILE if persistent changes across restarts are required.
Correct Answer: D, the change is applied in memory only, the change does not persist as the database can't write to the pfile The default SCOPE option, when you start the instance using a PFILE, is MEMORY (as well as the only scope option you can use with a pfile). If scope is omitted the default scope option is used instead. The SCOPE clause is optional and not mandatory when changing the value of a parameter! A is wrong, as the pfile is a read only file for the database so it can't write to it. Changes to the pfile have to be done manually by changing it directly using f.e. vi on unix systems. B is wrong, same reason as for why answer A is wrong C is wrong, as mentioned above, when omitted the scope clause defaults to MEMORY when using a pfile. Not related to that question but the more you know: When starting the database using a spfile you have three options for the scope (MEMORY, SPFILE and BOTH). Default value is BOTH.
yes, agree. D is the correct answer
Per adoptc94's final notes, in 19c "When starting the database using a spfile you have three options for the scope (MEMORY, SPFILE and BOTH). Default value is BOTH.". As the default is both, when SCOPE is not used, the change will be applied to both MEMORY and the SPFILE.
Yes, the default is SCOPE=BOTH if a server parameter file was used to start up the instance, and the default is MEMORY if a text initialization parameter file was used to start up the instance.
The right answer is B! Default is SCOPE=BOTH
A is wrong. PFILES are static and any changes made to the system does not update the text (p)file. B is wrong. Issue as with point A. C is wrong. Statement without the scope parameter does not fail. Easy to test. D is correct. PFILE based instances change parameters by default happen in memory and as with points A and B, will not modify the PFILE.
Sorry corect is a. I tested. And If you started your instance with a server parameter file, you can use the ALTER SYSTEM command to change the values of the initialization parameters in the server parameter file. Specify the SCOPE=SPFILE option to change the values in the server parameter file only. Specify SCOPE=BOTH to change values in memory and in the server parameter file. This option is valid only for dynamic parameters. SCOPE=BOTH is the default if the instance has been started with a server parameter file.
A - FALSE; DB_CACHE_SIZE is a dynamic parameter, its change is applied immediately to the instance B - FALSE; when starting db instance with PFILE, no parameter changes can be written to PFILE C - FALSE; for dynamic parameters and starting db instance with PFILE, SCOPE=MEMORY is a default /implicit option, so it can be omitted. Change is applied to current instance. D - TRUE; see C
D is correct since it's PFILE If a server parameter file (spfile) was used to start up the database, then BOTH is the default. If a parameter file (pfile) was used to start up the database, then MEMORY is the default, as well as the only scope you can specify. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-SYSTEM.html#GUID-2C638517-D73A-41CA-9D8E-A62D1A0B7ADB
When you execute the "ALTER SYSTEM" command to modify a parameter, the default behavior is to change the value for the current instance and also update the value in the server parameter file (in this case: PFILE) to make it persistent across instance restarts.
I was wrong, correct answer is D.
All answer are wrong. Tested: a) The change is applied immediately. Database cannot write into the PFILE (init.ora). b) Database cannot write into the PFILE (init.ora). c) It does not fail: SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M; System altered. Mon Feb 27 13:01:09 2023 ALTER SYSTEM SET db_cache_size=1024M SCOPE=BOTH; SQL> show parameter db_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 1G d) Default SCOPE option is BOTH (as shown in the alert.log). The change persist after instance restart (validated).
Correct answer should be B regarding SPFILE, but not PFILE.
Spfile (server parameter file)-> default scope both Pfile(texto parameter file) -> default scope memory (and only avalable). Database can't write un text parameter file.
right answer : D
D is correct