11.2.0.3/Solaris
I want to clear the value set for LOCAL_LISTENER and set it to null. As per doc, I have to use RESET command. But RESET works only with SCOPE= SPFILE . ie. I have to bounce the DB to bring this change into effect.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl.ora
SQL>
SQL> show parameter LOCAL_LISTENER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=TCP)(HOST=192.168.0.182)(POR
T=1892)))
SQL>
SQL>
SQL>
SQL> alter system reset LOCAL_LISTENER scope=both;
alter system reset LOCAL_LISTENER scope=both
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported
SQL> alter system reset LOCAL_LISTENER ;
System altered.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl.ora
SQL>
------ Still showing the old value as a bounce is required
SQL> show parameter LOCAL_LISTENER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=TCP)(HOST=192.168.0.182)(POR
T=1892)))
Is setting the parameter to empty string supported in Oracle . It seems to have worked for me, But i would like to know if it is recommended.
SQL> show parameter LOCAL_LISTENER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=TCP)(HOST=192.168.0.182)(POR
T=1892)))
SQL> alter system set LOCAL_LISTENER = '' scope = both;
System altered.
SQL> show parameter LOCAL_LISTENER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
SQL>
The below article says it is not recommeded to use empty string. But , what other option do I have to clear a parameter's value without bouncing the DB ?
http://coskan.wordpress.com/2007/03/07/setting-or-resetting-unsetting-parameters/
Edited by: greeny on 12-Nov-2012 06:25