Unable To Set Sga_max_size Parameter Value?
May 24, 2013
To tune the OID11g the DB parameter recommendation is :
sga_target Minimum 60% of RAM
sga_max_size Minimum 60% of RAM
db_cache_size Minimum 60% of RAM
So what is RAM size..is that the RAM size on the linux BOX which is 8 GB in my case
or RAM size assigned to Oracle DB?
If I take RAM size 8 GB I am not able to modify the parameter. I am getting below error:
SQL> alter system set sga_max_size=4916M scope=both;
alter system set sga_max_size=4916M scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
how to check that total memory assigned to Oracle DB?
View 16 Replies
ADVERTISEMENT
Sep 24, 2013
The Memory tuning advisor has recommended that I increase the size of the SGA_TARGET to 5G, from my current size of 3G.
SQL> select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
1536 .5 1555141 1.667 7251211
2304 .75 949130 1.0174 4572127
3072 1 932898 1 2258733
3840 1.25 930939 .9979 2168835
4608 1.5 929819 .9967 2005529
5376 1.75 929166 .996 2005529
6144 2 928980 .9958 2005529
7 rows selected.However, the SGA_MAX_SIZE parameter is set to 3G, so when I do:
SQL> ALTER SYSTEM SET sga_target=5376M scope=both;
ALTER SYSTEM SET sga_target=5376M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
My database used spfile. I know that the procedure to change this is the following: Create pfile from spfile, shutdown immediate, change value in pfile, startup with pfile..However, the pfile does not have any parameter called max_sga_size. It only has the following:
<dbsid>.__sga_target=3221225472
*.sga_target=3221225472
how to change sga_max_size?
View 7 Replies
View Related
May 28, 2013
I am having 11gr2 database on a 2 node RAC cluster . OS is RHEL version 5.8When i change the SGA_MAX_SIZE to 5GB, after i bounce it shows as 15GB.
I am trying to set my SGA_TARGET and SGA_MAX_SIZE to 5G. But after bouncing SGA_MAX_SIZE is showing as 15GB and not as 5GB
I also tried edit in pfile and then create spfile and started up with spfile. But still same i am observing.
View 2 Replies
View Related
Sep 21, 2010
I've done lots of reading about these two parameters, SGA_MAX_SIZE and DB_CACHE_SIZE.
What I understand now is, that setting db_cache_size to a target value and setting all other buffer cache setting to 0 will enable Oracle 9i to automatically manage buffer cache values, right? This is, per meta link document 223299.1 the most important parameter in Oracle 9i regarding memory management.
Now, SGA_MAX_SIZE set the maximum value at wich I can increase the db_cache_size to?
The question is ; WHY? Why should I allocate 1G of RAM to my oracle instance and not set the db_cache_size to the exact same value? What happens with the difference in memory between the SGA_MAX_SIZE and the DB_CACHE_SIZE, ex :
SGA_MAX_SIZE = 1000M
DB_CACHE_SIZE = 500M
What happens with the 500M left over, is it used for anything ?
View 6 Replies
View Related
Jun 28, 2012
I was just wondering how is the relationship between MEMORY_TARGET and SGA_MAX_SIZE parameters, if exists any at all.I set my memory parameters as:
# Enable AMM
MEMORY_MAX_SIZE=10G
MEMORY_TARGET=4G
# Disable ASMM
SGA_TARGET=0
SGA_MAX_SIZE=0
So I bounce my instances and when I checked the parameter SGA_MAX_SIZE, Oracle automatically set a value of 2456M to it:
sga_max_size big integer 2456M
sga_target big integer 0
Reading MOS ID 443746.1, there is no clear mention about this relationship. It seems that Oracle automatically set SGA_MAX_SIZE as 60% of MEMORY_TARGET.
In am running Oracle 11gR2 (11.2.0.2.3) under Red Hat Linux.
View 10 Replies
View Related
Jun 7, 2012
I am gettting the below mentioned error in my alert log file
The value (167) of MAXTRANS parameter ignored.
I have search this error on google, but i didnt find anything every post is telling that this is the impdp's or expdp's error.
But i havent use impdp and expdp here.
View 4 Replies
View Related
May 17, 2010
let's say a parameter changed in the database ex: alter system set retention_target= 1500; and i want to know what was the old value before it has been changed ,
View 1 Replies
View Related
Feb 9, 2012
From ETL tool i will be passing two parameters to a procedure. I want to know whether this parameter can be used in a select statement of a correlated query.
Query to be in the procedure :
begin
update table1 set table1.col1 = (select parameter2 from
table2 where table2.x = table1.x and table2.x = parameter1) ;
commit;
end;
parameter2 will be having the columnname to be passed. And everytime different columnname will be passed to this procedure.
View 16 Replies
View Related
Sep 5, 2012
I am writing a procedure in which I have a input string parameter in the following way..('NYC,ATL,OKC,KAC,LA'). I need to use that string to search values in the table.
Example
create or replace procedure search_city(p_string varchar2)
/*
paramater will be like ('NYC,ATL,OKC,KAC,LA')
*/
is
v_city varchar2(40)
begin
for i in (select city_name from cities
where city_code in (p_string )
) loop
v_city := i.city_name;
end loop;
end ;
View 2 Replies
View Related
Aug 5, 2010
if function having retrun statement, why we need to use out or inout parameters.
View 16 Replies
View Related
Aug 8, 2012
CREATE OR REPLACE PACKAGE test_package IS
TYPE refcur IS REF CURSOR;
END test_package;
CREATE OR REPLACE PROCEDURE get_info(o_cursor OUT test_package.refcur)
AS
BEGIN
OPEN o_cursor FOR
SELECT * FROM emp;
END get_info;
What is the advantage of using refcursor variable as OUT parameter in procedure. Instead of that why cannot we use variables or TYPE variables. use ref cursor as OUT parameter in procedure.
View 1 Replies
View Related
Jan 12, 2012
I need to test a parameter in a script that must adhere to what is currently in the database.For example, when a user enters the value when prompted from this script, ("Enter the schema copy desired: " ) the value must be of the current values in the db:, it would be something like this:
Values currently in DB: copyofschema201110 (the 2011 is the year, and 10 would be the month of oct).
copyofschema201109
copyofschema201108
So, if the user enters the value 'abc' (an incorrect value), then I would like the script to stop the user, and kick back a message, say from dbms_output.put_line, and force the user not to go forward, but re-enter a correct value.
View 13 Replies
View Related
Mar 25, 2013
I thought that you are not supposed to be able to assign the value of an out parameter to a variable (Feuerstein, 5th ed) and yet I was just
able to do this without any compilation error messages. Is Feuerstein wrong or am I missing something? Has the functionality changed?
PROCEDURE Parse_HC
(p_timestamp_string IN VARCHAR2,
p_timestamp_date OUT DATE)
AS
v_date DATE;
[Code]....
Here is my banner:
BANNER
----------------------------------------------------------------------
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
View 8 Replies
View Related
Mar 20, 2013
I have written this code, its throwing error.
begin
declare tmdate DATE;
select TO_DATE(TO_CHAR(MAX(DATERANGE),'DD-MON-YYYY')) into tmdate from table_check_date_range;
dbms_output.put_line(tmdate);
end;
View 10 Replies
View Related
Aug 29, 2012
What will happen when the OBJECT_CONSISTENT parameter is set to "Y" during export? When i have to set it as "Y"?
View 6 Replies
View Related
Oct 9, 2008
I have a scenario where I have to pass a paramter to "in statement". When I run the query from SQL plus its working fine.......but when I run the query at run time it doesnot come back with the results. I am sure its formating issue.
This is how I create a paramter
_sbInStatement.Append("(");
foreach (ListItem item in _listBox.Items)
{
[Code]....
oCmd.Parameters.Add(":InStatement", OracleDbType.Varchar2).Value = InStatement;
View 2 Replies
View Related
Sep 17, 2013
this PROCEDURE for Paging.
PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;
[code]...
View 1 Replies
View Related
Aug 22, 2011
We are seeing volume issue when taking Rman level 0 backup for a database , the database version is 11.2.0.2 and its on RHEL 2.1. As 11g supports compression for RMAN, we have implemented so as to reduce the backup space used.
" CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE "
However during full backup the volume size increases, meaning we have to increase /data volume (currently 500G) to more then a 1T for just rman to go through, else the backup hangs. Once backup is done we again bring down the volume size to less then 1T. The other compression parameters are HIGH and MEDIUM, hoever I am not very sure if changing to high or low will work as I couldn't find any right doc in meta link or may be I didn't searched correctly, I will continue to look for that.
View 2 Replies
View Related
Nov 16, 2012
How to pass parameter in a view.
create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60' then
case when a.ccn <> '091' then
[code]........
in this above view i have hardcoded dates, well how to pass parameter for this view without hardcoding? here i need to pass the date as a parameter from a select query to view the data from the above view!
View 4 Replies
View Related
Nov 1, 2010
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have this in one of the packages WCL_LIB:
TYPE vc2_255_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
and i use it in one of the procedures as
PROCEDURE "WCL_EVENTS"
(p_event_id IN NUMBER,
p_event_arr IN Wcl_Lib.vc2_255_arr,
p_model IN VARCHAR2 DEFAULT NULL,
p_model_code IN VARCHAR2 DEFAULT NULL
but the calling procedure doesnt have any array.... Can I declare something like in the procedure to be called
p_event_arr IN Wcl_Lib.vc2_255_arr DEFAULT NULL,
I tried, but doesnt seem to work? so how to call the procedure, which has a array as mandatory, but calling one doesnt have any?
View 5 Replies
View Related
Aug 11, 2010
I'm using Oracle 9i. How can i check for a string if it contains only numbers or letters? Otherwise i should return false.
View 6 Replies
View Related
Jan 12, 2012
I have the procedure with out parameter is ref cursor.
l_sql VARCHAR2(32767);
BEGIN
l_sql := 'select query with appending procedure IN aparameters';
OPEN rc_rpt FOR l_sql;
Here procedure IN parameter is a string with comma separated value which is appended in the dynamic query IN clause.So some time the size exceeded more then 32767 and getting error.If i am using normal parametrized cursor this issue is not there,but i want to return only ref cursor for some java purpose.My oracle version is 10g.
View 4 Replies
View Related
Aug 30, 2011
how to use different where caluse based on different in parameter. say I pass 1,2 as parameter if it is 1 then the where condition > 10 else if it is 2 then the where condition <5 the where conditions are placed in cursor for loops.
View 2 Replies
View Related
Apr 10, 2010
How to give table1 column to subquery with table2 :
(select t1.*, sq.*
from table1 t1,
(select a,b,c from table2 where col1= t1.col1) sq
where ...
View 4 Replies
View Related
Aug 8, 2012
The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
[code].....
how to use collection variables as OUT parameters in procedure.
View 1 Replies
View Related
Oct 11, 2010
i am trying to create a procedure that will take an input and based on that input it will delete or update a row, if the input is wrong we will dbs_ouput a line telling the user to enter the input again. can i have a input parameter in a procedure and insert multiple rows with a single insert on a procedure?
View 6 Replies
View Related
Jun 14, 2011
I have a procedure with 20 parameters, acutely it is to update a table and each param represents respective columns in a table. I want to update only few selected columns(random), as of now am passing Null as param values for remaining.
Is there any way to ignore the unnecessary parameters instead of passing NULL value.My Proc call looks like...
Exec MyProce(IN_ID, NULL, NULL, NULL,NULL,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);
Or
Exec MyProce(IN_ID, NULL, NULL, NULL,'SOME_VALUE' ,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);
View 4 Replies
View Related
Jun 22, 2010
In a procedure i need to pass date parameter, to check how to do this. I create a simple procedure, but it giving
PLS-00049: Bad Bind Variable 'FD'
PLS-00049: Bad Bind Variable 'TD'
How to resolve .......
CREATE PROCEDURE pass_date_parameter
AS
fd VARCHAR2 (10) DEFAULT '01-01-2010';
td VARCHAR2 (10) DEFAULT '01-06-2010';
ffd VARCHAR2 (10) DEFAULT '01-01-2010';
ttd VARCHAR2 (10) DEFAULT '01-06-2010';
[Code]....
View 19 Replies
View Related
Mar 23, 2012
we have a table which name is empl have two column (comapnyname,jobdate) i want output after passing parameter which name is month which value is like(feb 2011,or mar 2011 or apr 2011 or jun 2011 etc) output should come the nameof company with jobdate whose jobdate is equal to 3 or greate than three
name of company,jobdate whose job date in particular month is greater than three or equal to three the purpose of this we want to find only the name of company where we visit morethan three times in particular month table structure is
create table empl(companyname varchar2(10),jobdate date)
insert into empl values('ABC','1-feb-2011')
insert into empl values('ABC','10-feb-2011')
insert into empl values('ABC','21-feb-2011')
insert into empl values('xyz','18-feb-2011')
insert into empl values('xyz','1-feb-2012')
insert into empl values('xyz','1-apr-2011')
insert into empl values('ABC','28-feb-2012')
output:
after passing parameter month=feb 2011
companyname jobdate
abc 1-feb-2011
abc 10-feb-2011
abc 21-feb-2011
View 3 Replies
View Related
Oct 3, 2011
How can I pass multiple value in one parameter
example
select * from table
where table_cd in ('01','02','03','04')
here i want to put multiple value like above query by select 1 value in list
like when user select 'A'
THE VALUE PASS IN WHERE CLAUSE ('01','02','03')
FOR 'B' ('03','045','07')
FOR 'C'('044','046','078')
View 6 Replies
View Related
