You are connected to a pluggable database (PDB) as a common user with DBA privileges.
The STATISTICS_LEVEL parameter is PDB_MODIFIABLE. You execute the following:
SQL > ALTER SYSTEM SET STATISTICS_LEVEL = ALL SID = ‘*’ SCOPE = SPFILE;
Which is true about the result of this command?
A.
The STATISTICS_LEVEL parameter is set to all whenever this PDB is re-opened.
B.
The STATISTICS_LEVEL parameter is set to ALL whenever any PDB is reopened.
C.
The STATISTICS_LEVEL parameter is set to all whenever the multitenant container database
(CDB) is restarted.
D.
Nothing happens; because there is no SPFILE for each PDB, the statement is ignored.
Explanation:
Note:
* In a container architecture, the parameters for PDB will inherit from the root database. That
means if statistics_level=all in the root that will cascade to the PDB databases.
You can over ride this by using Alter system set, if that parameter is pdb modifiable, there is a new
column in v$system_parameter for the same.
Correct answer is A.
The Answer is A
A – TRUE
documentation write :
SPFILE The initialization parameter setting is changed for the PDB in the SPFILE. The new setting takes effect in any of the following cases:
The PDB is closed and re-opened.
The CDB is shut down and re-opened.
In these cases, the new setting affects only the PDB.
then C is OK but after CDB restart you must open PDB to load new version of parameter !!
http://docs.oracle.com/database/121/ADMIN/cdb_pdb_admin.htm#ADMIN14017
A
is this right. can you make an alter system in a pdb?
A is correct answer.
But would be reset back when CDB is reopened!
A
A
Even after restart CDB.
I have to agree with A, but question states connected as PDB. Does this syntax doesn’t through error since DBA is specifically connected to PDB itself?
syntax changes only the setting in PDB.
A and C are correct
with SPFILE option
The initialization parameter setting is changed for the PDB in the SPFILE. The new setting takes effect in any of the following cases:
The PDB is closed and re-opened.
The CDB is shut down and re-opened.
In these cases, the new setting affects only the PDB.
I tested.
A – is correct, i.e. whenever you restart that specific PDB
C – is incorrect, as it gets reset to TYPICAL, which is the setting of the CDB. Also, i created a pfile from spfile and the change in parameter is not recorded in the DB’s spfile, maybe the reason why it gets reset to the default value.
Sorry, I replied too quickly.. forgot to start the PDB after restarting the CDB.
C – is also correct after starting the PDB (after the CDB restart), the value remains ALL
SQL> show con_name
CON_NAME
——————————
PDB1
SQL> show parameter statistics_level
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string TYPICAL
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show parameter statistics_level
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string ALL
A phantastic explanation from JanK!
A
I tested the scenario.
SQL> ALTER SESSION SET CONTAINER = db12cp;
Session altered.
SQL> ALTER SYSTEM SET STATISTICS_LEVEL = ALL SID = ‘*’ SCOPE = SPFILE;
System altered.
SQL> show parameters STATISTICS_LEVEL
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string TYPICAL
SQL> startup force
Pluggable Database opened.
SQL> show parameters STATISTICS_LEVEL
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string ALL
SQL> sho con_id
CON_ID
——————————
3
SQL> select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
———- —————————— ———-
3 DB12CP READ WRITE