1.写一个emp_out.sql文件,内容如下。set heading off set feedback off set trimspool on set termout off set pages 0 set line 9999 spool emp.csv select 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO' from dual; select EMPNO ||','|| ENAME ||','|| JOB ||','|| MGR ||','|| TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS') ||','|| SAL ||','|| COMM ||','|| DEPTNO from EMP where SAL>0 order by EMPNO; spool off2。执行下面的命令。 $ sqlplus scott/tiger@SID @emp_out.sql3。打开出力的文件。emp.csv:EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 7369,SMITH,CLERK,7902,1980/12/17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981/02/20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981/02/22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981/04/02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981/09/28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981/05/01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981/06/09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987/04/19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981/11/17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981/09/08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987/05/23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981/12/03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981/12/03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982/01/23 00:00:00,1300,,10 这个满足你的要求了吧!
sqlplus中 直接用spool c:\test.txt就可以了. 格式可以自己定义. 或者也可以直接导成sql 语句.这样恢复起来方便. 如 Create table test (a number); Insert into test values(20); Insert into test values(30); Set head off; set echo off set pagesize 50000; set line 1000; spool c:\test.sql select 'Insert into test values(' ||a||');' from test; spool off
$exp scott/tiger tables=(emp,dept) file=/directory/scott.dmp grants=y
SQL> select * from sale; SHOPID GOODID SALENUM
---------- ---------- ----------
101 1 12
101 2 1456
102 3 45
102 4 8908
102 1 111
201 1 1201
201 3 3201
202 2 2202
301 1 13019行が選択されました。SQL> spool off2.
SQL> spool off
SQL> set linesize 1000
SQL> set pagesize 0
SQL> set trimspool on
SQL> set colsep ','
SQL> spool c:\2.txt
SQL> select * from sale;
101, 1, 12
101, 2, 1456
102, 3, 45
102, 4, 8908
102, 1, 111
201, 1, 1201
201, 3, 3201
202, 2, 2202
301, 1, 13019行が選択されました。SQL> spool off后面那种是导成csv格式的。
set feedback off
set trimspool on
set termout off
set pages 0
set line 9999
spool emp.csv
select 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO' from dual;
select
EMPNO ||','||
ENAME ||','||
JOB ||','||
MGR ||','||
TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS') ||','||
SAL ||','||
COMM ||','||
DEPTNO
from EMP
where SAL>0
order by EMPNO;
spool off2。执行下面的命令。
$ sqlplus scott/tiger@SID @emp_out.sql3。打开出力的文件。emp.csv:EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,1980/12/17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981/02/20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981/02/22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981/04/02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981/09/28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/05/01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981/06/09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987/04/19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981/11/17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981/09/08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987/05/23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981/12/03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981/12/03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982/01/23 00:00:00,1300,,10
这个满足你的要求了吧!
直接用spool c:\test.txt就可以了.
格式可以自己定义.
或者也可以直接导成sql 语句.这样恢复起来方便.
如
Create table test (a number);
Insert into test values(20);
Insert into test values(30);
Set head off;
set echo off
set pagesize 50000;
set line 1000;
spool c:\test.sql
select 'Insert into test values(' ||a||');' from test;
spool off