OS환경 : Oracle Linux7.3(64bit)
DB 환경 : Oracle Database 12.2.0.1
방법 : select 문을 조회하고 tmp에 담은 다음 결과 값만 보고 싶은 경우
sqlplus 접속 후 spool 로 로깅
SYS@ORCL> spool tmpall.tmp
쿼리실행
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 | SYS@ORCL>select * from dba_data_files where 1=1; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_ LOST_WR ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- ------- ------- /oracle/app/oracle/oradata/ORCL/system01.dbf 1 SYSTEM 1352663040 165120 AVAILABLE 1 YES 3.4360E+10 4194302 1280 1351614464 164992 SYSTEM OFF /oracle/app/oracle/oradata/ORCL/sysaux01.dbf 3 SYSAUX 1499463680 183040 AVAILABLE 3 YES 3.4360E+10 4194302 1280 1498415104 182912 ONLINE OFF /oracle/app/oracle/oradata/ORCL/jshts01.dbf 5 jshTS 7516192768 917504 AVAILABLE 5 NO 0 0 0 7515144192 917376 ONLINE OFF /oracle/app/oracle/oradata/ORCL/users01.dbf 7 USERS 671088640 81920 AVAILABLE 7 YES 3.4360E+10 4194302 160 670040064 81792 ONLINE OFF /oracle/app/oracle/oradata/ORCL/undotbs01.dbf 4 UNDOTBS1 718274560 87680 AVAILABLE 4 YES 3.4360E+10 4194302 640 717225984 87552 ONLINE OFF 5 rows selected. Elapsed: 00:00:00.12 |
spool off후 sqlplus 종료
1 2 | SYS@ORCL>spool off SYS@ORCL>exit |
spool로 나온 파일 확인
select 구문과 spool off 구문 모두 나온다.
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 | [oracle@oracle12 jsh]$ cat tmpall.tmp SYS@ORCL> select * from dba_data_files 2 where 1=1; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_ LOST_WR ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- ------- ------- /oracle/app/oracle/oradata/ORCL/system01.dbf 1 SYSTEM 1352663040 165120 AVAILABLE 1 YES 3.4360E+10 4194302 1280 1351614464 164992 SYSTEM OFF /oracle/app/oracle/oradata/ORCL/sysaux01.dbf 3 SYSAUX 1499463680 183040 AVAILABLE 3 YES 3.4360E+10 4194302 1280 1498415104 182912 ONLINE OFF /oracle/app/oracle/oradata/ORCL/jshts01.dbf 5 jshTS 7516192768 917504 AVAILABLE 5 NO 0 0 0 7515144192 917376 ONLINE OFF /oracle/app/oracle/oradata/ORCL/users01.dbf 7 USERS 671088640 81920 AVAILABLE 7 YES 3.4360E+10 4194302 160 670040064 81792 ONLINE OFF /oracle/app/oracle/oradata/ORCL/undotbs01.dbf 4 UNDOTBS1 718274560 87680 AVAILABLE 4 YES 3.4360E+10 4194302 640 717225984 87552 ONLINE OFF 5 rows selected. Elapsed: 00:00:00.12 SYS@ORCL> spool off |
제외시키는 방법1
설명 : sed -n '출력할 행p(8행부터 999행까지 출력)' 파일명
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [oracle@oracle12 jsh]$ sed -n '8,999p' tmpall.tmp /oracle/app/oracle/oradata/ORCL/system01.dbf 1 SYSTEM 1352663040 165120 AVAILABLE 1 YES 3.4360E+10 4194302 1280 1351614464 164992 SYSTEM OFF /oracle/app/oracle/oradata/ORCL/sysaux01.dbf 3 SYSAUX 1499463680 183040 AVAILABLE 3 YES 3.4360E+10 4194302 1280 1498415104 182912 ONLINE OFF /oracle/app/oracle/oradata/ORCL/jshts01.dbf 5 jshTS 7516192768 917504 AVAILABLE 5 NO 0 0 0 7515144192 917376 ONLINE OFF /oracle/app/oracle/oradata/ORCL/users01.dbf 7 USERS 671088640 81920 AVAILABLE 7 YES 3.4360E+10 4194302 160 670040064 81792 ONLINE OFF /oracle/app/oracle/oradata/ORCL/undotbs01.dbf 4 UNDOTBS1 718274560 87680 AVAILABLE 4 YES 3.4360E+10 4194302 640 717225984 87552 ONLINE OFF 5 rows selected. Elapsed: 00:00:00.12 SYS@ORCL> spool off |
이방법은 로우수가 매번 다를경우 999를 줘야해서 맨마지막에 n row selected.와 spool off 글자가 남음
제외시키는 방법2
설명 : sed -e '제외할 행n,m(1행부터 7행까지 제외)' 파일명 | head -n -4(끝에서 4행 제외)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [oracle@oracle12 jsh]$ sed -e '1,7d' tmpall.tmp | head -n -4 /oracle/app/oracle/oradata/ORCL/system01.dbf 1 SYSTEM 1352663040 165120 AVAILABLE 1 YES 3.4360E+10 4194302 1280 1351614464 164992 SYSTEM OFF /oracle/app/oracle/oradata/ORCL/sysaux01.dbf 3 SYSAUX 1499463680 183040 AVAILABLE 3 YES 3.4360E+10 4194302 1280 1498415104 182912 ONLINE OFF /oracle/app/oracle/oradata/ORCL/jshts01.dbf 5 jshTS 7516192768 917504 AVAILABLE 5 NO 0 0 0 7515144192 917376 ONLINE OFF /oracle/app/oracle/oradata/ORCL/users01.dbf 7 USERS 671088640 81920 AVAILABLE 7 YES 3.4360E+10 4194302 160 670040064 81792 ONLINE OFF /oracle/app/oracle/oradata/ORCL/undotbs01.dbf 4 UNDOTBS1 718274560 87680 AVAILABLE 4 YES 3.4360E+10 4194302 640 717225984 87552 ONLINE OFF |
원하는 결과(sql 결과)만 출력된것을 확인할 수 있다.
참조 :
https://jupiny.com/2017/07/09/linux-command-1-grep-less-head-tail/
'Linux, Unix > Tip' 카테고리의 다른 글
| 크론탭 실행시 파일 크기 0 => 답은 절대경로 및 권한 (0) | 2018.11.13 |
|---|---|
| vi 에서 치환 시 개행 문자(new-line character)(엔터) 입력 방법 (0) | 2018.11.09 |
| 리눅스6 새로넣은 디스크 mount 시키기 (0) | 2018.09.07 |
| oel6 oracle 11g raw device 용 vg, lv 생성 방법 (0) | 2018.08.16 |
| linux 자동 반복 작업 걸기 while (0) | 2018.06.12 |