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.
I have installed Oracle 10g on my PC and create a Database on it. I wanted to connect it with PL Sql Developer with the same PC but it`s not connecting. It gives the following error "ORA-01019: unable to allocate memory in the user " And some time blank error message box.
Here is system information:
OS: Winsows 7
Oracle Database 10g Express Edition
PL SQL Developer 8.0.3.1510
Login Setting
User Name: book
Password: oracle
Database: XE
Connect as: Normal
I'm using Oracle8i, with VB6 as front end When i try to connect Oracle using RDO in VB i get an error message from Oracle It is S1000:Oracle ODBC.ora Ora:1043 User Side Memory Corruption.
View 11 Replies View RelatedI 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 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?
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 are running oracle9i database in windows 2000 server few months before i re sized the SGA to sga_max_size =800M and pga-aggregate_target to 400M, actually we have 4gb RAM out of the we are allocated 1200Mb for SGA and PGA.
My question is if the allocated memory got filled in SGA or PGA whether the system will show any error, because when I am querying from sql> show parameter ; the sga free memory is reducing daily what will happen when it reaches its max_size.
I created an externally authenticated user in database. And can login without password with below syntax.
SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"
This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.
connect scott[proxy_user]/password_for_scott@TESTDB
So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?
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.
estimate the percentage of memory should i can allocate from the total physical memory (it is 6G) as attached in the image file as a print screen.
View 4 Replies View RelatedI am facing a problem as listed below:
$sqlplus /nolog
SQL>conn /as sydba
connected.
SQL>conn upt/upt
not connected
ora-01034 not available
ora-27101 shared memory realm doesntnot exists
linux error-2 no such file ...
SQL>...
UPT is the user in which our data resides.
wat cud be the problem??????
I tried increasing sizes in INIT.ORA.... listener,tns is OK
.bash_profile of ORACLE user is also correct.
I am facing this problem continuously from last 1 week on my production oracle database.I have performed all kind of changes on parameter but still am getting this error.
Oracle version -> 11g
O/S -> windows server 2003
RAM -> 11 GB
#########################INIT CONFIG###################
convdb.__db_cache_size=536870912
convdb.__java_pool_size=10485760
convdb.__large_pool_size=4194304
[code].....
I read in few blog and they are saying, oracle11g allocate 60% of the memory to SGA and 40% of the memory to PGA when we enable MEMORY_ TARGET to non-zero.Here are few blogs. URL.....It is not working on that way(60:40) on my database.
Here is my DB version.
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
[code]....
I am not able to find 60%:40% concept in oracle document..so all the above blogs info are wrong?
I have read this article:
[URL].........
And i see:
Quote:
If MEMORY_TARGET is set to non zero value:
SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
So, I have set:
alter system set sga_target=0 scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;
alter system set sga_max_size=0 scope=spfile;
alter system set memory_max_target=512M scope=spfile;
alter system set memory_target=300M scope=spfile;
, and then bounced the instance.
After startup, I see:
SQL> startup
ORACLE instance started.
Total System Global Area 272027648 bytes
Fixed Size 1384012 bytes
Variable Size 100663732 bytes
Database Buffers 163577856 bytes
Redo Buffers 6402048 bytes
Database mounted.
Database opened.
SQL>
But Total System Global Area should't be in that case 60% of memory mentioned in MEMORY_TARGET? Memory mentioned in MEMORY_TARGET was 300M, and 60% of 300M is 180M, which is not 272027648 bytes.
I just want to use automatic memory management, so I've set the other parameters above to 0. My instance is on my local machine, so just for my own personal use.
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.
we are trying ti implement BMC monitoring tool for our oracle 11g2 RAC/solaris10 environment. for the purpose we create a user called bmcuser in OS level, this user connecting database to monitor, this user occupy shared memory as oracle. Explain why this user taking that much shared memory just to connect database using sqlplus.
oracle taking 55% bmcuser taking 44%,
SQL> show parameters memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 3232M
memory_target big integer 3232M
shared_memory_address integer 0
SQL> show parameters sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3232M
sga_target big integer 0
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
6449 bmcuser 47M 44M sleep 23 10 0:57:31 0.2% PatrolAgent/1
[code]........
pga & uga usage sessionwise
SID ORA_USR OS_USR S_PROG S.MACHINE S.PGA_MEM S.PGA_MEM_MAX S.UGA_M S.UGA_M_MAX
252 (null) oracle oracle@racdev1.zakathouse.org (ARC3) racdev1. 31054584 32168696 181056 181056
227 (null) oracle oracle@racdev1.zakathouse.org (ARC2) racdev1. 31054584 32168696 181056 181056
177 (null) oracle oracle@racdev1.zakathouse.org (ARC0) racdev1. 31054584 32168696 181056 181056
576 (null) oracle oracle@racdev1.zakathouse.org (LGWR) racdev1. 16693544 21281064 181056 181056
326 (null) oracle oracle@racdev1.zakathouse.org (LMS0) racdev1. 13753080 13753080 181056 181056
376 (null) oracle oracle@racdev1.zakathouse.org (LMS2) racdev1. 13556472 13556472 181056 181056
[code]........
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 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.
View 4 Replies View RelatedI have a strange problem when creating a view in user from another user
I have a user called "Cash_tst"
its syntax creation is
-- Create the user
create user CASH_TST
identified by ""
default tablespace CASH
temporary tablespace TEMP
profile DEFAULT
quota unlimited on cash;
-- Grant/Revoke object privileges
grant connect to CASH_TST;
grant dba to CASH_TST;
grant resource to CASH_TST;
-- Grant/Revoke system privileges
grant create any view to CASH_TST;
grant unlimited tablespace to CASH_TST;
I want to create a view
CREATE VIEW TAMER
AS SELECT *
FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO
"AROFL" is another user on the same database
when try to create the view "tamer" i got message of
"insufficent privilege"
although i granted "create any view" to the user "cash_tst"
User session has expired. I have to enable user without changing the password of the user.
View 1 Replies View Relatedwhen 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].....
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?
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.
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.
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 Relatedi did this step
C:UsersAdministrator>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 17 15:59:33 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys@orcl as sysdba
Enter password:
Connected to an idle instance.
SQL> create pfile from spfile;
[code].....
here its my init.ora file
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
[code].....
Oracle 10.2.0.4 (user or developer getting error for not able to allocate ora 04031.
I am trying to do alter system shared_pool_size = 250M but it says
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
I checked the show parameter sga_max_size = 376 M
the set upin the init.ora for pools:
###########################################
# Pools
###########################################
java_pool_size=157286400
large_pool_size=33554432
shared_pool_size=157286400
From show parameter sga;
[code]...
no rows selected
OS Linux. this was the message in alert.log;
ORA-04031: unable to allocate 88 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","sql area","opn: qkexrInitOpn")
All of a sudden my oracle application has stopped functioning.When I try to log in I get the following messages:
ORA-27101 SHARED MEMORY REALM DOES NOT EXIST
ORA-01034 ORACLE NOT AVAILABLE
From the command line when I try to connect as sysdba and startup the following message appears: "oracle instance terminated.Disconnection forced"
I have a 11g database installed on a windows 2003 server. When I created the database I could not assign more than 2GB for SGA_TARGET where as I have 16GB of RAM available on the server. I created the database with SGA_TARGET as 1.5 GB and Memory_TARGET as 2GB. I could create the database successfully. Later when I again tried to increase SGA_TARGET to 6GB and MEMORY_TARGET to 8GB, I could not start the database. I got below error;
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command
Quote:I added /pae in the boot.ini
Added AWE_WINDOW_SIZE key to registry as 2000000000
Set use_indirect_data_buffers=true
added db_block_buffer=131072 (ie. 2GB DB_BLOCK_SIZE=16kb)
java_pool_size= 1000M
large_pool_size=1000M
shared_pool_size=2000M
I got again the same error
I could not use SGA_TARGET. So is AMM not allowed with AWE?