0

Why can't I spool the explain plan to a txt file?

SPOOL EXPLAIN_PLAN_1.txt

set heading off
set echo off
set pages 999
set long 90000

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical'));

SPOOL OFF;

Error that I see

SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Oracle Version: 11g

2
  • 2
    If you tried again and it still doesn't work, can you check if a file already exists and you it is owned by another user or has other privileges preventing you to overwrite it? Mar 31, 2016 at 15:57
  • It's not the EXPLAIN command that fails, it's the SPOOL command. Something in the command is wrong. Apr 2, 2016 at 14:13

1 Answer 1

3

SP2-0768 Illegal SPOOL command

Cause: An invalid option was used in the SPOOL command.

Action: Check the syntax of the SPOOL command for the correct options.

SP2-0769 Usage: SPOOL { <file> | OFF | OUT }

where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
Cause: Incorrect syntax for the SPOOL command was entered.

Action: Check the syntax of the SPOOL command for the correct usage.

Exact same command that you have shown in the question is working fine for me as shown below.
OS: Oracle Linux 6
Oracle Version: 11.2.0.4.0

[oracle@testsrv Desktop]$ sqlplus / as sysdba

SQL> SPOOL EXPLAIN_PLAN_1.txt
SQL> set heading off
SQL> set echo off
SQL> set pages 999
SQL> set long 90000
SQL> explain plan for select * from hr.employees;

Explained.

SQL> select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical'));  2  

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

SQL> SPOOL OFF;

[oracle@testsrv Desktop]$ cat EXPLAIN_PLAN_1.txt
SQL> 
SQL> explain plan for select * from hr.employees;

Explained.

SQL> select plan_table_output from
  2  table(dbms_xplan.display('plan_table',null,'typical'));

Plan hash value: 1445457117                                                     

------------------------------------------------------------------------------- 
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 | 
------------------------------------------------------------------------------- 

8 rows selected.

SQL> SPOOL OFF;
[oracle@testsrv Desktop]$ 

Not the answer you're looking for? Browse other questions tagged or ask your own question.