Sessions Value Always Seems To Be Higher Than Set Value

Jul 23, 2010

I 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

View 3 Replies


ADVERTISEMENT

Server Administration :: Upgrading To 10g 10.2.0.2.0 Or Higher

May 8, 2010

where I can download the software for upgrading to 10g 10.2.0.2.0 or higher, but not 11g. I am not going to install 11g. Currently, I have 10g 10.2.0.1.0 and I have Windows XP. I don't consider to install 11g.

View 3 Replies View Related

Server Utilities :: Importing Dump From Higher Database Version 2 Lower

Aug 3, 2010

We can Import dump from higher database version to lower database version.

View 26 Replies View Related

Sessions Based On SQL ID

Aug 31, 2012

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 Related

Inactive Sessions In Database?

Dec 12, 2011

In our databace there are around 300 sessions were in inactive state.

Database is not releas the sessions.

View 2 Replies View Related

INACTIVE Sessions In Oracle?

Apr 22, 2011

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 Related

Possible To Limit Sessions In These Views

Jan 17, 2013

I'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 Related

Sessions Processes And Transactions

Nov 20, 2012

Oracle 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

View 8 Replies View Related

Processes / Sessions Connected To DB After ORA 20 Error?

Dec 28, 2010

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

View 6 Replies View Related

Security :: Limit Sessions Per User In RAC

May 6, 2011

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.

View 5 Replies View Related

SQL & PL/SQL :: Disconnect User Who Are Blocking Sessions

Apr 11, 2011

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]......

View 7 Replies View Related

Performance Tuning :: Locking Of Two Sessions?

Apr 27, 2012

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.

View 3 Replies View Related

Client Tools :: 2 Sessions For One Connection?

Oct 30, 2010

why are there in the Administration --> --> Session 2 entries for 1 database connection (here with benthic golden).But if I login in a browser, there is only 1 entry for this database connection

View 3 Replies View Related

SQL & PL/SQL :: Number Of Active Sessions With Same Sentence?

Oct 20, 2011

I have a RAC on 9i and I want to know the number of active sessions that it's executing the same sql sentence.I'm using this sql but I'm not sure that this query give me te correct result.

SELECT se.inst_id,se.status,se.SQL_HASH_VALUE,se.USERNAME,substr(sq.SQL_TEXT,1,50) SQL,count(*)
FROM GV$SESSION se, gv$sql sq
WHERE se.STATUS='ACTIVE'
and se.username is not null

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Difference In Table Data Between Sessions

May 27, 2011

I create table in session1:

create table marco_tmp_270511
as
select 5 as t from dual

Then I log in session2 and perform query to

select * from marco_tmp_270511 and receive 5 in column t.

Then I return to session1 and perform:

delete marco_tmp_270511;
insert into marco_tmp_270511
select 99 as t from dual ;
select * from marco_tmp_270511;

Then I go again to session2 and perform query to

select * from marco_tmp_270511

and still receive 5 in column t. How do I synchronize data between sessions?

View 3 Replies View Related

SQL & PL/SQL :: Kill One Users Sessions Using A Procedure?

Mar 1, 2012

I want to create a procedure that kills the sessions of the user TEST.

I have coded the below, but that dont work.

CREATE OR REPLACE PACKAGE BODY SYSTEM.test AS procedure killsessions IS
v_sessions_sql varchar2(40);
--v_sessions_sql2 varchar2(40);

[Code].....

View 4 Replies View Related

How To Kill Invalid Sessions Automatically

Jul 3, 2013

On the oracle 11g, we see too many invalid sessions. Because of which, login is getting restricted. What do i need to do to kill those invalid session periodically, which will avoid of restarting of system.

View 4 Replies View Related

SQL & PL/SQL :: DBMS Job Commit Other Sessions Automatically

Apr 14, 2011

I am calling one procedure through job and after submitting the job i am using commit. problem is when job close the session it commit the code which is inside procedure and as per my understanding in another session also, which i want to avoid .

my procedure is

Create My_proc is
Begin
Insert into my_table values(1,2,3);
End;

And my job proc is

Create my_job_proc is
Begin
dbms_job.submit(jobno,'my_proc;',sysdate,null,null,false);
commit;
End;

now i am calling my job proc is

Declare
Begin
my_job_proc;
End;

Now problem is once it finish, it will insert the data into my_table which i don't want until I call Commit.I already try 'Set autocommit off'

View 9 Replies View Related

Slave Sessions In DBMS-PARALLEL-EXECUTE?

Oct 4, 2012

I need to identify, whether my current session is one of slave sessions scheduled as Oracle scheduler job via DBMS_PARALLEL_EXECUTE or not. I already succeeded using join of dba_scheduler_running_jobs, user_parallel_execute_chunks and v$session, but I feel that this is not optimal approach.

View 1 Replies View Related

Command Won't Terminate Current Sessions To Database

Aug 23, 2010

lsnrctl RELOAD [listener_name] Hope the above command wont terminate the current sessions to the database.

View 2 Replies View Related

Prevent Other Sessions From Writing To The Tables In Procedure?

Jun 26, 2013

here' my code.

delimiter //
SELECT CONNECTION_ID()//
LOCK TABLES source.jos_daikin_control_card_fcu_model WRITE//

[Code].....

ERROR 1192 (HY000): Can't execute the given command because you have active lock
ed tables or an active transaction

Is there a way to prevent other session from accessing the tables called in the procedure?

View 3 Replies View Related

Server Administration :: Sessions Having Status Sniped?

Mar 9, 2012

My application is opening a lot of sessions in my DB server. I applied resource_limit=true and idle_time=15 min. ans assign this profile to all application user.

