Oracle database client has an sqlplus component that allows us to perform some database operations on the command line. In order to improve the efficiency of dump file writing, we try to use sqlplus command to generate dump file.

sqlplus can specify running scripts, such as:

sqlplus admin/pwd@//127.0.0.1:1526/orcl@./tmp_sql/NODE_dump_sql_20180516130228316.sql

In this script, we can open a file by spool command, and then write the result of sql query to this file.

The code is as follows:

package com.code.dump;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DumpFile2 {
	/** Database connection parameters */
	static final String DB_HOST = "127.0.0.1:1526";
	static final String DB_SID = "oracl";
	static final String DB_USER = "admin";
	static final String DB_PWD = "pwd";
	static final String TABLE_NAME = "NODE";
	/** The number of characters per line in the spliced sql should be changed when it exceeds ROW_CHAR_COUNT to prevent sql from being too long. */
	static final int ROW_CHAR_COUNT = 1000;
	/** dump File save path */
	static final String DUMP_FILE_PATH = "./dump_file";
	/** sqlPlus Execution script storage path */
	static final String SQL_PLUS_FILE_PATH = "./tmp_sql";
	static Connection connection = null;

	public static void main(String[] args) {
		try {
			long start = System.currentTimeMillis();
			String sql = generateInsertSql();
			generateDumpFileBySqlPlus(sql);
			System.out.println("---------------------------\n"+"all cost time is " + (System.currentTimeMillis() - start) + " ms"+"\n-----------------------------\n");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/** Generate sql statements for use */
	static String generateInsertSql() throws Exception {
		// Store column_name,data_type of columns of tables that need to be backed up
		List<ColumnInfo> cols = new ArrayList<ColumnInfo>();
		// Create oracle database connection
		Class.forName("oracle.jdbc.driver.OracleDriver");
		connection = DriverManager.getConnection("jdbc:oracle:thin:@" + DB_HOST + ":" + DB_SID, DB_USER, DB_PWD);
		Statement sql = connection.createStatement();

		// Query all column information of the table that needs to be backed up
		ResultSet columns = sql.executeQuery("select column_name, data_type from user_tab_columns where table_name='" + TABLE_NAME.toUpperCase() + "'");
		while (columns.next()) {
			ColumnInfo columnInfo = new ColumnInfo(columns.getString(1), columns.getString(2));
			cols.add(columnInfo);
		}

		// The first half of splicing insert
		// eg:insert into charge_value_lookup(RESULT_ID,TAX_IDS,FEE_IDS) values(
		StringBuffer insertSql = new StringBuffer("insert into " + TABLE_NAME + "(");
		int index = 1;
		for (ColumnInfo col : cols) {
			if (insertSql.length() > ROW_CHAR_COUNT * index) {
				insertSql.append("\n");
				index++;
			}
			insertSql.append(col.getColumnName()).append(",");
		}
		insertSql.delete(insertSql.length() - 1, insertSql.length());
		insertSql.append(") values(");
		System.out.println("---------------------------\n"+insertSql.toString()+"\n-----------------------------\n");

		// Query statement splicing sql calls
		// eg:select 'insert into charge_value_lookup(RESULT_ID,TAX_IDS,FEE_IDS) values(' ||''''||RESULT_ID||''''||','||''''||TAX_IDS||''''||','||''''||FEE_IDS||''''||');' from charge_value_lookup
		StringBuffer excSql = new StringBuffer("select '").append(insertSql).append("' ");
		index = 1;
		for (int i = 0; i < cols.size(); i++) {
			if (excSql.length() > ROW_CHAR_COUNT * index) {
				excSql.append("\n");
				index++;
			}
			ColumnInfo columnInfo = cols.get(i);
			switch (columnInfo.getDataType()) {
			case "CHAR":
			case "VARCHAR2":
				excSql.append("||").append("''''").append("||").append(columnInfo.getColumnName()).append("||").append("''''");
				break;
			case "NUMBER":
			case "FLOAT":
			case "LONG":
				excSql.append("||").append("nvl(to_char(" + columnInfo.getColumnName() + ")," + columnInfo.getColumnName() + "||'null')");
				break;
			case "DATE":
				break;
			default:
				throw new Exception("Table column types are not supported");
			}
			if (i != cols.size() - 1) {
				excSql.append("||").append("','");
			} else {
				excSql.append("||');'");
			}
		}
		excSql.append(" from " + TABLE_NAME + "");

		System.out.println("---------------------------\n"+excSql.toString()+"\n-----------------------------\n");


		return excSql.toString();
	}

	/** Generating dump file by sqlPlus operation */
	private static void generateDumpFileBySqlPlus(String sql) throws Exception {
		// Create a File Storage Directory
		File directory = new File(DUMP_FILE_PATH);
		if (!directory.exists()) {
			directory.mkdirs();
		}
		directory = new File(SQL_PLUS_FILE_PATH);
		if (!directory.exists()) {
			directory.mkdirs();
		}
		// sql script invoked by sqlPlus
		String genSqlPlusFilePath = SQL_PLUS_FILE_PATH + "/" + TABLE_NAME + "_dump_sql_" + getTimeStamp() + ".sql";
		if (!Files.exists(Paths.get(genSqlPlusFilePath))) {
			Files.createFile(Paths.get(genSqlPlusFilePath));
		}
		// Generated dump file
		String dumpFilePath = DUMP_FILE_PATH + "/" + TABLE_NAME + "_dump_" + getTimeStamp() + ".dump";
		if (!Files.exists(Paths.get(dumpFilePath))) {
			Files.createFile(Paths.get(dumpFilePath));
		}

		// sqlPlus command header
		final String[] headers = new String[] { "set echo off;", "set feedback off;", "set term off;", "set heading off;", "set sqlblanklines off;",
				"set linesize 5000;", "spool " + dumpFilePath + ";" };

		final String[] tails = new String[] { "spool off;", "exit;" };

		// Create sql files that sqlplus can execute
		FileChannel fileChannel = FileChannel.open(Paths.get(genSqlPlusFilePath), StandardOpenOption.READ, StandardOpenOption.WRITE);
		for (String header : headers) {
			fileChannel.write(ByteBuffer.wrap((header + "\n").getBytes()));
		}
		fileChannel.write(ByteBuffer.wrap((sql + ";\n").getBytes()));
		for (String tail : tails) {
			fileChannel.write(ByteBuffer.wrap((tail + "\n").getBytes()));
		}
		fileChannel.close();

		String sqlPlusCmd = "sqlplus " + DB_USER + "/" + DB_PWD + "@//" + DB_HOST + "/" + DB_SID + " @" + genSqlPlusFilePath;
		System.out.println(sqlPlusCmd);
		Process process = Runtime.getRuntime().exec(sqlPlusCmd);
		process.waitFor();
		InputStream is = process.getInputStream();
		printCmdOutMsg(is);
		
	}

	static String getTimeStamp() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
		return sdf.format(new Date());
	}
	
	static void printCmdOutMsg(InputStream is) throws IOException {
		System.out.println("----------------start----------------------");
		BufferedReader reader = new BufferedReader(new InputStreamReader(is, Charset.forName("gbk")));
		String out = reader.readLine();
		while (out != null) {
			System.out.println(out);
			out = reader.readLine();
		}
		System.out.println("----------------end----------------------");
	}

}

Output of the program:

---------------------------
insert into NODE(TENANT_ID,NODE_ID,PARENT_NODE_ID,NAME,NODE_TYPE_ID,HIERARCHY_TYPE_ID,ACCOUNT_ID,SHORTCUT_NODE_ID,SHORTCUT,CHECK_ID,CHECK_STATUS,SCHEDULE_ID,CHECKED_BY,CHECKED_DATE,ADDED_BY,ADDED_DATE,UPDATED_BY,UPDATED_DATE,NAME_1,NAME_2,NAME_3) values(
-----------------------------

---------------------------
select 'insert into NODE(TENANT_ID,NODE_ID,PARENT_NODE_ID,NAME,NODE_TYPE_ID,HIERARCHY_TYPE_ID,ACCOUNT_ID,SHORTCUT_NODE_ID,SHORTCUT,CHECK_ID,CHECK_STATUS,SCHEDULE_ID,CHECKED_BY,CHECKED_DATE,ADDED_BY,ADDED_DATE,UPDATED_BY,UPDATED_DATE,NAME_1,NAME_2,NAME_3) values(' ||''''||TENANT_ID||''''||','||nvl(to_char(NODE_ID),NODE_ID||'null')||','||nvl(to_char(PARENT_NODE_ID),PARENT_NODE_ID||'null')||','||''''||NAME||''''||','||nvl(to_char(NODE_TYPE_ID),NODE_TYPE_ID||'null')||','||nvl(to_char(HIERARCHY_TYPE_ID),HIERARCHY_TYPE_ID||'null')||','||''''||ACCOUNT_ID||''''||','||nvl(to_char(SHORTCUT_NODE_ID),SHORTCUT_NODE_ID||'null')||','||''''||SHORTCUT||''''||','||nvl(to_char(CHECK_ID),CHECK_ID||'null')||','||nvl(to_char(CHECK_STATUS),CHECK_STATUS||'null')||','||nvl(to_char(SCHEDULE_ID),SCHEDULE_ID||'null')||','||''''||CHECKED_BY||''''||','||''''||CHECKED_DATE||''''||','||''''||ADDED_BY||''''||','||''''||ADDED_DATE||''''||','||''''||UPDATED_BY||''''||','||''''||UPDATED_DATE||''''||','||''''||NAME_1||''''||','
||''''||NAME_2||''''||','||''''||NAME_3||''''||');' from NODE
-----------------------------

sqlplus admin/pwd@//127.0.0.1:1526/orcl@./tmp_sql/NODE_dump_sql_20180516130228316.sql
----------------start----------------------

SQL*Plus: Release 11.2.0.1.0 Production on Wednesday, May 1613:02:32 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


//Connect to: 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

//From Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options To break off
----------------end----------------------
---------------------------
all cost time is 57403 ms
-----------------------------

dump file content and Java project generates Oracle database dump file (1) The dump file inside is the same.

In this implementation, we first spell out the select'Insert in...'statement, then we write the SQL statement and some sqlplus command headers into a temporary file under tmp_sql, which is as follows:

set echo off;
set feedback off;
set term off;
set heading off;
set sqlblanklines off;
set linesize 5000;
spool ./dump_file/NODE_dump_20180516130228320.dump;
select 'insert into NODE(TENANT_ID,NODE_ID,PARENT_NODE_ID,NAME,NODE_TYPE_ID,HIERARCHY_TYPE_ID,ACCOUNT_ID,SHORTCUT_NODE_ID,SHORTCUT,CHECK_ID,CHECK_STATUS,SCHEDULE_ID,CHECKED_BY,CHECKED_DATE,ADDED_BY,ADDED_DATE,UPDATED_BY,UPDATED_DATE,NAME_1,NAME_2,NAME_3) values(' ||''''||TENANT_ID||''''||','||nvl(to_char(NODE_ID),NODE_ID||'null')||','||nvl(to_char(PARENT_NODE_ID),PARENT_NODE_ID||'null')||','||''''||NAME||''''||','||nvl(to_char(NODE_TYPE_ID),NODE_TYPE_ID||'null')||','||nvl(to_char(HIERARCHY_TYPE_ID),HIERARCHY_TYPE_ID||'null')||','||''''||ACCOUNT_ID||''''||','||nvl(to_char(SHORTCUT_NODE_ID),SHORTCUT_NODE_ID||'null')||','||''''||SHORTCUT||''''||','||nvl(to_char(CHECK_ID),CHECK_ID||'null')||','||nvl(to_char(CHECK_STATUS),CHECK_STATUS||'null')||','||nvl(to_char(SCHEDULE_ID),SCHEDULE_ID||'null')||','||''''||CHECKED_BY||''''||','||''''||CHECKED_DATE||''''||','||''''||ADDED_BY||''''||','||''''||ADDED_DATE||''''||','||''''||UPDATED_BY||''''||','||''''||UPDATED_DATE||''''||','||''''||NAME_1||''''||','
||''''||NAME_2||''''||','||''''||NAME_3||''''||');' from NODE;
spool off;
exit;

Finally, we call the sqlplus command through the Runtime.getRuntime().exec method, start a process to execute the script, and get our dump file.

The output results show that for dump file generation of the same table, this method takes 57 seconds, which is 6 seconds faster than the previous 63 seconds, and the speed improvement is not obvious.

Moreover, this method requires the installation of sqlplus tools on the server. Because dump files are generated by the Runtime startup process, the abnormal rollback operation is not easy to handle.

Later, it was confirmed that the dump file we needed to generate was not actually the insert text file we generated here, but a file format for importing/exporting data from Oracle database. Therefore, there are the following schemes. Java project generates oracle database dump file (3).