Skip to Main Content
  • Questions

  • How to generate some big test tables and rapidly export their SQL data

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Quanwen.

Asked: May 29, 2018 - 7:48 am UTC

Last updated: June 03, 2018 - 9:00 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hello teams,

Our Developer manager asks me to export Oracle production database's three big tables with CSV or this format "insert ... into ..." for giving another App teams (their database is MySQL 5.7) once again, I remembered that I used PL/SQL Developer (or SQL Developer) to export those tables with two formats previous on "Apr 12th, 2018" last time and it spent more than 8 hours exporting completely. I didn't endured this very slowly exporting speed.

The following is those three tables' numbers of row and data size (case and res are partition table and map is not),

SQL> select count(*) from case;

  COUNT(*)
----------
  18637963  <<== 15.538 GB

SQL> select count(*) from res;

  COUNT(*)
----------
  35961080  <<== 12.914 GB

SQL> select count(*) from map;

  COUNT(*)
----------
  32914852  <<== 4.252 GB


Due to all of tables above (table name has been replaced) comes from Production Environment, hence I wanna seek advice for 2 questions,

(1) how to generate some test tables (particularly, data size is very huge like mine previous)?
(2) how to rapidly export those tables via CSV or "insert ... into ..." (it's better to split up a few small files because the file which I had exported last time is approximately 40 GB, and I didn't open it for viewing via UltraEdit or EditPlus )?

Very appreciate you if any help.

Best Regards
Quanwen Zhao

and Connor said...

Here's some ideas on generating test data

Quick and Basic
===============





More sophisticated
==================
Morten Egan has an extensive suite of tools to generate test data for common data domains at his blog

https://codemonth.dk


If you want to just *copy* existing data from another database, you can use create-table-as-select, or use the QUERY parameter in DataPump to export a subset.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Test table *test1* has generated and how to export pure data via *CSV* rapidly

Quanwen Zhao, May 31, 2018 - 9:07 am UTC

Hello Connor,

According to your video, currently I've created a test table *test1* and my SQL code is as follows,

SQL> set timing on
SQL> 
SQL> create table test1
  2  segment creation immediate
  3  nologging
  4  as
  5  with generator as (
  6          select
  7                  rownum id
  8          from dual
  9          connect by
 10                  level <= 10000000
 11  )
 12  select
 13          rownum                          id,
 14          mod(rownum-1,3)                 val1,
 15          mod(rownum-1,10)                val2,
 16          lpad('x',100,'x')               padding
 17  from
 18          generator       v1
 19  order by
 20          dbms_random.value
 21  ;

Table created.

Elapsed: 00:01:34.24  

SQL> set timing off 

SQL> select count(*) from test1;

  COUNT(*)
----------
  10000000

SQL> select sum(blocks),sum(bytes)/1024/1024 size_mb from user_segments where segment_name = 'TEST1' and segment_type = 'TABLE';

SUM(BLOCKS)    SIZE_MB
----------- ----------
     172032       1344

SQL> select blocks,num_rows from user_tables where table_name = 'TEST1';

    BLOCKS   NUM_ROWS
---------- ----------


SQL> begin
  2          dbms_stats.gather_table_stats(
  3                  ownname     => user,
  4                  tabname     => 'TEST1',
  5                  method_opt  => 'for all columns size 1'
  6          );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select blocks,num_rows from user_tables where table_name = 'TEST1';

    BLOCKS   NUM_ROWS
---------- ----------
    164587   10000000


Due to my another db is *MySQL 5.7*, thus I don't use *EXPDP/EXP* to export test table *test1* on Oracle DB and I just use *SPOOL* to export pure SQL data (format *CSV* or *insert ... into ...*).

By the way, I use *PL/SQL Developer* to export this format's data (*insert ... into ...*) last time (hence its exporting data size is gradually increased to 40 gb and original table's size is about 15.53 gb, maybe due to adding these words *insert ...* and *into ...*).

Now, I don't think that I will use *insert ... into ...*, therefore how to export *test1*'s pure data via *CSV* rapidly?

Best Regards
Quanwen Zhao

using 12.2 and SQLCL

Rajeshwaran, Jeyabal, May 31, 2018 - 11:46 am UTC

Two options to generate CSV output.

option#1 - using SET MARKUP CSV on - in 12c sqlplus and above.

able to get 177MB of CSV file in few seconds.

demo@ORA12C> set markup csv on
demo@ORA12C> @script.sql

Session altered.


"START_DT"
"31-may-2018 17:08:59"


"END_DT"
"31-may-2018 17:09:23"

