NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string D:\ORA10G\DBS\SPFILEORA10G.ORA
2、更改动态参数: 直接使用alter system命令更改,即刻生效
SQL> show parameter undo_retention
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900 SQL> select name,value from v$spparameter where name='undo_retention';
NAME VALUE ------------------------------ ------------------------------ undo_retention
SQL> alter system set undo_retention=600;
系统已更改。
SQL> select name,value from v$spparameter where name='undo_retention';
NAME VALUE ------------------------------ ------------------------------ undo_retention 600
SQL> show parameter undo_retention
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 600
3、更改静态参数1:alter system命令更改spfile之后重启生效
SQL> show parameter spfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string D:\ORA10G\DATABASE\SPFILEORA10G.ORA
SQL> select name,value from v$parameter where name in ('processes','sessions');
NAME VALUE -------------------- ------------------------------ processes 22 sessions 29 --已自动更新=processes*1.1+5,与spfile中的设置不同
SQL> select name,value from v$spparameter where name in ('sessions','processes');
NAME VALUE -------------------- ------------------------------ processes 22 sessions 25
SQL> alter system set processes=25; alter system set processes=25 * 第 1 行出现错误: ORA-02095: 无法修改指定的初始化参数
Total System Global Area 121634816 bytes Fixed Size 1247636 bytes Variable Size 67110508 bytes Database Buffers 50331648 bytes Redo Buffers 2945024 bytes 数据库装载完毕。 数据库已经打开。 SQL> select name,value from v$parameter where name in ('processes','sessions');
NAME VALUE -------------------- ------------------------------ processes 25 sessions 32
SQL> select name,value from v$spparameter where name in ('sessions','processes') ;
NAME VALUE -------------------- ------------------------------ processes 25 sessions 25
4、更改静态参数2:使用pfile更改
SQL> create pfile='D:\ora10g\dbs\pfileora10g.ora' from spfile;
文件已创建。
SQL> --更改pfile: sessions=40 SQL> create spfile from pfile='D:\ora10g\dbs\pfileora10g.ora'; create spfile from pfile='D:\ora10g\dbs\pfileora10g.ora' * 第 1 行出现错误: ORA-32002: 无法创建已由实例使用的 SPFILE
Total System Global Area 121634816 bytes Fixed Size 1247636 bytes Variable Size 67110508 bytes Database Buffers 50331648 bytes Redo Buffers 2945024 bytes 数据库装载完毕。 数据库已经打开。 SQL> create spfile from pfile='D:\ora10g\dbs\pfileora10g.ora';
文件已创建。
SQL> show parameter spfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SQL> select name,value from v$spparameter where name in ('sessions','processes')
2 ;
NAME VALUE -------------------- ------------------------------ processes sessions
Total System Global Area 121634816 bytes Fixed Size 1247636 bytes Variable Size 67110508 bytes Database Buffers 50331648 bytes Redo Buffers 2945024 bytes 数据库装载完毕。 数据库已经打开。 SQL> show parameter spfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string D:\ORA10G\DATABASE\SPFILEORA10 G.ORA SQL> select name,value from v$spparameter where name in ('sessions','processes') ;
NAME VALUE -------------------- ------------------------------ processes 25 sessions 40
SQL> select name,value from v$parameter where name in ('processes','sessions');
NAME VALUE -------------------- ------------------------------ processes 25 sessions 40