Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

The Oracle currenty login and what the process has done.

Posted on 2013-06-10
17
Medium Priority
?
361 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month10 days, 5 hours left to enroll

571 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