demo@ORA12C> $dir c:\users\123456\query.lst
 Volume in drive C is OS
 Volume Serial Number is D4D1-2052

 Directory of c:\users\123456

05/31/2018  05:09 PM       177,497,976 query.lst
               1 File(s)    177,497,976 bytes
               0 Dir(s)  99,837,390,848 bytes free

demo@ORA12C> $type c:\users
Access is denied.

demo@ORA12C> $type c:\users\123456\script.sql
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sysdate as start_dt from dual;

spool query.lst
set termout off
select * from big_table;
set termout on
spool off

select sysdate as end_dt from dual;

demo@ORA12C>


Option#2 - would be to use SQLCL utility and leverage SET SQLFORMAT CSV option in that.

Jeff describes them in detail at the below links

https://www.thatjeffsmith.com/archive/2015/02/a-quick-4-1-trick-set-sqlformat/
https://www.thatjeffsmith.com/archive/2015/12/more-set-sqlformat-fun-in-sqlcl/

Since you got tables partitioned, you could also use have queries using partition name /partition extended syntax and have them executed and spooled across different file concurrently.

It hasn't connected to my oracle db server via SQLcl 18.1.1

Quanwen Zhao, June 01, 2018 - 2:06 pm UTC

Hello, Rajeshwaran Jeyabal

Many thanks for your nice advice, because SQLcl utility usually deploys easily hence I download the latest version 18.1.1 from Oracle official website here http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

Unfortunately, afterwards I've deployed it successfully on my oracle db server. However I use Easy Connect String or TNSNAME to connect oracle db via SQLcl 18.1.1, and it don't connect successfully.

The following is my issue,

[oracle@oracle-test01 ~]$ sql -V
SQLcl: Release 18.1.1.0 Production

[oracle@oracle-test01 ~]$ java -version
java version "1.8.0_172"
Java(TM) SE Runtime Environment (build 1.8.0_172-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.172-b11, mixed mode)

[oracle@oracle-test01 ~]$ which java
/usr/java/jdk1.8.0_172-amd64/bin/java



[oracle@oracle-test01 ~]$ sql test/test@192.168.10.20:1521/test

SQLcl: Release 18.1.1 Production on Fri Jun 01 21:33:00 2018

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

  USER          = test

  URL           = jdbc:oracle:oci8:@192.168.10.20:1521/test

  Error Message = Could not initialize class oracle.jdbc.OracleDriver

  USER          = test

  URL           = jdbc:oracle:thin:@192.168.10.20:1521/test

  Error Message = Could not initialize class oracle.jdbc.OracleDriver

Username? (RETRYING) ('test/*********@192.168.10.20:1521/test'?)


At the same time my cursor key is always flashing in the last line (after the right parenthesis) above.

Best Regards
Quanwen Zhao
Connor McDonald
June 03, 2018 - 9:00 am UTC

Normally means ORACLE_HOME is set and so we're looking in there

thanks for your feedback about installing SQLcl 18.1.1 on 11.2.0.4.0 oracle db server

Quanwen Zhao, June 04, 2018 - 4:06 am UTC

Hi Connor,

Yep, $ORACLE_HOME actually conflicts with the $JAVA_HOME my new deployed on oracle user on Linux.

This issue has been just occurred on oracle db server (11.2.0.4.0) I've installed SQLCL 18.1.1 (need a JRE 1.8.0_172 and have no problem on client machine), please take a look at here (my post on ODC) https://community.oracle.com/thread/4149634

At the same time I found that the JRE version is 1.8.0._91 on oracle 12.2.0.1.0 for Windows x86_64. Please see some steps as follows,

C:\Users\Administrator>ora12cr2

C:\Users\Administrator>cd \

C:\>sqlplus -v

SQL*Plus: Release 12.2.0.1.0 Production  <<==

C:\>cd c:\app\Administrator\virtual\product\12.2.0\dbhome_1\jdk\bin

c:\app\Administrator\virtual\product\12.2.0\dbhome_1\jdk\bin>java -version
java version "1.8.0_91"  <<==
Java(TM) SE Runtime Environment (build 1.8.0_91-b61)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b61, mixed mode)


Best Regards
Quanwen Zhao

I found 2 good solutions for connecting oracle db server via SQLcl 18.1.1

Quanwen Zhao, June 07, 2018 - 8:27 am UTC

Hi, Connor

I found 2 good solutions for connecting oracle db server via SQLcl 18.1.1.

(1) I've created other two SHELL script files ".sqlcl1811" and ".sqlcl1811_out" on directory "/home/oracle/" on oracle user.

