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_
View 6 Replies
ADVERTISEMENT
Apr 4, 2012
dbo is a table with 1M records and structure similar to dba_objects
The following queries which does FTS, access same number of blocks as observed in v$session_longops
select * from dbo;
and
select * from dbo where object_type='VIEW';
Ex.
select * from dbo where object_type='VIEW';
select * from table(dbms_xplan.display_CURSOR(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
SQL_ID 5mh418w9stax2, child number 0
-------------------------------------
select * from dbo where object_type in('VIEW')
Plan hash value: 2675347415
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
|* 1 | TABLE ACCESS FULL| DBO | 77611 |
-------------------------------------------
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
Plan hash value: 1423969929
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| DBO | 1007K|
--------------------------------------------
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
View 6 Replies
View Related
Mar 21, 2012
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.
View 2 Replies
View Related
Jan 16, 2013
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.
View 1 Replies
View Related
Jul 8, 2010
I am oracle DBA (trainee) new in this field.
Some time my temporary tablespace size suddenly increase from 1gb to 5-6gb why this happened ? I want only reason why it suddenly increase?
View 6 Replies
View Related
Mar 11, 2010
I have data Block with many Items, text item content disappear after when mouse down trigger fire on text item in the same block!!!!!!!!!
View 1 Replies
View Related
Apr 15, 2010
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
4- I modify the "FORMSWEB.CFG" as follow:
archive_jini=icons.jar,f90all_jinit.jar;
imageBase=codeBase
4- In "Registry.dat" I added the flowing line:
default.icons.iconpath= c:icons
5- I modify the "orion-web.xml" .as follow:
<virtual-directory virtual-path="/html"
real-path="D:Dev9i/tools/web90/html" />
is modified to :
<virtual-directory virtual-path="/ICONS"
real-path="C:ICONS" />
6- In the property palette for the icon item and in the Icon Filename I put just the icon name called "w_prev" Without any extension , any path...
View 34 Replies
View Related
Jan 25, 2011
I have attached the PDF file contains the stacked canvas disappear problem.
View 9 Replies
View Related
Jan 30, 2012
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
:PLACE_FROM:=:parameter.mc_name;
:SRECEIVER_NAME:=:parameter.USER_NAME;
View 5 Replies
View Related
Feb 15, 2010
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.
View 7 Replies
View Related
Mar 14, 2012
We a running oracle9i database and forms 6i application.
We have 4 gb ram size and sized the sga for 700mb pga for 400mb for past 4 months there is no problem.
Now from last week we encounter the server hang, and we do restart. How to check reason for hanging.what are all the check list to carried out.
View 19 Replies
View Related
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
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
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
Jun 15, 2012
explain v$session and gv$session ;
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
View Related
Oct 18, 2010
Is there any way to identify who has compiled a specific PL / SQL stored program?
View 3 Replies
View Related
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
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
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
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
