Sqlplus usage tips

Databases in the production environment cannot directly use development tools (such as toad and PL/SQL developer) to connect and perform data operations, because they have strict permission control (for DB security considerations ), the only tool that our developers can use is to log on to the DB server through sshterm, then log on to the DB server with the specified restricted user name through sqlplus, and then perform operations. We are used to the convenience of using the development tool, returning to sqlplus for the first time will be quite uncomfortable, but as long as we set some simple parameters, we can greatly improve the availability of sqlplus;
In addition, you need to export data from the database to the file. By default, the exported file contains a lot of junk information, and we need to clear it, at this time, the related settings are very useful;

Sqlplus is optimized in two ways:
1. system parameter settings are implemented through the set operation (you can use show to view the configuration items, and show all to view the list of all system parameter settings), such as set linesize 180;
2. Set the display format through the column operation, such as column AA format A32;
3. Set session parameters through SQL operations, such as alter session set ...;

The following describes the list of commonly used sqlplus set parameters. For a more comprehensive set, see the Oracle official documentation;
Set linesize 180 set the total number of characters displayed per line
Set pagesize 100 set the number of lines displayed per page
Set feedback on/off to check whether XX rows have been selected
Set heading on/off to set whether to display column names
Set Time on/off: Set whether to display the current system time
Set timing on/off: Set whether to display the time consumed by each SQL statement execution.
Set termout on/off to set whether to print related information on the console when executing the SQL File
Set trimout on/off to set whether to Remove trailing spaces in each row of the standard output
Set trimspool on/off to set whether to Remove trailing spaces of each row in the spool output file

The column command is used to set the display format. The syntax is as follows:
Column columnname [format] [heading]
For example, column login_id format A32 heading trademanager ID
Heading trademanager ID indicates that the login_id field name in the result set is: trademanager ID;
The function is the same as select login_id as trademanager ID from;

The settings of the session parameters are very professional and complex, which is not involved by developers. However, we can set two parameters on our own;
The first is to set the display format of the datetime field. Run the following command in sqlplus:
Alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
Second, set the character encoding of the current session (the encoding varies with the database) to display Chinese characters. For example, execute the following command:
Alter session set nls_lang = 'American _ America. zhs16gbk ';
Of course, these two parameters can also be directly placed in the OS's current user's environment variables, so that you do not have to execute the above commands every time you enter sqlplus, which is much easier;