在执行该命令时出现下列错误

SYS@PROD>alter system set UTL_FILE_DIR='/home/oracle','/home/oracle/temp','/home/oracle/scripts' scope=spfile;
alter system set UTL_FILE_DIR='/home/oracle','/home/oracle/temp','/home/oracle/scripts' scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use

解决：

1.查看是否有spfile

SYS@PROD>show parameter spfile;

NAME                                 TYPE                             VALUE                      
------------------------------------ ---------------------
spfile                                   string

 

2.从pfile创建spfile

SYS@PROD>create spfile from pfile;

File created.

 

3.关闭数据库

SYS@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

 

4.启动数据库

SYS@PROD>startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             541068368 bytes
Database Buffers          289406976 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.


5.查看是否有spfile文件

SYS@PROD>show parameter spfile;

NAME                                 TYPE                    VALUE
------------------------------------ ----------------------
spfile                               string                 01/app/oracle/product/11.2.0/db_1/dbs/spfilePROD.ora
6.再次执行该命令，问题解决

SYS@PROD>alter system set UTL_FILE_DIR='/home/oracle','/home/oracle/temp','/home/oracle/scripts' scope=spfile;

System altered.