Oracle Default Scope in Alter Statement

Back to: Oracle DBA Tutorials

Oracle Default Scope in Alter Statement with Examples

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

Oracle Default Scope in Alter Statement

Until now we have used the alter system command to change the values of the parameters in the database. We have used scope=spfile, scope=memory, and scope=both. There is another option in the scope called Default Scope. The default scope is indicated as not mentioning scope in the command.

We have used alter system set parameter=value and then we use scope. If we didn’t use scope it takes default scope. The default scope has two cases.

1. If a server parameter was used to start up the database, then BOTH is the default case. If the last time you start the database using the spfile or normal startup the database the default scope will be both. So, the command behind “alter system set parameter=value “ will be “alter system set parameter=value scope=both”.
2. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify. So, if we start the database using pfile the only option for scope is memory. So, the command behind “alter system set parameter=value” will be “alter system set parameter= value scope=memory”.

Now, we will do an exercise to showcase the use of scope in detail.

Example to Understand Oracle Default Scope in Alter Statement:

We have a parameter called OPEN_CURSORS. This parameter OPEN_CURSORS specified the maximum number of open cursors a session can have at once. When we do any select statement or any other statement oracle will open the cursor in the memory. We can use this parameter to prevent a session from opening an excessive number of cursors. So, this is good for database performance. It prevents to the opening of a huge number of cursors of a user.

Note: 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’s check the database using the command “show con_name”

Now we are in the CDB$ROOT database. Let’s run a query for the OPEN_CURSORS parameter.

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

We can see the value of open_cursors is 300. So, any session is allowed to open 300 cursors. We can see ISSYS_MODIFIABLE is IMMEDIATE which indicates we can change the value in memory level. The value of ISSES_MODIFIABLE is FALSE but we can change the value in the pluggable database (ISPDB_MODIFIABLE). Let us try the same query but we will be using the V$SYSTEM_PARAMETER view.

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

We can see the value of open_cursors is 300. This means any new session has inherited this value. We can see the value of ISDEFAULT is FALSE which indicates that we can see the value parameter in spfile. So, now let us check how the database has been started using “show parameter spfile”.

So, the database has been started using the spfile. So, now let’s change the value of the parameter open_cursors using the below command.

Command: alter system set open_cursors= 301;

We haven’t mentioned the scope. So, the default scope for this command is scope = both and the background command will be “alter system set open_cursors=301 scope=both” because the scope will be both if the database had started using spfile.

Now, that we have changed the value of the parameter. let us query using the view V$SYSTEM_PARAMETER.

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

We can see the value is 301. These changes are made in both spfile and memory because it takes the default scope. Now, let’s go ahead and check the spfile if these are applied in the spfile or not. Go to the below location and open the spfile to view the changes.

Open the spfile and view the parameter open_cursors.

We can see the value of open_cursors is 301. So, we understood that when we start the database using spfile, the default scope will be both and the changes will affect both in memory as well as spfile.

Now, let’s just understand the case in the default scope. Open the virtual machine and connect to the database using “sqlplus / as sysdba”. Once connected to the database shut down the database.

Once the database is down, start the database using pfile. We need to mention the entire path of pfile. We have already started the database using pfile. You can check this document for how to start the database using pfile. Non-Default Name for Pfile in Oracle – Dot Net Tutorials

Command: startup pfile= /u01/app/oracle/product/19.0.0/dbhome_1/dbs/newpfile.ora

Now, the database is opened. Let’s check the parameter spfile to find out with which file the database has started.

We can see the value of spfile is null. This indicates the database has started using pfile. Let’s try to change the value of open_cursors using the scope as spfile and see what would be the output.

Command: alter system set open_cursors=301 scope=spfile;

We can see the error “write to SPFILE requested but no SPFILE is in use”. Because the spfile is not in use as we have started the database using pfile. So, we already discussed that if the database has started using pfile then memory is the default option as well as the only option.

In the next article, I am going to discuss Container Clause in Oracle with Examples. Here, in this article, I try to explain Oracle Default Scope in Alter Statement with Examples and I hope you enjoy this Oracle Default Scope in Alter Statement with Examples article.