Two Blocked Session - Not Getting Sqltext

Nov 24, 2011

I have two blocked session

select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;

BLOCKING
--------------------------------------------------------------------------------------------------
SID 142 is blocking 241

SQL> select sid,SERIAL# from v$session where sid in (142,241);
SID SERIAL#
---------- ----------
142 40643
241 16500

Now if I am trying to find what exactly getting executed under those two sid I get info for only one.

SQL> select a.SQL_ID,a.SQL_TEXT from v$sqltext a, v$session b where a.ADDRESS=b.SQL_ADDRESS and a.HASH_VALUE=b.SQL_HASH_VALUE and b.sid=241;
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------
d11mmj6wj4046 DELETE FROM OTABATCHEPTTABLE WHERE EPT_ID=0001471142180000002

BUT FOR OTHER SID I DONT HAVE ANY ENTRY IN SQLTEXT

SQL> select a.SQL_ID,a.SQL_TEXT from v$sqltext a, v$session b where a.ADDRESS=b.SQL_ADDRESS and a.HASH_VALUE=b.SQL_HASH_VALUE and b.sid=142;
no rows selected

FIND OUT WHAT EXACTLY ITS RUNNING AND WHY I AM NOT GETTING THE SQLTEXT FOR A SID?

View 3 Replies


ADVERTISEMENT

Performance Tuning :: Any Way To Have DB Articulate Why PDML Blocked

May 10, 2013

Can I have the database show why it cannot perform parallel DML (update) to a given object? I've checked the basics in accordance with the documentation i.e. triggers/partitions/self referencing FKs etc - I'd like to say I know what I'm doing with this but this eludes me.

The only vague reference in the documentation is "replication is not supported" but there is no expansion to this. I was hoping to find out what I'm missing that is blocking the PDML. I have tried a 10053 trace to check the hint section - but of course the hint is accepted anyway as even if the PDML cant be done, it still parallels the underlying object scan .

I CAN take a copy of the DDL and get PDML to work on that copy - my only suspect, really, is golden gate replication blocking it. You may think this is covered by "replication is not supported" but in my brief foray into that, I was pretty sure it sat well "under" the optimizer so I'd have been surprised (though not shocked) if that was the culprit.

View 10 Replies View Related

SQL & PL/SQL :: Entire Insert Script Row Will Be Inserted Into Cbxrdlog Table Sqltext Column

Aug 24, 2011

I have 2 tables. Cbxrd and Cbxrdlog. If the Cbxrd table having creation date column. when it inserts the row. if the column is null value. then the entire insert script row will be inserted into Cbxrdlog table sqltext column.

i have attached the trigger script. when i execute the table but i shows error like

"Ora-0756 Quoted String not properly terminated".
"Ora-04098: trigger os_wm_sit_owner.cbx trigger is invalid and failed revalidation.

View 5 Replies View Related

Application Express :: Kill Current Session When Another Session With Same User Starts

Nov 19, 2012

I have an urgent requirement to kill an existing session if a new session starts for the same user. I have been reading lot of blogs and posts on the above topic, but could clearly tell me how to do it.

I thought of putting a process in 101 page when login button is pressed to catch this and kill the old session.

View 7 Replies View Related

Application Express :: (X) Process Taking 60% CPU In Two Session (30% Each Session)

Oct 23, 2012

We are using Apex 4.0/Oracle 10gR2/ Hp-Ux, We noticed that there is a process Running in Two Sessions that seems running since 3 Hours using 60% CPU,** 30% given below sql it executes in two Sessions!

declare function x return boolean is begin -- if instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY')>0 then
declare
l_position number := 0;
begin
loop
l_position := instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) ;
exit when instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) = 0 ;

[code]....

View 4 Replies View Related

Grant Select On V$session And Gv$session Directly?

Aug 28, 2013

Instead of giving permission directly to v$session and gv$session to a developer, Is there any other way to do the same?

View 3 Replies View Related

V$session And GV$session?

Jun 15, 2012

explain v$session and gv$session ;

View 3 Replies View Related

V$session And Jdbc?

Dec 23, 2009

I am connecting to Oracle 9i using JDBC in my JSP/Servlets web pages. I am closing all my connections in Finally blocks.

Everything works great during the day with JDBC and Oracle.But after a Web crawler runs through our web server at 3 AM the next morning a v$session check shows several lines of JDBC Thin Client inactive status. find exactly what sql statement or program that could be causing the JDBC Thin Client to show up?

View 1 Replies View Related

SQL & PL/SQL :: Nls-session-parameters?

Sep 26, 2011

I just upgraded my version of my oracle from 9 to 11.2g.When I tried to execute some queries in PL/SQL i keep getting an
annoying error : "ORA-01843 not a valid month."

I realized that if I execute the following query:alter session set NLS_DATE_LANGUAGE=AMERICAN;before every query that I want to execute, then it's executed fine.

I preformed the next queries:
1. select * from nls_database_parameters;
2. select * from nls_instance_parameters;
3. select * from nls_session_parameters;

and I saw that the fields in the "nls_session_parameters" vieware not derived from "nls_database_parameters" "nls_instance_parameters".

How can I derive the parameters from the instancedatabase view into the session view?I want that the "nls_date_language" parameter will always be 'AMERICAN'.

View 3 Replies View Related

Sql Trace Session

Aug 28, 2012

i read some articals of how to trace oracle sessions and i have some question

i use database 10g

trace vs event ??? different between them ????

how to trace individual statement i hope to give me this point in ex: due to i can't get the sql_id ,how to know the sql_is of specify statement??

View 6 Replies View Related

PL/SQL :: How To Lock Session

Jul 18, 2013

i have application where user logs and perform manipulation operation such as insert and update on database table.some time its possible that same user login twice, i just want to hold one sessionĀ  until and unless dml operation perform by another session gets complete.how to accomplish this in database levelĀ .

View 6 Replies View Related

SQL & PL/SQL :: Creating A New Session

Mar 14, 2013

How to create a new session from a pl/sql. My requirement is that i will package which should run on new session and execute it.

BEGIN

A; --Calling package
B; --Calling package and this should run on new session.
C; --Calling Package
end;

Now when I will run the this plsql block, then A,C should execute will run in this package only but B will run separate session.Whole idea to have better performance. I heard we can use PRGMA AUTONOMOUS_TRANSACTION, but this is not working.

View 3 Replies View Related

Selecting From Two Tables - How To Get Value From Session

Jan 31, 2013

I have tables SUBJECT(subject_id, name, number) and PS(ps_id, subject_id, student_id). I need to select all from SUBJECT,subject_id and student_id from SP, joined by subject_id, where student_id needs to be read from session. I'm using asp.net with oracle database. How to get the value from the session.

View 2 Replies View Related

Session Details For A User

Nov 22, 2010

I have a user in my oracle database, I would like to know details such as, how many times and by using which tools same user has got logged for past one week.

How do I do that. I have sql which shows the current session either he is logged in, if so then which application or not logged in. But I required the information for past one week How do I get those details.

View 1 Replies View Related

Getting Different Values For Session Report

Sep 3, 2010

query for number of users connected to the database:

select count(*) "Number of users Connected" from v$session where type='USER'; it produces 125

The following query is used to describe where and when the users are connected to the database.

set pages 500
set linesize 750
column box format a20
column username format a7
column program format a20
column os_user format a20
[code]...

It should produce 125. But its showing only 119 users. so around 6 users missing in the list. find why 6 users are not listed..

View 6 Replies View Related

Alter Session On NLS_DATE_FORMAT Being Ignored

Feb 24, 2011

I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch process right after the connection takes place. The format I specify is YYYYMMDDHH24MISS, this is the format used all over the process. In my development environment this works perfectly, but I've had problems in other environments.

CASE A development environment: The process works fine, $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set.

CASE B Test envirnonment 1: The process failed.$NLS_LANG=American_America.WE8ISO8859P1 $NLS_DATE_FORMAT environment variable is not set.For some reason the $NLS_LANG variable seems to have more weight than the alter session command.. why? The process works fine after setting $NLS_DATE_FORMAT to the desired format.

CASE C Test envirnonment 2: The process failed. $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set. Can't get it to work here. why?

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

SQL & PL/SQL :: Restricting Form To Only One Session?

Feb 6, 2012

1. i want that if a form is opened in one user session. it will not be available to other users.

2. if a user executes a procedure. it should not be available to other users to execute until the first execution completes.

View 12 Replies View Related

SQL & PL/SQL :: Finding Session IDs Initiated By Particular Job

Feb 7, 2012

I want to track the session id that has been initiated by a particular job (I have job ids with me). Which dictionary views I can get this result.

View 1 Replies View Related

SQL & PL/SQL :: User Session Details

Sep 14, 2010

How can I find out a user session/connection details like ( his last connection time, his activity etc)

Here is the issue, a old dba_user_id who physically no longer exists is not removed from the database. At some time, a session is opened under his user_id and some activity runs and closes the session. So, checked scheduled jobs under him and disabled them. But not sure whether the scheduled jobs are creating a session under his user_id or some thing else.

I checked in v$session, but since I am not sure at what time his session opens, so I am not able to get his session details.

View 5 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

PL/SQL :: Queries Executed By A Session

Jul 11, 2012

I am trying to trace the SQL statements executed against a particular database user. I don't have AUDITING enabled and I am using Oracle 11g.I have the following query :

