SQL*Plus의 향상된 기능

일곱번째. SQL*Plus의 향상된 기능

Oracle Database 10g의 SQL*Plus 툴에는 프롬프트, 파일 조작 기능 등 몇 가지 눈에 띄는 기능 개선이 추가되었습니다.

DBA가 하루에 가장 많이 사용하는 툴은 무엇일까요 아마 GUI 대신 오래된 작업 방식을 고수하고 있는 본인과 같은 DBA들이라면 SQL*Plus 커맨드 라인 툴을 그 첫 번째로 꼽을 것입니다.

Oracle Database 10g에서 Enterprise Manager 10g의 기능이 한층 강화되었음에도 불구하고, 초심자와 숙련된 DBA 모두에게 있어 SQL*Plus는 앞으로도 오랫동안 유용하게 활용될 것입니다.

이번 연재에서는 SQL*Plus 10.1.0.2에 추가된 유용한 기능을 살펴 보고자 합니다. 이 강좌를 따라 해 보려면 Oracle Database 10g에 포함된 sqlplus를 사용해야 함을 주의하시기 바랍니다 (Oracle9i Database의 sqlplus을 사용해서 10g 데이타베이스에 접속해서는 안됩니다).

프롬프트의 활용

지금 나는 어디에 있는가 그리고 나는 누구인가 지금 형이상학적인 질문을 던지려는 것이 아닙니다. SQL*Plus 환경의 관점에서 사용자의 위치를 묻는 질문입니다. SQL*Plus가 지금까지 천편일률적으로 제공했던 SQL> 프롬프트는 사용자가 누구이고 어디에 연결되었는지에 대한 아무런 정보도 제공하지 않습니다. 이전 릴리즈에서는 프롬프트를 바꾸려면 복잡한 코딩이 필요했습니다. SQL*Plus 10.1.0.2는 아래와 같은 간단한 명령만으로 프롬프트를 바꿀 수 있습니다:

그러면 SQL*Plus 프롬프트는 아래와 같은 형식으로 표시됩니다:

위의 경우는 SYS 사용자가 SYSDBA로서 접속한 상황임을 의미합니다. 위에서 _user와 _privilege라는 두 개의 변수가 활용되는 방법을 참고하십시오. _user는 현재 사용자를 _privilege는 로그인에 사용되는 privilege를 뜻합니다.

이번에는 다른 방법을 써 봅시다. 아래는 프롬프트에 오늘 날짜가 함께 표시되도록 하는 명령입니다:

SQL> set sqlprompt "_user _privilege 'on' _date >"

SYS AS SYSDBA on 06-JAN-04 >

여기에 database connection identifier를 추가할 수도 있습니다. 이 방법은 사용자의 “위치”를 수시로 확인해야 하는 환경에서 대단히 유용합니다.

SQL> set sqlprompt "_user 'on' _date 'at' _connect_identifier 

>"

ANANDA on 06-JAN-04 at SMILEY >

이번에는 오늘 날짜에 시간과 분까지 함께 표시하도록 해 보겠습니다.

ANANDA on 06-JAN-04 at SMILEY > alter session set nls_date_format = 

'mm/dd/yyyy hh24:mi:ss';Session altered.ANANDA on 01/06/2004 13:03:51 at SMILEY >;

이처럼 몇 번의 간단한 키 입력 만으로 매우 많은 정보를 포함하는 SQL 프롬프트가 만들어졌습니다. 이 설정을 glogin.sql 파일에 저장하면 앞으로도 계속 같은 프롬프트를 사용할 수 있습니다.

불필요한 인용부호의 생략

Oracle9i이 internal login을 더 이상 지원하지 않는다고 발표되었을 때, 많은 DBA들은 불만의 함성을 터뜨렸습니다. Internal login이 없다면 커맨드 라인 상에서 SYS 패스워드를 입력할 수 없고 따라서 보안 관리가 어려워진다는 이유였습니다. 오라클은 그 해결책으로 운영체제 프롬프트 상에서 인용부호를 사용하는 방법을 제시했습니다:

