Create the most appropriate Oracle runtime environment

In Oracle databases, a set of default user operating environments is provided. The number of rows fetched from the database once, such as when a user queries, the separator between columns, the maximum width per line, the number of rows by default for each page, and so on. These are controlled by the environment variables of the database. Although these parameters are recommended by Oracle systems, they often do not meet the requirements of database management. Since we have been working for some time, we have developed our own set of work habits. So, we want the database to provide a familiar running environment after each Oracle-run environment is replaced. This will undoubtedly improve the interest and efficiency of our work.

To do this, we need to manually change Oracle's environment variables to meet our requirements. The author of the following combination of their own work habits, talk about some commonly used environment variable settings. With these parameters, you can provide a comfortable "working environment" for your database administrators.

environment variable One: sets the separator between columns.

Usually in the Sql*plus tool, the use of SQL statement query, the columns between the default is to use space to distinguish between. However, I think this distinction is not obvious enough. Sometimes, mistakes are often seen. When the data is more, it will give people a feeling of "Carsick". So the author often starts, will change this default setting. I like to use the "|" Symbols to differentiate between columns.

The following settings allow you to display the results with the "|" Symbols to differentiate each column. SET Colsep |. With this statement, you can set the environment variables for the database. The results of the final operation are as follows. The use of this symbol to differentiate the columns seems to be much clearer. The fields will be confused.

Environment variable Two: settings are automatically submitted.

There is a concept of transactional control in Oracle databases. That is, when we use the UPDATE statement to update some of the contents of the database, by default, this statement does not immediately make changes to the data in the database file. In the same conversation, the query is displayed with the result of the change. However, if you log out of this conversation first, the result will remain the same as if you were reconnecting and querying. The contents of the change have not been saved. This is mainly because the updated transaction has not been submitted.

Depending on the settings of the Oracle database, transactions are not submitted voluntarily by default. Instead, users are required to manually enter the Commmit command to submit related transactions. In general, DML statements require the user to submit the transaction manually before they can function.

The design was designed to provide a buffer to the database administrator, and it was an opportunity to give end users a chance to confirm whether the data was accurate. In addition, using this mechanism can also help the database administrator to implement the fallback mechanism easily.

If now in a invoicing management system, the need to transfer materials from one warehouse to another warehouse. At this point, you need to control through the transaction. Reduce the quantity of items from one warehouse and increase the number of other warehouses. However, if an increase in the number of operations in another warehouse fails for some reason, a rollback is required for the "number of warehouses in a decrease" transaction. In other words, the transaction is not submitted to the database. With this wit, you can easily achieve the consistency of data between the various jobs.

However, in the database design, hand-submitted related matters, I think there is the feeling of the superfluous. The author often changes this default setting when the database is developed earlier. The author wants the system to submit this transaction automatically. Then, the background test, then the environment variable back.

If you want the database to automatically submit related transactions, you can use the Set autocommit on command to implement. In this case, the database automatically submits the command each time the DML statement is executed. Instead of each time a user manually enters a commit to submit the related transaction. However, after the database design is completed, you need to change the environment variable back to the manual submission of the transaction.