Maintenance of Oracle tablespace

1. Oracle storage hierarchy Overview
   1) A database consists of one or more tablespaces.
   2) a tablespace consists of one or more files. The files that make up the tablespace can be various files in the file system, and the tablespace contains segments.
   3) a CIDR block consists of one or more CIDR blocks, including table and index segments. It is included in a tablespace, but can be included across multiple files in the tablespace.
   4) The partition is a logical continuous block on the disk. The partition is only in one tablespace, and the second phase is always in a file in the tablespace.
   5) blocks are the smallest allocation units in the database and the smallest I/O units used by the database. When allocating space to segments, the allocated space must be at least the integer of a block.

   Dba_tablespacesThe view can view the detailed information about the definition of a tablespace. This information can be defined when the tablespace create tablespace is created and the tablespace alter tablespace is modified.
   Dba_segmentsThe view can view the detailed information of all segments in a tablespace. If the tablespace has not been used after it is created (that is, if no object has been created on the tablespace ), dba_segments will not have any information about the tablespace. View the segment information of the devbase table space: Select * From dba_segments A where a. tablespace_name = 'devbase'; because the segment is still cross-file, there is no description about the file in the segment basic information. The segment_name field is the name of the created object, such as the table name and index name.
   Dba_extentsThe view can view the information of all partitions in the tablespace. If the tablespace does not have any information in dba_segments, no information exists in dba_extents. Because it does not span files, the Section can only be located in one file, and there is one file_id field in all the section information, indicating that the Section is located in that file.
   Dba_data_filesThe view can view the file information in a tablespace. A tablespace requires at least one file. Query the file information in the table space: Select * From dba_data_files A where a. tablespace_name = 'devbase '.

   Based on the descriptions of the preceding views, you can understand the composition of the table space. We can view the table space usage through the combination of the preceding views, and select the appropriate table space for table creation and index creation during development.

   The following uses examples to study these views.

2. Example
   Create a tablespace under the Sys user. The creation statement is as follows:
   Create tablespace devbase
   Datafile '/home/ora/tbspace/devbase_20090320.dbf' size 512 m
   Autoextend on
   Next 100 m
   Maxsize 1024 m;

   In the preceding statement, a file of MB is first submitted, and the initial space is MB.
   View the table space information. You can use the following statement to view the table space devbase information:
   Select * From dba_tablespaces A where a. tablespace_name = 'devbase ';
   View the information of the tablespace file. The SQL statement is as follows. The field bytes = 536870912 Bytes/1024/1024 = m indicates the file size.
   Select * From dba_data_files A where a. tablespace_name = 'devbase ';
   However, the devbase information does not exist in dba_extents and dba_segments, because no objects have been created on the tablespace.

   Modify the tablespace file. The statement is as follows:
   Alter database datafile '/home/ora/tbspace/devbase_20090320.dbf' resize 1024 m;
   After the successful operation, the records of the three views dba_tablespaces, dba_extents, and dba_segments remain unchanged.

   Exercise caution when deleting data files. First back up the data in the tablespace to another tablespace, and then delete the tablespace together with the data file.

   Create a table on the tablespace and create an index:
   Create Table staff_info
   (
   Staff_id number (10 ),
   Staff_name varchar2 (20 ),
   ADDR varchar2 (200)
   )
   Tablespace devbase;

   Create index idx_staff_info_pid on staff_info (staff_id) tablespace devbase;
   Two segments are generated, with the table and index names respectively. The statement is as follows:
   Select * From dba_segments A where a. tablespace_name = 'devbase ';
   Two segments are generated and allocated space. The statement is as follows:
   Select * From dba_extents A where a. tablespace_name = 'devbase ';
   Note that the sum of bytes in the segment and segment information is equal.

   Insert records into the table to affect the information of segments and segments. Execute the following script PLSQL:
   Begin
   For I in 200000
   Loop
   Insert into staff_info (staff_id, staff_name, ADDR)
   Values (I, 'name', 'address ');
   End loop;
   
   Commit;
   End;
   0.2 million records are inserted into the table empty.

   View the segment information of the devbase table space. Because there are two objects, there are two records:
   Select * From dba_segments A where a. tablespace_name = 'devbase ';
   We found that bytes, and blocks have increased a lot. bytes is the number of allocated bytes, blocks is the number of allocated blocks, and blocks is multiplied by the block size to be equal to the bytes size. The extents tables are 20 and 19 respectively, which means that creating an index is also very space-consuming.

   View the segment information of the tablespace devbase:
   Select * From dba_extents A where a. tablespace_name = 'devbase ';
   We can see that a lot of segment information is added, with 39 records, which is exactly equal to the sum of extents in the segment information; there are two file_id, that is, the partition information is created on both data files; the sum of bytes is equal to the bytes in the segment information.

   The available storage space of a tablespace is the total size of all data files.
   When creating a tablespace above, we use the field extension option "autoextend On". When the tablespace is not enough, the project will be selected based on the expansion to automatically expand the size of the data file; however, the option of automatic file extension has some problems in management. In this way, some database files are automatically extended too much during database operation, which may affect Io performance; there are a lot of objects and data on it, which is not conducive to backup and daily maintenance. Therefore, when creating data files, do not open the automatic expansion attribute. You should create some data files of the proper size, when the tablespace is not enough, add data files to meet the requirements.

   Next we will delete the data in the staff_info table.
   Delete from staff_info;
   Commit;
   View the information in dba_segments and dba_extents. The records are not changed. After the data is deleted, the data dictionary is not modified.
   Run the PLSQL script again:
   Begin
   For I in 200001 .. 400000
   Loop
   Insert into staff_info (staff_id, staff_name, ADDR)
   Values (I, 'name', 'address ');
   End loop;
   
   Commit;
   End;
   Check the information in dba_segments and dba_extents, or 39 segments, indicating that the space just released has been used.

   Next, we will delete the data in the staff_info table again:
   Truncate table staff_info;
   View the information in dba_segments and dba_extents. The same as when creating a table and an index, the tuncate space is recycled.

   In this way, after the system runs for a long time, the insert, update, and delete operations may leave fragments. In general, moving is used to remove objects from one tablespace to another. For some business systems, regular maintenance is required to eliminate fragments and improve system performance. I will not perform tests here.

3. Summary
   Table space is the most basic feature of oracle. Pay attention to the following points:
   1) I/O principle should be minimized. Several frequently-associated tables and indexes can be created in different tablespaces so that when executing SQL statements, data can be read from different files in parallel for hash operations.
   2) Minimize fragments. Perform regular maintenance on tables with frequent insert, update, and delete operations based on the service, move the tablespace, and eliminate fragments.
   3) Select multiple tablespaces in the space table and use the preceding views to check the usage of those tablespaces and the remaining space.