Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.