정차장님의 DROP DATABASE

Transcription

1 정차장님의 DROP DATABASE 정차장님

2 소개글 정차장님의 DROP DATABASE

3 목차 1 데이터베이스의 SQL 상태를확인하자 6 2 Table Full Scan을수행하는 SQL 추출 11 3 오라클제약조건 12 4 오라클 TIMESTAMP 이용한백업 34 5 HOW TO DISABLE ARCHIVE LOG IN ORACLE? 36 6 아카이브로그모드 (Archive Log Mode) 38 7 ORA-00257: 아카이버오류, 43 8 문자열중에서숫자만골라내서콤마붙이기 45 9 오라클에서 ISNUMERIC 함수구현하기 오라클에서중복데이터제거하기 오라클자바클래스호출 프로시저이력관리 MERGE INTO 오류사항 The Data Dictionary: Make Views Work for You ORA-01039: 사용되지않는오류 EXPLAIN PLAN( 실행계획 ) 이란? [ 오라클 ] 특정사용자에주어진권한보기 오라클특수문자데이터치환하기 데이터중에서최신데이터뽑아오기 업데이트힌트 ( 가변열오류무시 ) 오라클레코드변수사용예 오라클 10g XE...http port 바꾸기 오라클커서 Attribute Values 오라클게시자문제해결 아이바티스부등호처리 86

4 26 오라클더미행만들기 ORACLE ANALYZE 하는법 오라클분석함수 다중의결과값을하나의행으로컴마로분리해출력하는방법 에효... 맨날알면서도당하는거 오라클커서속성값 Handling PL/SQL Errors 일반적인정규표현식 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) 오라클정규표현식 - 1부 dcmctl Fusion Middleware command line tool 오옷.. 몰랐던함수 Explain plan/sql Trace file 보는법 EXPLAIN PALN 오라클 PL/SQL EXCEPTION 유형 EXPLAIN PLAN Oracle10g에서 CONNECT BY 오라클변환형함수 오라클 UPDATE 구문 Sequence 이용 LOB DATA TYPE의이해 오라클함수정리... 아.. 지겨워.. 맨날정리.. 정리 Oracle Clob 처리 오라클날짜다루기 케릭터셋인코딩 183

5 51 SQL PLUS 잡다한기능 MERGE 를이용한 ROW INSERT OR UPDATE MERGE INTO 오라클접속방법 오라클힌트사용법 CONSTRAINT_NAME변경하기.SQL 테이블 _ 컬럼 _ 코멘트붙이기.SQL 테이블 _ 명명규약검사.sql 211

6 데이터베이스의 SQL 상태를확인하자 :14 데이터베이스의 SQL 상태를확인하자 오라클데이터베이스를운영하다보면 SQL은데이터베이스에서관리하기힘들다고느낀다. 그래서애플리케이션에서관리해야한다고많이생각하게된다. 하지만 SQL은오라클인스턴스메모리 (SGA 내의 Shared pool) 나보조테이블스페이스 (SYSAUX) 에저장되고있으며, 딕셔너리뷰를조회해 SQL들에대한통계나성능을어렵지않게추출할수있다. 그렇다면오라클은이 SQL들을어떻게저장하는지알아보자. 1. 사용자는데이터처리를위해 SQL을이용해데이터베이스질의요청 2. 데이터베이스는요청된 SQL에대해하드파싱또는소프트파싱을수행한다. 3. 하드파싱된 SQL의커서정보는공유풀 (Shared pool) 에캐시되며, 동일한 SQL 요청에대해소프트파싱을수행해공유풀을탐색한다. 4. 1번에서 3번이여러번수행되고, 일정한시간이흐르면 MMON 백그라운드프로세스에의해 SYSAUX라는테이블스페이스에저장된다. 데이터베이스의 SQL 상태를확인하자 6

7 위의일련의과정을통해 SQL은오라클데이터베이스에저장되며, 활용할수있게된다. 앞서설명했듯이 SQL 커서정보는메모리와테이블스페이스에저장되는데테이블스페이스에저장된 SQL 커서정보들은데이터베이스가종료돼도유지된다. 하지만메모리에있는커서정보는장시간재호출이없거나데이터베이스가재기동될때사라진다. SQL의저장은메모리에있는온라인정보와테이블스페이스에저장되는오프라인정보로나누어지게되는데, 온라인정보는 V$SQL 등의딕셔너리뷰를조회해정보를추출할수있으며, 오프라인정보는 DBA_HIST_* 뷰를활용해정보추출이가능하다 앞으로보여줄예제들은딕셔너리뷰, 즉메모리내의정보를이용하는 SQL 을예제로한다. 첫번째로알아볼내 용은현재데이터베이스에서 SQL 수행속도의분포가어떻게되는지알아보는 SQL 이다. < 리스트 1> 의예제는 1 초미만, 3 초미만, 10 초미만그리고나머지등으로분류해수량을체크하는구문이다. < 리스트 1> 수행속도별 SQL 수량파악 SELECT CASE WHEN ELAPSED_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS) BETWEEN 0 AND THEN 1 WHEN ELAPSED_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS) BETWEEN AND THEN 3 WHEN ELAPSED_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS) BETWEEN AND THEN 10 ELSE 99 END EXEC_TIME, ROUND(AVG(ELAPSED_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS))/1000/1000,3) AVGTIME,COUNT(*) FROM V$SQL WHERE PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM') GROUP BY CASE WHEN ELAPSED_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS) BETWEEN 0 AND THEN 1 WHEN ELAPSED_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS) BETWEEN AND THEN 3 WHEN ELAPSED_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS) BETWEEN AND THEN 10 ELSE 99 END 데이터베이스의 SQL 상태를확인하자 7

8 V$SQL은메모리를액세스할수있는딕셔너리뷰로서, SQL에관련된정보를추출할수있는뷰이다. 해당 SQL 은파싱유저중 SYS와 SYSTEM을제외한 SQL들을수행속도로분류해결과를표시한다. 여기서 ELAPSED_TIME이수행속도이다. 수행속도는 CPU가수행되는시간과대기시간이더해져만들어지는실제수행속도를의미한다. 테이블을읽기위해 10 만블록을읽는데, 총 10 초가걸렸다. 여기서디스크의 I/O 속도가느려서메모리로올리 는시간이 8 초라면, 실제 CPU 가수행된시간은 2 초밖에되지않는것이다. 두번째로시스템에성능이슈가발생하면보편적으로 Full Scan 이라는용어를많이쓰게된다. Full Scan 은오라클실행계획에서세그먼트 ( 테이블또는인덱스 ) 를읽는방법중하나를지칭하는용어이다. 이 Full Scan 은세그먼트를처음부터끝까지전부읽는방식으로 OLTP 환경에서적절히사용하지못할경우시스템에일정부분악영향을주게된다. < 리스트 2> 의구문을이용해실행된 SQL 중 Full Scan을수행한 SQL에대해정보를추출해보자. < 리스트 2> Table Full Scan 을수행하는 SQL 추출 SELECT /*+ ORDERED USE_NL(A SQ)*/SQL_FULLTEXT, SQ.SQL_ID, PLAN_HASH_VALUE, S.MODULE, TRUNC(CPU_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS)/1000/1000,2) AS "CPU_ONE" TRUNC(ELAPSED_TIME/DECODE(EXECUTIONS,0,1, EXECUTIONS)/1000/1000,2) AS "EPLAP", TRUNC(BUFFER_GETS/DECODE(EXECUTIONS,0,1, EXECUTIONS)/1000/1000,2) AS "BUFFER_GET" FROM (SELECT DISTINCT SQL_ID FROM V$SQL_PLAN WHERE OBJECT_OWNER NOT ('SYS','SYSTEM') AND OPTIONS LIKE '%FULL%')A,V$SQLAREA S WHERE S.SQL_ID=A.SQL_ID; SQL 이처음수행되면옵티마이저는최적의경로를찾기위해파싱 (Parsing) 을수행하게된다. 파싱과정이이 데이터베이스의 SQL 상태를확인하자 8

9 뤄진후에는실행계획이생성되고, 실행계획에서 SQL 이어떻게수행돼야하는지에대한정보가들어가게된다. 실행계획은메모리에저장되며, 실행계획을추출하기위해서 V$SQL_PLAN 딕셔너리뷰를조회하면된다. V$SQL_PLAN 뷰에서 OPTIONS 컬럼은객체를읽는방식을기록한다. 이컬럼을이용해 LIKE 수식어로 Full Scan 을사용한 SQL_ID를추출하고, V$SQL로조인하게된다. 위구문으로추출되는정보는 SQL 구문, 실행계획고유값, 모듈명, CPU 사용시간, 수행시간, 메모리에서발생한 I/O 블록수등이다. 추출된 SQL은적절한튜닝 ( 인덱스생성, SQL 변경, 힌트수정등 ) 으로성능을향상시킬수있다. 마지막으로리터럴 SQL 을추출하는방법이다. 리터럴 SQL 은바인드변수없이사용된 SQL 로 1 회성 SQL 이라 보면된다. 리터럴 SQL 은한번사용된후재사용되지않아메모리공간을낭비하며빈번한하드파싱으로성능 지연의원인이되기도한다. < 리스트 3> 리터널 SQL 추출 SELECT Z.*, (SELECT SQL_FULLTEXT FROM V$SQL WHERE SQL_ID=Z.SQL_ID) SQL_TEXT FROM (SELECT MAX(SQL_ID) SQL_ID, COUNT(*) CNT FROM V$SQL A WHERE FORCE_MATCHING_SIGNATURE>0 AND FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE GROUP BY FORCE_MATCHING_SIGNATURE HAVING COUNT(*) > SQL이 20개이상존재 ORDER BY 2) Z; 비슷한유형의 SQL 이 20 개이상인 SQL 을추출하는구문이다. 이렇게추출된 SQL 은애플리케이션에서바인드 변수처리만으로데이터베이스의 CPU 사용량을줄이고성능을향상시킬수있다. 앞서보듯이손쉽게오라클의 딕셔너리뷰를활용해데이터베이스에서이뤄지는 SQL 의통계및성능분석이가능해진다. 데이터베이스의 SQL 상태를확인하자 9

10 출처 : 2013 년마이크로소프트웨어 12 월호 제공 : DB 포탈사이트 DBguide.net 데이터베이스의 SQL 상태를확인하자 10

11 Table Full Scan 을수행하는 SQL 추출 :13 원본자료 : SELECT /*+ ORDERED USE_NL(A SQ)*/ SQL_FULLTEXT,SQ.SQL_ID,PLAN_HASH_VALUE,SQ.MODULE,TRUNC(CPU_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000/1000,2) AS CPU_ONE,TRUNC(ELAPSED_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000/1000,2) AS EPLAP,TRUNC(BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000/1000,2) AS BUFFER_GET FROM (SELECT DISTINCT SQL_ID FROM V$SQL_PLAN WHERE OBJECT_OWNER NOT IN ('SYS','SYSTEM') AND OPTIONS LIKE '%FULL%' )A,V$SQLAREA SQ WHERE SQ.SQL_ID=A.SQL_ID --AND SQ.MODULE NOT IN ('DBMS_SCHEDULER',); AND SQ.MODULE IN ('JDBC Thin Client') AND SQ.SQL_FULLTEXT LIKE '%PCA%' Table Full Scan 을수행하는 SQL 추출 11

12 오라클제약조건 :03 출처 : Constraints( 제약조건 ) 테이블에부여된제약조건의관계키는 user_constraints 뷰를사용하여확인할수있다. 제약조건은 data integrity( 데이터무결성 ) 을위하여주로테이블에행 (row) 을입력, 수정, 삭제할때적용되는규칙으로사용되며테이블에의해참조되고있는경우테이블의삭제방지를위해서도사용된다. 관계키참조제약조건을사용하는이유제약조건없이도기본구조의테이블은생성된다. 그러나테이블을생성한경우에 DML 에의한데이터조작이사용자가원하는대로되지않을수있다. 예를들어, 유일하게지정되어야할주민번호컬럼이 UNIQUE 제약조건이없다면중복된데이터가입력될수있다. 이와같이 DML 에의한데이터가잘못조작되는것을방지하기위해사용자는각컬럼에대한제약조건을만들어줄수있다. 이러한제약조건은테이블에행을삽입, 수정, 삭제할때마다사용되며, 테이블에의해참조되고있는경우테이블삭제방지를위해서도사용된다. 제약조건의특징 DML 작업에서잘못되는것을제약조건에의해방지한다. 모든정보가자료사전 (data dictionary) 에저장된다. 언제든지 disable, enable 시킬수있다. 처리결과가즉시사용자에게넘겨진다. 한컬럼에여러개의제약조건을지정할수있다. 하나의컬럼뿐만아니라여러컬럼을조합하여하나의 key 를구성하는 composit key( 복합키 ) 를만들수있다. 예 : PRIMARY KEY(pno, ename) 제약조건의관리는 DB server 가담당한다. 다음 SQL 문에서 constraint 가선언되거나변경할수있다. CREATE TABLE ALTER TABLE CREATE VIEW ALTER VIEW 오라클데이터베이스는 view constraint 를강제하지는않지만, 테이블의 constraint 을통해서 view 에도 constraint 를강제할수있다. constraint 의기본형식은다음과같다. 형식 INline_constraint Out_of_line_constraint 오라클제약조건 12

13 inline_ref_constraint Out_of_line_ref_constraint constraint 의 prototype 은이곳을참조한다. 무결성제약조건형식제약조건의종류 constraint 설명 PRIMARY 해당컬럼값은반드시존재해야하며, 유일해야함 PK KEY (NOT NULL과 UNIQUE 제약조건을결합한형태 ) FOREIGN 해당컬럼값은참조되는테이블의컬럼값중의하나와일치하거나 FK KEY NULL을가짐 UNIQUE UK 테이블내에서해당컬럼값은항상유일해야함 NOT NULL NN 컬럼은 NULL 값을포함할수없다. CHECK CK 해당컬럼에저장가능한데이터값의범위나조건지정 무결성제약조건 (Constraint) 생성무결성제약조건은 CREATE TABLE 문에서테이블생성과동시에정의하거나, ALTER TABLE 문을사용하여테이블을생성한이후에도추가하거나삭제할수있다. CREATE TABLE 문을이용한 constraint 생성방법은다음과같이두가지가있다. 방법 1) IN-LINE constraint 방법 (column level 제약조건 ) 방법 2) OUT-OF-LINE constraint 방법 (Table level 제약조건 ) IN-LINE 제약조건은테이블생성시컬럼명바로뒤에기술하여생성하는방법이고, OUT-OF-LINE 제약조건은마지막제약조건에,( 컴마 ) 로구분한뒤제약조건을기술하여생성한다. 하나의컬럼에대하여제약조건을생성할경우에는두가지방법이모두사용될수있지만, 하나의제약조건이두개이상의컬럼에대하여동시에적용될때는반드시 OUT-OF-LINE 제약조건방법을사용하여생성해야한다. 1) 컬럼레벨무결성제약조건컬럼정의시해당컬럼별로지정하는무결성제약조건이다. 하나의컬럼에적용되는제약조건을정의할때사용한다. 컬럼의정의뒤에곧바로선언한다. 컬럼별로제약조건을정의한다. 무결성 5 가지 ( 아래표에나열된 PK,FK,UK,CK,NN) 를모두적용이가능하다. 컬럼레벨의제약조건을생성하는방법은다음과같으며, FOREIGN KEY 제약조건을지정할경우에는, FOREIGN KEY 라는키워드대신에 REFERENCES 라는키워드를반드시명시해주어야한다. CREATE TABLE 테이블명 ( 컬럼명데이터타입제약조건,... 컬럼명데이터타입제약조건 ); PRIMARY KEY 컬럼명데이터타입 [CONSTRAINT constraint 명 ] PRIMARY KEY( 컬럼명,...) 오라클제약조건 13

14 UNIQUE 컬럼명데이터타입 [CONSTRAINT constraint 명 ] UNIQUE 컬럼명데이터타입 [CONSTRAINT constraint 명 ] FOREIGN KEY REFERENCES 참조테이블명 ( 참조컬럼명 ) [ON DELETE CASCADE ON DELETE SET NULL] CHECK 컬럼명데이터타입 [CONSTRAINT constraint 명 ] CHECK ( 컬럼명조건 ) NOT NULL 컬럼명데이터타입 [CONSTRAINT constraint 명 ] NOT NULL constraint 명은생략가능하며, 오라클서버가자동적으로 constraint 명을부여한다. 이때부여되는형식은 SYS_Cnnn 형태로부여된다. 그러나알기쉽게하기위해사용자가정의한 constraint 명을부여하는것이보다편리하다. 일반적으로 constraint 명은테이블명 _ 컬럼명 _constraint 약자를사용한다. 예를들어, abc 테이블의 name 컬럼을 PRIMARY KEY로선언할때이의 constraint 명은 abc_name_pk와같이부여한다. PRIMARY KEY, UNIQUE와같은제약조건은자동으로 UNIQUE INDEX가생성된다. 이때, USING INDEX를사용하여오라클서버가사용할 INDEX의크기및테이블스페이스를지정할수있다. 2) 테이블레벨무결성제약조건하나이상의컬럼을참조하거나하나의컬럼에두개이상의제약조건을지정하는경우사용한다. 사용자는무결성제약조건의이름을지정할수있으며지정하지않으면 SYS_Cn 형태로자동적으로생성된다. USER_CONS_COLUMNS 데이터딕셔너리를통해서조회할수있다. 특정컬럼과독립적으로정의되므로반드시제약조건이적용되는컬럼을괄호안에명시해야한다. NOT NULL 제약조건은정의되지않는다.( 위표에서 4가지 (PK,FK,UK,CK) 만가능함 ) 무결성제약조건형식테이블레벨의제약조건의생성은다음과같다. CREATE TABLE 테이블명 ( 컬럼명데이터타입,... 컬럼명데이터타입, 제약조건 ); PRIMARY KEY [CONSTRAINT constraint 명 ] PRIMARY KEY( 컬럼 1 명, 컬럼 2 명,...) [CONSTRAINT constraint 명 ] FOREIGN KEY( 컬럼 ) FOREIGN KEY REFERENCES 참조테이블명 ( 참조컬럼명 ) [ON DELETE CASCADE ON DELETE SET NULL] UNIQUE [CONSTRAINT constraint 명 ] UNIQUE( 컬럼1명, 컬럼2명,...) CHECK [CONSTRAINT constraint 명 ] CHECK( 컬럼명조건 ) NOT NULL NOT NULL 제약조건은정의되지않는다 데이터조작시무결성제약조건의적용 오라클제약조건 14

15 DML 에서데이터의무결성을유지하기위하여테이블의정의에기술한제약조건의효력이발생한다. 참조무결성제약조건을위반한경우오류를발생한다. 무결성제약조건을위반한경우오류를발생한다. 자식테이블에입력하려고하는값이부모테이블에없는경우오류를발생한다. 자식테이블에서수정하려고하는값이부모테이블에없는경우오류가발생한다. 부모테이블에서삭제하려고하는값이자식테이블에서참조되는경우오류를발생한다. 기타제약조건에위배되는데이터를입력, 수정, 삭제하는경우에제약조건오류가발생한다. user_constraints 뷰를통해서부모와자식테이블관계를알수있다. SQL> select constraint_name,constraint_type, 2 table_name,r_constraint_name 3 from user_constraints; CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME FK_DEPTNO R EMP PK_DEPT PK_DEPT P DEPT PK_EMP P EMP SQL> 기존테이블에무결성제약조건의추가방법무결성제약조건은기존테이블에대해서도추가로생성할수있다. ALTER TABLE... ADD CONSTRAINT 문은기존테이블에제약조건을추가하기위한명령문이다. 하지만, NOT NULL 무결성제약조건의추가는 'NULL 허용 ' 상태를 'NULL 입력불가 ' 상태로변경하는것을의미하므로 ALTER TABLE... MODIFY 문을사용해야한다. 형식 ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명 ] 제약조건타입 ( 컬럼명 ); SQL> alter table test 2 add constraint test_tel_pk primary key(tel); 테이블이변경되었습니다. SQL> SQL> select * from user_cons_columns; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION SCOTT PK_DEPT DEPT DEPTNO 1 SCOTT PK_EMP EMP EMPNO 1 SCOTT FK_DEPTNO EMP DEPTNO 1 SCOTT TEST_TEL_PK TEST TEL 1 오라클제약조건 15

16 SQL> Constraint 의종류 1)PRIMARY KEY(PK) 테이블에대한기본키를생성한다. 기본키는테이블당하나만존재한다. 그러나, 반드시하나의컬럼으로만구성되는것은아니다. 테이블에서각행을유일하게식별하는컬럼또는컬럼의집합이다. NULL 값이입력될수없고, 이미테이블에존재하고있는데이터를다시입력할수없다. 즉, UNIQUE 와 NOT NULL 제약조건을결합한개념이다. UNIQUE INDEX 가자동으로만들어진다. 데이터를 select 할때 where 절에대한조건으로 INDEX 가생성되어있는컬럼이사용되면오라클은자동으로그 INDEX 를사용하여데이터를빠르게찾아낸다. 그러므로한개의테이블에는 primary key 가하나밖에존재할수없다. user_indexes 뷰를이용하여인덱스를확인할수있다. 컬럼레벨의형식 컬럼명데이터타입 [CONSTRAINT constraint 명 ] PRIMARY KEY( 컬럼명,...) 테이블레벨의형식 컬럼명데이터타입, 컬럼명데이터타입,... [CONSTRAINT constraint 명 ] PRIMARY KEY( 컬럼 1 명, 컬럼 2 명,...) constraint 를추가시에 constraint 명을생략하면오라클서버가자동적으로 constraint 명을부여한다. 일반적으로 constraint 명은 ' 테이블명 _ 컬럼명 _constraint 약자 ' 처럼기술한다. 예제 constraint 명을생략한경우 SQL> create table test(id number(13) primary key); 테이블이생성되었습니다. SQL> select constraint_name,table_name,r_constraint_name, constraint_type 2 from user_constraints; CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME C SYS_C TEST P SQL> 예제 id 컬럼에 PRIMARY KEY 제약조건을컬럼레벨로부여하는예컬럼명데이터타입 constraint SQL 문 id number(4) primary key name varchar2(10) SQL> create table aa( 2 id number(4) constraint aa_id_pk PRIMARY KEY, 오라클제약조건 16

17 3 name varchar2(10), 4 no number(4)); no number(4) Table created. SQL> 예제 no 컬럼에 PRIMARY KEY 제약조건을테이블레벨로부여하는예컬럼명데이터타입 constraint SQL 문 no number(4) primary key sno number(4) name varchar2(10) count number(7) 2)FOREIGN KEY(FK) SQL> create table bb( 2 no number(4), 3 sno number(4), 4 name varchar2(10), 5 count number(7), 6 constraint bb_no_pk PRIMARY KEY(no)); Table created. SQL> 두테이블 A,B 에서테이블 B 의기본키가테이블 A 의외래키 (Foreign key) 이다. 부모, 자식테이블간의행사이에일관성을유지하기위한제약조건이다. 부모테이블은참조를당하는쪽이고, 자식테이블은참조하는쪽이다. FOREIGN KEY 제약조건은참조하는자식테이블에서하나이상의컬럼에대해선언한다. 이때참조되는테이블의컬럼의수와데이터타입이같아야한다.( 부모테이블과자식테이블의참조하는컬럼과참조당하는컬럼의데이터타입은일치해야한다.) 부모테이블이먼저생성된후자식테이블 (foreign key 를포함하는테이블 ) 이생성되어야한다. FOREIGN KEY 는부모테이블의 PRIMARY KEY, UNIQUE 만참조할수있고, 컬럼의값과일치하거나 NULL 값이어야한다. ON DELETE CASCADE 옵션을이용하면부모테이블의행이삭제될때이를참조한자식 오라클제약조건 17

18 테이블의행을동시에삭제할수있다. ON DELETE SET NULL 은자식테이블이참조하는부모테이블의값이삭제되면자식테이블의값을 NULL 값으로변경시킨다. 참조무결성제약조건에서부모테이블의참조키컬럼에존재하지않는값을자식테이블에입력하면오류가발생한다. 컬럼레벨의형식 컬럼명데이터타입 CONSTRAINT constraint 명 REFERENCES 참조테이블명 ( 참조컬럼명 ) [ON DELETE {CASCADE SET NULL}] 테이블레벨의형식 컬럼명데이터타입, 컬럼명데이터타입,... CONSTRAINT constraint 명 FOREIGN KEY( 컬럼 ) REFERENCES 참조테이블명 ( 참조컬럼명 ) [ON DELETE {CASCADE SET NULL}] 예제 테이블레벨로참조토록한경우 SQL> ALTER TABLE aa ADD (CONSTRAINT aa_no_fk FOREIGN KEY(no) REFERENCES orders(no)); 테이블생성후에 constraint 의추가는테이블레벨로해야한다. 예제 aa 테이블의 id 컬럼을사용한컬럼레벨로참조토록함 컬럼명 데이터타입 constraint catalogno number(4) primary key name varchar2(10) no number(4) foreign key SQL> create table catalog( 2 catalogno number(4) CONSTRAINT catalog_catalogno_pk PRIMARY KEY, 3 name VARCHAR2(10), 4 no NUMBER(4) CONSTRAINT catalog_no_fk REFERENCES aa(id)); Table created. SQL> select constraint_name,constraint_type 2 from user_constraints 3 where table_name='catalog'; CONSTRAINT_NAME C CATALOG_CATALOGNO_PK P CATALOG_NO_FK R SQL> 오라클제약조건 18

19 FOREIGN KEY 생성시주의사항참조하고자하는부모테이블을먼저생성해야한다. 참조하고자하는컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이있어야한다. 테이블사이에 PRIMARY KEY 와 FOREIGN KEY 가정의되어있으면, primary key 삭제시 foreign key 컬럼에그값이입력되어있으면삭제가안된다. ( 단, FK 선언때 ON DELETE CASCADE 나 ON DELETE SET NULL 옵션을사용한경우에는삭제된다.) 부모테이블을삭제하기위해서는자식테이블을먼저삭제해야한다. 예제 자식테이블 (catalog) 이먼저삭제후부모테이블 (aa) 을삭제해야함 SQL> drop table aa; drop table aa * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL> drop table catalog; Table dropped. SQL> drop table aa; Table dropped. SQL> 갱신제한과삭제제한에서 DML 명령문 DML 문 부모테이블 자식테이블 INSERT 참조키값이유일한경우에만가능 외래키값이참조키값중의하나와일치하거나 NULL인경우에가능 UPDATE 참조키값을참조하는자식테이블의수정되는외래키값이참조키값중의컬럼값이없는경우에만가능하나와일치할경우에만가능 DELETE 참조키값을참조하는자식테이블의 RESTRICT 컬럼값이없는경우에만가능 항상가능 DELETE 항상가능 CASCADE 항상가능 3)UNIQUE(UK) Table에서지정한컬럼의데이터가중복되지않고유일하다. primary key가아닌경우라도컬럼내의모든값이유일해야할경우사용한다. 중복된값을가지는행이존재할수없다. PRIMARY KEY와유사하나 NULL값을허용한다. 내부적으로 UNIQUE INDEX를만들어처리한다. 즉, 유일키제약조건이정의되면테이블에제약조건과같은이름의인덱스가자동적으로생성된다. user_indexes 뷰를이용하여인덱스를확인할수있다. PRIMARY KEY와 UNIQUE의비교 PRIMARY KEY UNIQUE 오라클제약조건 19

20 한테이블에하나 한테이블에여러개가능 중복되지않는데이터 (unique) 중복되지않는데이터 (UNIQUE) NOT NULL NULL 허용 UNIQUE INDEX가생성됨 UNIQUE INDEX가생성됨 컬럼레벨의형식 컬럼명데이터타입 CONSTRAINT constraint 명 UNIQUE 테이블레벨의형식 컬럼명데이터타입, 컬럼명데이터타입,... CONSTRAINT constraint 명 UNIQUE( 컬럼 1 명, 컬럼 2 명,...) 예제 primary key는 P, Unique key는 U로표시됨 SQL> desc bb; Name Null? Type NO NOT NULL NUMBER(4) SNO NUMBER(4) NAME VARCHAR2(10) COUNT NUMBER(7) SQL> alter table bb 2 ADD (constraint bb_sno_uk UNIQUE(sno)); Table altered. SQL> desc bb; Name Null? Type NO NOT NULL NUMBER(4) SNO NUMBER(4) NAME VARCHAR2(10) COUNT NUMBER(7) SQL> select constraint_name,constraint_type 2 from user_constraints 3 where table_name='bb'; CONSTRAINT_NAME C BB_NO_PK P BB_SNO_UK U SQL> 오라클제약조건 20

21 4)CHECK(CK) 컬럼에서허용가능한데이터의범위나조건을지정하기위한제약조건이다. 하나의컬럼에대하여여러개의 CHECK 무결성제약조건을지정할수있다. CURRVAL, NEXTVAL 과같은가상컬럼이나 SYSDATE, USER 와같은함수는사용할수없다. 동일테이블의컬럼에서다른행을참조할수있다. 컬럼에입력되는데이터를검사해서조건에맞는데이터만입력되도록한다. 조건은 where 절과유사하게해당컬럼에저장되는데이터값의범위, 특정패턴의숫자또는문자열뿐만아니라같은테이블내의다른컬럼도참조할수있다. row( 행 ) 이만족해야하는조건을정의한다. user_constraints 뷰의 constraint_type 에서 CHECK 제약조건이나, NOT NULL 제약조건이모두 C 로표시되지만, 구체적으로확인하기위해서 search_condition 컬럼의값으로확인할수있다. 컬럼레벨의형식 컬럼명데이터타입 CONSTRAINT constraint 명 CHECK( 컬럼명조건 ) 테이블레벨의형식 컬럼명데이터타입, 컬럼명데이터타입,... CONSTRAINT constraint 명 CHECK( 컬럼명조건 ) 예제 SQL> alter table bb 2 ADD (constraint bb_sno_ck check(sno between 1000 and 5000)); Table altered. SQL> select constraint_name,constraint_type,search_condition 2 from user_constraints 3 where table_name='bb'; CONSTRAINT_NAME C SEARCH_CONDITION BB_NO_PK P BB_SNO_UK U BB_SNO_CK C sno between 1000 and 5000 SQL> alter table bb 2 add (constraint bb_nake_nn check (name is not null)); 테이블이변경되었습니다. SQL> select constraint_name,constraint_type,search_condition 2 from user_constraints 3 where table_name='bb'; 오라클제약조건 21

22 CONSTRAINT_NAME C SEARCH_CONDITION BB_NO_PK P BB_SNO_UK U BB_SNO_CK C sno between 1000 and 5000 BB_NAKE_NN C name is not null SQL> 5)NOT NULL(NN) 테이블에서지정한컬럼의데이터가 NULL 값을갖지못한다. NOT NULL 제약조건이없는컬럼은디폴트값으로 NULL 값이허용된다. 컬럼레벨에서만기술이가능하다. NOT NULL 제약조건이정의된컬럼에는 NULL 값이올수없다. 기본적으로테이블에있는모든컬럼에는 NULL 값이들어갈수있지만, 절대적으로 NULL 이입력되지못하게할필요가있을경우도있다. INSERT, UPDATE 문의실행시에체크하여삽입이나갱신의성공여부에영향을준다. user_constraints 뷰의 constraint_type 에서 CHECK 제약조건이나, NOT NULL 제약조건이모두 C 로표시되지만, 구체적으로확인하기위해서 search_condition 컬럼의값이 IS NOT NULL 으로확인할수있다. 기존데이터를 NULL 로수정하는경우에는오류가발생한다. 컬럼레벨의형식 컬럼명데이터타입 CONSTRAINT constraint 명 NOT NULL 테이블레벨의형식 컬럼명데이터타입, 컬럼명데이터타입,... CONSTRAINT constraint 명 CHECK( 컬럼명 IS NOT NULL) 기존테이블에 NOT NULL 컬럼을추가시에는 ADD 보다는 MODIFY 절을사용하면더간단하게부여할수있다. MODIFY 에의한방법 :constraint 이름을지정할수없음 SQL> ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL; 예제 컬럼레벨컬럼명데이터타입 constraint SQL 문 no number(4) not null SQL> create table test( 2 no number(4) constraint test_no_nn NOT NULL, 3 name varchar2(10)); name varchar2(10) Table created. SQL> 오라클제약조건 22

23 SQL> select constraint_name,constraint_type 2 from user_constraints 3 where table_name='test'; CONSTRAINT_NAME C TEST_NO_NN C SQL> 예제 테이블레벨 SQL> select constraint_name,constraint_type 2 from user_constraints 3 where table_name='test'; CONSTRAINT_NAME C TEST_NO_NN C SQL> alter table test 2 add (constraint test_name_nn check (name IS NOT NULL)); Table altered. SQL> select constraint_name,constraint_type,search_condition 2 from user_constraints 3 where table_name='test'; CONSTRAINT_NAME C SEARCH_CONDITION TEST_NO_NN C "NO" IS NOT NULL TEST_NAME_NN C name is not null SQL> 오라클제약조건 23

24 constraint 의비활성화 constraint 의비활성화에대하여살펴보자. 기존에선언된제약조건을비활성화시킬수있다. constraint 를체크하지않는다. 관련된 INDEX 도 DROP 된다. user_indexes 뷰를사용하여확인할수있다. 대량의데이터를테이블에입력하거나, batch 작업시에작업의성능을향상하기위하여사용한다.(primary key 를삭제하지않고도삭제한것과같은기능을하게된다 ) 형식 ALTER TABLE 테이블명 DISABLE CONSTRAINT constraint 명 [CASCADE]; 여기서 cascade 옵션은비활성화시키려는 constraint 를참조하는다른모든 constraint 들도비활성화시킨다. Disable 절을가진 ALTER TABLE 문장을사용하여삭제또는재생성없이 constraint 를비활성화시킬수있다. 예제 SQL> create table dept( 2 deptno number(4) not null primary key, 3 dname varchar2(10), 4 loc varchar2(10)); Table created. SQL> desc dept; Name Null? Type 오라클제약조건 24

