Export timed query results from SQL*Plus
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