我们这里看一下有关这个参数的修改和调整方法。主要分为pfile调整方法和spfile调整方法。这里重点描述一下通过pfile调整时的“新奇诡异”特点。
我们这里要调整目标结果如下。
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
--------------- -------- ------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
1.初始化环境
创建secooler目录。
ora10g@secdb /home/oracle$ mkdir secooler
2.第一种调整方法:通过调整pfile调整UTL_FILE_DIR参数
这里给出三种可能调整方法,一个比一个“新奇诡异”。
1)将所有参数以逗号分隔的格式写在参数后面
(1)调整pfile参数
ora10g@secdb /home/oracle$ vi $ORACLE_HOME/dbs/initora10g.ora
……省略其他参数信息……
*.utl_file_dir='/home/oracle','/home/oracle/secooler'
~
~
(2)使用pfile启动数据库
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 21:06:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
sys@ora10g> shutdown abort
ORACLE instance shut down.
sys@ora10g> startup pfile='?/dbs/initora10g.ora';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
(3)验证参数调整效果
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
------------- ------- -------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
调整成功。
2)将所有参数以逗号分隔的格式写在pfile参数后面,同时参数内容两侧增加括号
(1)调整pfile参数
ora10g@secdb /home/oracle$ vi $ORACLE_HOME/dbs/initora10g.ora
……省略其他参数信息……
*.utl_file_dir=('/home/oracle','/home/oracle/secooler')
~
~
(2)使用pfile启动数据库
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 21:09:13 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
sys@ora10g> shutdown abort
ORACLE instance shut down.
sys@ora10g> startup pfile='?/dbs/initora10g.ora';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
(3)验证参数调整效果
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
------------- ------- -------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
可见这种带括号的不标准写法使用在pfile上依然有效。
3)将所有参数分多行写在pfile参数文件中
(1)调整pfile参数
ora10g@secdb /home/oracle$ vi $ORACLE_HOME/dbs/initora10g.ora
……省略其他参数信息……
*.utl_file_dir='/home/oracle'
*.utl_file_dir='/home/oracle/secooler'
~
~
(2)使用pfile启动数据库
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 21:18:39 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> shutdown abort
ORACLE instance shut down.
sys@ora10g> startup pfile='?/dbs/initora10g.ora';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
sys@ora10g>
(3)验证参数调整效果
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
------------- ------- -------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
可见,这种pfile非常诡异的写法Oracle也是支持的。按照正常的逻辑思维,在pfile中定义的参数应该以最后一次定义的内容为准,但是这个法则在UTL_FILE_DIR是无效的。这里体现了几遍分多行给出UTL_FILE_DIR参数的内容,每一行内容都会最终体现到参数调整结果中。
3.第二种调整方法:通过调整spfile调整UTL_FILE_DIR参数
1)使用spfile启动数据库,并确认UTL_FILE_DIR参数为空
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 21:30:04 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> shutdown abort;
ORACLE instance shut down.
sys@ora10g> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter spfile;
NAME TYPE VALUE
-------------- ------ ---------------------------------------------------------
spfile string /oracle/ora10gR2/product/10.2.0/db_2/dbs/spfileora10g.ora
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
-------------- ------- ---------------------------
utl_file_dir string
2)注意UTL_FILE_DIR参数是静态参数
sys@ora10g> alter system set utl_file_dir='/home/oracle','/home/oracle/secooler';
alter system set utl_file_dir='/home/oracle','/home/oracle/secooler'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
3)通过调整spfile方式调整UTL_FILE_DIR参数
sys@ora10g> alter system set utl_file_dir='/home/oracle','/home/oracle/secooler' scope=spfile;
System altered.
4)重启数据库是修改调整生效
sys@ora10g> shutdow abort;
ORACLE instance shut down.
sys@ora10g> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
5)确认调整成果
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
-------------- ------ -------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
调整成功。
6)注意事项
(1)不可以使用带括号的参数内容进行修改,否则会收到ORA-02095报错
sys@ora10g> alter system set utl_file_dir=('/home/oracle','/home/oracle/secooler') scope=spfile;
alter system set utl_file_dir=('/home/oracle','/home/oracle/secooler') scope=spfile
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
(2)不可以分多次修改
如果使用如下两条语句进行修改,将只有最后一次调整生效。
sys@ora10g> alter system set utl_file_dir='/home/oracle' scope=spfile;
System altered.
sys@ora10g> alter system set utl_file_dir='/home/oracle/secooler' scope=spfile;
System altered.
sys@ora10g> startup force;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
------------- ------- ----------------------------
utl_file_dir string /home/oracle/secooler
4.小结
调整UTL_FILE_DIR参数的最大的弊端是“必须重启数据库”,无论是通过pfile调整还是spfile调整,这对于7*24小时的生产数据库来说代价很高。因此这个参数的功能渐渐地将会被DIRECTORY数据库对象所取代,UTL_FILE_DIR参数也将逐渐退出Oracle世界。
通过这个过程体验一下通过pfile调整UTL_FILE_DIR参数的“神奇诡异”特点。
Good luck.
secooler
11.06.14
-- The End --
我们这里要调整目标结果如下。
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
--------------- -------- ------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
1.初始化环境
创建secooler目录。
ora10g@secdb /home/oracle$ mkdir secooler
2.第一种调整方法:通过调整pfile调整UTL_FILE_DIR参数
这里给出三种可能调整方法,一个比一个“新奇诡异”。
1)将所有参数以逗号分隔的格式写在参数后面
(1)调整pfile参数
ora10g@secdb /home/oracle$ vi $ORACLE_HOME/dbs/initora10g.ora
……省略其他参数信息……
*.utl_file_dir='/home/oracle','/home/oracle/secooler'
~
~
(2)使用pfile启动数据库
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 21:06:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
sys@ora10g> shutdown abort
ORACLE instance shut down.
sys@ora10g> startup pfile='?/dbs/initora10g.ora';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
(3)验证参数调整效果
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
------------- ------- -------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
调整成功。
2)将所有参数以逗号分隔的格式写在pfile参数后面,同时参数内容两侧增加括号
(1)调整pfile参数
ora10g@secdb /home/oracle$ vi $ORACLE_HOME/dbs/initora10g.ora
……省略其他参数信息……
*.utl_file_dir=('/home/oracle','/home/oracle/secooler')
~
~
(2)使用pfile启动数据库
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 21:09:13 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
sys@ora10g> shutdown abort
ORACLE instance shut down.
sys@ora10g> startup pfile='?/dbs/initora10g.ora';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
(3)验证参数调整效果
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
------------- ------- -------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
可见这种带括号的不标准写法使用在pfile上依然有效。
3)将所有参数分多行写在pfile参数文件中
(1)调整pfile参数
ora10g@secdb /home/oracle$ vi $ORACLE_HOME/dbs/initora10g.ora
……省略其他参数信息……
*.utl_file_dir='/home/oracle'
*.utl_file_dir='/home/oracle/secooler'
~
~
(2)使用pfile启动数据库
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 21:18:39 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> shutdown abort
ORACLE instance shut down.
sys@ora10g> startup pfile='?/dbs/initora10g.ora';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
sys@ora10g>
(3)验证参数调整效果
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
------------- ------- -------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
可见,这种pfile非常诡异的写法Oracle也是支持的。按照正常的逻辑思维,在pfile中定义的参数应该以最后一次定义的内容为准,但是这个法则在UTL_FILE_DIR是无效的。这里体现了几遍分多行给出UTL_FILE_DIR参数的内容,每一行内容都会最终体现到参数调整结果中。
3.第二种调整方法:通过调整spfile调整UTL_FILE_DIR参数
1)使用spfile启动数据库,并确认UTL_FILE_DIR参数为空
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 21:30:04 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> shutdown abort;
ORACLE instance shut down.
sys@ora10g> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter spfile;
NAME TYPE VALUE
-------------- ------ ---------------------------------------------------------
spfile string /oracle/ora10gR2/product/10.2.0/db_2/dbs/spfileora10g.ora
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
-------------- ------- ---------------------------
utl_file_dir string
2)注意UTL_FILE_DIR参数是静态参数
sys@ora10g> alter system set utl_file_dir='/home/oracle','/home/oracle/secooler';
alter system set utl_file_dir='/home/oracle','/home/oracle/secooler'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
3)通过调整spfile方式调整UTL_FILE_DIR参数
sys@ora10g> alter system set utl_file_dir='/home/oracle','/home/oracle/secooler' scope=spfile;
System altered.
4)重启数据库是修改调整生效
sys@ora10g> shutdow abort;
ORACLE instance shut down.
sys@ora10g> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
5)确认调整成果
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
-------------- ------ -------------------------------------
utl_file_dir string /home/oracle, /home/oracle/secooler
调整成功。
6)注意事项
(1)不可以使用带括号的参数内容进行修改,否则会收到ORA-02095报错
sys@ora10g> alter system set utl_file_dir=('/home/oracle','/home/oracle/secooler') scope=spfile;
alter system set utl_file_dir=('/home/oracle','/home/oracle/secooler') scope=spfile
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
(2)不可以分多次修改
如果使用如下两条语句进行修改,将只有最后一次调整生效。
sys@ora10g> alter system set utl_file_dir='/home/oracle' scope=spfile;
System altered.
sys@ora10g> alter system set utl_file_dir='/home/oracle/secooler' scope=spfile;
System altered.
sys@ora10g> startup force;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 92275512 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter utl_file_dir
NAME TYPE VALUE
------------- ------- ----------------------------
utl_file_dir string /home/oracle/secooler
4.小结
调整UTL_FILE_DIR参数的最大的弊端是“必须重启数据库”,无论是通过pfile调整还是spfile调整,这对于7*24小时的生产数据库来说代价很高。因此这个参数的功能渐渐地将会被DIRECTORY数据库对象所取代,UTL_FILE_DIR参数也将逐渐退出Oracle世界。
通过这个过程体验一下通过pfile调整UTL_FILE_DIR参数的“神奇诡异”特点。
Good luck.
secooler
11.06.14
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-700147/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-700147/