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 ?
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].....
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.
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.
I need to filter a SQL statement with an application item value retrieved from session state. I am assuming I could do this using a Dynamic Action on a Page Load event but I'm not sure where to go from here. How could I append the filter to an existing report page SQL statement?
View 7 Replies View RelatedI have upgraded from APEX 4.1 to 4.2.all read only items with dynamic action are arising below error:
session state protection violation: this may caused by manual alteration of protected item P2_IDACCSET.
Using apex 4.2. I have created form item on page and item attribute DISABLES IN SETTINGS , set value as YES and SESSION STATE value as YES.
SETTINGS->DISABLES-> SESSION STATE
by doing this i am getting the error. Error is
Session state protection violation: This may be caused by manual alteration of protected page item P98_CHECK_AMOUNT. If you are unsure what caused this error, please contact the application administrator for assistance.
I have searched for comment on this, but could'n find. So problem is ... not all of the page items are showing in SESSION STATE.
View 4 Replies View RelatedI have a calendar region using SkillBuilders plug-in. When click on an event, a modal page opens showing the event details and allowing the user to modify it. Recently, I enabled Session State Protection in my pages, so I changed my query in the calendar region to use the APEX_UTIL.PREPARE_URL function when generating the URL to edit the event. Now, when I click on an event, the URL looks like: [URL]....
p=34925:3:102130990572822::::P3_ID:41&cs=3DE8BFFE2FCA925B7716361D6ECFC546B If you notice the URL has & before the check-sum (cs), instead of just & and it is causing an error on my page. The reason is that the plug-in is escaping special characters. If I change the source of the plug-in to use this:
l_url := l_column_value_list(6)(x); Instead of:
[code]....
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 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.
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.
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';
how can we change the color of active & inactive Tab canvas dynamically. I am using Dev Suite 10gR2
View 2 Replies View RelatedWe 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]....
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 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 can see the error in alert log like
2437312:ORA-12801: error signaled in parallel query server P009
2437313:ORA-01502: index 'POS.XIETBK_POS_FACT_TRAN_DATE' or partition of such index is in unusable state
and tried to rebuild the index and i got following error.
ORA-14086: a partitioned index may not be rebuilt as a whole.
The table size for the index is large. we need to rebuild the hole index.
I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.
marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.
I have got a table where the site address is stored as follows -
SOUTH PARK ROAD #822,,,,
HOLLYWOOD,FL,33021,,
USPO BOX 1111,,,,
PHILADELPHIA,PA,19178-3478,,
USNORTH AVE EAST,
P.O. BOX 1234,,,ELIZABETH,NJ,07207-6031,,
US12345 PARK SORRENTO SUITE 222,,,,
CALABASAS,CA,91302,,US
I have got 2 fields to be populated from this - CITYSTATE How can I pick up the 5th and 6th field separated by the comma.
For example: -
Following output should be shown for the above recordsCITY
STATEHOLLYWOOD FLPHILADELPHIA PAELIZABETH NJCALABASAS CA
When i am writing a function (Stand alone or inside a Package) i know what i am writing, i know weather i am changing a Package state or weather i am changing a Database State. So what is the use for giving a PRAGMA RESTRICT_REFERENCE?
as for the other PRAGMAs EXECPTION_INIT is needed to Specify an error name to a specifiec error number so that i can use the error name to handle the exception. AUTONOMOUS_TRANSACTION is used to execute the SQL Operation inside a Block as a child Transaction.SERIALLY_REUSABLE states that a package variable doesnot persists throughout the session.
RESTRICT_REFERENCE states that the code should not do the following
RNPS : Read no package state,
WNPS : Write no package state
RNDS : Read no database state
WNDS : Write no database state
what is there to state as i know what my code is doing.
without specifing EXECPTION_INIT, AUTONOMOUS_TRANSACTION or SERIALLY_REUSABLE i cannot get a handler for an exception which doesnot have a handler, i cannot execute SQL Operations from inside the CODE autonomously or i cannot reuse the package variable, but without the RESTRICT_ REFERENCE is can ensure that my code in not doing (RNPS,WNPS,RNDS,WNDS).
I understand the architecture of solid state drives favor different types of I/O. Oracle 11g introduced the database flash cache which allows flash storage to be used as a secondary cache. With database flash cache serving as a secondary cache to my Oracle buffer cache.
My understanding is I need to config two database parameters DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE. If i want to have certain tables/indexes to take advantage of this extra cache I would set the value KEEP within the parameter.
Is there anything that needs to be done to the tablespaces / datafiles.
so here is the query : For every state, list the most popular product. Here is the query I have so far :
SELECT C.State, P.Product_Name, Count(*) cnt FROM Product P, Customer C, OrderTable O, LineItem L WHERE C.CID = O.CID AND O.OID = L.OID AND L.PID = P.PID GROUP BY C.State, P.Product_Name;
Result :
STATE PRODUCT_NAME COUNT(*)
---------- -------------------- ----------
New Jersey Computer 3
Texas Computer 1
New Jersey Speaker 2
I would need the result to only say New Jersey Computer and Texas Computer because I only want a list of the states with the product name that is sold the most in each state.All I need to do is have the query only select the product name with the max count for each state...
I am getting the following error only for the ""first execution"", even the package is in "valid" state. If the execute the same package again "second execution", it runs ok.
usually we complie all the "invalid objects" in the schema when we move the code to TEST or PRE-PROD env.
but i am getting the following error, even if the package is in "valid state".
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "TEST_OWNER.TEST_PKG" has
been invalidated
ORA-04065: not executed, altered or dropped package
"TEST_OWNER.TEST_PKG"
ORA-06508: PL/SQL: could not find program unit being called:
"TEST_OWNER.TEST_PKG"
ORA-06512: at line 2I have gone through the following thread, blu explained the cause of this error,
but do we get this error even when the package is in "valid" state too?
Re: When should be package invalidated?
We are experiencing an issue with ORA-04068: existing state of packages has been discarded. We're running Oracle 10.2.0.4 on Solaris 10.
I'll provide the timeline of what occurred:
1.) Last night during a maintenance window I recompiled a package and package body. I ran "create or replace" statements from a script via sqlplus to recompile both package and package body. Because I am required to do all DDL under my own login (and not the schema owner login) the scripts require that the schema name be included in the "create or replace" statements. Otherwise an object will be incorrectly created in my schema. This is what happened with the package body. I discovered this when the package body would not compile.
2.) I ran a "drop package body" statement to drop the package body from MY schema.
3.) I added the proper schema name to the script and recompiled both the spec and body under the proper schema.
4.) I ran a script to check for any invalid objects under the application user schema. There were none.
5.) During the night a batch process ran which executed the package. There were several failures with the error ORA-04068: existing state of packages has been discarded.
6.) During the early AM today the error stopped. Not knowing this at the time and believing that the issue was still occurring I began searching the internet for information regarding this issue. One site I found said this could be due to DDL being done on the object (which was the case for us) and there could be an entry in the shared pool marked as invalid which our application's session may be accessing. It recommended running "alter package recompile" for the spec and body. I did this but it seems to have triggered the event again.
I also ran utlrp.sql.
I found some information suggesting the use of DBMS_SESSION.MODIFY_PACKAGE_STATE and/or RESET_PACKAGE but I'm not seeing mention of the risks/implications.
I can verify that the object is no longer in my schema and that the proper object owner has no invalid objects. I am not sure what to do to resolve the issue or keep it from occurring again.
I started database in mount state;startup mount; alter tablespace users offline;
I tried to make tablespace offline In another case online but showing error database not open same for command alter tablespace begin backup; do think we can make tablespace offline in mount...
after merging two partitions into single partition (partition is by list) of a table ,when i analyzed the table it is giving this error : ORA-01502 INDEX TEST.PK_ID or partition of such index is in unusable state.
View 7 Replies View Relatedora 20000 index partition in an unusable state. what can i do
View 6 Replies View Related