Oracle sqlplus 사용하는 방법

Toad, Orange 등 좋은 툴도 있는데 비싸기도 하지만 간단하게 쓰기에는 좀 무거운 감이 있다.
sqlplus를 잘 사용하는 경우 편할 때가 있어 배워보고자 합니다.

실행하기

1. sysdba로 바로 로그인합니다.
[oracle@rh5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 8 22:30:40 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

2. 위상태에서 다른 user로 로그인할 경우
SQL> connect scott/tiger
Connected.

3. 처음부터 scott로 접속할 경우
[OCRL]rh5.cs.com:/oracle/app/oracle/product/10g> sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 8 22:31:40 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: scott
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

4. sqlplus userid 또는 sqlplus userid/passwd 와 같이 해도 되는데 보안상 위 방법을 쓰는게 좋다고 합니다.

도움말 보기 : help [명령어]

SQL> help ed

 EDIT
 ----

 Invokes an operating system text editor on the contents of the
 specified file or on the contents of the SQL buffer. The buffer
 has no command history list and does not record SQL*Plus commands.

 ED[IT] [file_name[.ext]]

 Not available in iSQL*Plus

환경설정 : set [키워드] 값

select한 값이 보기좋게 나오게 하기 위해선
set linesize 150
set pagesize 50
정도로 값을 늘려주어야 한다.
set pause on은 중간에 멈추게 한다.

linesize (n) : 한줄에 표시될 텍스트숫자 기본값80

pages (n) 또는 pagesize (n) : 한페이지당 표시되는 라인수 기본값 24

colsep (text) : 칼럼이 표시될때 칼럼의 구별문자 기본값 공백

feedback (off | on) : 선택된 행이 몇 행인지 표시함 기본값은 6행이상일때 on
             예) set feedback 3 : 3행이상일때 on

heading (off | on) : 컬럼에 대한 heading을 표시함 기본값은 on

paues (on | off | text) : 엔터키를 누를때마다 화면이 지나감 기본값은 off

timing (on | off) : sql문장이 처리되는 시간을 표시 기본값은 off

verify (on | off) : & 변수로 값을 받을 경우 화면에 확인하기 위해 old,new를 표시할 것인지 기본값은  on

show all : 환경이 어떻게 설정 되었는가 확인
numformat : 큰 숫자가 표시될때 E+10 와 같은 식으로 나오지 않도록 함.
            예) set numformat 999999999999999999999999999999.9999999999
                 set numformat ""              <- 원래대로 하기

- 매번 환경변수를 변경해 주어야 하면 glogin.sql에 다음을 추가한다.

[OCRL]rh5.cs.com:/oracle/app/oracle/product/10g> cd sqlplus/admin
[OCRL]rh5.cs.com:/oracle/app/oracle/product/10g/sqlplus/admin> vi glogin.sql

SET PAGESIZE 100
SET LINESIZE 300

- 프롬프트 변경
SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
SCOTT@ORCL>

File 관련

- r, ru, run 또는 / : 바로 전에 실행한 sql을 다시 실행한다.
   L 또는 l : 바로 전에 실행한 sql을 본다.

SQL> select * from dept;

    DEPTNO DNAME   LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING   NEW YORK
 20 RESEARCH   DALLAS
 30 SALES   CHICAGO
 40 OPERATIONS   BOSTON

SQL> /

    DEPTNO DNAME   LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING   NEW YORK
 20 RESEARCH   DALLAS
 30 SALES   CHICAGO
 40 OPERATIONS   BOSTON

SQL> l
  1* select * from dept

- get : 파일을 메모리로 불러온다.
   test.sql에 dept를 조회하는 sql문이 있을때...

SQL> get test.sql
  1* select * from dept
SQL> /

    DEPTNO DNAME   LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING   NEW YORK
 20 RESEARCH   DALLAS
 30 SALES   CHICAGO
 40 OPERATIONS   BOSTON

- save : 바로 전에 실행한 sql을 저장한다.
   ! : 쉘로 이동하고 exit하면 다시 sqlplus로 돌아온다. 
   ! 명령어 : unix 명령을 바로 실행할 수 있다.

SQL> select * from salgrade;

     GRADE LOSAL    HISAL
---------- ---------- ----------
  1   700     1200
  2  1201     1400
  3  1401     2000
  4  2001     3000
  5  3001     9999

SQL> save testsal.sql
Created file testsal.sql

SQL> !
[oracle@rh5 ~]$ ls test*.sql
test.sql  testemp.sql  testsal.sql  testspool.sql
[oracle@rh5 ~]$ exit
exit

SQL> !ls test*.sql
test.sql  testemp.sql  testsal.sql  testspool.sql

- @ 또는 start 파일명 : 파일에 있는 sql을 바로 실행

SQL> @test.sql
  2  ;

    DEPTNO DNAME   LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING   NEW YORK
 20 RESEARCH   DALLAS
 30 SALES   CHICAGO
 40 OPERATIONS   BOSTON

- spool 파일명 또는 spool on / spool off : 화면에 출력되는 것을 파일로 저장
  파일명을 지정해 주지 않으면 on.lst 파일을 만든다.

SQL> spool testspool.sql
SQL> @test.sql
  2  ;

    DEPTNO DNAME   LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING   NEW YORK
 20 RESEARCH   DALLAS
 30 SALES   CHICAGO
 40 OPERATIONS   BOSTON

SQL> spool off
SQL> !ls -lsa testspool.sql
4 -rw-r--r-- 1 oracle dba 765 Sep  8 23:22 testspool.sql

- ed : 메모리에 있는 명령어 편집
            vi로 편집하여 저장하면 afiedt.buf 에 저장되고 / 로 실행이 가능
           사용전에 환경변수에 다음이 있어야 한다.
            vi내에서 ; 으로 끝내지 않아야 실행시 에러가 나지 않는다.

[OCRL]rh5.cs.com:/oracle/app/oracle/product/10g> vi .bash_profile
export EDITOR=vi
export FCEDIT=vi

기타 등등

- Auto Commit이 없기 때문에 commit을 해주어야 한다.

- unix 명령어 사용하기 : ! 를 앞에 붙여 사용한다.
SQL> !pwd
/oracle/app/oracle/product/10g