SQL*Plus와 SQLcl

#핵심 요약

SQL*Plus는 SQL과 PL/SQL 문을 제출하는 명령줄 프로그램임
데이터베이스를 설치하면 $ORACLE_HOME/bin에 함께 설치됨

SET, COLUMN, SAVE, SPOOL, DESCRIBE, SHOW USER 같은 명령은 SQL 문이 아니라 SQL*Plus 자체 명령임

조회는 SAVE로 파일에 저장하고 @파일명으로 실행함
sqlplus <접속 정보> @스크립트로 접속과 동시에 실행할 수도 있음

셸 스크립트 안에서 heredoc(<<EOF ... EOF)으로 SQL*Plus를 호출해 cron 같은 스케줄러로 배치 작업을 돌림

#개념과 동작 원리

SQL*Plus로 할 수 있는 일: SQL과 PL/SQL 명령의 입력, 수정, 실행, 저장
조회 결과의 서식 지정과 계산, 출력
테이블 컬럼 정의 조회
데이터베이스 시작과 종료 같은 관리 작업

운영 서버에는 GUI가 없는 경우가 대부분이라 SQL*Plus가 유일한 접속 수단이 되는 때가 있음

실습의 보안 관점 실습: 일반 사용자가 SQL Developer 같은 GUI 도구로 접속하면 V$SESSION의 머신 이름에 사용자 PC 이름이 남아 누가 어디서 접속했는지 알 수 있음
서버에서 SQLPlus로 접속한 SYS는 머신 이름이 서버 호스트명이라 누구인지 알 수 없음
실습에서는 이를 현장에서 일반 사용자의 SQLPlus 접속을 막는 이유로 들었음

DESCRIBE(DESC)는 컬럼 이름, NULL 허용 여부, 데이터 형식만 보여 주며 제약 조건은 보여 주지 않음
테이블뿐 아니라 뷰에도 쓸 수 있고 SQL Developer와 SQLcl도 해석함

SQLcl은 실습에서 sql 명령으로 실행했음
SET SQLFORMAT ANSICONSOLE, LOCKS 같은 SQLcl 전용 명령을 썼음

#필수 명령어 또는 SQL

#접속 별칭

alias sys='sqlplus / as sysdba'
alias app='sqlplus <USERNAME>/<PASSWORD>@<HOST>:<PORT>/<SERVICE_NAME>'

결과 해석:

실습에서는 긴 접속 문자열 대신 별칭을 등록해 썼음
별칭에 비밀번호를 넣으면 셸 설정 파일에 비밀번호가 평문으로 남음(확인 불가)

#스크립트 저장과 실행

SELECT status FROM v$instance;
SAVE i.sql REPLACE
@i

결과 해석:

실습 기록: SAVE로 i.sql이 기록되고, @i로 실행하면 STATUS가 OPEN으로 나왔음
확장자 .sql은 생략할 수 있음
ed 파일명으로 편집기를 열어 스크립트를 만들 수도 있음

#접속과 동시에 스크립트 실행

sqlplus / as sysdba @i

결과 해석:

실습 OCP 해설: 앞의 @는 접속 식별자 구분자이고 뒤의 @는 스크립트 실행 연산자임
스크립트는 현재 디렉터리(와 SQLPATH)에서 찾으므로 전체 경로가 필요 없음

#셸 스크립트에서 호출

#!/bin/bash
export ORACLE_HOME=<ORACLE_HOME>
export ORACLE_SID=<ORACLE_SID>
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin

sqlplus <USERNAME>/<PASSWORD>@<SERVICE_NAME> <<EOF
SELECT COUNT(*) FROM emp;
UPDATE emp SET sal = sal * 1.05 WHERE deptno = 30;
COMMIT;
EXIT
EOF

chmod +x nightly_job.sh
crontab -e
# 0 23 * * * /home/oracle/nightly_job.sh >> /home/oracle/logs/cron.log 2>&1

결과 해석:

실습 기록: 수동 실행에서 COUNT(*) 14, "6 행이 업데이트되었습니다", 커밋 완료가 나왔음
cron 등록과 crond 서비스 실행 상태도 확인했음

비밀번호를 스크립트에 평문으로 두는 문제는 수업 자료에 언급이 없음

#프롬프트에 사용자와 컨테이너 표시 (확인 문제)

cd $ORACLE_HOME/sqlplus/admin
ls -l glogin.sql

결과 해석:

glogin.sql에 아래 내용을 넣었음

SET TERMOUT OFF
COLUMN cur_user NEW_VALUE p_user NOPRINT
COLUMN cur_con  NEW_VALUE p_con  NOPRINT
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') cur_user,
       SYS_CONTEXT('USERENV', 'CON_NAME')     cur_con
  FROM dual;
SET SQLPROMPT "&&p_user@&&p_con> "
SET TERMOUT ON

결과 해석:

glogin.sql은 SQL*Plus가 시작할 때마다 실행하는 사이트 공통 프로필로 보이나, 설명은 기재 없음
접속 직후에만 실행되므로 세션 안에서 ALTER SESSION SET CONTAINER로 바꾸면 프롬프트가 따라 바뀌는지는 기재 없음

#사용 조건과 주의사항

실습의 두 세션 락 대기 재현 실습(V$SESSION의 BLOCKING_SESSION, EVENT, SQLcl LOCKS, ALTER SYSTEM KILL SESSION)은 동시성과 잠금, 세션 진단 글 범위로 이관 대기에 기록했음

실습의 oracle 사용자 sudo 설정 실습은 Linux 분야 범위라 여기서 다루지 않음