Use environment variables to configure the Oracle Runtime Environment (1)

The Oracle database provides a default user operation environment. For example, the number of rows extracted from the database, the delimiter between columns, the maximum width of each line, and the default number of lines displayed on each page. These are controlled by database environment variables. Although these parameters are recommended by the Oracle system, they often do not meet the database management requirements. Because we have developed a set of work habits for a while. Therefore, we hope that every time we replace an Oracle runtime environment, the database will be able to provide a familiar runtime environment. This will undoubtedly increase our interest and efficiency in our work.

Therefore, we need to manually change the Oracle environment variables to meet our requirements. I will talk about setting some common environment variables based on my work habits. We believe that these parameters can provide database administrators with a comfortable "working environment ".

Environment Variable 1: Set the delimiter Between Columns

When SQL * Plus is used for SQL statement query, columns are distinguished by spaces by default. However, I think this distinction is not obvious. Sometimes, you may often see errors. When there is a lot of data, it also gives people a feeling of "Dizzy. Therefore, I often change the default setting at the beginning. I like to use the "|" symbol to distinguish between columns.

As shown in the following settings, you can use the "|" symbol to differentiate columns in the display result. Set colsep |. With this statement, you can set the environment variables of the database. The final running result is as follows. Use the | symbol to differentiate columns, which looks much clearer. Fields are mixed.

Environment Variable 2: Set whether to submit automatically

There is a concept of transaction control in Oracle databases. That is to say, when we use the Update statement to Update some content of the database, by default, the data in the database file will not be changed immediately after this statement is executed. In the same dialog, The result displayed after the query is changed. However, if you log out of the dialog and re-connect and query it, the displayed result is still the result before modification. The modified content is not saved. This is mainly because the updated transaction is not submitted.

According to the settings of the Oracle database, transactions are not submitted by default. Instead, you must manually enter the commmit command to submit related transactions. Generally, DML statements can be used only by manually submitting transactions.

This design was designed to provide database administrators with a buffer. It also provides end users with an opportunity to confirm whether data is accurate. In addition, this mechanism can also help the database administrator easily implement the rollback mechanism.

For example, in an inventory management system, materials need to be transferred from one warehouse to another. At this point, you need to control it through transactions. Reduce the number of materials in one warehouse and increase in the other warehouse. However, if you fail to increase the number of operations in another warehouse for some reason, you need to roll back the transaction "decrease the number in one warehouse. That is to say, the transaction is not submitted to the database. With this wit, data consistency between jobs can be easily realized.

However, when designing databases, I manually submit related transactions, and I think there is a sense of being superfluous. When I develop databases in the early stage, I often change this default setting. I want the system to automatically submit this transaction. Then, when testing in the background, change the environment variable back.

If you want the database to automatically submit related transactions, you can use the set autocommit on command. In this case, the database automatically submits the DML statement each time. Instead of submitting related transactions every time you manually enter the COMMIT command. However, after the database design is complete, you need to change the environment variable back to manual transaction submission.

Environment Variable 3: Set the width of each line

This is an important environment variable. In Oracle databases, if the row data length exceeds the maximum length we set, it will automatically wrap. However, the data query results are displayed by column, but if the line feed is automatically displayed, the results will look bad. By default, the database's default row width is 80.

I think the width is too small. I like to use a relatively large width. I would rather use slide to scroll to view the data, rather than letting its automatic branches affect the display results. For example. If the row width is not enough, it will be displayed as follows. Ten out of ten people will be dazzled by this result.