OS환경 : Oracle Linux6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : sqlplus spool 기능 사용 시 명령어 안나오게 하는방법 sqlplus 명령어
sqlplus에서 spool 기능을 이용해 쿼리를 실행할 때 해당 쿼리도 같이 spool 한 파일에 나옴
기존 방법으로 sqlplus 접속 후 spool 후 쿼리 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
$ sqlplus scoot/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 11 16:46:43 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options
SQL> spool imsi1.txt
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
SQL> spool off
SQL> exit
|
spool 된 imsi1.txt 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ cat imsi1.txt
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
SQL> spool off
|
-s 옵션 사용해서 sqlplus 접속 후 spool 후 쿼리 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
$ sqlplus -s scott/tiger
spool imsi2.txt
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
spool off
|
spool 된 imsi2.txt 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
|
select * from emp; 구문이 나오지 않음
컬럼까지 나오지 않게 설정(set heading off)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
$ sqlplus -s scott/tiger
set heading off
spool imsi3.txt
select * from emp;
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
|
spool 된 imsi3.txt 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ cat imsi3.txt
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
|
위에 나오던 컬럼들이 나오지 않음
14 rows seleted. 까지 삭제 하기(set feedback off)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$ sqlplus -s scott/tiger
set heading off
set feedback off
spool imsi4.txt
select * from emp;
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
|
spool 된 imsi4.txt 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ cat imsi4.txt
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
|
14 rows seleted. 결과 건수가 나오지 않음
참조 :
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 drop database 데이터베이스 삭제 (0) | 2019.02.15 |
|---|---|
| oracle 9i client 12c 접속 12c client 9i 접속 시도 (0) | 2019.02.13 |
| 로그마이너 Supplemental logging 기능 (0) | 2019.02.11 |
| date 타입과 timestamp 타입 조회 (0) | 2019.01.28 |
| Automatic Undo Retention 자동 언두 리텐션 기능 (0) | 2019.01.25 |