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;
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
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.
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.
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.
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?
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
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.
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?
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?
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.
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?
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
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:
