All language subtitles for [SubtitleTools.com] Modifying Parameters - Learning Oracle 12c [Video]

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.