Description of Oracle tablespace settings

The following articles mainly describe how to set Oracle tablespace. We all know that there are many tables in the Oracle database. There are more than 600 tables in total, and you may occupy more than 10 Gb of space. The following article mainly describes Oracle tablespace settings:

Is it set to create a dozen GB Data File USERS01.DBF in the USERS tablespace?

You can also create three GB Data Files in the USERS tablespace: USERS01.DBF, USERS02.DBF, USERS03.DBF,

What is the best ceiling for data files in Oracle tablespace? Is the larger the size of a single file? Is it better to have multiple smaller files? What is the principle?

What should I do if the Oracle database generates many fragments? Can I regularly perform "disk fragmentation" for the drive letter used to store data files? What should I do? Do you want to turn off the database first?

The two databases run on one server simultaneously to serve the two business systems,

Is it reasonable for two databases to share one external port 1521? Will it affect data throughput performance? Do you want to set port 1521 and port 1520 in two listeners?

1. It is divided into multiple data files.

The reason is:

1) Some operating systems have restrictions on the file size or installation. You may not be aware of these restrictions. In addition, some versions of the Transfer Protocol do not support excessive files. For example, sftp of a certain version of AIX does not allow the transfer of files larger than 2 GB.

2) You have a small amount of data, so you don't have to worry too much about it. However, when the data volume increases in the future, we need to consider load balancing, we need to move some data files to other disks. Multiple data files make it easy.

3) when a part of your data file contains bad blocks, you need to temporarily restore a data file offline. If your data file is too large, the impact may be even greater.

4) use RMAN to back up data files separately. Recovery can also be restored separately, so it is obvious that it is advantageous to split multiple data files.

2. The size of a single data file. There are many things to consider. For example, your storage performance, your total data volume, and so on. The expert suggested that the size of a single data file is generally 2-10 Gb for dozens to hundreds of GB of data. The Principles have a set of theoretical explanations, but I forgot to mention my personal suggestions:

1) Operating System Restrictions. If this problem is not noticed, it is very likely to cause problems, especially self-extended data files, such as system and undotbs.

2) Oracle tablespace size. Consider moving or restoring a single data file. Obviously, if dozens of GB tablespaces are divided into two data files, it does not bring any benefits to your operations.

3) You should consider the raid situation of your hard disk based on experience. The situation is complex and can only be compromised. It cannot be simply generalized to be big or small.

Of course, this is not the main thing. You do not need to pay too much attention to this aspect. For a simple environment like yours, the modification of the size of a tablespace data file in the partition area is not as good as the performance improvement!

3. What should I do if the database generates fragments. I will tell you that the fragmentation of the database is different from that of windows. Do not confuse it. Windows is useless.

In fact, I honestly tell you that you don't have to worry about fragmentation issues in the short term for dozens of GB databases. In this case, we all know that the database is getting slower, and we need to recycle the segment space. In addition, if you say "whether to shut down or not", it means that your database can be shut down, which implies that it is "not very busy ", so you don't have to worry about doing this in the last 3-5 years.

4. It is unreasonable for two databases to use a listener 1521 at the same time. You cannot connect two databases with one listener. Do you think this is a great risk?

The two public ports have no impact on the data throughput performance. However, security is undoubtedly very low.

Therefore, of course, it is necessary to use two ports 1521 and 1520. In fact, I suggest you install the two databases under two different operating system users so that everyone can understand each other's logic and minimize the impact, you can learn more about the benefits. This is purely from my work experience. If you are in trouble, I will not say so.

How to Set it? This is a complicated question. I am too lazy to write a bunch of TNS editors.

Let me tell you a simple method. Run the command line to input netca to create two listeners, 1521 and 1520. Then run the command line dbca to create a database. Note that you will be prompted to bind the database to a port during the creation process. Just select the port. The graphic interface is very simple.