Oracle exports data to TXT

Source: Internet
Author: User
Tags sqlplus

using the Sqlplus spool command  
Operation Steps
1. Create a new script file and store it, such as D:\spool.sql, with the following code

Set echo off-when executing an SQL script with the start command, displays the SQL statement being executed in the script set feedback off-whether to display the current SQL statement query or modify the number of rows set newpage none-- There is no interval between the page and the page set verify off--set pagesize 0--How many pages set term off--when the contents of a large table are output to a file with the spool command, it takes a lot of time to output the content on the screen, setting the set Termspool off, the output will only be saved in the output file, not displayed on the screen, greatly improving the speed of spool. Set trims on--spool the extra space after each line in the output to remove set Linesize 600--Set the screen to display the width of the line, default to set heading off--Prohibit output column header set timing off--default is off, set Query time-consuming, can be used to estimate the execution time of SQL statements, test performance set Numwidth 38SPOOL D:\aaa.txtselect Usertelno | | ', ' | | To_char (Receivetime, ' yyyy-mm-dd HH24:MI:SS ') | | ', ' | | usercontent | | ', ' | | Replycontent from Logsmshall_mutual WHERE rownum<=100; SPOOL OFF



Note that if you have a CLOB field, it is best to convert it with the To_char function.
2. Log in to the Oracle database with Sqlplus
If the database is native, directly in the command line (win+r->cmd) window, enter "SQLPlus [user name]/[Password]".
if telnet, enter "SQLPlus [username]//password]@[database IP address: Listening port number/[DB instance Name]" on the command line, and enter. such as "Sqlplus ajita/[email PROTECTED]:1521/ORCL".
3. Execute the script file
In the sqlplus Command window, execute the script file in step 1. Command mode is "@[script file location", such as "@D: \spool.sql"

Advantages: Simple to use
Disadvantages: not easy to encapsulate into stored procedures

Oracle exports data to TXT

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.