[oracle@test ~]$ cat .sqlcl1811
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export ORACLE_BASE=
export ORACLE_HOME=
export ORACLE_SID=
export SQLCL_HOME=/home/oracle/sqlcl
export CLASSPATH=$SQLCL_HOME/lib:$CLASSPATH
export PATH=$SQLCL_HOME/bin:$PATH



[oracle@test ~]$ cat .sqlcl1811_out
#!/bin/bash

. ~/.bash_profile;

export SQLCL_HOME=
export CLASSPATH=



[oracle@test ~]$ . .sqlcl1811
[oracle@test ~]$ 
[oracle@test ~]$ sql test/test@test

SQLcl: Release 18.1.1 Production on Thu Jun 07 14:45:15 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options


SQL> show java
Java Detail
-----------
java.home= /usr/java/jdk1.8.0_172-amd64/jre
java.vendor= Oracle Corporation
java.vendor.url= http://java.oracle.com/
java.version= 1.8.0_172
-----------------------------------------------------------------------------------------------------------------------
os.arch= amd64
os.name= Linux
os.version= 2.6.32-504.el6.x86_64
path.separator= :
file.separator= /
line.separator= 

user.dir= /home/oracle
user.home= /home/oracle
user.name= oracle
-----------------------------------------------------------------------------------------------------------------------
Classpath=
/home/oracle/sqlcl/lib/dbtools-sqlcl.jar:/home/oracle/sqlcl/../rdbms/jlib/xdb6.jar:/home/oracle/sqlcl/../jdbc/lib/ojdbc
8.jar:/home/oracle/sqlcl/../jlib/orai18n-utility.jar:/home/oracle/sqlcl/../jlib/orai18n-mapping.jar:/home/oracle/sqlcl/
../jlib/orai18n.jar:/home/oracle/sqlcl/../modules/oracle.xdk/xmlparserv2.jar:/home/oracle/sqlcl/lib/javax.json.jar:/hom
e/oracle/sqlcl/lib/xmlparserv2.jar:/home/oracle/sqlcl/lib/commons-logging.jar:/home/oracle/sqlcl/lib/orai18n.jar:/home/
oracle/sqlcl/lib/xmlparserv2-sans-jaxp-services.jar:/home/oracle/sqlcl/lib/orai18n-collation.jar:/home/oracle/sqlcl/lib
/jackson-databind.jar:/home/oracle/sqlcl/lib/xdb6.jar:/home/oracle/sqlcl/lib/jackson-core.jar:/home/oracle/sqlcl/lib/st
ringtemplate.jar:/home/oracle/sqlcl/lib/oraclepki.jar:/home/oracle/sqlcl/lib/jackson-annotations.jar:/home/oracle/sqlcl
/lib/osdt_core.jar:/home/oracle/sqlcl/lib/httpmime.jar:/home/oracle/sqlcl/lib/osdt_cert.jar:/home/oracle/sqlcl/lib/http
core.jar:/home/oracle/sqlcl/lib/orajsoda.jar:/home/oracle/sqlcl/lib/httpclient.jar:/home/oracle/sqlcl/lib/orai18n-utili
ty.jar:/home/oracle/sqlcl/lib/ojdbc8.jar:/home/oracle/sqlcl/lib/dbtools-sqlcl.jar:/home/oracle/sqlcl/lib/dbtools-net.ja
r:/home/oracle/sqlcl/lib/dbtools-http.jar:/home/oracle/sqlcl/lib/dbtools-common.jar:/home/oracle/sqlcl/lib/orai18n-serv
let.jar:/home/oracle/sqlcl/lib/jsch.jar:/home/oracle/sqlcl/lib/commons-codec.jar:/home/oracle/sqlcl/lib/jdbcrest.jar:/h
ome/oracle/sqlcl/lib/orai18n-mapping.jar:/home/oracle/sqlcl/lib/jline.jar:/home/oracle/sqlcl/lib/antlr-runtime.jar:/hom
e/oracle/sqlcl/lib:
-----------------------------------------------------------------------------------------------------------------------
SQL> exit
                                                                                              
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@test ~]$ 
[oracle@test ~]$ . .sqlcl1811_out
######################################################################################
#                                                                                    #
#     +-------+     +-------        +         +-------/  +            +-------/ (R)  #
#    (         )    |       )      / \        (          |            (              #
#   (           )   |      )      /   \      (           |           (               #
#  (             )  |------      /     \    (            |          (-------/        #
#   (           )   |    \      / ----- \    (           |           (               #
#    (         )    |     \    |         |    (          |       /    (              #
#     +-------+     -      --  -         -    +-------/  +-------+    +-------/      #
#                                                                                    #
#                                                                                    #
#                                              +----------------------------------+  #
#                                              | Applications & Platform Services |  #
#                                              +----------------------------------+  #
#                                                                                    #
######################################################################################


(2) I've created another user "sqlcl" on Linux system and added a series of "export" command on ".bash_profile" (sqlcl's environment configuration file). Of course I've installed SQLcl 18.1.1 on sqlcl user.

[oracle@test ~]$ su - sqlcl
Password: 
[sqlcl@test ~]$ id sqlcl
uid=501(sqlcl) gid=503(sqlcl) groups=503(sqlcl)
[sqlcl@test ~]$ 
[sqlcl@test ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export SQLCL_HOME=/home/sqlcl/sqlcl
export CLASS_PATH=$SQLCL_HOME/lib:$CLASS_PATH
export PATH=$SQLCL_HOME/bin:$PATH

[sqlcl@test ~]$ sql test/test@test

SQLcl: Release 18.1.1 Production on Thu Jun 07 16:12:11 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options


SQL> show java
Java Detail
-----------
java.home= /usr/java/jdk1.8.0_172-amd64/jre
java.vendor= Oracle Corporation
java.vendor.url= http://java.oracle.com/
java.version= 1.8.0_172
-----------------------------------------------------------------------------------------------------------------------
os.arch= amd64
os.name= Linux
os.version= 2.6.32-504.el6.x86_64
path.separator= :
file.separator= /
line.separator= 

user.dir= /home/sqlcl
user.home= /home/sqlcl
user.name= sqlcl
-----------------------------------------------------------------------------------------------------------------------
Classpath=
/home/sqlcl/sqlcl/lib/dbtools-sqlcl.jar:/home/sqlcl/sqlcl/../rdbms/jlib/xdb6.jar:/home/sqlcl/sqlcl/../jdbc/lib/ojdbc8.j
ar:/home/sqlcl/sqlcl/../jlib/orai18n-utility.jar:/home/sqlcl/sqlcl/../jlib/orai18n-mapping.jar:/home/sqlcl/sqlcl/../jli
b/orai18n.jar:/home/sqlcl/sqlcl/../modules/oracle.xdk/xmlparserv2.jar:/home/sqlcl/sqlcl/lib/javax.json.jar:/home/sqlcl/
sqlcl/lib/xmlparserv2.jar:/home/sqlcl/sqlcl/lib/commons-logging.jar:/home/sqlcl/sqlcl/lib/orai18n.jar:/home/sqlcl/sqlcl
/lib/xmlparserv2-sans-jaxp-services.jar:/home/sqlcl/sqlcl/lib/orai18n-collation.jar:/home/sqlcl/sqlcl/lib/jackson-datab
ind.jar:/home/sqlcl/sqlcl/lib/xdb6.jar:/home/sqlcl/sqlcl/lib/jackson-core.jar:/home/sqlcl/sqlcl/lib/stringtemplate.jar:
/home/sqlcl/sqlcl/lib/oraclepki.jar:/home/sqlcl/sqlcl/lib/jackson-annotations.jar:/home/sqlcl/sqlcl/lib/osdt_core.jar:/
home/sqlcl/sqlcl/lib/httpmime.jar:/home/sqlcl/sqlcl/lib/osdt_cert.jar:/home/sqlcl/sqlcl/lib/httpcore.jar:/home/sqlcl/sq
lcl/lib/orajsoda.jar:/home/sqlcl/sqlcl/lib/httpclient.jar:/home/sqlcl/sqlcl/lib/orai18n-utility.jar:/home/sqlcl/sqlcl/l
ib/ojdbc8.jar:/home/sqlcl/sqlcl/lib/dbtools-sqlcl.jar:/home/sqlcl/sqlcl/lib/dbtools-net.jar:/home/sqlcl/sqlcl/lib/dbtoo
ls-http.jar:/home/sqlcl/sqlcl/lib/dbtools-common.jar:/home/sqlcl/sqlcl/lib/orai18n-servlet.jar:/home/sqlcl/sqlcl/lib/js
ch.jar:/home/sqlcl/sqlcl/lib/commons-codec.jar:/home/sqlcl/sqlcl/lib/jdbcrest.jar:/home/sqlcl/sqlcl/lib/orai18n-mapping
.jar:/home/sqlcl/sqlcl/lib/jline.jar:/home/sqlcl/sqlcl/lib/antlr-runtime.jar:
-----------------------------------------------------------------------------------------------------------------------
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[sqlcl@test ~]$ exit
logout
[oracle@test ~]$


Best Regards
Quanwen Zhao
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.