My disk is completely full so i decided to drop temporary tablespace and create a new one but unfortunately the query to drop temp tablespace hang in between to whic I come to know that there were some sessions which were using it for sort purpose.
check out the queries below with results:
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
2 a.username,a.osuser, a.status
3 FROM v$session a,v$sort_usage b
4 WHERE a.saddr = b.session_addr;
[code]....
Was able to killed three of the four sessions but not the last one.
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 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.
I would like to log session activities. I would also like to be able to check if the session for which an activity has been logged is still active. The problem is, what do I need to uniquely identify a session?
DBMS_SESSION.UNIQUE_SESSION_ID - is explained as: unique for all sessions currently connected to this database. Meaning that if I restart the DB or connections are closed and opened, it might not be unique anymore, right?
SYS_CONTEXT('userenv','sessionid') - returns AUDSID, but is this guarantied to be unique and not been reused, forever? What I do not want to happen is, that I use a session identifier, which might reappear.
My best guess is currently: SYS_CONTEXT('userenv','sessionid') + logon_time (from V$SESSION)
This combination should be really unique and should never reappear, right?
Can I extract data from our Oracle server regarding the no. of sessions per day? But the problem is that our Boss wants a previous dates from April 11-17, 2011.
when I was analyzing high CPU utilization issue, I saw that the most of the top PID's were INACTIVE in database. But it was utilizing more than 4% CPU. how it is utilizing CPU without doing any work in database?
One user complained that, his session is closing automatically when his session exceeds exactly 01 hour 1 minute.
default profile is assigned for this user. I checked the idle_time value of this default profile. it is UNLIMITED only. What else I need to check to find out is there any timeout parameter has been set or not?
I am using TOAD to monitor user sessions. I have a user who uses nearly 80% of Overall resources in the mornings. He calls me daily and say that his work is done. But even then the TOAD shows that he is using same resources for some time.
My question is can i kill the session once he confirms his work is done OR do i need to wait till the redologs are cleared.
I can detect the CURRENT_SCHEMA with a query against the user env context, but I can't find any of the others there. Could there be an issue with these values being stored in PGA, and therefore not visible though any regular views? I did find an article that showed a query against an x$ data structure which showed something for different settings of CONSTRAINTS, but I can't find it again.
I am using 10.2.0.4 oracle database on Solaris 10. We are running some Peoplesoft upgrade on one of the database from last 6-7 hours. The status of the session is INACTIVE from couple of hours. And the sql_id is null for these sessions.
select sid,serial#,STATUS,to_char(logon_time,'DD-MON_YY HH24:MI:SS') from v$session where username like '%&usern%' ORDER BY 4 DESC; 533 2234 INACTIVE 04-MAR_10 01:59:02 525 5919 ACTIVE 04-MAR_10 01:54:50 534 4186 INACTIVE 04-MAR_10 01:05:15 520 2320 INACTIVE 04-MAR_10 00:29:44
[code].....
While when i see the current sql from the v$active_session_history, it keep on changing after every 10-15 minutes. Using below query to find the sql_text for the session from v$active_session_history.
select sql_id, sql_text from v$sql where sql_id in ( select sql_id from v$active_session_history where session_id=511
[code].....
We have not faced issue like this during Peoplesoft upgrade. what could be the reason that the status is not changing while the base sqls keep on changing after every 10 minutes.
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?
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.
SQL> SELECT COUNT(*) FROM v$parameter WHERE value IS NOT NULL; COUNT(*) ---------- 246 SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; COUNT(*) ---------- 20
v$parameter displays information about the initialization parameters that are currently in effect for the session,but v$spparameter displays information about the contents of the spfile.I want to know why the count of parameters whose values are not null in v$parameter is greater.where are they from? My initialization parameter file is spfile.
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 ;
so when i changed my idle_time value in profile and the when the user exceeds the idle_time value the user still i can see in-active state in v$session.
and when i was tried to execute any query on inactive session that time i got an ORA- error and then session was not visible from v$session.
in my environment inactive session was not getting used afterwards so is there any way to kill that sessions automatically once reached idle_timeout value.
lets say i have 50 max sessions, out of which 10 are inactive state and 40 in active state .what if i created one more sessions will that give me ORA- error stating max sessions reached or it will kill the one session which are in-active state.
its showing report output in a new window, but problem is that if i copy the url, then close current report browser window, start new IE session and paste the copied url it showing the same report... but i don't want to allow this and want to Kill the current job id ( while it displaying the same report ), how i can do this?
