Home » SQL & PL/SQL » Client Tools » Spool Command (Oracle 10g,windows 7)
Spool Command [message #634781] Mon, 16 March 2015 01:31 Go to next message
hcdba
Messages: 34
Registered: March 2015
Member
i am very new to dba field.
there is more than one query which provide database size,Largest Table,Largest Index,Asm Size etc.
and i want this all query run at one time and result store in any file format

i found spool option for this
but when i am using this option my query is also stored in file but i just want to store ouput only.
is there any other command is there?

please guide me
Re: Spool Command [message #634782 is a reply to message #634781] Mon, 16 March 2015 01:58 Go to previous messageGo to next message
Littlefoot
Messages: 21325
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you put everything you do into a SQL script and then run that script from the SQL*Plus command prompt, there won't be any query in the output file.

P1.SQL (which simulates your query):
spool p1.txt
select * from dept;
select ename, job, sal from emp where rownum < 5;
spool off;


SQL*Plus session:
M:\>sqlplus scott/tiger@ora10

SQL*Plus: Release 11.2.0.1.0 Production on Pon O×u 16 07:56:23 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @p1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK           1600
ALLEN      SALESMAN        2400
WARD       SALESMAN        2050
JONES      MANAGER         3775

SQL>


Output (P1.TXT):
    DEPTNO DNAME          LOC                                                   
---------- -------------- -------------                                         
        10 ACCOUNTING     NEW YORK                                              
        20 RESEARCH       DALLAS                                                
        30 SALES          CHICAGO                                               
        40 OPERATIONS     BOSTON                                                


ENAME      JOB              SAL                                                 
---------- --------- ----------                                                 
SMITH      CLERK           1600                                                 
ALLEN      SALESMAN        2400                                                 
WARD       SALESMAN        2050                                                 
JONES      MANAGER         3775                                                 



[Updated on: Mon, 16 March 2015 01:58]

Report message to a moderator

Re: Spool Command [message #634783 is a reply to message #634781] Mon, 16 March 2015 02:03 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
Here is an example:

Contents of my sql file -

SQL> host type d:\emp.sql
set feedback off
set trimspool on
set heading off
set echo off
set pagesize 0
spool d:\emp.log
select empno, ename from emp;
spool off


On execution of the script -

SQL> @d:\emp.sql
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
SQL>


Edit : Oops, didn't see LF's reply.

[Updated on: Mon, 16 March 2015 02:05]

Report message to a moderator

Re: Spool Command [message #634785 is a reply to message #634782] Mon, 16 March 2015 02:38 Go to previous messageGo to next message
hcdba
Messages: 34
Registered: March 2015
Member
thanx a lot....done
Re: Spool Command [message #634786 is a reply to message #634785] Mon, 16 March 2015 02:54 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The key word here is "echo".
You must be sure to "set echo off" to prevent from having the statement in your spool file.
"set echo on" adds the statement in the spool file.
This works for statements in a script, statements interactively enter are always in the spool file.

See SET command in SQL*Plus® User's Guide and Reference.

Previous Topic: SQL*Plus - simulate "echo -n" (prompt with no newline)
Next Topic: how to paste huge code in SQLPLUS
Goto Forum:
  


Current Time: Tue Jan 16 14:17:59 CST 2018

Total time taken to generate the page: 0.01800 seconds