Currently, I am using an SPFILE :
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -ltr
total 32
-rw-r--r-- 1 oracle oracle 2851 May 16 2009 init.ora
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_DBUA0_0
-rw-rw---- 1 oracle oracle 1544 Oct 30 2009 hc_DBUA0.dat
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_orcl_0
-rw-rw---- 1 oracle oracle 24 Apr 7 2010 lkORCL
-rw-r----- 1 oracle oracle 1536 Jul 7 2010 orapworcl
-rw-rw---- 1 oracle oracle 1544 May 15 12:59 hc_orcl.dat
-rw-r----- 1 oracle oracle 2560 May 15 12:59 spfileorcl.ora
[oracle@localhost dbs]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 14:27:26 2011
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
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/app/oracle/produc
t/11.2.0/dbhome_1/dbs/spfileor
cl.ora
SQL>
If I create a PFILE, I have 25 lines of parameters :
(UPDATE : As suggested by Coskan, use "create pfile=/tmp/init.ora from spfile;" so as to not overwrite an existing pfile -- e.g. if it happens to be different from the current set of processes.)
But what if I want to see all the "hidden" parameters ? I could query some view.
SQL> create pfile from spfile;
File created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ ls -ltr|tail
total 36
-rw-r--r-- 1 oracle oracle 2851 May 16 2009 init.ora
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_DBUA0_0
-rw-rw---- 1 oracle oracle 1544 Oct 30 2009 hc_DBUA0.dat
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_orcl_0
-rw-rw---- 1 oracle oracle 24 Apr 7 2010 lkORCL
-rw-r----- 1 oracle oracle 1536 Jul 7 2010 orapworcl
-rw-rw---- 1 oracle oracle 1544 May 15 12:59 hc_orcl.dat
-rw-r----- 1 oracle oracle 2560 May 15 12:59 spfileorcl.ora
-rw-rw-r-- 1 oracle oracle 959 May 15 14:28 initorcl.ora
[oracle@localhost dbs]$ wc -l spfileorcl.ora
25 spfileorcl.ora
[oracle@localhost dbs]$ wc -l initorcl.ora
25 initorcl.ora
[oracle@localhost dbs]$
But what if I want to see all the "hidden" parameters ? I could query some view.
I could also do this :
(UPDATE : As suggested by Coskan, use "create pfile=/tmp/init.ora from memory;" so as to not overwrite an existing pfile -- e.g. if it happens to be different from the current set of processes.)
I now have 163 entries in my PFILE !
And these contain 137 parameters beginning with the "_" character :
I can also create an SPFILE with all the parameters :
The "CREATE SPFILE || PFILE FROM MEMORY" is a new command in 11g.
UPDATE : You can still query the V$PARAMETER, V$SYSTEM_PARAMETER (and V$PARAMETER2, V$SYSTEM_PARAMETER2) views WHERE ISDEFAULT=TRUE to get the default values (of "normal" parameters) that you hadn't put in your parameter file.
I leave it to you to find the reasons for the slight discrepancies in line counts.
[oracle@localhost dbs]$ rm initorcl.ora
[oracle@localhost dbs]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 14:30:07 2011
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
SQL> create pfile from memory;
File created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ ls -l initorcl.ora
-rw-rw-r-- 1 oracle oracle 5625 May 15 14:30 initorcl.ora
[oracle@localhost dbs]$ wc -l initorcl.ora
163 initorcl.ora
[oracle@localhost dbs]$
I now have 163 entries in my PFILE !
And these contain 137 parameters beginning with the "_" character :
[oracle@localhost dbs]$ strings -a initorcl.ora |grep '^\_' | wc -l
137
[oracle@localhost dbs]$
I can also create an SPFILE with all the parameters :
[oracle@localhost dbs]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 14:36:38 2011
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
SQL> create spfile='spfileorcl.bck' from memory;
File created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ wc -l spfileorcl.bck
164 spfileorcl.bck
[oracle@localhost dbs]$
[oracle@localhost dbs]$ strings -a spfileorcl.bck | grep '^\*\.\_' | wc -l
136
[oracle@localhost dbs]$
The "CREATE SPFILE || PFILE FROM MEMORY" is a new command in 11g.
UPDATE : You can still query the V$PARAMETER, V$SYSTEM_PARAMETER (and V$PARAMETER2, V$SYSTEM_PARAMETER2) views WHERE ISDEFAULT=TRUE to get the default values (of "normal" parameters) that you hadn't put in your parameter file.
I leave it to you to find the reasons for the slight discrepancies in line counts.
.
.
.
4 comments:
For 'CREATE PFILE/SPFILE FROM MEMORY' v$system_parameter4 is the source. When the above command is executed, oracle internally triggers the following command:
select num,name,type,display_value,update_comment from v$system_parameter4 order by lower(name),ordinal.
References (MOS) 784133.1 & 464781.1
Regards,
Jaffar
I think it might be better if you use pfile='/tmp/init.ora' instead of just "pfile from spfile" so readers won't accidently overwrite their init files which points to shared spfile (did that couple of time in my early career :))
SELECT * FROM SYS.X$KSPPI;
Anonymous,
Yes, I do know X$KSPPI. I have been querying this in earlier versions.
Howerver, in 11g, the CREATE SPFILE | PFILE FROM MEMORY is a "neater" method, imho.
Post a Comment