Print Friendly, PDF & Email

 
REQUEST

To generate an initialization parameter file by querying a database.

 
RELATED

IS MY DATABASE USING PFILE OR SPFILE?

 
DETAILS

V$PARAMETER displays information about the initialization parameters that are currently in effect for the session.
V$SPPARAMETER displays information about the contents of the server parameter file.
V$SYSTEM_PARAMETER displays information about the initialization parameters that are currently in effect for the instance.

 
SOLUTION

SET LINES 300 
SET PAGES 999
COL "INITIALIZTION PARAMETERS" FOR A100
SELECT '*.' || NAME || '=' || DISPLAY_VALUE "INITIALIZTION PARAMETERS" FROM V$SYSTEM_PARAMETER WHERE ISDEFAULT='FALSE' ORDER BY 1;

INITIALIZTION PARAMETERS
----------------------------------------------------------------------------------------------------
*.audit_file_dest=/oracle/admin/DBPILOT/adump
*.audit_trail=DB
*.compatible=12.2.0
*.control_files=/oracle/oradata/DBPILOT/control01.ctl, /oracle/oradata/DBPILOT/control02.ctl
*.db_block_size=8192
*.db_name=DBPILOT
*.diagnostic_dest=/oracle
*.dispatchers=(PROTOCOL=TCP) (SERVICE=DBPILOTXDB)
*.local_listener=LISTENER_DBPILOT
*.nls_language=AMERICAN
*.nls_territory=AMERICA
*.open_cursors=300
*.pga_aggregate_target=9664M
*.processes=640
*.remote_login_passwordfile=EXCLUSIVE
*.sga_target=28992M
*.undo_tablespace=UNDOTBS1

The initialization file contains the same values

oracle@dbpilot.net: cat $ORACLE_HOME/dbs/initDBPILOT.ora
DBPILOT.__data_transfer_cache_size=0
DBPILOT.__db_cache_size=26172456960
DBPILOT.__inmemory_ext_roarea=0
DBPILOT.__inmemory_ext_rwarea=0
DBPILOT.__java_pool_size=134217728
DBPILOT.__large_pool_size=201326592
DBPILOT.__oracle_base='/oracle'#ORACLE_BASE set from environment
DBPILOT.__pga_aggregate_target=10133438464
DBPILOT.__sga_target=30400315392
DBPILOT.__shared_io_pool_size=536870912
DBPILOT.__shared_pool_size=3288334336
DBPILOT.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/DBPILOT/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/oracle/oradata/DBPILOT/control01.ctl','/oracle/oradata/DBPILOT/control02.ctl'
*.db_block_size=8192
*.db_name='DBPILOT'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBPILOTXDB)'
*.local_listener='LISTENER_DBPILOT'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=9664m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=28992m
*.undo_tablespace='UNDOTBS1'

 
 

Version  : 11:27 19.01.2018
Database : Standalone Oracle database 10g – 12cR2