Export timed query results from SQL*Plus
| Lastmod: 2019-05-08
At work, we have to SSH into an ubuntu box in order to run queries. Since this box has no GUI, all work must be done through command line and SQL*Plus.
Sadly, pasting multi-line queries into SQL*Plus is a no-go. And copying query results, if they’re more than a few dozen lines, isn’t much better. So, after trial and error, here’s my solution:
- Copy the multi-line query from my host (macOS)
- Create a file on ubuntu box using vi, then paste in the query. Save it as
- Open SQL*Plus and make a connection to the Oracle database
- Run the query file by entering
- The timed results are exported to a file named
- Use SCP to copy
output.txtfrom the ubuntu box to my host
Here’s an example query file. Note, the query must end with a semicolon.
SPOOL output.txt TIMING START timer <query_goes_here> TIMING STOP timer SPOOL OFF
And an example SCP command.
scp <username>@<host>:/path/to/output.txt ~/Desktop