This video presents the method you can use to view values for existing database parameters and configuration options as well as modify these values using Oracle SQL*Plus. This video also describes how to make changes persistent and export the configuratio
- View Offline
- [Teacher] The Oracle database instance is configured via a long list of initialization parameters that are stored in the database SPFILE, or server parameter file. Remember that is a binary file that holds the instance configuration. Modifying parameters can be done using the Oracle SQL*Plus interface. Let's go ahead and change some of the Oracle instance parameter starting off as always by connecting to the database as the sys DBA user.
SQL*Plus as sys DBA. We can view the currently configured value for a specific Oracle parameter by running the command show space parameter, and then the parameter name. Such as SGA underscore target. We see that in my database, I have the SGA underscore target parameter configured with a value of about 1400 megabytes.
We can also type show, parameter, SGA underscore, press enter, and see the values for all parameters that start with SGA underscore. Such as both SGA underscore target, as well as the SGA underscore max underscore size parameter. Setting the value for a parameter is done via the alter system set command.
For example, let's set the value for SGA underscore target by typing alter, system, set, SGA, underscore target, equals 1000 megabytes, or a gigabyte. So I'm decreasing the size available for the SGA, space, I'll need to follow this command with a scope equals argument.
Where we can specify either memory, which will modify the parameter in the instance temporarily, and it will be reset to the previous value following a restart. I can also specify SPFILE, which means that the parameter will only be stored in the SPFILE and only take effect following an instance restart. Or, I can specify both, which would mean the parameter will both be dynamically changed right now in memory, as well as stored in the SPFILE, so that upon restart, the new value will take effect.
Let's take a look at the value for SGA target after we have performed a change. Show parameter SGA underscore target. And you can see we have a new value in effect. Remember that Oracle sometimes rounds up values for memory parameters. While the SPFILE is a binary file, so you cannot manually edit it, only change parameters using the SQL*Plus interface, or Oracle Enterprise Manager for example, you can export the SPFILE to a text file, which will be humanly readable.
We can do that by typing create, PFILE, which stands for parameter file. Let's store it in the Oracle Linux user home directory. So quotation marks slash home slash Oracle slash, and let's give it a name such as my underscore PFILE dot ora quotation mark, from SPFILE. Oh, my bad! Note that I've specified the location for the parameter file using double quotes; however, Oracle expects single quotes.
So let's modify the command to use single quotes instead. my PFILE dot ora from SPFILE. Ah, there we have it. As you can see, Oracle has created a PFILE from our SPFILE. Let's take a look inside our PFILE. In order to do that, we need to quit SQL*Plus, go to our Oracle home directory, type ls, and as you can see, we have a my underscore PFILE dot ora file created.
Let's take a look inside the file and see its contents. So write cat space my PFILE dot ora, press enter, oh and that's a long list of parameters you have there. The PFILE contains a list of all currently configured Oracle instance parameter. We can search for a specific parameter by typing cat space my underscore PFILE dot ora, and passing the grep command, such as grep SGA underscore target.
Remember this parameter? We just modified it. So there you have it. We can easily view and set Oracle instance parameters using the SQL*Plus command line interface, and export the binary Oracle configuration file, or SPFILE, into a plain text file, so it can be humanly readable.
After completing this course, you'll have fundamentals required for installation, configuration, and administration of an Oracle 12c database.
- Database instance and storage
- Instance memory pools
- Instance background processes
- Client connections
- Database storage file types
- Control files and backup files
- Multitenant databases
- Starting and stopping the database
- Installing Oracle 12c software
- Using the developer tools
- Database management