Difference between V$parameter and v$spparameter

V$PARAMETER
It displays the information about initialization parameters that are currently in effect for the session.

V$SYSTEM_PARAMETER
In this view it displays what will be the value if a new session inherits parameter values from the instance-wide values.

V$SPPARAMETER
It displays the information about contents of the server parameter file. If a server parameter file was not used to start the instance, then ISSPECIFIED column contains FALSE value.

V$SYSTEM_PARAMETER2
It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view.

You may mess up in this stage. An example will make you clear through these parameter as well as ALTER SYSTEM SET value settings.

Let's experiment over audit_file_dest parameter.

SQL> show parameter audit_file_dest

NAME                        TYPE           VALUE
------------------          ------        --------------------------------------
audit_file_dest             string /oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

Only setting this in memory.
SQL> alter system set audit_file_dest='/oradata2' DEFERRED scope=memory;
System altered.

SQL> select value from V$SYSTEM_PARAMETER where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from V$SYSTEM_PARAMETER2 where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from v$parameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

SQL> select value from v$spparameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

Change only in spfile.
SQL> alter system set audit_file_dest='/backup1' scope=spfile;
System altered.

SQL> select value from V$SYSTEM_PARAMETER where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from V$SYSTEM_PARAMETER2 where name='audit_file_dest';

VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from v$parameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

SQL> select value from v$spparameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/backup1

Related Documents
Using ALTER SYSTEM to Change Initialization Parameter Values