Oracle tablespace Design Principles

Common principles of oracle tablespace design principles: 1. Generally, a large table or index is allocated with a single tablespace. 2. The Read only object or Read mostly object is grouped into a group and exists in the corresponding tablespace. 3. If all the objects in tablespace are read only objects, you can set tablespace to read only mode. During backup, read only tablespace only needs to be backed up once. 4. Objects with high-frequency insert are grouped into one group, which exists in the corresponding tablespace. 5. add, delete, and modify objects into a group, which exists in the corresponding tablespace. 6. Tables and indexes are stored in different tablespaces. 7. The extent size of the table (or index) stored in the same tablespace is better than that of the table, which facilitates reuse of space and reduction of fragments. Based on the above principles, the database tablespace design principles are as follows: in principle, each schema corresponds to a tablespace and a corresponding index tablespace; each schema corresponds to a single tablespace and an index tablespace; for a super large table to be partitioned, each sub-partition corresponds to a separate table space and index space. Typical application 1: control the tablespaces occupied by users. 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. Typical application 2: control the disk space occupied by the database. Sometimes, there may be more than one service running on the Oracle Database Server. In addition to database servers, there may also be application system servers such as email servers. Therefore, you must first plan the disk space of the Oracle database. Otherwise, when the disk space occupied by multiple application services increases infinitely, in the end, each service may be stopped due to the exhaustion of hard disk space. Therefore, to use multiple application services on the same server, we often need to plan and allocate disk space for them first. Each service cannot exceed the maximum limit we allocate to it, or remind us immediately after it is exceeded. Only in this way can we avoid the crash of various application services due to the exhausted disk space. Typical application 3: flexibly place tablespaces to improve database input/output performance. The database administrator can also store different types of data in different tablespaces, which can significantly improve the database input and output performance and facilitate data backup and recovery management. Because our database administrator can back up data by tablespace during backup or data recovery. For example, when designing a large distribution system background database, we can create tablespaces by province. Data Files related to Zhejiang Province are stored in the table space of Zhejiang Province. Business records in Beijing are recorded in the table space of Beijing. In this way, when the business data in Zhejiang Province is incorrect, the tablespace in Zhejiang province can be restored directly. Obviously, in this design, when data in a tablespace is wrong and needs to be restored, the impact on other tablespaces can be avoided. In addition, the table space can be backed up independently. When the database capacity is relatively large, if you back up the entire database at once, it will obviously take a lot of time. Although Oracle Database supports hot backup, it occupies a large amount of system resources during the backup process, resulting in a decline in database performance. Therefore, when the database capacity is relatively large, we need to set up multiple tablespaces, and then plan the backup time for each tablespace to improve the backup efficiency of the entire database, reduce the impact of backup on the normal operation of the database. Typical application 4: Sorting of large tables. We all know that when there are many records in the table, the query speed will be slow. After the first query is successful, it still takes so much time to re-Sort it for the second time. To this end, when designing data databases, we usually place such a large table object in an independent tablespace to improve database performance. Typical application 5: open log files and data files to improve database security. By default, log files and data files are stored in the same tablespace. However, this is not very good for database security. Therefore, in the process of database design, we often like to reload log files, especially log files, into an independent tablespace and store them on another hard disk. In this case, when the hard disk for storing data files fails, the database can be repaired immediately by redoing log files stored in another tablespace, to reduce the loss caused by data loss. 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. During database design, we recommend that the database administrator set the tablespace in the following order. Step 1: Create a 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 an enterprise has a large data capacity and high requirements on database performance, it needs to set different tablespaces based on different types of data, to improve the input/output performance. Step 2: create a user and create a default tablespace for the user. 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 permissions on table space A, and may have only query permissions on table Space B, or even connection permissions. Therefore, configuring table space access permissions for users is also a way to improve database security. Common Principle 1. Generally, a large table or index is allocated with a single tablespace. 2. The Read only object or Read mostly object is grouped into a group and exists in the corresponding tablespace. 3. If all the objects in tablespace are read only objects, you can set tablespace to read only mode. During backup, read only tablespace only needs to be backed up once. 4. Objects with high-frequency insert are grouped into one group, which exists in the corresponding tablespace. 5. add, delete, and modify objects into a group, which exists in the corresponding tablespace. 6. Tables and indexes are stored in different tablespaces. 7. The extent size of the table (or index) stored in the same tablespace is better than that of the table, which facilitates reuse of space and reduction of fragments. Based on the above principles, the database tablespace is designed as follows: in principle, each schema corresponds to a tablespace and a corresponding index tablespace; each schema corresponds to a single tablespace and an index tablespace; for a large table to be partitioned, each sub-partition corresponds to a separate tablespace and index space.