How can Oracle export a large amount of data to a text file?

1 year ago
Sophia Anderson
2 minutes

Oracle has the ability to export large amounts of data to a text file using either SQLPlus or PL/SQL Developer tools. Here is the method using SQLPlus:

  1. Open the command prompt window and log in to the Oracle database by entering sqlplus.
  2. Connect to the database from which you want to export data using the following command:
CONNECT username/password@host:port/service_name

In which, the username is the database username, password is the password, host is the database hostname, port is the database port number, and service_name is the database service name.

  1. Enter the following command to set the export format and options:
SET PAGESIZE 0
SET LINESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF

PAGESIZE determines the number of rows per page, LINESIZE determines the number of characters per line, FEEDBACK controls the display of feedback information, VERIFY controls the display of SQL statements, and HEADING controls the display of column headers.

  1. Enter the following command to export the data to a text file:
SPOOL file_path/file_name.txt
SELECT * FROM table_name;
SPOOL OFF

Among them, “file_path” refers to the path of the exported file, “file_name” refers to the name of the exported file, and “table_name” refers to the name of the table to export data from.

  1. Enter the EXIT command to exit SQL*Plus.

In this way, the data will be exported to the designated text file.

bannerAds