Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Save a pfile in the same directory where the spfile is located

Posted on 2011-03-02
Medium Priority
Last Modified: 2012-05-11
I need to save a pfile in the same directory where the spfile is located, by means of the create pfile from spfile command.
The question is how to extract the directory from the spfile value in v$system_parameter, and then use it in order to create the pfile from spfile in that same directory.
Question by:mordi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35016878

show parameter spfile;
select value from v$parameter where name = 'spfile';

Author Comment

ID: 35018981
I forgot to say: it should be done by a script which will be run on more than one instance.
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35019305

create pfile  ='$oracle_home/database/pfileINSTANCENAME.ora' from spfile.

LVL 35

Accepted Solution

johnsone earned 2000 total points
ID: 35022213
This query should generate the file name for you:

SELECT Substr(a.VALUE, 1, Instr(a.VALUE, '/', -1))
       || 'init'
       || b.NAME
       || '.ora'
FROM   v$parameter a,
       v$database b
WHERE  a.NAME = 'spfile';

Author Closing Comment

ID: 35055155
Many thanks.
p.d. In windows you search for a backslash instead.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question