| Spool Command [message #634781] |
Mon, 16 March 2015 01:31  |
 |
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  |
 |
Littlefoot
Messages: 21823
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 |
Lalit Kumar B
Messages: 3174
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 #634786 is a reply to message #634785] |
Mon, 16 March 2015 02:54 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
