Original subtitles

In this lesson we're going to be looking at how

to modify database parameters.

So modifying parameters is extremely important in the life

of a DBA, because database parameters

will control every aspect of how the database operates.

So we have two types of parameters in the database.

We have dynamic parameters and static parameters.

Dynamic parameters can be modified hot

if an spfile is used.

So we must start up the database with an spfile,

which is the default method.

And as long as we do, there are dynamic parameters

that we can change while the database is still open.

So this is radically different than if we

were to use a pfile, where we would have

to restart the database anytime we changed

any of the database parameters.

So the spfile is highly recommended and is the default

method, because it gives us the ability

to change some, not all, but many parameters and the amount

of parameters that we can change while the database is open,

things like memory parameters.

It's so beneficial that the spfile is really

the preferred method.

The second type are static parameters.

And static parameters require the database

to be restarted in order for those parameter changes

to take effect.

And again, if an spfile is used, all parameters are static.

So let's take a look at how to modify parameters.

We're going to log in as sysdba.

And we'll look at some commands to find out what

the values of parameters are.

So if we want to just see a list of the parameters,

we could say, show parameter.

And it gives us a list of all the parameters

that are in the database.

And it's a very long list.

I think it actually scrolls back further

than I can get to the top of.

But you see here things like a parameter

called timed statistics.

And the type of value it is.

And it's a Boolean value.

So the value is true.

So true is the state of timed statistics.

So timed statistics are being taken in the database.

Undo management is a string which is set to auto.

So automatic undo management is occurring.

Undo retention is a number value, and it's set to 900.

In order to interpret these, you'd

need some Oracle documentation.

The document that they call Reference on their document

site that has all the information about parameters.

So the values, min and max values, the type

of parameter, a little bit of explanation

about what it does, all of those kinds of things.

So if I want to limit this list a little bit, I can do,

show parameter, and then any portion

of the name of the parameter itself.

Show parameter undo.

So it will give me all of the parameters that

have undo in the name of the parameter, in the beginning,

in the middle, the end, wherever.

It'll show me those parameters.

So let's take a look at how to modify these a little bit.

We said that there's dynamic parameters

and then there are static parameters.

So let's attempt to just change one of the static parameters.

So any time we want to change a parameter,

we use the command alter system, set,

then the name of the parameter equal to the value, and then

the scope.

So the scope has three possible values,

scope=memory scope=spfile, or scope=both.

So when we do a scope=memory, we're saying that we want

to change the parameter in the database memory right now.

So in a dynamic parameter, those would take effect immediately.

However, they're not written out to the spfile.

So whenever the database is restarted,

that value of 301 for processes would not be in there.

And it would start with whatever it has in the spfile.

If we set scope=spfile, it will only change the value

of the parameter in the spfile.

It will not change it in memory, so that would not

take effect until the database was restarted

and the spfile was reread.

And the other possibility is both.

When we do both, we're changing it in memory and the spfile

simultaneously.

So again, this can only be done if the parameter is dynamic.

So for testing purposes, let's try to change the parameter

processes to scope=memory.

All right.

So it says that our specified initialization parameter cannot

be modified.

So this will be the error message

that we get any time that we want

to change a static parameter dynamically and can't do that.

So we'll get this error message.

So let's try one that is dynamic.

Alter system set undo retention equal to--

notice that our value for undo retention is 900--

and we'll set it to 1,200.

So we say scope=memory.

And it changes it.

If we do show parameter undo retention,

it shows us that now the value for undo retention is 1,200.

And this is actually measured in seconds,

and is the number of seconds that Oracle attempts

to retain undo information even after a commit has occurred.

And then we may say, well, I also

want undo retention to be set in the spfile.

So in that case, we could simply say, scope=both.

So now, when the database is restarted,

it will retain that parameter change and the undo retention

will be set to 1,200.

So let's go back to processes.

So the value for processes is 300.

We attempted to set it to 301, but it told us

that we couldn't change that parameter dynamically.

So how do we change it?

Well, in this case, let's do an alter system set processes=301.

scope equal-- now, think of what our options here are.

We can't set it in memory.

So we can't do scope=memory.

We can't do scope=both, because that would do it in memory

and in the spfile.

So our option is to say scope=spfile.

Now, if we look at the value of it,

we notice that it's still 300, because it will not

pick up that change until we restart the database.

So we're going to shut down the database and start it up.

So now, we'll take a look at our parameters.

So first, we'll show parameter undo retention.

Notice that undo retention, which

was 900 when the database was started before,

we modified it in memory and the spfile both.

So now that the database is restarted,

it reads the spfile and the value

that we put into the spfile, and has the new value for 1,200.

What about processes?

Notice here that the value for processes,

which was static and we could not change dynamically,

we changed it to 301 in the spfile.

And when the database was restarted,

the parameter file was reread, and the spfile

had the value of 301.

And so now that is the value in the database.

So now we've increased the number of processes

that a database can have to 301 from 300

using the modification of parameters.