Oracle re-create or recover  parameter file
..

set linesize 300 pagesize 300

column num noprint

col value for a100

col name for a35

select num, '*' "thread#", name, value from v$parameter where num in (select num from v$parameter 

WHERE (isdefault = 'FALSE' or ismodified <> 'FALSE') and name NOT LIKE 'nls%'

MINUS

select num from gv$parameter gvp, gv$instance gvi 

where 1=1

and num in   ( select distinct gvpa.num from gv$parameter gvpa, gv$parameter gvpb where gvpa.num = gvpb.num 

and  gvpa.value <> gvpb.value 

and (gvpa.isdefault = 'FALSE' or gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%'

              ) 

and gvi.inst_id = gvp.inst_id  

and (gvp.isdefault = 'FALSE' or gvp.ismodified <> 'FALSE') 

and gvp.name not like 'nls%')

union

select num, to_char(thread#) "thread#", name, value from gv$parameter gvp, gv$instance gvi 

where 1=1

and num in (select distinct gvpa.num from gv$parameter gvpa, gv$parameter gvpb 

where 1=1

and gvpa.num = gvpb.num 

and gvpa.value <> gvpb.value 

and (gvpa.isdefault = 'FALSE' or gvpa.ismodified <> 'FALSE') 

and gvp.name not like 'nls%'

           ) 

and gvi.inst_id = gvp.inst_id  

and (gvp.isdefault = 'FALSE' or gvp.ismodified <> 'FALSE') 

and gvp.name NOT LIKE 'nls%' 

order by 1, 2;

=============

Oracle re-create or recover parameter file

SELECT

initcap(p2.value) ||' startup with '|| DECODE(p1.value, NULL,'pfile', 'spfile') || ' on '|| to_char(startup_time,'dd/mon/yyyy hh24:mi:ss') "Instance startup info"

FROM v$parameter p1, v$parameter p2, v$instance

WHERE p1.name = 'spfile'

and p2.name='db_name';

-- NUMB

col name format a25

col value format a20

-- type is_defaul is_se is_system is_modified is_ad

col description format a20

set linesize 200  pagesize 200

select

nam.indx+1 numb,

nam.ksppinm name,

val.ksppstvl value,

nam.ksppity type,

val.ksppstdf is_default,

decode(bitand(nam.ksppiflg/256,1),1,'True','False') is_session_modifiable,

decode(bitand(nam.ksppiflg/65536,3),1,'Immediate',2,'Deferred' ,3,'Immediate','False') is_system_modifiable,

decode(bitand(val.ksppstvf,7),1,'Modified',4,'System Modified','False') is_modified,

decode(bitand(val.ksppstvf,2),2,'True','False') is_adjusted,

nam.ksppdesc description

from x$ksppi nam,

x$ksppsv val

where nam.indx = val.indx

/

select

nam.indx+1 numb,

nam.ksppinm name,

val.ksppstvl value,

nam.ksppity type,

val.ksppstdf is_default,

decode(bitand(nam.ksppiflg/256,1),1,'True','False') is_session_modifiable,

decode(bitand(nam.ksppiflg/65536,3),1,'Immediate',2,'Deferred' ,3,'Immediate','False') is_system_modifiable,

decode(bitand(val.ksppstvf,7),1,'Modified',4,'System Modified','False') is_modified,

decode(bitand(val.ksppstvf,2),2,'True','False') is_adjusted,nam.ksppdesc description

from x$ksppi nam,x$ksppcv val

where nam.indx = val.indx

;

/

===

set head off

set feed off

set pages 0

--spool $ORACLE_ADMIN/pfile/initPROD920.ora.sav

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;

SELECT '############### END ################' FROM DUAL;

--spool off;

======

col param_name format a40 heading "Parameter Name"

col param_value format a38 heading "Parameter Value" word_wrap

col isdefault format a7 heading "Default|Value"

col isses_modifiable format a10 heading "Session|Modifiable"

col issys_modifiable format a10 heading "System|Modifiable"

col ismod                          heading "Is|Modified"

-- Get database name and store in variable

column name new_value s_dbname

SELECT rtrim(name) name from v$database;

-- Get today's date

column today new_value s_curDate

SELECT to_char(sysdate, 'Month DD, YYYY') today from dual;

-- Get host name and store in variable

column host_name new_value s_machine

SELECT host_name from v$instance;

set termout on

set feedback off

set head on

select name param_name,value param_value,isdefault,ismodified ismod,isses_modifiable,issys_modifiable from v$parameter

order by param_name;

spool off

ttitle off

btitle off

clear columns

clear breaks

set feedback on

set termout on

set verify on

===

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

set linesize 300 pagesiz 300

col NAME for a45

col BVAL for a20

col EVAL for a20

select e.parameter_name             name

         , b.value                      bval

         , decode(b.value, e.value, NULL, e.value) eval

      from dba_hist_parameter b

         , dba_hist_parameter e

     where b.snap_id(+)         = :BgnSnap

       and e.snap_id            = :EndSnap

       and b.dbid(+)            = :DID 

       and e.dbid               = :DID 

       and b.instance_number(+) = 1

       and e.instance_number    = 1

       and b.parameter_hash(+)  = e.parameter_hash

       and (   nvl(b.isdefault, 'X')   = 'FALSE'

            or nvl(b.ismodified,'X')  != 'FALSE'

            or     e.ismodified       != 'FALSE'

            or nvl(e.value,0)         != nvl(b.value,0)

           )

       and e.parameter_name not like '\_\_%' escape '\'

     order by e.parameter_name;

 

----

 

set linesize 600 pagesize 300

col sid for a20

col name for a20

col value for a70

col host_name  for a15

select sid,SYS_CONTEXT('USERENV', 'HOST', 15) host_name ,name,value from v$spparameter where isspecified='TRUE' 

--and name='event' 

 order by 2;

==========

set linesize 300 pagesize 20

col NAME for a20

col VALUE for a20

col DESCRIPTION for a70

select name, value, isdefault, isses_modifiable, issys_modifiable,isinstance_modifiable, isdeprecated, description from v$parameter where upper(name) = 'PARALLEL_MAX_SERVERS';

set linesize 400 pagesize 300

col name for a30

SELECT name,

       CASE

         WHEN type = 1 THEN 'Boolean'

         WHEN type = 2 THEN 'String'

         WHEN type = 3 THEN 'Integer'

         WHEN type = 4 THEN 'Parameter file'

         WHEN type = 5 THEN 'Reserved'

         WHEN type = 6 THEN 'Big integer'

       END AS parameter_type,

       default_value

 FROM v$parameter

  WHERE ispdb_modifiable = 'TRUE' 

AND isdeprecated = 'FALSE'

   ORDER BY name;

=================

SELECT name,

           CASE

             WHEN type = 1 THEN 'Boolean'

             WHEN type = 2 THEN 'String'

             WHEN type = 3 THEN 'Integer'

             WHEN type = 4 THEN 'Parameter file'

             WHEN type = 5 THEN 'Reserved'

             WHEN type = 6 THEN 'Big integer'

           END AS parameter_type,

          default_value

    FROM v$parameter

     WHERE ispdb_modifiable = 'TRUE' AND isdeprecated = 'FALSE'

      ORDER BY name;