SELECT
S.MODULE,
SQL_TEXT ,
S.EXECUTIONS
FROM
[code].....

But if multiple users running the 'APP.EXE' are connected to the same db user, I am not able to understand which OS user executed which query. So I tried to join with V$SESSION view to get the user details.

SELECT
S.MODULE,SQL_TEXT ,SN.OSUSER, SN.MACHINE, S.EXECUTIONS
FROM
SYS.V_$SQL S,
SYS.ALL_USERS U,
V$SESSION SN
WHERE
S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('USERNAME')
AND (UPPER(S.MODULE)='APP.EXE')
AND S.SQL_ID=SN.SQL_ID
ORDER BY S.LAST_LOAD_TIME

But this doesn't seems to be working(In my case it didn't return any rows) I have also tried the following

select S.SQL_TEXT,S.MODULE,S.EXECUTIONS,SN.OSUSER,SN.MACHINE,SN.SID, U.username,U.user_id from
SYS.V_$SQL S, v$open_cursor oc,SYS.ALL_USERS U,V$SESSION SN
where S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('USERNAME')
AND (UPPER(S.MODULE)='APP.EXE')
and oc.SQL_ID=s.SQL_ID AND SN.SID=OC.SID AND SN.SADDR=OC.SADDR;

but I am not sure whether this is giving the right results. So, I have the following questions

1) How do I get the queries executed by each session?

2) The EXECUTIONS column of V_$SQL seems to the executions from all the sessions. How do I know the number of times a particular query is executed by a session?

3) How long a record about a query will be stored in V_$SQL? When do Oracle delete it from the view?

View 5 Replies View Related

Session Hang Due To Foreign Key?

Jun 21, 2012

Version 10.2.0.4

CREATE TABLE T1 (I INT PRIMARY KEY);

CREATE TABLE T2 (I INT PRIMARY KEY, J INT);

ALTER TABLE T2 ADD CONSTRAINT T2_FK FOREIGN KEY (J) REFERENCES T1(I);Session 1

INSERT INTO T1 VALUES (1);Session 2

INSERT INTO T2 VALUES (1,1);Session 2 hangs. Why ?

View 9 Replies View Related

Configuration Duplicate Session ID

Oct 29, 2012

I am getting a duplicate SID (ABC1 (2)) on UNIX. I have checked all the possibilities how to remove it but unable to find it. As you you see below:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORATAB:
================================================
ABC1:/oraclehome/oracle10g:Y
================================================
/oraclehome/oracle10g/bin/tnslsnr LISTENER_ABC1 -inherit
================================================

SID_LIST_LISTENFCATDR =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oraclehome/oracle10g)

[Code]...

Result:

ls -lad /orahome/ora10g/dbs/initABC1 (2).ora

ls -lad /orahome/ora10g/dbs/initABC1 (2).ora ; echo ;
/bin/sh: 0403-057 Syntax error: `(' is not expected.
--------------------------------------------------------------------------------------------------------------

what to need to do to avoid this duplication? Is it something wrong with Oracle Listener.ora as it have two listeners (ABC and ABC1) that might be causing duplication?

View 8 Replies View Related

Oracle 11g R2 - Session ID For Database

Dec 1, 2012

I am installing oracle 11g R2 (Oracle Database 11g Release 11.2.0.3.0 - 64bit Production) on linux box, by the time the SID provided was 9 characters like oimdbdevd. The completion of installation showing as oimdbdevd for sid. But the init.ora file and database table v$database are showing only oimdbdev.

It mean the SID will allow only 8 characters.

View 3 Replies View Related

Server Administration :: Get Rid Of A Session?

Jun 9, 2010

we have a software which keeps a check on v$session table so that a client can not have more than one sessions running simultaneously with the server. But if a client program terminates unexpectedl, it's session information stays in the v$session table, which prevents us from opening a new instance of the client program. Using

ALTER SYSTEM KILL SESSION 'SID, SERIAL#'

doesn't work since it takes ages to rollback and release the locks. And the session stays there with a 'KILLED' status.I want this session out of the v$session table and instantly.

View 4 Replies View Related

Session Inactive State?

Jul 1, 2012

want to know the following

1. when the oracle session changed from active to inactive?
2. what is the time for active session?
3. session is changed to inactive from active. but it is still showing in v$session.
4. in v$session, can i see the ipaddress of client machine ?

View 6 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

How To Obtain User Session Info

Jan 26, 2007

How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.

View 8 Replies View Related

Unable To Kill Session Using Command?

Oct 22, 2010

I killed the session using the command like :

alter system kill session '454,48158';

but again it is showing the session in v$session view. How can i kill the session.

View 1 Replies View Related







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