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/SHAIK/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/SHAIK/adump
SQL> select value from v$spparameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/SHAIK/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/SHAIK/adump
SQL> select value from v$spparameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/backup1
No comments:
Post a Comment