I published a video a few days ago on the various tools you can use to unload data out of the Oracle Database to a flat file format (CSV in this particular case). I compared SQLcl, SQL*Plus, UTL_FILE and even Pro*C to look at getting the data spooled out as efficiently as possible.
You can check out that video below, but read on to get another cool shortcut.
Spoiler Alert: You more work you put in, the better results you get.
For the SQL*Plus demo, you’ll notice that I had to nominate the markup settings, and also tweaked some SQL*Plus settings a little to get the best query fetch performance. If this all seems too complicated, then here’s a shortcut for you. When you start SQL*Plus, if you know that the purpose of running this SQL*Plus session is to unload some data, you can nominate the MARKUP options right there on the command line at startup.
C:\>sqlplus -MARKUP "CSV ON" scott/tiger@db19_pdb1 SQL*Plus: Release 126.96.36.199.0 - Production on Wed Dec 1 14:42:01 2021 Version 188.8.131.52.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Last Successful login time: Wed Dec 01 2021 14:39:57 +08:00
Not only does that immediately get the output ready for unloading to CSV, behind the scenes, we will also alter the settings to ensure that we get good fetching performance as well. Compare a standard session settings
SQL> show arraysize arraysize 15 SQL> show lobprefetch lobprefetch 0 SQL> show rowprefetch rowprefetch 1 SQL> show statementc statementcache is 0 SQL>
with that of a SQL*Plus session launched with the markup option:
SQL> show arraysize arraysize 100 SQL> show lobprefetch lobprefetch 16384 SQL> show rowprefetch rowprefetch 2 SQL> show statementc statementcache is 20
All of this is covered in the docs as well.