ORACLE database, instance, table space, user, database object

Oracle is a database management system, which is a relational database management system. Usually we call the "database", contains the physical data, database management system, memory, operating system of the composition of the process, refers to the database management system described here.

A complete Oracle database is typically made up of two parts: an Oracle database and a database instance.

①oracle database is a collection of a series of physical files;

The files that make up the Oracle database can be divided into three types: The data file, the Redo log file (redo log files), and the control file. Data files, any number of data files can exist in Oracle, and redo log files are used just like SQL Server's transaction log files to hold records of changes to the data, which is required during the system recovery phase; Control files are special small files. It is used to save critical information about the database, without which the instance cannot open the database.

In addition to data files, redo log files, and control files, the database also contains a parameter file (parameter file), a password file (password file), and an optional archive log file (archive log files).

The ②oracle DB instance is a set of Oracle background processes/threads and the shared memory area allocated on the server.

When Oracle starts up, it takes up some server memory to perform operations like SQL Server, the memory region--SGA (System Global area)--is divided into several different structures, While creating the SGA, a series of background processes are also initiated to interact with the SGA, where the allocated memory space and background processes are combined to be Oracle instances. Note that there is no reference to the database, in fact Oracle instances are good to run without a database or database access, and when installing Oracle, we can choose to install the software only and then install the database.

When the Oracle system starts, it first creates the DB instance in memory, then the instance finds the database saved on disk, and finally opens the database for the user to operate. When the system shuts down, the instance is erased from memory, and the entire memory structure and background processes disappear, but the database remains on disk, just in the closed state.

1. Database

A database is a collection of data.
When installing the Oracle database, let's choose to install the startup database (that is, the default global database);

Start the database: Also called the global database, is the entry of the database system, it will be built with some advanced permissions of users such as Sys,system. We log in with these advanced rights accounts to create table spaces, users, and tables in the DB instance.

There are 3 users with some advanced privileges: Sys,system,scott

①sys User Super Administrator (equivalent to SQL Sa,mysql root), with the highest privileges of Oracle, with SYSDBA role, with the right to create database, password default to manager.
②.system User is the Operation administrator, second only to SYS, with the role of Sysoper (ATE), the system has no permissions to create database, and the other permissions are the same as sys.
③.scott User Ordinary user, the default password is tiger, the user is locked by default, can be unlocked with the system

Global database name: is the identity of a database, in the installation of the need to think well, later generally do not modify, change is also troublesome, because once the database is installed, the database name is written into the control files, database tables, many places will use the database name.

Query the current database name:
1 Select name from V$database;

2. DB instance

Oracle Official Description: 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.

It is actually a piece of process used to access and use the database, it only exists in memory.

We access the Oracle database through an instance to connect to the database and then access the database files. If the instance has a database file associated with it, it can be accessed, and if not, you will get an error that the instance is not available.

When the Oracle system starts, it first creates the DB instance in memory, then the instance finds the database saved on disk, and finally opens the database for the user to operate. When the system shuts down, the instance is purged from memory: the entire memory structure and the background process disappear, but the database still exists on disk, only in a closed state. As I've said before, Oracle instances can run without opening the database

The instance name refers to the name of the database management system that is used in response to a database operation. She is also called SID. The instance name is determined by the parameter instance_name.

Query the current DB instance name:

1 Select instance_name from v$instance;

The database instance name (instance_name) is used for external connections. To get a connection to the database in the operating system, you must use the database instance name. For example, to connect to a database, we have to connect to the database instance and connect through the instance name:

Jdbc:oracle:thin: @localhost: 1521:orcl (ORCL is the DB instance name)

Strictly speaking: An Oracle service that includes only one Oracle instance and one database (regardless of the case of dual-machine parallelism).

However, one instance can only correspond to one database, and one database may correspond to multiple instances. Unless you use the Parallel Oracle server option, each Oracle database has an instance associated with it, and a database is loaded with a unique instance.

The relationship between the database and the instance is 1 to 1/n, and each Oracle database in a non-parallel database system corresponds to an instance; In a parallel database system, a database will correspond to multiple instances, the same time the user is only associated with one instance, when one instance fails, the other instance automatically services, Keep the database running properly. In any case, each instance can have only one database.

3. Table Space

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.

Database comes with table space:
①sysaux table Space

The Sysaux table space is introduced in Oracle Database 10g as a secondary tablespace for system table spaces. Previously, some database components that use a separate tablespace or system tablespace are now created in the Sysaux table space. Sysaux Tablespace holds some other Metadata components, such as Oem,streams, are stored in the Sysaux table space by default. By separating these components and functions, the load on the system table space is reduced. Repeatedly creating some related objects and components prevents fragmentation of the system tablespace.

②system table Space

The system tablespace is created automatically when Oracle creates a database, each Oracle database has a system tablespace, and the system table space is always kept in online mode because it contains the basic information required to run the database, such as: Data dictionary, online Help mechanism, All fallback segments, temporary segments and bootstrap segments, all user database entities, tables for other Oracle software PRODUCT requirements, and so on.

③temp table Space

Temporary tablespaces are used to manage database sorting operations and temporary objects such as temporary tables, intermediate sort results, and so on, when the sort is needed in Oracle, and when the sort_area_size size in the PGA is insufficient, the data is placed in a temporary table space for sorting. Like some operations in the database: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER by, GROUP by, UNION all, INTERSECT, minus, Ort-merge JOINS, HASH join, etc. May use a temporary tablespace. When the operation is complete, the temporary object in the temporary tablespace is automatically cleaned up and the temporary segment is automatically freed. The release here is simply marked as free, reusable, and the actual disk space consumed is not actually released. This is also why temporary table spaces can sometimes grow. Staging table space stores large-scale sorting operations (small-scale sorting operations are done directly in RAM, large-scale sorting requires disk sort), and intermediate results for hashing operations. It differs from the permanent table space in that it consists of a temporary data file (temporary files), Instead of a permanent data file (datafiles). Temporary tablespace does not store persistent types of objects, so it does not and does not need to be backed up. Additionally, the operation of the temporary data file does not produce a redo log, but an undo log is generated.

④undo table Space

The Undo table space is a unique concept for Oracle. The undo segment is automatically assigned in the undo Tablespace to hold the undo information for the DML statements in the transaction, that is, to save the value of the data before it is modified. In rollback, the instance is restored (rolled forward), and the undo information is used when constructing a consistent read CR block. Because of the introduction of undo, when Oracle's SELECT statement implements consistent reads, no locks are required. The Undo tablespace and other table spaces have many similarities: The Undo data block is also read to the buffer cache, and the redo log is generated when modified, and the data is written back to the disk on the Undo table space. So after the crash, the buffer cache of the undo block will be restored.

⑤users table Space

The creation user must specify a tablespace for it, and if there is no explicit specified default tablespace, it is specified as the Users table space, and all information for this user is placed in the people table space.

To view the table spaces that have been created:

1 Select Default_tablespace, Temporary_tablespace, username from dba_users;

4. Users

After the Oracle database is built, to build the table in the database, you must first establish the user for the database and specify the table space for the user. Oracle's tablespace, including the use and operation of various database objects, is used between users in an authoritative manner.

To view database users:

1 Select * from Dba_users;

In summary, existing instances and databases; Both the user and the tablespace exist after the instance and are authorized to access it, but the system built-in advanced rights user and the self-contained tablespace are loaded at the beginning of the instance creation.

Therefore, when an instance fails, other instances take over the service, the tablespace of the failure instance, and so on, can be re-assigned to other instances through advanced permission users.

5. Database objects

With the database, tablespace, and user, the user creates tables in their own table space. With a table, you can develop it.

All objects of the database, including procedures, functions, packages and packages, jobs, tables, views, indexes, sequences, triggers, etc., are developed by the user and exist in the table space.

ORACLE database, instance, table space, user, database object