View parameters of spfile in database. (v$spparameter)

DBAs sometime require to see the contents of spfile, and they are aware of converting spfile to pfile to see the contents like below


SQL> create pfile='/u01/oracle/product/pfile.txt' from spfile;

SQL> !

[oracle@localhost ~]$ gedit /u01/oracle/product/pfile.txt

The same above information can be seen at the database without converting the spfile to pfile by querying v$spparameter view.

SQL> select name,value from v$spparameter where isspecified != 'FALSE'
NAME                                          VALUE
----------------------------------- -------------------------------------------------------
processes                                       150
shared_pool_size                            79691776
large_pool_size                               4194304
java_pool_size                                4194304
streams_pool_size                          8388608
sga_target                                      209715200
control_files                                   /u01/oracle/control01.ctl
db_block_size                               8192
db_cache_size                              104857600
compatible                                    10.2.0.3.0
log_archive_dest_2                        service=catdb optional reopen=20
db_file_multiblock_read_count       16
db_recovery_file_dest                   /u01/oracle/product/10.2.0/db_1/flash_recovery_area
db_recovery_file_dest_size           2147483648
undo_management                        AUTO
undo_tablespace                           UNDOTBS1
remote_login_passwordfile           EXCLUSIVE
db_domain
dispatchers                                  (PROTOCOL=TCP) (SERVICE=catdbXDB)
job_queue_processes                 10
background_dump_dest              /u01/oracle/product/10.2.0/db_1/admin/catdb/bdump
user_dump_dest                         /u01/oracle/product/10.2.0/db_1/admin/catdb/udump
core_dump_dest                        /u01/oracle/product/10.2.0/db_1/admin/catdb/cdump
audit_file_dest                           /u01/oracle/product/10.2.0/db_1/admin/catdb/adump
db_name                                   catdb
open_cursors                            300
pga_aggregate_target                68157440

27 rows selected