Differences between pfile and spfile in oracle

Differences between pfile and spfile in oracle pfile the default name is "init + routine name. ora file path:/app/oracle/product/10.2.0/dbs. This is a text file and can be opened using any text editing tool. The default spfile name is "spfile + routine name. ora file path:/app/oracle/product/10.2.0/dbs exists in the form of binary text. You cannot use the vi editor to modify the parameters. The two files can be created by using the create pfile from spfile or create spfile from pfile command: 1. the startup sequence of SPfile is higher than that of Pfile. 2. PFILE is a static file and will not take effect immediately after modification. The database must restart to read the file. 3. SPFILE is a dynamic parameter file and a binary file. You cannot directly use notepad or other programs to modify the file. You can use the ALTER command to modify the file without restarting the database. Modification Method: # view parameter information SQL> show parameter; # modify the parameter SQL> alter system set parameter name = value scope = parameter 2; parameter 2 has the following three values: 1. scope = spfile: Modify the parameters in the server initialization parameter file. The modified parameters will take effect the next time you start the database. Applies to dynamic and static initialization parameters. 2. scope = memory: the modification record of the parameter is stored in the memory, and the modification of the dynamic initialization parameter takes effect immediately. After the database is restarted, the loss is restored to the parameter value before modification. 3. scope = both: Modifications to parameters are recorded in both the server parameter file and the internal storage. Dynamic Parameters take effect immediately and cannot be used for static parameters. If the server parameter file is used, scope = both is the default option when the alter system statement is executed. If the server parameter file is not used, an error occurs when scope = spfile | both is specified when the alter system statement is executed. Appendix: parameters # Cache and I/O high-speed Cache and I/O ########################### ############### db_block_size = 8192 specify the data block size as 8 kbdb_cache_size = 33554432 and specify the data buffer as 32 MB, A larger value can reduce the number of I/O operations on database files, improve Efficiency db_file_multiblock_read_count = 16 ################################### ######### Cursors and Library Cache ###################### #################### open_cursors = 300 specify the maximum number of cursors that a session can open at a time as 300, this value should be set high enough to prevent the application from exhausting the opened cursor ############ ############################### Database Identification Database identifier #### ####################################### db_domain = "" The database domain name is mynet, add the database name db_name to form the global database name db_name = The Name Of The cicro database is myoracle ######################### ################### Diagnostics and Statistics diagnosis and Statistics ############### ########################### background_dump_dest =/opt/oracle/admin/cicro/bdump background process trace file directory core_dump_dest =/opt/oracle/admin/CRN O/cdump core dump tracking file directory timed_statistics = TRUE to collect the timing information of the operating system, this information can be used to optimize the database and SQL statement user_dump_dest =/opt/oracle/admin/cicro/udump user process tracking file directory ############# ############################## File Configurationcontrol_files = ("/opt/oracle /oradata/cicro/control01.ctl ", "/opt/oracle/oradata/cicro/control02.ctl", "/opt/oracle/oradata/cicro/control03.ctl ") specify the path and file name of the control file ################################# ################### ################################### Instance Identification network registration # ######################################## # instance_name = test routine name: test ############################### ############ Job Queues ######################### ################## job_queue_processes = 10 ################### ######################### configuration ID of the MTS multi-threaded server, in Oracle 9i, it is called the Shared Server Configuration ################################ ########## dispatchers = "(PROTOCOL = TCP) (SERVICE = testXDB) "multi-threaded server configuration #################################### ######## Miscellaneous ############################## ############ aq_tm_processes = 1 compatible = 9.2.0.0.0 compatible version 9.2.0 ################## ########################## Optimizer ############# ############################ hash_join_enabled = TRUEquery_rewrite_enabled = FALSEstar_transformation_enabled = FALSEdb_name = cicro # ###################### ######################################## ######################## Pools ############## ############################ java_pool_size = 83886080 specify the Java storage pool size as 32 MB, it is used to store Java methods, class definitions, and Java objects. Large_pool_size = 16777216 the size of a large pool is 1 MB, which is used to share the server's session memory, the message buffer for parallel execution, and the disk I/O buffer for RMAN backup and recovery. Shared_pool_size = 83886080 the size of the Shared Pool is 32 MB. It is used to store shared cursors, stored procedures, control structures, parallel execution message buffers, and other objects. A large value can improve the performance of multi-user systems. ######################################## #### Processes and Sessions ############################## ############ processes = 150 specify the maximum number of user processes that can be connected to an Oracle Server simultaneously ###### ###################################### Redo Log and Recovery redo log and Recovery ################################## ######### fast_start_mttr_target = 300 specify that the time required for recovery from the crash of a single data warehouse routine is 300 seconds ############## ############################## Security and Auditing Security and verification ################################## ######### remote_login_passwordfile = EXCLUSIVE specifies whether the operating system or password file has the permission to check the user password. Set to EXCLUSIVE. Enter the Database Password File to each user with permissions. ######################################## #### Sort, hash Joins, Bitmap Indexes sorting, Hash join, bitmap index ###################################### ##### pga_aggregate_target = 25165824sort_area_size = 524288 specify that the maximum memory used in the sorting area is kb. After sorting is completed, the rows are returned and the memory is released. Increasing this value can improve the efficiency of large-scale sorting. ######################################## #### Undo and Rollback Segments Managed by the System ####################### ################### undo_management = AUTO specifies that the Undo space management mode used by the system is SMU, in the SMU mode, undo space is allocated externally like undo tablespace. undo_retention = 10800 undo_tablespace = UNDOTBS1 specify the tablespace to be rolled back as UNDOTBS