All language subtitles for [SubtitleTools.com] Monitoring Sessions - Learning Oracle 12c [Video]

We're going to be doing a little
work with monitoring sessions.

And this is important
because monitoring sessions

is one of the core things
that a DBA does, or at least

the ability to go in and
find a particular session.

So if a session has a job
that's hung or long-running

and it needs to be
killed for some reason,

a DBA needs to go in
and find the session,

see what it's doing, or
possibly disconnect it

from the database.

So to do this, we
need some sessions

connected to our
database, so I'm

going to open one
with the Kara user,

and then a separate one
from SQL Plus, or Scott.

All right, so
there's a lot of ways

actually that we can look
at session information,

and most of those involve
leveraging the data dictionary.

And that's what we're
going to focus the most on.

But I did want to point out
that SQL Developer has a session

monitoring tool.

If we go up to Tools,
then Monitor Sessions,

we select from our
dropdown whichever one

of our connections we would use.

And we're going to
want a system account,

because we're going to want
to have the privileges to look

at v$session, because that's
what the tool will do,

and click OK.

So notice here that we've
got a Kara user connected

and a Scott.

And then of course our
system has a connection

to run this monitor
session's browser.

And so it has information
such as the machine,

the operating system
user, whether it's

active or inactive,
the module it's using,

which will basically normally
be the application name.

So we see a couple with
SQL Developer and one

with SQL Plus.

So that can be useful if
we're working in SQL Developer

and want to look at
basic system information.

I'm going to close out of that.

But what we want to spend the
majority of our time doing

is leveraging the
data dictionary.

So we'll be looking at
v$session and v$process.

So I've got my system
connection here.

I'm going to right-click, go
down to Open SQL Worksheet.

So let's take a basic
look first at v$session.

We'll do select
star from v$session.

Now, SQL Developer shows us
the output from this command.

And I did want to mention that
v$session is one of the widest

data dictionary
views that there is,

and it does seem they add to
it continually as versions

increase.

So you can see as
I scan here there

are a really large
number of columns.

But the kind of information
that we have here--

things like SID and serial
number, the username, status,

the operating system user--

one thing that's different
than the SQL Developer session

browser is that we're going
to include in v$session all

of the background processes.

So these are going to be some of
the ones where username is null

and the OS user is system
instead of the SRIES user

that's connected in.

So what we want to do normally
when we v$session is we want

to kind of be more
selective with it.

So let's bring it
down a little bit.

Username, status, OS user,
machine, action, logon_time,

event, and state
from v$session--

so this limits the
information a little bit more.

So when we go to v$session,
normally we're looking

for something.

This would help us kind of bring
down the volume of information

that we have in v$session
into a more manageable piece.

But of course that's the
important thing and the beauty

really of using the
data dictionary,

is that whatever information you
want, you can just select it.

Whereas with our SQL
Developer session browser,

that information is
already decided for you.

All right, so we have things
here like the usernames.

We have a Kara user here.

There's status,
inactive or active.

Inactive indicates
that they're logged

in but not executing anything.

The OS user and machine,
the action that's going on,

the logon time, so the
time that they logged on,

and the event, so
whatever event is

occurring for them at this
moment, and then the state.

So it could be
waiting, it could be

waiting on a specific
action, it could be running,

it could be any of
those different things.

So what if we want to be a
little more targeted about who

we're looking at?

Well, we simply add a where
clause, where username

equals Kara.

Now we have Kara's
specific information.

We can also take
a look at Scott.

And we can see some
differences here,

the way it shows up,
because one of them

is a tool that connects
in, essentially remotely.

It's basically coming out
and back in over the network,

whereas our SQL Plus session
is connecting locally.

What if we needed some
direct information

about the background processes?

So I mentioned that the
background processes are listed

in v$session.

So what if we want to
exclude the other sessions

and just look at background
process information?

Well, we could do
a query like this.

So we'll select program, status,
action, and logon_time where

the type equals BACKGROUND.

So notice here that we
should be able to see

some of the processes
that we're familiar with,

like the checkpoint process.

And further down we have SMON,
PMON, the database writer

process, DBW0, and
so on and so forth.

And a lot of these
other processes

are slave processes
to the parent process.

So we can get background process
information from v$session

as well.

To look at another way and
leverage a different data

dictionary view, so here we're
going to look at a different

view, v$process.

So every session that connects
into the database is going

to have a process
that goes with it,

and so we can also get
background process information

from the v$process, as well.

So here we have more
directly their name, we

have the PMON and the
MMAN, log writer process,

so on and so forth.

And then it actually
has their trace file,

the location of the
trace file that they're

writing to at this moment.

So if any messages come
through any of those processes,

they'll be written
in those trace files.

So one of the things that
we'll need to do as a DBA

from time to time is kill a
process or kill a session.

So how do we find
the information

needed to kill a session?

Well, let's try this query.

So this is going to give
us the username and the SID

and serial number
for the session.

So these two pieces
of information

are what we need when we
want to kill a session.

We can't simply say,
kill the Kara session,

because there could
be many Kara sessions.

So we're going to identify that
with a SID and a serial number.

The SID is not always
unique, but the two together,

SID and serial number, are.

And keep in mind that
SID here should not

be confused with the database
SID or system identifier.

SID in this case really refers
to a session identifier.

So I have my Scott session here.

So let's kill that session.

Altar system, kill session.

And in single quotes we put
the SID and the serial number.

We'll double check
that to make sure.

All right, so we're killing
the Scott session, 130 and 167.

We execute.

Now, when we come
back over here,

and Scott attempts to
select star from emp,

he gets a message that his
session has been killed.

So that's a little bit
about session management.