Server Administration :: Close All Open Cursors In Other Session?
Mar 23, 2010
How to Close All open cursors in other session.
NAME TYPE VALUE
------------------------------------ ----------- ---------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 500
session_cached_cursors integer 50
Ex. as above we have 500 max cursor
seesion show 499 and now we need to flust all open cursors
View 9 Replies
ADVERTISEMENT
May 28, 2013
In my environment found maximum open cursor exceeds error. So how can I found the open cursor list and how can I close that cursor without restarting. Any SQL commands to close the open cursor.
View 4 Replies
View Related
Apr 26, 2013
My current database MAX OPEN CURSORS VALUE=300.
Applications "A","B","C" using same DB user (USER1) then connect to a database.
Last night I created dedicated DB user (USER2) then application "B" connect to DB using this new user.Starting traffic increase today morning "B" application receiving "ORA-01000: maximum open cursors exceeded" error from DB.When I change current DB user USER2 to USER1, application did not receiving above errors.
I don't understanding what happening in database?
View 5 Replies
View Related
Oct 23, 2006
I think this is the scariest error I've encountered in oracle. After some thime of developing/debuggin I've always reached this error. This leaves me no choice but to restart the java app server, in that case I'll be affecting all the other applications deployed on it which is a very bad thing for me.
Is there anyway I could solve this, I've made it sure that I've handled all errors so that I could always close the connection. Simply put, for every termination case of my program I've placed a closing statement just to be sure that no connections are left open. But even after this, I still experience the ORA 0100 error.
View 8 Replies
View Related
Sep 21, 2010
I am trying to create the Physical StandBy Database in the same server. Till last 2 Final steps, everything went on well. In the final steps, when I try to open the StandBy Database, it throws the following Error:
*******************************************************
SQL...> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
*******************************************************
I tried creating the Physical Standby with the following Steps.
Environment: Oracle Release 10.2.0.1.0 / Windows 2003 Server Enterprise Edition SP2
Primary DB = 'PrimDB'
StandBy DB to be created ='StBy1DB'
In the same Server, location for
PrimDB datafiles='F:oracleproduct10.2.0oradataPrimDBData',
StandBy Datafiles='E:StandBy_DBData'
PrimDB Control Files='F:oracleproduct10.2.0oradataPrimDBControl'
StandBy Control Files= 'E:StandBy_DBControl'
[code]....
Step 1 Create the Oracle Service for StandBy DB "StBy1DB' and Create the Standy DB Password file.
ORADIM -NEW -sid StBy1DB -intpwd passwd -startmode manual
Step 2 Shut down the primary database.
SQL> SHUTDOWN IMMEDIATE;
Step 3 Copy the datafiles to 'E:StandBy_DBData'. (including Temp01.dbf is it Right?)
Step 4 Restart the primary database.
Restart the primary database:
SQL> STARTUP;
Step 5 Create a Control File for the Standby Database (Should this command be given in Mount Status or Open Status?)
From Primary database,
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'E:StandBy_DBControlStByControl01.ctl';
Step 6 Prepare the Initialization Parameter File to be Copied to the Standby Database
SQL> CREATE PFILE='E:StandBy_DBPFileinitStBy1DB.ora' FROM SPFILE;
Step 7 Set Initialization Parameters on a Physical Standby Database
db_cache_size=444596224
java_pool_size=4194304
large_pool_size=4194304
shared_pool_size=150994944
streams_pool_size=0
[code]....
Now When I type the following command, it throws an error:
SQL...> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
View 2 Replies
View Related
Feb 7, 2011
I have a table by name DCODE.
I insert a row in it(TOAD) immediately after inserting it, if i try to delete it i am geeting an error saying ORA-01000: maximum open cursors exceeded.
I have tried to to solve this by increasing the value of OPEN_CURSORS. But still i get the same message.
View 12 Replies
View Related
Jun 23, 2011
I am working on SQL,PLSQL and my application is in Java. I have 2 schemas in same database.
In one schema, the DML operations are very slow while in other schema is fine.
Also I am getting an error ORA-01000 maximum open cursors exceeded frequently while running application in Java.
View 13 Replies
View Related
Jun 9, 2010
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.
View 4 Replies
View Related
Mar 8, 2013
I am using oracle 10gXE just for basic practice but its showing following Errors
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 8 11:46:34 2
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL>
SQL>
SQL>
SQL> startup;
ORA-01081: cannot start already-running ORACLE - shut it down fir
SQL>
SQL>
SQL>
SQL> select * from dept;
select * from dept
[code]....
View 3 Replies
View Related
Jul 26, 2010
While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
View 13 Replies
View Related
Aug 23, 2011
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.
View 20 Replies
View Related
Jun 11, 2007
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?
View 11 Replies
View Related
Apr 17, 2011
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.
View 4 Replies
View Related
Dec 17, 2012
Which command is the right way to open a alert log file of below size.
-rw-r----- 1 oracle asmadmin 28070129 Dec 17 08:06 alert_DEMO.log
I used to open a alert log file using vi editor. Is that a right way to open and view a alert log ?
View 5 Replies
View Related
Mar 18, 2013
I need to ask about PGA memory. Is this memory for every user or every session
i.e. PGA 200M
I have 2 users with 300 connection with one of them. Is 200M for 300 connection or for every connect.
View 2 Replies
View Related
Nov 22, 2012
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].....
View 3 Replies
View Related
May 22, 2012
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?
View 6 Replies
View Related
Feb 6, 2010
Which is the best way to kill a session?
alter system kill session sid,serail#
or
kill -9 spid
or
orakill sid,thread
I am using unix,windows operating system.
View 3 Replies
View Related
Apr 23, 2013
How to kill of a killed session?
I had killed inactive session using alter system kill session 'sid,serial#';
after that that sessions marked as killed and still get in v$session.
Then i try to get spid so that i can killed then from os command kill -9 spid
but when i try to get that join with v$process, no row select.
select * from v$session where username ='CAS' and status ='KILLED'
return 205 records.
select p.*,s.username,s.program,s.machine,s.status from v$process p,v$session s
where p.addr=s.paddr and s.status='KILLED'
return no record.
View 24 Replies
View Related
Nov 22, 2010
what does 'SNIPED' status in v$session mean? It is killed/ clear from instance automatically or need to kill manually for releasing the resource.
View 3 Replies
View Related
Jun 10, 2011
I want to configure this
my end user open only one session
means one session per one user only
View 8 Replies
View Related
Jun 8, 2010
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.
View 9 Replies
View Related
Aug 9, 2013
A way to show session parameters? Such as
CONSTRAINTS
USE_STORED_OUTLINES
ROW ARCHIVE VISIBILITY
CURRENT_SCHEMA
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.
View 4 Replies
View Related
Aug 5, 2011
when i modify a column of table tb_hxl_user,just as flowing:
Alter Table TB_HXL_USER Modify CREATE_DATE Not Null
now i select the table TB_HXL_USER,just as flowing:
select * from TB_HXL_USER;
it is hang,and i found the wait event is library cache lock in v$session_waits,why?how can i avoid this.
View 8 Replies
View Related
Mar 4, 2010
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.
View 8 Replies
View Related
May 24, 2013
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,
EVENT# WAIT_CLASS
------ ----------------------------------------------------------------
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
115 Configuration
116 Commit
116 Commit
116 Commit
116 Commit
EVENT# WAIT_CLASS
------ ----------------------------------------------------------------
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
EVENT# WAIT_CLASS
------ ----------------------------------------------------------------
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?
View 3 Replies
View Related
Apr 5, 2010
I am facing a strange issue on 11gR2 (OEL 5.4) standby readonly with apply database.It's throwing 16000: database open for read-only access during SELECT's .
Here is snapshot of errors.
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
SQL> l
1* SELECT t0.airportID, t0.archived, t0.assetTag, t0.bluetoothID, t0.cmBundle, t0.createdDate,
t0.currentProductTaskID, t0.ethernetID, t0.failOrReworkCount, t0.highestCompletedTaskTypeID, t0.lastModDate, t0.lastStationID,
t0.modCount, t0.modelID, t0.oemSerialNumber, t0.orgSerialNumber, t0.pdmVersion, t0.preburnComplete,
t0.productID, t0.reworked, t0.secondaryEthernetID,
t0.serialNumber, t0.shipped, t0.specialBuildTypeID,
[code]....
View 7 Replies
View Related
Apr 27, 2011
I am using oracle 10g R2. Some how control file is corrupted and database is not open. and there is no backup of control file. Now i need to open the database without recreating the database.
View 5 Replies
View Related
Dec 27, 2011
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.
View 7 Replies
View Related
Jan 31, 2012
I have a question about spfile.
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.
View 3 Replies
View Related
