Saving Oracle learning database data as files

Saving Oracle learning database data as files

It is often necessary to generate documents from data in Oracle databases. Because you prefer the script method, you need to set the format when using spool, the following is a simple example of how to set the format in Oracle. There are a total of 18 items. In fact, there are only a few commonly used items. I will attach a script for simple shell operations I wrote later, we hope that we can communicate with each other as needed and use it as our own backup.

The meaning of the set command:
Arraysize indicates the number of rows extracted from the database at a time. The default value is 15.
Whether autocommit is automatically submitted. The default value is off.
Colsep delimiter between selected columns. The default Delimiter is space.
When echo runs a script file using the start command, the echo command is used to control whether the SQL statements being executed in the script file are displayed. The default value is off.
Feedback when a query selects at least n rows of records, the number of returned rows is displayed in the items in the result set. The default value is 6.
Whether heading displays the column title of the query result. The default value is on
Headsep specifies the punctuation marks behind it to display the tab or column title branch. The default value is "|"
Linesize: the number of characters displayed per line, that is, the width. The default value is 80.
Number of blank lines between The newpage separator page and page
Pagesize: the number of lines displayed per page. The default value is 14.
Whether to pause output on each page. If pause text is set, text is displayed in the lower left corner.
Whether serveroutput displays pl/SQL block or stored procedure output. That is, the output of the function DBMS_OUTPUT.PUT_LINE () is allowed to be displayed on the screen.
Sqlprompt sqlplus command prompt, the default is "SQL>"
Whether to display the current time of the system before the sqlplus command prompt. The default value is off.
Whether timing displays the time spent on executing SQL statements and pl/SQL blocks. The default value is off.
Whether the trimspool outputs unnecessary spaces behind each row in the spool output. The default value is off.
The character of underline. The default value is "_".
The replacement variable used for verify interaction is whether to list the text content of an SQL statement before and after obtaining the value of the replacement variable. The default value is on

Note:
Set the format of a xxx command:

Set xxx on/off/value

Example: set timing on -- set the time spent on executing SQL statements and pl/SQL blocks.

Set pagesize 100 -- set to display 100 rows per page
The format of a xxx command is show xxx.
Example: show timing -- check whether the current timing is set to on/off?

Show pagesize -- display the number of lines per page currently set

Reference:
<Oracle from entry to entry> Qian shenyi, A Suzhi, water conservancy and hydropower Publishing House, Chapter 2009.9, text, 140: 5.3.2

Oracle 11g download from entry to proficient in PDF + CD source code