How can get my oracle sqlplus querys into .sql file

Viewed 55

I run queries in SQLPlus. Now I want to get all queries into .sql file. how can I do it? please help me.

1 Answers

I don't think that you save all of them, but - you can one by one. Use save command.

SQL> select empno, ename, job sal
  2  from emp
  3  where deptno = 10;

     EMPNO ENAME      SAL
---------- ---------- ---------
      7782 CLARK      MANAGER
      7839 KING       PRESIDENT
      7934 MILLER     CLERK

SQL> save q1.sql replace
Wrote file q1.sql

Only query gets saved, not its result:

SQL> $type q1.sql
select empno, ename, job sal
from emp
where deptno = 10
/

SQL>

As you already closed SQL*Plus, check v$sql. In order to be able to use it, sys should grant you (i.e. the user which will query it) privilege to do so:

SQL> connect sys/pwd as sysdba
Connected.
SQL> grant select on v_$sql to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

You'd then spool result into a file:

SQL> spool q.sql
SQL> select sql_text
  2  from v$sql
  3  where to_date(first_load_time, 'yyyy-mm-dd hh24:mi:ss') >
  4        to_date('30.05.2020 20:50', 'dd.mm.yyyy hh24:mi')
  5    and parsing_schema_name = 'SCOTT';

SQL_TEXT
--------------------------------------------------------------------------------
select empno, ename, job sal from emp where deptno = 10

SQL> spool off
Related