25 DEPTNO DNAME LOC NOT NULL NUMBER(4) VARCHAR2(10) VARCHAR2(10) SQL> select constraint_name, table_name,status from user_constraints 2 where table_name='dept'; CONSTRAINT_NAME TABLE_NAME STATUS SYS_C DEPT ENABLED SYS_C DEPT ENABLED SQL> alter table dept disable primary key cascade; Table altered. cascade 옵션을사용한이유는 dept 테이블의 primary key 는 emp 테이블의 deptno 컬럼에의 SQL> select constraint_name, table_name, status from user_constraints 2 where table_name='dept'; CONSTRAINT_NAME TABLE_NAME STATUS SYS_C DEPT ENABLED SYS_C DEPT DISABLED SQL> 예제 SQL> alter table dept 2 add (constraint dept_dname_nn check (dname IS NOT NULL)); Table altered. ALTER TABLE dept MODIFY dname NOT NULL); 처럼해도됨, 다만 constraint 명을지정할수없음 SQL> select constraint_name, table_name,status from user_constraints 2 where table_name='dept'; CONSTRAINT_NAME TABLE_NAME STATUS SYS_C DEPT ENABLED SYS_C DEPT DISABLED DEPT_DNAME_NN DEPT ENABLED SQL> alter table dept 2 disable constraint dept_dname_nn cascade; Table altered. SQL> select constraint_name, table_name,status from user_constraints 2 where table_name='dept'; 오라클제약조건 25

26 CONSTRAINT_NAME TABLE_NAME STATUS SYS_C DEPT ENABLED SYS_C DEPT DISABLED DEPT_DNAME_NN DEPT DISABLED SQL> constraint 의활성화기존에선언된 constraint 의비활성화상태를활성화시킨다. constraint 가 check 기능을다시수행한다. 관련된 index 가다시생성된다. 테이블의행 (row) 이 constraint 에위배되는지않되는지체크한다. check 가끝날때까지테이블에 LOCK 이걸린다. constraint 가생성되면, 디폴트로 Enable( 활성화 ) 된다. utlexcpt.sql 은제약조건에위배되어활성화되지않은행을보관하는테이블로이를수정하여활성화시킬수있다. 형식 ALTER TABLE 테이블명 ENABLE CONSTRAINT constraint 명 ; constraint 가 disable 된후 constraint 에위배되는데이터가들어간경우, 다시 constraint 를 ENABLE 시키려하면 error 가발생한다. 예제 SQL> select constraint_name, table_name,status from user_constraints 2 where table_name='dept'; CONSTRAINT_NAME TABLE_NAME STATUS SYS_C DEPT ENABLED SYS_C DEPT DISABLED DEPT_DNAME_NN DEPT DISABLED SQL> alter table dept 2 enable constraint dept_dname_nn; Table altered. SQL> alter table dept 2 enable constraint sys_c005838; Table altered. SQL> select constraint_name, table_name,status from user_constraints 2 where table_name='dept'; 오라클제약조건 26

27 CONSTRAINT_NAME TABLE_NAME STATUS SYS_C DEPT ENABLED SYS_C DEPT ENABLED DEPT_DNAME_NN DEPT ENABLED SQL> cascade 되어있는 primary key 를 disable 시킨후, enable 하면 PK 는 enable 이되지만, 자식테이블의 FK 는여전히 disable 상태로있게된다. 결론적으로 constraint 의 Enable 과 Disable 에서 Disable 은 CASCADE 즉, 종속적비활성화가일어나지만, Enable 은 CASCADE 가되지않는다. constraint 의삭제제약조건은수정할수없으며, 기존의 constraint 를삭제후재생성하여야한다. constraint 를삭제할려면, 직접 constraint 명을사용해서삭제하거나또는 constraint 가포함된테이블을삭제하면그테이블에속한 constraint 도함께삭제된다. 무결성 constraint 를삭제할때, 그 constraint 는더이상서버에의해서적용되지않기때문에 data dictionary 에서확인할수없다. primary key 는테이블당하나만존재하므로삭제시 constraint 명을지정하지않아도 primary key 제약조건이삭제된다. 예 ) SQL> ALTER TABLE 테이블명 DROP PRIMARY KEY; 방법1) ALTER TABLE 테이블명 DROP [CONSTRAINT constraint 명 PRIMARY KEY UNIQUE( 컬럼명 )] [CASCADE]; CASCADE 옵션은참조하는 FOREIGN KEY 가있을때사용한다. 방법 2) DROP TABLE 테이블명 CASCADE CONSTRAINTS; 테이블과그테이블을참조하는 foreign key 를동시에삭제할수있다. 방법 3) DROP TABLESPACE 테이블스페이스명 INCLUDING CONTENTS CASCADE CONSTRAINTS; 이방법은테이블이다른테이블스페이스에있는테이블의 FOREIGN KEY 에의하여참조되는경우 TABLESPACE 까지함께삭제하는경우이다. 예제 SQL> select * from user_cons_columns; 오라클제약조건 27

28 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION SCOTT PK_DEPT DEPT DEPTNO 1 SCOTT PK_EMP EMP EMPNO 1 SCOTT FK_DEPTNO EMP DEPTNO 1 SCOTT TEST_TEL_PK TEST TEL 1 SQL> alter table test 2 drop constraint test_tel_pk cascade; 테이블이변경되었습니다. SQL> select * from user_cons_columns; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION SCOTT PK_DEPT DEPT DEPTNO 1 SCOTT PK_EMP EMP EMPNO 1 SCOTT FK_DEPTNO EMP DEPTNO 1 SQL> SQL> select constraint_name,constraint_type,table_name 2 from user_constraints; CONSTRAINT_NAME C TABLE_NAME TEST_NAME_NN C TEST TEST_NO_NN C TEST BB_SNO_CK C BB BB_NO_PK P BB BB_SNO_UK U BB 5 rows selected. SQL> alter table test 2 drop constraint test_name_nn; Table altered. SQL> alter table bb 2 drop primary key; 다른방법으로, alter table bb drop constraint bb_no_pk; 처럼해도됨 Table altered. SQL> alter table bb 2 drop constraint bb_sno_uk; 오라클제약조건 28

29 Table altered. SQL> alter table bb 2 drop constraint bb_sno_ck; Table altered. SQL> select constraint_name,constraint_type,table_name 2 from user_constraints; CONSTRAINT_NAME C TABLE_NAME TEST_NO_NN C TEST SQL> deferred constraint Deferred constraint 는 transaction 이 COMMIT 된상태에서수행된모든 DML 작업들을 (insert, delete, update, select 문 ) 한꺼번에처리한다. 이때 constraint 에위배된다면, transaction 전체를 ROLLBACK 시킨다. 일반적으로 DML 작업의끝에서 constraint 를처리하는것을 IMMEDIATE CONSTRAINT 라고한다. DEFERRED CONSTRAINT 는 FOREIGN KEY 가설정된부모테이블과자식테이블관계에서유용하게사용할수있다. constraint 관리 Deferred constraint 의상태에따라 INDEX 를다르게생성한다. 단계 1 : 현재의 primary key, unique constraint 상태가 DISABLE 이라면 INDEX 가필요없다. ENABLE 상태라면단계 2 로넘어간다. 단계 2 : 기존의 INDEX 가있다면별도로만들지않는다. INDEX 가없다면다음단게로넘어간다. 단계 3 : constraint 의상태가 DEFERRED CONSTRAINT 라면 INDEX 에중복값이허용되지않으므로 오라클제약조건 29

30 NON UNIQUE INDEX 가생성되고, DEFERRED CONSTRAINT 가아니라면 UNIQUE INDEX 가생성된다. 또한테이블수정이나삭제시참조하는자식테이블이있을경우에는 CASCADE CONSTRAINTS 옵션을사용해야한다. DROP TABLE table 명 CASCADE CONSTRAINTS; 그리고 tablespace 삭제시에 tablespace 내부의 object 에서 foreign key 가존재할경우에는 CASCADE CONSTRAINTS 옵션을사용해야한다 DROP TABLESPACE tablespace 명 INCLUDING CONTENTS CASCADE CONSTRAINTS; constraint 정보조회 USER_CONSTRAINTS 와 USER_CONS_COLUMNS 를조회하여사용자의모든 constraint 정보를확인할수있다. DBA_CONSTRAINTS 와 DBA_CONS_COLUMNS 를조회하여데이터베이스의모든 constraint 정보를확인할수있다. DBA_CONSTRAINTS 뷰는데이터베이스내부의모든 constraint 의세부정보로 constraint 명과상태등에관한정보. DBA_CONS_COLUMNS 뷰는 CONSTRAINT 와연관된컬럼의정보. $ sqlplus '/as sysdba' SQL> desc dba_constraints; Name Null? Type OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14) SQL> desc dba_cons_columns; Name Null? Type OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) 오라클제약조건 30

31 TABLE_NAME COLUMN_NAME POSITION NOT NULL VARCHAR2(30) VARCHAR2(4000) NUMBER SQL> DBA_CONSTRAINTS 뷰에서 CONSTRAINT_TYPE 의의미는다음과같다. PRIMARY KEY P UNIQUE U constraint type FOREIGN KEY F CHECK, NOT NULL C SQL> SELECT constraint_name,constraint_type,search_condition 2 FROM dba_constraints 3 WHERE table_name='table1'; CONSTRAINT_NAME C SEARCH_CONDITION TABLE1_NAME_NN C "NAME" IS NOT NULL TABLE1_MAIL_UK U TABLE1_NO_PK P SQL> select b.constraint_name AS "PK", a.constraint_name AS "FK" 2 FROM dba_constraints a, dba_cons_columns b 3 WHERE a.owner='jijoe' AND a.table_name='table2' 4 AND a.r_owner=b.owner AND a.r_constraint_name=b.constraint_name 5 AND a.constraint_type='r'; PK FK TABLE1_NO_PK TABLE2_NO_FK SQL> 일반적으로 dba_constraints 와 dba_cons_columns 을 join 시켜사용함으로써필요한정보를획득한다. 특히두테이블사이에형성되는 FOREIGN KEY CONSTAINT 를확인하는데이두뷰를 join 시켜사용하면유용하다. SQL> list 1 select a.constraint_name, a.status, b.column_name 2 FROM dba_constraints a, dba_cons_columns b 3* WHERE a.owner=b.owner AND a.table_name='table1' SQL> / CONSTRAINT_NAME STATUS COLUMN_NAME 오라클제약조건 31

32 TABLE1_NO_PK TABLE1_MAIL_UK TABLE1_NAME_NN TABLE1_NO_PK TABLE1_MAIL_UK TABLE1_NAME_NN TABLE1_NO_PK TABLE1_MAIL_UK TABLE1_NAME_NN TABLE1_NO_PK TABLE1_MAIL_UK ENABLED NO ENABLED NO ENABLED NO ENABLED NAME ENABLED NAME ENABLED NAME ENABLED MAIL ENABLED MAIL ENABLED MAIL ENABLED NO ENABLED NO CONSTRAINT_NAME STATUS COLUMN_NAME TABLE1_NAME_NN ENABLED NO TABLE1_NO_PK ENABLED JUMIN1 TABLE1_MAIL_UK ENABLED JUMIN1 TABLE1_NAME_NN ENABLED JUMIN1 TABLE1_NO_PK ENABLED JUMIN2 TABLE1_MAIL_UK ENABLED JUMIN2 TABLE1_NAME_NN ENABLED JUMIN2 TABLE1_NO_PK ENABLED ADDRESS TABLE1_MAIL_UK ENABLED ADDRESS TABLE1_NAME_NN ENABLED ADDRESS TABLE1_NO_PK ENABLED ID CONSTRAINT_NAME STATUS COLUMN_NAME TABLE1_MAIL_UK ENABLED ID TABLE1_NAME_NN ENABLED ID TABLE1_NO_PK ENABLED ID TABLE1_MAIL_UK ENABLED ID TABLE1_NAME_NN ENABLED ID TABLE1_NO_PK ENABLED NAME TABLE1_MAIL_UK ENABLED NAME TABLE1_NAME_NN ENABLED NAME TABLE1_NO_PK ENABLED AA TABLE1_MAIL_UK ENABLED AA TABLE1_NAME_NN ENABLED AA 33 rows selected. SQL> 무결성제약조건이있는상태에서 insert, update,delete 시 ERROR 발생제약조건에대한정보조회 all_constraints user 가 access 할수있는 constraint 정보 오라클제약조건 32

33 all_cons_columns user_constraints user 가 access 할수있는컬럼별 constraint 정보 user 소유테이블의 constraint 정보 user_cons_columns user 소유테이블의각컬럼별 constraint 정보 dba_constraints dba_cons_columns 모든테이블에대한 constraint 정보 모든테이블에대한컬럼별 constraint 정보 오라클제약조건 33

34 오라클 TIMESTAMP 이용한백업 :39 출처 : 오라클 COMMAND 창에서 sql> show parameter undo; 를쳐보면 undo_management undo_retention undo_tablespace 의속성들에대한정보가나오게된다. 그중 undo_retention 은 delete, update 후에 ROLLBACK 이아닌 COMMIT 을하였을때부터 속성값의초까지는오라클에서임시로저장을하게끔되어있다. DEFAULT 속성값은 '900' 으로 900/60 초 = 15 분 COMMIT 후 15 분안에는데이터를복구할수있게된다. 그시간을늘리거나줄이려면 'alter system set undo_retention = 1500 ; ' 초 (25 분 ) 이렇게늘릴수가있다. 지금까지는복구를위한셋팅방법이었으며, 이제는복구방법을알아보자. 복구를하는방법은 DELETE FROM TEST WHERE USER_ID = 'ITDI'; COMMIT; 위와같은방법으로 USER_ID = 'ITDI' 의데이터를 TEST 테이블에서삭제를하고 COMMIT 을하였을경우, 오라클 TIMESTAMP 이용한백업 34

35 SELECT * FROM TEST AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE) WHERE USER_ID = 'ITDI' ; 이렇게하면삭제를한지 15 분안의데이터를찾아서조회를할수있다. 복구하는방법은 CTAS 를써서임시테이블에넣어서차차복구를하여도되고 다이렉트로 INSERT INTO TEST SELECT * FROM TEST AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE) WHERE USER_ID = 'ITDI' ; 이렇게처리를하여도된다. 일단서버를셋팅을하게되고중요한운영 DB 일경우에는이와같은방법으로 데이타의삭제를예방할수있다. DMP 백업이나 ARCHIVE 백업등의처리또한백업의종류이기는하나, 위와같은방법이백업복구의대처능력이다른복구작업보다더좋다고생각한다. 오라클 TIMESTAMP 이용한백업 35

36 HOW TO DISABLE ARCHIVE LOG IN ORACLE? :59 HOW TO DISABLE ARCHIVE LOG IN ORACLE? Posted by Rohit Khurana on February 5, 2012 at 12:00pm View Blog SQL*Plus: Release Production on Mon Feb 6 01:36: Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options SQL> set line 9090 SQL> SHOW USER USER is "SYS" Step -1) Check the Existing mode in your Database SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ARCHIVELOG Step -2) Shut Down Your Database Cleanly to Disable Archiving Mode. SQL> SHUT IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. Step -3) Start up Your Database in Mount Stage. SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area bytes HOW TO DISABLE ARCHIVE LOG IN ORACLE? 36

37 Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted. Step -4) SQL> ALTER DATABASE NOARCHIVELOG; Database altered. SQL> ALTER DATABASE OPEN; Database altered. DONE :-) HOW TO DISABLE ARCHIVE LOG IN ORACLE? 37

38 아카이브로그모드 (Archive Log Mode) :40 아카이브로그모드 (Archive Log Mode) 란? 우리가오라클데이터베이스에접속을해서 DML 이나 DDL 등의명령어로작업을수행하면, 모든작업의기록이리두로그파일에저장이된다. 작업의양이많아지면리두로그파일에기록하는내용도굉장히많아지게되겠죠. 그렇게되면데이터를기록하기위해서리두로그파일을늘려야하는일이발생을한다. 그런데오라클리두로그파일은계속증가하는것이아니라몇개의리두로그파일을만들어놓고번갈아가면서기록하는구조로되어있다. 이렇게번갈아가면서기록을하게되면새로운작업의내용이예전의작업내용을덮어쓰므로예전의작업한내용을잃게된다는단점이있다. 그래서예전의작업한내용에데이터손실이발생하면복구하기어렵다는단점이있다. 이런단점을해결하기위한방법이리두로그파일의내용을다른디렉토리에자동으로복사해서저장하도록운영하는방법이다. 이렇게운영하는방법을아카이브로그모드 (Archive Log Mode) 라고한다. 오라클데이터베이스는기본적으로 No Archive Log Mode 이고, Archive Log Mode 로운영하기위해서는따로설정을해주어야한다. PFILE 을수정하여데이타베이스를 archivelog mode 로설정하기 NO ARCHIVE LOG 상태의데이터베이스를 ARCHIVE LOG 모드상태로변경하기위해서는다음과같은순서로작업해야한다. 1) INIT.ORA 파라미터파일을수정한다. 2) 데이터베이스인스턴스를종료 (SHUTDOWN) 한다. 3) 데이터베이스인스턴스를 MOUNT 한다.(OPEN 하지않습니다 ) 4) 데이터베이스를 ARCHIVE LOG 모드로변경한다. 5) 데이터베이스인스턴스를 OPEN 한다. 1) INIT.ORA 파일의 parameter 수정 INIT.ORA 파일에서아래부분을수정하고, 주석 (#) 을제거하고저장합니다. # 아카이브프로세스를오라클시작과함께실행하도록설정 # log switch 발생시자동으로 archive 를수행합니다 LOG_ARCHIVE_START = TRUE # 아카이브로그파일을저장할디렉토리설정 LOG_ARCHIVE_DEST = "C:\oracle\ora92\database\archive" 아카이브로그모드 (Archive Log Mode) 38

39 # 아카이브로그파일의이름설정 LOG_ARCHIVE_FORMAT = %S.ARC LOG_ARCHIVE_FORMAT 옵션 - %S : redo 로그시퀀스번호를표시하여자동으로왼쪽이 0으로채워져파일이름길이를일정하게만든다. - %s : redo 로그시퀀스번호를표시하고, 파일이름길이를일정하게맞추지않는다. - %T : redo 스레드넘버를표시하며, 자동으로왼쪽이 0으로채워져파일이름길이를일정하게만든다. - %t : redo 스레드넘버를표시하며, 파일이름길이를일정하게맞추지않는다. 2) 데이터베이스인스턴스를종료 -- SQLPLUS 실행 SQLPLUS /nolog -- SYSDBA 권한으로접속합니다. SQL>CONN SYS/MANAGER AS SYSDBA SQL> SHUTDOWN IMMEDIATE 데이터베이스가닫혔습니다. 데이터베이스가마운트해제되었습니다. ORACLE 인스턴스가종료되었습니다. 3) 데이터베이스인스턴스를 MOUNT SQL> STARTUP MOUNT pfile=c:\oracle\ora92\database\initora9i.ora 데이터베이스가마운트되었습니다. 4) DATABASE 를 ARCHIVE LOG MODE 로전환. SQL> ALTER DATABASE ARCHIVELOG; 데이타베이스가변경되었습니다. 5) DATABASE OPEN SQL> ALTER DATABASE OPEN; 6) ARCHIVE LOG MODE 가정상적으로설정되어있는지확인한다. SQL> ARCHIVE LOG LIST 데이터베이스로그모드 아카이브모드 아카이브로그모드 (Archive Log Mode) 39

40 자동아카이브 사용 아카이브대상 C:\oracle\ora92\database\archive 가장오래된온라인로그순서 16 아카이브할다음로그 18 현재로그순서 18 7) 강제로로그스위치를발생시켜서아카이브로그파일이저장되는지확인 C:\oracle\ora92\database\archive 디렉토리에파일이생성되었는지확인한다. SQL> ALTER SYSTEM SWITCH LOGFILE; 시스템이변경되었습니다. ARCHIVELOG MODE 에서 NO ARCHIVELOG MODE 로전환하기 먼저, 위에서 setting 했던 INIT.ORA 파일에서설정했던부분을 (#) 으로주석처리한다. #LOG_ARCHIVE_START = TRUE #LOG_ARCHIVE_DEST = "C:\oracle\ora92\database\archive" #LOG_ARCHIVE_FORMAT = %S.ARC -- 데이터베이스종료 SQL> SHUTDOWN IMMEDIATE -- 데이터베이스인스턴스를 mount SQL> STARTUP MOUNT pfile=c:\oracle\ora92\database\initora9i.ora -- 데이터베이스를 no archive log mode 로전환. SQL> ALTER DATABASE NOARCHIVELOG; -- database open SQL> ALTER DATABASE OPEN; -- 아카이브로그모드상태확인 SQL> ARCHIVE LOG LIST 데이터베이스로그모드 아카이브모드가아님 자동아카이브 사용안함 아카이브대상 C:\oracle\ora92\RDBMS 가장오래된온라인로그순서 17 현재로그순서 19 SPFILE( 서버파라미터파일 ) 을수정하여데이타베이스를 ARCHIVELOG MODE 로설정 Oracle9i 이상의경우서버파라미터파일을사용할경우아래와같은과정을거쳐서아카이브로그모드로변경해야한다. 1) 파라미터설정 아카이브로그모드 (Archive Log Mode) 40

41 -- sqlplus 실행 SQLPLUS /nolog -- SYSDBA 권한으로접속합니다. SQL> CONN / AS SYSDBA -- LOG_ARCHIVE_START 파라미터변경 SQL> ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE; -- LOG_ARCHIVE_DEST 파라미터변경 SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST='C:\oracle\ora92\database\archive' SCOPE=SPFILE; -- LOG_ARCHIVE_FORMAT 파라미터변경 SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%S.ARC' SCOPE=SPFILE; 2) DB Shutdown SQL> SHUTDOWN IMMEDIATE 3) Mount 상태로 Startup SQL> STARTUP MOUNT 4) 아카이브로그모드활성화 SQL>ALTER DATABASE ARCHIVELOG; 5) 데이타베이스오픈 SQL> ALTER DATABASE OPEN; 6) 아카이브로그모드가정상적으로설정되어있는지확인한다. SQL> ARCHIVE LOG LIST; 데이터베이스로그모드 아카이브모드 자동아카이브 사용 아카이브대상 C:\oracle\ora92\database\archive 가장오래된온라인로그순서 17 아카이브할다음로그 19 현재로그순서 19 SPFILE( 서버파라미터파일 ) 에서 NO ARCHIVE LOG 모드로전환하기 1) 자동아카이브모드를 false 로변경한. SQL> ALTER SYSTEM SET 아카이브로그모드 (Archive Log Mode) 41

42 LOG_ARCHIVE_START=FALSE SCOPE=SPFILE; 2)DB shutdown SQL> SHUTDOWN IMMEDIATE 3) mount 상태로 startup SQL> STARTUP MOUNT 4) 데이터베이스를 no archive log mode 로전환. SQL> ALTER DATABASE NOARCHIVELOG; 5) 데이타베이스오픈 SQL> ALTER DATABASE OPEN; 6) 아카이브로그모드상태확인 SQL> ARCHIVE LOG LIST; 데이터베이스로그모드 아카이브모드가아님 자동아카이브 사용안함 아카이브대상 C:\oracle\ora92\database\archive 가장오래된온라인로그순서 17 현재로그순서 19 출처 : 아카이브로그모드 (Archive Log Mode) 42

43 ORA-00257: 아카이버오류, :33 SQL> connect /as sysdba 연결되었습니다. SQL> recover database; ORA-00283: 복구세션이오류로인하여취소되었습니다. ORA-00264: 복구가필요하지않습니다. SQL> recover database until cancel; 매체복구가완료되었습니다. SQL> alter database open resetlogs; 데이타베이스가변경되었습니다. SQL> shutdown sqlplus 접속시 "ORA-00257: 아카이버오류. 공간이확보되기전에는내부접속만가능." 에러발생 - 아카이브용량확인 C>sqlplus / as sysdba SQL> select * from v$recovery_file_dest; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES D:\archivelog E E+11 SQL> select dest_name,error from v$archive_dest; DEST_NAME ERROR LOG_ARCHIVE_DEST_1 LOG_ARCHIVE_DEST_2 LOG_ARCHIVE_DEST_3 LOG_ARCHIVE_DEST_4 LOG_ARCHIVE_DEST_5 LOG_ARCHIVE_DEST_6 LOG_ARCHIVE_DEST_7 LOG_ARCHIVE_DEST_8 LOG_ARCHIVE_DEST_9 LOG_ARCHIVE_DEST_10 ORA-19809: 복구파일에대한한계를초과함 SQL> recover database; ORA-00283: 복구세션이오류로인하여취소되었습니다 ORA-01124: 1 데이터파일을복구할수없음 - 파일이사용중이거나복구중입니다 ORA-01110: 1 데이터파일 : 'D:\ORADATA\ORADB\SYSTEM01.DBF' ORA-00257: 아카이버오류, 43

44 해결방법 1. 아카이브로그를삭제 SQL> select * from v$recovery_file_dest; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES D:\archivelog E E+11 D:\archivelog 이아래파일모두삭제 db 리스타트 플래쉬백해제 alter database flashback off; 2. 아카이브모드해제 SQL> startup mount SQL> alter database noarchivelog; SQL> archive log list SQL> alter database open; ORA-00257: 아카이버오류, 44

45 문자열중에서숫자만골라내서콤마붙이기 :14 CREATE OR REPLACE FUNCTION OP_BPSBSC.FC_BPSSB_FML_TXT ( I_OUT_FML IN BPSSB_OPR_PLAN.OUT_FML%TYPE ) RETURN VARCHAR2 IS V_RETVAL BPSSB_OPR_PLAN.OUT_FML%TYPE := ''; V_CHR_CNT NUMBER := 0; -- 전체문자열길이 V_CHR VARCHAR2(3) := NULL; -- 문자하나잘라서받을변수 TYPE T_ARRAY IS TABLE OF BPSSB_OPR_PLAN.OUT_FML%TYPE; -- 테이블변수타입선언 V_NUM_ARR T_ARRAY := T_ARRAY(); -- 테이블변수선언 V_TXT_ARR T_ARRAY := T_ARRAY(); -- 테이블변수선언 V_OUT_FML VARCHAR2(100) := ''; V_LCNT NUMBER := 0; V_RVAL VARCHAR2(100) := ''; V_TMP VARCHAR2(100) := ''; V_RETURN VARCHAR2(100) := ''; BEGIN IF I_OUT_FML <> '' OR I_OUT_FML IS NOT NULL THEN V_OUT_FML := REPLACE(I_OUT_FML,',',''); V_CHR_CNT := LENGTH(V_OUT_FML); 문자열중에서숫자만골라내서콤마붙이기 45

46 FOR I IN 1..V_CHR_CNT LOOP V_CHR := SUBSTR(V_OUT_FML,I,1) ; V_NUM_ARR.EXTEND(1); V_NUM_ARR(V_NUM_ARR.COUNT):= V_CHR; END LOOP; FOR K IN REVERSE V_NUM_ARR.FIRST.. V_NUM_ARR.LAST LOOP IF FC_BPSSB_ISNUMBER(V_NUM_ARR(K)) = 1 THEN V_LCNT := V_LCNT + 1; IF MOD(V_LCNT,3) = 0 AND K < V_NUM_ARR.COUNT THEN IF K > 1 AND FC_BPSSB_ISNUMBER(V_NUM_ARR(K-1)) = 1 THEN V_RVAL := V_RVAL V_NUM_ARR(K) ',' ; ELSE V_RVAL := V_RVAL V_NUM_ARR(K) ; END IF; ELSE V_RVAL := V_RVAL V_NUM_ARR(K) ; END IF; ELSE V_LCNT := 0; V_RVAL := V_RVAL V_NUM_ARR(K) ; END IF; 문자열중에서숫자만골라내서콤마붙이기 46

47 END LOOP; V_CHR_CNT := LENGTH(V_RVAL); FOR I IN 1..V_CHR_CNT LOOP V_CHR := SUBSTR(V_RVAL,I,1) ; V_TXT_ARR.EXTEND(1); V_TXT_ARR(V_TXT_ARR.COUNT):= V_CHR; END LOOP; FOR K IN REVERSE V_TXT_ARR.FIRST.. V_TXT_ARR.LAST LOOP V_RETURN := V_RETURN V_TXT_ARR(K) ; END LOOP; ELSE V_RETURN := ''; END IF; RETURN V_RETURN; EXCEPTION WHEN OTHERS THEN V_RETURN := I_OUT_FML; END FC_BPSSB_FML_TXT; 문자열중에서숫자만골라내서콤마붙이기 47

48 오라클에서 ISNUMERIC 함수구현하기 :05 CREATE OR REPLACE FUNCTION ISNUMERIC(i_str VARCHAR2) RETURN NUMBER IS v_ret NUMBER; BEGIN IF i_str IS NULL OR LENGTH(TRIM(i_str)) = 0 THEN RETURN 0; END IF; V_RET := TO_NUMBER(I_STR); RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN 0; END ; 오라클에서 ISNUMERIC 함수구현하기 48

49 오라클에서중복데이터제거하기 :28 기본키 (primary) 가없는상태에서중복되는데이터가있을경우 나중에들어온중복데이터삭제하기. DELETE FROM 테이블 A WHERE ROWID > (SELECT MIN(ROWID) FROM 테이블 B WHERE A. 컬럼 = B. 컬럼 먼저들어온중복데이터삭제하기 DELETE FROM 테이블 A WHERE ROWID < (SELECT MAX(ROWID) FROM 테이블 B WHERE A. 컬럼 = B. 컬럼 오라클에서중복데이터제거하기 49

50 오라클자바클래스호출 :09 단순한 Java class 를 Oracle 에로드시켜실행시키는절차를정리해봤습니다. 참조 1. 간단한 class 파일생성 2. java 컴파일 - Oracle 버전에따라지원하는 JDK 버전이한정되어있습니다. 저의경우는 11g / JDK 따로받기보단 Oracle Client에위치한 JDK를이용해컴파일했습니다. - Oracle Client JDK Path : D:\oracle\product\11.2.0\client_1\jdk\bin 3. java class 를 oracle 에 load 4. java class 호출 function 생성 5. 테스트 오라클자바클래스호출 50

51 프로시저이력관리 :04 툴은아니고프로시져나뷰, 펑션등을수정할때마다쏘스를저장하는방법이있습니다. DDL 트리거를이용합니다. CREATE TABLE dbo.splog( 일련번호 int IDENTITY(1,1) NOT NULL, 오브젝트명 varchar(100) NULL, 구분 varchar(20) NULL, SQLCMD varchar(max) NULL, 수정자 varchar(20) NULL, 수정일 datetime NULL, CONSTRAINT XPKSPLOG PRIMARY KEY NONCLUSTERED ( 일련번호 ASC )) GO CREATE TRIGGER TRG_SPLOG ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER AS XML = EVENTDATA() INSERT INTO DBO.SPLOG ( 오브젝트명, 구분, SQLCMD, 수정자, 수정일 ) VALUES (@DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(MAX)'), HOST_NAME(), 프로시저이력관리 51

52 GETDATE()) 프로시저이력관리 52

53 MERGE INTO 오류사항 :00 프로그램코딩시유의할오류유형 ) 1.ORA 에러가발생하는경우 ORA 원본테이블의고정행집합을가져올수없습니다. ORA unable to get a stable set of rows in the source tables - MERGE 를사용하는경우 1) INTO 절에사용되는테이블에 Primary Key 를사용하는경우즉 INSERT 구문에서 DUPLICATE가발생하거나 UPDATE 에 MULTI ROW가 UPDATE되는경우 2) ON 구문에서 UPDATE되는 ROW가 1개이상일경우즉 ON 구문에서맞는테이블값이하나이상일경우 2.ORA 에러가발생하는경우이에러는 TARGET이되는테이블에는중복건이없어서 UPDATE를할려고했는데, SOURCE테이블자체에서데이터가중복건이있어서두번INSERT하려고하다가문제가발생합니다. 보통문자타입이나사이즈를잘못잡아서 INSERT할대상자체를중복인지아닌지모르는경우에발생하게되므로, TRIM처리를해서비교하시든파일자체에중복이있는지확인을하시든하는방법으로에러를처리하실수있습니다. MERGE INTO 오류사항 53

54 The Data Dictionary: Make Views Work for You :07 TECHNOLOGY: PL/SQL As Published In The Data Dictionary: Make Views Work for You By Steven Feuerstein Part 10 in a series of articles on understanding and using PL/SQL November/December 2012 If you re reading this article, there s a really good chance that you write PL/SQL code. Lots of it. Which means that you also will at least occasionally need to analyze that code, answering questions such as On which database objects does my program depend? Which of my packages contain calls to a subprogram in another package or a reference to a global variable? Do any of my subprograms contain parameters whose datatypes should not be used? Are all of my subprograms compiled with a sufficiently high level of optimization? You can, of course, always use the search feature of your editor or integrated development environment to look through multiple database objects and files to find specific chunks of text. But that won t be enough to answer all of the above questions and many more you will encounter. Don t despair! One of the most lovely aspects of writing PL/SQL code and compiling that code into the database is that Oracle Database offers a set of views known collectively as the data dictionary that enable you to use the SQL and PL/SQL languages to get answers to just about any question you have about your code. Table 1 offers a high-level overview of the data dictionary views most often used to manage PL/SQL code. USER_ARGUMENTS The arguments (parameters) in all the procedures and functions in your schema. USER_DEPENDENCIES The dependencies to and from objects you own. This view is used mostly by Oracle Database to invalidate the status of database objects when an object on which they depend changes. USER_ERRORS The current set of compilation errors for all stored objects (including triggers) you own. This view is accessed by the SHOW ERRORS SQL*Plus command. You can, however, write your own queries against it as well. USER_IDENTIFIERS Introduced in Oracle Database 11g and populated by the PL/Scope compiler utility. Once populated, this view provides you with information about all the identifiers program names, variables, and so on in your code base. The Data Dictionary: Make Views Work for You 54

55 USER_OBJECT_SIZE The size of the objects you own. Actually, this view shows you the source, parsed, and compile sizes for your code. Although it is used mainly by the compiler and runtime engine, you can use it to identify the large programs in your environment. USER_OBJECTS The objects you own. You can, for instance, use this view to see if an object is marked INVALID, find all the packages that have EMP in their names, and so on. USER_PLSQL_OBJECT_SETTINGS Information about the characteristics such as the optimization level and debug settings of a PL/SQL object that can be modified through the ALTER and SET DDL commands. USER_PROCEDURES Information about stored programs, such as the AUTHID setting, whether the program was defined as DETERMINISTIC, and so on. USER_SOURCE The text source code for all objects you own (in Oracle9i Database and above, including database triggers and Java source). This is a very handy view, because you can run all sorts of analyses of the source code against it with SQL and, in particular, Oracle Text. USER_STORED_SETTINGS PL/SQL compiler flags. Use this view to discover which programs have been compiled via native compilation. USER_TRIGGERS and USER_TRIGGER_COLS The database triggers you own (including the source code and a description of the triggering event) and any columns identified with the triggers, respectively. You can write programs against USER_TRIGGERS to enable or disable triggers for a particular table. Table 1: Useful views for PL/SQL programmers This article explores many of the views in the table, describing the most useful columns in the views and offering examples of how you can put those views to use. Data Dictionary Fundamentals The data dictionary consists of numerous tables and views created by the database instance. User schemas generally have no privileges on these tables; Oracle Database grants only SELECT access on the views. Most data dictionary views come in three versions: 1. The USER view: information about database objects owned by the schema to which you are connected 2. The ALL view: information about database objects to which the currently connected schema has access The Data Dictionary: Make Views Work for You 55

56 3. The DBA view: unrestricted information about all the database objects in a database instance (non-dba schemas usually have no authority to query DBA views) Let s look at an example. Suppose I want to obtain a list of the objects defined in the database. tables, views, packages, and so on The following query returns all the objects defined in my schema: SELECT * FROM user_objects This query returns all the objects that are defined in my schema or for which I have been granted the privilege to use those objects in some way: SELECT * FROM all_objects Finally, the following query returns a list of all the objects defined in the database instance to select from the view: if I have the authority SELECT * FROM dba_objects Usually the only difference between the USER view and the ALL view is that the latter contains one extra column, OWNER, that shows which schema owns the object. The remainder of this article provides examples based on the USER view. Display Information About Stored Objects The USER_OBJECTS view contains a row for every database object owned by your schema. The most commonly used columns are OBJECT_NAME: Name of the object OBJECT_TYPE: Type of the object, such as PACKAGE, FUNCTION, or TRIGGER STATUS: Status of the object VALID or INVALID LAST_DDL_TIME: Time stamp indicating the last time this object was changed Here are some examples of queries against USER_OBJECTS. Show the names of all tables in my schema: SELECT object_name FROM user_objects WHERE object_type = 'TABLE' ORDER BY object_name Show the names of all objects whose status is invalid: SELECT object_type, object_name FROM user_objects WHERE status = 'INVALID' ORDER BY object_type, object_name The status of a program unit (PL/SQL package, procedure, or function) is set to INVALID if a database object on which it depends is changed. That program unit must then be recompiled (which Oracle Database will often The Data Dictionary: Make Views Work for You 56

57 do automatically the next time you try to use that program unit). Show all objects that have been changed today: SELECT object_type, object_name, last_ddl_time FROM user_objects WHERE last_ddl_time >= TRUNC (SYSDATE) ORDER BY object_type, object_name Display and Search Source Code All the program unit source code you ve compiled into the database is accessible through the USER_SOURCE view, whose columns are NAME: Name of the object TYPE: Type of the object (ranging from PL/SQL program units to Java source and trigger source) LINE: Number of the line of the source code TEXT: Text of the source code You can write queries against USER_SOURCE to Find all the program units that call a particular subprogram of a package Verify that coding standards are being followed Find all occurrences of a literal value that needs to be changed Here is an example: I need to change the parameter list and code of a procedure named CALC_TOTALS in the SALES_MGR package. I d like to find out where this procedure is called, outside of the SALES_MGR package itself. SELECT name, line, text FROM user_source WHERE UPPER (text) LIKE '%SALES_MGR.CALC_TOTALS%' ORDER BY name, line Of course, this query will also find comments that contain this string, and there could be invocations of CALC_TOTALS that are not found, such as SALES_MGR. CALC_TOTALS Assuming, however, that you don t write or format your code to break up subprogram calls like that, the query will do a pretty good job of identifying the places in your code you need to review. And for an Oracle Database 11g instance, you could use the PL/Scope feature. See the A Better USER_SOURCE sidebar for more information. Compiler Settings of Stored Code The USER_PLSQL_OBJECT_SETTINGS view provides information about compiler settings of stored PL/SQL objects. Key columns are The Data Dictionary: Make Views Work for You 57

58 PLSQL_OPTIMIZE_LEVEL: Optimization level that was used to compile the object PLSQL_CODE_TYPE: Compilation mode for the object PLSQL_DEBUG: Whether or not the object was compiled for debugging PLSQL_WARNINGS: Compiler warning settings that were used to compile the object NLS_LENGTH_SEMANTICS: NLS length semantics that were used to compile the object Here are some examples of queries against USER_PLSQL_OBJECT_SETTINGS. Find all the program units that are not taking sufficient advantage of compile time optimization in Oracle Database: SELECT name FROM user_plsql_object_settings WHERE plsql_optimize_level < 2 An optimization level of 0 means no optimization at all. An optimization level of 1 means a minimal amount of optimization. Neither of these levels should be seen in a production environment. Identify all programs for which compile time warnings (which provide feedback on the quality of your code) are disabled: SELECT name, plsql_warnings FROM user_plsql_object_settings WHERE plsql_warnings LIKE '%DISABLE%'; Detailed Information About Procedures and Functions The USER_PROCEDURES view provides information about all functions and procedures, both schema-level and those defined within packages, in your schema. Columns of this view are AUTHID: Shows whether a procedure or a function is defined as an invoker rights (CURRENT_USER) or definer rights (DEFINER) program unit DETERMINISTIC: Set to YES if the function is defined to be deterministic, which theoretically means that the value returned by the function is determined completely by the function s argument values PIPELINED: Set to YES if the function is defined as a pipelined function, which means that it can be executed in parallel as part of a parallel query OVERLOAD: Set to a positive number if this subprogram is overloaded, which means that there are at least two subprograms with this name in the same package Here are some examples of queries against USER_PROCEDURES. Find all the procedures and functions that will run under invoker rights (the privileges of the invoker of the program are used at runtime to resolve references to database objects such as tables): SELECT object_name, procedure_name FROM user_procedures WHERE authid = 'CURRENT_USER' ORDER BY object_name, procedure_name The Data Dictionary: Make Views Work for You 58

59 Show all the functions declared to be deterministic: SELECT object_name, procedure_name FROM user_procedures WHERE deterministic = 'YES' ORDER BY object_name, procedure_name Analyze and Modify Trigger State If you work with database triggers, USER_TRIGGERS, which contains a row for each trigger defined in your schema, will come in handy. Key columns are TRIGGER_NAME: The name of the trigger TRIGGER_TYPE: A string that shows if this is a BEFORE or AFTER trigger and whether it is a row- or statementlevel trigger (in a trigger that is fired before an INSERT statement, for example, the value of this column is BEFORE STATEMENT) TRIGGERING_EVENT: The type of SQL operation that will cause the trigger to fire such as INSERT, INSERT OR UPDATE, DELETE OR UPDATE TABLE_NAME: The name of the table on which the trigger is defined STATUS: The status of the trigger ENABLED or DISABLED WHEN_CLAUSE: An optional clause you can use to avoid unnecessary execution of the trigger body TRIGGER_BODY: The code executed when the trigger fires Here are some examples of queries against USER_TRIGGERS. Find all disabled triggers: SELECT * FROM user_triggers WHERE status = 'DISABLED' Find all row-level triggers defined on the EMPLOYEES table: SELECT * FROM user_triggers WHERE table_name = 'EMPLOYEES' AND trigger_type LIKE '%EACH ROW' Find all triggers that fire when an UPDATE operation is performed: SELECT * FROM user_triggers WHERE triggering_event LIKE '%UPDATE%' One limitation in the USER_TRIGGERS view is that the TRIGGER_BODY column type is LONG, which means that it The Data Dictionary: Make Views Work for You 59

60 cannot be used in a SQL comparison. Suppose, for example, that I want to find all the triggers whose trigger body contains the string emp. The following query, unfortunately, fails and produces an ORA error: SELECT * FROM user_triggers WHERE trigger_body LIKE '%emp%' So if you do want to search the contents of trigger bodies, you will need to use PL/SQL, in a block like this: BEGIN FOR rec IN (SELECT * FROM user_triggers) LOOP IF rec.trigger_body LIKE '%emp%' THEN DBMS_OUTPUT.put_line ( 'Found in ' rec.trigger_name); END IF; END LOOP; END; Note that the USER_TRIGGER_COLS view keeps track of the columns that are referenced inside a trigger body. Object Dependency Analysis The USER_DEPENDENCIES view describes the dependencies between the procedures, packages, functions, package bodies, and triggers accessible to the current user. You can use it to perform impact analysis on your code, as in: How many programs will need to be changed if I change this table? Key columns in this view are NAME: Name of the object TYPE: Type of the object REFERENCED_OWNER: Owner of the referenced object REFERENCED_NAME: Name of the referenced object REFERENCED_TYPE: Type of the referenced object Here are some examples of queries against USER_DEPENDENCIES. Find all the objects that depend on (reference) the EMPLOYEES table: SELECT type, name FROM user_dependencies WHERE referenced_name = 'EMPLOYEES' ORDER BY type, name Find all the objects in the current schema on which the ORDER_MGR package depends: SELECT referenced_type, referenced_name FROM user_dependencies WHERE name = 'ORDER_MGR' AND referenced_owner = USER ORDER BY referenced_type, referenced_name A best practice that I, and others, strongly recommend is to avoid repeating SQL statements by hiding those statements inside a procedure or a function. Let s look at an example and then at how the USER_DEPENDENCIES view can help us identify violations of this best practice. The Data Dictionary: Make Views Work for You 60

61 It is very common in PL/SQL code to find many queries that retrieve a single row for a primary key. Here s a PL/SQL example with a query that uses the standard Oracle Database EMPLOYEES table: PROCEDURE process_employee ( IS employee_id_in IN INTEGER) l_name VARCHAR2 (100); BEGIN SELECT last_name END; INTO l_name FROM employees WHERE employee_id = employee_id_in; Instead of writing this query each time, I suggest writing a function once that contains this query and returns the desired value. Then you can call the function as needed. Assuming that I have created a package named EMPLOYEES_API with a function named LAST_NAME, the above procedure can be changed to PROCEDURE process_employee ( IS employee_id_in IN INTEGER) l_name VARCHAR2 (100); BEGIN l_name := END; employees_api. last_name (employee_id_in); Now if I ever need to change the query for any reason (such as to take advantage of Oracle Database 11g s function result cache feature), I ll be able to make the change in one place, rather than having to find all occurrences of the query in my application code. So suppose my development team has added this best practice to its coding standards: the only PL/SQL program units that should contain SQL statements are packages that end with the suffix _API. I can then write a query against USER_DEPENDENCIES that identifies all program units that violate this rule: SELECT name, TYPE, referenced_owner, referenced_name FROM user_dependencies WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', The Data Dictionary: Make Views Work for You 61

62 'TRIGGER', 'TYPE') AND referenced_type = 'TABLE' AND name NOT LIKE '%\_API' ESCAPE '\' ORDER BY name, referenced_owner, referenced_name Analyze Argument Information USER_ARGUMENTS is a very useful view for PL/SQL programmers. It contains information about the arguments (also known as parameters) of each of the stored programs in your schema. It offers, simultaneously, a wealth of nicely parsed information and a complex structure. Key columns are OBJECT_NAME: The name of the procedure or function PACKAGE_NAME: The name of the package in which the procedure or function is defined ARGUMENT_NAME: The name of the argument POSITION: The position of the argument in the parameter list (if 0, this is the RETURN clause of a function) IN_OUT: The mode of the argument IN, OUT, or IN OUT DATA_TYPE: The datatype of the argument DATA_LEVEL: The nesting depth of the argument for composite types (for example, if one of your arguments datatypes is a record, USER_ARGUMENTS will have a row for this argument with a DATA_LEVEL of 0 and then a row for each field in the record with a DATA_LEVEL of 1) Here are some examples of queries against USER_ARGUMENTS. Find all programs that have an argument of type LONG. This is the datatype that was used to store large strings (more than 4,000 characters) in past versions of Oracle Database. Now the database uses large object types such as character large object (CLOB). Oracle recommends that any usages of LONG be converted to CLOB. USER_ARGUMENTS makes it easy to find all such usages in parameter lists: SELECT object_name, package_name, argument_name FROM user_arguments WHERE data_type = LONG Find all functions that have an OUT or an IN OUT argument. A recommendation you will hear from many programming experts is that functions should contain only IN arguments. A function with an OUT or an IN OUT argument cannot be called inside a SQL statement, and it cannot be used in a function-based index. If you need to return multiple pieces of information, use a procedure or return a record. Listing 1 demonstrates a query that will identify all functions defined in packages that violate this best practice. Code Listing 1: Find functions that have an OUT or an IN OUT argument 1 SELECT ua.object_name, Next Steps DOWNLOAD Oracle Database 11g TEST your PL/SQL knowledge READ PL/SQL 101, Parts 1-9 READ more about READ more about the data dictionary The Data Dictionary: Make Views Work for You 62

63 2 ua.package_name, 3 ua.argument_name, 4 ua.in_out 5 FROM (SELECT * 6 FROM user_arguments 7 WHERE position = 0) funcs, 8 user_arguments ua 9 WHERE ua.in_out IN ('OUT', 'IN OUT') 10 AND ua.position > 0 11 AND ua.data_level = 0 12 AND funcs.object_name = ua.object_name 13 AND funcs.package_name = ua.package_name 14 AND ( funcs.overload = ua.overload 15 OR (funcs.overload IS NULL 16 AND ua.overload IS NULL)) Lines Description 5 7 I use an inline view in the FROM clause to identify all those rows in USER_ARGUMENTS that are RETURN clauses (and therefore identify functions) I look for OUT or IN OUT arguments that are not in RETURN clauses and are not nested information, such as fields of a record argument. 12 I use this rather lengthy join condition between the inline view (abbreviated as funcs ) and 16 USER_ARGUMENTS. The object names and package names must match, and the overload value must be the same or both must be NULL. The overload column is not NULL if the package has two or more subprograms with the same name. It s a Gold Mine in There This article merely scratches the surface of the application information that can be mined from the data dictionary views in Oracle Database. PL/SQL editors such as Oracle SQL Developer provide user interfaces to many of these views, making it easier to browse their contents. A Better USER_SOURCE You can execute queries against USER_SOURCE to check for the presence or absence of certain strings and thus perform some fairly simplistic quality assurance checks of your code. But Oracle offers a much more powerful means of analyzing your source code in Oracle Database 11g: with PL/Scope. PL/Scope is a tool invoked by the PL/SQL compiler to collect information about all the identifiers (variables, procedures, functions, types, and so on) in your PL/SQL program unit and make it available through the USER_IDENTIFIERS view. It makes it relatively easy to get answers to questions that would otherwise require you to parse a PL/SQL program unit and then analyze the parse tree. The Data Dictionary: Make Views Work for You 63

64 Here s one example: My manager has asked me to remove from our programs any variables, constants, exceptions, and the like that are declared but never used. Finding all candidates for removal by simply searching code would be both timeconsuming and error-prone. With PL/Scope, it s easy. USER_IDENTIFIERS contains a row for each declaration of an identifier. It may also contain other rows for usages of that identifier: a reference to it or a line of code that changes its value. So a MINUS operation between these two sets of rows will leave us with all those identifiers that are declared but never referenced or used. Here s the query for exceptions: WITH subprograms_with_exception AS (SELECT DISTINCT owner, object_name, object_type, name FROM all_identifiers has_exc WHERE has_exc.owner = USER AND has_exc.usage = 'DECLARATION' AND has_exc.type = 'EXCEPTION'), subprograms_with_raise_handle SELECT * AS (SELECT DISTINCT owner, object_name, object_type, name FROM all_identifiers with_rh WHERE with_rh.owner = USER AND with_rh.usage = 'REFERENCE' AND with_rh.type = 'EXCEPTION') FROM subprograms_with_exception MINUS SELECT * FROM subprograms_with_raise_handle PL/Scope is a powerful, flexible utility that can have a big impact on your ability to analyze code and identify ways to improve it. I wrote at more length about PL/Scope in 2010 in Zoom In on Your Code. Take the Challenge Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in it. The quiz appears below and also at PL/SQL Challenge, a Website that offers online quizzes on the PL/SQL language as well as SQL and Oracle Application Express. The Data Dictionary: Make Views Work for You 64

65 Here is your quiz for this article: Assume that all the packages in my schema contain at least one subprogram (procedure or function). Which of these queries will display the names of all the packages in my schema? a. SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE' ORDER BY object_name / b. SELECT package_name FROM user_procedures WHERE package_name IS NOT NULL ORDER BY package_name / c. SELECT DISTINCT object_name FROM user_procedures WHERE procedure_name IS NOT NULL ORDER BY object_name / d. SELECT DISTINCT name FROM user_dependencies WHERE TYPE = 'PACKAGE' / Steven Feuerstein ([email protected]) is Quest Software s PL/SQL evangelist. He has published 10 books on Oracle PL/SQL (O Reilly Media) and is an Oracle ACE Director. More information is available at stevenfeuerstein.com. The Data Dictionary: Make Views Work for You 65

66 Send us your comments The Data Dictionary: Make Views Work for You 66

67 ORA-01039: 사용되지않는오류 :47 Toad 에서 plan 을뜰때다음과같은오류가나옵니다. ORA-01039: 사용되지않는오류 물론 utlxplan.sql 를실행해놓았구요... 어제는 ' 지정된플랜테이블이없습니다 ' 였든가.. 이런메시지가떳었구요. 오늘은위와같은에러가보이면서플랜을볼수가없네요... 참고로 W2K 에서돌아가는 9i 입니다. 답변부탁합니다. 이글에대한댓글이총 1건있습니다. 권한이없어서그런에러가나오는겁니다.. 플랜을떠서보려고하면기본적으로가져야할권한이있습니다... v_$sesstat v_$statname v_$session 이세가지의권한을유저에게줘보세요... 팁한가지말씀드리면, 추후에도이와같은일을반복해야할경우가있습니다... 그래서그냥이세가지를 Grant 하기보다는위, 세가지권한을가진 Role을만들고, 그 Role을사용자에게주면관리면에서편할꺼에요...^^ ORA-01039: 사용되지않는오류 67

68 EXPLAIN PLAN( 실행계획 ) 이란? :34 EXPLAIN PLAN( 실행계획 ) 이란? SQL 문의엑세스경로를확인하고튜닝할수있도록 SQL 문을분석하고해석하여실행계획을수립한후실행계획을 PLAN_TABLE 에저장하도록해주는명령이다. SQL Trace 없이사용할수있다. ORACLE_HOME/rdbms/admin/utlxplan.sql 실행하여 PLAN_TABLE 을생성한다. statement_id 컬럼에인덱스를생성해주는것이수행속도를향상시켜주고 id 값이중복되는것을막을수있다. 문법 - statement_id = 'identifiedr' : 1-30 자로부여할수있는해당실행문의제목 - INTO tablename : 출력문을저장하는테이블명 PLAN_TABLE 을사용하지않을경우사용 - FOR statement : 실행계획을수립하고자하는 SQL 문 (SELECT, INSERT, DELETE, UPDATE) 1. Plan_table 생성 Explain plan을 sql에포함해서수행하면옵티마이저가실행계획까지만수립하여 plan_table에저장한다. 이 table을생성하기위한 script는 $ORACLE_HOME/rdbms/admin/utlxplan.sql 이다. 2. Index 생성 테이블생성후수행속도향상과동일한 statement_id 가생성되는것을방지하기위해 index 를생성한다. SQL> CREATE UNIQUE INDEX plan_index ON PLAN_TABLE(statement_id,id); 3. SQL 문사용 FOR 문장다음에확인하고자하는 sql 문을입력실행한다. SQL> EXPLAIN PLAN SET STATEMENT_ID='a1' FOR SELECT /*+ index(emp emp_pk) */ * FROM emp WHERE empno > 0; 4. PLAN_TABLE 을 SELECT 하는 SQL 문을실행 EXPLAIN PLAN( 실행계획 ) 이란? 68

69 SQL> SELECT LPAD(operation,LENGTH(operation)+ 2*(LEVEL-1)) DECODE(id,0,'cost estimate:' DECODE(position,'0','N/A',position),null) ' ' options DECODE(object_name,null,null,':') RPAD(object_owner, LENGTH(object_name)+1,',') object_name DECODE (object_type,'unique','(u) ','NON_UNIQUE','(NU)',null) DECODE(object_instance,null,null,'(' object_instance ')') "Explain Plan" FROM PLAN_TABLE START WITH ID= 0 and STATEMENT_ID = '&&id' CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id' -- a1 을입력하면아래와같은실행계획을볼수있다. Explain Plan SELECT STATEMENTcost estimate:1 TABLE ACCESS BY INDEX ROWID:TESTEMP(1) INDEX RANGE SCAN:TEST,,,EMP_PK PLAN_TABLE 컬럼설명 컬럼명 STATEMENT_ID TIMESTAMP REMARKS 설명 EXPLAIN PLAN문에서사용자가지정한제목실행계획이수립된날짜와시간사용자가부여한주석 (COMMENT) OPERATION 아래표에자세히설명되어있습니다. OPTIONS 아래표에자세히설명되어있습니다. OBJECT_NODE OBJECT_OWNER OBJECT_NAME OBJECT_INSTANCE OBJECT_TYPE ID PARENT_ID POSITION OTHER 사용한데이터베이스링크해당라인의오브젝트를생성한사용자그룹테이블이나인덱스, 클러스터등의이름 SQL의 FROM절에기술된오브젝트를좌에서우로부여한번호오브젝트의종류 ( 예 non-unique index) 수립된각실행단계에붙여진일련번호해당 ID의부모가가진 ID 같은부모 ID를가지고있는자식 ID간의처리순서다른필요한텍스트를지정하기위한필트 OPERATION 의종류와 OPTIONS 에대한설명 OPERATION( 기능 ) OPTIONS( 옵션 ) 설명 AGGREGATE GROUP BY 그룹함수를사용하여하나의로우가추출되도록하는처리 ( 버전 7에서 만표시됨 ) AND-EQUAL CONNECT BY 인덱스머지를이용하는경우 CONNECT BY 를사용하여트리구조로전개 EXPLAIN PLAN( 실행계획 ) 이란? 69

70 CONCATENATION COUNTING FILTER 단위액세스에서추출한로우들의합집합을생성 테이블의로우스를센다 선택된로우에대해서다른집합에대응되는로우가있다면제거하는작업 FIRST ROW 조회로우중에첫번째로우만추출한다. FOR UPDATE 선택된로우에 LOCK을지정한다. INDEX INQUE UNIQUE인덱스를사용한다. ( 단한개의로우추출 ) RANGE SCAN NON-UNIQUE 한인덱스를사용한다.( 한개이상의로우 ) RANGE SCAN DESCENDING RANGE SCAN 하고동일하지만역순으로로우를추출한다. NTERSECTION 교집합의로우를추출한다. MERGE JOIN 먼저자신이ㅡ조건만으로액세스한후각각을 SORT 하여 MERGE 해가는조인 OUTER 위와동일하지만 outer join 을사용한다 MINUS MINUS 함수를사용한다. NESTED LOOPS 먼저어떤드라이빙테이블의로우를액세스한후그결과를이용해다른테이블을연결하는조인 OUTER 위와동일하지만 outer join 을사용한다. PROJECTION REMOTE 내부적인처리의일종 다른분산데이터베이스에있는오브젝트를추출하기위해 DATABASE LINK 를사용하는경우 SEQUENCE 시퀀스를액세스한다. SORT UNIQUE 같은로우를제거하기위한 SORT GROUP BY JOIN ORDER BY 액세스결과를 GROUP BY 하기위한 SORT MERGE JOIN 을하기위한 SORT ORDER BY 를위한 SORT TABLE ACCESS FULL 전체테이블을스캔한다. CLUSTER CLUSTER를액세스한다. HASH 키값에대한해쉬알고리즘을사용 ( 버전 7에서만 ) BY ROWID ROWID를이용하여테이블을추출한다. UNION 두집합의합집합을구한다.( 중복없음 ) 항상전체범위처리를한다. UNION ALL VIEW 두집합의합집합을구한다.( 중복가능 ) UNION 과는다르게부분범위처리를한다. 어떤처리에의해생성되는가상의집합에서추출한다.( 주로서브쿼리에의해수행된결과 ) EXPLAIN PLAN( 실행계획 ) 이란? 70

71 문서에대하여 - 강좌 URL : - 이문서를다른블로그나홈페이지에게재하실경우에는출처를꼭밝혀주시면고맙겠습니다.~^^ - 오라클클럽의모든강좌는크리에이티브커먼즈의저작자표시 - 비영리 - 동일조건변경허락 (BY-NC-SA) 라이선스에따라자유롭게수있습니다. EXPLAIN PLAN( 실행계획 ) 이란? 71

72 [ 오라클 ] 특정사용자에주어진권한보기 :36 SELECT USERNAME, ROLENAME, PRIVILEGE FROM (SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME) username, SUBSTR(U2.NAME,1,20) rolename, SUBSTR(SPM.NAME,1,27) PRIVILEGE FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1, SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM WHERE SA1.GRANTEE# = U1.USER# AND SA1.PRIVILEGE# = U2.USER# AND U2.USER# = SA2.GRANTEE# AND SA2.PRIVILEGE# = SPM.PRIVILEGE UNION SELECT U.NAME username, NULL rolename, SUBSTR(SPM.NAME,1,27) privilege FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U WHERE SA.GRANTEE#=U.USER# AND SA.PRIVILEGE#=SPM.PRIVILEGE) WHERE USERNAME = 'KDY'; 'KDY' ===>> 사용자 ID 이다. 결과는 : USERNAME ROLENAME PRIVILEGE KDY RESOURCE CREATE CLUSTER KDY RESOURCE CREATE INDEXTYPE KDY RESOURCE CREATE OPERATOR KDY RESOURCE CREATE PROCEDURE KDY RESOURCE CREATE SEQUENCE KDY RESOURCE CREATE TABLE KDY RESOURCE CREATE TRIGGER [ 오라클 ] 특정사용자에주어진권한보기 72

73 KDY RESOURCE CREATE TYPE KDY CREATE SESSION KDY UNLIMITED TABLESPACE 10 rows selected 이런식으로나온다. 주어진 ROLE 과 PRIVILEGE 가나온다. ========================================================================= 부여된권한확인 부여받거나부여한권한을확인하기위해 Dictionary 뷰를액세스한다. Dictionary뷰설명 ROLE_SYS_PRIVS role에부여된시스템권한 ROLE_TAB_PRIVS role에부여된테이블권한 USER_ROLE_PRIVS 사용자가액세스할수있는 role USER_TAB_PRIVS_MADE 사용자가부여한객체권한 USER_TAB_PRIVS_RECD 사용자에게부여된객체권한 USER_COL_PRIVS_RECD 특정 Column에대하여사용자에게부여된객체권한 [ 오라클 ] 특정사용자에주어진권한보기 73

74 오라클특수문자데이터치환하기 :15 with a as ( select '@_#$ $%^&*()0afdfZ ' text from dual union all select Z~+129@$#8984' text from dual union all select '@_#$#$%^&*() = ?fd~fZ$% ' text from dual union all select '@_# 가나다라 ' text from dual ) select --REGEXP_REPLACE(text,'1','!') regexp_replace(regexp_replace(trim(lower(text)),'[^a-z,a-z,0-9, ㄱ-ㅎ, 가-힐 ]',''),'[[:space:]]{1,}',' ') from a 텍스트데이터중에서특수문자들어있는거몽땅없애기 오라클특수문자데이터치환하기 74

75 데이터중에서최신데이터뽑아오기 :27 SELECT * FROM AAA WHERE ROWID = (SELECT MAX(ROWID) FROM AAA GROUP BY COL1) 데이터중에서최신데이터뽑아오기 75

76 업데이트힌트 ( 가변열오류무시 ) :57 UPDATE /*+ bypass_ujvc */ ( SELECT A.GV_DT, B.ACPT_DT FROM aaa A, bbb B WHERE A.pk = B.pk ) SET GV_DT = ACPT_DT 업데이트힌트 ( 가변열오류무시 ) 76

77 오라클레코드변수사용예 :15 CREATE OR REPLACE PROCEDURE AFF.SP_RECORD_TEST IS TYPE ACEC001TYP IS RECORD( MEMB_SRNUM VARCHAR2(10),KOR_NM VARCHAR2(40) ) ; TYPE ARR_NO IS TABLE OF ACEC001TYP; V_ARR_NO ARR_NO := ARR_NO(); BEGIN -- SELECT MEMB_SRNUM,KOR_NM BULK COLLECT INTO V_ARR_NO FROM ACEC350 WHERE ROWNUM < 11; V_ARR_NO.EXTEND(); V_ARR_NO(1).MEMB_SRNUM := ' 회원 1'; V_ARR_NO(1).KOR_NM := ' 정윤구 '; V_ARR_NO.EXTEND(); V_ARR_NO(2).MEMB_SRNUM := ' 회원 2'; V_ARR_NO(2).KOR_NM := ' 김명수 '; -- SELECT 'DKDKD' INTO V_ARR_NO(1).KOR_NM FROM DUAL; FOR i IN V_ARR_NO.FIRST..V_ARR_NO.LAST LOOP DBMS_OUTPUT.PUT_LINE(V_ARR_NO(i).MEMB_SRNUM ' '); DBMS_OUTPUT.PUT_LINE(V_ARR_NO(i).KOR_NM ' '); END LOOP; 오라클레코드변수사용예 77

78 DBMS_OUTPUT.PUT_LINE(V_ARR_NO.COUNT); END SP_RECORD_TEST; 오라클레코드변수사용예 78

79 오라클 10g XE...http port 바꾸기 :29 바꿀때.. begin dbms_xdb.sethttpport('80'); bms_xdb.setftpport('2100'); end; 바꾸고나서확인.. select dbms_xdb.gethttpport as "HTTP-Port", dbms_xdb.getftpport as "FTP-Port" from dual; 오라클 10g XE...http port 바꾸기 79

80 오라클커서 Attribute Values :17 오라클커서 Attribute Values 80

81 오라클게시자문제해결 :23 Oracle 게시자문제해결이항목에서는 Oracle 게시자를구성및사용할때발생할수있는여러가지문제를나열합니다. Oracle 클라이언트및네트워킹소프트웨어와관련된오류가발생했습니다. Microsoft SQL Server가배포자에서실행되는계정에는 Oracle 클라이언트네트워킹소프트웨어가설치된디렉터리및모든하위디렉터리에대한읽기및실행권한이부여되어야합니다. 사용권한이부여되지않거나 Oracle 클라이언트구성요소가제대로설치되지않으면다음과같은오류메시지가표시됩니다. "[Microsoft OLE DB Provider for Oracle] 로서버에연결하지못했습니다. Oracle 클라이언트및네트워킹구성요소를찾을수없습니다. 이러한구성요소는 Oracle 버전 이상의클라이언트소프트웨어설치의일부로 Oracle사에서제공합니다. 이러한구성요소를설치해야공급자를사용할수있습니다." 해당 Oracle 클라이언트가배포자에설치된경우클라이언트설치가완료된후에 SQL Server가중지되었다가다시시작되었는지확인합니다. 이렇게해야 SQL Server가클라이언트구성요소를인식할수있습니다. 권한을부여하고구성요소를올바르게설치한다음에도이오류가계속발생하면 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI 의레지스트리설정이올바른지확인하십시오. Oracle 10g의경우올바른설정은다음과같습니다. OracleOciLib = oci.dll OracleSqlLib = orasql10.dll OracleXaLib = oraclient10.dll Oracle 9i의경우올바른설정은다음과같습니다. OracleOciLib = oci.dll OracleSqlLib = orasql9.dll OracleXaLib = oraclient9.dll SQL Server 배포자가 Oracle 데이터베이스인스턴스에연결할수없습니다. SQL Server 배포자가 Oracle 게시자에연결할수없는경우다음사항을확인하십시오. 필요한 Oracle 소프트웨어가배포자에설치되어있어야합니다. Oracle 데이터베이스가온라인상태이고 SQL*Plus 와같은도구를사용하여이데이터베이스에연결할수있어야합니다. 복제에서 Oracle 게시자연결에사용하는로그인에충분한권한이있어야합니다. 자세한내용은 Oracle 게시자구성을참조하십시오. Oracle 게시자를구성하는동안정의된 TNS 이름이 tnsnames.ora 파일에나열되어있어야합니다. 올바른 Oracle 홈및경로를사용해야합니다. SQL Server 배포자에하나의 Oracle 바이너리집합만설치한경우에도 Oracle 홈과관련된환경변수를제대로설정해야합니다. 환경변수값을변경한경우 SQL Server를중지하고다시시작하여변경내용을적용해야합니다. 연결을구성및테스트하는방법은 Oracle 게시자구성의 "SQL Server 배포자에 Oracle 클라이언트네트워킹소프트웨어설치및구성 " 을참조하십시오. Oracle 게시자가다른배포자와연결되어있습니다. 오라클게시자문제해결. 81

82 Oracle 게시자는한 SQL Server 배포자에만연결할수있습니다. Oracle 게시자에배포자가연결되어있는경우다른배포자를사용하려면먼저기존배포자를삭제해야합니다. 기존배포자를먼저삭제하지않으면다음오류메시지중하나가표시됩니다. "'<SQLServerDistributorName>' 을 ( 를 ) 배포자로사용하도록 Oracle 서버인스턴스 '<OraclePublisherName>' 이 ( 가 ) 구성되어있습니다. '<NewSQLServerDistributorName>' 을 ( 를 ) 배포자로사용하려면 Oracle 서버인스턴스의현재복제구성을제거하여해당서버인스턴스의모든게시를삭제해야합니다." "Oracle 서버 '<OracleServerName>' 은 ( 는 ) 배포자 <SQLServerDistributorName>.<DistributionDatabaseName> 에서이미게시자 '<OraclePublisherName>' 으로정의되어있습니다. 게시자를삭제하거나공용동의어 '<SynonymName>' 을 ( 를 ) 삭제하고다시만드십시오." Oracle 게시자를삭제하면 Oracle 데이터베이스의복제개체가자동으로정리됩니다. 그러나어떤경우에는 Oracle 복제개체를수동으로정리해야합니다. 복제에의해생성된 Oracle 복제개체를수동으로정리하려면다음을수행하십시오. 1. DBA 권한으로 Oracle 게시자에연결합니다. 2. SQL 명령 DROP PUBLIC SYNONYM MSSQLSERVERDISTRIBUTOR; 를실행합니다. 3. SQL 명령 DROP USER <replication_administrative_user_schema> CASCADE; 를실행합니다. PRIMARY KEY 부재와관련된 SQL Server 오류 21663이발생했습니다. 트랜잭션게시의아티클에는올바른기본키가있어야합니다. 아티클에올바른기본키가없으면아티클을추가할때다음오류메시지가표시됩니다. " 원본테이블 [<TableOwner>].[<TableName>] 에대해올바른기본키를찾을수없습니다." 기본키요구사항에대한자세한내용은 Oracle 게시자에대한디자인고려사항및제한사항항목의 " 고유인덱스및 UNIQUE 제약조건 " 섹션을참조하십시오. 연결된서버로의중복로그인과관련된 SQL Server 오류 21642가발생했습니다. Oracle 게시자를처음구성하면게시자와배포자간연결에대해연결된서버항목이생성됩니다. 연결된서버의이름은 Oracle TNS 서비스이름과동일합니다. 이름이동일한연결된서버를만들면다음오류메시지가표시됩니다. " 유형이다른게시자에는연결된서버가필요합니다. 이름이 '<LinkedServerName>' 인연결된서버가이미있습니다. 연결된서버를제거하거나다른게시자이름을선택하십시오." 이오류는연결된서버를직접만들거나이전에삭제한 Oracle 게시자와 SQL Server 배포자간관계를다시구성하려고하는경우발생할수있습니다. 게시자를다시구성하는동안이오류가표시되면 sp_dropserver(transact-sql) 를사용하여연결된서버를삭제하십시오. 연결된서버연결을통해 Oracle 게시자에연결하려면다른 TNS 서비스이름을만든다음이이름을사용하여 sp_addlinkedserver(transact-sql) 를호출합니다. TNS 서비스이름을만드는방법은 Oracle 설명서를참조하십시오. 중첩트랜잭션과관련된 SQL Server 오류 7395가발생했습니다. Oracle 게시자에대한연결된서버연결에서중첩트랜잭션을실행하면다음오류가표시됩니다. " 연결된서버 '<ServerName>' 의 OLE DB 공급자 'MSDAORA' 에대해중첩트랜잭션을시작할수없습니다. XACT_ABORT 옵션이 OFF 로설정되어있으므로중첩트랜잭션이필요합니다." 복제저장프로시저를사용하여 Oracle 게시자를구성또는유지관리하는경우명시적트랜잭션에프로시저를래핑하지마십시오. SQL Server 오류 21617이발생합니다. Oracle 게시는 Oracle 응용프로그램 SQL*PLUS를사용하여게시자지원코드패키지를 Oracle 데이터베이스로다운로드합니다. SQL Server는 Oracle 게시자를구성하기전에배포자에서시스템경로로 SQL*PLUS에액세스할수있는지확인합니다. SQL*PLUS를로드할수없으면다음오류메시지가표시됩니다. "SQL*PLUS를실행할수없습니다. 배포자에최신버전의 Oracle 클라이언트코드가설치되었는지확인하십시오." 오라클게시자문제해결. 82

83 배포자에서 SQL*PLUS를찾으십시오. Oracle 10g 클라이언트설치의경우이실행파일의이름은 sqlplus.exe이며일반적으로 %ORACLE_HOME%/bin에설치됩니다. SQL*PLUS의경로가시스템경로에표시되는지확인하려면시스템변수 Path 값을검사하십시오. 1. 내컴퓨터를마우스오른쪽단추로클릭한다음속성을클릭합니다. 2. 고급탭을클릭한다음환경변수를클릭합니다. 3. 환경변수대화상자의시스템변수목록에서 Path 변수를선택한다음편집을클릭합니다. 4. 시스템변수편집대화상자에서 sqlplus.exe 가들어있는폴더의경로가변수값텍스트상자에나타나지않으면해당문자열을편집하여추가합니다. 5. 열려있는각대화상자에서확인을클릭하여변경내용을저장하고종료합니다. 배포자에서 sqlplus.exe를찾을수없으면배포자에최신버전의 Oracle 클라이언트소프트웨어를설치합니다. 자세한내용은 Oracle 게시자구성을참조하십시오. SQL Server 오류 21620이발생합니다. Oracle 데이터베이스 8.1 이전버전에연결하는경우 Oracle 게시를위해배포자에설치된 Oracle 클라이언트소프트웨어는버전 9여야합니다. Oracle 데이터베이스 8.1 이후버전에연결하는경우에는버전 10 이상의 Oracle 클라이언트소프트웨어를사용하는것이좋습니다. Oracle 게시는 Oracle 게시자를구성하기전에배포자에서시스템경로로액세스할수있는 SQL*PLUS 가버전 9 이상인지확인합니다. 버전 9 이상이아니면다음오류메시지가표시됩니다. " 시스템경로변수로액세스할수있는 SQL*PLUS 버전이최신버전이아니어서 Oracle 게시를지원할수없습니다. 배포자에최신버전의 Oracle 클라이언트코드가설치되었는지확인하십시오." 여러버전의 Oracle 클라이언트소프트웨어가배포자에설치되어있을경우최신버전이버전 9 이상이며시스템경로변수가먼저이버전을가리키는지확인하십시오. 최신버전이먼저표시되기만하면다른버전에대한참조도표시될수있습니다. 시스템경로변수를편집하는방법은이항목의앞부분에있는 "SQL Server 오류 21617이발생합니다 " 섹션을참조하십시오. SQL Server 오류 또는오류 21629가발생합니다. 64비트배포자의경우 Oracle 게시는 Oracle OLEDB 공급자 (OraOLEDB.Oracle) 를사용합니다. 배포자에 Oracle OLEDB 공급자가설치및등록되었는지확인하십시오. 공급자가설치및등록되어있지않으면다음오류메시지중하나또는둘다가표시됩니다. " 배포자 '%s' 에서 Oracle OLEDB 공급자 OraOLEDB.Oracle을찾을수없습니다. 배포자에최신버전의 Oracle OLEDB 공급자가설치및등록되었는지확인하십시오." "Oracle OLEDB 공급자 OraOLEDB.Oracle이등록되었음을나타내는 CLSID 레지스트리키가배포자에없습니다. 배포자에 Oracle OLEDB 공급자가설치및등록되었는지확인하십시오." Oracle 클라이언트소프트웨어버전 10g를사용하는경우공급자는 OraOLEDB10.dll입니다. 버전 9i의경우에는 OraOLEDB.dll입니다. 공급자는 %ORACLE_HOME%\BIN에설치됩니다 ( 예 : C:\oracle\product\10.1.0\Client_1\bin). 배포자에 Oracle OLEDB 공급자가설치되어있지않으면 Oracle에서제공한 Oracle 클라이언트소프트웨어설치디스크에서설치합니다. 자세한내용은 Oracle 게시자구성을참조하십시오. Oracle OLEDB 공급자가설치되어있으면등록되었는지확인하십시오. 공급자 DLL을등록하려면 DLL이설치된디렉터리에서다음명령을실행한다음 SQL Server 인스턴스를중지했다가다시시작합니다. 1. regsvr32 OraOLEDB10.dll 또는 regsvr32 OraOLEDB.dll SQL Server 오류 또는오류 이발생했습니다. Oracle 게시환경이올바로구성되었는지확인하기위해 SQL Server 는구성중에지정한로그인자격증명을사용하여 Oracle 게시자에 연결합니다. SQL Server 배포자가 Oracle 게시자에연결할수없는경우다음오류메시지중하나가표시됩니다. 오라클게시자문제해결. 83

84 "Oracle OLEDB 공급자 OraOLEDB.Oracle을사용하여 Oracle 데이터베이스서버 '%s' 에연결할수없습니다." "Microsoft OLEDB 공급자 MSDAORA를사용하여 Oracle 데이터베이스서버 '%s' 에연결할수없습니다." 이오류메시지가표시되면 Oracle 게시자구성중에지정한로그인및암호로직접 SQL*PLUS 를실행하여 Oracle 데이터베이스에대한연결을확인합니다. 자세한내용은이항목의앞부분에있는 "SQL Server 배포자가 Oracle 데이터베이스인스턴스에연결할수없습니다 " 섹션을참조하십시오. SQL Server 오류 21628이발생했습니다. 64비트배포자의경우 Oracle 게시는 Oracle OLEDB 공급자 (OraOLEDB.Oracle) 를사용합니다. SQL Server는 Oracle 공급자가 SQL Server 프로세스에서실행되도록허용하기위해레지스트리항목을만듭니다. 이레지스트리항목을읽거나쓰는데문제가있으면다음오류메시지가표시됩니다. "Oracle OLEDB 공급자 OraOLEDB.Oracle이 SQL Server 프로세스에서실행되도록허용하기위해배포자 '%s' 의레지스트리를업데이트할수없습니다. 현재로그인에 SQL Server 소유레지스트리키를수정할권한이부여되었는지확인하십시오." Oracle 게시는레지스트리항목이있어야하며 64비트배포자의경우 1로설정되어야합니다. 항목이없으면 SQL Server에서항목을만듭니다. 항목이있지만 0으로설정된경우에는설정이변경되지않으며 Oracle 게시자구성이실패합니다. 레지스트리설정을보고수정하려면 1. 시작을클릭한다음실행을클릭합니다. 2. 실행대화상자에 regedit를입력한다음확인을클릭합니다. 3. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers로이동합니다. Providers 아래에는 OraOLEDB.Oracle 폴더가있어야하며이폴더내에이름이 AllowInProcess이고값이 1인 DWORD 값이있어야합니다. 4. AllowInProcess가 0으로설정되어있으면레지스트리항목을 1로업데이트합니다. 1. 항목을마우스오른쪽단추로클릭한다음수정을클릭합니다. 2. 문자열편집대화상자의값데이터필드에 1을입력합니다. SQL Server 오류 21684가발생했습니다. 관리사용자계정에충분한권한이없으면다음오류메시지가표시됩니다. Oracle 게시자 '%s' 의관리자로그인과연관된사용권한이충분하지않습니다. 사용자에게부여된사용권한을확인하려면 SELECT * from session_privs. 출력은다음과같은형태가됩니다. PRIVILEGE CREATE SESSION CREATE TABLE CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM CREATE VIEW CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER 복제사용자스키마에대한사용권한문제가발생했습니다. 복제사용자스키마에는 Oracle 게시자구성의 " 수동으로사용자스키마만들기 " 에설명된권한이있어야합니다. Oracle 오류 ORA 복제는게시에아티클을추가할때 Oracle 게시자에커서를사용합니다. 이때게시자에서사용할수있는최대커서수가초과되어다음오 오라클게시자문제해결. 84

85 류가발생할수있습니다. "ORA-01000: maximum open cursors exceeded" 이문제를방지하려면 Oracle 데이터베이스에서 max_open_cursors 설정을충분히높은수 ( 최소 1000) 로설정하십시오. 이설정에대한자세한내용은 Oracle 설명서를참조하십시오. Oracle 오류 ORA 다음 Oracle 데이터베이스오류는스냅숏복제와는관계가없으며 Oracle에서일관된읽기를수행할수있는데이터뷰를생성하는방식과관련되어있습니다. "ORA-01555: Snapshot too old" Oracle에서는롤백세그먼트라는개체를사용하여 SQL 문이실행된시점에서일관된읽기를수행할수있는데이터뷰를생성합니다. 다른동시세션에서롤백정보를덮어쓰는경우 "snapshot too old" 오류가발생할수있습니다. Oracle 9i 이전에서는이오류의발생빈도를줄이기위해롤백세그먼트의크기및 / 또는수를늘리고큰트랜잭션을특정롤백세그먼트에할당하는방법이권장되었습니다. Oracle 9i에서는롤백세그먼트를대체하는 UNDO 테이블스페이스개념을도입했습니다. Oracle 9i에서 "snapshot too old" 오류를방지하려면다음을수행하십시오. 사용가능한공간을적절히지정한다음 UNDO 테이블스페이스를만듭니다. 테이블스페이스에보존이보장되는기간을설정합니다 (Oracle 10G 이상 ). Oracle 초기화매개변수인 UNDO_MANAGEMENT 및 UNDO_RETENTION을구성합니다. "snapshot too old" 오류방지방법에대한자세한내용은 Oracle 설명서를참조하십시오. Oracle 오류 ORA 테이블에 BFILE 열이포함되어있는경우에는이열의데이터가파일시스템에저장됩니다. 다음구문을사용하여복제관리사용자계정에데이터가저장되어있는디렉터리에대한액세스권한을부여해야합니다. GRANT READ ON DIRECTORY <directory_name> TO <replication_administrative_user_schema> 액세스권한을부여하지않으면로그판독기에이전트에서다음오류를표시합니다. "ORA-22285: non-existent directory or file for FILEOPEN operation" 게시자를다시구성해야하는변경내용이적용되었습니다. 복제메타데이터테이블또는프로시저를변경하면게시자를삭제하고다시구성해야합니다. 게시자를다시구성하려면게시자를삭제하고 SQL Server Management Studio, Transact-SQL 또는 RMO를사용하여다시구성해야합니다. 게시자구성방법은 Oracle 게시자구성을참조하십시오. Oracle 게시자를삭제하려면 (SQL Server Management Studio) 1. SQL Server Management Studio에서 Oracle 게시자에대한배포자에연결한다음해당서버노드를확장합니다. 2. 복제를마우스오른쪽단추로클릭한다음배포자속성을클릭합니다. 3. 배포자속성대화상자의게시자페이지에서 Oracle 게시자에대한확인란의선택을취소합니다. 4. 확인을클릭합니다. Transact-SQL을사용하여 Oracle 게시자를삭제하려면 sp_dropdistpublisher 를실행합니다. 자세한내용은 sp_dropdistpublisher(transact-sql) 를참조하십시오. 오라클게시자문제해결. 85

86 아이바티스부등호처리 :40 <![CDATA[ SELECT * FROM BOARD WHERE WRITE_TIME >= DATE_ADD(NOW(), INTERVAL -1 DAY) ORDER BY WRITE_TIME DESC LIMIT 7 ]]> 아이바티스부등호처리 86

87 오라클더미행만들기 :59 select rownum,level from dual connect by level <= 20 오라클더미행만들기 87

88 ORACLE ANALYZE 하는법 :53 오라클을통해작업할시 insert 시점에서정상적으로인덱싱이되지않는경우가발생한다. 특히결합인덱스를많이사용하고있는경우발생될확률이높다. 이런경우오라클의 Analyzed 를통해서해결이가능하고 어느정도의실행속도를향상시킬수있다. ( 실제오라클사에서도 3 개월에한번씩은 Analyze 를실행하라권고하고있다.) [Analyzed 확인방법 ] select table_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_tables select index_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_indexes ex) select table_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_tables; TABLE_NAME NUM_ROWS TO_CHAR( ABS_TYPE ANNIVERS APPRFLDRHISTORY APPRFOLDER APPRFOLDER_ERR APPRFORM USR_INFO_ADMIN VAR_DEPT_INFO VIEW_TYPE WASTEBOX ZIP_CODE rows selected. 참고 : desc user_tables 에서보통 num_rows 로도확인가능 ORACLE ANALYZE 하는법 88

89 [ 특정 Table 만 Analyze 하는방법 ] analyze table document compute statistics ex) DOCUMENT Table 만 Analyze analyze index xpkdocbox compute statistics ex) XPKDOCBOX Index 만 Analyze [ 전체 Table Analyze 하는간단한방법 ] 1. vi analyze_all.sql select 'analyze table table_name estimate statistics;' from user_tables 3. set heading off set echo off set feedback off set pagesize 300 (line 이 300 미만일경우 ) spool analyze_table.sql / spool off 4. vi analyze_table.sql 필요없는 Line 제거및정리 [ 전체 Index Analyze 하는간단한방법 ] 1. vi analyze_all.sql select 'analyze index index_name estimate statistics;' from user_indexes 3. set heading off set echo off ORACLE ANALYZE 하는법 89

90 set feedback off set pagesize 300 (line 이 300 미만일경우 ) spool analyze_index.sql / spool off 4. vi analyze_index.sql 필요없는 Line 제거및정리 ORACLE ANALYZE 하는법 90

91 오라클분석함수 :13 FIRST_VALUE(expr) OVER( ) 정렬된값들중첫번째값을반환한다. COUNT(expr) OVER( ) SUM(expr) OVER () LAST_VALUE(expr) OVER RANK() OVER ROW_NUMBER() OVER DENSE_RANK() OVER 예제테이블 SQL 쿼리 CREATE TABLE TEST ( A VARCHAR(10), B VARCHAR(10) ); INSERT INTO TEST VALUES ('2','516958'); INSERT INTO TEST VALUES ('1','123458'); INSERT INTO TEST VALUES ('1','458512'); INSERT INTO TEST VALUES ('2','468521'); INSERT INTO TEST VALUES ('2','735196'); INSERT INTO TEST VALUES ('1','794528'); COMMIT; RANK() 함수와 ROW_NUMBER() 함수정의 오라클분석함수 91

92 RANK() 정의 less.. RANK() 함수는레코드단위로순차적으로순위 (1부터출력 ) 을부여하고레코드단위로같은값에대해서는동일한순위를부여한다. PARTITION BY 를사용하면전체를한그룹으로보는것이아니라 PARTITION BY 에사용된컬럼을기준으로다르게그룹을나누어순위를부여한다. 오라클 8i부터지원하는분석함수입니다. 아래는순위입니다. 순위는동점자가있을수있고공동순위가있다면중간에비는숫자도있겠죠. RANK() 사용예제 SELECT A,B, RANK() OVER(order by A,B) from TEST; 결과 SELECT A,B, RANK() OVER(PARTITION BY A ORDER BY A,B) from TEST; 결과 OVER() 함수는필수입니다. OVER() 함수안에 PARTITION BY 는미필수지만 ORDER BY는필수이다. 보통 OVER() 함수안에 ORDER BY 절에는출력컬럼을모두써준다. PARTITION BY 을사용하면출력결과를하나의그룹으로보는것이아니라 PARTITION BY 에사용된컬럼을기준으로그룹을나누어지게된다. ( 순위도그룹별로별개로 1부터부여지게된다.) less.. ROW_NUMBER() 정의 less.. 오라클분석함수 92

93 ROW_NUMBER() 는레코드단위로동일한값이라도매번새로운순위를부여한다. ROW_NUMBER() 함수는각 PARTITION 내에서 ORDER BY절에의해정렬된순서로유일한값을돌려주는함수이며 ROWNUM 과는관계가없습니다. 오라클 8i부터지원하는분석함수입니다. 위에거는순번이구요순번은유일한값이구요, ROW_NUMBER() 기본예제 SELECT A, ROW_NUMBER() OVER(ORDER BY A,B) FROM TEST; 결과 SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) FROM TEST; 결과 OVER() 함수는필수입니다. OVER() 함수안에 PARTITION BY 는미필수지만 ORDER BY 는필수이다. PARTITION BY 을사용하면 PARTITION BY 에사용된컬럼을기준으로서로별개로 1 부터순위를매기게됩니다. ROW_NUMBER() 응용예제 A,B 그룹별 A,B 의출력을상위 2 개만출력하기 SELECT A,B FROM ( SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) rn 오라클분석함수 93

94 FROM TEST ) WHERE rn <= 2; 또는 SELECT A,B FROM ( SELECT A,B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) RM FROM TEST GROUP BY A,B ) WHERE RM <= 2; 결과 A 그룹별 A 출력을상위 1 개만출력하기 SELECT A FROM ( SELECT A, ROW_NUMBER() OVER(ORDER BY A) rn FROM TEST GROUP BY A ) WHERE rn <= 1; row_number() 함수예제 3 직업별로급여합계를계산해서급여합계가많은순으로가장많은직업 3 개만출력하는예제입니다. -- 일반적인 SQL방법 SELECT job, sal FROM ( SELECT job, SUM(sal) sal FROM emp GROUP BY job ORDER BY sal DESC ) WHERE rownum < 4; JOB SAL MANAGER 오라클분석함수 94

95 ANALYST 6000 PRESIDENT Analytic function ROW_NUMBER() 을사용하는방법 SELECT job, sal FROM ( SELECT job, SUM(sal) sal, ROW_NUMBER() OVER (ORDER BY SUM(sal) DESC) num FROM emp GROUP BY job ) WHERE num < 4; JOB SAL MANAGER ANALYST 6000 PRESIDENT 5000 오라클분석함수 95

96 다중의결과값을하나의행으로컴마로분리해출력하는방법 :34 출처 : 쏘쿨 글쓴이 : 김홍선 문제 ) 다중의결과값을하나의행으로컴마로분리해출력하는방법을알고싶습니다. return ========= 홍길동김길동 --> 홍길동, 김길동, 이길동이길동 ========== 답변 ) emp.ename 컬럼을예로들어쿼리를구성해보면아래와같다. 정확히어떤컬럼들이어떤역할을하는지숙지하는것이중요. SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) path# FROM (SELECT ename, ROWNUM rnum FROM emp) START WITH rnum = 1 CONNECT BY PRIOR rnum = rnum 다중의결과값을하나의행으로컴마로분리해출력하는방법 96

97 에효... 맨날알면서도당하는거 :55 에효... 맨날알면서도당하는거.. 97

98 에효... 맨날알면서도당하는거.. 98

99 오라클커서속성값 :34 Table 6-1 Cursor Attribute Values %FOUND %ISOPEN %NOTFOUND %ROWCOUNT OPEN before exception FALSE exception exception after NULL TRUE NULL 0 First FETCH before NULL TRUE NULL 0 after TRUE TRUE FALSE 1 Next FETCH(es) before TRUE TRUE FALSE 1 after TRUE TRUE FALSE data dependent Last FETCH before TRUE TRUE FALSE data dependent after FALSE TRUE TRUE data dependent CLOSE before FALSE TRUE TRUE data dependent after exception FALSE exception exception The following applies to the information in Table 6-1: Example 6-17 Using %ROWCOUNT DECLARE CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11; name employees.last_name%type; BEGIN OPEN c1; LOOP FETCH c1 INTO name; EXIT WHEN c1%notfound OR c1%notfound IS NULL; DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT '. ' name); IF c1%rowcount = 5 THEN DBMS_OUTPUT.PUT_LINE('--- Fetched 5th record ---'); END IF; END LOOP; CLOSE c1; END; 오라클커서속성값 99

100 Example 6-16 Using %NOTFOUND DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; my_ename employees.last_name%type; my_salary employees.salary%type; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%notfound THEN -- fetch failed, so exit loop -- Another form of this test is "EXIT WHEN c1%notfound OR c1%notfound IS NULL;" EXIT; ELSE -- fetch succeeded DBMS_OUTPUT.PUT_LINE('Name = ' my_ename ', salary = ' my_salary); END IF; END LOOP; END; / DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; the_name employees.last_name%type; the_salary employees.salary%type; BEGIN IF c1%isopen = FALSE THEN -- cursor was not already open OPEN c1; END IF; FETCH c1 INTO the_name, the_salary; CLOSE c1; END; / Example 6-14 Using %FOUND DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; my_ename employees.last_name%type; my_salary employees.salary%type; BEGIN OPEN c1; 오라클커서속성값 100

101 LOOP FETCH c1 INTO my_ename, my_salary; IF c1%found THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE('Name = ' my_ename ', salary = ' my_salary); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END; / 오라클커서속성값 101

102 10 Handling PL/SQL Errors :04 10 Handling PL/SQL Errors Run-time errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program. With many programming languages, unless you disable error checking, a run-time error such as stack overflow or division by zero stops normal processing and returns control to the operating system. With PL/SQL, a mechanism called exception handling lets you bulletproof your program so that it can continue operating in the presence of errors. This chapter contains these topics: Overview of PL/SQL Runtime Error Handling Advantages of PL/SQL Exceptions Summary of Predefined PL/SQL Exceptions Defining Your Own PL/SQL Exceptions How PL/SQL Exceptions Are Raised How PL/SQL Exceptions Propagate Reraising a PL/SQL Exception Handling Raised PL/SQL Exceptions Overview of PL/SQL Compile-Time Warnings Overview of PL/SQL Runtime Error Handling In PL/SQL, an error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names. You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exceptionhandling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions. To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, 10 Handling PL/SQL Errors 102

103 the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. For information on managing errors when using BULK COLLECT, see "Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute". Example 10-1 calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE, the execution of the block is interrupted, and control is transferred to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically. Example 10-1 Runtime Error Handling DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN -- Calculation might cause division-by-zero error. pe_ratio := stock_price / net_earnings; DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' pe_ratio); EXCEPTION -- exception handlers begin -- Only one of the WHEN blocks is executed. WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.'); pe_ratio := NULL; WHEN OTHERS THEN -- handles all other errors DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.'); pe_ratio := NULL; END; -- exception handlers and block end here / The last example illustrates exception handling. With some better error checking, we could have avoided the exception entirely, by substituting a null for the answer if the denominator was zero, as shown in the following example. DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN pe_ratio := CASE net_earnings WHEN 0 THEN NULL ELSE stock_price / net_earnings end; END; 10 Handling PL/SQL Errors 103

104 / Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions Because reliability is crucial for database programs, use both error checking and exception handling to ensure your program can handle all possibilities: Add exception handlers whenever there is any possibility of an error occurring. Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Errors could also occur at other times, for example if a hardware failure with disk storage or memory causes a problem that has nothing to do with your code; but your code still needs to take corrective action. Add error-checking code whenever you can predict that an error might occur if your code gets bad input data. Expect that at some time, your code will be passed incorrect or null parameters, that your queries will return no rows or more rows than you expect. Make your programs robust enough to work even if the database is not in the state you expect. For example, perhaps a table you query will have columns added or deleted, or their types changed. You can avoid such problems by declaring individual variables with %TYPE qualifiers, and declaring records to hold query results with %ROWTYPE qualifiers. Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers. Learn the names and causes of the predefined exceptions. If your database operations might cause particular ORAerrors, associate names with these errors so you can write handlers for them. (You will learn how to do that later in this chapter.) Test your code with different combinations of bad data to see what potential errors arise. Write out debugging information in your exception handlers. You might store such information in a separate table. If so, do it by making a call to a procedure declared with the PRAGMA AUTONOMOUS_TRANSACTION, so that you can commit your debugging information, even if you roll back the work that the main procedure was doing. Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue. Remember, no matter how severe the error is, you want to leave the database in a consistent state and avoid storing any bad data. Advantages of PL/SQL Exceptions Using exceptions for error handling has several advantages. With exceptions, you can reliably handle potential errors from many statements with a single exception handler: Example 10-2 Managing Multiple Errors With a Single Exception Handler DECLARE emp_column VARCHAR2(30) := 'last_name'; table_name VARCHAR2(30) := 'emp'; temp_var VARCHAR2(30); BEGIN temp_var := emp_column; SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS 10 Handling PL/SQL Errors 104

105 WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = UPPER(emp_column); -- processing here temp_var := table_name; SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE'; -- processing here EXCEPTION WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' temp_var); END; / Instead of checking for an error at every point it might occur, just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled. Sometimes the error is not immediately obvious, and could not be detected until later when you perform calculations using bad data. Again, a single exception handler can trap all division-by-zero errors, bad array subscripts, and so on. If you need to check for errors at a specific spot, you can enclose a single statement or a group of statements inside its own BEGIN-END block with its own exception handler. You can make the checking as general or as precise as you like. Isolating error-handling routines makes the rest of the program easier to read and understand. Summary of Predefined PL/SQL Exceptions An internal exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a systemdependent limit. PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. You can use the pragma EXCEPTION_INIT to associate exception names with other Oracle error codes that you can anticipate. To handle unexpected Oracle errors, you can use the OTHERS handler. Within this handler, you can call the functions SQLCODE and SQLERRM to return the Oracle error code and message text. Once you know the error code, you can use it with pragma EXCEPTION_INIT and write a handler specifically for that error. PL/SQL declares predefined exceptions globally in package STANDARD. You need not declare them yourself. You can write handlers for predefined exceptions using the names in the following table: Exception ORA Error SQLCODE Raise When... ACCESS_INTO_NULL A program attempts to assign values to the attributes of an uninitialized object CASE_NOT_FOUND None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign 10 Handling PL/SQL Errors 105

106 values to the elements of an uninitialized nested table or varray. CURSOR_ALREADY_OPEN A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop. DUP_VAL_ON_INDEX A program attempts to store duplicate values in a column that is constrained by a unique index. INVALID_CURSOR A program attempts a cursor operation that is not allowed, such as closing an unopened cursor. INVALID_NUMBER n a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number. LOGIN_DENIED A program attempts to log on to Oracle with an invalid username or password. NO_DATA_FOUND A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query. NOT_LOGGED_ON A program issues a database call without being connected to Oracle. PROGRAM_ERROR PL/SQL has an internal problem. ROWTYPE_MISMATCH The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible. SELF_IS_NULL A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method. STORAGE_ERROR PL/SQL runs out of memory or memory has been corrupted. SUBSCRIPT_BEYOND_COUNT A program references a nested table or varray element using an index number larger than the number of elements in the collection. SUBSCRIPT_OUTSIDE_LIMIT A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range. SYS_INVALID_ROWID The conversion of a character string into a universal rowid fails 10 Handling PL/SQL Errors 106

107 because the character string does not represent a valid rowid. TIMEOUT_ON_RESOURCE A time out occurs while Oracle is waiting for a resource. TOO_MANY_ROWS A SELECT INTO statement returns more than one row. VALUE_ERROR An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.) ZERO_DIVIDE A program attempts to divide a number by zero. Defining Your Own PL/SQL Exceptions PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. Declaring PL/SQL Exceptions Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. In the following example, you declare an exception named past_due: DECLARE past_due EXCEPTION; Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions. Scope Rules for PL/SQL Exceptions You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks. Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block. If you redeclare a global exception in a sub-block, the local declaration prevails. The sub-block cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label: block_label.exception_name Example 10-3 illustrates the scope rules: Example 10-3 Scope of PL/SQL Exceptions 10 Handling PL/SQL Errors 107

108 DECLARE past_due EXCEPTION; acct_num NUMBER; BEGIN DECLARE sub-block begins past_due EXCEPTION; -- this declaration prevails acct_num NUMBER; due_date DATE := SYSDATE - 1; todays_date DATE := SYSDATE; BEGIN IF due_date < todays_date THEN RAISE past_due; -- this is not handled END IF; END; sub-block ends EXCEPTION WHEN past_due THEN -- does not handle raised exception DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.'); END; / The enclosing block does not handle the raised exception because the declaration of past_due in the sub-block prevails. Though they share the same name, the two past_due exceptions are different, just as the two acct_num variables share the same name but are different variables. Thus, the RAISE statement and the WHEN clause refer to different exceptions. To have the enclosing block handle the raised exception, you must remove its declaration from the sub-block or define an OTHERS handler. Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time, not at run time. In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle. You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number); where exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in Example Handling PL/SQL Errors 108

109 Example 10-4 Using PRAGMA EXCEPTION_INIT DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN NULL; -- Some operation that causes an ORA error EXCEPTION WHEN deadlock_detected THEN NULL; -- handle the error END; / Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions. To call RAISE_APPLICATION_ERROR, use the syntax raise_application_error( error_number, message[, {TRUE FALSE}]); where error_number is a negative integer in the range and message is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it. An application can call raise_application_error only from an executing stored subprogram (or method). When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error. In Example 10-5, you call raise_application_error if an error condition of your choosing happens (in this case, if the current schema owns less than 1000 tables): Example 10-5 Raising an Application Error With raise_application_error DECLARE num_tables NUMBER; BEGIN SELECT COUNT(*) INTO num_tables FROM USER_TABLES; IF num_tables < 1000 THEN /* Issue your own error code (ORA-20101) with your own error message. Note that you do not need to qualify raise_application_error with DBMS_STANDARD */ raise_application_error(-20101, 'Expecting at least 1000 tables'); ELSE NULL; -- Do the rest of the processing (for the non-error case). 10 Handling PL/SQL Errors 109

110 END IF; END; / The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. Also, it can use the pragma EXCEPTION_INIT to map specific error numbers returned by raise_application_error to exceptions of its own, as the following Pro*C example shows: EXEC SQL EXECUTE /* Execute embedded PL/SQL block using host variables v_emp_id and v_amount, which were assigned values in the host environment. */ DECLARE null_salary EXCEPTION; /* Map error number returned by raise_application_error to user-defined exception. */ PRAGMA EXCEPTION_INIT(null_salary, ); BEGIN raise_salary(:v_emp_id, :v_amount); EXCEPTION WHEN null_salary THEN INSERT INTO emp_audit VALUES (:v_emp_id,...); END; END-EXEC; This technique allows the calling application to handle error conditions in specific exception handlers. Redeclaring Predefined Exceptions Remember, PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself. Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. For example, if you declare an exception named invalid_number and then PL/SQL raises the predefined exception INVALID_NUMBER internally, a handler written for INVALID_NUMBER will not catch the internal exception. In such cases, you must use dot notation to specify the predefined exception, as follows: EXCEPTION WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN -- handle the error END; How PL/SQL Exceptions Are Raised Internal exceptions are raised implicitly by the run-time system, as are user-defined exceptions that you have 10 Handling PL/SQL Errors 110

111 associated with an Oracle error number using EXCEPTION_INIT. However, other user-defined exceptions must be raised explicitly by RAISE statements. Raising Exceptions with the RAISE Statement PL/SQL blocks and subprograms should raise an exception only when an error makes it undesirable or impossible to finish processing. You can place RAISE statements for a given exception anywhere within the scope of that exception. In Example 10-6, you alert your PL/SQL block to a user-defined exception named out_of_stock. Example 10-6 Using RAISE to Force a User-Defined Exception DECLARE out_of_stock EXCEPTION; number_on_hand NUMBER := 0; BEGIN IF number_on_hand < 1 THEN RAISE out_of_stock; -- raise an exception that we defined END IF; EXCEPTION WHEN out_of_stock THEN -- handle the error DBMS_OUTPUT.PUT_LINE('Encountered out-of-stock error.'); END; / You can also raise a predefined exception explicitly. That way, an exception handler written for the predefined exception can process other errors, as Example 10-7 shows: Example 10-7 Using RAISE to Force a Pre-Defined Exception DECLARE acct_type INTEGER := 7; BEGIN IF acct_type NOT IN (1, 2, 3) THEN RAISE INVALID_NUMBER; -- raise predefined exception END IF; EXCEPTION WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('HANDLING INVALID INPUT BY ROLLING BACK.'); ROLLBACK; END; / How PL/SQL Exceptions Propagate 10 Handling PL/SQL Errors 111

112 When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. If no handler is found, PL/SQL returns an unhandled exception error to the host environment. Exceptions cannot propagate across remote procedure calls done through database links. A PL/SQL block cannot catch an exception raised by a remote subprogram. For a workaround, see "Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR". Figure 10-1, Figure 10-2, and Figure 10-3 illustrate the basic propagation rules. Figure 10-1 Propagation Rules: Example 1 Description of lnpls009.gif follows Description of the illustration lnpls009.gif Figure 10-2 Propagation Rules: Example 2 Description of lnpls010.gif follows Description of the illustration lnpls010.gif Figure 10-3 Propagation Rules: Example 3 Description of lnpls011.gif follows Description of the illustration lnpls011.gif An exception can propagate beyond its scope, that is, beyond the block in which it was declared, as shown in Example Example 10-8 Scope of an Exception BEGIN DECLARE sub-block begins past_due EXCEPTION; due_date DATE := trunc(sysdate) - 1; todays_date DATE := trunc(sysdate); BEGIN IF due_date < todays_date THEN RAISE past_due; END IF; END; sub-block ends EXCEPTION WHEN OTHERS THEN ROLLBACK; END; / Because the block that declares the exception past_due has no handler for it, the exception propagates to the enclosing block. But the enclosing block cannot reference the name PAST_DUE, because the scope where it was declared no longer exists. Once the exception name is lost, only an OTHERS handler can catch the exception. If there 10 Handling PL/SQL Errors 112

113 is no handler for a user-defined exception, the calling application gets this error: ORA-06510: PL/SQL: unhandled user-defined exception Reraising a PL/SQL Exception Sometimes, you want to reraise an exception, that is, handle it locally, then pass it to an enclosing block. For example, you might want to roll back a transaction in the current block, then log the error in an enclosing block. To reraise an exception, use a RAISE statement without an exception name, which is allowed only in an exception handler: Example 10-9 Reraising a PL/SQL Exception DECLARE salary_too_high EXCEPTION; current_salary NUMBER := 20000; max_salary NUMBER := 10000; erroneous_salary NUMBER; BEGIN BEGIN sub-block begins IF current_salary > max_salary THEN RAISE salary_too_high; -- raise the exception END IF; EXCEPTION WHEN salary_too_high THEN -- first step in handling the error DBMS_OUTPUT.PUT_LINE('Salary ' erroneous_salary ' is out of range.'); DBMS_OUTPUT.PUT_LINE('Maximum salary is ' max_salary '.'); RAISE; -- reraise the current exception END; sub-block ends EXCEPTION WHEN salary_too_high THEN -- handle the error more thoroughly erroneous_salary := current_salary; current_salary := max_salary; DBMS_OUTPUT.PUT_LINE('Revising salary from ' erroneous_salary ' to ' current_salary '.'); END; / Handling Raised PL/SQL Exceptions When an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part, which is formatted as follows: 10 Handling PL/SQL Errors 113

114 EXCEPTION WHEN exception1 THEN -- handler for exception1 sequence_of_statements1 WHEN exception2 THEN -- another handler for exception2 sequence_of_statements2... WHEN OTHERS THEN -- optional handler for all other errors sequence_of_statements3 END; To catch raised exceptions, you write exception handlers. Each handler consists of a WHEN clause, which specifies an exception, followed by a sequence of statements to be executed when that exception is raised. These statements complete execution of the block or subprogram; control does not return to where the exception was raised. In other words, you cannot resume processing where you left off. The optional OTHERS exception handler, which is always the last handler in a block or subprogram, acts as the handler for all exceptions not named specifically. Thus, a block or subprogram can have only one OTHERS handler. Use of the OTHERS handler guarantees that no exception will go unhandled. If you want two or more exceptions to execute the same sequence of statements, list the exception names in the WHEN clause, separating them by the keyword OR, as follows: EXCEPTION WHEN over_limit OR under_limit OR VALUE_ERROR THEN -- handle the error If any of the exceptions in the list is raised, the associated sequence of statements is executed. The keyword OTHERS cannot appear in the list of exception names; it must appear by itself. You can have any number of exception handlers, and each handler can associate a list of exceptions with a sequence of statements. However, an exception name can appear only once in the exception-handling part of a PL/SQL block or subprogram. The usual scoping rules for PL/SQL variables apply, so you can reference local and global variables in an exception handler. However, when an exception is raised inside a cursor FOR loop, the cursor is closed implicitly before the handler is invoked. Therefore, the values of explicit cursor attributes are not available in the handler. Exceptions Raised in Declarations Exceptions can be raised in declarations by faulty initialization expressions. For example, the following declaration raises an exception because the constant credit_limit cannot store numbers larger than 999: Example Raising an Exception in a Declaration DECLARE credit_limit CONSTANT NUMBER(3) := 5000; -- raises an error BEGIN NULL; EXCEPTION WHEN OTHERS THEN -- Cannot catch the exception. This handler is never called. 10 Handling PL/SQL Errors 114

115 END; / DBMS_OUTPUT.PUT_LINE('Can''t handle an exception in a declaration.'); Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block. Handling Exceptions Raised in Handlers When an exception occurs within an exception handler, that same handler cannot catch the exception. An exception raised inside a handler propagates immediately to the enclosing block, which is searched to find a handler for this new exception. From there on, the exception propagates normally. For example: EXCEPTION WHEN INVALID_NUMBER THEN INSERT INTO might raise DUP_VAL_ON_INDEX WHEN DUP_VAL_ON_INDEX THEN cannot catch the exception END; Branching to or from an Exception Handler A GOTO statement can branch from an exception handler into an enclosing block. A GOTO statement cannot branch into an exception handler, or from an exception handler into the current block. Retrieving the Error Code and Error Message: SQLCODE and SQLERRM In an exception handler, you can use the built-in functions SQLCODE and SQLERRM to find out which error occurred and to get the associated error message. For internal exceptions, SQLCODE returns the number of the Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns SQLERRM returns the corresponding error message. The message begins with the Oracle error code. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message User-Defined Exception unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names. If no exception has been raised, SQLCODE returns zero and SQLERRM returns the message: ORA-0000: normal, successful completion. You can pass an error number to SQLERRM, in which case SQLERRM returns the message associated with that error number. Make sure you pass negative error numbers to SQLERRM. Passing a positive number to SQLERRM always returns the message user-defined exception unless you pass +100, in which case SQLERRM returns the message no data found. Passing a zero to SQLERRM always returns the message normal, successful completion. You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local 10 Handling PL/SQL Errors 115

116 variables, then use the variables in the SQL statement, as shown in Example Example Displaying SQLCODE and SQLERRM CREATE TABLE errors (code NUMBER, message VARCHAR2(64), happened TIMESTAMP); DECLARE name employees.last_name%type; v_code NUMBER; v_errm VARCHAR2(64); BEGIN SELECT last_name INTO name FROM employees WHERE employee_id = -1; EXCEPTION WHEN OTHERS THEN v_code := SQLCODE; v_errm := SUBSTR(SQLERRM, 1, 64); DBMS_OUTPUT.PUT_LINE('Error code ' v_code ': ' v_errm); -- Normally we would call another procedure, declared with PRAGMA -- AUTONOMOUS_TRANSACTION, to insert information about errors. INSERT INTO errors VALUES (v_code, v_errm, SYSTIMESTAMP); END; / The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to err_msg. The functions SQLCODE and SQLERRM are especially useful in the OTHERS exception handler because they tell you which internal exception was raised. When using pragma RESTRICT_REFERENCES to assert the purity of a stored function, you cannot specify the constraints WNPS and RNPS if the function calls SQLCODE or SQLERRM. Catching Unhandled Exceptions Remember, if it cannot find a handler for a raised exception, PL/SQL returns an unhandled exception error to the host environment, which determines the outcome. For example, in the Oracle Precompilers environment, any database changes made by a failed SQL statement or PL/SQL block are rolled back. Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to OUT parameters (unless they are NOCOPY parameters). Also, if a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram. You can avoid unhandled exceptions by coding an OTHERS handler at the topmost level of every PL/SQL program. Tips for Handling PL/SQL Errors In this section, you learn techniques that increase flexibility. Continuing after an Exception Is Raised 10 Handling PL/SQL Errors 116

117 An exception handler lets you recover from an otherwise fatal error before exiting a block. But when the handler completes, the block is terminated. You cannot return to the current block from an exception handler. In the following example, if the SELECT INTO statement raises ZERO_DIVIDE, you cannot resume with the INSERT statement: CREATE TABLE employees_temp AS SELECT employee_id, salary, commission_pct FROM employees; DECLARE sal_calc NUMBER(8,2); BEGIN INSERT INTO employees_temp VALUES (301, 2500, 0); SELECT salary / commission_pct INTO sal_calc FROM employees_temp WHERE employee_id = 301; INSERT INTO employees_temp VALUES (302, sal_calc/100,.1); EXCEPTION WHEN ZERO_DIVIDE THEN NULL; END; / You can still handle an exception for a statement, then continue with the next statement. Place the statement in its own sub-block with its own exception handlers. If an error occurs in the sub-block, a local handler can catch the exception. When the sub-block ends, the enclosing block continues to execute at the point where the sub-block ends, as shown in Example Example Continuing After an Exception DECLARE sal_calc NUMBER(8,2); BEGIN INSERT INTO employees_temp VALUES (303, 2500, 0); BEGIN -- sub-block begins SELECT salary / commission_pct INTO sal_calc FROM employees_temp WHERE employee_id = 301; EXCEPTION WHEN ZERO_DIVIDE THEN sal_calc := 2500; END; -- sub-block ends INSERT INTO employees_temp VALUES (304, sal_calc/100,.1); EXCEPTION WHEN ZERO_DIVIDE THEN NULL; END; 10 Handling PL/SQL Errors 117

118 / In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets sal_calc to Execution of the handler is complete, so the sub-block terminates, and execution continues with the INSERT statement. See also Example 5-38, "Collection Exceptions". You can also perform a sequence of DML operations where some might fail, and process the exceptions only after the entire operation is complete, as described in "Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute". Retrying a Transaction After an exception is raised, rather than abandon your transaction, you might want to retry it. The technique is: 1. Encase the transaction in a sub-block. 2. Place the sub-block inside a loop that repeats the transaction. 3. Before starting the transaction, mark a savepoint. If the transaction succeeds, commit, then exit from the loop. If the transaction fails, control transfers to the exception handler, where you roll back to the savepoint undoing any changes, then try to fix the problem. In Example 10-13, the INSERT statement might raise an exception because of a duplicate value in a unique column. In that case, we change the value that needs to be unique and continue with the next loop iteration. If the INSERT succeeds, we exit from the loop immediately. With this technique, you should use a FOR or WHILE loop to limit the number of attempts. Example Retrying a Transaction After an Exception CREATE TABLE results ( res_name VARCHAR(20), res_answer VARCHAR2(3) ); CREATE UNIQUE INDEX res_name_ix ON results (res_name); INSERT INTO results VALUES ('SMYTHE', 'YES'); INSERT INTO results VALUES ('JONES', 'NO'); DECLARE name VARCHAR2(20) := 'SMYTHE'; answer VARCHAR2(3) := 'NO'; suffix NUMBER := 1; BEGIN FOR i IN 1..5 LOOP -- try 5 times BEGIN -- sub-block begins SAVEPOINT start_transaction; -- mark a savepoint /* Remove rows from a table of survey results. */ DELETE FROM results WHERE res_answer = 'NO'; /* Add a survey respondent's name and answers. */ INSERT INTO results VALUES (name, answer); -- raises DUP_VAL_ON_INDEX if two respondents have the same name COMMIT; 10 Handling PL/SQL Errors 118

119 EXIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO start_transaction; -- undo changes suffix := suffix + 1; -- try to fix problem name := name TO_CHAR(suffix); END; -- sub-block ends END LOOP; END; / Using Locator Variables to Identify Exception Locations Using one exception handler for a sequence of statements, such as INSERT, DELETE, or UPDATE statements, can mask the statement that caused an error. If you need to know which statement failed, you can use a locator variable: Example Using a Locator Variable to Identify the Location of an Exception CREATE OR REPLACE PROCEDURE loc_var AS stmt_no NUMBER; name VARCHAR2(100); BEGIN stmt_no := 1; -- designates 1st SELECT statement SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'ABC%'; stmt_no := 2; -- designates 2nd SELECT statement SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'XYZ%'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Table name not found in query ' stmt_no); END; / CALL loc_var(); Overview of PL/SQL Compile-Time Warnings To make your programs more robust and avoid problems at run time, you can turn on checking for certain warning conditions. These conditions are not serious enough to produce an error and keep you from compiling a subprogram. They might point out something in the subprogram that produces an undefined result or might create a performance problem. To work with PL/SQL warning messages, you use the PLSQL_WARNINGS initialization parameter, the DBMS_WARNING package, and the USER/DBA/ALL_PLSQL_OBJECT_SETTINGS views. PL/SQL Warning Categories 10 Handling PL/SQL Errors 119

120 PL/SQL warning messages are divided into categories, so that you can suppress or display groups of similar warnings during compilation. The categories are: SEVERE: Messages for conditions that might cause unexpected behavior or wrong results, such as aliasing problems with parameters. PERFORMANCE: Messages for conditions that might cause performance problems, such as passing a VARCHAR2 value to a NUMBER column in an INSERT statement. INFORMATIONAL: Messages for conditions that do not have an effect on performance or correctness, but that you might want to change to make the code more maintainable, such as unreachable code that can never be executed. The keyword All is a shorthand way to refer to all warning messages. You can also treat particular messages as errors instead of warnings. For example, if you know that the warning message PLW represents a serious problem in your code, including 'ERROR:05003' in the PLSQL_WARNINGS setting makes that condition trigger an error message (PLS_05003) instead of a warning message. An error message causes the compilation to fail. Controlling PL/SQL Warning Messages To let the database issue warning messages during PL/SQL compilation, you set the initialization parameter PLSQL_WARNINGS. You can enable and disable entire categories of warnings (ALL, SEVERE, INFORMATIONAL, PERFORMANCE), enable and disable specific message numbers, and make the database treat certain warnings as compilation errors so that those conditions must be corrected. This parameter can be set at the system level or the session level. You can also set it for a single compilation by including it as part of the ALTER PROCEDURE... COMPILE statement. You might turn on all warnings during development, turn off all warnings when deploying for production, or turn on some warnings when working on a particular subprogram where you are concerned with some aspect, such as unnecessary code or performance. Example Controlling the Display of PL/SQL Warnings -- To focus on one aspect ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE'; -- Recompile with extra checking ALTER PROCEDURE loc_var COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE' REUSE SETTINGS; -- To turn off all warnings ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'; -- Display 'severe' warnings, don't want 'performance' warnings, and -- want PLW warnings to produce errors that halt compilation ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE', 'ERROR:06002'; -- For debugging during development ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; 10 Handling PL/SQL Errors 120

121 Warning messages can be issued during compilation of PL/SQL subprograms; anonymous blocks do not produce any warnings. The settings for the PLSQL_WARNINGS parameter are stored along with each compiled subprogram. If you recompile the subprogram with a CREATE OR REPLACE statement, the current settings for that session are used. If you recompile the subprogram with an ALTER... COMPILE statement, the current session setting might be used, or the original setting that was stored with the subprogram, depending on whether you include the REUSE SETTINGS clause in the statement. For more information, see ALTER FUNCTION, ALTER PACKAGE, and ALTER PROCEDURE in Oracle Database SQL Reference. To see any warnings generated during compilation, you use the SQL*Plus SHOW ERRORS command or query the USER_ERRORS data dictionary view. PL/SQL warning messages all use the prefix PLW. Using the DBMS_WARNING Package If you are writing a development environment that compiles PL/SQL subprograms, you can control PL/SQL warning messages by calling subprograms in the DBMS_WARNING package. You might also use this package when compiling a complex application, made up of several nested SQL*Plus scripts, where different warning settings apply to different subprograms. You can save the current state of the PLSQL_WARNINGS parameter with one call to the package, change the parameter to compile a particular set of subprograms, then restore the original parameter value. For example, Example is a procedure with unnecessary code that could be removed. It could represent a mistake, or it could be intentionally hidden by a debug flag, so you might or might not want a warning message for it. Example Using the DBMS_WARNING Package to Display Warnings -- When warnings disabled, the following procedure compiles with no warnings CREATE OR REPLACE PROCEDURE unreachable_code AS x CONSTANT BOOLEAN := TRUE; BEGIN IF x THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; END unreachable_code; / -- enable all warning messages for this session CALL DBMS_WARNING.set_warning_setting_string('ENABLE:ALL','SESSION'); -- Check the current warning setting SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL; -- Recompile the procedure and a warning about unreachable code displays ALTER PROCEDURE unreachable_code COMPILE; SHOW ERRORS; 10 Handling PL/SQL Errors 121

122 In Example 10-16, you could have used the following ALTER PROCEDURE without the call to DBMS_WARNINGS.set_warning_setting_string: ALTER PROCEDURE unreachable_code COMPILE PLSQL_WARNINGS = 'ENABLE:ALL' REUSE SETTINGS; For more information, see ALTER PROCEDURE in Oracle Database SQL Reference, DBMS_WARNING package in Oracle Database PL/SQL Packages and Types Reference, and PLW- messages in Oracle Database Error Messages 10 Handling PL/SQL Errors 122

123 일반적인정규표현식 :08 정규표현식 (Regular Expressions) PHP 에서정규표현식을지원하는함수로는 ereg(), eregi(), ereg_replace(), eregi_replace(), split(), spliti() 등이있다. 이때 ereg_replace 보다 preg_replace 가훨씬빠르다고한다. 이외에도펄형식을지원하는정규표현식에관련된함수들도있다. 1. 기초 (Basic Syntax) First of all, let's take a look at two special symbols: '^ (start)' and '$ (end)'. "^The": matches any string that starts with "The"; "of despair$": matches a string that ends in the substring "of despair"; "^abc$": a string that starts and ends with "abc" -- that could only be "abc" itself! "notice": a string that has the text "notice" in it. "notice" 그자체가아닌 "notice" 를포함하는것을뜻한다. There are also the symbols '* (zero or more)', '+ (one or more)', and '? (zero or one)', which denote the number of times a character or a sequence of characters may occur. "ab*": matches a string that has an a followed by zero or more b's ("a", "ab", "abbb", etc.); "ab+": same, but there's at least one b ("ab", "abbb", etc.); "ab?": there might be a b or not; "a?b+$": a possible a followed by one or more b's ending a string. You can also use bounds, which come inside braces and indicate ranges in the number of occurences: "ab{2}": matches a string that has an a followed by exactly two b's ("abb"); "ab{2,}": there are at least two b's ("abb", "abbbb", etc.); "ab{3,5}": from three to five b's ("abbb", "abbbb", or "abbbbb"). "ab{0,}" "ab*" "ab{1,}" "ab+" "ab{0,1}" "ab?" Now, to quantify a sequence of characters, put them inside parentheses: "a(xxyy)*": matches a string that has an a followed by zero or more copies of the sequence "xxyy"; "a(bc){1,5}": one through five copies of "bc."; ("abc", "abcbc", "abcbcbc", "abcbcbcbc", or "abcbcbcbcbc") There's also the ' ' symbol, which works as an OR operator: "hi hello": matches a string that has either "hi" or "hello" in it; 일반적인정규표현식 123

124 "(b cd)ef": a string that has either "bef" or "cdef"; "(a b)*c": a string that has a sequence of alternating a's and b's ending in a c; ("ac", "bc", or "c") A period ('.') stands for any single character: "a.[0-9]": matches a string that has an a followed by one character and a digit; ("ax1", "ak7", etc.) "^.{3}$": a string with exactly 3 characters. "^(100)(\.[0-9]{1,2})?$" : "100" 이거나 100 다음에소수점이있다면첫째나둘째자리까지있다. Bracket expressions specify which characters are allowed in a single position of a string: "[ab]": matches a string that has either an a or a b (that's the same as "a b"); "[a-d]": a string that has lowercase letters 'a' through 'd' (that's equal to "a b c d" and even "[abcd]"); "^[a-za-z]": a string that starts with a letter; "[0-9]%": a string that has a single digit before a percent sign; ",[a-za-z0-9]$": a string that ends in a comma followed by an alphanumeric character. You can also list which characters you DON'T want -- just use a '^' as the first symbol in a bracket expression (i.e., "%[^a-za-z]%" matches a string with a character that is not a letter between two percent signs). "[^abc]": a, b, 또는 c를제외한문자. "[^0-9]: 숫자를제외한문자. 특수문자에해당하는문자사용하기 \ 는특수문자를문자자체로해석하도록하는 Escape 문자로사용된다.? 자체를찾으려고하면 \? 와같이사용되어야한다. \ 를사용해야하는문자로는 ^ \ [] () {}. *? + 가있다. 이문자들을문자자체로해석을하기위해서는 \^ \\ \ \[ \( \{ \. \* \? \+ 등과같이사용해야한다. In order to be taken literally, you must escape the characters "^.[$() *+?{\" with a backslash ('\'), as they have special meaning. On top of that, you must escape the backslash character itself in PHP3 strings, so, for instance, the regular expression "(\$?[0-9]+" would have the function call: ereg("(\\$?[0-9]+", $str) (what string does that validate?) Just don't forget that bracket expressions are an exception to that rule--inside them, all special characters, including the backslash ('\'), lose their special powers (i.e., "[*\+?{}.]" matches exactly any of the characters inside the brackets). And, as the regex man pages tell us: "To include a literal ']' in the list, make it the first character (following a possible '^'). To include a literal '-', make it the first or last character, or the second endpoint of a range." 예를들어 what? 이라는물음표로끝나는문자를찾고싶다고, egrep 'what?'... 이라고하면? 이특수문자이므로 wha를포함한 whale도찾게된다. 또, 3.14로찾을때는 3+14 등도찾게된다. 특수문자가 [] 안과밖에서다르다는점을생각하여각각의경우를살펴보자. 우선 [] 밖의경우는, \ 을특수문자앞에붙이기. 예, "what\?", "3\.14" [] 을사용하기. 예, "what[?]", "3[.]14" 첫번째방법은보통 escape라고부르며, 특수문자앞에 \ 을붙여서특수문자의특수한기능을제거한다. 두번째방법은 [] 밖의많은특수문자들이 [] 안에서는일반문자가되는점을이용한것이다. 보통첫번째방법을많이사용한다. 이때 \ 도뒤에나오는특수문자를일반문자로만드는특수문자이기때문에, 문자그대로의 \ 을나타내려면 \\ 을사용해야한다. 물론 일반적인정규표현식 124

125 [\] 도가능하다. [] 안의특수문자는위치를바꿔서처리한다. 먼저, ^는 [^abc] 와같이처음에나와야만의미가있으므로 [abc^] 와같이다른위치에사용하면된다. -는 [a-z] 와같이두문자사이에서만의미가있으므로 [-abc] 나 [abc-] 와같이제일처음이나마지막에사용한다. (grep과같이도구에따라역으로일반문자앞에 \ 를붙여서특수문자를만드는경우가있다.) For completeness, I should mention that there are also collating sequences, character classes, and equivalence classes. I won't be getting into details on those, as they won't be necessary for what we'll need further down this article. You should refer to the regex man pages for more information. character class의예 [[:alpha:]] [a-za-z] [[:digit:]] [0-9] [[:alnum:]] [0-9a-zA-Z] [[:space:]] 공백문자 2. 응용예제 lidating Money Strings Ok, we can now use what we've learned to work on something useful: a regular expression to check user input of an amount of money. A quantity of money can be written in four ways we can consider acceptable: " " and "10,000.00", and, without the cents, "10000" and "10,000". Let's begin with: ^[1-9][0-9]*$ That validates any number that doesn't start with a 0. But that also means the string "0" doesn't pass the test. Here's a solution: ^(0 [1-9][0-9]*)$ "Just a zero OR some number that doesn't start with a zero." We may also allow a minus sign to be placed before the number: ^(0 -?[1-9][0-9]*)$ That means: "a zero OR a possible minus sign and a number that doesn't start with a zero." Ok, let's not be so strict and let the user start the number with a zero. Let's also drop the minus sign, as we won't be needing it for the money string. What we could do is specify an optional decimal fraction part in the number: ^[0-9]+(\.[0-9]+)?$ It's implicit in the highlited construct that a period always comes with at least one digit, as a whole set. So, for instance, "10." is not validated, whereas "10" and "10.2" are. ^[0-9]+(\.[0-9]{2})?$ We specified that there must be exactly two decimal places. If you think that's too harsh, you can do the following: ^[0-9]+(\.[0-9]{1,2})?$ That allows the user to write just one number after the period. Now, as for the commas separating the thousands, we can put in: ^[0-9]{1,3}(,[0-9]{3})*(\.[0-9]{1,2})?$ "A set of 1 to 3 digits followed by zero or more sets of a comma and three digits." Easy enough, isn't it? But let's make the commas optional: 일반적인정규표현식 125

126 ^([0-9]+ [0-9]{1,3}(,[0-9]{3})*)(\.[0-9]{1,2})?$ That's it. Don't forget that the '+' can be substituted by a '*' if you want empty strings to be accepted also (why?). And don't forget to escape the backslash for the function call (common mistake here). Now, once the string is validated, we strip off any commas with str_replace(",", "", $money) and typecast it to double so we can make math with it. Validating Addresses Ok, let's take on addresses. There are three parts in an address: the POP3 user name (everything to the left of the '@'), the '@', and the server name (the rest). The user name may contain upper or lowercase letters, digits, periods ('.'), minus signs ('-'), and underscore signs ('_'). That's also the case for the server name, except for underscore signs, which may not occur. Now, you can't start or end a user name with a period, it doesn't seem reasonable. The same goes for the domain name. And you can't have two consecutive periods, there should be at least one other character between them. Let's see how we would write an expression to validate the user name part: ^[_a-za-z0-9-]+$ That doesn't allow a period yet. Let's change it: ^[_a-za-z0-9-]+(\.[_a-za-z0-9-]+)*$ That says: "at least one valid character followed by zero or more sets consisting of a period and one or more valid characters." To simplify things a bit, we can use the expression above with eregi(), instead of ereg(). Because eregi() is not sensitive to case, we don't have to specify both ranges "a-z" and "A-Z" -- one of them is enough: ^[_a-z0-9-]+(\.[_a-z0-9-]+)*$ For the server name it's the same, but without the underscores: ^[a-z0-9-]+(\.[a-z0-9-]+)*$ Done. Now, joining both expressions around the 'at' sign, we get: ^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*$ Microsoft.NET framework의 RegularExpressionValidate 컨트롤에서는 ^[_a-za-z0-9]+([-+.][_a-za-z0-9]+)*@[_a-za-z0-9]+([-.][_a-za-z0-9]+)*\.[_a-za-z0-9]+([-.][_a-za-z0-9]+)*$ 이렇게쓰고있다. 이게더정확한듯하다. 위의것은 '-' 으로시작하는것을허용하는데이것은그렇지않다. 주민등록번호 주민등록번호는 "6 자리의숫자 - ( ) 로시작하는 7 자리의숫자 " 의형태로되어있다. [0-9]{6}-( )[0-9]{6} 자연수및유리수 <script language="javascript"> <!-- function realcheck(str) { var foopat=/^[1-9][0-9]*(\.[0-9]+)?$/ 일반적인정규표현식 126

127 } var matcharray=str.match(foopat) if (matcharray==null) {return false;} return true; function positiveintcheck(str) { var foopat=/^[1-9][0-9]*$/ var matcharray=str.match(foopat) if (matcharray==null) {return false;} return true; } //--> </script> URL 주소 URL 주소의형식은 과같으며영숫자. \? & _ = ~ + 등으로이루어져있다. [0-9a-zA-Z_]+(\.[0-9a-zA-Z/\?_=+~]+)* (http ftp https):/{2}[0-9a-za-z_]+(\.[0-9a-za-z/\?_=+~]+)* 기타등등 if(!eregi("^([[:alnum:]_%+=.-]+)@([[:alnum:]_.-]+)\.([a-z]{2,3} [0-9]{1,3})$",$ )) { echo " 잘못된 address 다."; } if(!ereg("([^[:space:]])",$name)) { echo("<script>alert(' 이름을입력하세요!');history.go(-1)</script>"); exit; } if(!ereg("([a-za-z0-9]+)@([a-za-z0-9])([.a-za-z0-9]+)([a-za-z0-9])$",$ )) { echo("<script>alert(' 올바른 주소가아닙니다.');history.go(-1)</script>"); exit; } if(!ereg("(^[0-9a-za-z]{4,8}$)",$passwd)) { echo("<script>alert(' 비밀번호는 4 자에서 8 자까지의영문자또는숫자로입력하세요!');history.go(-1) 일반적인정규표현식 127

128 </script>"); exit; } if(!ereg("([^[:space:]])",$title)) { echo("<script>alert(' 제목을입력하세요!');history.go(-1)</script>"); exit; } Other uses Extracting Parts of a String ereg() and eregi() have a feature that allows us to extract matches of patterns from strings (read the manual for details on how to use that). For instance, say we want do get the filename from a path/url string -- this code would be all we need: ereg("([^\\/]*)$", $pathorurl, $regs); echo $regs[1]; Advanced Replacing ereg_replace() and eregi_replace() are also very useful: suppose we want to separate all words in a string by commas: ereg_replace("[ \n\r\t]+", ",", trim($str)); 찾은문자열사용하기 정규식을이용하여문자열을찾는함수는 ereg() 와 eregi() 함수이다. 이함수문자열에서찾고자하는문자의패턴이있는지확인할수있다. 그런데문자열이존재하는지확인만하고어떤문자열을찾았는지볼수있는방법은없을까? ereg("a(b*)(c+)", "abbbbcccd", $reg); 다음과같이세번째인자를주면주어진패턴과일치하는문자열을구할수있다. 그럼 $reg 에는어떤것들이저장되어있을까? $reg는배열의형태로반환이된다. $reg[0] 에는주어진전체패턴인 a(b*)(c+) 에대한내용이들어간다. 위의함수결과에서는 abbbbccc 가된다. $reg[1] 에는첫번째 (b*) 에해당하는 bbbb가들어간다. $reg[2] 에는두번째 () 인 (c+) 에해당하는 ccc가들어가게된다. 그러면 () 안에또 () 가들어가면어떤식으로해석이될까? 만약에 (a+(a*))a(b+(c*)) 라는패턴이있으면어떤식으로들어가게될지를알아보자. 먼저 $reg[0] 에는전체패턴인 (a+(a*))a(b+(c*)) 에대한내용이들어간다. $reg[1] 에는첫번째큰 () 인 (a+(a*)) 에대한내용이들어가고, $reg[2] 에는이괄호안에있는작은괄호 (a*) 에대한내용이들어간다. $reg[3], $reg[4] 에는각각 (b+(c*)), (c*) 의내용이들어가게된다. 일반적인정규표현식 128

129 정규식을이용한문자열치환 정규표현식을이용하여문자열을치환하는함수는 ereg_replace() 와 eregi_replace() 함수가있다. 두함수의차이점은 eregi_replace() 의경우에는영문자의대소문자구분을하지않는것이다. $str = "test test"; // 두개의 test 사이에는 4개의스페이스가있다. &str = ereg_replace(" "," ", $str); 이명령의실행결과로 $str = "test test"; 가된다. 이는내용을출력할때많이사용되는구문이다. 그럼문자열치환시에문자열패턴을사용할경우찾아낸문자열들을다시사용할방법은어떤것이있는가? 이럴때사용되는것들이 \\0, \\1, \\2,... 등이다. 다음과같은패턴이있다고하자. ([_0-9a-zA-Z]+(\.[_0-9a-zA-Z]+)*)@([0-9a-zA-Z]+(\.[0-9a-zA-Z]+)*) 이런패턴이있을경우전체가 \\0이된다. \\1은 ([_0-9a-zA-Z]+(\.[_0-9a-zA-Z]+)*) 이된다. \\2는 \.[_0-9a-zA-Z]+) 이된다. \\3은 ([0-9a-zA-Z]+(\.[0-9a-zA- Z]+)*) 이되고 \\4는 (\.[0-9a-zA-Z]+) 이된다. 다음과같은구문이있을경우 : $mail = "[email protected]"; $pattern = "([_0-9a-zA-Z]+(\.[_0-9a-zA-Z]+)*)@([0-9a-zA-Z]+(\.[0-9a-zA-Z]+)*)"; $link = ereg_replace($pattern, "<a href = 'mailto:\\0'>\\0</a>",$mail); 결과는 <a href='mailto:[email protected]'>[email protected]</a> 이된다. Some exercises Now here's something to make you busy: modify our -validating regular expression to force the server name part to consist of at least two names (hint: only one character needs to be changed); build a function call to ereg_replace() that emulates trim(); make up another function call to ereg_replace() that escapes the characters '#', '@', '&', and '%' of a string with a '~'. 일반적인정규표현식 129

130 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) :45 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) 저자 Alice Rischert 제 1 부로돌아가기백레퍼런스 (Backreference) 정규표현식의유용한기능의하나로재활용을위해서브표현식을저장하는기능이제공됩니다. 이기능을백레퍼런스 (backreferencing) 라부릅니다 ( 상세한설명은표 10 참고 ). 백레퍼런스를이용하여패턴을새로운위치에맞교체하거나반복적인단어또는문자를검색하는등의고급대체기능을구현할수있습니다. 서브표현식과일치하는문자열은임시버퍼에저장됩니다. 이버퍼에는왼쪽에서오른쪽순서로숫자가매겨지며 \digit 형태로표현됩니다. 여기서 digit은 1과 9 사이의숫자를의미하며각숫자에해당하는서브표현식과매치됩니다. 아래예제는서브익스프레션에대한백레퍼런스를이용하여 Ellen Hildi Smith라는이름을 Smith, Ellen Hildi로변환하고있습니다. SELECT REGEXP_REPLACE( 'Ellen Hildi Smith', '(.*) (.*) (.*)', '\3, \1 \2') FROM dual REGEXP_REPLACE('EL Smith, Ellen Hildi 위의 SQL 구문은각각괄호로묶인 3 개의서브표현식을사용하고있습니다. 각서브표현식은임의의단일문자와매치되는메타문자 (.) 와, 임의의문자 (newline 제외 ) 와 0 회또는그이상매치되는 * 메타문자를연이어사용하고있습니다. 각서브표현식사이에사용된공백기호역시매치되어야합니다. 괄호로묶인서브표현식에의해캡처된값은 \digit에의해참조할수있습니다. 따라서첫번째서브표현식에는 \1이, 두번째표현식에는 \2이할당됩니다. 이백레퍼런스들은함수의마지막매개변수 (\3, \1 \2) 로사용되어, 서브문자열을대체하고 ( 쉼표와공백을포함하는 ) 포맷으로표현하는용도로활용되고있습니다. 표 11은정규표현식의개별컴포넌트에대한상세정보를설명하고있습니다. 백레퍼런스는값을대체, 포맷하는용도로유용하게활용되며, 서로인접한값을찾기위해이용할수도있습니다. 다음예제는 REGEP_SUBSTR 함수를이용하여공백기호로구분된알파벳 / 숫자값의중복사례를검색하고있습니다. 이함수가반환된서브문자열을통해 is 문자열이반복되고있음을알수있습니다. SELECT REGEXP_SUBSTR( 'The final test is is the implementation', '([[:alnum:]]+)([[:space:]]+)\1') AS substr FROM dual SUBSTR is is 매치매개변수옵션앞에서예시한예제들을통해정규표현식연산자와함수에매치매개변수 (match parameter) 를추가적으로활용할수있음을눈치채셨을것입니다. 이매개변수는대소문자구분, newline 문자의매치, 멀티라인입력의보존등을지원합니다. 정규표현식의활용사례 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) 130

131 Y정규표현식은단순쿼리이외에도 PL/SQL 언어처럼 SQL 연산자, 함수가사용되는경우라면언제든적용이가능합니다. 또정규표현식을이용하여값의검증, 생성, 추출을위한기능을트리거형태로구현할수있습니다. 다음예제는입력된데이터에대해컬럼제약조건을검증하기위해 REGEXP_LIKE 연산자를사용하는방법을예시하고있습니다. 이쿼리는 INSERT, UPDATE 작업이발생하는경우입력이사회보장번호의포맷과일치하는지확인합니다 또는 과같은포맷으로표현된사회보장번호는컬럼의제약조건을만족합니다. 데이터는 3 개의숫자로시작하여하이픈으로연결된후, 다시두개의숫자, 하이픈, 마지막으로 4 개의숫자로표현됩니다. 또는사회보장번호를 9 개의연속적숫자로표현하는것도가능합니다. 수직기호 ( ) 는두가지옵션중선택이가능함을의미합니다. ALTER TABLE students ADD CONSTRAINT stud_ssn_ck CHECK (REGEXP_LIKE(ssn, '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4} [[:digit:]]{9})$')) ^, $. 기호는가장앞에또는가장뒤에오는문자를무시할것을지정하고있습니다. 정규표현식이두개이상의라인으로분절되거나표현식에불필요한공백기호가포함되지않도록주의하시기바랍니다. 표 12는위정규표현식예제에서사용된각각의컴포넌트에대해설명하고있습니다. 정규표현식과기존 SQL 기능의비교 Next Steps 정규표현식은기존에사용되던 LIKE 연산자와 INSTR, SUBSTR, and REPLACE 함 Oracle Database 10g 페이지 : 수와비교했을때여러가지이점을제공하고있습니다. 기존의 SQL 함수는패턴매칭 /products/database/oracle10g/index.html 을전혀지원하지않고있습니다. 문자의매칭을지원하는컴포넌트는 LIKE연산자가유일하며 %, _ 와일드카드문자가지원됩니다. 하지만 LIKE 연산자는표현식의반복, 복잡한대체패턴, 문자영역, 문자리스트, POSIX 문자클래스등을지원하지않습니다. 또새로운정규표현식함수는반복단어와패턴의맞교환 (swap) 을지원한다는장점이있습니다. 본문서에서제시된예제들이정규표현식의개념과활용방법을이해하는데도움이되었기바랍니다. 툴킷확장을위한유용한도구정규표현식은복잡한문제의해결을가능하게하는매우강력한기능입니다. 정규표현식은기존 SQL 함수로는흉내내기어려운다양한기능을지원합니다. 정규표현식의사용법이다소복잡해보이기는하지만, 그기본적인구성요소만익혀도 SQL뿐아니라다른프로그래밍언어에서도유용한도구로활용하실수있을것입니다. 원하는패턴을얻기위해서는여러차례의시행착오가불가피할수도있습니다. 하지만어느누구도정규표현식의우아함과강력한기능을결코무시할수없을것입니다. Alice Rischert ([email protected]) 는콜럼비아대학컴퓨터테크놀로지 / 애플리케이션프로그램의데이터베이스애플리케이션개발 / 설계트랙담당회장입니다. 그녀는 <Oracle SQL Interactive Workbook 2nd edition(prentice Hall, 2002)> 과조만간출판예정인 <Oracle SQL by Example(Prentice Hall, 2003)> 의저자이기도합니다. Rischert 는포춘 100대기업에서데이터베이스설계, DBA, 프로젝트리더로 15 년간의경력을보유하고있으며오라클데이터베이스는버전 5부터사용해온베테랑입니다. 표 1: 메타문자의앵커 (anchor) 적용 Metacharacter ^ 설명 라인의시작부분에표현식을적용 $ 라인의끝부분에표현식을적용 표 2: 반복연산자 (Repetition Operator, Quantifier) Quantifier 설명 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) 131

132 * 0 회또는그이상횟수로매치? 0 회또는 1 회매치 + 1 회또는그이상횟수로매치 {m} {m,} {m, n} 정확히 m 회매치 최소한 m 회매치 최소 m 회, 최대 n 회매치 표 3: 사전정의된 POSIX 문자클래스 Character Class [:alpha:] [:lower:] [:upper:] [:digit:] [:alnum:] [:space:] [:punct:] [:cntrl:] [:print:] 설명알파벳문자소문자알파벳문자대문자알파벳문자숫자알파벳 / 숫자출력되지않는공백문자 ( 예 : carriage return, newline, vertical tab, form feed 등구두점기호 ( 출력되지않는 ) 컨트롤문자출력가능한문자 표 4: 대체매칭및표현식의그룹화 Metacharacter 설명 Alternation 대체문자를구분 ( 그룹핑연산자 () 와함께사용하기도함 ) ( ) Group 반복연산자또는백레퍼런스 (backreference) 를위해대체유닛을서브표현식으로그룹화 (" 백레퍼런스 " 섹션 참고 ) [char] Character list I 문자리스트. 문자리스트내부에위치하는대부분의메타문자는일반문자로인식됨. 그예외가캐럿 (^) 기호와 하이픈 (-) 기호 표 5: REGEXP_LIKE 연산자 문법 REGEXP_LIKE(source_string, pattern 설명 source_string 은문자데이터타입지원 (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, NCLOB 단 LONG 은제외 ). pattern 매개변수는정규표현식을참조하는또 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) 132

133 [, match_parameter]) 다른이름. 옵션으로제공되는 match_parameter 를이용하여 newline 문자의처리, 멀티라 표 6: REGEXP_INSTR 함수 인포맷의유지, 대소문자구분등을처리할수있음. 문법 REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]]) 설명 pattern을검색하여패턴의첫번째위치를반환. 필요한경우 start_position y을이용하여검색을시작할위치를지정할수있음. occurrence매개변수는같은패턴을여러번검색하고자하는경우에사용 ( 디폴트는 1). return_option은패턴의시작위치를반환 ( 디폴트값은 0). 1로설정되는경우매치된패턴의다음문자위치를반환. 표 7: 5 자리숫자 + 4 자리 Zip-Code 표현식에대한설명 문법 설명 매치되어야하는빈공백 [:digit:] POSIX "numeric digit" 클래스 ] 문자리스트 (character list) 의끝 {5} 문자리스트에대해정확히 5 회반복 ( 서브표현식 (subexpression) 의시작부분 - 하이픈 (-) 문자 ( 문자리스트내의 range 메타문자로사용되지않았음에주의 ) [ 문자리스트의시작부분 [:digit:] POSIX [:digit:] 클래스 [ 문자리스트의시작부분 ] 문자리스트의끝부분 {4} 문자리스트를정확히 4 회반복 ) 서브표현식을묶는괄호기호?? 반복연산자는그룹핑된서브표현식을 0 회또는 1 회매치하여옵션으로 4 자리코드를사용할수있게함 $ 검색위치를라인의끝부분으로지정하기위한앵커메타문자 표 8: The REGEXP_SUBSTR 함수 문법 REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]]) 설명 REGEXP_SUBSTR 함수는패턴애매치되는서브문자열을반환. 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) 133

134 표 9: TheREGEXP_REPLACE 함수 문법 REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]]) 설명 매치된패턴을지정된 replace_string 으로대체하고, 복잡한검색 / 대체작업을가능하 게하는함수. 표 10: 백레퍼런스메타문자 (Backreference Metacharacter) Metacharacter 설명 \digit Backslash 백슬래시뒤에 1-9 사이의숫자를명시하여, 괄호처리된 N 번째서브표현식을참조하기위해사용. ( 참고 : 백슬래시는정규표현식에서다른의미로사용되기도함. 문맥에따라 Escape 문자를의미할수도있음. 표 11: 패턴 - 스왑 (Pattern-Swap) 정규표현식의설명 정규표현식아이템 설명 ( 첫번째서브표현식의시작. (newline 을제외한 ) 임의의단일문자와매치 * 반복연산자 - 앞의. 메타문자와 0 회에서 n 회까지매치 ) 첫번째서브표현식의마지막. 매치결과는 \1 에캡처됨 ( 예제에서는 Ellen 을캡처 ) 공백기호 ( 두번째서브표현식의시작. newline 을제외한임의의단일문자와매치 * 반복연산자 - 앞의. 메타문자와 0 회에서 n 회까지매치 ) 두번째서브표현식의마지막 ; 매치결과는 \2 에캡처됨 ( 예제에서는 Hildi 를저장 ). 공백기호 ( 세번째서브표현식의시작. newline 을제외한임의의단일문자와매치 * 반복연산자 - 앞의. 메타문자와 0 회에서 n 회까지매치 ) 세번째서브표현식의끝부분. 매치결과는 \3 에캡처 ( 예제에서는 Smith 를저장 ). 표 12: Social Security Number 정규표현식의설명 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) 134

135 정규표현식아이템 설명 ^ 라인문자의시작 ( 정규표현식은매치되기이전의문자를가져올수없음.) ( 서브표현식을시작하고 메타문자를이용하여대체목록을제공 [ 문자리스트 (character list) 의시작 [:digit:] POSIX "numeric digit" 클래스 ] 문자리스트의끝부분 {3} 문자리스트를정확하게 3 회반복적용 - 하이픈 [ 문자리스트의시작 [:digit:] POSIX numeric digit 클래스 ] 문자리스트의끝부분 {2} 문자리스트를정확하게 2 회반복적용 - 또다른하이픈기호 [ 문자리스트의시작 [:digit:] POSIX numeric digit 클래스 ] 문자리스트의끝부분 {4} 문자리스트를정확하게 4 회반복적용 대체 (alternation) 메타문자 - 첫번째선택을종료하고두번째대체표현식을시작 [ 문자리스트의시작 [:digit:] POSIX numeric digit 클래스. ] 문자리스트의끝부분 {9} 문자리스트를정확하게 9 회반복 ) 대체를위해사용된서브표현식그룹을종료하는괄호기호 $ 라인의끝부분을의미하는앵커메타문자 ( 매치된패턴이후에어떤문자도뒤따라올수없음 ) 정규표현식을이용한 SQL 구문의개선 (1 부에이어계속 ) 135

136 오라클정규표현식 - 1 부 :42 Inside Oracle Database 10g 정규표현식을이용한 SQL 구문의개선 저자 - Alice Rischert Oracle Database 10g의정규표현식기능을텍스트데이터처리를위한강력한도구로활용할수있습니다. Oracle Database 10g에추가된새로운기능을이용하여문자데이터의검색, 처리능력을극적으로개선할수있습니다. 정규표현식 (regular expression) 이라불리는이기능은, 텍스트패턴을기술하기위한일종의표기법으로, 이미오래전부터다양한프로그래밍언어와 UNIX 유틸리티를통해지원되어왔습니다. 오라클의정규표현식은 SQL 함수와 WHERE절연산자의형태로제공됩니다. 정규표현식에익숙하지않은독자라면, 이문서를통해전혀새롭고강력한기능을체험하실수있을것입니다. 또정규표현식에이미친숙한독자분들은, Oracle SQL 언어의문맥에이기능을적용하는방법을이해하는기회로활용하실수있습니다. 정규표현식이란? 정규표현식은하나또는그이상의문자열과메타문자 (metacharacter) 로구성됩니다. 가장단순한형태의정규표현식은 cat과같은단하나의문자열로만구성될수있습니다. 이정규표현식은문자 c와문자 a, 문자 t의순서를갖는패턴매치문자열로 cat, location, catalog 등의문자열과매치됩니다. 메타문자는정규표현식을구성하는문자들을처리하는방법을명시하기위한알고리즘을제공합니다. 다양한메타문자의의미를이해한다면, 정규표현식이텍스트데이터를비교하고대체하는용도로매우유용하게활용될수있음을금방깨닫게되실것입니다. 데이터의검증, 중복단어의확인, 불필요한공백의제거, 문자의파싱 (parsing) 등정규표현식의활용방법은실로다양합니다. 정규표현식을이용하여전화번호, 우편번호, 이메일주소, 주민등록번호, IP 주소, 파일이름, 경로이름등을검증할수도있습니다. 또 HTML 태그, 숫자, 날짜, 기타특정텍스트데이터와일치하는패턴을확인하고다른패턴으로대체하는것이가능합니다. Oracle Database 10g에서정규표현식사용하기오라클에새로추가된기능으로 Oracle SQL REGEXP_LIKE 연산자, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE 함수등이있습니다. 이함수와연산자는기존의 LIKE 연산자와 INSTR, SUBSTR, REPLACE 함수를보완하는효과를제공합니다. 실제로새로운기능들은기존연산자및함수와유사하지만훨씬강력한패턴매칭환경을구현하고있습니다. 검색의기준이되는데이터는간단한문자열일수도있고데이터베이스테이블의문자컬럼에저장된대량의텍스트일수도있습니다. 정규표현식을이용하면이전에는생각도못했던유연한방법으로데이터를검색, 대체, 검증할수있습니다. 정규표현식의기본적예제새로운기능을사용해보기기전에, 몇가지메타문자의의미를이해해보기로합시다. 마침표 (.) 는정규표현식에존재하는모든문자 (newline 제외 ) 와매칭됩니다. 예를들어정규표현식 a.b는문자 a, (newline을제외한 ) 임의의단일문자, 그리고문자 b의순서로구성된문자열과매칭됩니다. 문자열 axb, xaybx, abba는모두이정규표현식에정의된패턴을포함하고있으므로매치가가능합니다. 라인이 a로시작하여 b로끝나는, 3 개문자로구성된문자열을매칭하고자하는경우에는앵커 (anchor) 가사용되어야합니다. 캐럿 (^) 메타문자는라인의시작을, 달러 ($) 기호는라인의끝을의미합니다 ( 표 1 참고 ). 따라서정규표현식 ^a.b$ 는 aab, abb, axb와같은문자열과매칭됩니다. LIKE 연산자에서이와동일한기능을수행하려면 a_b 패턴을사용해야합니다. 여기서밑줄기호 (_) 는단문자와일드카드를의미합니다. 기본적으로, 정규표현식의개별문자또는문자리스트는단한번만매칭됩니다. 정규표현식은문자가여러번반복출현되는조건을지정하기위한? 반복연산자 (repetition operator) 를제공합니다 ("quantifier" 라부르기도합니다 ). 문자 a로시작해서 b로끝나는문자열매 오라클정규표현식 - 1 부 136

137 칭을위한정규표현식이아래와같습니다 : ^a.*b$. * 메타문자는임의의메타문자 (.) 가 0 번, 한번, 또는여러번반복되는조건에매칭됩니다. LIKE 연산자에서는이와동일한연산자로 a%b를지원합니다. 여기서퍼센트 (%) 기호는임의문자가 0 번, 한번, 또는여러번반복됨을의미합니다. 표 2는반복연산자의전체목록을보여주고있습니다. 이표에제시된예를통해정규표현식이기존의 LIKE와일드카드문자보다훨씬뛰어난유연성을제공함을확인할수있습니다. 표현식에괄호를씌우는경우, 서브표현식 (subexpression) 으로활용됩니다. 서브표현식은임의의횟수만큼반복될수있습니다. 예를들어, 정규표현식 b(an)*a는 ba, bana, banana, yourbananasplit등과매치됩니다. 오라클의정규표현식은 POSIX(Portable Operating System Interface) 문자클래스를지원합니다 ( 표 3 참고 ). 따라서검색하는문자의유형을세부적으로정의하는것이가능합니다. 알파벳이아닌문자를검색하는조건을 LIKE 연산자로작성한다면, WHERE 절이훨씬복잡한형태로구현되어야할것입니다. POSIX 문자클래스는반드시대괄호 ([]) 로묶여져야합니다. 예를들어, 정규표현식 [[:lower:]] 는소문자와매치되며 d [[:lower:]]{5} 는 5 개의연속적인소문자와매치됩니다. POSIX 문자클래스와별도로, 개별문자를문자리스트 (character list) 에포함시키는기능이제공됩니다. 예를들어정규표현식 ^ab[cd]ef$ 는문자열 abcef, abdef와매치됩니다. 여기서 c 또는 d 두개의문자중하나가사용되고있어야합니다. 문자리스트내부에위치하는대부분의메타문자는일반문자로인식됩니다. 그예외가캐럿 (^) 기호와하이픈 (-) 기호입니다. 일부메타문자는문맥에따라다른의미를갖습니다. 이때문에정규표현식이무척복잡해보일수도있습니다. 캐럿 ^이그한가지예입니다. 이기호를문자리스트의첫번째문자로사용되는경우에는, 문자리스트의반대조건 (negation) 을의미합니다. 따라서 [^[:digit:]] 은숫자가아닌문자로구성된패턴과매칭되는반면 ^[[:digit:]] 은숫자로시작되는패턴과매칭됩니다. 하이픈 (-) 은영역 (range) 을의미합니다. 정규표현식 [a-m] 은 a와 m 사이의임의의문자와매칭됩니다. 하지만 [-afg] 의경우처럼하이픈이문자리스트의첫번째문자로사용된경우에는실제하이픈문자를의미합니다앞에서괄호를사용하여서브표현식을구현하는방법을예시한바있습니다. 서브표현식에서는수직기호 ( ) 메타문자를사용하여여러개의대체문자를지정할수있습니다. 예를들어, 정규표현식 t(a e i)n은문자와 t n 사이에오는 3 개의대체문자를지정하고있습니다. tan, ten, tin, Pakistan 등의문자열은매치되지만 teen, mountain, tune 등은매치되지않습니다. 또정규표현식 t(a e i)n을문자리스트 t[aei]n으로표현할수도있습니다. 표 4는이러한메타문자들을요약하고있습니다. 지금까지설명한것말고도다양한메타문자가있지만, 여기에서는본문서에서예제로사용되는정규표현식을이해할수있는정도만이해하고넘어가기로합니다. REGEXP_LIKE연산자 REGEXP_LIKE오라클데이터베이스에적용가능한정규표현식기능을제공합니다. 표 5는 REGEXP_LIKE의문법을보여주고있습니다아래 SQL 쿼리의 WHERE 절에서사용된 REGEXP_LIKE 연산자는정규표현식 [^[:digit:]] 을만족하는패턴의 ZIP 컬럼을검색하고있습니다. 이조건절을이용하여, ZIPCODE 테이블로부터숫자가아닌문자를포함하는 ZIP 컬럼이포함된모든로우를가져올수있습니다. SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]') ZIP ab xy 007ab abcxy 이정규표현식은메타문자, 좀더정확히말하면콜론과대괄호로묶인 POSIX 문자클래스 digit만을사용하고있습니다. [^[:digit:]] 에서두번째로사용된대괄호는문자클래스리스트를묶는용도로사용됩니다. 앞에서설명한것처럼 POSIX는문자리스트를구성하는용도로만사용되므로이와같은처리가필요합니다. REGEXP_INSTR 함수 오라클정규표현식 - 1 부 137

138 이함수는패턴의시작위치를반환하며, 따라서 INSTR 함수와유사한형태로동작합니다. REGEXP_INSTR함수의사옹방법은표 6에서확인할수있습니다. 두함수의가장중요한차이는 REGEXP_INSTR를이용하는경우특정문자열이아닌패턴을지정할수있으며, 따라서훨씬유연한검색이가능하다는사실입니다. 다음예에서는 REGEXP_INSTR을사용하여 Joe Smith, Berry Lane, San Joseph, CA 91234문자열에서 5 개의숫자로구성된우편번호패턴의시작부분을반환하고있습니다. 정규표현식 [[:digit:]]{5} 를사용하는경우우편번호가아닌집주소번호의시작위치를얻게됩니다 ( 처음으로검색되는 5 개연속숫자패턴이 10045이기때문입니다 ). 따라서 $ 메타문자를사용하여표현식의앵커를라인끝부분으로지정해야합니다. 이렇게하면집주소번호에관계없이우편번호의시작위치를얻을수있습니다. SELECT REGEXP_INSTR('Joe Smith, Berry Lane, San Joseph, CA 91234', '[[:digit:]]{5}$') AS rx_instr FROM dual RX_INSTR 좀더복잡한패턴의작성앞의예의우편번호패턴을확장하여네가지숫자를포함하는패턴을만들어보기로합시다. 새로작성된패턴이아래와같습니다 : [[:digit:]]{5}(-[[:digit:]]{4})?$. 소스문자열이 5 개숫자로종료되든, 또는 "5 개숫자 + 4 자리우편번호 " 포맷을갖든, 패턴의시작위치를얻을수있습니다. SELECT REGEXP_INSTR('Joe Smith, Berry Lane, San Joseph, CA ', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual STARTS_AT 위의예에서괄호로묶인서브표현식 (-[[:digit:]]{4}) 는반복연산자? 로지정된조건에따라 0 회또는 1 회반복됩니다. 다시말하지만, 기존의 SQL 함수를이용하여같은결과를얻어내려면아무리 SQL 전문가라해도쉽지않은작업이될것입니다. 표 7은정규표현식을구성하는각문자와메타문자의의미를설명하고있습니다. REGEXP_SUBSTR함수 REGEXP_SUBSTR 함수는 SUBSTR 함수와마찬가지로문자열의일부를추출합니다. 표 8은새로운함수의사용법을설명하고있습니다. 아래예제에서는 [^,]*, 패턴에매치되는문자열이반환됩니다. 정규표현식은공백에이어사용된쉼표를검색하고, 쉼표가아닌문자가 0 회또는그이상반복되는패턴을검색 ([^,]*) 한후마지막으로또다른쉼표를검색합니다. 이패턴은쉼표로구분된문자열 (commaseparated values) 과유사한배열을갖습니다. SELECT REGEXP_SUBSTR('first field, second field, third field', ', [^,]*,') FROM dual REGEXP_SUBSTR('FIR , second field, REGEXP_REPLACE 함수특정문자열을다른문자열로대체하는기존의 REPLACE SQL 함수의기능을잠시되짚어보겠습니다. 데이터의텍스트에필요이상의공백기호가존재하는상황에서, 이를단일공백기호로대체하는경우를가정해봅시다 REPLACE 함수를사용할때에는대체할공백기호의 오라클정규표현식 - 1 부 138

139 숫자를정확하게지정해야합니다. 하지만, 필요없는공백의수가일정하리라는보장은없습니다. 아래예는 Joe와 Smith사이에 3 개의공백기호가존재하는경우를검색하고있습니다. REPLACE 함수의매개변수는두개의공백기호를하나의공백기호로대체할것을명시하고있습니다. 하지만 Joe와 Smith 사이에 3 개의공백기호가존재하는경우에는여전히필요없는공백이하나남게됩니다. SELECT REPLACE('Joe Smith',' ', ' ') AS replace FROM dual REPLACE Joe Smith REGEXP_REPLACE 한층개선된문자열대체기능을제공합니다. 그사용법은표 9에서설명되고있습니다. 아래쿼리는두개또는그이상의공백기호를하나의공백기호로대체합니다. ( ) 서브표현식은하나의공백기호를포함하며 {2,} 의조건에의해지정된대로 2 회또는그이상반복되는조건을명시합니다. SELECT REGEXP_REPLACE('Joe Smith', '( ){2,}', ' ') AS RX_REPLACE FROM dual RX_REPLACE Joe Smith 오라클정규표현식 - 1 부 139

140 dcmctl Fusion Middleware command line tool :34 About the dcmctl Fusion Middleware command line tool The dcmctl tool of Fusion Middleware can be used to change the configuration of the components of Fusion Middleware and to deploy / undeploy a Java Web application. For changing the configuration of components, dcmctl can modify XML configuration files or configuration data located in the metadata. Here are the tasks that you can accomplish using the tool : List managed components dcmctl listcomponents Refresh configuration from the repository dcmctl resyncinstance -i <instance name> ex.: dcmctl resyncinstance -i oracleas Update repository information from local configuration dcmctl updateconfig -ct <component type> ex.: dcmctl updateconfig -ct oc4j Create a new OC4J instance dcmctl createcomponent -ct oc4j -co <component name> ex.: dcmctl createcomponent -ct oc4j -co FONC Deploy a J2EE application dcmctl deployapplication -f <EAR or WAR file> -a <application name> -co <component name> ex.: dcmctl deployapplication -f webapplication.ear -a webapplication -co FONC Undeploy a J2EE application dcmctl Fusion Middleware command line tool 140

141 dcmctl undeployapplication -a <application name> -co <component name> ex.: dcmctl undeployapplication -a webapplication -co FONC If you don't specify the -co option, the default OC4J instance will be used. Launch taks in batch mode dcmctl shell -f <filename&t; ex.: dcmctl shell -f deploywebapptasks.txt dcmctl Fusion Middleware command line tool 141

142 오옷.. 몰랐던함수 :38 1. to_single_byte select * from 테이블명 where to_single_byte( 컬럼명 ) like '%S%' => 전각문자등으로입력된데이타를조회할때사용 2. NVL2 요건 decode + nvl 이넹.. ㅎㅎ 오옷.. 몰랐던함수.. 142

143 Explain plan/sql Trace file 보는법 :18 Explain plan/sql Trace file 보는법 1. Explain Plan 보는법 SQL 문장을사용시중요한부분중의하나가 SQL TUNING 이다. SQL 문장을사용시 많이고민하고생각하지만의도대로안되는경우가많이있습니다. 이를확인하는 방법중의하나가 EXPLAIN 을사용하는것입니다. 사용하는방법도중요하지만분석하는방법이더욱중요함을강조하고싶습니다. 사용법 SQL> EXPLAIN PLAN SET STATEMENT_ID='TEXT' FOR 2 SELECT * FROM TAB WHERE TNAME = 'KKK' ; 2번째라인은분석하고자하는 SQL 문장을쓰시면됩니다. 확인법 SQL> SELECT LPAD(' ',2*(LEVEL-1)) OPERATION OPERATION, OPTIONS,OBJECT_NAME POSITION FROM PLAN_TABLE START WITH ID = 0 AND STATEMENT_ID = 'TEXT' CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'TEXT' ; 위의 SQL 문장의결과는 2번째 LINE 의 SQL 문장의 ACCESS PATH를보여줍니다. 다음은예를하나들겠습니다. plan 의결과가다음과같을때 OPERATION OPTION OBJNM POS SELECT STATEMENT 2 NESTED LOOPS 1 3 NESTED LOOPS 1 4 NESTED LOOPS 1 5 TABLE ACCESS BY ROWID PQASC01T 1 6 INDEX RANGE SCAN PQASC01T_PK 1 7 TABLE ACCESS BY ROWID PQDLI03T 2 8 INDEX RANGE SCAN PQDLI03T_X INDEX RANGE SCAN PQDLI06T_PK 2 10 TABLE ACCESS BY ROWID PQASC01T 2 Explain plan/sql Trace file 보는법 143

144 11 INDEX RANGE SCAN PQASC01T_PK 1 위의결과는 PQASC01T 와 PQDLI03T 라는두개의 TABLE 이 INDEX 를사용하므로아무 이상이없을겁니다. 그러나위의 TABLE 중 PQASC01T 의건수가적고상대적으로 PQDLI03T 의건수가 많을경우는아래와위가바뀌어야하지않을까요특히 PQASC01T 가건수가적은 TABLE 일경우는 FULL TABLE SCAN 하는것도하나의방법이지요. 5 번 LINE 의 NESTED LOOPS 는작은 TABLE(PQASC01T) 을찾고그리고큰 TABLE(PQDLI03T) 을찾는것이므로시간이많이걸리겠지요. 위의 SQL 문장은 PQASC01T 에있는 INDEX 를사용하지않거나 PQDLI03T 의 INDEX 를 먼저사용하는것이겠지요. INDEX 를사용하지않는방법은여러가지가있습니다. 해당 COLUMN 의값을변경하는경우도있고위치를바꾸는경우도있습니다. ( 인덱스컬럼의변형 (Suppressing) 을통해서가능 - 대용량데이터베이스솔루션 ( 이화식저 ) 1. 인덱스의활용참조 ) 2. SQL_TRACE 1. SQL Trace 란? Trace 파일은시스템을튜닝하는데필요한아주유효한정보를제공한다. 시스템전체에대해서 SQL_TRACE를수행시키면전체적인수행성능은 20%~30% 정도감소한다. SQL문의실행통계를 Session별로모아서 Trace 파일을만든다. SQL Parsing, Execute, Fetch를수행한횟수 CPU Time, Elapsed Time( 총경과시간 ) Disk( 물리적 ), Memory( 논리적 ) 읽기를수행한횟수 추출된 Row의수 라이브러리캐쉬 miss 수 SQL_TRACE에의해서생성되는 Trace 파일의확장자는.trc이다. TKPROF 유틸리티로읽을수있는파일을생성해야한다. 2. SQL_TRACE 를수행하는방법 SQL*Plus 에서는다음과같이세션을변경시켜야한다. SQL>alter session set sql_trace=true; 이렇게설정해두면실행되는질의문에대해서트레이스파일이생성된다. 생성되는위치는 init.ora에서정의한 user_dump_dest 디렉토리에 *.trc Explain plan/sql Trace file 보는법 144

145 형태로생성된다. 3. 트레이스파일보기 생성된트레이스파일은 (*.trc) 바로볼수없다. 그래서 tkprof라는유틸리티를사용하여생성된트레이스파일을분석이가능한형식으로전환하여준다. 이미생성된트레이스파일이나트레이스파일을생성하고있는중에도 tkprof를수행시킬수있다. 트레이스파일은 SQL문에대한실행계획뿐만아니라실행시간, 다양한옵션을이용하여분석하기쉬운형태등의정보를보여준다. tkprof는다음과같이실행시킬수있다. Usage: tkprof tracefile outputfile [explain=user/passwd] tracefile : 생성된트레이스파일명 outputfile : tkprof가출력하는텍스트파일명 ( 디폴트로확장자가.prf임) explain=user/passwd : 해당트레이스파일이수행된세션의사용자및패스워드 예 ) tkprof ccdb_ora_1124.trc 1124.txt explain=scott/tiger 4. 트레이스파일의분석 tkprof는정형화된리스트 ( 출력파일 ) 를생성한다. 생성된파일에는다음과같은내용들을포함하고있다. call count cpu elapsed disk query current rows Parse Execute Fetch total parse SQL문이파싱되는단계에대한통계이다. 새로파싱을했거나, 공유풀에서찾아온것도포함된다. 단, PL/SQL 내에서반복수행 (Loop) 된 SQL인경우에는한번만파싱된다. execute SQL문의실행단계에대한통계이다. UPDATE, INSERT, DELETE 문들은여기에수행한결과가나타난다. 전체범위방식으로처리된결과가여러건인경우는주로여기에많은값이나타나며 fetch에는아주적은값이나타난다. fetch Explain plan/sql Trace file 보는법 145

146 SQL문이실해되면서페치된통계이다. 부분범위방식으로처리된 SELECT문들이나전체범위처리를한후한건을추출하는경우 (AGGREGATE, 전체집계, Count 등 ) 는주로여기에많은값들이나타나고 execute에는아주적은값이나타난다. count SQL문이파싱된횟수, 실행된횟수, 페치가수행된횟수이다. cpu pares, execute, fetch가실제로사용한 CPU 시간이다.(1/100초단위 ) elapsed 작업의시작에서종료시까지실제소요된총시간이다. disk 디스크에서읽혀진데이타블록의수 query 메모리내에서변경되지않은블록을읽거나다른세션에의해변경되었으나아직 Commit되지않아복사해둔스냅샷블록을읽은블록의수이다. SELECT문에서는거의가여기에해당하며 UPDATE, DELETE, INSERT 시에는소량만발생한다. current 현세션에서작업한내용을 Commit하지않아오로지자신에게만유효한블록 (Dirty Block) 을액섹스한블록수이다. 주로 UPDATE, INSERT, DELETE 작업시많이발생한다. SELECT 문에서는거의없으나아주적은양인경우가대부분이다. rows SQL문을수행한결과에의해최종적으로액세스된로우의수이다. 서브쿼리에의해서추출된로우는제외된다. 만약 SUM, AVG, MAX, MIN, COUNT 등의그룹함수를사용한경우라면큰의미가없다. 5. 분석결과의예 트레이스의중간부분에 'Misses im library cache during parse:1' 이라는문장이있다. 이것은공유 SQL 영역에서파싱된결과를찾지못하여실제파싱작업을하게되었다는것을의미한다. 최종적으로추출된로우의수는적으나많은 CPU 시간이소요되었다면이것은분명히적절한액세스경로로수행되지않았음을의미한다. CPU 시간과 ELAPSED 시간의차이는적을수록좋다. 만약 CPU시간에비해 ELAPSED 시간이훨씬많다면, 그원인은다음중하나일가능성이높다. 주변의다른세션에서많은부하를발생시켜시스템전체에부하가많이걸려있는경우 어플리케이션의문제이거나다량의데이타처리에따른 I/O 병목현상이발생한경우 Explain plan/sql Trace file 보는법 146

147 disk, query, current의숫자는적을수록좋다. 이숫자들이커다는것은메모리공유영역의적중률 (Hit Ratio) 이낮다는것을의미한다. 다음은아주빠른응답이요구되는온라인프로세싱시스템의경우에서만적용되는규칙들이다. 모든 Execute 'CPU' 가 1초보다적어야한다. Parse 'CPU' 시간이 Parse당 0.01초보다적어야한다. 작은테이블 (200로우이하 ) 에서만전체테이블스캔이일어나게한다. sysdate만찾아오거나, 오직연산만하거나, 'SELECT... INTO...' 로값을복사하는경우를위해서 DUAL 테이블들을불필요하게사용하는것은모두없앤다. 동시에작업되는 SQL들은가능한 PL/SQL을사용한다. Explain plan/sql Trace file 보는법 147

148 EXPLAIN PALN :50 EXPLAIN PLAN( 실행계획 ) SQL문의실행계획을보면비효율이발생한원인을알수있고좋은실행계획이수립될수있도록하는방법을찾을수있다. 이러한실행계획을자주참조하는것이바로옵티마이저를이해할수있는최선책이며, SQL문장을튜닝할수있는능력을키울수있는가장좋은방법이다. 사용자들이 SQL문의액세스경로를확인하고튜닝을할수있도록 SQL문을분석하고해석하여실행계획을수립한후실행계획을테이블 (Plan_table) 에저장하도록해준다. EXPLAIN PLAN 명령은오라클옵티마이저에의해서 SELECT, UPDATE, INSERT 그리고 DELETE문의실행계획을보여준다. 실행계획은데이타를축출하기위해오라클이 SQL 문장을차례로실행하는작업방법을말한다. PLAN_TABLE의생성 PLAN_TABLE 생성스크립트 create table PLAN_TABLE statement_id varchar2(30), -- 사용자가지정한제목 timestamp date, -- 실행계획이수립된날짜와시간 remarks varchar2(80), -- 사용자가부여한주석 (comments) operation varchar2(30), -- AND-EQUAL,INDEX, SORT 등과같은실행연산자 options varchar2(30), -- BY ROWID, JOIN, FULL 등과같은실행옵션 object_node varchar2(128), -- 사용한데이타베이스링크 object_owner varchar2(30), -- 객체를생성한소유자 object_name varchar2(30), -- 테이블, 인덱스, 클러스터등의객체의이름 object_instance numeric, -- SQL문의 FROM절에기술된객체를좌에서우로부여한번호 object_type varchar2(30), -- UNIQUE, NON-UNIQUE INDEX 등의객체의종류 optimizer varchar2(255), -- CHOOSE, FIRST_ROW 등의현재의옵티마이져모드 search_columns numeric, -- 현재사용하지않음 id numeric, -- 수립된각실행단계에붙여진일련번호 parent_id numeric, -- 부모단계의일련번호 EXPLAIN PALN 148

149 position numeric, -- 부모 ID 를가지고있는자식 ID 간의처리순 cost cardinality bytes other_tag other numeric, numeric, numeric, varchar2(255), long); -- 다른필요한텍스트를저장하기위한필드 EXPLAIN PLAN 명령의사용 EXPLAIN PLAN문을사용하려면먼저 PLAN_TABLE이라는테이블을생성한다. 대부분은 Oracle_Home_directory/rdbms/admin/utlxplan.sql 이라는스크립트를실행한다. 이테이블 (PLAN_TABLE) 과동일한칼럼을가지는별도의테이블을만들어서사용해도된다.(INTO문에사용 ) PLAN_TABLE 테이블을생성했다면수행속도를향상시키고동일한 Statement_id 가생기지않도록다음과같은인덱스를생성시킨 SQL>CREATE UNIQUE INDEX PLAN_INDEX ON PLAN_TABLE(statement_id, id); EXPLAIN PLAN 명령의문법 EXPLAIN PLAN [SET STATEMENT_ID = 'text'] [INTO [schema.]table[@dblink]] FOR SQL_statement STATEMENT_ID ='text' 30자이내로사용자가부여할수있는해당실행문의제목 INTO table 출력물을지정할테이블이름. PLAN_TABLE 을사용하지않고사용자가생성한테이블에저장하고자하는경우에만사용 FOR SQL_statement 실행계획을세우고자하는 SQL문 (SELECT, INSERT, DELETE, UPDATE) 을기술한다 EXPLAIN PLAN 명령을실행 EXPLAIN PLAN EXPLAIN PALN 149

150 SET STATEMENT_ID='aa' FOR SELECT ENAME, SAL FROM EMP, DEPT WHERE emp.deptno=dept.deptno AND NOT EXISTS( SELECT * FROM EMP WHERE sal<1000); Explained. SELECT 문으로실행계획을본다. SQL> SELECT operation, options, object_name, id, parent_id, position 2 FROM plan_table 3 WHERE statement_id='aa' 4 ORDER BY id; OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION SELECT STATEMENT 0 40 FILTER NESTED LOOPS TABLE ACCESS FULL EMP TABLE ACCESS FULL DEPT TABLE ACCESS FULL EMP 또는, PLAN_TABLE 을 SELECT 하는아래의 SQL 문을만들어서실행계획을볼수있다. SELECT lpad(operation, length(operation)+2*(level-1)) decode(id,0,'cost Estimate:' decode(position,'0', EXPLAIN PALN 150

151 'N/A',position), null) '' options decode(object_name,null,null,':') rpad(object_owner, length(object_name)+1,',') object_name decode(object_type,'unique',' (U) ', 'NIN_UNIQUE', '(NU)',null) decode(object_instance,null,null,'(' object_instance ')') FROM PLAN_TABLE START with ID=0 AND STATEMENT_ID='&&id' CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='&&id'; 실행시킨 EXPLAIN PLAN 문의 STATEMENT_ID= 에서부여한제목을 id 에지정해주고다음과같은실행하면실행계획이생성된 SQL>DEF ID=aa old 9: START with ID=0 AND STATEMENT_ID='&&id' new 9: START with ID=0 AND STATEMENT_ID='aa' old 10: CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='&&id' new 10: CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='aa' LPAD(OPERATION,LENGTH(OPERATION)+2*(LEVEL-1)) DECODE(ID,0,'COSTESTIMATE:' DECO SELECT STATEMENTCost Estimate:54 FILTER HASH JOIN TABLE ACCESSFULL:SCOTTDEPT(2) TABLE ACCESSFULL:SCOTEMP(1) TABLE ACCESSFULL:SCOTEMP(3) 6 rows selected. SQL> EXPLAIN PALN 151

152 OPERATION 과 OPTION 의추가적인설명 OPERATION OPTION 설명 AGGREGATE GROUP BY 그룹함수 (SUM, COUNT 등 ) 를사용하여하나의로우가추출되도록하는처리 AND-EQUAL CONNECT BY CONCATENATION 인덱스머지를이용하는경우중복제거, 단일인덱스칼럼을사용하는경우 CONNECT BY 를사용하여트리구조로전개 단위액세스에서추출한로우들의합집합을생성 (UNION-ALL) COUNTING 테이블의로우수를센다. FILTER 선택된로우에대해서다른집합에대응되는로우가있다면제거하는작업 FIRST ROW 조회로우중에첫번째로우만추출한다. FOR UPDATE 선택된로우에락 (LOCK) 을지정한다. UNIQUE SCAN UNIQUE 인덱스를사용 ( 단한개의로우를추출 ) INDEX* RANGE SCAN NON-UNIQUE 한인덱스를사용 ( 한개이상의로우 ) RANGE SCAN DESCENDING RANGE SCAN 하고동일하지만역순으로로우를추출 INTERSECTION 교집합의로우를추출한다.( 같은값이없다 ) MERGE JOIN+ 먼저자신의조건만으로액세스한후각각을소트하여머지해가는조인 OUTER 위와동일한방법으로 OUTER JOIN 을한다. MINUS MINUS 함수를사용한다. 먼저어떤드라이빙테이블의로우를액세스한후그결과를이용해다른테이블 NESTED LOOPS+ OUTER 위와동일한방법으로 OUTER JOIN을한다. PROJECTION REMOTE 내부적인처리의일종 다른분산데이타베이스에있는객체를추출하기위해데이타베이스링크를사용 SEQUENCE 시퀀스를액세스한다. SORT TABLE ACCESS* AGGREGATE UNIQUE GROUP BY JOIN ORDER BY FULL CLUSTER HASH BY ROWID 그룹함수 (SUM, COUNT 등 ) 를사용하여하나의로우가추출되도록하는처리같은로우를제거하기위한소트액세스결과를 GROUP BY하기위한소트머지조인을하기위한소트 ORDER BY를위한소트전체테이블스캔클러스터액세스키값에대한해쉬알고리즘을사용 ROWID를이용하여테이블을추출 UNION 두집합의합집합을구한다.( 중복없음 ) 항상전체범위를구한다. VIEW 어떤처리에의해생성되는가상의집합 ( 뷰 ) 에서추출한다.( 주로서브쿼리에의해 * access methods + join operations EXPLAIN PALN 152

153 EXPLAIN PALN 153

154 오라클 PL/SQL EXCEPTION 유형 :48 NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR ZERO_DIVIDE ORA_01403 데이타를반환하지않은 SELECT 문 ORA_01422 두행이상을반환한 SELECT 문 ORA_01001 잘못된커서연산발생 ORA_ 으로나누기시도 DUP_VAL_ON_INDEX ORA_00001 고유한인덱스를갖는 column 에중복된값을삽입하려고할때 오라클 PL/SQL EXCEPTION 유형 154

155 EXPLAIN PLAN :39 - Oracle 10g 에서태스트한것입니다. EXPLAIN PLAN 이란? 사용자들이 SQL 문의액세스경로를확인하고 튜닝을할수있도록 SQL 문을분석하고해석하여실행계획을수립한후 실행계획을테이블 (plan_table) 에저장하도록해주는명령입니다. 1. PLAN TABLE의생성 Explain plan을 sql 에포함해서수행하면옵티마이저가실행계획까지만수립하여 plan_table에저장해둡니다. PLAN을사용하고자하는 USER로 SQLPLUS LOGIN한후 ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql 을수행하여 plan_table을생성합니다. C:>SQLPLUS scott/tiger SQL*Plus: Release Production on 화 10 월 10 16:41: Copyright (c) 1982, 2005, Oracle. All rights reserved. 다음에접속됨 : Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options 테이블이생성되었습니다. SQL> 2. PLUSTRACE ROLE 의생성 sqlplus "/ as sysdba" 로접속하여 PLUSTRACE ROLE 을생성합니다. ORACLE_HOME/sqlplus/admin/plustrce.sql 을수행하여 plustrace role 을생성합니다. C:>sqlplus "/ as sysdba" SQL*Plus: Release Production on 화 10 월 10 17:01: Copyright (c) 1982, 2005, Oracle. All rights reserved. 다음에접속됨 : EXPLAIN PLAN 155

156 Oracle Database 10g Enterprise Edition Release Production With the Partitioning, OLAP and Data Mining options SQL> SQL> drop role plustrace; drop role plustrace * 1행에오류 : ORA-01919: 롤 'PLUSTRACE'( 이 ) 가존재하지않습니다 SQL> create role plustrace; 롤이생성되었습니다. SQL> SQL> grant select on v_$sesstat to plustrace; 권한이부여되었습니다. SQL> grant select on v_$statname to plustrace; 권한이부여되었습니다. SQL> grant select on v_$mystat to plustrace; 권한이부여되었습니다. SQL> grant plustrace to dba with admin option; 권한이부여되었습니다. SQL> SQL> set echo off SQL> 3. PLUSTRACE Role 의부여 PLUSTRACE ROLE 을 plan 을사용하고자하는유저에게부여합니다. SQL> GRANT plustrace TO scott; 권한이부여되었습니다. 권한을부여한다음. 다시 plan을사용하는유저로접속을합니다. SQL> conn scott/tiger 연결되었습니다. autotrace 상태를 on 으로바꿉니다. SQL> SET AUTOTRACE ON ; SQL> EXPLAIN PLAN 156

157 SQL문을실행합니다. SQL> SELECT a.ename, a.sal, b.dname 2 FROM emp a, dept b 3 WHERE a.deptno = b.deptno; ENAME SAL DNAME SMITH 800 RESEARCH ALLEN 1600 SALES WARD 1250 SALES JONES 2975 RESEARCH MARTIN 1250 SALES BLAKE 2850 SALES CLARK 2450 ACCOUNTING SCOTT 3000 RESEARCH KING 5000 ACCOUNTING TURNER 1500 SALES ADAMS 1100 RESEARCH ENAME SAL DNAME JAMES 950 SALES FORD 3000 RESEARCH MILLER 1300 ACCOUNTING 14 개의행이선택되었습니다. Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 NESTED LOOPS (0) 00:00:01 2 TABLE ACCESS FULL EMP (0) 00:00:01 3 TABLE ACCESS BY INDEX ROWID DEPT (0) 00:00:01 * 4 INDEX UNIQUE SCAN PK_DEPT 1 0 (0) 00:00: Predicate Information (identified by operation id): access("a"."deptno"="b"."deptno") EXPLAIN PLAN 157

158 Statistics recursive calls 0 db block gets 134 consistent gets 10 physical reads 0 redo size 856 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 14 rows processed SQL> @C:oracleproduct10.2.0db_2sqlplusadminplustrce.sql EXPLAIN PLAN 158

159 Oracle10g 에서 CONNECT BY :38 Oracle10g 에서 CONNECT BY 의새로운기능들 oracle & mssql :37 Oracle10g 부터 CONNECT BY 절에서제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 기능에대해서알아보겠습니다. SQLPLUS scott/tiger SQL>SET LINESIZE 100 SQL>SET PAGESIZE 100 SQL>COL ename FORMAT A20 상관관계쿼리예제 SQL>SELECT LPAD(' ', 4*(LEVEL-1)) ename ename, empno FROM emp START WITH job='president' CONNECT BY PRIOR empno=mgr; ENAME EMPNO MGR JOB KING 7839 PRESIDENT JONES MANAGER SCOTT ANALYST ADAMS CLERK FORD ANALYST SMITH CLERK -- 여기서 START WITH job='president' 부분을 START WITH EMPNO = :AS_EMPNO 로바꿔서 해당사번에대한계층구조를구할수도있음.. START WITH 와 CONNECT BY 를이용해데이터를계층적인순서로조회할수있습니다. START WITH - 계층질의의루트 ( 부모행 ) 로사용될행을지정합니다.. Oracle10g 에서 CONNECT BY 159

160 - 서브쿼리를사용할수도있습니다. CONNECT BY - 이절을이용하여계층질의에서상위계층 ( 부모행 ) 과하위계층 ( 자식행 ) 의관계를규정합니다. - 보통 PRIOR 연산자를많이사용합니다.. - 서브쿼리를사용할수없습니다.. CONNECT BY 의실행순서는다음과같습니다. - 첫째 START WITH 절 - 둘째 CONNECT BY 절 - 세째 WHERE 절순서로풀리게되어있습니다. 데이터가많아질경우... - 첫째로풀리는 START WITH job='president' job 컬럼에 index 가생성되어있지않는다면 속도를보장할수없습니다. - 그리고둘째로풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의컬럼값이상수가 되기때문에 MGR 컬럼에 index 를생성하여야 CONNECT BY 의속도를보장할수있습니다. - 계층구조를 CONNECT BY, START WITH 로풀면부분범위처리가불가능하고 Desc 으로 표현하기가어렵습니다. CONNECT_BY_ROOT - 상관관계쿼리에서 LEVEL 이 0 인최상위로우의정보를얻어올수있습니다. SQL>SELECT LPAD(' ', 4*(LEVEL-1)) ename ename, empno, CONNECT_BY_ROOT empno "Root empno", level FROM emp START WITH job='president' CONNECT BY PRIOR empno=mgr; ENAME EMPNO Root empno LEVEL KING JONES Oracle10g 에서 CONNECT BY 160

161 SCOTT ADAMS FORD SMITH SYS_CONNECT_BY_PATH - 상관관계쿼리에서현재로우까지의 PATH 정보를쉽게얻어올수있습니다. SQL>COL path FORMAT A40 SQL>SELECT LPAD(' ', 4*(LEVEL-1)) ename ename, empno, SYS_CONNECT_BY_PATH(ename, '/') "Path" FROM emp START WITH job='president' CONNECT BY PRIOR empno=mgr; ENAME EMPNO Path KING 7839 /KING JONES 7566 /KING/JONES SCOTT 7788 /KING/JONES/SCOTT ADAMS 7876 /KING/JONES/SCOTT/ADAMS FORD 7902 /KING/JONES/FORD SMITH 7369 /KING/JONES/FORD/SMITH CONNECT_BY_ISLEAF - 상관관계쿼리에서로우의최하위레벨여부를반환합니다. SELECT LPAD(' ', 4*(LEVEL-1)) ename ename, empno, CONNECT_BY_ISLEAF "leaf", level FROM emp START WITH job='president' CONNECT BY NOCYCLE PRIOR empno=mgr; ENAME EMPNO leaf LEVEL KING JONES Oracle10g 에서 CONNECT BY 161

162 SCOTT ADAMS FORD SMITH Oracle10g 에서 CONNECT BY 162

163 오라클변환형함수 :02 - 변환형함수 TO_CHAR : 숫자나날짜를문자열로변환 TO_NUMBER : 문자를숫자로변환 TO_DATE : 문자를날짜로변환 - TO_CHAR에서숫자를문자로변환시에형식에사용되는요소 9 : 일반적인숫자를나타냄 0 : 앞의빈자리를 0으로채움 $ : dollar를표시함 L : 지역통화단위 (ex ). : 소숫점을표시함, : 천단위를표시함 - TO_CHAR에서날짜를문자로변환시에형식에사용되는요소 SCC : 세기를표시 S는기원전 (BC) YEAR : 연도를알파벳으로 spelling YYYY : 4자리연도로표시 YY : 끝의 2자리연도로표시 MONTH : 월을알파벳으로 spelling MON : 월의알파벳약어 MM : 월을 2자리숫자로표시 DAY : 일에해당하는요일 DY : 일에해당하는요일의약어 DDD,DD,D : 연도, 월, 일중의날짜를숫자로표시 HH, HH24 : (1-12), (0-23) 중의시간을표시 MI : 분을표시 SS : 초를표시 AM(A.M.),PM(P.M.) : 오전인지오후인지를표시 TO_CHAR( 문자값, 형식 ) 숫자를문자로변환 : TO_CHAR(350000,'$999,999') $350,000 숫자를날짜로변환 : TO_CHAR(SYSDATE,'YY/MM/DD') 95/05/25 TO_DATE( 문자값, 형식 ) : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY') 10-SEP-92 TO_NUMBER( 문자값 ) : TO_NUMBER('1234') 1234 오라클변환형함수 163

164 오라클 UPDATE 구문 :56 Basic Update Statements Update all records UPDATE SET = CREATE TABLE test AS SELECT object_name, object_type FROM all_objs; SELECT DISTINCT object_name FROM test; UPDATE test SET object_name = 'OOPS'; SELECT DISTINCT object_name FROM test; ROLLBACK; Update a specific record UPDATE SET = WHERE = SELECT DISTINCT object_name FROM test; UPDATE test SET object_name = 'LOAD' WHERE object_name = 'DUAL'; COMMIT; SELECT DISTINCT object_name FROM test Update based on a single queried value UPDATE SET = ( SELECT 오라클 UPDATE 구문 164

165 FROM WHERE ); CREATE TABLE test AS SELECT table_name FROM all_tables; ALTER TABLE test ADD (lower_name VARCHAR2(30)); SELECT * FROM test WHERE table_name LIKE '%A%'; UPDATE test t SET lower_name = ( SELECT DISTINCT lower(table_name) FROM all_tables a WHERE a.table_name = t.table_name AND a.table_name LIKE '%A%'); COMMIT; SELECT * FROM test; Update based on a query returning multiple values UPDATE SET (, ) = ( SELECT (, ) FROM WHERE = ); CREATE TABLE test AS SELECT t. table_name, t. tablespace_name, s.extent_management FROM user_tables t, user_tablespaces s WHERE t.tablespace_name = s. tablespace_name AND 1=2; desc test SELECT * FROM test; -- does not work UPDATE test SET (table_name, tablespace_name) = ( SELECT table_name, tablespace_name FROM user_tables); -- works INSERT INTO test (table_name, tablespace_name) SELECT table_name, tablespace_name FROM user_tables; 오라클 UPDATE 구문 165

166 COMMIT; SELECT * FROM test WHERE table_name LIKE '%A%'; -- does not work UPDATE test t SET tablespace_name, extent_management = ( SELECT tablespace_name, extent_management FROM user_tables a, user_tablespaces u WHERE t.table_name = a.table_name AND a.tablespace_name = u.tablespace_name AND t.table_name LIKE '%A%'); -- does not work UPDATE test t SET (tablespace_name, extent_management) = ( SELECT DISTINCT u.tablespace_name, u.extent_management FROM user_tables a, user_tablespaces u WHERE t.table_name = a.table_name AND a.tablespace_name = u.tablespace_name AND t.table_name LIKE '%A%'); rollback; -- works UPDATE test t SET (tablespace_name, extent_management) = ( SELECT DISTINCT u.tablespace_name, u.extent_management FROM user_tables a, user_tablespaces u WHERE t.table_name = a.table_name AND a.tablespace_name = u.tablespace_name) WHERE t.table_name LIKE '%A%'; COMMIT; SELECT * FROM test; Update the results of a SELECT statement UPDATE ( ) SET = ( SELECT FROM WHERE = ; conn hr/hr CREATE TABLE empbak AS SELECT * FROM employees; UPDATE employees 오라클 UPDATE 구문 166

167 SET salary = salary * 1.1; COMMIT; UPDATE employees t1 SET salary = ( SELECT salary FROM empbak t2 WHERE t1.employee_id = t2.employee_id); Multi-column UPDATE TABLE( ) SET...; conn hr/hr var bnd1 NUMBER var bnd2 VARCHAR2(30) var bnd3 NUMBER UPDATE employees SET job_id ='SA_MAN', salary = salary , department_id = 140 WHERE last_name = 'Jones' RETURNING salary*0.25, last_name, department_id INTO :bnd1, :bnd2, :bnd3; print bnd1 print bnd2 print bnd3 conn hr/hr variable bnd1 NUMBER UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100 RETURNING SUM(salary) INTO :bnd1; print bnd1 Update Object Table Update a table object UPDATE ( 오라클 UPDATE 구문 167

168 Sequence 이용 :20 Sequence 이용 시퀀스를만들면테이블에서사용할순차적인번호를생성하기위해시퀀스를이용할수있다. NEXTVAL 과 CURRVAL Pseudocolumn 을써서 Sequence 값을참조하라. 질의예제 : 지역 2 에 "Finance" 라는입력을삽입하라. SQL>INSERT INTO s_dept(id, name, region_id) 2 VALUES (s_dept_id, NEXTVAL, 'Finance', 2): 1 row selected. 질의예제 : S_DEPT_ID Sequence 에대한현재값을보라. SQL>SELECT s_dept_id,currval 2 FROM SYS,dual; NEXTVAL과 CURRVAL Pseudocolumn NEXTVAL은사용가능한다음시퀀스값을반환한다. - 매번고유한값을반환한다. CURRVAL은현재시퀀스값을구한다. -CURRVAL은 NEXTVAL사용후에사용되어야한다. 사용규칙을따르라. NEXTVAL과 CURRVAL 의사 Pseudocolumn NEXTVAL Pseudocolumn은명시된 Sequence에서다음값을추출하는데에사용된다. Sequence명.NEXTVAL을참조하면새로운번호가생성되고 CURRVAL은현재의시퀀스번호값을갖는다. CURRVAL이참조되기전에현사용자의세션에서 NEXTVAL이이용되어야합니다. Sequence명,CURRVAL이참조되면사용자프로세스에사용된최종 Sequence값이출력된다. NEXTVAL과 CURRVAL 사용규칙 다음에서 NEXTVAL과 CURRVAL을사용할수있다. - Subquery가아닌 SELECT 문의 SELECT List - INSERT 문의 Subquery SELECT List - INSERT 문의 VALUE 절 Sequence 이용 168

169 - UPDATE 문의 SET 절 다음에서 NEXTVAL과 CURRVAL을사용할수없다. - 뷰의 SELECT List - DISTINCT 키워드가있는 SELECT 문 - GROUP BY, HAVING, 또는 ORDER BY 절이있는 SELECT 문 - SELECT, DELETE, 또는 UPDATE 문의 Subquery - CREATE TABLE 또는 ALTER TABLE 명령의 DEFAULT 값 자세한설명은 Oracle7 Server SQL Reference, Release 7.2, "Pseudocolumn" 절과 "CREATE SEQUENCE" 을보라 Sequence 값의캐슁 Sequence 를메모리에캐쉬하면 Sequence 값을더빨리액세스할수있다. 다음 Sequence 값을요구하면캐쉬된 Sequence 에서 return 해준다. 증가없이다음시퀀스값보기 NOCACHE 로 Sequence 가생성될경우에만 USER_SEQUENCES 테이블을 query 를하여다음 Sequence 값을볼수있다. Sequence 이용 169

170 LOB DATA TYPE 의이해 :58 제품 : ORACLE SERVER 작성날짜 : < LOB DATA TYPE의이해 > PURPOSE LOB data type에대해알아본다. Explanation ) 4 가지의 LOB TYPE CLOB : Character LOB (1 byte character만사용 ), 4Giga 보다작은 data. BLOB : Binary LOB, <= 4Gig. NCLOB: National Character LOB (multibyte 로구성된 national characterset) <= 4Gig. BFILE : Binary file로 DB 에저장되는것이아니고, O/S 에저장 2) 사용하기 LOB 이 table 에저장되어질때, data (LOB VALUE) 와 LOB_LOCATOR 라불리는 pointer 가분리되어저장된다. 이는같은 table 에저장되어질수도다른 table 에저장되어질수도있다. drop table test_lobs / drop directory tmp_dir / create table test_lobs ( c1 number, c2 clob, c3 bfile, c4 blob ) LOB (c2) STORE AS (ENABLE STORAGE IN ROW) LOB (c4) STORE AS (DISABLE STORAGE IN ROW) / desc test_lobs Name Null? Type LOB DATA TYPE 의이해 170

171 C1 NUMBER C2 CLOB C3 BINARY FILE LOB C4 BLOB <LOB storage 특성 > PCTVERSION - LOB data 가 update 시 read consistency 를위해 old version 을보관하기위해사용하는 storage space 의 percent. 이는 default 값이 10 이다. CHUNK - 한번의 read, write 를통해 access하는 BLOB data 의 database block CLOB,BFILE,BLOB column 을같이 create 하는 table 은 BLOB column 의 data 는항상다른 row 에저장되어야한다. 몇개의 row 를 insert 해보자 empty_<lob>() function 은해당 column LOB locator 를 generate 하는 constructor 의역할을한다. 이 locator 가없이는 PL/SQL 에서 LOB data 를 access 할수없다. (1) 1 번째 row 를 insert : locator 없는입력 insert into test_lobs values (1,null,null,null) / (2) 2 번째 row 를입력 :"null" locator 즉 locator 는생성하나, pointer 는아무런값을갖지않는다. 그러나, BFILE 은 BFILENAME(null,null) 처럼 null directory 와 null file 을이용해서 initialize 는가능하나 empry locator 가존재하지않는다. insert into test_lobs values (2,EMPTY_CLOB(),null,EMPTY_BLOB()) / (3) 3 번째 row 를입력 : 4K까지 direct 하게입력이가능. 비록 locator 만 access 한다하더라도해당 data 까지포함되게되어있다. BLOB 이직접 insert 될때 string 은 hex 여야하므로 HEXTOROW 가행이되던지 UTL_RAW.CAST_TO_RAW('the string') 를 call 해서값이 covert 되도록해야한다. 즉, '48656C6C6F' = 'Hello' 이다. insert into test_lobs values (3,'Some data for record 3.', BFILENAME(null,null),'48656C6C6F' UTL_RAW.CAST_TO_RAW(' there!')) / (4) select 해보기 select * from test_lobs / SQL*Plus는 BFILE(null 이더라도 ) 이나, BLOB의 data는 convert하지못한다. column c2 format a60 wrap LOB DATA TYPE 의이해 171

172 select c1, c2 from test_lobs / C1 C Some data for record 3. 위의경우 LOB locator 만 fetch 가가능하고, 대응하는 data도 fetch가가능하다. 만일 3GL, PL/SQL을사용하면 character string을 insert하나, 한번에 select는불가능하다. 예제 ) declare c_lob varchar2(10); begin c_lob := 'Record 4.'; insert into test_lobs values (4,c_lob,BFILENAME(null,null), EMPTY_BLOB()); end; / 작업완료. 그러나, -- declare c_lob varchar2(10); begin select c2 into c_lob from test_lobs where c1 = 4; end; / error message 가다음과같다. : ERROR at line 4: ORA-06550: line 4, column 19: PLS-00385: type mismatch found at 'C_LOB' in SELECT...INTO statement ORA-06550: line 4, column 4: PL/SQL: SQL Statement ignored (3) BFILE column을설정하기 1. 이를위해맨처음 BFILE 이있는directory 의 handle 을생성한다. /tmp 에 4 줄의 rec2.txt를생성한다. /tmp 에 5 줄의 rec3.txt를생성한다. 2. directory /tmp 의 ALIAS 를생성한다. create directory tmp_dir as '/tmp' / 3. BFILE 의관련 record 를 update 한다. LOB DATA TYPE 의이해 172

173 update test_lobs set c3 = BFILENAME('TMP_DIR','rec2.txt') where c1 = 2 / update test_lobs set c3 = BFILENAME('TMP_DIR','rec3.txt') where c1 = 3 / 이들 column 은 oracle 의 READ-ONLY mode 로관리되고 DBMS_LOB package 나 OCI 를통해 access 가가능하다. Example <DBMS_LOB 사용하기 > LOB data 의 length 를구하기. empty locator 가 specify 된곳의 length 는 0 이다. column len_c2 format 9999 column len_c3 format 9999 column len_c4 format 9999 select c1, DBMS_LOB.GETLENGTH(c2) len_c2, DBMS_LOB.GETLENGTH(c3) len_c3, DBMS_LOB.GETLENGTH(c4) len_c4 from test_lobs / C1 LEN_C2 LEN_C3 LEN_C SUBSTR/INSTR 사용 :CLOB, BLOB, BFILE은모두사용가능하나BFILE 을위해 file 은미리 open 되어있어야한다. SUBSTR parameter 는 LOB, amount, offset 이다. 이는기존의 substr function 과는반대이다. INSTR 는 LOB, string, offset, occurence,(offset, occurrence 가생략시 1) 이다. column sub_c2 format a10 column ins_c4 format 99 select c1, DBMS_LOB.SUBSTR(c2,9,3) sub_c2, DBMS_LOB.INSTR(c4,UTL_RAW.CAST_TO_RAW('ello'),1,1) ins_c4 LOB DATA TYPE 의이해 173

174 from test_lobs / C1 SUB_C2 INS_C me data f 2 4 cord 4. 0 다음은 PL/SQL block 에서 DBMS_LOB 을사용하는것을보여준다. set serveroutput on set long 1000 declare b_lob BLOB; c_lob CLOB; c_lob2 CLOB; bf BFILE; buf varchar2(100) := 'This is some text to put into a CLOB column in the' chr(10) 'database. The data spans 2 lines.'; n number; fn varchar2(50); --Filename fd varchar2(50); --Directory alias /* 다음의 procedure는한번에 1line을 print하는것을보여준다 */ procedure print_clob is offset number; len number; o_buf varchar2(200); amount number; --} f_amt number := 0; --}To hold the amount of data f_amt2 number; --}to be read or that has been amt2 number := -1; --}read begin len := DBMS_LOB.GETLENGTH(c_lob); offset := 1; while len > 0 loop amount := DBMS_LOB.INSTR(c_lob,chr(10),offset,1); --Amount returned is the count from the start of the file, --not from the offset. if amount = 0 then --No more linefeeds so need to read remaining data. LOB DATA TYPE 의이해 174

175 amount := len; amt2 := amount; else f_amt2 := amount; --Store position of next LF amount := amount - f_amt; --Calc position from last LF f_amt := f_amt2; --Store position for next time amt2 := amount - 1; --Read up to but not the LF end if; DBMS_LOB.READ(c_lob,amt2,offset,o_buf); dbms_output.put_line(o_buf); len := len - amount; offset := offset+amount; end loop; end; Reference Document LOB DATA TYPE 의이해 175

176 오라클함수정리... 아.. 지겨워.. 맨날정리.. 정리 :55 단일행함수 - 문자형함수 UPPER : 모든문자를대문자로전환 LOWER : 모든문자를소문자로전환 INITCAP : 문자를단어별로앞머리는대문자나머지는소문자로전환 CONCAT : 두문자열을합성. 연산자와같은용도로사용 SUBSTR : 특정문자열의부분을선택 LENGTH : 문자열의길이를구함 LPAD : 왼쪽문자자리를채움 RPAD : 오른쪽문자자리를채움 LTRIM : 왼쪽문자를지움 RTRIM : 오른쪽문자를지움 TRANSLATE : 특정문자열을대체 REPLACE : 특정문자열을대신 UPPER( 문자값 ) : UPPER('Oracle Server') ORACLE SERVER LOWER( 문자값 ) : LOWER('Oracle Server') oracle server INITCAP( 문자값 ) : INITCAP('Oracle Server') Oracle Server CONCAT( 문자값1, 문자값2) : CONCAT('Oracle', ' Server') Oracle Server SUBSTR( 문자값, a, b) a 선택할문자열의시작위치. 음수면끝에서부터시작 b 선택할문자열의개수. 이인자는생략할수있으며, 생략할경우문자열의끝까지선택 : SUBSTR(' 강남구역삼동,5,2) 역삼 LENGTH( 문자값1, a, 문자값2) : LENGTH( 홍길동 ) 3 LPAD( 문자값1, a, 문자값2) RPAD( 문자값1, a, 문자값2) a : 전체채울자리수 오라클함수정리... 아.. 지겨워.. 맨날정리.. 정리.. 176

177 문자값2 : 채울문자생략할수있으며, 생략되면공백값임 : LPAD( 홍길동,10 * ) **** 홍길동 LTRIM( 문자값1, 문자값2) RTRIM( 문자값1, 문자값2) 문자값1에서왼쪽 ( 오른쪽 ) 에서부터더이상문자값2를만나지않을때까지지움 : LTRIM('XXAXBA','X') AXBA TRANSLATE( 문자값, a, b) a 대체하고싶은문자 (from) b 대체할결과의문자 (to) : TRANSLATE('AABBA','B','C') AACCA REPLACE ( 문자값, a, b) a 바꾸고싶은문자 (from) b 바꿀결과의문자 (to) : REPLACE ('JACK and JUE','J','BL') BLACK and BLUE - 숫자형함수 ROUND : 숫자를반올림 TRUNC : 숫자를절사 MOD : 나누기연산에서나머지구함 POWER : 거듭제곱 SQRT : 제곱근 SIGN : 양수인지음수인지 0인지를구벌함 CHR : ASCII 값에해당하는문자를구함 ROUND( 숫자값, a), TRUNC( 숫자값, a) a 숫자값을반올림 ( 버림 ) 하여 a자리까지돌려줌.a가양수이면소수이하자리를, 음수이면정수부분자리임생략할수있으며, 생략하면 0 : ROUND(35.735,2) MOD( 숫자값, a ) a 숫자값을나누기할수있음 : MOD(7,2) 1 POWER( 숫자값1, 숫자값2) : POWER(3,2) 9 SQRT ( 숫자값 ) : SQRT(25) 5 SIGN( 숫자값 ) : SIGN(-15) -1 CHR( 숫자값 ) : CHR(65) A 오라클함수정리... 아.. 지겨워.. 맨날정리.. 정리.. 177

178 - 날짜형함수 SYSDATE : 현재시스템의날짜및시간을구함 LAST_DAY : 지정한날짜의해당월의마지막날짜를구함 MONTHS_BETWEEN : 두날짜사이의개월수를구함 ADD_MONTHS : 지정한날짜로부터몇개월후의날짜를구함 ROUND : 날짜에대한반올림 TRUNC : 날짜에대한버림 SYSDATE : SYSDATE 10-MAY-99 LAST_DAY( 날짜값 ) : LAST_DAY('17-FEB-98') 28-FEB-98 MONTHS_BETWEEN( 날짜값1, 날짜값2) : MONTHS_BETWEEN('26-APR-97','22-JUL-95') ADD_MONTHS( 날짜값, 숫자값 ) : ADD_MONTHS('22-JUL-95',21) 22-APR-97 ROUND( 날짜값, 자리수 ) : 현재날짜가 1999년 5월 10일이라고가정하자. ROUND(SYSDATE,'MONTH') 01-MAY-99 TRUNC( 날짜값, 자리수 ) : 현재날짜가 1999년 5월 10일이라고가정하자. TRUNC(SYSDATE,'YEAR') 01-JAN-99 - 날짜에대한산술연산날짜 + 숫자 : 날짜특정한날로부터몇일후의날짜계산날짜 - 숫자 : 날짜특정한날로부터몇일전의날짜계산날짜 - 날짜 : 숫자두날짜사이의차이를숫자로계산 - 변환형함수 TO_CHAR : 숫자나날짜를문자열로변환 TO_NUMBER : 문자를숫자로변환 TO_DATE : 문자를날짜로변환 - TO_CHAR에서숫자를문자로변환시에형식에사용되는요소 9 : 일반적인숫자를나타냄 0 : 앞의빈자리를 0으로채움 $ : dollar를표시함 L : 지역통화단위 (ex ). : 소숫점을표시함, : 천단위를표시함 - TO_CHAR에서날짜를문자로변환시에형식에사용되는요소 SCC : 세기를표시 S는기원전 (BC) YEAR : 연도를알파벳으로 spelling YYYY : 4자리연도로표시 YY : 끝의 2자리연도로표시 MONTH : 월을알파벳으로 spelling MON : 월의알파벳약어 MM : 월을 2자리숫자로표시 DAY : 일에해당하는요일 DY : 일에해당하는요일의약어 DDD,DD,D : 연도, 월, 일중의날짜를숫자로표시 HH, HH24 : (1-12), (0-23) 중의시간을표시 오라클함수정리... 아.. 지겨워.. 맨날정리.. 정리.. 178

179 MI : 분을표시 SS : 초를표시 AM(A.M.),PM(P.M.) : 오전인지오후인지를표시 TO_CHAR( 문자값, 형식 ) 숫자를문자로변환 : TO_CHAR(350000,'$999, 999') $350,000 숫자를날짜로변환 : TO_CHAR(SYSDATE,'YY/MM/DD') 95/05/25 TO_DATE( 문자값, 형식 ) : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY') 10-SEP-92 TO_NUMBER( 문자값 ) : TO_NUMBER('1234') DECODE 함수 DECODE 함수는값을비교하여해당하는값을돌려주는함수 > DECODE ( 형식, 비교값1, 결과치1, 비교값2, 결과치2,... 기본치 ) 형식 : 컬럼이나값비교값1 : 형식이비교값1에맞는지를비교결과값1 : 형식이비교값1에맞을때갖는값기본치 : 형식이비교값1,2,... 에맞지않을때가지는값생략될수있으며, 생략되면 NULL이다. SQL> SELECT name,title,decode(substr(title,-2,2), 2 ' 부장 ',salary*1.1, 3 ' 과장 ',salary*1.07, 4 ' 사원 ',salary*1.05, 5 salary) 이번달급여 6 FROM s_emp 7 ORDER BY 3 DESC ; * 다중행함수 - 그룹함수 COUNT( a ) : a의행의개수를구함 AVG( a ) : a의평균을구함 SUM( a ) : a의합계를구함 MIN( a ) : a의최소값을구함 MAX( a ) : a의최대값을구함 STDDEV( a ) : a의표준편차를구함 VARIANCE( a ) : a의분산을구함 COUNT(*) 를제외한모든그룹함수는 NULL값을고려하지않습니다. 중복값을제거하고싶은경우는 a의앞에 DISTINCT를기술합니다. MAX, MIN, COUNT를제외한그룹함수는숫자타입의데이터에만가능합니다 오라클함수정리... 아.. 지겨워.. 맨날정리.. 정리.. 179

180 Oracle Clob 처리 :49 ==== Oracle Clob 처리 ==== SELECT dbms_lob.substr( 칼럼, dbms_lob.getlength( 카럼 ), 1), <-- 전체로우뿌리기 DBMS_LOB.INSTR( 칼럼, 'test', 1, 1) <-- 검색된내용에대한디스플레이 FROM 테이블 WHERE DBMS_LOB.INSTR( 칼럼, 'test', 1, 1) <> 0; * 사용법 -- dbms_lob.substr(clob칼럼, 전체로우가져오는길이, offset) -- dbms_lob.instr(clob칼럼, 검색된단어, offset, 몇번째에있는검색된단어 ) Oracle Clob 처리 180

181 오라클날짜다루기 :03 날짜다루기 1) 현재의날짜와시간 SYSDATE을사용합니다. SELECT SYSDATE FROM DUAL ; 2) 날짜를문자열로변환하기 SELECT TO_CHAR( sysdate, 'YYYY/MM/DD' ) from dual 3) 문자열을날짜로변환하기 TO_DATE( string_column_name, format ) 5) 시분초의표현 예 ) SELECT TO_CHAR( birthday, 'HH:MI:SS' ) 예 ) SELECT TO_CHAR( birthday, 'HH:MI:SS PM' ) 6) 날짜의더하기와빼기 SELECT SYSDATE + 7 FROM DUAL ; 오라클날짜다루기 181

182 케릭터셋인코딩 :40 System.out.println(new String(word.getBytes("utf-8"), "euc-kr")); System.out.println(new String(word.getBytes("utf-8"), "ksc5601")); System.out.println(new String(word.getBytes("utf-8"), "x-windows-949")); System.out.println(new String(word.getBytes("utf-8"), "iso ")); System.out.println(new String(word.getBytes("iso "), "euc-kr")); System.out.println(new String(word.getBytes("iso "), "ksc5601")); System.out.println(new String(word.getBytes("iso "), "x-windows-949")); System.out.println(new String(word.getBytes("iso "), "utf-8")); System.out.println(new String(word.getBytes("euc-kr"), "ksc5601")); System.out.println(new String(word.getBytes("euc-kr"), "utf-8")); System.out.println(new String(word.getBytes("euc-kr"), "x-windows-949")); System.out.println(new String(word.getBytes("euc-kr"), "iso ")); System.out.println(new String(word.getBytes("ksc5601"), "euc-kr")); System.out.println(new String(word.getBytes("ksc5601"), "utf-8")); System.out.println(new String(word.getBytes("ksc5601"), "x-windows-949")); System.out.println(new String(word.getBytes("ksc5601"), "iso ")); System.out.println(new String(word.getBytes("x-windows-949"), "euc-kr")); System.out.println(new String(word.getBytes("x-windows-949"), "utf-8")); System.out.println(new String(word.getBytes("x-windows-949"), "ksc5601")); System.out.println(new String(word.getBytes("x-windows-949"), "iso ")); 케릭터셋인코딩 182

183 SQL PLUS 잡다한기능 :44 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 시작 DB 시작 - 단계별시작 $ sqlplus "sys/passwd as sysdba" startup nomount; -- NO Mount 단계 alter database mount; -- Mount 단계 alter database open; -- Open 단계 DB 종료 SQL PLUS 잡다한기능 183

184 $ sqlplus "sys/passwd as sysdba" shutdown normal -- 세션, 트랜잭션종료시까지대기후종료 shutdown transactional -- 트랜잭션종료시까지대기후종료 shutdown immediate -- 즉시종료. 모든 DML 롤백 shutdown abort -- 비정상즉시종료. 백업과복구시에만사용. 로그인없이 SQL*Plus 만실행하기 $ sqlplus "/nolog" 테이블생성스크립트뽑아내기 $ 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 프로시져혹은함수등의소스뽑아내기 SQL PLUS 잡다한기능 184

185 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 숫자출력범위늘리기 ( 숫자를 15 자리까지출력 ) SET NUM 15 쿼리결과를셸스크립트로자동저장하기 #!/bin/sh # 오늘날짜를셸변수로지정 TODAY=`date +"%Y%m%d"` sqlplus username/password << ENDSQL -- 아래는출력시불필요한형식꾸미기가안들어가게한다. SQL PLUS 잡다한기능 185

186 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) SQL PLUS 잡다한기능 186

187 SQL PLUS 잡다한기능 187

188 MERGE 를이용한 ROW INSERT OR UPDATE :45 Merge 를사용하여 row 삽입 merge를하려고하는 source 테이블의행을읽어 target 테이블에매치되는행이있을경우에는새로운값으로 UPDATE 를수행하고, 만일매치되는행이없을경우에는새로운행으로인식하여 target 테이블에 INSERT를수행한다. merge 문을수행하기위해서는다음과같은권한이주어져야한다. target 테이블새로운행을삽입하기위한 insert 권한. target 테이블에서기존행을갱신하기위한 update 권한. source 테이블행을검색하기위한 select 권한. 형식 MERGE INTO 테이블명 alias USING [ 테이블명 뷰명 subquery] alias ON 조건 WHEN MATCHED THEN UPDATE SET... WHEN NOT MATCHED THEN INSERT... VALUES...; merge into 뒤에테이블을삽입하거나 target 테이블명을지정한다. WHERE 절은사용할수없고, 대신 ON 절에 JOIN 조건을기술한다. WHEN MATCHED THEN 절에는 ON 절에서기술한 JOIN 조건을만족하는즉, source 테이블과 target 테이블간에매치되는행이존재할경우 target 테이블의행을새로운값으로 MERGE 를이용한 ROW INSERT OR UPDATE 188

189 UPDATE하기위한구문을기술한다. WHEN NOT MATCHED THEN 절에는 ON 절에서기술한 JOIN 조건을만족하지않는경우즉, target 테이블에행을 INSERT하기위한구문을기술한다. WHEN MATCHED THEN 절과 WHEN NOT MATCHED THEN 절에는테이블명대신 alias를사용한다. 예제 SQL> create table dept1 2 AS select * from dept; 테이블이생성되었습니다. SQL> create table dept2 2 AS select * from dept; 테이블이생성되었습니다. SQL> insert into dept2 values(50,'kim_gun','corea'); 1 개의행이만들어졌습니다. SQL> update dept2 2 set dname='lee_gun' where deptno=30; 1 행이갱신되었습니다. SQL> select * from dept2; DEPTNO DNAME LOC MERGE 를이용한 ROW INSERT OR UPDATE 189

190 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 LEE_gun CHICAGO 40 OPERATIONS BOSTON 50 KIM_gun COREA SQL> select * from dept1; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> merge into dept1 d 2 using dept2 de 3 on (d.deptno=de.deptno) ON 조건절에들어있는컬럼은 update절에넣으면오류발생 4 when matched then 5 update set 6 d.dname=de.dname, 7 d.loc=de.loc 8 when not matched then 9 insert values(de.deptno, de.dname, de.loc); 5 행이병합되었습니다. MERGE 를이용한 ROW INSERT OR UPDATE 190

191 SQL> select * from dept1; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 LEE_gun CHICAGO 40 OPERATIONS BOSTON 50 KIM_gun COREA SQL> merge 후 테이블 aa 1111 홍길동 2222 길동무 3333 죽마고우 테이블 aa 를 bb 로 merge ====================> merge 전 bb 의내용 1111 홍길동 2222 옛친구 테이블 bb 의값이바뀜 ====================> bb 의내용 1111 홍길동 2222 길동무 3333 죽마고우 SQL> create table aa(id number(4), name varchar2(10)); 테이블이생성되었습니다. SQL> create table bb (id number(4), name varchar2(10)); 테이블이생성되었습니다. MERGE 를이용한 ROW INSERT OR UPDATE 191

192 SQL> insert into aa values(1111,' 홍길동 '); 1 개의행이만들어졌습니다. SQL> insert into aa values(2222,' 길동무 '); 1 개의행이만들어졌습니다. SQL> insert into aa values(3333,' 죽마고우 '); 1 개의행이만들어졌습니다. SQL> insert into bb values(1111,' 홍길동 '); 1 개의행이만들어졌습니다. SQL> insert into bb values(2222,' 옛친구 '); 1 개의행이만들어졌습니다. SQL> select * from aa; ID NAME 홍길동 2222 길동무 3333 죽마고우 MERGE 를이용한 ROW INSERT OR UPDATE 192

193 SQL> select * from bb; ID NAME 홍길동 2222 옛친구 SQL> merge into bb target 2 using aa source 3 on (source.id=target.id) 4 when matched then 5 update set 6 target.name=source.name 7 when not matched then 8 insert values(source.id,source.name); 3 행이병합되었습니다. SQL> select * from bb; ID NAME 홍길동 2222 길동무 3333 죽마고우 SQL> MERGE 를이용한 ROW INSERT OR UPDATE 193

194 MERGE 를이용한 ROW INSERT OR UPDATE 194

195 MERGE INTO :43 merge 문 merge 문은구조가같은두개의테이블을비교하여하나의테이블로합치기위한데이터조작이다. 예를들어, 하루에수만건씩발생하는데이터를하나의테이블에관리할경우대량의데이터로인해질의문의성능이저하된다. 이런경우, 지점별로별도의테이블에서관리하다가년말에종합분석을위해하나의테이블로합칠때 merge 문을사용하면편리하다. merge하고자하는소스테이블의행을읽어타킷테이블에매치되는행이존재하면새로운값으로 UPDATE 를수행하고, 만일매치되는행이없을경우새로운행을타킷테이블에서 INSERT를수행한다. Merge 는여러데이터소스로부터다양한데이터를수집해서작업을수행하는 dataware housing 등에적용하면유용하다. merge 문에서 where 절은사용할수없으며대신 on이사용된다. 또한 when matched then 절과 when not matched then 절에는테이블명대신 alias를사용한다. 형식 MERGE INTO 테이블명별칭 USING 대상테이블 / 뷰별칭 ON 조인조건 WHEN MATCHED THEN UPDATE SET 컬럼1= 값1 컬럼2= 값2 WHEN NOT MATCHED THEN MERGE INTO 195

196 INSERT ( 컬럼 1, 컬럼 2,...) VALUES( 값 1, 값 2,...); 예제 SQL> create table emp( 2 id number primary key, 3 name varchar2(10) not null, 4 salary number, 5 bonus number default 100); Table created. SQL> desc emp; Name Null? Type ID NAME SALARY BONUS NOT NULL NUMBER NOT NULL VARCHAR2(10) NUMBER NUMBER SQL> insert into emp(id,name,salary) values(1001,'jijoe',150); SQL> insert into emp(id,name,salary) values(1002,'cho',130); SQL> insert into emp(id,name,salary) values(1003,'kim',140); SQL> select * from emp; ID NAME SALARY BONUS jijoe MERGE INTO 196

197 1002 cho kim SQL> create table bonus(id number, bonus number default 100); Table created. SQL> insert into bonus(id) 2 (select e.id from emp e); 3 rows created. SQL> select * from bonus; ID BONUS SQL> merge into bonus D 2 using (select id,salary from emp) S 3 on (D.id = S.id) 4 when matched then update set 5 D.bonus=D.bonus + S.salary* when not matched then insert(d.id, D.bonus) 7 values(s.id,s.salary*0.01); MERGE INTO 197

198 3 rows merged. SQL> select * from bonus; ID BONUS SQL> 예제 SQL> delete from bonus where id=1003; 1 row deleted. SQL> merge into bonus D 2 using (select id,salary from emp) S 3 on (D.id = S.id) 4 when matched then update set 5 D.bonus=D.bonus + S.salary* when not matched then insert(d.id, D.bonus) 7 values(s.id, S.salary*0.01); 3 rows merged. SQL> select * from bonus; MERGE INTO 198

199 ID BONUS MERGE INTO 199

200 오라클접속방법 :37 1. 도스커맨드창에서 sqlplus 계정명 / 2.. 서버연결텔넷등에서 ORACLE_HOME 디렉토리로이동후 sqlplus " /as sysdba" ==> sysdba 로접속 sqlplus 계정명 / 암호 3. 토드, 오렌지등에서 tnsping 부터확인해봐라.. 쫌!!!!!! 오라클접속방법 200

201 오라클힌트사용법 :15 Hints ( 출처 -Oracle8.0 Tuning Guide) * 정리상의오류가있을수있으므로원서참조바람. A. initialization parameter 중 OPTIMIZER_MODE 지정가능값 1.ALL_ROWS Goal : Best Throughput 용도 : 전체 RESOURCE 소비를최소화시키기위한힌트. Cost-Based 접근방식. 예 : SELECT /*+ALL_ROWS */ EMPNO,ENAME FROM EMP WHERE EMPNO = 7655; 2.FIRST_ROWS Goal : Best Response Time 용도 : 조건에맞는첫번째 row를리턴하기위한 Resource 소비를최소화시키기위한힌트. Cost-Based 접근방식. 특징 : - Index Scan 이가능하다면 Optimizer 가 Full Table Scan 대신 Index Scan을선택한다. - Index Scan 이가능하다면 Optimizer가 Sort-Merge 보다 Nested Loop 을선택한다. - Order By절에의해 Index Scan 이가능하다면, Sort과정을피하기위해 Index Scan을선택한다. - Delete/Update Block 에서는무시된다. - 다음을포함한 Select 문에서도제외된다. 집합연산자 (Union,Intersect,Minus,Union All) Group By For UpDate Group 함수 Distinct 오라클힌트사용법 201

202 예 : SELECT /*+FIRST_ROWS */ EMPNO,ENAME FROM EMP WHERE EMPNO = 7655; 3.CHOOSE Goal : Acess되는테이블에통계치존재여부에따라 Optimizer로하여금 Rule-Based Approach와 Cost-Based Approach 중하나를선택할수있게한다. 용도 : Data Dictionary가해당테이블에대해통계정보를가지고있다면 Optimizer는 Cost-Based Approach를선택하고, 그렇지않다면 Rule-Based Approach를선택한다. 예 : SELECT /*+CHOOSE */ EMPNO,ENAME FROM EMP WHERE EMPNO = 7655; 4.RULE 용도 : Rule-Based 최적화를사용하기위해. 예 : SELECT /*+RULE */ EMPNO,ENAME FROM EMP WHERE EMPNO = 7655; B. Access Methods 로써의 Hints 1.FULL 용도 : 해당테이블의 Full Table Scan 을유도. 예 : SELECT /*+FULL(EMP) */ EMPNO,ENAME FROM EMP WHERE EMPNO = 7655; * 테이블 Alias 가있는경우는 Alias사용. Schema Name은사용안함 (From 에 SCOTT.EMP 라고기술해도 hint에는 EMP사용 ). 2.ROWID 용도 : 지정된테이블의 ROWID 를이용한 Scan 유도 3.CLUSTER 용도 : 지정된테이블 Access 에 Cluster Scan 유도. Cluster 된 Objects 에만적용가능. 예 : SELECT /*+CLUSTER(EMP) */ ENAME,DEPTNO 오라클힌트사용법 202

203 FROM EMP,DEPT WHERE DEPTNO = 10 AND EMP.DEPTNO = DEPT.DEPTNO; 4.HASH 용도 : 지정된테이블 Access 에 HASH Scan 유도. /*+HASH(table) */ 5.HASH_AJ 용도 : NOT IN SubQuery 를 HASH anti-join 으로변형 /*+HASH_AJ */ 6.HASH_SJ 용도 : correlated Exists SubQuery 를 HASH semi-join 으로변형 /*+HASH_SJ */ 7.INDEX 용도 : 지정된테이블Access 에 Index Scan 유도. * 하나의 index만지정되면 optimizer는해당index를이용. * 여러개의인덱스가지정되면 optimizer 가각 index의 scan시 cost를분석한후최소비용이드는 index사용. 경우에따라 optimizer 는여러 index를사용한후결과를 merge하는 acees방식도선택. * index가지정되지않으면 optimizer는테이블의이용가능한모든 index에대해 scan cost를고려후최저비용이드는 index scan을선택한다. 예 : SELECT /*+INDEX(EMP EMPNO_INDEX) */ EMPNO,ENAME FROM EMP WHERE DEPTNO=10 8.INDEX_ASC 용도 : INDEX HINT 와동일단,ASCENDING 으로 SCAN 함을확실히하기위함. 9.INDEX_COMBINE 용도 : INDEX명이주어지지않으면 OPTIMIZER는해당테이블의 best cost 로선택된 Boolean combination index 를사용한다. index 명이주어지면주어진특정 bitmap index 의 boolean combination 의사용을시도한다. /*+INDEX_COMBINE(table index) */ 10.INDEX_DESC 오라클힌트사용법 203

204 용도 : 지정된테이블의지정된 index 를이용 descending 으로 scan 하고자할때사용. /*+INDEX_DESC(table index) */ 11.INDEX_FFS 용도 : full table scan 보다빠른 full index scan 을유도. /*+INDEX_FFS(table index) */ 12.MERGE_AJ 용도 : not in subquery 를 merge anti-join 으로변형 /*+MERGE_AJ */ 13.MERGE_SJ 용도 : correalted EXISTS subquery 를 merge semi-join 으로변형 /*+MERGE_SJ */ 14.AND_EQUAL 용도 : single-column index 의 merge 를이용한 access path 선택. 적어도두개이상의 index 가지정되어야한다. /*+AND_EQUAL(table index1,index2...) */ 15.USE_CONCAT 용도 : 조건절의 OR 를 Union ALL 형식으로변형한다. 일반적으로변형은비용측면에서효율적일때만일어난다. /*+USE_CONCAT */ C. JOIN 순서를결정하는 Hints 1.ORDERED 용도 : from 절에기술된테이블순서대로 join 이일어나도록유도. /*+ORDERED */ 예 : SELECT /*+ORDERED */ TAB1.COL1,TAB2.COL2,TAB3.COL3 FROM TAB1,TAB2,TAB3 WHERE TAB1.COL1=TAB2.COL1 오라클힌트사용법 204

205 AND TAB2.COL1=TAB3.COL1; 2.STAR 용도 : STAR QUERY PLAN이사용가능하다면이를이용하기위한 HINT. STAR PLAN은규모가가장큰테이블이 QUERY에서 JOIN ORDER상마지막으로위치하게하고 NESTED LOOP 으로 JOIN이일어나도록유도한다. 적어도 3개테이블이상이조인에참여해야하며 LARGE TABLE의 CONCATENATED INDEX는최소 3컬럼이상을 INDEX에포함해야한다. 테이블이 ANALYZE 되어있다면 OPTIMIZER가가장효율적인 STAR PLAN을선택한다. /*+STAR */ D. JOIN OPERATION 을결정하는 HINTS. 1.USE_NL 용도 : 테이블의 JOIN 시테이블의각 ROW 가 INNER 테이블을 NESTED LOOP 형식으로 JOIN 한다. /*+USE_NL(inner_table) */ 예 : SELECT /*+ORDERD USE_NL(CUSTOMER) */ FROM ACCOUNT.BALANCE,CUSTOMER.LAST_NAME,CUSTOMER.FIRST_NAME WHERE ACCOUNT.CUSTNO = CUSTOMER.CUSTNO; 2.USE_MERGE 용도 : 지정된테이블들의조인이 SORT-MERGE 형식으로일어나도록유도. /*+USE_MERGE(table) */ * 괄호안의테이블은 JOIN ORDER 상의뒤의테이블 (?) 3.USE_HASH 용도 : 각테이블간 HASH JOIN 이일어나도록유도. /*+USE_HASH(table) */ * 괄호안의테이블은 JOIN ORDER 상의뒤의테이블 (?) 4.DRIVING_SITE 용도 : QUERY 의실행이 ORACLE 에의해선택된 SITE 가아닌다른 SITE 에서 일어나도록유도. 오라클힌트사용법 205

206 /*+DRIVING_SITE(table) */ 예 : SELECT /*+DRIVING_SITE(DEPT) */ FROM EMP,DEPT@RSITE WHERE EMP.DEPTNO = DEPT.DEPTNO; DRIVING_SITE 힌트를안쓰면 DEPT의 ROW가 LOCAL SITE로보내져 LOCAL SITE에서 JOIN이일어나지만, DRIVING_SITE 힌트를쓰면 EMP의 ROW들이REMOTE SITE로보내져 QUERY가실행된후 LOCAL SITE로결과가 RETURN된다. 오라클힌트사용법 206

207 CONSTRAINT_NAME 변경하기.SQL :50 SELECT 'ALTER TABLE EP_USER.' B.TABLE_NAME ' RENAME CONSTRAINT ' B.CONSTRAINT_NAME ' TO CK_' B.TABLE_NAME '_' B.COLUMN_NAME ';' FROM SYS.ALL_CONSTRAINTS A, SYS.ALL_CONS_COLUMNS B WHERE A.OWNER = '' AND A.CONSTRAINT_TYPE = 'P' AND A.TABLE_NAME LIKE 'TBEP%' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME ORDER BY A.TABLE_NAME CONSTRAINT_NAME 변경하기.SQL 207

208 테이블 _ 컬럼 _ 코멘트붙이기.SQL :37 SELECT 'COMMENT ON TABLE EP_USER.' TB_NAME ' IS ''' MAX(ENTITY_NAME) ''';' FROM TABLE_COLUMN_LIST GROUP BY TB_NAME SELECT 'COMMENT ON COLUMN EP_USER.' TB_NAME '.' COL_NAME ' IS ' '''' ATRIBUTE_NAME ''';' FROM TABLE_COLUMN_LIST 테이블 _ 컬럼 _ 코멘트붙이기.SQL 208

209 테이블 _ 명명규약검사.sql :35 SELECT * FROM SYS.ALL_CONSTRAINTS WHERE OWNER = 'OWNER' AND ( CONSTRAINT_NAME NOT LIKE 'CK%' AND CONSTRAINT_NAME NOT LIKE 'PK%' AND CONSTRAINT_NAME NOT LIKE 'FK%' ) AND TABLE_NAME LIKE 'TBEP%' 테이블 _ 명명규약검사.sql 209

210 정차장님의 DROP DATABASE 블로그 저자 코코호 ~ 정차장님 발행일 :00:02 저작권법에의해한국내에서보호를받는저작물이므로무단복제와전재를금합니다.