Share a query for inactive sessions consuming memory? I have found many inactive sessions with the following query and would like to join this information with memory usage as well.
select username, machine, program, to_char(LOGON_TIME, 'hh24:mi:ss DD-MM-YYYY'), LAST_CALL_ET/60, status from gv$session where status='INACTIVE';
In our databace there are around 300 sessions were in inactive state.
Database is not releas the sessions.
our db shows more than 200 INACTIVE Sessions ; and the DBA plans to reboot the db to get rid of these sessions . Can we not KILL these sessions and avoid the reboot ?
View 4 Replies View RelatedI am using Oracle 11g On windows 2003 Server, and in the task manager, tnslsnr.exe comsuming approx 1 GB of RAM. After restarting the listener it came to some mb(20-30). but Again after few days it raies and consume lots of memory..
View 11 Replies View RelatedWe recently migrated from 9i to 11g.
One UPDATE query runs for 2min and it opens multiple oracle sessions. Almost 40 sessions. Once the UPDATE is done, the sessions close.
When monitoring V$SESSION, found that the newly opened sessions have a PROGRAM value like "oracle@server (P001)"
The UPDATE query is as below. The query works fine, no issue with performance.
SET t1.text =
( SELECT text
FROM table2 t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND ROWNUM < 2
[code].......
I am using an OrdImage type to allow me to rotate a photo and I understand the larger the photo the more time it will take to rotate. However I was wondering if there was a way I could perhaps speed up the process? Currently a photo of 1meg takes approx 27 seconds to rotate, for one about 400k it seems o take about 7 seconds. To me these seem like quite long periods of time for the rotation, though maybe thats how long they take?
I am using oracle 10g and the process I am using for the rotation is: source_ord_image.process('rotate "90"');
I would like to have a query which should fetch previous day records from column which is having timestamp data type.
select mdn from user_table where updatetimestamp > trunc(sysdate) - INTErVAL '24' HOUR;
But this gives output not for previous day, but all records which are 24 hrs less than current day. How to get records for previous day based on column having timestamp data type.
I read and tried a lot, but I can't consume a UTF-8-Webservice correct in a 11gR2-DB: German "Umlaute" like ü are displayed/written as ü.
The service sends all correct. (tested with soap-ui 4.5.1)
The DB-NLS-Parameters are:
SQL> select * from NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
------------------------------ --------------------------
NLS_LANGUAGE GERMAN
NLS_TERRITORY GERMANY
[code]...
I consume Service with that skript:
DECLARE
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_buffer_size NUMBER (10) := 512;
l_line_size NUMBER (10) := 50;
l_lines_count NUMBER (10) := 20;
[code]....
What can I do, to get the response correct in NLS_CHARACTERSET WE8MSWIN1252.I think, it must be possible to convert the response, but I didn't found the correct solution.And I don't understand, why the body_charset is ever "ISO-8859-1" (commented in procedure). Settings utl_http.body_charset is without effect.
I have table which has 240 columns.
Here is the list of column data type.
VARCHAR2(50)
TIMESTAMP(6)
VARCHAR2(25)
VARCHAR2(25)
NUMBER(15,2)
VARCHAR2(50)
NUMBER(1)
[code]....
The table has 64 partition.. When i count the table(select count(*) from table), i see close to 22 million records.
SQL> select (num_rows*avg_row_len)/1024/1024/1024 GB,num_rows,avg_row_len
2 from dba_tables
3 where table_name='TRX_TAB';
GB NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
74.9393936 21871585 3679
SQL>
It is supposed to take 75 GB. But this table is consuming 135 GB now. It is occupying 8 GB per day.
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 the following setup
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3G
sga_target big integer 2G
from what I read I beleive this will initially grab 2GB of memory on startup and will grab up to to 3GB of memory total for the SGA. The "total" memory can be allocated to different peices of the SGA when needed but will never exceed 3GB. Is this correct or would these settings infringe on any available memory on a system that is already tight on memory?
Secondly, what happens if both these values are set to the same value?
Can i know the internal process of initialization of DB into memory in timesten , when a new connection is establishing? Will timesten create tables and indexes in RAM when first connection is established if the RAM policy is default?
want to know the internal functional flow of timesten when any command is fired against it.
want to know the following
1. when the oracle session changed from active to inactive?
2. what is the time for active session?
3. session is changed to inactive from active. but it is still showing in v$session.
4. in v$session, can i see the ipaddress of client machine ?
i was gone through the below link
[URL]
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.
i have a form having 5 listitems for making selections. once selecting through listitems i am adding this selection into below datablock by a ok button and saving it into database by a ok button. as i made one selection and press ok buttton it's working fine but after this when i want one more selection through listitems, listitems becomes inactive. it's not working after one selection. my coding on ok button is like this:
begin
select distinct lcode intolcode_ss from vac_view where locality=:vac_view.locality;
select distinct hid intohid_ss from vac_clr_vu where vr_no=:vac_view.vr_no;
:prop_temp.vr_no :=:vac_view.vr_no;
:prop_temp.qtr_type :=:vac_view.qtr_type;
:prop_temp.locality :=:vac_view.locality;
[code]....
may be it's all because of clear_list('vac_view.locality'); but i have to put this as i want to make clear all listitems after making each selections.
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?
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
24013 oracle 2167M 1581M sleep 53 0 0:01:53 20% oracle/1
13260 oracle 2167M 1580M sleep 30 0 0:01:16 14% oracle/1
24399 oracle 2167M 1582M sleep 59 0 0:03:01 4.2% oracle/1
11509 oracle 2178M 1593M sleep 59 0 0:02:10 2.8% oracle/11
[code].....
how can we change the color of active & inactive Tab canvas dynamically. I am using Dev Suite 10gR2
View 2 Replies View RelatedI 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.
I am trying use host command in Oracle forms. I get the process id as input from the user and on clicking ok the form should kill the session related to process id.
PS: Users will be entering only frmweb.exe process id which are inactive.
cmd := 'CMD /C taskkill /F /FI /pid 'process which is got as input' /IM frmweb.exe'; host(cmd)I also tried by writing the above command into a .bat file in application server.When trying to execute the bat file it din work. But when tryin to run the bat file by double clicking in Application server the session was killed.
On executing the above I am unable to kill the process. I would also like to know whether the host command was successful or not. Orakill and alter session are working but I don want to use it.
Is their any query to find cpu usage & memory usage of all the queries currently running on DB?
View 5 Replies View RelatedI have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).
View 7 Replies View RelatedI have a confusion with MEMORY_TARGET and MEMORY_MAX_TARGET parameter. if i set SGA_TARGET, SGA_MAX_SIZE along with MEMORY_TARGET and MEMORY_MAX_TARGET then how oracle will manage the memory? Because as per my understanding if we set MEM
View 3 Replies View RelatedI have set my processes and session value in my database as 1000 and 1248 respectively. I am using 11.2.0.1 but when I restart my DB it showing me sessions=1524...its too high value then what I set.I don't know why it showing higher value than the value I set...
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=1248 SCOPE=SPFILE;
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u02/app/oracle/product/11.2.0/db_1/dbs/spfileeng11g02.ora
System parameters with non-default values:
processes = 1000
sessions = 1524
And one more thing is there any recommended formula for calculate sessions in 11g? [ I found one formual from google:
SESSIONS derived: (1.1 * PROCESSES) + 5
One of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?
View 9 Replies View RelatedI'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.
View 9 Replies View RelatedOracle database 11g Expression Edition 11.2.0.2
I want to change the the number of processes to 150 (default is 100) I did this by issuing the below command.
alter system set processes=150 scope=spfile;But why that modified value is not being reflected?
select name, value from v$parameter where name in ('sessions','processes','transactions');
NAME VALUE
-------------------- -----
processes 100
sessions 172
transactions 189
Is there any way to tell what processes/sessions were connected to a DB after I get an ORA 20error. As you can see from the query results below, I did hit the max value of processes but I would like to know when it happen and who was connected.
I realize I can up the value, but before I do that I want to see what caused this to happen.
SQL> select * from v$resource_limit where resource_name in ('sessions', 'processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
LIMIT_VALU
----------
processes 221 1000 1000
1000
sessions 223 1020 1105
1105
The following link states
Quote:
SESSIONS_PER_USER
Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.
[URL].....
Of course the following is not working even when resource_limit is TRUE
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;
How can I restrict a user to have limited sessions say 2 sessions across 4 node cluster? Presently I am checking the sessions logged in using sql+ and no connection pooling of front end etc. is involved.
I have an assignment, i am suppose to disconnect all the users that are blocking sessions for over three hours. Below is my code which is not working
Create or replace procedure b_user IS
Declare
ssid number;
serial number;
Begin
[code]......
how to avoid oracle deadlock with two sessions. Here is my example, We use Select query with For Update in order to get the unique number from a table. When one user has accessed this query, while the other user tries, System gives a Performace slow and when checked it is due to locking of this select query with For update.
We don't want to use For update WAIT or FOR UPDATE NOWAIT since these will result in missing of number.
