An explanation of Oracle related concepts

First, preface

The author of the Oracle database understanding, a long time to stay in the "illusion" of the state, you say do not understand it, and will use, a take it up, on some basic concepts and can not say a way to ~ if you want to go farther on the coding path, this must also be around the threshold, so I decided to take some time, Tidy up the concepts often mentioned in Oracle ~

First, the author first declares the concepts that this article will explain including: database, database instance (DB instance), database name (db_name), database domain name, global database name, database service name (service_name), table space ( tablespace), Scheme (schema), segment (segment), zone (extent), block, SID.

Second, the text

　　2.1 Databases (database)

Usually we call the "database", and not only refers to the physical data collection, it contains a series of physical files (data files, control files, online logs, parameter files, etc.) and database management system, that is, the database is the physical data, memory, operating system process composition.

2.2 Database instance (DB instance)

An instance is a subset of the computer memory and secondary processing background processes required to access an Oracle database, which is a collection of processes and the memory (SGA) used by those processes. is actually a process used to access and use the database, which only exists in memory. Just like the new instance object in Java.

2.3 Database name (db_name)

The database name is the identity of a database, just like a person's ID number. He uses parameter db_name to indicate that if a machine is loaded with multiple databases, then each database has a database name. After the database installation or creation is complete, the parameter db_name is written to the parameter file init.ora.xxxxxxx. The format is as follows:
Db_name=ctoporcl

When you create a database, you should consider the name of the database, and after you create the database, the database name should not be modified, even if you want to modify it will be cumbersome. Because the database name is also written to the control file, the control file is stored in binary mode, and the user cannot modify the contents of the control file. Suppose the user modifies the database name in the parameter file, that is, modifies the value of the db_name. However, when Oracle starts, a ORA-01103 error is returned because the db_name in the parameter file is inconsistent with the database name in the control file, causing the database to fail to start.

The database name is required to install the database, create a new database, create a database control file, modify the data structure, and back up and restore the database. There are many Oracle installation file directories that are associated with database names, such as:

                win:d:\oracle\product\10.1.0\oradata\db_name\ ... Unix:/home/app/oracle/product/10.1.0/oradata/db_name/         ... Pfile:         win:d:\oracle\product\10.1.0\admin\db_name\pfile\ini.ora         Unix:/home/app/oracle/product/10.1.0/ Admin/db_name/pfile/init$oracle_sid.ora         Trace Files directory:         win:/home/app/oracle/product/10.1.0/admin/db_name/ bdump/...  

　　　　In addition, when creating data, the database name in the Create Databases command also matches the value of the db_name parameter in the parameter file, otherwise an error will be generated. Similarly, the statement that modifies the structure of the database alterdatabase, of course, the name of the database to be modified. If the control file is corrupted or missing, the database will not load, and the control file must be recreated by starting the instance nomount and then creating the control file with the Create Controlfile command, which is also referred to as db_name. There are also database names that need to be used when backing up or recovering a database. In short, the database name is important to understand exactly what it does.

Query the current database name:

       1) select name from V$database;  2) Showparameter db  3) view parameter file (init.ora.xxxxx)

To Modify the database name:

It is suggested that the database name should be determined when the database is created, and the database name should not be modified because it is a complex matter to modify the database name. Now, let's explain how to modify the database name after the data has been created. The steps are as follows:
1) Close the database.
2) Modify the value of the db_name parameter in the database parameter file to the new database name.
3) Launch the instance in Nomount mode and build the control file (refer to Oracle Documentation for command syntax for creating control files)

　2.4 Database Domain Name

In distributed database systems, between different versions of the database server, regardless of whether the operating system is UNIX or Windows, each server can be remotely replicated through the database link, the database domain name is mainly used for replication in the Oracle distributed environment.

In the same environment of Oracle's distributed system, if the database name of two databases is different, the database link can be established between two databases, and the remote real-time transmission of the database is carried out.
If the database name of the two databases is the same, no data replication can be made between the two databases.
If you want to replicate data between two databases with the same database name, you need to increase the domain, and two databases have different subdomains, and if you violate these conditions, you cannot replicate data between the two databases.
When to use a domain:
1) in an Oracle distributed environment, remote data transfer between two databases via a database link
2) in the same network environment, the database name of two databases is the same

　　　　Query the domain name of the database:

      1) Selectvalue from v$parameter where name = ' Db_domain ';  2) Showparameter domain  3) query in the parameter file.

　2.5 Global Database name

Global database name = database name + database domain name

2.6 Database service name (SERVICE_NAME)

Beginning with the oracle9i version, a new parameter, the database service name, was introduced. The name of the parameter is service_name. If the database has a domain name, the database service name is the global database name; otherwise, the database service name is the same as the database name. If a domain name is not defined in the database, the database service name is the same as the database name.

Query database service Name:

1) Selectvalue from v$parameter where name = ' service_name ';       2) Showparameter service_name       3) query in the parameter file.  

　　2.7 Table Space (tablespace)

An Oracle database is a table space for storing physical tables, a database instance can have n table spaces, and a table space can have n tables. With the database, you can create table spaces. A tablespace (tablespace) is a logical division of a database, with at least one table space (called the system tablespace) for each database. To facilitate management and improve operational efficiency, you can use additional tablespaces to divide users and applications. For example, the user table space is for general users and the RBS table space is used for rollback segments. A table space can belong to only one database.

2.8 Scenario (Schema)

Schema, also known as pattern, is a logical concept that is smaller than the table space, and it is also a logical container. Multiple users may share a table space, how do you partition each user? Then there is a schema for each user in the tablespace that holds the information for a single user.

A more professional explanation (definition of Schema):

