정차장님의 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 (steven.feuerstein@quest.com) 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