Container Clause in Oracle

Back to: Oracle DBA Tutorials

Container Clause in Oracle with Examples

In this article, I am going to discuss Container Clause in Oracle with Examples. Please read our previous article where we discussed Oracle Default Scope in Alter Statement with Examples.

Container Clause in Oracle

Until now we have discussed the scope in the alter statement. Now, we will teach you about the container clause in the alter system statement. This is one of the most confusing clauses in any statement. But we will try to make it simple to understand. We will be using the clause called the CONTAINER Clause. The syntax of the command will be as follows.

Command: alter system set parameter=value container=CURRENT| ALL;

We need to set the CONTAINER clause when you set a parameter value in the container database. We use this clause in the container database. A CDB uses an inheritance model for the initialization parameters in which PDBs inherit initialization parameter values from the root. This indicates when you change the parameter at the root level the parameters will also change at the database level. This is called inheritance.

A Pluggable database can override the root’s setting for some parameters [ISPDB_MODIFIABLE=TRUE]. This indicates the parameter can overwrite the root parameter value. For example, we could see the open_cursors parameter in the root as 300 and in the pluggable database as 500. This indicates that a pluggable database can overwrite the root parameter. We will discuss this later.

Note: If the pluggable database overrides the root setting, then a new record will be added to V$SYSTEM_PARAMETER.

If we specify CONTAINER=ALL, then the parameter setting applies to all containers in the CDB, including the root and all of the PDBs. The current container must be the root database. If you have logged in to the CDB$ROOT database and you use alter statement with CONTAINER=ALL then the changes will affect all the pluggable databases.

If we specify CONTAINER=CURRENT, then the parameter setting applies to the current container database. But, when the current container is CDB$ROOT, then the parameter setting applies to the root and to any PDB with an inheritance property of true but with no override done before by the pluggable database. Even if we logged in to the CDB$ROOT database and specify CONTAINER=CURRENT then the changes will affect all the pluggable databases but one condition is that the previous value should not be overridden by the pluggable database. We will understand by doing an exercise.

Examples to understand Container Clause in Oracle

Open the Linux virtual machine and open a terminal. Previously we started the database using pfile. We will not use pfile in this exercise. So, shut down the database and start the database using spfile.

Once the database is started let’s register the listener and check for all the listeners whether the listeners are up and running fine.

Command: alter system register;

Now, that we have registered the listener let’s go ahead and check whether all the listeners are running or not.

Command: lsnrctl status

Now, that the database is started using spfile. Then let’s open the SQL developer and connect to the database as “sysdba”.

We are using steps now.

Step 1: Connect to CDB$ROOT database.

Open the Linux machine and start the database and open the pluggable database. After finishing the start-up of the database, open SQL developer and connect to the database as sys as sysdba. Once the connection is successful let us check the database using the command “show con_name”

Step 2: We will be using the same open_cursors parameter for this exercise as well. We will be using the below query to check the value for the open_cursors parameter.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULAT,con_id from V$SYSTEM_PARAMETER where name=’open_cursors’;

We can see the value of open_cursors is 301. So, we can see that we have only one record with the name open_cursors. The value of ISPDB_MODIFIABLE=TRUE. This means that the pluggable database can override the value of this open_cursors parameter.

The PDBs inherit initialization parameter values from the root database. The pluggable database will read the value 301 for the parameter open_cursors.

Step 3: Change the value of OPEN_CURSORS.

Let us go ahead and change the value of the open_cursors parameter to 400 with the container clause as CONTAINER=CURRENT;

If we specify CONTAINER=CURRENT, then the parameter setting applies to the current container database. But, when the current container is CDB$ROOT, then the parameter setting applies to the root and to any PDB with an inheritance property of true but with no override done before by the pluggable database. Even if we logged in to the CDB$ROOT database and specify CONTAINER=CURRENT then the changes will affect all the pluggable databases but one condition is that the previous value should not be overridden by the pluggable database.

Command: alter system set open_cursors=400 container=current;

Now, that we have changed the value go ahead and run the same query as above to check the value of the open_cursors parameter.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULAT,con_id from V$SYSTEM_PARAMETER where name=’open_cursors’;

We can see the value of open_cursors is 400. Any new sessions in the root or pluggable database will inherit this value.

Step 4: Checking the value of open_cursors in the pluggable database.

Let us move from the container database to the pluggable database and check the value of the parameter open_cursors.

Now that we are in the pluggable database let us go ahead and same the query to check the value of the open_cursors parameter.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULAT,con_id from V$SYSTEM_PARAMETER where name=’open_cursors’;

We can see the value of open_cursors is 400. Let’s go ahead and change the value of the parameter to 450 inside the pluggable database.

Step 5: Change the value of the parameter open_cursors in the pluggable database.

We will be changing the value of the parameter from 400 to 450 inside the pluggable database.

Command: alter system set open_cursors=450 container=current;

Now, these changes are affected only in the pluggable database. Let’s run the query to check the value of open_cursors now.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULAT, con_id from V$SYSTEM_PARAMETER where name=’open_cursors’;

We can see the value of open_cursors is 450 and the con_id value is 3. This means that these changes will be affected only by this pluggable database.

Step 6: Check the value of open_cursors in the CDB$ROOT database.

After changing the value of open_cursors in the pluggable database. We will move back to the CDB$ROOT database.

Command: alter session set container=CDB$ROOT;

Now, let’s go ahead and check the value of the parameter open_cursors using the query.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULAT, con_id from V$SYSTEM_PARAMETER where name=’open_cursors’;

We can see two entries for the parameter open_cursors. Because we have changed the value of open_cursors in the pluggable database and that is the reason we have two values for the parameter open_cursors. This means that the pluggable database has overwritten the value of the open_cursors.

Step 7: Changing the value of open_cursors with container=current.

Now, let us change the value of the parameter open_cursors to 410 with container=current. This clause will make a change to only the CDB$ROOT database because the pluggable database has overwritten the value of open_cursors. We have already changed the value of open_cursors using the pluggable database and

Command: alter system set open_cursors=410 container=current;

So, we have changed the value of open_cursors to 410 in the CDB$ROOT database. Let’s run the value query to check the value of open_cursors.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULAT, con_id from V$SYSTEM_PARAMETER where name=’open_cursors’;

We can see the value of open_cursors is 410 in the CDB$ROOT database and the value of open_cursors in the pluggable database is 450 which we changed earlier.

Step 8: Changing the value of open_cursors with container=all.

So, previously we have mentioned container=current. Now let’s try to mention container=all and let’s see how this command works in both container and pluggable databases.

So, we have changed the value of open_cursors to 410 with container= all. This clause will reset all the current values and change the value of open_cursors to 410. Let’s run the query to check the value of open_cursors.

Query: select name, value, isses_modifiable, issys_modifiable, ispdb_modifiable, ISDEFAULAT, con_id from V$SYSTEM_PARAMETER where name=’open_cursors’;

From the output, we can see the value of open_cursors is changed to 410 and then all the current values have been reset.

If we specify CONTAINER=ALL, then the parameter setting applies to all containers in the CDB, including the root and all of the PDBs. The current container must be the root database. If you have logged in to the CDB$ROOT database and you use alter statement with CONTAINER=ALL then the changes will affect all the pluggable databases. But we need to mention this while we are in the container database.

In the next article, I am going to discuss the Automatic Diagnostic Repository in Oracle with Examples. Here, in this article, I try to explain Container Clause in Oracle with Examples and I hope you enjoy this Container Clause in Oracle with Examples article.