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

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.