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.
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.
I often use "alter session set xxx=xxx;" command to change parameter value temporarily. After that, how to reset parameter value to default and I don't want logout sqlplus.
View 3 Replies View RelatedSQL> alter system set sga_max_size=1G scope=spfile;
System altered.
SQL> startup force
ORA-12545: Connect failed because target host or object does not exis
SQL> show parameter sga
ERROR:
ORA-03114: not connected to ORACLE
SQL> startup force
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> startup force
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
what's your method,when you want to change the character set ?
View 3 Replies View RelatedIt always used to be that Automatic Memory Management and Linux huge pages were incompatible: you had to use one or the other. But 11.2.0.2 has new parameter, USE_LARGE_PAGES. This isn't documented apart from a few articles on metalink, but Googling it suggests that if it is on TRUE (the default) or ONLY then I can use AMM with huge pages.
View 3 Replies View RelatedI have a question ragarding undo tablespace. I want to ask that why only undo tablespace information we need to specify in parameter file. We do not specify any other tablespace information. Not even for temporary tablespace. Then why we need to give undo tablespace name while instance is creating.
View 5 Replies View Relatedwhich initializaion parameter cant be change after creating the database?
View 5 Replies View RelatedWe have migrated database data from physical servers to virtual servers. i want to ensure all database parameters are set correctly in both physical and vblock servers. My question is what are all the parameters need to check and compare in both servers to ensure database from both servers ( physical/vblock ) are in sync.
Database used -- RAC database
OS used -- RHL 5.7
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?
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
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 Relatedi 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.
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]....
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 RelatedWhat 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.
I'm going to migrate my current db 9.2.0.8 (Timezone version is 1!) to a new server 11.2.0.3, so I was wondering if I must upgrade timezone version of my current 9iR2 to version 4! Before upgrade it...Btw my new server has timezone v14.
View 4 Replies View RelatedWhen I list the parameters with "show parameter", I get only numeric value for hash_area_size with no unit, what is the unit for that value and conversion of it to MegaBytes.
View 3 Replies View RelatedI have a simple question about database sessions. The value of parameter "sessions" is set to 500 and the users connect to database through an application server(Jboss). There are more than 500 users connect to the database through application.
My question is, how more than 500 users can connect to the database without any issue, if we set the value of "sessions" parameter to 500?
quick check on a hidden parameter? I need to know the default value and possible values for _serial_direct_read on various releases, I have only 11.2.0.3 available right now and I'ld like to know this for 11.2.x.x 11.1.x.x, and 10.2.x.x.
Below is the query that will show what it is currently which (unless you've changed it) will be the default: auto for my 11.2.0.3. Then to see the options, try to set it to something wrong, my options are false/true/never/auto/always. I think false/true were the only choices for 10.2 and are maintained only for compatibility. But I can't rememebnr 11.1.
orcl>
orcl> set lin 80
orcl> select KSPPSTVL from x$ksppcv join x$ksppi using (indx) where ksppinm='_serial_direct_read';
KSPPSTVL
--------------------------------------------------------------------------------
auto
orcl>
orcl> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
*
ERROR at line 1: ORA-00096: invalid value RUBBISH for parameter _serial_direct_read, must be from among false, true, never, auto, always
orcl>
update: added 11.1.x.x to my wishlist
I am getting the following error while starting database.
ORA-32004:obsolete and/or deprecated parameter(s) specified
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?
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?
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
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?
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.
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 RelatedI 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.
Does dbms_redefinition can move the tablespace to another of a table,including indexes?
View 4 Replies View RelatedHow oracle deals with the setting of init.ora parameter compatible.
e.g. we have
DB Version=10.2.0.4
compatible=10.2.0
what is the possible leak for us ? Is there any description about the features changed in 10.2 up to 10.2.0.4 in all steps - so that we can check out which functions we do not have.