Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

The Oracle currenty login and what the process has done.

Posted on 2013-06-10
17
Medium Priority
?
359 Views
Last Modified: 2013-08-08
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?
0
Comment
Question by:marrowyung
  • 8
  • 6
  • 3
17 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39235148
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39236104
Use Enterprise Manager dbConsole to monitor your database.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39236744
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39237414
Here are some scripts.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 39237504
>>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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 800 total points
ID: 39237753
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39278386
let me check
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39366099
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39366835
>>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
 
LVL 1

Author Comment

by:marrowyung
ID: 39382061
"
>>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
 
LVL 1

Author Comment

by:marrowyung
ID: 39382158
so V$session is real time information from RAM.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39382412
>>so V$session is real time information from RAM.

Pretty much.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39384722
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39385574
>>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
 
LVL 1

Author Comment

by:marrowyung
ID: 39388206
">>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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39389017
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39392375
thanks,
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question