DBA들은 이러한 변화에 만족하지 않았지만 참고 받아들일 수 밖에 없었습니다. Oracle Database 10g에서는 이러한 요구사항이 없어졌으며, 아래와 같은 방법으로 인용부호를 사용하지 않고 SYSDBA권한으로 로그인하는 것이 가능합니다.

이러한 변화는 단순히 입력할 문자의 수가 줄었다는 것만을 의미하지 않습니다. Unix와 같은 운영체제에서 escape 문자를 사용할 필요가 없어졌다는 것도 새로운 이점의 하나입니다.

파일 조작 기능의 향상

DBA가 문제 해결 과정에서 임시로 만든 SQL 구문이 있다고 가정해 봅시다. 이 DBA는 만든 구문을 나중에 다시 재사용하기 위해 저장하고 싶어할 수도 있습니다. 이럴 때 어떻게 해야 할까요 아마도 아래와 같은 방법으로 파일에 개별 저장하는 방법을 쓰게 될 것입니다:

select something1 ....

save 1

select something else ....

save 2

select yet another thing ....

save 3

결국 위의 구문을 모두 사용하려면 그에 해당하는 세이브 파일을 모두 불러내어야 합니다. 이 얼마나 번거로운가요! SQL*Plus 10.1.0.2는 여러 구문을 파일에 append하는 형태로 저장하는 기능을 제공합니다. 위의 경우라면 아래와 같은 명령을 사용할 수 있습니다:

select something1 ....

save myscripts

select something else ....

save myscripts append

select yet another thing ....

save myscripts append

이렇게 하면 모든 구문을 myscript.sql 파일에 append 된 형태로 저장할 수 있으므로, 여러 파일에 나누어 저장한 뒤 이를 다시 하나로 연결할 필요가 없게 됩니다.

스풀링(spooling)에서도 append 형태의 저장 방식이 사용됩니다. 이전 릴리즈에서는 SPOOL RESULT.LST 명령으로 result.lst 파일을 생성할 수 있었지만, 기존에 같은 이름의 파일이 존재하는 경우 아무런 경고도 없이 덮어쓰기가 실행된다는 문제가 있었습니다. 이 때문에 실제로 작업을 수행하는 과정에서 중요한 파일이 덮어씌워져 버리는 곤란한 상황이 발생하곤 했습니다. 10g에서는 spool 명령을 수행하면서 append 방식으로 저장하도록 설정할 수 있습니다:

덮어쓰기를 원한다면 위의 append 조건 대신 REPLACE 조건을 삽입하면 됩니다. (REPLACE는 디폴트로 적용됩니다.) 아래 명령을 사용하면 세이브하기 전에 기존 파일이 존재하는지 점검합니다:

spool result.lst create

Use another name or "SPOOL filename[.ext] REPLACE"

This approach will prevent the overwriting of the file result.lst.

Login.sql 관련 문제

login.sql과 glogin.sql이라는 파일을 기억하십니까 SQL*Plus가 실행되면 현재 디렉토리에 있는 login.sql이 자동으로 실행됩니다. 하지만 심각한 기능상의 문제가 존재했습니다. Oracle9i 및 이하 버전의 login.sql 파일에 아래와 같은 내용이 포함되어 있는 경우를 생각해 봅시다:

데이타베이스DB1에 접속하기 위해 SQL*Plus를 실행하면 아래와 같이 프롬프트가 표시됩니다:

이 프롬프트 상에서 데이타베이스 DB2로의 접속을 시도해 봅시다:

DB1> connect scott/tiger@db2

Connected

DB1>

