Select an appropriate destination for Oracle database tables

As we all know, Oracle database tables are stored in tablespaces. Theoretically, a table can be placed in any tablespace. However, in practice, database engineers tend to be less casual in terms of performance and security. Before creating a database, they plan the table space and select an appropriate destination for the Oracle database. As there are no technical restrictions, there is no uniform rule to find. The wise sees wisdom, and the benevolent sees benevolence. This is a question that tests the level of database engineers.

Because there is no unified standard, the public opinions are justified, and the case is justified. For this reason, the following suggestions may not necessarily be the truth. These are just a Summary of the author's work experience. You can choose from them.

Rule 1: do not place your table in the System tablespace.

After the database is installed, the database creates three tablespaces by default, namely System, Sysaus, and Temp. The first two-bit system tablespaces, and the last one is the temporary tablespace. These two tablespaces are often used by the system. Therefore, in general, only tables including data dictionaries and Oracle system objects should be stored in the system tablespace. If the database administrator creates a table in the system tablespace (including other solution objects such as views), the database performance will be affected. Therefore, Oracle and I strongly recommend that you do not create other database objects such as data tables in the system tablespace. That is to say, except for the objects created by default during system installation, Oracle Database engineers should not put any database solution objects into these system tablespace. By default, the database does not have permission control, so the user is primarily conscious. To prevent tables from being created in the system tablespace, I have the following suggestions.

First, if multiple users participate in database development, it is best to set a default tablespace for each user (of course, this tablespace cannot be a system tablespace ). Because when you create a database table, it is stored in the default tablespace set by default. Therefore, as long as you set the default tablespace for the user, the user will not mistakenly put the database table into the system tablespace due to negligence and other reasons.

Second, permission control can be performed. For example, during database development, You can restrict the right of other database users to store database objects in the system tablespace. Sometimes, in order to prevent accidents, you can set a user to have the right to put things in the system tablespace. When other users need to create database tables or other database objects between System and other System tables, only one user can do this. This limits the cleanliness of the system tablespace to the maximum extent. It prevents user tables from being placed into system tablespace, thus improving the database operation efficiency.

Third, before the database is delivered and used, the Database Engineer should perform another check to check whether there are non-system table data tables in the system tablespace. Database engineers can make adjustments before delivery. If this error is discovered only after the user has stored data in the system after delivery, the workload will be greatly adjusted.

In short, a basic principle is that the system tablespace cannot store user tables and other database objects. Otherwise, the database performance will be reduced. Do not move system objects to other non-system tablespaces. This superfluous practice will also reduce the database performance.

1 2 Next Page