Oracle 11g新特性-初始化文件创建（Memory）
Oracle 11g对于初始化增加了一个特别有用的功能，它可以根据内存中的参数设置来创建初始化参数文件。
Oracle从9i推出SPFILE之后，就给出了使用PFILE创建SPFILE以及由SPFILE创建PFILE的语法。
在Oracle 11g中，Oracle增强了这种语法，使得创建PFILE或SPFILE时，不在需要指定一个物理的文件，而是可以从当前内存中的设置来获取参数配置。
采用这种方式创建的PFILE或SPFILE，可以保证获得的参数就是当前运行的参数，而如果从PFILE或SPFILE则无法确保文件中的参数设置与数据库运行的参数设置一致。
语法很简单，将创建时的FROM语句后面的文件类型PFILE或SPFILE改为MEMORY就可以了。
[oracle@yangtk ~]$ sqlplus "/ as sysdba"
|
1
2
3
4
5
|
SQL*Plus: Release
11.1.
0.6.
0
- Production
on
Thu Jan
10
15
:
06
:
30
2008
Copyright (c)
1982
,
2007
, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release
11.1.
0.6.
0
- Production
With the Partitioning, OLAP, Data Mining
and
Real Application Testing options
|
SQL> create pfile='/home/oracle/initora11g_p.ora' from memory;
File created.
SQL> create spfile='/home/oracle/spfileora11g_p.ora' from memory;
File created.
注意：上面的这种方式要求数据库至少处于NOMOUNT状态，否则会出现报错：
SQL> shutdown immediate
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile=
'/home/oracle/initora11g_p.ora'
from
memory;
create pfile=
'/home/oracle/initora11g_p.ora'
from
memory
*
ERROR
at
line
1
:
ORA
-00922
: missing
or
invalid option
SQL> create spfile=
'/home/oracle/spfileora11g_p.ora'
from
memory;
create spfile=
'/home/oracle/spfileora11g_p.ora'
from
memory
*
ERROR
at
line
1
:
ORA
-00922
: missing
or
invalid option
SQL> startup nomount
ORACLE instance started.
Total System Global Area
267825152
bytes
Fixed Size
1299316
bytes
Variable Size
176163980
bytes
Database Buffers
88080384
bytes
Redo Buffers
2281472
bytes
SQL> create pfile=
'/home/oracle/initora11g_p.ora'
from
memory;
File created.
SQL> create spfile=
'/home/oracle/spfileora11g_p.ora'
from
memory;
File created.
|
案例：
[root@rh6 ~]# su - oracle
[oracle@rh6 ~]$ export ORACLE_SID=test1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
[oracle@rh6 ~]$ sqlplus
'/as sysdba'
SQL*Plus: Release
11.2.
0.1.
0
Production
on
Mon Feb
24
16
:
22
:
52
2014
Copyright (c)
1982
,
2009
, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release
11.2.
0.1.
0
- Production
With the Partitioning, OLAP, Data Mining
and
Real Application Testing options
16
:
22
:
52
SYS@ test1 >create spfile=
'/home/oracle/spfiletest1.ora'
from
memory;
File created.
[oracle@rh6 ~]$ strings /home/oracle/spfiletest1.ora |more
*.__db_cache_size=16M
*.__java_pool_size=24M
*.__large_pool_size=32M
*.__oracle_base=
'/u01/app/oracle'
# ORACLE_BASE set
from
environment
*.__pga_aggregate_target=10M
*.__sga_target=280M
*.__shared_io_pool_size=
0
*.__shared_pool_size=128M
*.__streams_pool_size=
0
*._aggregation_optimization_settings=
0
*._always_anti_join=
'CHOOSE'
*._always_semi_join=
'CHOOSE'
*._and_pruning_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_prun
ing_enabled=TRUE
*._complex_view_merging=TRUE
*._compression_compatibility=
'11.2.0'
*._connect_by_use_union_all=
'TRUE'
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=
0
*._dimension_skip_null=TRUE
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_se
mi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._ksb_restart_policy_times=
'0'
*._ksb_restart_policy_times=
'60'
*._ksb_restart_policy_times=
'120'
*._ksb_restart_policy_times=
'240'
# internal update to set default
*._left_nested_loops_random=TRUE
*._local_communication_costing_enabled=TRUE
*._minimal_stats_aggregation=TRUE
......
以上为截取的部分spfile里的内容 。
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1362813，如需转载请自行联系原作者
