"Sqlplus"의 두 판 사이의 차이
DB CAFE
(→SQL문 수행 이력 설정(12c 이상)) |
(→콘솔에서 sql 명령 직접 실행 하기) |
||
228번째 줄: | 228번째 줄: | ||
echo -e "select 1 from dual; \n select 2 from dual;" | sqlplus -s username/password@host:port/service | echo -e "select 1 from dual; \n select 2 from dual;" | sqlplus -s username/password@host:port/service | ||
+ | </source> | ||
+ | === sqlplus select 결과 파일로 저장 === | ||
+ | 1. TXT파일 생성 예제 | ||
+ | - 오라클에서는 SPOOL기능을 이용해서 TXT파일이나 SAM파일에 검색된 테이터 저장 | ||
+ | - 파일에 저장될 SQL문을 스크립트 파일 생성. | ||
+ | 1) 실행시킬 SQL문을 C:\SpoolSel.sql로 저장 | ||
+ | <source lang=sql> | ||
+ | ---- SpoolSel.sql 시작 --- | ||
+ | SELECT empno, sal, ename, to_char(hiredate, 'YYYY-MM-DD') FROM emp; | ||
+ | SELECT deptno, dname FROM dept; | ||
+ | ---- SpoolSel.sql 끝 --- | ||
+ | </source> | ||
+ | |||
+ | 2) sqlplus scott/tiger을 실행 | ||
+ | |||
+ | 3) header가 display되지 않고 데이터만 display | ||
+ | <source lang=sql> | ||
+ | SQL>SET HEADING OFF | ||
+ | </source> | ||
+ | |||
+ | 4) pagesize의 default는 14이며 그대로 하면 14줄마다 1줄씩 공백이 생기므로 그런 현상을 방지하기 위해 크게 지정. | ||
+ | <source lang=sql> | ||
+ | SQL>SET PAGESIZE 1000 | ||
+ | </source> | ||
+ | |||
+ | 5) linesize도 record 길이만큼 지정하여 아래로 구분되지 않도록 지정. | ||
+ | <source lang=sql> | ||
+ | SQL>SET LINESIZE 300 | ||
+ | </source> | ||
+ | |||
+ | 6) 명령이 display되지 않도록 조치 . | ||
+ | <source lang=sql> | ||
+ | SQL>SET ECHO OFF | ||
+ | </source> | ||
+ | |||
+ | 7) 조회 결과가 화면에 나오지 않도록 지정. | ||
+ | <source lang=sql> | ||
+ | SQL>SET TERM OFF | ||
+ | </source> | ||
+ | |||
+ | 8) data가 들어가는 화일 이름을 지정 . | ||
+ | <source lang=sql> | ||
+ | SQL>SPOOL C:\test.txt | ||
+ | </source> | ||
+ | |||
+ | 9) 스크립트 파일을 실행 시킵니다. | ||
+ | <source lang=sql> | ||
+ | SQL>@C:\SpoolSelect.sql | ||
+ | </source> | ||
+ | |||
+ | 10) SPOOL 기능 끄기 | ||
+ | <source lang=sql> | ||
+ | SQL>SPOOL Off | ||
</source> | </source> | ||
2024년 7월 12일 (금) 17:15 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 SQL PLUS 사용법(Oracle)
- 1.1 사용법
- 1.1.1 SQL*PLUS 접속
- 1.1.2 HELP 사용법
- 1.1.3 TABLE 구조 확인
- 1.1.4 Unix/Linux 쉘 명령어를 수행
- 1.1.5 이전 SQL 재수행
- 1.1.6 SQL 편집
- 1.1.7 파일로 저장
- 1.1.8 SQL 파일로 저장(spool)
- 1.1.9 SQL 스크립트 수행
- 1.1.10 명령어를 화면에 Display
- 1.1.11 SQL 수행 시간 켜기
- 1.1.12 컬럼명 On/Off
- 1.1.13 버퍼에서 사용 하는 명령어
- 1.1.14 LINESIZE / PAGESIZE / COLUMN
- 1.1.15 그 외 SET 환경 설정
- 1.2 사용팁
- 1.2.1 바인드 변수 사용 (12c 부터 가능)
- 1.2.2 SQL문 수행 이력 설정(12c 이상)
- 1.2.3 SQLPLUS 상에서는 Auto Commit이 안된다.
- 1.2.4 한 화면씩 보여주는 기능
- 1.2.5 Unix Shell 명령어를 사용하기 위해서는 ?
- 1.2.6 바로전에 실행한 SQL문을 FILE로 저장하려면 ?
- 1.2.7 파일의 내용을 메모리로 불러오려면 ?
- 1.2.8 메모리로 불러온 SQL문이나 메모리에 있는 명령 실행
- 1.2.9 바로 전에 수행한 명령어 편집
- 1.2.10 바로 전에 수행한 명령어
- 1.2.11 SQL문이 있는 FIle 바로 실행
- 1.2.12 Columns 명을 나타내지 않는다.
- 1.2.13 한번에 DATA를 가져오는 단위
- 1.2.14 sql문 수행 소요되는 시간
- 1.2.15 sqlplus 프로파일에 추가
- 1.2.16 sqlplus 실행후 적용 하기
- 1.2.17 & 문자를 입력대기 문자로 인식 하지 않도록 ?
- 1.2.18 콘솔에서 sql 명령 직접 실행 하기
- 1.2.19 sqlplus select 결과 파일로 저장
- 1.2.20 쉘 에서 sqlplus 실행하기
- 1.3 SYS 로그인 실패
- 1.1 사용법
1 SQL PLUS 사용법(Oracle)[편집]
1.1 사용법[편집]
1.1.1 SQL*PLUS 접속[편집]
$ sqlplus / as sysdba // 로컬에서 SYS 계정으로 접속
$ sqlplus scott/oracle // 로컬에서 일반계정 SCOTT 유저로 접속
$ sqlplus system/oracle // 로컬에서 SYSTEM 유저로 접속
$ sqlplus system/oracle@massdb // 클라이언트나 로컬에서 리스너를 이용하여 SYSTEM 유저로 접속
1.1.2 HELP 사용법[편집]
SYS@testdb> help start // start 관련 명령어를 출력
SYS@testdb> help shutdown // shutdown 관련 명령어를 출력
SYS@testdb> help a // a와 연관된 명령어 모두 출력
1.1.3 TABLE 구조 확인[편집]
SYS@testdb> desc tablename // tablename의 구조를 보여 줍니다.
1.1.4 Unix/Linux 쉘 명령어를 수행[편집]
SYS@testdb> ! ls -al // 현재 유닉스/리눅스의 목록을 보여줍니다.
SYS@testdb> host ls -al // 위와 동일 함
1.1.5 이전 SQL 재수행[편집]
SYS@testdb> / // 메모리로 불러온 SQL 문을 실행 하게 됩니다.(이전)
SYS@testdb> L // 메모리로 불러온 SQL 문을 보여 줍니다.(이전)
1.1.6 SQL 편집[편집]
SYS@testdb> ed[IT] // 기본적으로 1번 이상의 SQL을 수행해야 합니다.(버퍼관련..)
SYS@testdb> ed[IT] file // 편집 후 저장하게 되면 file.sql로 저장 됩니다.
1.1.7 파일로 저장[편집]
SYS@testdb> save filename // filename.sql 파일을 현재 디렉토리에 저장
SYS@testdb> save /doc/filename // /doc/filename.sql 디렉토리에 저장(절대경로)
1.1.8 SQL 파일로 저장(spool)[편집]
SYS@testdb> spool filename.txt // filename.txt 현재 시점 부터 화면 내용 저장
SYS@testdb> spool off // filename.txt 저장 해제
1.1.9 SQL 스크립트 수행[편집]
SYS@testdb> @script // 현재 디렉토리의 script.sql을 수행 합니다.
1.1.10 명령어를 화면에 Display[편집]
SYS@testdb> set echo [On/Off] // on, off 따라 다릅니다.
1.1.11 SQL 수행 시간 켜기[편집]
SYS@testdb> set timing [on/off] // sql문장이 처리되는 시간을 표시 기본값은 off
1.1.12 컬럼명 On/Off[편집]
SYS@testdb> set heading [on / off] // 컬럼 명 설정 또는 해제
1.1.13 버퍼에서 사용 하는 명령어[편집]
SYS@testdb> A // 라인끝에 텍스트를 추가
SYS@testdb> C/old/new // old 를 new로 바꿈
SYS@testdb> I text // 다음 line 에 text를 추가
SYS@testdb> L // 전체 문자을 화면에 보여 ("이전 SQL 재 수행" 내용을 참조)
SYS@testdb> n text // n라인 전체를 text로 바꿈
SYS@testdb> R // buffer에 있는 문장 실행
SYS@testdb> edit // buffer에 있는 문장을 file로 부름 ("SQL 편집" 내용을 참조)
1.1.14 LINESIZE / PAGESIZE / COLUMN[편집]
set linesize 100 : 행의 사이즈
set pagesize 10 : 한 페이지에 출력될 행의 갯수
col(umn) 컬럼명 format a10 : 컬럼 사이즈
desc 테이블 : 테이블 컬럼값 확인
- column을 col로 바꾸어도 가능
col A for a10 heading "B" : A 라는 컬럼의 사이즈를 a10, 이름을 B 로 바꾼다.
col A heading "B" : A 라는 컬럼을 B 로 바꾼다.
col A format 0,000,000 : 포맷변경 ex) 0,035,823
col A format 9,999,999 : 포맷변경 ex) 90,000
col [name] : 컬럼설정을 모두 출력하거나 지정된 컬럼설정을 출력한다.
col [name] clear : 지정된 컬럼설정을 삭제한다.
1.1.15 그 외 SET 환경 설정[편집]
SYS@testdb> colsep (text) // 칼럼이 표시될때 칼럼의 구별문자 기본값 공백
SYS@testdb> feedback (off | on) // 선택된 행이 몇 행인지 표시함 기본값은 6행이상일때 on
SYS@testdb> linesize (n) // 한줄에 표시될 텍스트숫자 기본값80
SYS@testdb> pages (n) // 한페이지당 표시되는 라인수 기본값 24
SYS@testdb> paues (on | off | text) // 엔터키를 누를때마다 화면이 지나감 기본값은 off
SYS@testdb> verify (on | off) // & 변수로 값을 받을 경우 화면에 확인하기 위해 old,new를 표시할 것인지 기본값은 on
SYS@testdb> show all // 환경이 어떻게 설정 되었는가 확인
1.2 사용팁[편집]
1.2.1 바인드 변수 사용 (12c 부터 가능)[편집]
SQL> VAR v1 NUMBER = 1
SQL> SELECT :v1 FROM DUAL;
1.2.3 SQLPLUS 상에서는 Auto Commit이 안된다.[편집]
그러므로 중간 중간 " COMMIT "를 수행한다.
1.2.4 한 화면씩 보여주는 기능[편집]
> set pause on; (설정)
set pause off; (해제)
※ 설정을 하고나서 SQL 명령을 수행하면 커서가 그 다음 LINE에서 대기하고 있으므로 [Enter] Key를 친다. 그 다음부터는 Enter키로 한화면씩 보면 됨
1.2.5 Unix Shell 명령어를 사용하기 위해서는 ?[편집]
> ! 를 붙여 사용한다.
or
> host
1.2.6 바로전에 실행한 SQL문을 FILE로 저장하려면 ?[편집]
> save
1.2.7 파일의 내용을 메모리로 불러오려면 ?[편집]
> get 를 붙여 사용한다.
1.2.8 메모리로 불러온 SQL문이나 메모리에 있는 명령 실행[편집]
> / 를 붙여 사용한다.
1.2.9 바로 전에 수행한 명령어 편집[편집]
> ed 를 붙여 사용한다.
1.2.10 바로 전에 수행한 명령어[편집]
> l 를 붙여 사용한다.
1.2.11 SQL문이 있는 FIle 바로 실행[편집]
> @ 또는 Start 를 붙여 사용한다.
1.2.12 Columns 명을 나타내지 않는다.[편집]
> set heading off / on
1.2.13 한번에 DATA를 가져오는 단위[편집]
set arraysize line_number(숫자);
1.2.14 sql문 수행 소요되는 시간[편집]
> set timing on / off
1.2.15 sqlplus 프로파일에 추가[편집]
$ORACLE_HOME/sqlplus/admin/glogin.sql 에 초기값 세팅 함.
SET sqlprompt "_USER'@'_CONNECT_IDENTIFIER> " // 프롬프트 변경
1.2.16 sqlplus 실행후 적용 하기[편집]
SET sqlprompt "_USER'@'_CONNECT_IDENTIFIER SQL> "
1.2.17 & 문자를 입력대기 문자로 인식 하지 않도록 ?[편집]
SET DEFINE OFF
INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES ('Finance & Treasury');
1.2.18 콘솔에서 sql 명령 직접 실행 하기[편집]
--1
sqlplus -s user/pass @myFile.sql;
--2
sqlplus -s /nolog <<EOF
connect user/pass
select blah;
quit
EOF
--3
echo "select 1 from dual;" | sqlplus -s username/password@host:1521/service
--4
sqlplus -s username/password@host:port/service <<< "select 1 from dual;"
--5
echo -e "select 1 from dual; \n select 2 from dual;" | sqlplus -s username/password@host:port/service
1.2.19 sqlplus select 결과 파일로 저장[편집]
1. TXT파일 생성 예제 - 오라클에서는 SPOOL기능을 이용해서 TXT파일이나 SAM파일에 검색된 테이터 저장 - 파일에 저장될 SQL문을 스크립트 파일 생성.
1) 실행시킬 SQL문을 C:\SpoolSel.sql로 저장
---- SpoolSel.sql 시작 ---
SELECT empno, sal, ename, to_char(hiredate, 'YYYY-MM-DD') FROM emp;
SELECT deptno, dname FROM dept;
---- SpoolSel.sql 끝 ---
2) sqlplus scott/tiger을 실행
3) header가 display되지 않고 데이터만 display
SQL>SET HEADING OFF
4) pagesize의 default는 14이며 그대로 하면 14줄마다 1줄씩 공백이 생기므로 그런 현상을 방지하기 위해 크게 지정.
SQL>SET PAGESIZE 1000
5) linesize도 record 길이만큼 지정하여 아래로 구분되지 않도록 지정.
SQL>SET LINESIZE 300
6) 명령이 display되지 않도록 조치 .
SQL>SET ECHO OFF
7) 조회 결과가 화면에 나오지 않도록 지정.
SQL>SET TERM OFF
8) data가 들어가는 화일 이름을 지정 .
SQL>SPOOL C:\test.txt
9) 스크립트 파일을 실행 시킵니다.
SQL>@C:\SpoolSelect.sql
10) SPOOL 기능 끄기
SQL>SPOOL Off
1.2.20 쉘 에서 sqlplus 실행하기[편집]
#!/bin/bash
acreds="user_a/supergreatpassword"
bcreds="user_b/anothergreatpassword"
hoststring='fancyoraclehoststring'
runsql () {
# param 1 is $1
sqlplus -S /nolog << EOF
CONNECT $1@$hoststring;
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
$2
exit;
EOF
}
echo "TS::$(date): Starting SCHEM_A.PROC_YOU_NEED()..."
runsql "$acreds" "execute SCHEM_A.PROC_YOU_NEED();"
echo "TS::$(date): Starting superusefuljob..."
/var/scripts/superusefuljob.sh
echo "TS::$(date): Starting SCHEM_B.SECRET_B_PROC()..."
runsql "$bcreds" "execute SCHEM_B.SECRET_B_PROC();"
echo "TS::$(date): DONE"