Oracle SPOOL Command - Exercise

This exercise is worth 10 points. Use the solution on the result page to assess your response:

• Correct SPOOL, SELECT, and SPOOL OFF commands: 4 points.
• Correct explanation of the file location: 2 points.
• Correct explanation of the tables listed: 2 points.
• Confirmation that you checked the file, or a clear description of the expected output if you cannot run SQL*Plus: 2 points.

The website displays your submission and a sample solution. It does not execute your commands or verify your local output file.

1. Open SQL*Plus and connect to your intended database or pluggable database (PDB) using an ordinary account permitted to connect. For example, replace the following placeholders with your username and configured Oracle Net service name:

   CONNECT your_username@your_service

   Enter your password when prompted. This exercise does not require SYSDBA privileges or a change to the database connection mode.

2. Choose a writable directory on the computer running SQL*Plus. Then enter these commands:

   SPOOL output.log REPLACE
   
   SELECT owner, table_name
   FROM all_tables
   ORDER BY owner, table_name;
   
   SPOOL OFF

   REPLACE overwrites an existing file named output.log. Use a different filename if you need to preserve an existing file.

3. Open output.log in a text editor and check that it contains the query results. Because the filename has no directory component, SQL*Plus writes it in its current working directory. The file is created on the computer running SQL*Plus, even when the database is on another server.

   You can specify an absolute path instead. Enclose the filename in double quotes if the path contains spaces.

4. In the response box, provide:

   • The commands you used.
   • The output filename and directory.
   • A brief explanation of which tables the query lists.
   • Whether you opened the file successfully and what you observed.

   If SQL*Plus is unavailable, submit the commands you would use, describe the expected file location and contents, and state that you did not run the commands.

Note: SPOOL is a SQL*Plus command, so it does not need a semicolon. The SELECT statement does need its SQL terminator. The explicit .log extension determines the output filename.