DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
사용자들은 암호화의 효용성에 대해 엇갈린 반응을 보이곤 합니다. 암호화는 필요한 기술이지만, 키 관리의 복잡성으로 인해 올바르게 설정되지 않은 경우 효율적이지 못한 결과를 초래할 수도 있습니다. 또 대부분의 애플리케이션 설계자들은 키 값의 암호화 및 해독 과정에서 수반되는 성능 오버헤드를 우려합니다. 이러한 이유로 암호화를 적용하지 않는 대신 패스워드, 인증 메커니즘 등의 변경 보안(perimeter protection)을 강화하는 쪽을 선택하는 경우가 많습니다. 하지만, 서버(또는 디스크)를 통째로 훔쳐, 동일한 운영체제에 서버를 마운트하고 데이터에 접근하는 경우를 가정해 볼 수 있습니다. 또는 내부사용자가 업무 수행 과정에서 DBA의 권한으로 데이터베이스에 접근하여 중요한 정보를 빼내는 경우를 생각해 볼 수 있습니다. 미 캘리포니아주에서는, 이와 같은 상황이 발생한 경우, 영향 범위에 있는 모든 고객들에게 보안 침해 사실을 통보하도록 법적으로 규정하고 있습니다 (다른 주에서도 이와 유사한 법안이 발의될 예정입니다). (흔히 경험할 수 있는 것은 아니지만 결코 무시할 수 없는) 이러한 사고에서는, 인증 메커니즘이 아무런 효과를 갖지 못합니다. 보안이 각별히 중시되는 환경에서 TDE(transparent data encryption)이 중요한 기능으로 활용되고 있는 것도 바로 이 때문입니다. TDE는 복잡한 키 관리 업무를 데이터베이스 엔진 내부에서 수행하는 암호화 기술입니다. 또 DBA는 데이터베이스 테이블의 관리에 필요한 모든 권한을 갖지만, 반면 모든 데이터를 직접 조회할 수는 없도록 통제 가능합니다. Oracle Database 10g Release 2의 TDE는, 특정 테이블의 하나 또는 그 이상의 컬럼을 쉽게 암호화할 수 있는 기능을 제공합니다. 관리자는 코드를 전혀 작성하지 않고도 컬럼을 암호화 대상으로 정의할 수 있습니다. 암호화는 기본적으로 입력 값을 암호화하기 위한 키(key)와 알고리즘을 필요로 합니다. TDE는 암호화를 위한 키를 자동으로 생성합니다. 이러한 방식은 키 관리를 단순화해 준다는 장점이 있지만 유출의 가능성이 더 높아진다는 단점도 뒤따릅니다. 이러한 문제를 보완하기 위해 또 다른 키(master key)가 데이터베이스 레벨에서 설정됩니다. 테이블 키는 마스터 키와 함께 암호화되며, 따라서 특정 컬럼을 해독하기 위해서는 테이블 키와 마스터 키가 함께 필요합니다. (암호화 기술 및 오라클의 암호화 패키지 구현방식에 대한 자세한 정보는 필자가 기고한 오라클 매거진 기사 "Encrypt Your Data Assets- 를 참고하시기 바랍니다.) 마스터 키는 “wallet”이라고 부르는 데이터베이스 외부의 별도 저장소에 저장됩니다. (wallet의 디폴트 위치는 “$ORACLE_BASE/admin/$ORACLE_SID/wallet”으로 정의됩니다.) 그 개념적인 구조가 아래 그림과 같습니다. TDE가 설정되고 나면 (좀 더 정확히 말해 wallet과 마스터 키가 설정되고 나면), 이 설정을 이용하여 데이터를 보호할 수 있습니다. 테이블의 컬럼을 암호화하려면 아래와 같은 SQL 구문을 사용해야 합니다: 위 구문에서는 SSN 컬럼과 FOLIO_ID 컬럼에 TDE를 적용하여 두 컬럼의 데이터를 암호화하였습니다. 하지만 사용자가 테이블을 조회하면, 데이터가 인출되는 과정에서 자동으로 해독 작업이 수행되기 때문에 데이터는 일반 텍스트의 형태로 보여지게 됩니다. 하지만 디스크가 도난 당한 경우에는, 테이블 세그먼트에 저장된 정보가 암호화된 상태로 유지되며, 이 값을 해독하기 위해서는 테이블 키가 무엇인지 알아야 합니다. 테이블 키를 얻으려면 마스터 키가 필요하지만, 이 키는 데이터베이스 외부에 존재하므로 해독이 불가능하게 됩니다. SSN 컬럼과 FOLIO_ID 컬럼에 대한 정의에서 128-비트 AES(Advanced Encryption Standard)를 이용한 ENCRYpT 옵션이 적용되었음을 주목하시기 바랍니다. 데이터베이스의 wallet은 사전 설정되어 있습니다. 이 wallet의 암호를 설정하기 위해서는 아래와 같은 명령을 사용합니다: 이 명령은 기존에 wallet이 존재하지 않는 경우 wallet을 자동생성하고, 암호를 “topSecret”로 설정합니다 (암호는 대소문자를 구별합니다). 이와 같이 설정한 후 테이블의 생성/수정 과정에서 컬럼 별로 암호화 여부를 결정할 수 있습니다. 위 예에서는, 컬럼의 암호화를 위해 해시 테이블(hash table)을 사용했습니다. External table에 대해 TDE를 적용하는 것 또한 가능합니다. 예를 들어 ACCOUNTS 테이블의 데이터를 다른 위치에 덤프하고자 하는 경우, 다음과 같은 간단한 ENCRYpT 구문을 적용하면 됩니다. accounts_*_ext.dmp 파일의 SSN 컬럼 및 FOLIO_ID 컬럼은 암호화된 형태로 저장됩니, 파일을 읽기 위한 패스">지금까지 설명한 것처럼 TDE는 매우 유용한 액세스 컨트롤 메커니즘으로 활용이 가능합니다. XML은 이미 오랜 기간 동안 대량의 컨텐트를 기반으로 하는 애플리케이션을 위한 표준 데이터타입으로 활용되어 왔으며, 최근에는 그 적용범위가 다른 유형의 애플리케이션으로 확대되고 있습니다. 오라클은 Oracle9i Database가 발표된 이후로 XML 통합 기능을 꾸준히 개선해 왔습니다. Oracle9i Database에서는 여러 가지 방법으로 XML 컨텐트를 조회할 수 있는 기능이 처음으로 추가된 바 있습니다. Oracle Database 10g Release 2에서는 XML 컨텐트의 조회를 용이하게 하기 위한 새로운 XQuery와 XMLTable 함수가 추가되었습니다. (참고: XQuery 표준에 대한 상세한 설명은 본 문서의 주제 범위에서 벗어납니다. XQuery에 대한 자세한 정보가 필요하신 경우, 오라클 매거진 기사 -"XQuery: A New Way to Search" - 를 참고하시기 바랍니다.) 먼저 XQuery에 대해 살펴 보기로 합시다. 그 적용 예가 아래와 같습니다: “xquery” 커맨드는 XQuery 명령을 위해 사용됩니다. 위의 예제는 Oracle9i Database에 소개된 FOR ... IN ... 인라인 뷰(inline view)를 시뮬레이션하고 있습니다. XQuery의 구조는 FLOWR("flower"로 발음합니다)라는 약어로 표현됩니다. FLOWR는 FOR, LET, ORDER BY, WHERE, RETURN을 의미합니다. 위의 예에서 두 번째 라인은 데이터 소스(1~9까지의 숫자)를 정의하고 있습니다. 여기에는 다양한 형태의 소스가 사용 가능합니다. 스칼라 값을 적용할 수도 있고 FOR 절을 이용해 XML 데이터 엘리먼트를 적용할 수도 있습니다. 또 두 번째 라인은 이 값을 저장할 변수(var1)을 함께 명시하고 있습니다. 세 번째 라인에서는 LET 구문을 통해 var1에 1을 더한 값을 var2에 저장하고 있습니다. 그런 다음 (WHERE 절에 정의된 대로) 6이하의 값을 가져와 (ORDER BY 절의 정의에 따라) 정렬(sort) 작업을 수행합니다. 마지막으로 RETURN 구문을 통해 사용자에게 결과를 반환합니다. 위에서 사용된 RETURN, FOR, WHERE, ORDER BY 키워드는 일반 SQL 구문의 SELECT, FROM, WHERE, ORDER BY에 대응합니다. (단 LET 의 경우 일반 SQL 구문에는 대응되는 키워드가 존재하지 않습니다.) 그렇다면 이 툴이 실제로 활용되는 사례를 살펴보기로 합시다. 먼저, 계정 보유자와 수행한 커뮤니케이션에 대한 상세 정보를 저장할 테이블을 생성합니다. 다음에는 이 테이블에 몇 가지 레코드를 삽입합니다. XMLTable 함수는 XQuery와 유사한 목적으로 활용되지만, 일반 SQL 쿼리를 통해 컬럼 데이터를 반환한다는 차이를 갖습니다. 그 적용 예가 아래와 같습니다: 이와 같은 방법으로 일반 SQL 구문을 이용하여 XML 테이블을 조회하는 것이 가능합니다. 지금까지 일반 SQL 쿼리에서 XML을 활용하기 위한 두 가지 방법을 살펴 보았습니다. 그렇다면 두 가지 방법이 각각 어떤 상황에서 활용되는지 알아보기로 합시다. 먼저 XQuery는 데이터를 XMLType의 형태로 가져와, XML을 지원하는 애플리케이션 내부적으로 XML 형태로 처리할 수 있게 합니다. 위에 제시된 예에서 데이터는 XML 포맷으로 출력되며, 따라서 관계형 툴이 아닌 어떤 환경에서도 이 데이터를 조작하거나 디스플레이하는 것이 가능합니다. 두 번째로 XMLTable 함수는, SQL와 XML의 기능을 조합하여 활용하고 있으며, 그 결과는 XML 포맷이 아닌 관계형 포맷으로 출력됩니다. 두 가지 경우 모두 소스는 XML이지만, XQuery의 경우 XMLType을 이용한 XML 포맷으로 데이터를 출력하고, XMLTable 은 관계형 테이블로 데이터를 출력하이블 형태로 출력해야 하는 환경에서 XML의 장점을 최대한 활용하고자 하는 경우에 효과적입니다. XML은 데이터의 구조를 미리 정의하기 어려운 경우에 특히 유용합니다. 위의 예에서, 커뮤니케이션 기록의 포맷은 커뮤니케이션 방식에 따라 달라질 수 있습니다. 이메일을 이용한 커뮤니케이션의 경우, 수신자 이메일 주소, 회신 주소, 참조, 메시지 텍스트 등의 속성이 필요합니다. 전화 연락의 경우라면, 연락 전화번호, 전화번호 유형(집, 직장, 휴대폰 등), 수신자, 보이스 메일 등의 속성이 필요할 것입니다. 이처럼 많은 정보를 테이블에서 정의하려 한다면, 컬럼 수가 무척 많아질 뿐 아니라 읽기에도 불편할 것입니다. 그 대신, 단 하나의 컬럼을 XMLType으로 정의하고 모든 데이터를 이 컬럼에 구겨 넣는 한편으로, XMLType 내에 각 커뮤니케이션 유형별 속성을 정의할 수 있습니다. 또 단순한 SQL 인터페이스를 이용하여 쿼리를 수행하고 애플리케이션 개발 과정을 한층 단순화할 수 있습니다. 오라클 데이터베이스에 구현된 XQuery 기능에 대한 자세한 정보는 OTN의 Oracle XQuery페이지를 참고하시기 바랍니다. 세션이 커밋(commit)되면, 리두 로그 버퍼는 디스크 상의 온라인 리두 로그(redo log)로 플러시(flush) 됩니다. 이 프로세스는 데이터베이스 복구 작업을 수행하는 과정에서 리두 로그를 이용한 트랜잭션의 롤백/롤포워드를 가능하게 합니다. 하지만, 복구 능력을 어느 정도 희생하는 대신 성능을 개선하는 쪽을 선택하고 싶은 경우가 있을 수 있습니다. Oracle Database 10g Release 2는 리두 스트림(redo stream)을 온라인 로그 파일에 기록하는 방식을 제어할 수 있는 기능을 제공합니다. 이 기능은 특정 구문 단위, 또는 데이터베이스 레벨에서 설정이 가능합니다. 그럼 먼저 커밋 구문이 어떤 방식으로 활용되는지 알아봅시다. 트랜잭션의 마지막 부분에서 COMMIT을 수행할 때 다음과 같은 옵션을 추가할 수 있습니다: 여기서 <option>부분의 설정에 따라 리두 스트림을 처리하는 방식이 달라집니다. 디폴트 옵션은 WAIT입니다. 예를 들어 다음과 같은 구문이 가능합니다: 이 구문은 COMMIT과 동일한 효과를 갖습니다. 따라서 리두 스트림이 온라인 리두 로그 파일에 기록될 때까지는 제어권이 사용자에게 반환되지 않습니다. 기록이 완료되는 시점까지 대기하는 것을 원하지 않는다면 아래와 같은 명령을 사용할 수 있습니다: 이 경우, 제어권은 (리두 스트림이 온라인 리두 로그에 기록되기 이전에) 즉각적으로 세션에 반환됩니다. 커밋이 발행되면, Log Writer 프로세스는 리두 스트림을 온라인 리두 로그에 기록합니다. 배치 프로세싱의 경우처럼, 다수의 트랜잭션을 연속적으로 실행하는 경우에는 굳이 커밋을 여러 차례 수행할 필요가 없을 것입니다. 물론 가장 좋은 방법은 코드에 포함된 커밋의 숫자를 경우가 많습니다. 그 대신, 아래와 같은 커밋 구문을 실행할 수 있습니다: 위 명령은 리두 스트림을 (개별 커밋 단위가 아닌) 배치 형태로 로그 파일에 기록하도록 합니다. 이 테크닉을 이용하여 커밋이 빈번하게 발생되는 환경에서 로그 버퍼 플러싱(log-buffer flushing)의 빈도를 줄일 수 있습니다. 로그 버퍼의 쓰기 작업을 바로 수행하고자 하는 경우에는 아래 명령을 사용합니다: 특정 커밋 옵션을 데이터베이스의 디폴트 설정으로 사용하고자 하는 경우에는 아래 구문을 사용합니다: 위 명령은 명기된 옵션을 데이터베이스 레벨의 디폴트 설정으로 사용하도록 설정합니다. 또는 아래와 같이 세션 레벨로 커밋 옵션을 정의할 수도 있습니다: 다른 매개변수와 마찬가지로, 위 매개변수는 먼저 시스템 레벨에서 적용됩니다. 하지만 세션 레벨에 별도의 설정이 존재하는 경우 세션 레벨의 설정이 시스템 레벨의 설정보다 우선하며, COMMIT 구문에 설정된 옵션은 시스템 레벨, 세션 레벨의 설정보다 우선합니다. 이 옵션은 분산 트랜잭션(distributed transaction)에서는 사용할 수 없습니다. ACCOUNTS_NY 테이블의 레코드를 ACCOUNTS 테이블에 삽입하려 하는 경우를 가정해 봅시다. ACCOUNTS 테이블은 ACC_NO 컬럼에 프라이머리 키를 가지고 있습니다. ACCOUNTS_NY의 로우(row) 중 일부는 프라이머리 키 제약사항을 위반하고 있습니다. 여기서 아래와 같은 구문을 실행해 보기로 합니다: ACCOUNTS_NY 테이블에는 레코드가 전혀 추가되지 않았습니다. 이번에는 에러 로깅(error logging)을 활성화하고 같은 작업을 반복해 보기로 합시다. 먼저 DML 구문 실행 시 에러로 인해 거부 처리된 레코드를 임시 저장할 테이블을 생성해야 합니다. 이 테이블을 ERR_ACCOUNTS로 명명하기로 합니다. ACCOUNTS_NY 테이블의 10개 로우 중 6개 로우 만이 성공적으로 INSERT 되었습니다. 다른 4개의 로우는 에러로 인해 거부 처리되었습니다. 그 이유가 무엇인지 확인하기 위해 ERR_ACCOUNTS 테이블을 조회해 봅시다. ORA_ERR_NUMBER$ 컬럼은 DML 구문의 실행 과정에서 발생한 오라클 에러 코드를, ORA_ERR_MESG$ 컬럼은 에러 메시지를 저장하고 있습니다. 이 경우, 4개의 레코드가 pK_ACCOUNTS의 프라이머리 키 제약조건을 위반했기 때문에 거부 처리되었음을 알 수 있습니다. 또 이 테이블에는 ACCOUNTS 테이블의 모든 컬럼 값이 캡처되어 있습니다. ACCOUNTS 테이블의 ACC_NO 컬럼 값을 비교해 보면, 거부된 레코드의 ACC_NO 컬럼 값이 이미 ACCOUNTS 테이블 내에서 사용되고 있으며, ORA-00001 에러가 발생한 이유가 바로 이 때문임을 알 수 있습니다. 에러 로깅이 활성화되지 않았다면, 구문의 실행이 실패하고 아무런 레코드도 삽입되지 않았을 것입니다. 반면, 에러 로깅을 활성화하면 제약조건에 위반되는 레코드만이 거부되며, 다른 레코드는 모두 성공적으로 처리됩니다. pL/SQL 프로그램에는 기업의 프로세스, 또는 거래 과정에 관련된 민감한 정보가 포함되어 있을 수 있습니다. 따라서 pL/SQL 역시 테이블과 마찬가지로 보호되어야 합니다. 소스 코드에 대한 불법적인 조회를 방지하기 위해서, wrap 커맨드 라인 유틸리티를 사용할 수 있습니다. wrap 유틸리티는 이미 생성된 pL/SQL 스크립트에 대해서만 실행 가능합니다. 이 유틸리티는 일반 텍스트 형태의 입력으로부터 “wrapped” 파일을 생성합니다. 하지만, 경우에 따라 pL/SQL 코드 내부에서 “wrapper”를 다이내믹하게 생성해야 하는 경우도 있을 수 있습니다. 이러한 경우에는 소스 파일이 아직 존재하지 않기 때문에 wrap 유틸리티를 호출하는 것이 불가능합니다. Oracle Database 10g Release 2는 “wrapped” 포맷으로 코드를 생성하기 위한 새로운 패키지를 제공합니다. 이 패키지는 기존의 wrap 유틸리티를 (대체하는 것이 아니라) 보완하는 용도로 활용됩니다. wrap 유틸리티는 커맨드 라인 옵션을 통해 많은 수의 소스 파일에 대한 래핑(wrapping)을 수행하는 용도로 활용됩니다. 예를 들어 프로시저 p1을 “wrapped” 포맷으로 생성하고자 하는 경우를 생각해 봅시다: 이제 프로시저의 래핑이 성공적이었는지 확인해 봅시다. 아래와 같이 딕셔너리에서 소스 텍스트를 조회합니다. 첫 번째 라인(“procedure p1 wrapped”)에서 프로시저가 “wrapped” 포맷으로 생성되었음을 확인할 수 있습니다. DBMS_METADATA.GET_DDL() 함수를 통해 프로시저의 DDL을 생성하는 방법으로, 소스의 래핑 여부를 확인할 수도 있습니다. 또 경우에 따라서는 pL/SQL 코드는 생성하되, 프로시저는 생성하지 않는 방법을 선택할 수도 있습니다. 이 경우, 생성된 코드는 나중에 활용하기 위해 파일 또는 테이블에 저장하게 됩니다. 이와 같은 상황에서는 패키지 내의 다른 함수를 호출해야 합니다: WRAp 함수는 전달된 pL/SQL 코드를 래핑한 결과를 매개변수로 반환합니다. 매개변수는 플랫 파일 또는 테이블에 저장해 두었다가 나중에 활용할 수 있습니다. 이러한 방법은 코드 레벨의 보안을 침해하지 않고, 생성된 코드를 다른 서버에서 활용하고자 하는 경우 유용합니다. 위에서 소개한 방법은 저장된 코드의 전체 텍스트가 (32K의 길이 제한을 갖는) varchar2 데이터타입으로 전달 가능한 경우에만 효과가 있습니다. pL/SQL 코드가 32K를 초과하는 경우, 컬렉션 변수(collection variable)을 입력으로 활용하는 방법을 사용해야 합니다. 여기서는 DBMS_SQL 패키지에서 제공하는 varchar2 데이터타입을 사용합니다. 이 컬렉션 데이터타입(TABLE OF VARCHAR2)의 각 테이블 엘리먼트는 32KB의 텍스트를 저장하며, 필요에 따라 엘리먼트의 수를 확장할 수 있습니다. 예를 들어, 아래와 같이 정의된 myproc 프로시저를 래핑하는 경우를 생각해 봅시다: 물론 위 프로시저의 경우 굳이 컬예제를 단순화하기 위해 짧은 프로시저를 생성하려면 아래와 같은 pL/SQL 구문을 실행해야 합니다: 위 코드에서 정의된 l_input_code 변수는 일반 텍스트 코드를 저장하는 용도로 사용됩니다. 4번째 라인에서 10번째 라인까지는, 래핑 대상이 되는 코드가 정의되고 있습니다. 위 예제에서는 매우 짧은 코드가 사용되었지만, 실제 환경에서는 이 부분에 최대 32KB에 이르는 긴 코드가 들어가게 될 것입니다. 마찬가지로, 예제의 어레이에 사용된 엘리먼트의 수도 7개 밖에 되지 않지만, 실제로는 매우 많은 엘리먼트가 필요할 것입니다. 11번째 라인에서 15번째 라인에서는 “wrapped” 프로시저를 생성하기 위해 프로시저를 호출하는 방법을 보여주고 있습니다. 12번째 라인에서는 매개변수 DDL을 통해 컬렉션을 전달하고 있습니다. 여기서 어레이의 첫 번째 엘리먼트와 마지막(7번째) 엘리먼트에 커멘트를 추가했음을 참고하시기 바랍니다. 이 커멘트는 문법적으로 유효하지 않으므로, 13~14번째 라인에서 최소/최대 영역을 지정하여 2-6번째 엘리먼트만을 취하도록 하였습니다. 매개변수 LB는 어레이의 최소 영역(lower bound), 매개변수 HB는 어레이의 최대 영역(higher bound)를 의미합니다. 지금까지 설명한 방법으로, 프로시저의 길이에 관계없이 “wrapped” 프로시저를 생성하는 것이 가능합니다. C 언어를 사용해 본 독을 이해하고 있을 것입니다. C 프로그램에서는, 사용되는 컴파일러의 버전에 따라 특정 변수의 값이 달라질 수 있습니다. 문자열을 반환하는 간단한 함수의 예가 아래와 같습니다: 5번째 라인에서, ppval 변수의 값을 얻기 위해 pre-processor directive를 사용하고 있음을 주목하시기 바랍니다. ppval은 일반적인 pL/SQL 변수가 아닌 pre-processor 변수이기 때문에, $$ 기호를 사용하여 정의되고 있습니다. 또 이 라인을 컴파일 시점에 처리해야 함을 컴파일러에게 알리기 위해, $ 기호를 사용하였습니다 (따라서 if 대신 $if가 사용되었습니다). 이제, 이 ppval 변수에 다른 값을 적용하면서 함수를 컴파일해 봅시다. 이제 함수를 컴파일하여 실행합니다. 컴파일 과정에서 ppval의 값은 True로 설정되었습니다. 이번에는 변수의 값을 변경하고 함수를 다시 실행해 봅시다. 세션에서 ppval의 값이 FALSE로 설정되었음에도 불구하고, 함수는 이 값을 취하는 대신 컴파일 과정에서 설정된 값을 이용합니다. 함수를 다시 컴파일하고 실행해 봅시다. 컴파일 시점에 ppval의 값은 FALSE로 설정되었고, 이 값이 최종적으로 반환됩니다. 그렇다면 이 기능을 어떻게 활용할 수 있을까요 몇 가지 사례를 들 수 있습니다. 예를 들어, 추가적인 메시지를 표시하기 위한 디버그 플래그(debug flag)로 활용하거나, 프로그램을 플랫폼에 따라 다른 방법으로 컴파일하는 용도로 이용할 수 있습니다. 또 변수의 값이 런타임이 아닌 컴파일 과정에서 결정되므로, 런타임 효율성을 대폭적으로 향상시킬 수 있습니다. 위의 예제는 동일한 pre-processor 플래그가 (컴파일 대상이 되는) 모든 함수에 의해 참조되고 있는 경우에만 실행 가능합니다. 그렇다면 각 코드 별로 다른 플래그가 사용되는 경우에는 어떻게 될까요 예를 들어 calculate_interest 함수가 TRUE로 설정된ACTIVE_STATUS_ONLY 플래그를 갖고, apply_interest 함수가 FALSE로 설정된 FOREIGN_ACCOUNTS 플래그를 갖는다고 가정해 봅시다. 각 함수를 적절한 플래그와 함께 컴파일하려면 다음과 같이 실행해야 합니다: 여기서 세션 레벨의 설정은 사용되지 않음을 참고하시기 바랍니다. reuse settings 구일한 컴파일러 디렉티브가 사용됨을 보장합니다. Oracle Database 10g Release 2는 조건부 변수를 정의하는 기능 이외에도, 조건부 컴파일 과정에서 패키지의 정적 상수(static constant)를 점검하는 기능을 추가로 지원합니다. 예를 들어, Boolean 패키지 상수(packaged constant) 값에 따라 pL/SQL 프로시저의 결과를 디버를 아래와 같이 생성합니다: 패키지 상수가 $ 기호를 사용하지 않고 직접 참조되고 있음을 참고하시기 바랍니다. 이 경우, 세션/시스템 레벨의 조건부 컴파일 매개변수를 설정할 필요가 없습니다. 또 함수가 컴파일되는 동안, 별도의 구문을 전달할 필요도 없습니다. 그러면 작성된 프로시저를 실행해 봅시다: debug_pkg.debug_flag의 값이 FALSE로 설정되었기 때문에, 프로시저 실행 결과로 "F가 반환됩니다. 이번에는 상수 값을 변경해 봅시다: 이번에는 예상대로 프로시저가 “T”를 반환하였습니다. 여기서 한 가지 중요한 차이점을 발견하셨을 것입니다. 프로시저를 다시 컴파일하지 않고, 변경된 상수값을 자동으로 확인하고 있다는 것이 바로 그 차이입니다. 아래와 같은 에러 코드를 본 일이 있으십니까 위 에러는, dbms_output 패키지가 처리할 수 있는 최대 문자수(1백만 바이트)를 초과한 경우에 발생합니다. Oracle Database 10g Release 2에서는 이러한 제약이 해제되었습니다. 아래와 같은 명령을 사용하면 출력의 길이를 “무제한”으로 설정할 수 있습니다. 위 구문을 실행하면 다음과 같은 결과가 반환됩니다: 위에서, 디폴트로 설정된 최대 출력 문자수가 2000 바이트임을 확인할 수 있습니다. Oracle Database 10g Release 2에서 명령을 실행하면 다음과 같은 결과가 반환됩니다: 위의 경우, 디폴트 값은 “UNLIMITED”로 설정되어 있습니다. 또 dbms_output가 출력하는 최대 라인 길이 역시 제한되어 있다는 것도 문제가 되곤 합니다. 255 바이트보다 긴 라인이 존재하는 경우 표시되는 에러가 아래와 같습니다: Oracle Database 10g Release 2에서는 이러한 라인 길이의 제한 역시 없어졌습니다.SQL 및 pL/SQL 관련 기능
SQL 및 pL/SQL 관련 기능
Transparent Data Encryption
create table accounts
(
acc_no number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
SSN varchar2(9) ENCRYpT USING 'AES128',
acc_type varchar2(1) not null,
folio_id number ENCRYpT USING 'AES128',
sub_acc_type varchar2(30),
acc_open_dt date not null,
acc_mod_dt date,
acc_mgr_id number
)
alter system set encryption key authenticated BY "topSecret";External Table의 암호화
create table account_ext
organization external
(
type oracle_datapump
default directory dump_dir
location ('accounts_1_ext.dmp',
'accounts_2_ext.dmp',
'accounts_3_ext.dmp',
'accounts_4_ext.dmp')
)
parallel 4
as
select
ACC_NO,
FIRST_NAME,
LAST_NAME,
SSN ENCRYpT IDENTIFIED BY "topSecret",
ACC_TYpE,
FOLIO_ID ENCRYpT IDENTIFIED BY "topSecret",
SUB_ACC_TYpE,
ACC_OpEN_DT,
ACC_MOD_DT
from accounts;
XML 쿼리
XQuery
SQL> xquery
2 for $var1 in (1,2,3,4,5,6,7,8,9)
3 let $var2 := $var1 + 1
4 where $var2 < 6
5 order by $var2 descending
6 return $var2
7 /Result Sequence
------------------
5
4
3
2
create table acc_comm_log
(
acc_no number,
comm_details xmltype
);
insert into acc_comm_log
values
(
1,
xmltype(
'<COMMRECORD>
<COMMTYpE>EMAIL</COMMTYpE>
<COMMDATE>3/11/2005</COMMDATE>
<COMMTEXT>Dear Mr Smith</COMMTEXT>
</COMMRECORD>')
)
/insert into acc_comm_log
values
(
2,
xmltype(
'<COMMRECORD>
<COMMTYpE>LETTER</COMMTYpE>
<COMMDATE>3/12/2005</COMMDATE>
<COMMTEXT>Dear Mr Jackson</COMMTEXT>
</COMMRECORD>')
);insert into acc_comm_log
values
(
3,
xmltype(
'<COMMRECORD>
<COMMTYpE>pHONE</COMMTYpE>
<COMMDATE>3/10/2005</COMMDATE>
<COMMTEXT>Dear Ms potter</COMMTEXT>
</COMMRECORD>')
);
XMLTable
1 select t.column_value
2 from acc_comm_log a,
3 xmltable (
4 'for $root in $date
5 where $root/CommRecord/CommType!="EMAIL"
6 return $root/CommRecord/CommDate/text()'
7 passing a.comm_details as "date"
8* ) t
SQL> /COLUMN_VALUE
---------------------
3/12/2005
3/10/2005
쿼리에서는 구조화된 FLOWR 패턴이 사용됩니다.XQuery와 XMLTable의 비교
COMMIT 관련 개선 기능
COMMIT WRITE <option>
COMMIT WRITE WAIT;
COMMIT WRITE NOWAIT;
COMMIT WRITE BATCH;
COMMIT WRITE IMMEDIATE;
ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
ALTER SESSION SET COMMIT_WRITE = NOWAIT;
Error-Logging구문
SQL> insert into accounts
2 select * from accounts_ny;
insert into accounts
*
ERROR at line 1:
ORA-00001: unique constraint (ARUp.pK_ACCOUNTS) violated
exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS')Next, execute the earlier statement with the error-logging clause.
SQL> insert into accounts
2 select * from accounts_ny
3 log errors into err_accounts
4 reject limit 200
5 /6 rows created.
SQL> select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO
2 from err_accounts;ORA_ERR_NUMBER$ ORA_ERR_MESG$ ACC_NO
--------------- -------------------------------------------------- ------
1 ORA-00001: unique constraint (ARUp.pK_ACCOUNTS) vi 9997
olated
1 ORA-00001: unique constraint (ARUp.pK_ACCOUNTS) vi 9998
olated
1 ORA-00001: unique constraint (ARUp.pK_ACCOUNTS) vi 9999
olated
1 ORA-00001: unique constraint (ARUp.pK_ACCOUNTS) vi 10000
olated
소스 레벨의 코드 보호: WRAp 패키지
create or replace procedure p1 as
begin
null;
end;pL/SQL 구문 안에서, “wrapped” 포맷의 프로시저를 다이내믹하게 생성할 수 있습니다:
begin
dbms_ddl.create_wrapped
('create or replace procedure p1 as begin null; end;')
end;
/
SQL> select text from user_source where name = 'p1';TEXT
-----------------------------------------------------------------
procedure p1 wrapped
a000000
369
abcd
abcd
...and so on ...
SQL> select dbms_ddl.wrap
2 ('create or replace procedure p1 as begin null; end;')
3 from dual
4 /DBMS_DDL.WRAp('CREATEORREpLACEpROCEDUREp1ASBEGINNULL;END;')
----------------------------------------------------------------------
create or replace procedure p1 wrapped
a000000
369
abcd
abcd
... and so on ...
create or replace procedure myproc as
l_key VARCHAR2(200);
begin
l_key := 'ARUpNANDA';
end;
1 declare
2 l_input_code dbms_sql.varchar2s;
3 begin
4 l_input_code (1) := 'Array to hold the MYpROC';
5 l_input_code (2) := 'create or replace procedure myproc as ';
6 l_input_code (3) := ' l_key VARCHAR2(200);';
7 l_input_code (4) := 'begin ';
8 l_input_code (5) := ' l_key := ''ARUpNANDA'';';
9 l_input_code (6) := 'end;';
10 l_input_code (7) := 'the end';
11 sys.dbms_ddl.create_wrapped (
12 ddl => l_input_code,
13 lb => 2,
14 ub => 6
15 );
16* end;
pL/SQL의 조건부 컴파일: “Write Once, Execute Many”
1 create or replace function myfunc
2 return varchar2
3 as
4 begin
5 $if $$ppval $then
6 return 'ppVAL was TRUE';
7 $else
8 return 'ppVAL was FALSE';
9 $end
10* end;
SQL> alter session set plsql_ccflags = 'ppVAL:TRUE';Session altered.
SQL> alter function myfunc compile;Function altered.SQL> select myfunc from dual;MYFUNC
-------------------------------------
ppVAL was TRUE
SQL> alter session set plsql_ccflags = 'ppVAL:FALSE';Session altered.SQL> select myfunc from dual;MYFUNC
---------------------------------------------------------
ppVAL was TRUE
SQL> alter function myfunc compile;Function altered.SQL> select myfunc from dual;MYFUNC
---------------------------------------------------
ppVAL was FALSE
alter function calculate_interest compile
plsql_ccflags = 'ACTIVE_STATUS_ONLY:TRUE'
reuse settings;
alter function apply_interest compile
plsql_ccflags = FOREIGN_ACCOUNTS:TRUE'
reuse settings;
create or replace package debug_pkg
is
debug_flag constant boolean := FALSE;
end;
SQL> exec myprocDebug=F
create or replace package debug_pkg
is
debug_flag constant boolean := TRUE;
end;Then, execute the procedure again:
SQL> exec myprocDebug=T
DBMS 출력 문자수 제한의 해제
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTpUT", line 32
ORA-06512: at "SYS.DBMS_OUTpUT", line 97
ORA-06512: at "SYS.DBMS_OUTpUT", line 112
ORA-06512: at line 2
set serveroutput on
SQL> show serveroutput
serveroutput ON size 2000 format WORD_WRAppED
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAppED
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTpUT", line 35
ORA-06512: at "SYS.DBMS_OUTpUT", line 115
ORA-06512: at line 2