I run queries in SQLPlus. Now I want to get all queries into .sql file. how can I do it? please help me.
Add a comment
|
1 Answer
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
3 Comments
Madhushani Hewagama
i already execute all the command and I closed the sqlplus. but now I need to get all the previous queries. is there any way to do that
Littlefoot
Yes; query v$sql. I added an example; have a look, please.
@MadhushaniHewagama There is an option
set history on but it's cleared as soon as you exit Sqlplus other option is to download sqlcli or GUI tool SqlDeveloper