Server Administration :: Change SGA_TARGET And SGA_POOL_SIZE Parameters
Aug 31, 2010
When I was going through the Enterprise Manager Grid Control, I found an error due to which I looked at the trace file and it said:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION unable_to_trans_pc PC:0x7C81BD02 ADDR:0x49444E49 UNABLE_TO_READ]
I searched and found that it has something to do with the SGA parameters. I saw that the shared_pool_size and the sga_target paramters are set to 0...Also there are certain SQLs hanging at some point. I thought I should change the above mentioned parameters.
My question now is, can I use the Alter System statements from the SQL Plus to change these parameters, and do they change immediately or do I need to reboot the Oracle instance for those changes to take effect? I would like to do:
alter system set sga_target=400m;
alter system set shared_pool_size=200m;
would these work and take effect immediately?
View 5 Replies
ADVERTISEMENT
Jul 11, 2011
I can not change the parameters RESOURCE_MANAGER_PLAN, at first, I set the parameters to DAYTIME,but when I restart my db,the parameters hold old values named MAXCAP_PLAN. Why?
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
[code]....
View 5 Replies
View Related
Jun 15, 2012
What value should i set for sga_target in my oracle 10g database?
Currently -
sga_max_size = 32GB
pga_aggregate_target = 6GB
RAM on server = 64 GB
I'll need to disable db_block_buffers parameters in order to enable sga_target. right?
View 8 Replies
View Related
Mar 19, 2013
I recently discovered that there was a difference in my QA and prod environments, which I have since rectified.
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 1
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
I found a script that allows me to compare values from v$parameter
set pagesize 1000
col name format a28
col local format a20
col remote format a20
select local.name, local.value local,
remote.value remote,
[code]....
Is there other SQL code or another methode out there that would find differences in my DB's such as the method_opt setting, which don't appear in v$parameter.
View 4 Replies
View Related
Oct 26, 2011
I created a view with parameters l_id and l_name, how can i find them in oracle view?
Create Table tb_test
(
Id Number,
Name Varchar2(64)
);
Create Or Replace View vw_tb_test(l_id,l_name)
As
Select Id,Name From tb_test;
View 3 Replies
View Related
Sep 15, 2011
What is the function by specified the size_clause of parameters method_opt?
dbms_stats.gather_table_stats
method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size_clause]
column|attribute [size_clause]
[,column|attribute [size_clause] ... ]
size_clause := SIZE [integer | auto | skewonly | repeat],
where integer is between 1 and 254
View 16 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
Feb 25, 2010
I had newly installed oracle 11g database, for testing & learning purpose.
Don't know wht happen to it after successfull installation , I am not able to start database.
Following error is appearing, how to resolve this.
Ora-01078:failure in processing system parameters
I can do only login to idle instance.
View 9 Replies
View Related
Oct 11, 2004
I have a small problem with creating the SPFILE,
These are the commands i had issued :-
1. startup nomount
2. create SPFILE from PFILE ;
Then i got an error :-
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:ORACLEORA92DATABASEINITORCL.ORA'
Ever since this happened i am unable to connect to ORACLE under any schema !
I get the following error : -
SQL> conn scott/tiger
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Warning: You are no longer connected to ORACLE.
WHY DOES THIS HAPPEN AND HOW CAN I OVERCOME IT ?
View 8 Replies
View Related
Feb 8, 2011
I successfully created a DB manually with PFILE.
I want the DB to startup with SPFILE at next login.So i fired the below command.
CREATE SPFILE='path/SPFILEPRAGAT.ORA' FROM PFILE='path/file.ora'.
But it shows the error as below:
ORA-01078: failure in processing system parameters
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (os 123) The filename, directory name,or volume label syntax is incorrect.
View 20 Replies
View Related
Feb 24, 2012
what's your method,when you want to change the character set ?
View 3 Replies
View Related
Aug 31, 2010
How to change db timezone parameter. My application team has asked me to change the db timzone parameter by following sql.
ALTER database SET TIME_ZONE = '+10:00';
Is this way is the correct one , and do we have any impact on database and does it require a db bounce.
View 2 Replies
View Related
Sep 21, 2012
As per the bellow the default XML storage has been changed in 11gr2.
docs.oracle.com/cd/E11882_01/appdev.112/e10492/whatsnew.htm
Quote:
Oracle Database 11g Release 2 (11.2.0.2) New Features in Oracle XML DB
The following Oracle XML DB features are new in Oracle Database 11g Release 2 (11.2.0.2).
Default Storage Model for XMLType
The default XMLType storage model is used if you do not specify a storage model when you create an XMLType table or column. Prior to Oracle Database 11g Release 2 (11.2.0.2), unstructured (CLOB) storage was used by default. The default storage model is now binary XML storage.
We have a application which works fine on r1 but not on r2 due to this change, we are going to investigate resolving the issue on the application in the future, in the mean time we need to be able to use the CLOB storage.
Does any one know where we can change this functionality back to the pre 11gR2 change?
View 7 Replies
View Related
Jan 25, 2013
Currently my oracle database character set is we8mswin1252 and it is only containing English data as well as spatial data (which is in English of course). I would like to change the Database character set so it could accept Arabic characters.
I have checked the below command on a test DB and it worked fine, but I want to know if it's recommended as a best practice when changing the character set to accept arabic and this won't corrupt my old entered Data ?
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256
SHUTDOWN IMMEDIATE
STARTUP
View 11 Replies
View Related
Feb 6, 2013
When I created a new oracle database the port number was 1521 (default), but I want to change it to 1522.
View 4 Replies
View Related
Feb 6, 2010
i have a oracle server installed enterprise edition, for some reation i need to degrade the version from enterprise to standard
i need same tablespace,data's everything same as of now.
View 8 Replies
View Related
Dec 21, 2010
Which config file is used to change the os version(RHEL 4.0 to RHEL 5.5) by OS admin what will be its impact on ORACLE Databases.
View 3 Replies
View Related
Nov 17, 2011
What is best practice to change small disk D:? I am beginner with Oracle. 10g on W2008. 5 datafiles (all indexes,second data file, 2 undotabs)*.dbf (34;30;1;34;12 GB) is on D:. Part of tablespaces (1 data, 1 undo)has files on c:.
I.
1.Shutdown 2008 server.
2.Copy D: image with GHOST to USB, network.
3.Connect new D, create RAID.
4.Restore image to D.
5.Start 2008 server.
II.
1.Stop application.
2.CONNECT AS SYSDBA
3.SHUTDOWN NORMAL or (IMMEDIATE)?
4.Copy files *.dbf at OS level from d: to ... USB disk, network.
5.Shutdown 2008 server.
6.Change disks, create RAID in BIOS.
7.Start W2008.
Is Oracle at this moment in SHUTDOWN mode?
8.Copy back *.dbf to new D: (with directory structure).
9.STARTUP Oracle.
View 1 Replies
View Related
Aug 21, 2011
This facility has one last 10g database and a very problematic tablespace and last datafile associated with it. The tablespace was set up with INITIAL_ EXTENT of 131,072 (128K) instead of the more 'normal' 4,194,304 (4M) and NEXT_EXTENT of 262,144 (256K) instead of 4,194,304 (4M).
More worryingly, the datafile has INCREMENT_BY set to 1 (8K) instead of 1,280 (10M) or 2,048 (16M).Has anyone ever updated sys.ts$.dflinit and sys.ts$. dflincr to modify the INITIAL_EXTENT and NEXT_EXTENT, and sys.file$.inc to modify the INCREMENT_BY?
View 7 Replies
View Related
Feb 26, 2013
1-how can i alter/change the size of tablespaces?.
2-is any changing in tablespace size will effect the over all performance?
Tablespace ; Size (MB); Free (MB); % Free; % Used
------------------------------;----------;----------;----------;----------
USERS ; 5; 4; 80; 20
SYSAUX ; 600; 140.875; 23; 77
UNDOTBS1 ; 640; 114.125; 18; 82
SYSTEM ; 700; 28.3125; 4; 96
TEMP ; 64; 0; 0; 100
View 4 Replies
View Related
Feb 22, 2013
SQL> update t set a = 1 where b = 2; -- must have redo record
2 rows updated.
SQL> rollback;
the above redo record that uncommit changed must be written from redo buffer to the online redo logfile. why Oracle write the redo record that uncommit changed to the online redo logfile ? when it will be used?
View 10 Replies
View Related
Nov 3, 2012
While renaming the datafile.
1) ALTER TABLESPACE .... OFFLINE;
2) CHANGING THE DATAFILE NAME IN OS LEVEL;
3) ALTER TABLESPACE .... RENAME DATAFILE 'FILE_OLD' TO 'FILE_NEW';
4) ALTER TABLESPACE .... ONLINE;
In the above steps, I HAD FORGOTTON TO FOLLOW THE 2ND STEP.
At the step of 3rd, i got error message, now i am not able to change the name in OS level Also.
View 19 Replies
View Related
May 11, 2012
I want to change cursor_sharing, open_cursors and session_cached_cursors parameter values for SPFILE (Only SPFILE) using SQL Query.
I have found following SQL Queries, but I don't know these queries are only for SPFILE or not.
ALTER SESSION SET cursor_sharing='EXACT';
ALTER system set open_cursors=2500;
ALTER SESSION set session_cached_cursors=70;
I need SQL Queries only for SPFILE.
View 3 Replies
View Related
Aug 15, 2013
How to change Character set for oracle 8i database. Is there anyway to change the Character set without affecting the current database.
View 11 Replies
View Related
Jul 10, 2013
I am currently in the process of migrating our database from US7ASCII to AL32UTF8 using DMU.
I am stuck at a point where I have encrypted data that, when the conversion happens, will be destroyed. Oracle suggests to move the data which is currently stored in VARCHAR2 datatype, to a "character set safe way" like hex notation or base64 before converting.
View 7 Replies
View Related
Aug 10, 2011
Does dbms_redefinition can move the tablespace to another of a table,including indexes?
View 4 Replies
View Related
Sep 29, 2012
I am going to change some parameters on primary database (db cache, java pool etc.). I am not sure, if the changes are automaticallly propagated to standby or if I have to make the changes manually on stanby site too? I couldn't find clear answer in documentation. how parameter changes work in dg physical configuration?
View 2 Replies
View Related
Mar 1, 2011
I want to change database character set from AL32UTF8 to WE8ISO8859P1.
what are the steps I need to follow without creating any new database with the character set as WE8ISO8859P1.
View 1 Replies
View Related
Feb 27, 2012
How to change the attributes of a table from nologging to logging?
SQL> select table_name,LOGGING from dba_tables where owner='HXL';
TABLE_NAME LOG
------------------------------ ---
TB_OBJECTS NO
SQL> alter table hxl.tb_test logging;
Table altered.
SQL> select table_name,LOGGING from dba_tables where owner='HXL';
TABLE_NAME LOG
------------------------------ ---
TB_OBJECTS NO
View 4 Replies
View Related
Oct 24, 2012
I want to change space allocation for character columns in my database, So it will store them as 'CHAR' and not 'BYTE'.my character set is
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL32UTF8
SQL> alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both;
System altered.I bounced the instance just to make sure
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
SQL>
SQL>
SQL> startup
ORACLE instance started.
[code].....
And then I want to see that when I create a table with some varchar2 column,The space for it will be allocated by chars, and not by bytes! However, when I run a check of create table, this is what I get:
SQL> drop table check_char;
Table dropped.
SQL> create table check_char (some_name varchar2(10));
Table created.
SQL> select a.char_used
2 from all_tab_columns a
3 where table_name='CHECK_CHAR'
4 and a.owner='SYS';
C
-
B
SQL>
What is the reason for space allocation to remain in BYTES and not CHAR, or what else I can check?
View 4 Replies
View Related
