Oracle reset initialization parameters

Oracle reset initialization parameter SQL> select * from v $ version where rownum <2; BANNER -------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionSQL>! Uname-a Linux rac1 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux www.2cto.com 1, resetSQL> select name, value, issys_modifiable from v $ parameter where name = 'open _ cursors '; name value ISSYS_MOD parameter --------- open_cursors 400 IMMEDIATESQL> alter system set open_cursors = 500 scope = both; System altered. SQL> show Parameter open_cursorsNAME type value ------------------------------------ ---------------------------------------- open_cursors integer 500 SQL> alter system reset open_cursors; System altered. SQL> show parameter open_cursorsNAME type value -------------------------------------- ------------------------------------ open_cursors integer 500 SQL> shutdown immediate Database sed. database d Ismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. total System Global Area 730714112 bytes Fixed Size 2216944 bytes Variable Size 515902480 bytes Database Buffers 209715200 bytes Redo Buffers 2879488 bytes Database mounted. database opened. SQL> show parameter open_cursorsNAME TYPE VALUE -------------------------------------- ------------------------------------ open_c Ursors integer 400 2, static parameter resetSQL> select name, value, issys_modifiable from v $ parameter where name = 'session '; name value ISSYS_MOD -------------------- -------------------------------- sessions 247 FALSESQL> alter system set sessions = 255 scope = spfile; System altered. SQL> show parameter sessionsNAME TYPE VALUE -------------------------------------- ---------------------------------------- ja Va_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 247 shared_server_sessions integer SQL> shutdown immediate Database closed. database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. total System Global Area 730714112 bytes Fixed Size 2216944 bytes Variable Size 51 5902480 bytes Database Buffers 209715200 bytes Redo Buffers 2879488 bytes Database mounted. database opened. SQL> show parameter sessionsNAME TYPE VALUE =----------- export java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 255 shar Ed_server_sessions integer SQL> alter system reset sessions; System altered. SQL> show parameter sessionsNAME type value =----------- export java_max_sessionspace_size integer 0 hour integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 255 shared_server_sessions integer SQL> sh Utdown immediate Database closed. database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. total System Global Area 730714112 bytes Fixed Size 2216944 bytes Variable Size 515902480 bytes Database Buffers 209715200 bytes Redo Buffers 2879488 bytes Database mounted. database opened. SQL> show parameter sessionsNAME TYPE VALUE --------------------------------------------- -- Export java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 247 shared_server_sessions integer we can read through the preceding two examples, to use the reset command to modify parameters to the default value, you must restart the database. In fact, the alter sysetm reset statement has a scope clause, but the scope can only be spfile, while the both and memory parameters do not exist. That is, when we perform the reset operation, the scope = spfile clause is used by default, this is also the reason why it takes effect only after a restart. The following is an official document: Clearing Initialization Parameter Values You can use the alter system reset command to clear (remove) the setting of any initialization parameter in the SPFILE that was used to start the instance. neither SCOPE = MEMORY nor SCOPE = BOTH are allowed. the SCOPE = SPFILE clause is not required, but can be attached ded. you may want to clear a parameter in the SPFILE so that upon the next database startup a default value is used.