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?
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....
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.
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
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.
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]....
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.
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?
i want to open a new window from my form. everything going well but when a new window opened. the control remains on previous form. and the new window is also inactive. i tried go_item with the item belongs to the new canvas but in vain.
i'm not able to open layout in the custom form.automatically it will be closing and giving this error. Oracle Forms Designer has encountered a problem and needs to close.
At once when I open the form when I press F11 it is not entering query mode. But if I type something in the text box placed in the form then I press F11 means it is asking "Do you want save the changes you have made?" if I press yes or no, no matter it is going for query mode. How to go to query mode without typing something in it?
GROUP# THREAD# MEMBER ARCHIVED STATUS MB 1 1 +DATA01/mydb/redolog_group1_member1 NO INACTIVE 1536 1 1 +DATA01/mydb/redolog_group1_member2 NO INACTIVE 1536 2 1 +DATA01/mydb/redolog_group2_member2 NO CURRENT 1536 2 1 +DATA01/mydb/redolog_group2_member1 NO CURRENT 1536 3 1 +DATA01/mydb/redolog_group3_member2 NO INACTIVE 1536 3 1 +DATA01/mydb/redolog_group3_member1 NO INACTIVE 1536Tue Jul 10 18:37:48 2012 [code]....
Could an increase on the redo log file sizes solve this problem? Unfortunately I do not have a test platform, where I could have tested this.
I encountered error ORA-00338 while performing incomplete database recovery:
ORA-00283: recovery session canceled due to errors ORA-00338: log 3 of thread 1 is more recent than control file ORA-00312: online log 3 thread 1: '+DG_REDO/dbase/onlinelog/group_3.267.814820795' Recovery Manager complete.
I performed this recovery on oracle 10g, ASM database.
1. What are possible causes behind this error ORA-00338 ?
2. This error occurs at the start of media recovery, while RMAN is applying archived logs. So does RMAN apply "online REDO" logs after applying "archived" logs required for recovery or before them ?
3. How does rman come to know that online log 3 is more recent than control file ? How can I get scn of online log & control file in my environment ?
Following are few details related to checkpoint scn/change# info. I found in my environment..Before recovery:
differences of enabling thread as public or private?
lets assume that i have two threads (1, 2) for two instances (1, 2) and each of thread consists of 2 redolog groups - minimum configuration.in case i need add third node i need third thread.
what will be difference if i set this thread as public instead private?
While starting up my database i am getting this error.by mistake i had moved log files, and after some time again i inserted those log files again into same directory i am getting this error.
When i open the primary database in my dataguard env,it raise error?
SQL> alter database open; alter database open * ERROR at line 1: ORA-00314: log 1 of thread 1, expected sequence# 77 doesn't match 0 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/oracl/redo01.log'
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.
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 ;
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?
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'.
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 .
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.
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.
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.
