Oracle pfile from sqlplus re-create

re-create pfile from sqlplus



set head off
set feed off
set pages 0
spool $ORACLE_HOME/dbs/initPROD.ora.anuj
SELECT
'######################################' ||CHR(10)||
'# file : init'||upper(value)||'.ora #' ||CHR(10)||
'# Date : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')|| ' #' ||CHR(10)||
'######################################' ||CHR(10) ||CHR(10)
FROM v$parameter
WHERE name = 'db_name';


SELECT '# '|| DESCRIPTION ||CHR(10) ,
'*.'||NAME||'='|| DECODE(VALUE,'TRUE',VALUE,'FALSE',VALUE,
DECODE(SIGN(INSTR(VALUE,' ')),
1,'( "'||REPLACE(VALUE,', ','",'||CHR(10)||' "')||'" )', '"'||VALUE||'"'))||
DECODE(upper(ISDEFAULT),'TRUE',' # (Default value) - ',NULL)|| CHR(10)
FROM v$parameter
WHERE ISDEFAULT = 'FALSE'
ORDER BY num;

spool off

*******************************************************
ISDEFAULT='FALSE' ---- means value via paramater file

*******************************************************