alter system reset

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 included.

You may want to clear a parameter in the SPFILE so that upon the next database startup a default value is used.

清除spfile 文件中的参数，不能使用scope=memory 和 scope=both，  下次启动数据库的时生效。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter db_create_file_dest 

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_create_file_dest                  string

SQL> alter system set db_create_file_dest='/home/oracle';

System altered.

SQL> alter system reset db_create_file_dest;

System altered.

SQL> show parameter db_create_file_dest 

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_create_file_dest                  string
/home/oracle
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             377489360 bytes
Database Buffers           33554432 bytes
Redo Buffers                4288512 bytes
Database mounted.
Database opened.
SQL> show parameter db_create_file_dest 

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_create_file_dest                  string

SQL> 

连接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter system set db_create_file_dest='/home/oracle';

系统已更改。

SQL> alter system reset db_create_file_dest;
alter system reset db_create_file_dest
                                     *
第 1 行出现错误:
ORA-00905: 缺失关键字


SQL> alter system reset db_create_file_dest scope=spfile;
alter system reset db_create_file_dest scope=spfile
                                                  *
第 1 行出现错误:
ORA-00905: 缺失关键字


SQL> alter system reset db_create_file_dest sid=oratest1 scope=spfile;
alter system reset db_create_file_dest sid=oratest1 scope=spfile
                                           *
第 1 行出现错误:
ORA-01780: 要求文字字符串


SQL> alter system reset db_create_file_dest sid='oratest1' scope=spfile;
alter system reset db_create_file_dest sid='oratest1' scope=spfile
                                                      *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束


SQL> alter system reset db_create_file_dest scope=spfile sid='oratest1';
alter system reset db_create_file_dest scope=spfile sid='oratest1'
*
第 1 行出现错误:
ORA-32010: 无法在 SPFILE 中找到要删除的条目


SQL> alter system reset db_create_file_dest scope=spfile sid='*';

系统已更改。

在10g 里面 alter system reset 命令必须跟 scope 和 sid ，scope 在 sid 前面