Sort related concepts in oralce and oralce

Sort related concepts in oralce and oralce

【Database Name]

Concept:It is a database identifier, which is equivalent to our ID card. If multiple databases are installed on one machine, each database has a database name, when these database names are created, the database names are also written to the control file, which is stored in binary files, it is not very convenient to modify the database name, so we should try to make it clear when starting the database name.

Purpose:The database name is used when creating a database, installing a database, creating a database control file, modifying the data structure, or backing up a database.

To view the Database Name of the current connection in sqlplus:

Method 1: select name from v $ database;

Method 2: shower parameter db;

Method 3: View All parameter files.

【Database instance name]

Concept: an instance is a part of the computer memory required to access the oracle database and assists in processing computer background processes. It is a collection of processes and the memory used by these processes. It does not need a database associated with it, and can start the instance without any data files.

[Start an oracle database instance in sqlplus]

SQL> connect system/manager as sysdba; connected to the idle routine. SQL> startup open finance; the ORACLE routine has been started. Total System Global Area 1249902592 bytes Fixed Size 1292132 bytes Variable Size 318769308 bytes Database Buffers 922746880 bytes Redo Buffers 7094272 bytes Database load is complete. The database has been opened.

【 Stop an oracle database instance in sqlplus]

SQL> connect system/manager as sysdba; connected. SQL> shutdown immediate; the database has been closed. The database has been detached. The ORACLE routine has been disabled.

【 View the name of an oracle database instance in sqlplus]

SQL>select instance_name from v$instance;

When you access the oracle database, you can access an instance. However, if the instance is associated with a database file, it is accessible. If it is not, there will be an error indicating that the instance does not exist.

Instance name refers to the name of the database management system used to respond to a database operation. The term is "SID ".

Used for external connections. To get in touch with the database in the operating system, you must use the database instance name.

[Differences between instances and databases]

Q: The instance is temporary and exists only when the related process and memory set exist. The database is permanent and the file exists.

Q: A single database can be installed and opened for an instance during its lifetime. The database can be installed and opened by multiple instances, or installed and opened by one instance, or installed and opened by multiple instances at the same time.

[Database Service name]

Previously, we used SID to represent an instance of the database. However, in the parallel environment of oracle, a database corresponds to multiple instances, in this way, multiple network service names are required. For convenience, the Database Service name is introduced. This parameter corresponds to a database, not an instance.

If the database has a domain name, the Database Service name is the global database name. The Database Service name is the same as the database name.

The oralce network component starting with oracle8i. The host string connecting the client to the database uses the Database Service name and the database instance name.

SQL>select value from v$parameter where name='service_names';

SQL>show parameter service_name;

[Network service name]

It can also be called the database alias. It is required when the client accesses the database. The network service name is recorded in the tnsnames. ora file.

In ORACLE, the concept of data blocks (the more detailed the better)

Oracle database data is stored in data blocks,
A data block is the smallest data organization unit in a database.

Oracle manages the storage space of data files in data blocks,
The data block value range is 2 kb ~ Between 64 KB,

Continuous Data blocks can form another concept in Oracle-partition
The partition is the smallest unit allocated in the computer disk space.

The logical component relationships of oracle databases are as follows:
Oracle Database -- tablespace -- segment -- zone -- data block
 
What is the concept and function of tablespace in Oracle?

The ORACLE database is divided into logical regions called tablespaces-forming the logical structure of the ORACLE database. One ORACLE database can have one or more tablespaces, while one tablespace corresponds to one or more physical database files. Table space is the minimum unit for ORACLE database recovery. It contains many database entities, such as tables, views, indexes, clustering, rollback segments, and temporary segments. Each ORACLE database has a SYSTEM tablespace, which is automatically created when the database is created. The SYSTEM tablespace must always be online, because it contains the basic information required for running the database (data dictionary, online help mechanism, all rollback segments, temporary and auto-lifting segments, all user database entities, tables required by other ORACLE software products ). The ORACLE database of a small application usually only contains the SYSTEM tablespace. However, the ORACLE database of a slightly large application uses multiple tablespaces, which makes the database more convenient to use. The role of Oracle tablespace in tablespaces can help DBA users do the following: 1. determines the space allocation of database entities; 2. set the Space share of database users. 3. control the availability of some data in the database; 4. data is distributed between different devices to improve performance. back up and restore data. When a user creates a database entity, it must have the corresponding power in the given tablespace. Therefore, for a user, to manipulate the data in an ORACLE database, it should: 1. granted the RESOURCE privilege for one or more tablespaces; 2. the default tablespace is specified. 3. the storage space used by the allocated tablespace; 4. the tablespace of the specified default temporary segment.
Edit this section to control the Oracle user tablespace quota
In some large database applications, we need to control the disk space occupied by a user or a group of users. This is like setting a disk quota for each user on the file server to prevent hard disk space depletion. Therefore, in the database, we also need to limit the disk space available to users. To achieve this goal, we can implement it through the tablespace. We can create different tablespaces in the Oracle database, set the maximum storage capacity for them, and then assign users to the tablespaces. In this case, the user's storage capacity is limited by the size of the tablespace. Of course, the advantages of table space are not just that. The higher the performance requirements of enterprises for databases, or the larger the database capacity, the greater the advantages of table space. Next, let's take a look at the processing methods of tablespace in Oracle databases to see if they have sufficient advantages in performance and security to compete with SQL Server databases.
How to edit the tablespace in this Oracle database
During database design, we recommend that the database administrator set the tablespace in the following order. Step 1: Create an Oracle user tablespace. When designing a database, you must first design the tablespace. We need to consider whether to create only one tablespace or multiple tablespaces, as well as the storage location and disk quota of each tablespace. There is no uniform saying about how many tablespaces are designed reasonably. This is mainly determined based on the actual needs of the enterprise. If an enterprise needs to control the user's disk quota, it needs to set the tablespace based on the number of users. When the data capacity of an enterprise is large and it has high requirements on database performance, you need to set different tablespaces based on different types of data, to improve the input/output performance. Step 2: Create an Oracle user tablespace and define the user's default tablespace. When creating a user, we recommend that the database administrator specify the user's default tablespace. Because we use the CREATE statement to CREATE database objects, such as database tables, which are stored in the current default space of the database by default. If you do not specify the user's default tablespace, you must specify the tablespace each time you create a database object. Obviously, this is not very reasonable. Note that different tablespaces have different permission control policies. The user has full control over table space A... the remaining full text>