Security :: Find Out Locked Table And Session From Database

May 20, 2013

I used to find out locked table and session from database....query with SYS user but i want to hand over the user session kill role to location level IT person so i have created one user in database named as rab and i have given "select any table,alter session and Grant dba to that rab user,but with that user they will drop and delete any table also

col owner format a12
col object_name format a25
col ORACLE_USERNAME format a15
col OS_USER_NAME format a15
col OBJECT_TYPE format a15
set lines 140
[code]....

View 3 Replies


ADVERTISEMENT

Object Locked But Can't Find Session / Process Blocking?

Sep 5, 2012

I am on database 10.2.0.5, windows x64.

A developer calls me and tells me there are locks on 3 tables, and the locks are not released. So I run this to see which objects are locked by which session

select vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
  , DECODE(vlo.LOCKED_MODE, 0,'NONE', 1,'NULL', 2,'ROW SHARE', 3,'ROW EXCLUSIVE', 4,'SHARE', 5,'SHARE ROW EXCLUSIVE', 6,'EXCLUSIVE', NULL) LOCK_MODE
  , do.owner, do.object_name, do.object_type

[code]...

When I run the query later, I sometimes get another session, but the lock stays. It seems here, that I am catching the same SID on new sessions, but it is probably a different serial # than the original session locking the table rows.

I get nothing from
select * from dba_waiters;
select * from dba_blockers;

Now why are those 3 tables row locked on some rows and I can't find the session responsible? The developer tells me his application crashed and the rows are locked since. So far ( keep in mind I am not a locking expert) the only way I found to release the locks is a DB bounce, its a test DB so no biggy.

View 18 Replies View Related

Security :: To Find Out Which Row Locked By Particular User

May 14, 2011

If the user has locked some row in the database how to find out the row id or the row which is locked by that particular USER.

View 2 Replies View Related

Security :: Audit Database Session From A Particular Applications?

Aug 14, 2013

Is there a way where we can audit database session from a particular applications? For example : We need to audit Toad and SQL developer sessions .

View 1 Replies View Related

Security :: Accounting - Cause Of Oracle User Being Locked

Oct 23, 2012

An Oracle user account got locked, how do I check, which program or session was the cause for it? how to fetch this details along with details like timestamp, ip address, program name.

Wanted to know for Oracle 11 version running on Solaris machine.

View 2 Replies View Related

Security :: User Account Locked Error

Oct 3, 2012

we have a prod application which use oracle database 11g as backend and .NET technology as front end . There is a user TESTUSER in database.

Issue is that we can login in to the application, but when we try to login in database through toad or Db visualizer then it gives an error of 'user account locked'.

View 5 Replies View Related

SQL & PL/SQL :: Find Locked Tables In Hierarchy

Feb 1, 2012

Is it possible to find the locked objects in hierarchical order. Consider the below example

--Connect to scott schema
create table block_session(a NUMBER, b VARCHAR2(100))
/
insert into block_session select rownum,rownum*10 from dual connect by level<=10
/
COMMIT
/
GRANT ALL ON block_session to HR
/
update scott.block_session
SET a=10
where b=10
[code]......

Is it possible to get all locked table details in hierarchical order?

View 4 Replies View Related

Security :: How To Find Current User In Database

Sep 22, 2011

query to find the current user accessing the database

View 5 Replies View Related

Security :: Find Out The User Access The Database?

Nov 8, 2011

Is there a way to find out the user access the database?

View 1 Replies View Related

Security :: Find A List Of Roles And Privileges In Database?

Feb 24, 2011

How can I list all the roles and privileges of roles in Oracle? Can I get the details of the activities they perform?

View 12 Replies View Related

Security :: How To Find User And IP Address From Oracle Database

Jun 19, 2013

Last week we have realized that a user who connects through SQL Developer(as nothing wrong found in application server logs) has made a serious change in the database which created a real mess. The user has done some mischief in some calculations by making some inserts and updates in some important tables in our production database.

How can I find which user or from which IP the change is made.

View 6 Replies View Related

Security :: User Locked / Track User Who Hit Wrong Password

Dec 21, 2010

We have the database user called c88888 and is locked yesterday. I came to this with the following query.

select * from dba_users where username like 'C88888';

Due to invalid password the user was locked. Can we track who hit the database with the wrong password?

View 5 Replies View Related

Get Table RowID Of Session That Is Locking In RAC Database

Jun 11, 2012

I am a developer and not a DBA and I need to find th correct query to find the exact rowid of the record locked on a table. This is for a RAC database and locked record can be from the web form in oracle application server. When I try to get the correct row id, I get the following error:

ORA-01410 - Invalid row id For the criteria, the output is Dbms_Rowid.rowid_create(1, -1, 36, 7845, 0), why I get a -1 for the ROW_WAIT_OBJ#?

Additional Information: The lock type is DML and the lock mode is: Row Exclusive, the table is locked and the program is web oracle forms executing.

I am executing the query in Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

How to accomplish getting the correct rowid? Below is the selection criteria I have:

select vs.inst_id,
vs.audsid audsid,
locks.sid sid,

[Code]....

View 1 Replies View Related

Security :: PL/SQL Compilation Session

Oct 18, 2010

Is there any way to identify who has compiled a specific PL / SQL stored program?

View 3 Replies View Related

Security :: How To Know Which Session Causing TX Lock

Feb 28, 2011

I have to find out the cause of a lock where a particular session with some serial no is causing a TX level lock for certain duration.

View 17 Replies View Related

Security :: Grant To Select Session Details

Mar 13, 2006

Developers they use Toad and in that they want to use session browser option so that they can see the SQL running and open Cursor,other session specific details...So what grant/ permissions can be given to them so that the Schema they logged on can access those information.

View 4 Replies View Related

Security :: How To Configure End User Open Only One Session

Jun 10, 2011

I want to configure this

my end user open only one session

means one session per one user only

View 8 Replies View Related

SQL & PL/SQL :: Security - Grant Roles To Session On Time Of Connection?

Jan 18, 2012

When I use Forms_ddl('set role My_Role IDENTIFIED BY PWD'); in form's "When-New-From-Instance" to grant a role to a user.

What should I do for the same for a report?

Is there any way to grant roles to a session on the time of connection?

View 6 Replies View Related

Not Able To Find Blocking Session

Jan 10, 2013

I am working on oracle 11g and AIX.We were facing some blocking issues.For more investigation on the blocking sesssion we checked active session history of the database.Like session 'A' was blocked by session 'B' ...Now if we check infromation about sessions 'A' it is showing that this session was blocked by session 'B'.

But when we checked for session 'B' informaiton ,it is not showing any thing...from where we can get infromation about session B and in what all scenarios active session history does not show information about blocking session....

View 7 Replies View Related

Security :: How To Restrict Access To Table (Database Vault)

Nov 12, 2012

I configured a simple security configuration for the HR sample database schema. URL....

Now system user can't select data from table hr.employee, but HR user still can. How to restrict access to table for table owner?

View 1 Replies View Related

How To Find CPU Usage By Each User Session

Sep 7, 2011

I can get "CPU used by this session' value from v$sesstat. That is the value in centiseconds used by each session. How to convert the value into a percentage of total CPU resource ?

Or there are some other ways to gain the value. i.e. Percentage of Total CPU usage for each Oracle session.

View 5 Replies View Related

Find Thread That Opened A Session In AIX

Sep 3, 2012

As stated in "Determining which Oracle database session is being used by a specific Siebel user session [ID 863771.1]":

On the Oracle database side, you can query the V$SESSION table (make sure you are logged in as the tableowner) using the following WHERE clause format:

WHERE PROCESS='XXXX:YYYY'

XXXX = Server PID
YYYY = Thread ID

So querying v$session, sessions opened by windows clients have in the PROCESS column values like "12345:876", where 12345=process id and 876=thread id.

Our application servers are installed in AIX, which is also multithreaded, but in the PROCESS column only process id is stored. Is there any way to find thread id?

View 17 Replies View Related

SQL & PL/SQL :: Find Out IP Address For All Currently Connected Users In V$session

Apr 6, 2010

I need a query to find out IP Adress for all currently connected users in V$session.

View 7 Replies View Related

Server Administration :: Find SID From Current Session As It Make Connection With DB

Dec 27, 2011

How to find the sid from v$session of my current session.

I want to ask:

Let say I have open 10 TOAD session and only in one toad session i have fired a sql query ( the rest 9 session are just blank), then the rest 9 status are in active status. Now, how i can figure out which sid ( from v$session ) belongs to which session as all the 9 session having nothing to run.

Quote: My main intention is to find out the current session sid as soon as it make connection with DB.

View 7 Replies View Related

Server Administration :: Query To Find Hostname For Oracle Session

May 20, 2010

How I can find the hostname for an oracle session id?

View 4 Replies View Related

SQL & PL/SQL :: Find Out Column Value In All Table In Whole Database

May 19, 2010

I need the column name and table name which is having the particular value in the whole database.

E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.

View 14 Replies View Related

Performance Tuning :: How To Find Out Session Consuming High Resource In Past

Mar 28, 2013

How can I find out the particular oracle session which was consuming high memory in the past?

I can't get the data in v$sessstat
Unable to get the information in AWR

dba_hist_active_session_history do not have field which indicate memory related information
Shall I concetrate on EVENT in dba_hist_active_session_history which continuosly had sort, direct path read
Or
Locate sql_id from dba_hist_sqlstat with high SORTS_DELTA for snapshots belonging to problematic time period and then using the sql_id query dba_hist_active_session_history

which approach I shall take to find out the session which consumed most memory in the past?

View 8 Replies View Related

SQL & PL/SQL :: How To Find Size Of View Or Table In Database

Jul 10, 2010

How can we find the size of a view or synonym or table in a database. What is the code.

View 4 Replies View Related

Security :: To Find All Failed Logins Through Audit Report

Feb 9, 2011

I have to find all the 'failed log ins' through audit report. then it has to be uploaded to a table. The script, either in windows or unix should be reusable and can read files one by one.

View 13 Replies View Related

Security :: How To Find Out Date View Status Changed To Invalid

Mar 10, 2011

We are trying to audit a view. We are currently seeing that view in production instance and trying to find out when exactly it got invalidated.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved