Oracle 관리 및 SQL Plus 정리

오라클 9i Release 2 기준으로 작성되었음.

◈ SGA 정보보기(정상 설치/실행 상태인가?)

$ sqlplus system/manager
SQL> show sga



◈ DBA로 접속

$ sqlplus "sys/passwd as sysdba"



◈ DB 시작
-- 일반 시작

$ sqlplus "sys/passwd as sysdba"
startup -- DB 인스턴스 시작
startup force -- DB가 실행중일 경우 강제로 종료했다 시작
startup restrict -- 일반 사용자 접근 금지 상태로 DB 시작



-- 단계별 시작

$ sqlplus "sys/passwd as sysdba"
startup nomount; -- NO Mount 단계
alter database mount; -- Mount 단계
alter database open; -- Open 단계



◈ DB 종료

$ sqlplus "sys/passwd as sysdba"
shutdown normal -- 세션,트랜잭션 종료시까지 대기후 종료
shutdown transactional -- 트랜잭션 종료시까지 대기후 종료
shutdown immediate -- 즉시 종료. 모든 DML 롤백
shutdown abort -- 비정상 즉시 종료. 백업과 복구시에만 사용.



◈ 로그인 없이 SQL*Plus 만 실행하기

$ sqlplus "/nolog"



◈ Table 생성 스크립트 뽑아내기

$ exp mlb/mlb file=결과덤프파일.dmp compress=n rows=n tables=테이블명



◈ & 기호 이용하기

1. 첫번째 방법
SELECT 'You '||Chr(38)||' Me' FROM DUAL;

2. 두번째 방법
SET DEFINE OFF
SELECT 'You & me' FROM DUAL;



◈ 편집기 및 SQL*Plus 공통 설정 지정하기
$ORACLE_HOME/sqlplus/admin/glogin.sql에 SQL*Plus 실행시 항상 지정되는 전체 설정을 할 수 있다.
여기서 "ed" 명령으로 실행되는 에디터는 다음 처럼 지정할 수 있다.

DEFINE_EDITOR=gvim.exe



◈ 프로시져 혹은 함수등의 소스 뽑아내기

SET NEWPAGE 0
SET TERMOUT OFF
SET VERIFY OFF
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET LINESIZE 500
SPOOL procedure_name.sql
SELECT TEXT FROM USER_SOURCE WHERE NAME='프로시져혹은함수이름' ORDER BY LINE;
SPOOL OFF


이렇게 저장된 procedure_name.sql 에서 불필요한 부분을 삭제하한다. 각 줄의 공백은 SET TRIMSPOOL ON 에 의해 제거된다.
VIM 공백 제거 : :%s/ *$//g
그리고 이렇게 생성된 소스 맨 앞에 CREATE OR REPLACE PROCEDURE 등을 붙여서 각 프로시져 등의 생성 스크립트로 사용할 수 있게 된다.

◈ 오류 내역출력

SHOW ERRORS


직전에 발생한 오류 내역 출력

◈ 숫자 출력 범위 늘리기

SET NUM 15


숫자를 15자리까지 출력

◈ 쿼리 결과를 셸 스크립트로 자동 저장하기

#!/bin/sh
# 오늘 날짜를 셸 변수로 지정
TODAY=`date +"%Y%m%d"`

sqlplus username/password << ENDSQL
-- 아래는 출력시 불필요한 형식 꾸미기가 안들어가게 한다.
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON

SPOOL 저장할파일_${TODAY}_ORIG
SELECT COL1 || '|' || COL2 || '|' || COL3 -- 실행할 쿼리
FROM MYTABLE;
SPOOL OFF
QUIT
ENDSQL

# 처음과 마지막 쿼리 잔재 제거. 일단 스풀을 실행해보고나서 파일 위,아래에 추가되는
# 불필요한 줄수를 알아본다. (여기서는 위에 2줄과 맨 아래 1줄)
sed -e "1,2d" -e "\$d" 저장할파일_${TODAY}_ORIG > 저장할파일_${TODAY}

# 최종적으로 "저장할파일_${TODAY}"만 남겨둔다
rm 저장할파일_${TODAY}_ORIG


위와 같은 내용으로 셸 스크립트를 만들면 SQL*Plus 로 쿼리 결과를 특정한 형식(여기서는 각 컬럼을 세로 바(|)로 나눈 형식)으로 뽑아낼 수 있다.
그리고 이 경우 셸 환경 변수 값을 TODAY 처럼 SQL 쿼리 문장에 삽입하는 것이 가능하다.

◈ 쿼리 수행 시간 알아내기

SET TIMING ON


이렇게 설정하고 쿼리를 날리면 쿼리 수행시간도 함께 출력된다.

◈ DBMS_OUTPUT.* 으로 출력되는 양 조절

SET SERVEROUTPUT ON SIZE 4000;
Execute DBMS_OUTPUT.PUT_LINE(SYSDATE