The Oracle currenty login and what the process has done.

Dear all,

I come from MS SQL background, sometime some kind of bad query can kill a system DB's transcation log( some one might test how they can kills MS SQL), so we have to find it out and kill the process.

May I know:

1) any script to find out what is the real time process to find out which host the query from, what locks it is introducing to the table it runs against, what application via that the query runs, the start time and end time of the query executed.
2) how to kill that process, by an id I think? what we call the id number? call SPID (in MS SQL)?
3) what kind of lock it introduced?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>please give me one example.

The link posted by MikeOM_DBA seems pretty decent.  Personally, I don't keep one around in my scripts folder.

It's so rare I have to go looking for them that when I do have to, I just Google for the script I need.  Google: oracle find locks

>>is this means use "select * from v$session" ?

That will get it but there are many columns in that view.  Go after a targeted strike:
select username, machine, sid, serial# from v$session;

>>any view or something else list them all ?

Depends on the type of lock you are looking for.  In the doc link above, look through it for "V$".  For example: V$LOCK.

>>just like sp_who2 in ms SQL.

Sorry.  I'm not a sql server person so I don't know what this shows.
0
 
slightwv (䄆 Netminder) Commented:
1)  There are many script examples out there if you look around.

2) alter system kill session (sid,serial#);
sid and serial# come from the v$session view

3) types of locks are in the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#CNCPT88970
0
 
MikeOM_DBACommented:
Use Enterprise Manager dbConsole to monitor your database.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
marrowyungSenior Technical architecture (Data)Author Commented:
slightwv,

"1)  There are many script examples out there if you look around."

please give me one example.

"2) alter system kill session (sid,serial#);
sid and serial# come from the v$session view"

is this means use "select * from v$session" ?

"3) types of locks are in the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#CNCPT88970 "

any view or something else list them all ? just like sp_who2 in ms SQL.
0
 
MikeOM_DBACommented:
Here are some scripts.
0
 
MikeOM_DBAConnect With a Mentor Commented:
Why re-invent the wheel?
With each database you get Enterprise Manager utility FREE.
EM will give you ALL the information you are requesting PLUS much, much more.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
let me check
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
MikeOM_DBA,

"With each database you get Enterprise Manager utility FREE."

Just like MS SQL, if something jam up one of the system DB and the Console will say cant' connect to it, then can't check.

using script is good ! DBA like script !

What is this for ? sess_current_user_transactions.sql? seeing real time transcation which doing inside Oracle?

slightwv,

"The link posted by MikeOM_DBA seems pretty decent.  Personally, I don't keep one around in my scripts folder."

What do you use ?

"Google for the script I need.  Google: oracle find locks"

Just google it and you always find the scirpt you wnat ?

"v$session;"

ALL real time session ?

"Depends on the type of lock you are looking for.  In the doc link above, look through it for "V$".  For example: V$LOCK."

basicall what is V$ stand for ?
0
 
slightwv (䄆 Netminder) Commented:
>>What do you use ?

I really don't ever have a need for scripts to find locks.  From time to time I do need to track down who is running some bad SQL that is pounding the server.

When I need one, I Google to find it.  I really never use one enough to keep it lying around in my scripts folder.

>>basicall what is V$ stand for ?

Those are the Virtual views.  They connect metrics and statistics since the instance started up.  They do not persist in the database.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"
>>basicall what is V$ stand for ?

Those are the Virtual views.  They connect metrics and statistics since the instance started up.  They do not persist in the database."

ok, it is same as the dynamic view in MS SQL, I think.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
so V$session is real time information from RAM.
0
 
slightwv (䄆 Netminder) Commented:
>>so V$session is real time information from RAM.

Pretty much.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok, then really need to try then.

But any view to see what is in the virtual RAM or Real RAM?

in MSSQL, opentran() is to check the virtual RAM.
0
 
slightwv (䄆 Netminder) Commented:
>>in MSSQL, opentran() is to check the virtual RAM.

I don't understand what you mean by virtual RAM.

I'm not a SQL Server person.  If you can provide a doc link to what that does I'm sure we can provide something similar in Oracle.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
">>in MSSQL, opentran() is to check the virtual RAM.

I don't understand what you mean by virtual RAM."

Ignore that. VIRTUL RAM just like paging file in Windows, if RAM is not enough, it will store thing in the disk instead of RAM. That's what I mean Virtual RAM.
0
 
slightwv (䄆 Netminder) Commented:
I don't know of a way for Oracle to report on how much memory it is using is swap space versus physical memory.

This doesn't mean it doesn't exist, I have never seen anything on it.

You typically monitor swap usage from the OS level.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.