A schema is a collection of the database objects (used by a user.).　　　 Schema objects is the logical structures that directly refer to the database ' s data.　　　 A user is a name, defined in the database, can connect to and Access objects. Schemas and users Help database administrators manage database security.

From the definition we can see that the schema is a collection of database objects, in order to distinguish between the collection, we need to give this collection a name, these are the many similar user names that we see under the Enterprise Manager Scheme node, these are similar to the user name of the node is actually a schema, The schema contains various objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.

A user typically corresponds to a schema, the user's schema name equals the user name, and is used as the default schema for that user. This is why we see the schema name as the database username under the Enterprise Manager scenario. The Oracle database cannot create a new schema, and to create a schema, it can only be solved by creating a user's method (although Oracle has the CREATE SCHEMA statement, it is not used to create a schema) Create a user with a schem that has the same name as the user name and act as the default shcema for that user. That is, the number of schemas is the same as the number of user, and the schema name corresponds to the user name one by one and the same, so we can call the schema user alias, although this is not accurate, but it is easier to understand some.

A user has a default schema, whose schema name is equal to the user name, and of course a user can use other schemas. If we visit a table and do not indicate which schema the table belongs to, the system automatically adds the default Sheman name to the table. For example, when we access the database, we access the EMP table under the Scott user, through the select * from EMP; In fact, the complete syntax for this SQL statement is select * from Scott.emp. The full name of an object in the database is Schema.object, not user.object. Similar if we do not specify the schema of the object when creating the object, the schema of the object is the user's default schema. This is like a user with a default tablespace, but the user can also use other tablespaces, and if we do not specify a tablespace when we create the object, the object is stored in the default tablespace, and in order for the object to be stored in another table space, we need to specify the table space for the object when it is created.

2.9 Segment (segment)

Segments are allocation units in an Oracle database, and objects such as tables, indexes, and so on are allocated in segments. When a table is created, a table segment is created, and an index segment is created when an index is created. Each object that consumes storage space is ultimately stored in a single segment. There are rollback segments, temporary segments, clustered segments, index segments, and so on.

Zone 2.10 (extent)

Area is called the disk area, is a continuous allocation space in the data file, it is larger than the block, composed of blocks. Some objects may require at least two extents when allocating space, such as a rollback segment, and the two extents do not necessarily require a connection. The size of the area ranges from one block to 2GB.

2.11 Blocks (block)

Blocks are the physical units of data storage and are the most basic units in a data file, and data is stored directly on the block. is the smallest unit of Oracle space allocation. There are three common block sizes in Oracle, 2KB, 4KB, and 8KB. The size of the block is fixed when the database is created, the size of each block in the database is the same, and all the blocks are in the same format, consisting of the "size + Table Directory + row directory + Free Space + data space". The header contains information about the block type (for example, the block, or the index block), the location of the block on the disk, and so on. The table directory, if any, contains information about the tables in this block that store the rows (if data in more than one table exists in a block). Row directory contains a description of the data row, which is an array of pointers that indicate the physical location of each row in the data block. The size, table directory, and row directory are collectively called Block overhead, which Oracle uses to count and manage the blocks themselves. The rest of the two parts are very simple, already have data is the data space, temporarily does not exist is the free space.

2.12 SID

Sid is instance_name.

Third, something

Here are some of the above confusing concepts to do some more explanation, I hope you have some help ~

the difference between 3.1 sid and Service_Name

It is easy to understand that a company is likened to a server, and the database is a department in the company.
1) SID: A database can have multiple instances (such as RAC), and the SID is used to identify each instance within the database name, as if a department, everyone has a name of their own.
2) Service_Name: Is the name of this database, the outside people want to connect my database, you will be in the client's connection to write Service_Name.                       It is like the name of a department, the name of the Department of the Janitor (listener) There are registered, the janitor see you are looking for service_name this department, tell you that our company does have this department, so you found, even Then it was established.
In a word: SID is Internal, is a name of the instance level, used for internal address. Service_Name is external, is a database level name, used to tell the outside people, my database is called "service_name". You can use the service_name parameter to specify what the name is, you can have more than one name, name casually, called the dog eggs, Cui Huadu does not matter.          If you do not specify, the default is db_name. Db_domain, Global_name.
The database, and Oracle_sid, is telling the OS system what I call this example. These confusing names, you have to remember, they do not refer to the database, that is, the example of these two things, there is no other. The specific name they use is to see who it is and what the occasion is. Whether it is for the database, the operating system, or the external link. As you are to your parents, you have a small name, for the students, you have a nickname called the light bulb, to the office, you have a regular name called Wang Xiaoming. But in the final analysis, it is one thing. It is not easy to confuse this point.

3.2 The relationship between Oracle instances and databases

1) Temporary and permanent

2) instances can start startup Nomount independently without data files, which usually makes no sense

　　　　3) An instance can only be loaded (ALTER DATABASE mount) and open (ALTER DATABASE open) in its lifetime

4) A database can be loaded and opened by many instances simultaneously (that is, RAC), the role of instances in the RAC environment can be fully reflected! Iv. References and reference linkshttp://blog.sina.com.cn/s/blog_a5a24bcb01010npc.htmlhttp://blog.itpub.net/6906/viewspace-21647/http://blog.csdn.net/elvis_dataguru/article/details/8984961http://blog.csdn.net/nrlovestudy/article/details/49684571http://www.2cto.com/database/201305/213418.htmlhttp://blog.csdn.net/z69183787/article/details/25706269http://www.zhetao.com/content240http://blog.csdn.net/elvis_dataguru/article/details/8984961http://www.cnblogs.com/gsk99/archive/2011/04/14/2016216.htmlhttp://langgufu.iteye.com/blog/1469055

(article content If there is a mistake or an understanding of the wrong to point out, thank you very much ~qq:1163142850~)

An explanation of Oracle related concepts