Now I am seeing a lot of sessions having status sniped in v$session.

I want to clean up these sniped sessions and what they mean.

View 19 Replies View Related

RAC & Failsafe :: Dbms Jobs Initiates Many Sessions

Sep 11, 2012

We created a job yesterday which will call the below procedure. if we start this job, it initiates 92 sessions parellely. How it is initiating 92 sessions parelley?

procedure prc_HECTOR_CIDB_IN_PURGE
IS
cursor CUR_PROC is
select rowid from CUSTMODEL.HECTOR_CIDB_IN where PROCESS_FLAG in ('Y','F');

TYPE TYP_CUR_DATA IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
v_typ_cur_data typ_cur_data;
[code]....

View 4 Replies View Related

Server Administration :: Oracle 10g Blocking Sessions

Aug 19, 2010

I am using oracle 10g as server in my lab. I faced some problems initially, but later after increasing the USERS tablespace it is working fine.

But there is still one problem. During the query execution some queries will be blocked and it doesn't leave any consequent queries to execute from the same user.

The blocked sessions will be displayed in the admin page under blocking sessions link. There is a option to kill the session. But when i do that, it affects all the users and the connection will be lost to all the users. again I have startup the database from beginning.

View 1 Replies View Related

Performance Tuning :: Identify Blocking Sessions

Feb 21, 2011

My statspack reports are sometimes showing figures such as this from the Foreground Wait Events section:

Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
PL/SQL lock timer 1,430 100 6,982 4883 0.0 18.0
enq: TM - contention 3 0 6,438 ###### 0.0 16.6
db file sequential read 310,385 0 1,765 6 3.5 4.6
TCP Socket (KGAS) 12,593 13 180 14 0.1 .5
db file scattered read 23,673 0 87 4 0.3 .2
log file sync 19,409 0 82 4 0.2 .2
ksfd: async disk IO 1,680 0 29 17 0.0 .1
[code].....

The TM lock that occurred 3 times appears to be disastrous.Historically, this could be caused by missing indexes on foreign key columns.

How can I be alerted when this event occurs, so that I can do some real-time investigation into the sessions and the SQL that hit it? I suppose I could schedule a job to query v$lock/dba_waiters/dba_blockers every few minutes, but is there a better way? Any standard edition scripts for this?

View 4 Replies View Related

SQL & PL/SQL :: Trace Sessions From Specific User And Machine?

Sep 10, 2012

I need to trace all sessions created from specific user and specific machine . Got some code from internet and customised .

CREATE OR REPLACE TRIGGER SYS.enable_trace_trigger
AFTER LOGON ON DATABASE
DECLARE

[Code]....

I have yet not created it on db .

View 3 Replies View Related

RAC & Failsafe :: Check How Many Sessions Connected To Particular Node

May 30, 2013

I have few doubts in rac environment

1.In 2 node rac while adding datafile to tablespace if you forget to mention '+'then what will happen whether it is going to be create or it throws an error if it creates where exactly located and other node users how to work on that tablespace .what all steps to perform that datafile is useful for all node users.

2. In Rac environment how to check how many sessions connected to particular node.

3)In Rac any node is evicted due to network failure then after we rebuild the network .Is there any steps to do manually to access the failure node after rebuilding the network or it will automatically available in cluster group which service is perform this activity.

4.While configuring cluster ware you choose voting disk and ocr disk location and which redundancy you will choose suppose if you go for normal redundancy how many disks you can select for each file either one or two?

View 9 Replies View Related

Server Administration :: Sessions And LGWR Locking?

Jul 13, 2010

We had an issue last week were we had a session with a very basic SQL query lock up the database, spiking the CPU at 100%. When you would kill the session, the lock would just jump to another session and so on. We finally had to restart the database since our clients were being kicked out. After the restart of the database, the LGWR ended up locking and held the CPU between 85-95%. The archive logs were switching every 5 minutes, when normally it would be every 45min. We spoke with Oracle Support, but they just ended up brushing the issue off and saying it was a hardware issue and were not able to provide any kind of backing to that.

View 4 Replies View Related

RAC & Failsafe :: Why Sessions Are Not Disconnected After Service Reallocation

Feb 2, 2013

i have 2 nodes RAC with service piodbserv2 which is active only on 2nd node. client is connected for (100% sure) to piodbserv2 on node2. i am doing relocation of this service to node1 using -f (force) option:

[oracle@rac1 ~]$ srvctl relocate service -d piodb -s piodbserv2 -i piodb2 -t piodb1 -f
[oracle@rac1 ~]$ srvctl start service -d piodb -s piodbserv2 -i piodb1
PRKP-1064 : Service piodbserv2 is already running on instance piodb1.
[oracle@rac1 ~]$ srvctl start service -d piodb -s piodbserv2 -i piodb2

-f (force) option is described as this which forcibly rellocate users from one to another node simply disconnecting sessions from node2 in my example

but i am surprised because my session is still opened on node2 after piodbserv2 relocation

SQL> select sys_context('userenv','instance') from dual;

SYS_CONTEXT('USERENV','INSTANCE')
-------------------------------------------------------------

2

SQL> select sys_context('userenv','instance') from dual;

SYS_CONTEXT('USERENV','INSTANCE')
-------------------------------------------------------------

2

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
2

SQL>

at this moment piodbserv2 is only on node2. when i kill my user session and try connect again it will connect to node1. but i suspected that after forcibly rellocated service, my sessions will be disconnected from node2 forcibly also.

View 6 Replies View Related

Query For Inactive Sessions Consuming Memory

Apr 5, 2013

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';

View 19 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved