Oracle structure-Logical Structure

The logical structure of oracle includes tablespace, segments, data blocks, and schema objects ).
Oracle databases are logically composed of multiple tablespaces, which physically contain one or more data files. The data file size is an integer multiple of the block size. The objects stored in the tablespace are called segments, such as data segments, index segments, and rollback segments. Segments are composed of zones, which are the smallest unit allocated to disks. The increase of segments is achieved by increasing the number of segments. The size of each partition is an integer multiple of the data block size, and the partition size can be different. The data block is the smallest I/O unit in the database and also the unit of the memory data buffer, and data file storage space units. The block size is set by the DB_BLOCK_SIZE parameter, and its value should be set to an integer multiple of the operating system block size.

I. tablespace
1. What is a tablespace?
A tablespace is the largest logical unit in a database. Each tablespace consists of one or more data files. A data file can only be associated with one tablespace. Each database has a SYSTEM tablespace that is automatically created during database creation or database installation. It is used to store the SYSTEM's data dictionary tables, Program SYSTEM units, and process functions, packages and triggers can also be used to store user data tables and index objects.
Tablespaces have the online and offline attributes. You can set tablespaces other than SYSTEM and RBS to offline.
Query the file information of a table space: SELECT * FROM DBA_DATA_FILES. Query the free space information of a table space: SELECT * FROM DBA_FREE_SPACE. If the remaining space in the tablespace is insufficient, You can reset the data file size: alter database datafile 'filenamespec 'RESIZE 500 m. Query the tablespace information in the database: SELECT * FROM DBA_TABLESPACE.

2. Table space features
A. Restrict the disk space available to users
SQL> ALTER USER user1 QUOTA 100 M ON tablespace1
B. Set it to offline, online, read-only, and read/write
SQL> ALTER TABLESPACE tbs1 ONLINE;
SQL> ALTER TABLESPACE tbs1 OFFLIKE;
SQL> ALTER TABLESPACE tbs1 READ ONLY;
SQL> ALTER TABLESPACE tbs1 READ WRITE;
Note: After the tablespace is set to read only or OFFLINE, the synchronization information of the tablespace data file cannot be changed, resulting in non-synchronization. After the tablespace is set to read write or ONLINE, use the alter system switch logfile command to force multiple log switches, and the SYSTEM automatically keeps all data files synchronized.
You cannot set the SYSTEM tablespace to read only or OFFLINE. If a rollback segment has been created in the undo space and its status is ONLINE, the undo tablespace cannot be set to read only or OFFLINE.
C. backup and recovery of tablespaces
During database backup, only one independent tablespace can be backed up. Table space backup can be performed online or offline.
D. To optimize I/O performance, the data files in the tablespace should be placed on different disks to balance I/O.

3. Temporary tablespace
The temporary tablespace is used in the order by statement for sorting users. When creating a database, you can specify the default temporary tablespace of the database. When creating a user, you can specify the user's default temporary tablespace.
SQL> CREATE USER fey IDENTIFIED BY 12345 TEMPORARY TABLESPACE temp;
In oracle8i, if the user's temporary tablespace is not specified when the user is created, the SYSTEM uses the SYSTEM tablespace as the user's temporary tablespace. In oracle10g, if the user's temporary tablespace is not specified when the user is created, the system uses the TEMP tablespace as the temporary tablespace of the user.

Ii. Section
Database segments can be divided into four categories: data segments, index segments, rollback segments, and temporary segments.
1. Data Segment
A data segment is used to store data. It is stored in the data table space. Each table has a data segment (except for clustering). The table data is stored in the data segment. By default, the lower part of the table has the same name as the table name. The following statement query segment information:
SQL> SELECT SEGMENTNAME, TABLESPACE, NAME, BYTES, BLOCKS FROM USER_EXTENTS;

2. Index segment
The index segment is used to store the index. The following statement queries the index information:
SQL> SELECT * FROM ALL_INDEXS
SQL> SELECT * FROM USER_INDEXS

3. rollback segments
The rollback segment is used to store the values before the user data is modified, so that the data can be rolled back under certain conditions. Rollback segments have one to multiple relationships with transactions. A transaction can only use one rollback segment, while a rollback segment can store the rollback data of one or more transactions.
In oracle8i and earlier versions, managing rollback segments is a complex task. The administrator needs to determine and calculate the number of rollback segments and storage parameters based on the size and quantity of each transaction, after oracle9i, oracle adds the undo tablespace, which is automatically managed by the system. You do not need to manage the rollback segments. The undo_management parameter determines whether the database uses a rollback segment or an undo tablespace. The value of auto indicates that the undo tablespace is automatically managed, and manual indicates that the rollback segment is managed manually.

4. Temporary Section
The temporary segment is used to sort and summarize the order by statement.

Iii. Zone
The partition is the minimum unit for disk space allocation. The disk is divided by zone. At least one zone is allocated each time. The partition is stored in segments and consists of continuous data blocks.
In a segment, the minimum number of partitions is determined by the MINEXTENTS parameter. The MAXEXTENTS parameter determines the maximum number of partitions in a data segment. These parameters can be dynamically modified online.
In the allocation process of the partition, each time to the allocation of 5 areas, if the remaining free space is not 5 areas, the error will occur: ORA-01653.
You can use the dba_tablespace dictionary to query information in the central area of the table space. You can use the user_table dictionary to query information in the segments. You can use the dictionary user_extents to query the distribution status of a partition.

Iv. Data blocks
A data block is the smallest data organization and management unit in the data, the storage space unit of the data file disk, and the smallest unit of the database I/O. The data block size is determined by the DB_BLOCK_SIZE parameter, the default value of DB_BLOCK_SIZE varies with oracle versions.
In versions earlier than 9i, only one block size can be set. In 9i and 10g, multiple block sizes can be set. Different tablespace sizes can be specified.

V. Mode object
A schema object is an application that includes tables, views, clustering, index sequence generators, synonyms, hashing, program units, and database chains. The schema object will be detailed in the oracle schema column later.