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

Hi,
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.
LVL 3
mordiAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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';
0
 
Aaron ShiloChief Database ArchitectCommented:
hi

show parameter spfile;
select value from v$parameter where name = 'spfile';
0
 
mordiAuthor Commented:
I forgot to say: it should be done by a script which will be run on more than one instance.
0
 
Aaron ShiloChief Database ArchitectCommented:

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

0
 
mordiAuthor Commented:
Many thanks.
p.d. In windows you search for a backslash instead.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.