DB2로 접속한 상태임에도 프롬프트가 여전히 DB1으로 표시되고 있습니다. 이유는 간단합니다. login.sql 파일은 데이타베이스 연결 시에 실행되지 않으며 SQL*Plus 시작 시에만 실행되기 때문입니다. Oracle Database 10g에서는 이러한 문제가 해결되었습니다. login.sql은 SQL*Plus가 시작되는 시점뿐 아니라 새로운 연결이 설정되는 경우에도 자동 실행됩니다. 10g 환경에서 DB1에 접속해 있다가 다른 데이타베이스로 연결을 변경하는 경우, 아래와 같이 프롬프트도 같이 변경됩니다.

In Oracle Database 10g, this limitation is removed. The file login.sql is not only executed

at SQL*Plus startup time, but at connect time as well. So in 10g, if you are currently connected

to database DB1 and subsequently change connection, the prompt changes.SCOTT at DB1> connect scott/tiger@db2

SCOTT at DB2> connect john/meow@db3

JOHN at DB3>

변화를 원치 않는다면

어떤 이유로든, SQL*Plus의 개선된 기능을 사용하고 싶지 않은 경우도 있을 수 있습니다. 그런 경우라면, 아래처럼 C 옵션을 적용하면 됩니다:

위와 같이 입력하는 경우 9.2 버전의 SQL*Plus 환경이 실행됩니다.

Use DUAL Freely

아래와 같은 명령을 실제로 사용하는 개발자(또는 DBA)의 수가 얼마나 될 것이라 생각하십니까

아마 거의 모든 이들이 사용하고 있을 것입니다. DUAL은 호출될 때마다 새로운 논리적 I/O(Buffer I/O)를 생성합니다. 이 기능은 매우 유용하게 활용됩니다. DUAL은 := USER와 같은 구문만큼이나 자주 사용되고 있습니다. 하지만 오라클 코드는 DUAL을 특수한 형태의 테이블로서 취급하며, 따라서 일반적인 튜닝 방법은 적용할 수 없다는 문제가 있습니다.

Oracle Database 10g에서라면 이에 관련한 걱정은 할 필요가 없습니다. DUAL이 특수한 테이블이기 때문에, 논리적 I/O를 나타내는 consistent gets의 값도 줄어들며, event 10046 trace에서 확인할 수 있는 것처럼 optimization plan도 다른 형태로 나타납니다.

Oracle9i의 경우

Rows     Execution Plan

-------  ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE

      1   TABLE ACCESS (FULL) OF 'DUAL'

10g의 경우

Rows     Execution Plan

-------  ---------------------------------------------------

      0  SELECT STATEMENT   MODE: ALL_ROWS

      0   FAST DUAL

Oracle 9i에서 사용되는 DUAL의 FULL TABLE SCAN 대신, 10g에서는 FAST DUAL optimization plan을 사용하고 있다는 점을 주목하시기 바랍니다. 이러한 기능 개선을 통해 DUAL 테이블을 자주 사용하는 애플리케이션의 연속적인 읽기 작업 성능이 대폭적으로 향상되었습니다.

그 밖에 유용한 팁

그 밖에 SQL*Plus에 관련한 몇 가지 팁이 이 시리즈 전반에 걸쳐 소개되고 있습니다. 예를 들어, RECYCLEBIN 개념은 제 5주 Flashback Table 관련 연재에서 소개됩니다.

일반에 알려진 것과 달리, COPY 명령은 여전히 사용 가능합니다. 하지만 향후 릴리즈에서는 기능이 삭제될 것이라 합니다. (Oracle9i 시절부터 듣던 얘기가 아닌가요) 이 명령이 적용된 스크립트를 사용하는 중이라면 고민할 필요가 없습니다. 사용 가능할 뿐 아니라 기술지원도 제공됩니다. 더군다나 에러 메시지 리포팅에 관련한 기능 개선도 일부 이루어졌습니다. 테이블이 LONG 칼럼을 포함하는 경우, 해당 테이블에 대한 복제본을 생성하려면 COPY 명령에 의존할 수 밖에 없습니다. CREATE TABLE AS SELECT 명령은 long 데이타 타입의 컬럼을 포함하는 테이블을 지원하지 않습니다.