Which is true about the result of this command?

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?

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.

Show Hint

Leave a Reply 5

Your email address will not be published. Required fields are marked *

Liz

Liz

the correct answer is A. I test it on my db. It shows cdb is still typical but pdb is set as all

Reply

Rocky

Rocky

I tested the scenario, the answer shows it to be A .

SQL> alter session set container=TESTPDB;

Session altered.

SQL> sho parameter statis

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE

SQL> ALTER SYSTEM SET STATISTICS_LEVEL =ALL SID = ‘*’ SCOPE = SPFILE;

System altered.

SQL> alter pluggable database TESTPDB close;

Pluggable database altered.

SQL> alter pluggable database TESTPDB open;

Pluggable database altered.

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
———- —————————— ———-
3 TESTPDB READ WRITE

SQL> show con_id

CON_ID
——————————
3
SQL> sho parameter statis

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string ALL
timed_os_statistics integer 0
timed_statistics boolean TRUE

SQL> sho con_id

CON_ID
——————————
1
SQL> sho parameter statis

NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE

After CDB restart , the parameter is still typical at CDB and ALL for TESTPDB

Reply

Alexis

Alexis

I tested the scenario, the answer shows it to be A .

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

Reply