ISDEFAULT

There is a column in V$PARAMETER called ISDEFAULT:

SQL> col name format a10
SQL> col value format a10
SQL> select name, value, isdefault
  2  from v$parameter
  3  where name = 'recyclebin'
  4  /

NAME       VALUE      ISDEFAULT
---------- ---------- ---------
recyclebin on         TRUE

SQL>

You might think that this tells you whether a particular parameter is set to its default value but this is not the case. If you add a recyclebin=on line to the parameter file and bounce the database, the same query runs as follows:

SQL> col name format a10
SQL> col value format a10
SQL> select name, value, isdefault
  2  from v$parameter
  3  where name = 'recyclebin'
  4  /

NAME       VALUE      ISDEFAULT
---------- ---------- ---------
recyclebin on         FALSE

SQL>

That’s because an ISDEFAULT value of FALSE simply means that the parameter has been included in the parameter file. You can see what I mean on the screen print below (click to enlarge it). On the left is a SQL*Plus session which lists those parameters with ISDEFAULT = FALSE and on the right is the database’s parameter file. You will see that they match: