Oracle 23c export table data to csv via sql Tips

You can take advantage of the CSV sql format in SQLcl to pump your data out in that format. And use a parallel hint to have many processes running your query. Then you just need to spool the output:

[oracle@ol8-23c ~]$ source .bash_profile
[oracle@ol8-23c ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/23.0.0/dbhome_1
[oracle@ol8-23c ~]$ cat 1.sql 
set term off
set feed off
set sqlformat csv
spool out.csv
select /*+ parallel(2) */* from t1 where rownum < 100;
spool off
exit
[oracle@ol8-23c ~]$ cat exp01.sh 
#!/bin/bash

CONN_STR="hong/oracle@pdb1"
$ORACLE_HOME/sqldeveloper/sqldeveloper/bin/sql $CONN_STR @/home/oracle/1.sql

exit 0
[oracle@ol8-23c ~]$ nohup sh exp01.sh &
[1] 120207
[oracle@ol8-23c ~]$ nohup: ignoring input and appending output to 'nohup.out'
[oracle@ol8-23c ~]$ 
[1]+  Done                    nohup sh exp01.sh
[oracle@ol8-23c ~]$

set colsep ,     -- separate columns with a comma
set pagesize 0   -- No header rows
set trimspool on -- remove trailing blanks
set headsep off  -- this may or may not be useful...depends on your headings.
set linesize X   -- X should be the sum of the column widths
set numw X       -- X should be the length you want for numbers (avoid scientific notation on IDs)

spool myfile.csv

select table_name, tablespace_name
from all_tables
where owner = 'SYS'
 and tablespace_name is not null;
spool Off;

set markup csv on
spool filename.csv
select * from <tablename>
spool off

SELECT /*csv*/ * FROM scott.emp;
SELECT /*xml*/ * FROM scott.emp;
SELECT /*html*/ * FROM scott.emp;
SELECT /*delimited*/ * FROM scott.emp;
SELECT /*insert*/ * FROM scott.emp;
SELECT /*loader*/ * FROM scott.emp;
SELECT /*fixed*/ * FROM scott.emp;
SELECT /*text*/ * FROM scott.emp;

SET sqlformat csv
 
SELECT * FROM scott.emp;

[oracle@ol8-23c ~]$ which sql
/u01/app/oracle/product/23.0.0/dbhome_1/bin/sql
[oracle@ol8-23c ~]$ sql hong/oracle@pdb1


SQLcl: Release 21.4 Production on Wed Dec 21 11:46:12 2022

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

Last Successful login time: Wed Dec 21 2022 11:46:13 +08:00

Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0

HONG@pdb1> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

HONG@pdb1> SET EDITFILE .sqlplus.buf
HONG@pdb1> SET NULL "<n>"
HONG@pdb1> SET TIMING ON
HONG@pdb1> SET TIME ON
11:46:13 HONG@pdb1> SET TRIMSPOOL ON
11:46:14 HONG@pdb1> SET FEEDBACK 1
11:46:14 HONG@pdb1> SET TAB OFF
11:46:14 HONG@pdb1> select /*csv*/ * from t1 where rownum <10;

"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED","APPLICATION","DEFAULT_COLLATION","DUPLICATED","SHARDED","IMPORTED_OBJECT","SYNCHRONOUS_DUPLICATED","CREATED_APPID","CREATED_VSNID","MODIFIED_APPID","MODIFIED_VSNID"
"SYS","I_FILE#_BLOCK#",<n>,9,9,"INDEX",2022-10-07 08:17:38,2022-10-07 08:17:38,"2022-10-07:08:17:38","VALID","N","N","N",4,<n>,"NONE",<n>,"Y","N",<n>,"N","N","N","N",<n>,<n>,<n>,<n>
"SYS","I_OBJ3",<n>,38,38,"INDEX",2022-10-07 08:17:38,2022-10-07 08:17:38,"2022-10-07:08:17:38","VALID","N","N","N",4,<n>,"NONE",<n>,"Y","N",<n>,"N","N","N","N",<n>,<n>,<n>,<n>
"SYS","I_TS1",<n>,45,45,"INDEX",2022-10-07 08:17:38,2022-10-07 08:17:38,"2022-10-07:08:17:38","VALID","N","N","N",4,<n>,"NONE",<n>,"Y","N",<n>,"N","N","N","N",<n>,<n>,<n>,<n>
"SYS","I_CON1",<n>,51,51,"INDEX",2022-10-07 08:17:38,2022-10-07 08:17:38,"2022-10-07:08:17:38","VALID","N","N","N",4,<n>,"NONE",<n>,"Y","N",<n>,"N","N","N","N",<n>,<n>,<n>,<n>
"SYS","IND$",<n>,19,2,"TABLE",2022-10-07 08:17:38,2022-10-07 09:29:31,"2022-10-07:08:17:38","VALID","N","N","N",1,<n>,"METADATA LINK",<n>,"Y","N","USING_NLS_COMP","N","N","N","N",<n>,<n>,<n>,<n>
"SYS","CDEF$",<n>,31,29,"TABLE",2022-10-07 08:17:38,2022-10-07 09:29:31,"2022-10-07:08:17:38","VALID","N","N","N",1,<n>,"METADATA LINK",<n>,"Y","N","USING_NLS_COMP","N","N","N","N",<n>,<n>,<n>,<n>
"SYS","C_TS#",<n>,6,6,"CLUSTER",2022-10-07 08:17:38,2022-10-07 08:17:38,"2022-10-07:08:17:38","VALID","N","N","N",5,<n>,"METADATA LINK",<n>,"Y","N",<n>,"N","N","N","N",<n>,<n>,<n>,<n>
"SYS","I_CCOL2",<n>,58,58,"INDEX",2022-10-07 08:17:38,2022-10-07 08:17:38,"2022-10-07:08:17:38","VALID","N","N","N",4,<n>,"NONE",<n>,"Y","N",<n>,"N","N","N","N",<n>,<n>,<n>,<n>
"SYS","I_PROXY_DATA$",<n>,24,24,"INDEX",2022-10-07 08:17:38,2022-10-07 08:17:38,"2022-10-07:08:17:38","VALID","N","N","N",4,<n>,"NONE",<n>,"Y","N",<n>,"N","N","N","N",<n>,<n>,<n>,<n>

9 rows selected. 

Elapsed: 00:00:00.160
11:46:30 HONG@pdb1>