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....
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 want to create notification/ or triggered mail to my client and my dba group which will occur while session and user has been blocked. I want to create this procedure in 12c OEM.
One issue happens frequently in My database,Logwr process blocking other session,when i checked blocked session in v$session wait_class was commit and event was logfile sync,
As per this we predicate Logbuffer Might be full so i just to reboot server,Note server was 32 Bit i can allocate only 1GB of SGA SYStem not allows me to increase SGA further. Server reboot is proper solution?
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.
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 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 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.
I am using Oracle Application Server 10gAs and Linux 4.
Our Test Application is access by users using 7778 port,i want just few users to access my test application(just admin user) and all other user should not get access to the application,even if they specify proper URL and port number.
Some access control policy using oracle application server 10gas.
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 using oracle 10g as server in my lab. I faced some problems initially, but later after increasing the USERS tablespace it is working fine.
But there is still one problem. During the query execution some queries will be blocked and it doesn't leave any consequent queries to execute from the same user.
The blocked sessions will be displayed in the admin page under blocking sessions link. There is a option to kill the session. But when i do that, it affects all the users and the connection will be lost to all the users. again I have startup the database from beginning.
The TM lock that occurred 3 times appears to be disastrous.Historically, this could be caused by missing indexes on foreign key columns.
How can I be alerted when this event occurs, so that I can do some real-time investigation into the sessions and the SQL that hit it? I suppose I could schedule a job to query v$lock/dba_waiters/dba_blockers every few minutes, but is there a better way? Any standard edition scripts for this?
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') 2 || ' User '||s1.username || '@' || s1.machine 3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text ||' is blocking the SQL statement on '|| s2.username || '@' 4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> ' 6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 , 7 v$session s2,v$sql sqlt1, v$sql sqlt2 8 WHERE s1.sid =l1.sid 9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1 10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2; [code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
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.
