Posted by 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 19.0.0.0.0 - Production on Wed Dec 1 14:42:01 2021
Version 19.12.0.0.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.
Learning is not a spectator sport 