다른 명령
SQL PLUS 사용법(Oracle)
사용법
SQL*PLUS 접속
$ sqlplus / as sysdba // 로컬에서 SYS 계정으로 접속 $ sqlplus scott/oracle // 로컬에서 일반계정 SCOTT 유저로 접속 $ sqlplus system/oracle // 로컬에서 SYSTEM 유저로 접속 $ sqlplus system/oracle@massdb // 클라이언트나 로컬에서 리스너를 이용하여 SYSTEM 유저로 접속
HELP 사용법
SYS@testdb> help start // start 관련 명령어를 출력 SYS@testdb> help shutdown // shutdown 관련 명령어를 출력 SYS@testdb> help a // a와 연관된 명령어 모두 출력
TABLE 구조 확인
SYS@testdb> desc tablename // tablename의 구조를 보여 줍니다.
Unix/Linux 쉘 명령어를 수행
SYS@testdb> ! ls -al // 현재 유닉스/리눅스의 목록을 보여줍니다. SYS@testdb> host ls -al // 위와 동일 함
이전 SQL 재수행
SYS@testdb> / // 메모리로 불러온 SQL 문을 실행 하게 됩니다.(이전) SYS@testdb> L // 메모리로 불러온 SQL 문을 보여 줍니다.(이전)
SQL 편집
SYS@testdb> ed[IT] // 기본적으로 1번 이상의 SQL을 수행해야 합니다.(버퍼관련..) SYS@testdb> ed[IT] file // 편집 후 저장하게 되면 file.sql로 저장 됩니다.
파일로 저장
SYS@testdb> save filename // filename.sql 파일을 현재 디렉토리에 저장 SYS@testdb> save /doc/filename // /doc/filename.sql 디렉토리에 저장(절대경로)
SQL 파일로 저장(spool)
SYS@testdb> spool filename.txt // filename.txt 현재 시점 부터 화면 내용 저장 SYS@testdb> spool off // filename.txt 저장 해제
SQL 스크립트 수행
SYS@testdb> @script // 현재 디렉토리의 script.sql을 수행 합니다.
명령어를 화면에 Display
SYS@testdb> set echo [On/Off] // on, off 따라 다릅니다.
SQL 수행 시간 켜기
SYS@testdb> set timing [on/off] // sql문장이 처리되는 시간을 표시 기본값은 off
컬럼명 On/Off
SYS@testdb> set heading [on / off] // 컬럼 명 설정 또는 해제
버퍼에서 사용 하는 명령어
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 편집" 내용을 참조)
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 : 지정된 컬럼설정을 삭제한다.
그 외 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 // 환경이 어떻게 설정 되었는가 확인
사용팁
바인드 변수 사용 (12c 부터 가능)
SQL> VAR v1 NUMBER = 1 SQL> SELECT :v1 FROM DUAL;
SQL문 수행 이력 설정(12c 이상)
set hist on
SQL> hist SQL> hist <이력번호> run
SQLPLUS 상에서는 Auto Commit이 안된다.
그러므로 중간 중간 " COMMIT "를 수행한다.
한 화면씩 보여주는 기능
> set pause on; (설정) set pause off; (해제)
※ 설정을 하고나서 SQL 명령을 수행하면 커서가 그 다음 LINE에서 대기하고 있으므로 [Enter] Key를 친다. 그 다음부터는 Enter키로 한화면씩 보면 됨
Unix Shell 명령어를 사용하기 위해서는 ?
> ! 또는 host
바로전에 실행한 SQL문을 FILE로 저장하려면 ?
> save
파일의 내용을 메모리로 불러오려면 ?
> get
메모리로 불러온 SQL문이나 메모리에 있는 명령 실행
> /
바로 전에 수행한 명령어 편집
> ed
바로 전에 수행한 명령어
> l
SQL문이 있는 FIle 바로 실행
> @ 또는 Start
Columns 명을 나타내지 않는다.
> set heading off / on
한번에 DATA를 가져오는 단위
set arraysize line_number(숫자);
sql문 수행 소요되는 시간
> set timing on / off
sqlplus 프로파일에 추가
$ORACLE_HOME/sqlplus/admin/glogin.sql 에 초기값 세팅 함.
SET sqlprompt "_USER'@'_CONNECT_IDENTIFIER> " // 프롬프트 변경
sqlplus 실행후 적용 하기
SET sqlprompt "_USER'@'_CONNECT_IDENTIFIER SQL> "
& 문자를 입력대기 문자로 인식 하지 않도록 ?
SET DEFINE OFF INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES ('Finance & Treasury');
콘솔에서 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
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
쉘 에서 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"
SYS 로그인 실패
토드/오렌지 툴 접속 이 안될경우 ORA-01017
$ORACLE_HOME/dbs/orapwSID 파일(sys 암호를 저장하는) 생성 해야함
orapwd file=$ORACLE_HOME/dbs/orapwsid1 password="SYS암호문자" force=y