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.
Nothing happens; because there is no SPFILE for each PDB, the statement is ignored.
B.
The STATISTICS_LEVEL parameter is set to all whenever the multitenant container
database (CDB) is restarted.
C.
The STATISTICS_LEVEL parameter is set to all whenever this PDB is re-opened.
D.
The STATISTICS_LEVEL parameter is set to ALL whenever any PDB is reopened.
C is correct answer.
SYS@pdb2_2:SQL> show parameter statistics_level
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string TYPICAL
SYS@pdb2_2:SQL> alter system set statistics_level=all sid=’*’ scope=spfile;
System altered.
SYS@pdb2_2:SQL> shutdown immediate
Pluggable Database closed.
SYS@pdb2_2:SQL> startup
Pluggable Database opened.
SYS@pdb2_2:SQL> show parameter statistics_level
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string ALL
SYS@pdb2_2:SQL>
C
I tested the scenario, the answer shows it to be C.
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
i check a bit more then just the PDB.
the parameter *.statistics_level=’ALL’ is added to the spfile (as instructed)
but that means C is not correct but B and D are.
B is right as the CDB will change the parameter upon restart
D is right as the ANY clause
the above answers are limited to only the PDB the command was executed.
i used sys as common user with DBA privs.