Use script files to manage Oracle databases

Using script files to manage Oracle databases has great advantages.

For example, you can reduce the command input. For example, you can save the commands that pass the debugging test so that you can use them again when needed. For example, you can avoid input errors. Although the SQL * Plus program editing tool provided by the Oracle database provides a certain degree of reality, editing, and modifying SQL Buffer commands, its ability to edit commands is very weak, and operations are not as convenient as script files. Therefore, many database management experts like to make some common commands into script files. You can directly call the script file when you need it later. For example, I have saved nearly useful script files. When needed, you only need to take the script file, and some can be directly executed; some can be used as long as they are slightly modified according to the actual situation.

Step 1: Create a script file

There are many ways to create script files when the Oracle database is heavy. For example, you can directly create a script file in notepad or using SQL * Plus. However, the author's opinion is that it is troublesome to directly create a script file in SQL * Plus. This is mainly because the command editing capability is very effective in the SQL * Plus tool. For example, you cannot use the arrow keys on the keyboard to locate the input position, or use the DEL key to delete the input content. It is also difficult to read.

My favorite practice is to write the script statements in some editors, such as text files, and then copy them directly to the SQL * Plus tool for testing. If the test passes, you can save it for use next time. Therefore, using a third-party script statement editing tool, there is a defect that the statement cannot be directly tested. Manually copy and paste it to the Oracle environment for testing. However, this is worthwhile for command editing. After all, editing a script statement in a third-party tool is much easier than editing a script in SQL * Plus. I don't know why I didn't develop a decent script statement editor for database management software such as Oracle.

Note that, in SQL * Plus statements, you can save untested or wrong script statements. However, doing so will only waste time. In order to improve the availability of script statements, I strongly recommend that the database administrator not be too confident. After writing the script statements, you must test them. The script can be archived only when the script statement has been verified as useful.

Secondly, if you use notepad or other tools to write script statements, you are advised to change the extension of the script file to SQL in order to directly call this script file in the Oracle environment. This is the script file extension recognized by the Oracle database.

For the database administrator who first contacts the script file, pay attention to the script file format. In general, it is similar to a common SQL statement. It should only be noted in two aspects. First, add the "/" symbol to the last line of the script file. This symbol indicates that the database can now execute this statement. Second, do not add the ";" sign in the last SQL statement. Otherwise, the next time you run the script statement, an error will occur.

Finally, when writing a script file, pay attention to the variable issue in the script file. If the script file requires external input parameters, the SQL * Plus tool will use these external input values to replace the replacement parameters in the script file during execution, in the script file, the replacement parameter life method is slightly different from the variable declaration method in the Oracle database for functions and procedures. In the script file, you do not need to declare the parameters before they are replaced. Instead, you can directly use & 1 & 2 to describe it.

Step 2: edit the script file

When we use a script file, we often need to adjust it slightly according to the actual situation. You can edit a script file in either of the following ways. The first is through the SQL * Plus tool, and the second is the third-party independent script editing software.

For some script files with minor adjustments, we can directly use SQL * Plus to open and edit them. However, for script files that require a large number of modifications, the author suggests that the database administrator use a third-party independent script editing software. The reason is very simple. As mentioned above, the script command editing function of SQL * Plus is very weak. If you use this tool to edit the existing script statements, the workload may be reduced by writing a new one. Therefore, the database administrator should select an appropriate script editing tool based on the actual situation.

In addition, pay attention to the correctness of the syntax During the editing process. In particular, you cannot change the format. For example, do not accidentally delete the end of the last "/" symbol, and so on. I like to use Microsoft notepad as a script editing tool. He is convenient and does not need to go online to find professional script editing tools. That is to say, it is easy to worry, and it is able to meet the regular needs of our database administrators!

Step 3: run the script file

After the script is created, how can I run the script? Many methods are also provided in the Oracle system. Database administrators can choose based on their usage habits.

First, use the Start statement to call the script file. The syntax is Start Filemame [related parameters]. When running this command, you need to pay attention to several issues.

1. The script file extension is incorrect. When I created a script file, I skipped it. In order to directly call this script file in SQL * Plus and other tools, it is best to change the extension to an acceptable extension for Oracle databases. By default, the extension is set to SQL.

2. The path of the script file is incorrect. If you use Start to call the script file, if you do not have a clear name to save the path, the SQL * Plus tool will search for it in the current directory; if not, the search will be performed based on the directories identified in the environment variables. In general, script files are stored independently. Therefore, when using the Start command to execute a script file, it is best to specify the absolute path name of the script file. Prevents statement execution errors.

In addition, the @ command can also play the same role as the Start command. However, the former is more widely used. @ Command can be used without the SQL * PLUS tool. For example, you can directly use the command line in the Microsoft operating system. Of course, this operating system requires the prior deployment of the Oracle database environment.

Second, you can directly use SQL * Plus to open text files and execute script statements. Click "file" and "execute" to execute the script statement. The advantage of this method is that the system will actively prompt the parameters that the user needs to enter.

However, I do not like the above two methods. I usually use the script editor to open the corresponding script file. Then, check whether necessary changes are required. If not, copy and paste the statement to the SQL * Plus tool for execution. In this case, you do not need to specify the specific location of the script file like Start.

When executing a script statement, pay special attention to the location of a parameter. Parameters passed in from the outside world must match the replacement parameters of the script statement one by one. If there is a problem with the parameter location, the execution result will also be a problem.

In short, the script file is the provisioner for managing Oracle databases. Using script files can improve the management efficiency of Oracle databases. After all, it is very troublesome to write commands whenever necessary. The biggest benefit of a script file is that it can improve statement reuse and save time for writing statements and debugging and testing.

1. Guidelines for writing effective transactions in Oracle databases
2. Usage of SQL statements in Oracle
3. How to efficiently Delete duplicate data from Oracle databases