Reason For SID To Suddenly Disappear From Session Longops
Jul 31, 2012
I'm working on online reorganization of a huge table (1TB) using DBMS_REDEFINITION. It's been running fine over 2 days and is about 85% complete(Based on TOAD, Long ops tab from session browser) before % status disappeared from TOAD and no entry in V$SESSION_LONGOPS. However, I see SID is active and also able to see the current running statement.SID appears active in v$session and no entry in session longops.
It doesn't sound normal, what could be reason for SID to suddenly disappear from session longops? Below is the current running statement captured from TOAD.. session browser. From sqlplus, DBMS_REDEFINITION.START_REDEF_TABLE is the procedure running.
/* Formatted on 7/31/2012 11:37:20 PM (QP5 v5.163.1008.3004) */
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */
INTO "SCHEMA1"."SCHEMA1_TABLE_
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("OBJECT_TYPE"='VIEW')
I understand that because the query is accessing large number of blocks which exceeds 6 seconds threshold, the session appears in v$session_longops And this has nothing to do with the time spent in displaying the records on the screen
Now, why the following query does not appear in v$session_longops?
select /*+ full(dbo) */ count(*) from dbo;
select /*+ full(dbo) */ count(*) from dbo;
COUNT(*) ---------- 1006525
Elapsed: 00:00:01.36 dv3_erie-dev_08 >select * from table(dbms_xplan.display_CURSOR(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------ SQL_ID 4mgjwp3tv70db, child number 0 ------------------------------------- select /*+ full(dbo) */ count(*) from dbo
The table has all nullable columns and thus I assume the index on ID can't be used for this COUNT Thus ideally it shall be accessing same number of blocks and thus shall appear in v$session_longops
Im not able to open the oracle reports 10g under Oracle Developer Suite Using win 7 enterprise version It gives me no error but unable to see the reports opening.It is running in the task manager but it is not opening.
I'm currently having this kind of issue whenever I click the 'Finish Enrolling' that dialog box appears. what kind of error is this and what was the cause of this error.
I develop a form using Devloper9i AS, oracle 9i, Winxp. The problem is: I used icon button at design view ".fmb" but at run time this icon disappears in the file ".fmx" After reading some topics covered this problem in this forum; The following steps are executed:
1-In regedit I put UI_ICON = c:icon --where my gif icon file is located
2-At command prompts i type: C:>jar -cvf icons.jar c:icons Then "ICONS.JAR" file is created on D:Dev9ijdkin
3- I Copy the file "ICONS.JAR" and past into D:Dev9iforms90java
display value from login page to call another form first time value is appear when I enter new 2nd record that the time call value disappear.I means user name fix it when i create new record.
this is my code for pre-insert trigger block level
Something strange happens when i give execute query in a form (Oracle Forms 10g.). On executing query, one field disappeared but when i clicked on the location of the field it appeared again. I am not sure if this has something to do with visual attributes/display properties. This is happening only after migration to 10g.
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.
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?
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?
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.
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....
