用过Oracle的朋友都知道,现在绝大部分Oracle数据库使用的是基于服务器的参数文件。对于查询当前数据库实例级别,system级别,session级别参数的具体设置有些时候很容易混淆。本文主要介绍了Oracle数据库参数使用的相关视图(V$PARAMETER,V$PARAMETER2,V$SY
用过Oracle的朋友都知道,现在绝大部分Oracle数据库使用的是基于服务器的参数文件。对于查询当前数据库实例级别,system级别,session级别参数的具体设置有些时候很容易混淆。本文主要介绍了Oracle数据库参数使用的相关视图(V$PARAMETER,V$PARAMETER2,V$SYSTEM_PARAMETER,V$SYSTEM_PARAMETER2,V$SPPARAMETER),以及通过查询Oracle视图来获得不同情形下的参数值并进行对比。
1、对比有关参数视图
b、V$PARAMETER2
c、V$SYSTEM_PARAMETER
d、V$SYSTEM_PARAMETER2
e、V$SPPARAMETER
a、查看session级别的参数 --基于session级别可以直接使用show parameter 方式 --演示环境 scott@USBO> select * from v$version where rownum<2; BANNER ------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production scott@USBO> show parameter cursor_shar NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ cursor_sharing string EXACT scott@USBO> select name,value,isses_modifiable,issys_modifiable,isinstance_modifiable 2 from v$parameter where name='cursor_sharing'; NAME VALUE ISSES_MODIFIABL ISSYS_MODIFIABLE ISINSTANCE_MODI ------------------------------ --------------- --------------- --------------------------- --------------- cursor_sharing EXACT TRUE IMMEDIATE TRUE scott@USBO> alter session set cursor_sharing='SIMILAR'; --->上面的查询可知session级别为true,即可修改 Session altered. scott@USBO> show parameter cursor_shar -->再次查看,修改已经生效 NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ cursor_sharing string SIMILAR b、查看基于实例级别的参数 --基于实例级别的参数需要通过V$SYSTEM_PARAMETER访问 scott@USBO> select name,value from v$system_parameter where name='cursor_sharing'; NAME VALUE ------------------------------ --------------- cursor_sharing EXACT --->可以看到此时的值依旧是exact,并没有受到之前修改的影响 scott@USBO> alter system set cursor_sharing='FORCE'; --->将参数cursor_sharing进行基于系统级别进行修改 System altered. scott@USBO> show parameter cursor_sharing --->此时可以看到当前session级别该参数从system级别派生 NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ cursor_sharing string FORCE scott@USBO> alter session set cursor_sharing='EXACT'; --->再次基于session级别进行修改 Session altered. scott@USBO> show parameter cursor_shar --->此时查询到session的值变为EXACT,那system级别呢?肯定是FORCE,大家自行验证 NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ cursor_sharing string EXACT c、演示v$parameter与v$parameter2的差异 scott@USBO> show parameter control_files; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u03/database/usbo/oradata/con trol01.ctl, /u03/database/usbo /oradata/control02.ctl scott@USBO> select name,value from v$parameter where name='control_files'; NAME VALUE ------------------------------ ------------------------------------------------------------ control_files /u03/database/usbo/oradata/control01.ctl, /u03/database/usbo /oradata/control02.ctl --查询v$parameter2时,对于存在多个参数值的参数,在这个视图中分为多行进行列出,如下 --对于v$system_parameter与v$system_parameter2存在类似的情形,不再演示 scott@USBO> select name,value from v$parameter2 where name='control_files'; NAME VALUE ------------------------------ ------------------------------------------------------------ control_files /u03/database/usbo/oradata/control01.ctl control_files /u03/database/usbo/oradata/control02.ctl d、关于v$spparameter --该视图可以用于判断当前数据库使用的是pfile还是spfile来启动 scott@USBO> select distinct isspecified from v$spparameter; ISSPECIFIED ------------------ TRUE --->第一个值为TRUE,spfile启动 FALSE goex_admin@MFTST> select distinct isspecified from v$spparameter; ISSPEC ------ FALSE --->为false,pfile文件启动数据库 goex_admin@MFTST> select name,value from v$spparameter where name='control_files'; NAME VALUE ------------------------- ------------------------------------------------------------ control_files --->由于为pfile启动,所以查询不到任何信息 goex_admin@MFTST> select count(*) from v$spparameter where isspecified='TRUE'; COUNT(*) ---------- --->列isspecified没有一个值为TRUE,同样是因为使用了pfile 0 对于spfile启动下的v$spparameter,对于存在多个参数值的参数,在这个视图中也分为多行进行列出 scott@USBO> select name,value from v$spparameter where name='control_files'; NAME VALUE ------------------------------ ------------------------------------------------------------ control_files /u03/database/usbo/oradata/control01.ctl control_files /u03/database/usbo/oradata/control02.ctl

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SublimeText3 English version
Recommended: Win version, supports code prompts!

SublimeText3 Chinese version
Chinese version, very easy to use

Dreamweaver Mac version
Visual web development tools

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft