32 - [Oracle 19C] V$SYSTEM_PARAMETER vs V$SYSTEM_PARAMETER2

 

V$SYSTEM_PARAMETER is the view which shows instance level parameters 
(and these are what all new sessions inherit)
ISDEFAULT Indicates 
(TRUE) - whether the parameter is set to the default value from the instance
or
(FALSE)- the parameter value was specified in the parameter file (spfile or pfile)  

 

 

col name for a50
col value for a50
col isses_modifiable for a20
col issys_modifiable for a20
col ispdb_modifiable for a20

SQL> select name,value,ISDEFAULT,isses_modifiable , issys_modifiable , ispdb_modifiable ,con_id
from V$SYSTEM_PARAMETER
order by name


NAME                                     VALUE                                 ISDEFAULT ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE         CON_ID
---------------------------------------- ------------------------------------- --------- -------------------- -------------------- -------------------- ----------
DBFIPS_140                               FALSE                                 TRUE      FALSE                FALSE                FALSE                         0
active_instance_count                                                          TRUE      FALSE                FALSE                FALSE                         0
adg_account_info_tracking                LOCAL                                 TRUE      FALSE                FALSE                TRUE                          0
adg_redirect_dml                         FALSE                                 TRUE      FALSE                IMMEDIATE            FALSE                         0
allow_global_dblinks                     FALSE                                 TRUE      FALSE                IMMEDIATE            FALSE                         0
allow_group_access_to_sga                FALSE                                 TRUE      FALSE                FALSE                FALSE                         0
allow_rowid_column_type                  FALSE                                 TRUE      TRUE                 IMMEDIATE            TRUE                          0
approx_for_aggregation                   FALSE                                 TRUE      TRUE                 IMMEDIATE            TRUE                          0
approx_for_count_distinct                FALSE                                 TRUE      TRUE                 IMMEDIATE            TRUE                          0
approx_for_percentile                    none                                  TRUE      TRUE                 IMMEDIATE            TRUE                          0
aq_tm_processes                          1                                     TRUE      TRUE                 IMMEDIATE            TRUE                  
archive_lag_target                       0                                     TRUE      FALSE                IMMEDIATE            FALSE                         0
asm_diskstring                                                                 TRUE      TRUE                 IMMEDIATE            TRUE                          0
asm_preferred_read_failure_groups                                              TRUE      FALSE                IMMEDIATE            FALSE                         0
audit_file_dest                          /u01/app/oracle/admin/orcl/adump      FALSE     FALSE                DEFERRED             FALSE                         0
audit_sys_operations                     TRUE                                  TRUE      FALSE                FALSE                FALSE                         0
audit_syslog_level                                                             TRUE      FALSE                FALSE                FALSE                         0
audit_trail                              DB                                    FALSE     FALSE                FALSE                FALSE                         0
*/

 

 

 

--(FALSE)- the parameter value was specified in the parameter file (spfile or pfile)  

select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$SYSTEM_PARAMETER
where ISDEFAULT='FALSE' 
AND CON_ID=0
order by name

 

 

 

-- what is difference between V$SYSTEM_PARAMETER and V$PARAMETER

alter session 명령어로는 V$SYSTEM_PARAMETER 값을 변경할 수 없다

 

SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$SYSTEM_PARAMETER
where name='nls_date_format'

NAME               VALUE           ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE     ISDEFAULT     CON_ID
------------------ --------------- -------------------- -------------------- -------------------- --------- ----------
nls_date_format                    TRUE                 FALSE                TRUE                 TRUE               0


SQL> alter session set nls_date_format='dd-mm-yyyy'

Session altered.


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,con_id
from V$SYSTEM_PARAMETER
where name='nls_date_format'

NAME                VALUE        ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE         CON_ID
------------------- ------------ -------------------- -------------------- -------------------- ----------
nls_date_format                  TRUE                 FALSE                TRUE                          0


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,con_id
from V$PARAMETER
where name='nls_date_format'

NAME                VALUE         ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE         CON_ID
------------------- ------------- -------------------- -------------------- -------------------- ----------
nls_date_format     dd-mm-yyyy    TRUE                 FALSE                TRUE                          1

 

 

 

V$SYSTEM_PARAMETER2 is the view which shows instance level parameters 
(and these are what all new sessions inherit)
--but it is more readable in case the value has many lines

 

SWQ> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,con_id
from V$SYSTEM_PARAMETER2
where name ='control_files'

NAME              VALUE                                          ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE         CON_ID
----------------- ---------------------------------------------- -------------------- -------------------- -------------------- ----------
control_files     /u01/app/oracle/oradata/ORCL/control01.ctl     FALSE                FALSE                FALSE                         0
control_files     /u01/app/oracle/oradata/ORCL/control02.ctl     FALSE                FALSE                FALSE                         0