pajama

Problem description:

Oracle 11g utilities like tnsping, lsnrctl or sqlplus on AIX 6.1 hanged for about 2,5 minutes (150 seconds) before connection with alias to any destination when DNS server was unreachable. In the same conditions (host and configuration) 10g utilities connected without delay. Oracle*Net was configured for local naming method only – NAMES.DIRECTORY_PATH=(tnsnames) in the both 10g and 11g Oracle Home. The tnsnames.ora files where identical in the 10g and 11g Oracle Home. The aliases in the tnsnames.ora used host names instead of IP addresses in the connection descriptions; if a host name was substituted by IP address the delay disappeared for this alias. Nevertheless all the host names from tnsnames where described in the /etc/hosts and resolution order was hosts = local, bind in the netsvc.conf (analog of  the nsswitch.conf in other UNIXs). This configuration should guarantee local name resolution when DNS server was unreachable. What is the root reason of this delay in 11g Oracle*Net ?

Diagnostic:

First of all I checked configuration of the Oracle*Net – sqlnet.ora, tnsnames.ora and AIX configuration of network name resolution  – /etc/resolv.conf, /etc/netsvc.conf. The configuration was typical.

The next steps were Oracle*Net and OS level tracing both 11g and 10g tnsping utility when DNS was reachable and unreachable. To emulate unreachable DNS server it’s IP address was changed in the /etc/resolv.conf

The Oracle*Net Support level tracing was enabled:

TNSPING.TRACE_LEVEL = 16
TRACE_LEVEL_CLIENT = 16

The comparison of 11g Oracle*Net traces gave no clue. Traces with and without reachable DNS server were almost identical. The comparison of 10g and 11g Oracle*Net traces did not help too. There were some differences but generally they were similar.

To trace tnsping at OS level were used AIX truss utility (truss -aefo tnsping.trc tnsping db_test). I checked the traces but din’t found any system call (like gethostbyname, gethostbyname2, gethostbyaddr or getaddrinfo) which could use DNS for the name resolution.

Comparing 11g with 10g trace files I found some new actions in the 11g trace. Here are some parts of tracing tnsping (11g and 10g) when DNS server was unreachable:

10g part:

kopen(“/etc/resolv.conf”, O_RDONLY)    = 4
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(4, ” n a m e s e r v e r\t 1″.., 4096) = 38
kread(4, ” n a m e s e r v e r\t 1″.., 4096) = 0
statx(“/etc/resolv.conf”, 0x0FFFFFFFFFFFA2D8, 176, 0) = 0
close(4)                = 0
socket(1, 1, 0)            = 4
kfcntl(4, F_SETFD, 0x0000000000000001) = 0
connext(4, 0x0FFFFFFFFFFF9DA8, 1025)    Err#2  ENOENT
close(4)                = 0
kopen(“/etc/netsvc.conf”, O_RDONLY)    = 4
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(4, ” #   @ ( # ) 4 3        “.., 4096) = 4096
kread(4, ” o n   a n d   r e s o l”.., 4096) = 663
close(4)                = 0
_thread_self()            = 33816773
__libc_sbrk(0x0000000000010020)    = 0x00000001109A2AA0
_thread_self()            = 33816773
kopen(“/etc/hesiod.conf”, O_RDONLY)    Err#2  ENOENT
kopen(“/etc/irs.conf”, O_RDONLY)    Err#2  ENOENT
_thread_self()            = 33816773
getdomainname(0x09001000A00DB290, 1024) = 0
_thread_self()            = 33816773
_thread_self()            = 33816773
_thread_self()            = 33816773
getdomainname(0x09001000A00DB290, 1024) = 0
_thread_self()            = 33816773
_thread_self()            = 33816773
_thread_self()            = 33816773
_thread_self()            = 33816773
_thread_self()            = 33816773
_thread_self()            = 33816773
_thread_self()            = 33816773
getdomainname(0x09001000A00DB290, 1024) = 0
_thread_self()            = 33816773
_thread_self()            = 33816773
_thread_self()            = 33816773
getdomainname(0x09001000A00DB290, 1024) = 0
_thread_self()            = 33816773
_thread_self()            = 33816773
__libc_sbrk(0x0000000000010020)    = 0x00000001109B2AC0
kopen(“/etc/hosts”, O_RDONLY)        = 4
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kfcntl(4, F_SETFD, 0x0000000000000001) = 0
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(4, ” #   @ ( # ) 4 7\t 1 . 2″.., 4096) = 2029
kread(4, ” #   @ ( # ) 4 7\t 1 . 2″.., 4096) = 0
close(4)                = 0
socket(2, 1, 0)            = 4
getsockopt(4, 65535, 4104, 0x0FFFFFFFFFFFC124, 0x0FFFFFFFFFFFC120) = 0
connext(4, 0x00000001109B9E30, 16)    = 0
ngetsockname(4, 0x0FFFFFFFFFFFC1D4, 0x0FFFFFFFFFFFC1D0) = 0
getsockopt(4, 65535, 4097, 0x0FFFFFFFFFFFC324, 0x0FFFFFFFFFFFC320) = 0
getsockopt(4, 65535, 4098, 0x0FFFFFFFFFFFC324, 0x0FFFFFFFFFFFC320) = 0
setsockopt(4, 6, 1, 0x0FFFFFFFFFFFC32C, 4) = 0
kfcntl(4, F_SETFD, 0x0000000000000001) = 0
sigprocmask(2, 0x09001000A01BAAC0, 0x0FFFFFFFFFFFC330) = 0
_sigaction(13, 0x0FFFFFFFFFFFC460, 0x0FFFFFFFFFFFC4B8) = 0
thread_setmystate(0x0FFFFFFFFFFFBF20, 0x0000000000000000) = 0
kwrite(4, ” W0101 901 ,”.., 87) = 87
kread(4, ” A04 ” 5″.., 2064) = 65
close(4)                = 0
lseek(3, 27136, 0)            = 27136
kread(3, “\r\r ® V\r ¯”.., 512) = 512
kwrite(1, ” O K   ( 1 0   m s e c )”.., 13) = 13
kfcntl(1, F_GETFL, 0x0000000000000008) = 67110914
kfcntl(1, F_GETFL, 0x0000000000000008) = 67110914
close(3)                = 0
kfcntl(1, F_GETFL, 0x00000000120400C5) = 67110914
kfcntl(2, F_GETFL, 0x00000000120400C5) = 67110914
_exit(0)

11g part:

kopen(“/etc/resolv.conf”, O_RDONLY)    = 4
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(4, ” n a m e s e r v e r\t 1″.., 4096) = 38
kread(4, ” n a m e s e r v e r\t 1″.., 4096) = 0
statx(“/etc/resolv.conf”, 0x0FFFFFFFFFFF7B08, 176, 0) = 0
close(4)                = 0
socket(1, 1, 0)            = 4
kfcntl(4, F_SETFD, 0x0000000000000001) = 0
connext(4, 0x0FFFFFFFFFFF75D8, 1025)    Err#2  ENOENT
close(4)                = 0
kopen(“/etc/netsvc.conf”, O_RDONLY)    = 4
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(4, ” #   @ ( # ) 4 3        “.., 4096) = 4096
kread(4, ” o n   a n d   r e s o l”.., 4096) = 663
close(4)                = 0
_thread_self()            = 22020181
_thread_self()            = 22020181
kopen(“/etc/hesiod.conf”, O_RDONLY)    Err#2  ENOENT
kopen(“/etc/irs.conf”, O_RDONLY)    Err#2  ENOENT
_thread_self()            = 22020181
getdomainname(0x09001000A00DB290, 1024) = 0
_thread_self()            = 22020181
_thread_self()            = 22020181
_thread_self()            = 22020181
getdomainname(0x09001000A00DB290, 1024) = 0
_thread_self()            = 22020181
_thread_self()            = 22020181
_thread_self()            = 22020181
_thread_self()            = 22020181
_thread_self()            = 22020181
_thread_self()            = 22020181
_thread_self()            = 22020181
getdomainname(0x09001000A00DB290, 1024) = 0
_thread_self()            = 22020181
_thread_self()            = 22020181
_thread_self()            = 22020181
getdomainname(0x09001000A00DB290, 1024) = 0
_thread_self()            = 22020181
_thread_self()            = 22020181
__libc_sbrk(0x0000000000010020)    = 0x0000000110A699A0
kopen(“/etc/hosts”, O_RDONLY)        = 4
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kfcntl(4, F_SETFD, 0x0000000000000001) = 0
kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(4, ” #   @ ( # ) 4 7\t 1 . 2″.., 4096) = 2029
kread(4, ” #   @ ( # ) 4 7\t 1 . 2″.., 4096) = 0
close(4)                = 0
 kopen(“/etc/hosts”, O_RDONLY)        = 4
 kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
 kfcntl(4, F_SETFD, 0x0000000000000001) = 0
 kioctl(4, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
 kread(4, ” #   @ ( # ) 4 7\t 1 . 2″.., 4096) = 2029
 kread(4, ” #   @ ( # ) 4 7\t 1 . 2″.., 4096) = 0
 socket(2, 2, 0)            = 5
getsockopt(5, 65535, 4104, 0x0FFFFFFFFFFF65E4, 0x0FFFFFFFFFFF65E0) = 0
connext(5, 0x09001000A0024658, 16)    = 0
_esend(5, 0x0FFFFFFFFFFF74F0, 35, 0, 0x0000000000000000) = 35
_poll(0x0FFFFFFFFFFF66B0, 1, 5000) (sleeping…)
_poll(0x0FFFFFFFFFFF66B0, 1, 5000)    = 0
close(5)                = 0
 socket(2, 2, 0)            = 5
getsockopt(5, 65535, 4104, 0x0FFFFFFFFFFF65E4, 0x0FFFFFFFFFFF65E0) = 0
connext(5, 0x09001000A0024658, 16)    = 0
_esend(5, 0x0FFFFFFFFFFF74F0, 35, 0, 0x0000000000000000) = 35
_poll(0x0FFFFFFFFFFF66B0, 1, 10000) (sleeping…)
_poll(0x0FFFFFFFFFFF66B0, 1, 10000)    = 0
close(5)                = 0
socket(2, 2, 0)            = 5
getsockopt(5, 65535, 4104, 0x0FFFFFFFFFFF65E4, 0x0FFFFFFFFFFF65E0) = 0
connext(5, 0x09001000A0024658, 16)    = 0
_esend(5, 0x0FFFFFFFFFFF74F0, 35, 0, 0x0000000000000000) = 35
_poll(0x0FFFFFFFFFFF66B0, 1, 20000) (sleeping…)
_poll(0x0FFFFFFFFFFF66B0, 1, 20000)    = 0
close(5)                = 0
socket(2, 2, 0)            = 5
getsockopt(5, 65535, 4104, 0x0FFFFFFFFFFF65E4, 0x0FFFFFFFFFFF65E0) = 0
connext(5, 0x09001000A0024658, 16)    = 0
_esend(5, 0x0FFFFFFFFFFF74F0, 35, 0, 0x0000000000000000) = 35
_poll(0x0FFFFFFFFFFF66B0, 1, 40000) (sleeping…)
_poll(0x0FFFFFFFFFFF66B0, 1, 40000)    = 0
close(5)                = 0
 socket(2, 2, 0)            = 5
getsockopt(5, 65535, 4104, 0x0FFFFFFFFFFF65E4, 0x0FFFFFFFFFFF65E0) = 0
connext(5, 0x09001000A0024658, 16)    = 0
_esend(5, 0x0FFFFFFFFFFF74F0, 27, 0, 0x0000000000000000) = 27
_poll(0x0FFFFFFFFFFF66B0, 1, 5000) (sleeping…)
_poll(0x0FFFFFFFFFFF66B0, 1, 5000)    = 0
close(5)                = 0
socket(2, 2, 0)            = 5
getsockopt(5, 65535, 4104, 0x0FFFFFFFFFFF65E4, 0x0FFFFFFFFFFF65E0) = 0
connext(5, 0x09001000A0024658, 16)    = 0
_esend(5, 0x0FFFFFFFFFFF74F0, 27, 0, 0x0000000000000000) = 27
_poll(0x0FFFFFFFFFFF66B0, 1, 10000) (sleeping…)
_poll(0x0FFFFFFFFFFF66B0, 1, 10000)    = 0
close(5)                = 0
socket(2, 2, 0)            = 5
getsockopt(5, 65535, 4104, 0x0FFFFFFFFFFF65E4, 0x0FFFFFFFFFFF65E0) = 0
connext(5, 0x09001000A0024658, 16)    = 0
_esend(5, 0x0FFFFFFFFFFF74F0, 27, 0, 0x0000000000000000) = 27
_poll(0x0FFFFFFFFFFF66B0, 1, 20000) (sleeping…)
_poll(0x0FFFFFFFFFFF66B0, 1, 20000)    = 0
close(5)                = 0
socket(2, 2, 0)            = 5
getsockopt(5, 65535, 4104, 0x0FFFFFFFFFFF65E4, 0x0FFFFFFFFFFF65E0) = 0
connext(5, 0x09001000A0024658, 16)    = 0
_esend(5, 0x0FFFFFFFFFFF74F0, 27, 0, 0x0000000000000000) = 27
_poll(0x0FFFFFFFFFFF66B0, 1, 40000) (sleeping…)
_poll(0x0FFFFFFFFFFF66B0, 1, 40000)    = 0
 close(5)                = 0
close(4)                = 0
socket(2, 1, 0)            = 4
kfcntl(4, F_GETFL, 0x0000000000000000) = 2
kfcntl(4, F_SETFL, 0x0000000000000006) = 0
kioctl(4, -2147195266, 0x0FFFFFFFFFFF89C8, 0x0000000000000000) = 0
getsockopt(4, 65535, 4104, 0x0FFFFFFFFFFF9594, 0x0FFFFFFFFFFF9590) = 0
connext(4, 0x0000000110A76590, 16)    = 0
kfcntl(4, F_GETFL, 0x0000000000000000) = 6
kfcntl(4, F_SETFL, 0x0000000000000002) = 0
kioctl(4, -2147195266, 0x0FFFFFFFFFFF89C8, 0x0000000000000000) = 0
ngetsockname(4, 0x0FFFFFFFFFFF9FD8, 0x0FFFFFFFFFFF9680) = 0
getsockopt(4, 65535, 4097, 0x0FFFFFFFFFFFA634, 0x0FFFFFFFFFFFA630) = 0
getsockopt(4, 65535, 4098, 0x0FFFFFFFFFFFA634, 0x0FFFFFFFFFFFA630) = 0
setsockopt(4, 6, 1, 0x0FFFFFFFFFFFA63C, 4) = 0
kfcntl(4, F_SETFD, 0x0000000000000001) = 0
sigprocmask(2, 0x09001000A01BAAC0, 0x0FFFFFFFFFFFB0C0) = 0
_sigaction(13, 0x0FFFFFFFFFFFB1F0, 0x0FFFFFFFFFFFB248) = 0
thread_setmystate(0x0FFFFFFFFFFFACB0, 0x0000000000000000) = 0
__libc_sbrk(0x0000000000010020)    = 0x0000000110A799C0
kwrite(4, ” W0101 :01 ,”.., 87) = 87
kread(4, ” A04 ” 5″.., 8208) = 65
close(4)                = 0
lseek(3, 27136, 0)            = 27136
kread(3, “0E\t Ý \\t Þ”.., 512) = 512
kwrite(1, ” O K   ( 1 5 0 0 2 0   m”.., 17) = 17
kfcntl(1, F_GETFL, 0x0000000000000008) = 67110914
kfcntl(1, F_GETFL, 0x0000000000000008) = 67110914
close(3)                = 0
kfcntl(1, F_GETFL, 0x0000000011500055) = 67110914
kfcntl(2, F_GETFL, 0x0000000011500055) = 67110914
_exit(0)

We can see some new actions in the 11g part:

• /etc/hosts look up were done 2 times; after the second lookup the file wasn’t close;
• socket(2, 2, 0) was opened and closed 8 times (2 cycles by 4 times);
• every time through this socket was sent a message (_esend) and status of the message queue was checked (_poll);
• every time the checking of this message queue was waiting for response and finally timed out;
• the waiting time was doubled (5000, 10000, 20000, 40000 ms) in the every next try in the cycle;
• if we sum all the waiting time (5+10+20+40)*2 = 150 seconds !

The question is why all this new actions were done?

The reason

The answer is IPv6. It’s support was included in 11g Oracle*Net.

Even when the host name was resolved in IPv4 successfuly, the second look up in /etc/hosts is needed for IPv6 address host name resolution. If IPv6 address was not found in /etc/hosts then Oracle*Net requests DNS server for IPv6 name resolution because name resolution order is local, bind configured in the /etc/netsvc.conf.

Solution

To resolve the problem we should replace local, bind by local4, bind4  in the /etc/netsvc.conf. The meaning of the local4 and bind4 is explained in the comments in /etc/netsvc.conf:

# bind4       Uses BIND/DNS services for resolving only IPv4 addresses
# bind6       Uses BIND/DNS services for resolving only IPv6 addresses
# local4      Searches the local /etc/hosts file for resolving only IPv4 addresses
# local6      Searches the local /etc/hosts file for resolving only IPv6 addresses

https://odenysenko.wordpress.com/2012/02/11/investigation-of-the-11g-oraclenet-connection-delay-when-dns-server-is-unreachable/#more-482

안녕하세요. 오라클 데이터베이스에서 Microsft Access (mdb) 파일을 조회하는 기능을 테스트해보았습니다.

테스트 환경은 윈도우 10 64비트, 오라클 데이터베이스 11gR2 64비트 입니다.

먼저 Database Gateway가 설치되어야 합니다. 오라클 기본 설치 구성 중 제외하지 않았다면 사용할 수 있습니다.

mdb 파일은 odbc 를 통해 접근할 수 있으므로 DG4ODBC 설정을 했습니다. 10g에서는 HSODBC라고 하죠.

참고로 Database Gateway는 Oracle Document 232482.1에 따르면 별도의 라이센스는 필요하지 않다고 합니다.

아래는 해당 문서 내용의 일부입니다.

The purpose of this note is to clarify the licensing considerations for the Oracle Database Gateway products, such as ODBC, SQL Server, Sybase, DRDA, and more.
The licensing applies to both Enterprise and Standard RDBMS editions.

The Oracle Database Gateway for ODBC (DG4ODBC) is included in your Oracle Database Server license without extra cost. If DG4ODBC is installed on a different machine from where the Oracle Database Server is installed then it is still covered by the Oracle Database Server license and that install of DG4ODBC does not need a separate license.

오라클 데이터베이스 설치는 전부 기본 설정으로 했고 아래의 설정파일만 수정했습니다.

1. %ORACLE_HOME%\network\admin\tnsnames.ora

2. %ORACLE_HOME%\network\admin\.ora

3. %ORACLE_HOME%\network\admin\initdg4odbc.ora

4. odbc 데이터소스를 정의한 화면입니다. sample.mdb 파일을 선택하고 확인을 누릅니다.

5. sample odbc 데이터소스에 대한 데이터베이스 링크를 만들고 SQL을 실행한 화면입니다.

linux에서 먼저 시도해봤는데 오류 해결방법을 아직 찾지 못해 윈도우로 해보니 비교적 쉽게(?) 되네요.

참고:

Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations (Doc ID 232482.1)

https://docs.oracle.com/cd/E11882_01/gateways.112/e12013/configodbc.htm#OTGIS110

https://stackoverflow.com/questions/24081001/how-to-query-an-external-ms-access-db-from-an-oracle-db

4KB가 넘는 이미지 데이터가 hex string으로 존재하고 이를 이미지 형태로 ORACLE 테이블에 입력하는 예제입니다.

먼저 아래와 같은 함수를 만듭니다. 함수는 stack overflow의 글에서 참조했습니다.

기본적인 기능만 있어 예외처리가 필요할 수 있습니다.

create or replace function hextoblob(data in clob) return blob

is

    v_blob    blob;

    v_start  pls_integer := 1;

    v_buffer pls_integer := 4000;

begin

    dbms_lob.createtemporary(v_blob, true);

    for i in 1..ceil(dbms_lob.getlength(data) / v_buffer)

    loop

        dbms_lob.append(v_blob, hextoraw(DBMS_LOB.SUBSTR(data, v_buffer, v_start)));

        v_start := v_start + v_buffer;

    end loop; 

    return v_blob;

end;

위의 예제는 NULL값 처리는 제외된 내용입니다. 관련 내용은 아래 링크를 참고해주세요.

작업 전에 hex string 데이터를 CLOB 필드에 입력합니다. 

$ sqlplus scott/tiger

SQL> create table img (img clob);

Table created.

$ cat img.ctl     << SQL*Loader control file

load data

infile '/work2/INFORMIX/1150FC9W3/HDR_PRI/unload/img.unl'

append

into table img

( IMG CHAR(1000000) )

$  sqlldr userid=scott/tiger control='./img.ctl'

SQL*Loader: Release 11.2.0.1.0 - Production on Tue Nov 22 10:51:25 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1

그리고 입력된 hex string 을 4000바이트씩 자른 후, hextoraw 함수로 변환하는 것입니다.

hextoraw 함수는 CHAR, VARCHAR2, NCHAR, NVARCHAR2 타입만 지원하므로 잘라서 처리해야 합니다.

SQL> create table test_lob (img blob);

Table created.

insert into test_lob select hextoblob(img) from img;

1 row created.

참조 사이트:

http://stackoverflow.com/questions/8549138/oracle-rawtohex-function-what-happens-if-return-value-exceeds-varchar2-limit

Windows 7 64bit에서 오라클 11gR2 클라이언트를 설치할 때 필요 조건 검사 수행후 화면이 사라지는 경우가 있습니다.

이는 시스템에 jre가 32비트로 설치되었기 때문일 수 있습니다. 

설치시 64비트의 jre나 jdk의 경로를 지정하여 해결할 수 있습니다. 또는 32비트의 클라이언트를 설치할 수도 있겠지요.

Linux/Unix의 경우:

setup -jreloc 

Windows의 경우:

setup.exe -jreloc C:\Oracle\Middleware\jdk160_18\jre

참조: 

http://blog.naver.com/PostList.nhn?blogId=lsv400

https://forums.oracle.com/forums/thread.jspa?threadID=2190825

The hash value is the high speed method for locating the statement, but the unique identifier in v$sql is (hash_value, address, child_number) In Oracle 10g, there is a new column called the sql_id which is closer to unique (less chance of collision) than the hash_value, and this also has a high-speed access path into v$sql. (But again, is only unique there when combined with address and child_number)

Starting with Oracle Database 10g Release 1, the column SQL_ID is used to uniquely identify a cursor in views such as V$SQL and V$OPEN_CURSOR. Earlier releases use a combination of the HASH_VALUE and ADDRESS columns for this purpose.

REPLACE 함수와 비슷하게 문자열을 치환해주는 기능을 하는데 표현식을 이용할 수 있어서

대상 문자열이 숫자 또는 문자를 판단할 때 간편하게 사용할 수 있습니다.

1. 단순 문자열 치환 : TRANSLATE('대상문자열', '비교문자', '바꿀문자')
    > select translate('12345', '1', 'x')  from dual;
    > select replace('12345', '1', 'x')  from dual;

 둘다 결과값은 동일합니다.

2. 숫자 제거 : TRANSLATE('대상문자열', ' +.0123456789', ' ')
    > select translate('abc1def2', ' +.0123456789', ' ')  from dual

 숫자 1,2 가 제거되고 'abcdef' 을 리턴합니다.

 응용하면, 
 > select nvl(length(translate('a', ' +.0123456789', ' ')),0)  from dual     ==> 1을 리턴
 > select nvl(length(translate('1', ' +.0123456789', ' ')),0)  from dual     ==> 0을 리턴

 위와 같이 숫자 여부 판단에 사용될 수 있습니다. 왠지 숫자여부 판단하는 함수가 있을듯한데;;

3. 문자 제거 : TRANSLATE('대상문자열', '0123456789'||'대상문자열', '0123456789')
    > select translate('abc1def2', '0123456789' || 'abc1def2', '0123456789')  from dual          ==> 12를 리턴

 응용하면,
 > select translate('010-123-4567', '0123456789' || '010-123-4567', '0123456789')  from dual  ==> 0101234567 을 리턴

 예) select TRANSLATE('전화번호', '0123456789'||'전화번호', '0123456789') from dual; 
 숫자만 리턴됩니다~

http://blog.naver.com/kdi85/30097007828

## 샘플 데이터

Row# CD SHOP_CD PER

1 SG A 9.37

2 SP B 14.71

3 SP A 5.71

Row# CD SHOP_CD PER

1 SG P 0

2 SG B 0

3 SG A 0

4 SP P 0

5 SP B 7.14

6 SP A 3.03

## CD 컬럼 기준으로 SHOP_CD 컬럼 데이터 그룹핑

with kk as (

select '1' as Row#, 'SG' as CD, 'A' as SHOP_CD, '9.37' as PER from dual

union all

select '2','SP','B','14.71' from dual

union all

select '3','SP','A','5.71' from dual

)

select CD,min(decode(cnt,1,SHOP_CD)) || ' & ' || min(decode(cnt,2,SHOP_CD))

from (select kk.*,row_number() over(partition by CD order by CD) cnt from kk) group by CD;

with kk as (

select '1' as Row#, 'SG' as CD, 'P' as SHOP_CD, '0' as PER from dual

union all

select '2','SG','B','0' from dual

union all

select '3','SG','A','0' from dual

union all

select '4','SP','P','5.71' from dual

union all

select '5','SP','B','7.14' from dual

union all

select '6','SP','A','3.03' from dual

)

select CD,min(decode(cnt,1,SHOP_CD)), min(decode(cnt,2,SHOP_CD)), min(decode(cnt,3,SHOP_CD))

from (select kk.*,row_number() over(partition by CD order by CD) cnt from kk)

group by CD;

http://blog.daum.net/_blog/BlogTypeView.do?blogid=0KEP0&articleno=7609595&categoryId=591133®dt=20100106103323

In this Document

  Purpose
  Scope and Application
  Parameter DIRECT: Conventional Path Export Versus Direct Path Export
     1. Introduction.
     2. Performance issues.
     3. Restrictions. 
     4. Security considerations. 
     5. Typical errors.
     6. Known defects. 
  References

Applies to:

Purpose

Scope and Application

Parameter DIRECT: Conventional Path Export Versus Direct Path Export

1. Introduction.

1.1. Starting with Oracle7 release 7.3, the Export utility provides two methods for exporting table data: 
- Conventional Path Export 
- Direct Path Export 

1.2. Conventional path Export. 
Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into the buffer cache, and rows are transferred to the evaluating buffer. The data, after passing ｅxpression evaluation, is transferred to the Export client, which then writes the data into the export file. 

1.3. Direct path Export. 
When using a Direct path Export, the data is read from disk directly into the export session's program global area (PGA): the rows are transferred directly to the Export session's private buffer. This also means that the SQL command-processing layer (evaluation buffer) can be bypassed, because the data is already in the format that Export expects. As a result, unnecessary data conversion is avoided. The data is transferred to the Export client, which then writes the data into the export file. 

1.4. The parameter DIRECT specifies whether you use the direct path Export (DIRECT=Y) or the conventional path Export (DIRECT=N). 

1.5. To use direct path Export, specify the DIRECT=Y parameter on the command line or in the parameter file. The default is DIRECT=N, which extracts the table data using the conventional path. 

1.6. This parameter is only applicable to the original export client. Export DataPump (expdp) uses a Direct Path unload by default and switches to External Table mode if required.

2. Performance issues.

2.1. Direct path Export can be much faster than Conventional path Export because the SQL command-processing layer is bypassed.

2.2. You may be able to improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export.  Your exact performance gain depends upon the following factors: 
- DB_BLOCK_SIZE 
- the types of columns in your table 
- your I/O layout (the drive receiving the export file should be separate from the disk drive where the database files reside) 

The following values are generally recommended for RECORDLENGTH: 
- multiples of the file system I/O block size 
- multiples of DB_BLOCK_SIZE 

For example, invoking a Direct path Export with a maximum I/O buffer of 64kb can improve the performance of the Export with almost 50%. This can be achieved by specifying the additional Export parameters DIRECT and RECORDLENGTH. E.g.:

2.3. A Direct path Export does not influence the time it takes to Import the data. That is, an export file created using Direct path Export or Conventional path Export, will take the same amount of time to Import. 

3. Restrictions.

3.1. Command-line versus interactive. 
You cannot invoke a direct path Export using the interactive method. To invoke a direct path Export, you must use either the command-line method or a parameter file. 

3.2. Export mode. 
You cannot use the DIRECT=Y parameter when exporting in tablespace-mode  (i.e. when specifying the parameter TRANSPORT_TABLESPACES=Y).  You can use the DIRECT=Y parameter when exporting in full, user or table mode (i.e.: when specifying FULL=Y or OWNER=scott or TABLES=scott.emp). 

3.3. Objects and LOBs. 
In versions of Export prior to 8.1.5, you could not use direct path Export for tables containing objects and LOBs. If you tried to, their rows were not exported. This behavior has changed in Oracle8i and Oracle9i. Rows in tables that contain objects and LOBs will now be exported using conventional path, even if direct path was specified. Import will correctly handle these conventional path tables within direct path dump files. 
Note that when you use Export release 8.0 on an Oracle9i database, then Export doesn't export rows from tables containing objects and LOBs when you have specified a direct path load (DIRECT=y). 

3.4. Export parameter QUERY. 
The parameter QUERY applies onLY to conventional path Export. It cannot be specified in a direct path export (DIRECT=Y).  This QUERY parameter allows you to select a subset of rows from a set of tables when doing a table mode export. For example, if user scott wants to export only those employees whose job title is salesman and whose salary is less than 1600, he could do the following (this example is UNIX-based):

3.5. Export parameter BUFFER. 
The BUFFER parameter applies onLY to conventional path Export. It has no effect on a direct path Export. This BUFFER parameter specifies the size (in bytes) of the buffer used to fetch rows. It determines the maximum number of rows in an array, fetched by Export.  For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file. 

3.6. Export parameter RECORDLENGTH. 
The RECORDLENGTH parameter specifies the length (in bytes) of the file record. You can use this parameter to specify the size of the Export I/O buffer (highest value is 64 kb). Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to disk.  It does not affect the operating system file block size.  If you do not define this parameter, it defaults to your platform-dependent value for BUFSIZ (1024 bytes in most cases). 

3.7. Character Set Conversion. 
A Direct path Export can only export the data when the NLS_LANG environment variable of the session who is invoking the export, is equal to the database characterset. If NLS_LANG is not set (default is AMERICAN_AMERICA.US7ASCII) and/or NLS_LANG is different, Export will display the warning EXP-41 and abort with EXP-0. For example:

Setting NLS_LANG to the same value as the database character set (e.g.: NLS_LANG=AMERICAN_AMERICA.UTF8 in previous example), and retrying the export, will resolve this error. 
Note that this restriction only applies when exporting with an Oracle8i and below export utility. 

3.8. Compatibility. 
When using an Oracle7 or lower EXPORT release on an Oracle8i or higher release database, you have to use the default CONVENTIONAL path export (i.e.: do not specify DIRECT=YES in the export command statement).
Also beware that export does not export rows from tables containing objects and LOBs when you use Export release 8.0 on an Oracle8i or higher release database and have specified a direct path load (DIRECT=Y). 

4. Security considerations.

4.1. Virtual Private Database (VPD) and Oracle Label Security are enforced during a Conventional Path Export. The export will terminate successfully, but with the warning: EXP-79 "Data in table %s is protected. Conventional path may only be exporting partial table." 
VPD and Oracle Label Security (Fine Grained Access Control - FGAC) are not enforced during Direct Path Exports. 

4.2. The following users are exempt from Virtual Private Database and Oracle Label Security enforcement regardless of the export mode (Direct or Conventional), application, or utility used to extract data from the database:
a. the database user SYS; 
b. database users granted the Oracle9i EXEMPT ACCESS POLICY privilege, either directly or through a database role. 
WARNING! 
- Any user who is granted the Oracle9i EXEMPT ACCESS POLICY privilege is completely free from the enforcement of VPD and Oracle Label Security. This is a powerful privilege and should be carefully managed. 
- The Oracle9i EXEMPT ACCESS POLICY privilege does not affect the enforcement of traditional object privileges such as SELECT, INSERT, UPDATE, and DELETE. These privileges are enforced even if a user has been granted the EXEMPT ACCESS POLICY privilege. 

5. Typical errors.

5.1. EXP-00067 
Meaning: "Direct path cannot export %s which contains object or lob data." 
An Oracle7 and Oracle8 Direct Path export give EXP-67 because of tables with LOB datatype. Workaround: use Conventional Path export. 
Advanced Queueing (AQ) tables with LOB datatypes can also cause EXP-67 When these AQ tables are not being used, then dropping these tables could be another workaround (see references). 

5.2. EXP-00041 
Meaning: "Export done in server's %s, different from user's character set %s" 
A Direct Path export gives EXP-41 and EXP-0 when the export session characterset (NLS_LANG) is different than the database characterset. 
Solution: make the user session NLS_LANG equal to database characterset (when problem persists: re-run script catexp.sql). 

5.3. EXP-00079 
Meaning: "Data in table %s is protected. Conventional path may only be exporting partial table" 
A Conventional Path export uses the SQL SELECT statement to extract the data. As a result, the "Fine Grained Access Control" policies also apply during the Conventional Path export. 
Solution: in Oracle8i perform the export as user SYS or perform a Direct Path Export. In Oracle9i the privilege EXEMPT ACCESS POLICY can be used, regardless of the export mode. 

6. Known defects.

Below an overview of known DIRECT path related defects in the various export releases:

Bug:498891 Direct Export can use excessive private memory
Fixed: 7.3.4.0 Workaround: use Conventional Path export 

Bug:514344 Direct EXPORT may omit trailing NULLs causing IMP-9 for very long rows.
Fixed: 7.3.4.0 Workaround: Use larger RECORDLENGTH (64000). 

Bug:558511 Direct Export generated on platform with totally different code page (e.g. EBCDIC) will cause IMP-10 (e.g. when importing on ASCII platform).
Fixed: 8.0.6.0 Workaround: use Conventional Path export 

Bug:690413 Direct Export could raise ORA-600 [729] (space leak) if it fails due to running out of disk space, or similar problem. 
Fixed: 8.0.6.0 Workaround: use Conventional Path export or ensure that Export does not fail (disk or similar cause). 

Bug:1092638 Direct Export with MTS may result in a dump (ORA-7445). 
Fixed: 8.1.7.0 Workaround: don't use MTS for Direct export. 

Bug 1324748 Direct Export of an IOT may yield ORA-600 [2845] if the primary key ends with a ROWID column (not a public bug)
Fixed: 8.1.7.1 Workaround: use Conventional Path export 

Bug:1672945 Direct Export causes ORA-600 [729] when tablespace is offline 
Fixed: 9.0.1.0 Workaround: use Conventional Path export or make sure tablespace if online when starting Export. 

Bug:1477959 Direct Export for a table containing columns of type ROWID could fail with ORA-600 [6193] or ORA-600 [6190] or ORA-4030 
Fixed: 8.1.7.4 and 9.0.1.3 and 9.2.0.1 
Workaround: use Conventional Path export 

Bug 1820663 Direct Export of a table can generate duplicate column names (not a public bug) 
Fixed: 9.2.0 Workaround: use Conventional Path export 

Bug 1686987 Direct Export can result in ORA-600 [6191] with a possible stack: kdeuwrd kdeuprd kdeuscn kueuld (not a public bug)
Fixed: 8.1.7.4 and 9.0.1.3 and 9.2.0 
Workaround: use Conventional Path export 

Bug 1805146 DROP Functional index does not clean up dictionary - Can cause corrupt export file (not a pubic bug). 
Fixed: 8.1.7.3 9.0.1.3 9.2.0.1 
Workaround: re-run export in Conventional path mode 
See also alert in:
Note:148740.1 "ALERT: Export Of Table With Dropped Functional Index May Cause IMP-20 on Import" 

Bug:2666174 Export in direct path mode can produce a corrupt export dump file if an ORA-1555 (snapshot too old) error occurs. 
Fixed: 9.2.0.5 and Oracle10g (= export will now terminate unsuccessfully with EXP-0 error) 
Workaround: Ensure that no other transaction is updating the same table, and that the size of the rollback segment is sufficient. Or use Conventional Path export. 

Bug:2308033 Direct Export of a long row and with an 8k or larger database blocksize, can result in ORA-600 [6191] or ORA-600 [6193] 
Fixed: 9.0.1.4 and 9.2.0.2 and Oracle10g 
Workaround: use Conventional Path export 

Bug:3083560 ORA-1410 / ORA-8103 from direct path export if concurrent DML occurs. 
Fixed: 9.2.0.5 and Oracle10g 
Workaround: use Conventional Path export 

Bug:3230116 Direct Export of Compressed table fails with: ORA-600 [6193] 
Fixed: 9.2.0.5 and Oracle10g 
Workaround: use Conventional Path export 

Bug:3579567 9.2.0.5 Import of Compressed table fails: IMP-9 (IMP-00009) 
Fixed: 9.2.0.6 and 10.1.0.4 and 10.2.0.1 
Workaround: use Conventional Path export 

Bug:3596139 10.1.0.2 ORA-10632 on direct path export of IOT 
Fixed: 10.1.0.4 and 10.2.0.1 
Workaround: use Conventional Path export 

Bug:3868753 ORA-1410 / ORA-8103 from direct path export if concurrent inserts against ASSM tables occur. 
Fixed: 9.2.0.7 and 10.1.0.5 and 10.2.0.1 
Workaround: use Conventional Path export 

Bug:4085785 9.2.0.6 Export of Compressed Partition: ORA-600 [6191] 
Fixed: 10.2.0.1 
Workaround: use Conventional Path export 

References

append 힌트의 효용성에 대한 논란이 잠시 있어 막간을 이용, 확인차 테스트해봤습니다..

아래 링크를 차례대로 읽어보세요...

i) NOLOGGING이 아닌 테이블에는 INSERT /*+ APPEND */ 효과가 없다? 
   : "APPEND 힌트의 진실과 거짓" --> http://blog.naver.com/addibuddi/22476554

ii) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869

위 사이트에서 Kyte는 다음과 같이 정리함..

Table Mode    Insert Mode     ArchiveLog mode      result
-----------   -------------   -----------------    ----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ""                   redo generated
NOLOGGING     no append       ""                   redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated
NOLOGGING     no append       noarchive log mode   redo generated

이제 테스트를 시작합니다..

############################################
### 1. logging 테이블에 conventional INSERT
############################################

SQL> conn scott
Enter password: 
Connected.

SQL> create table tab3 logging
as select * from all_objects
where 1=2;

Table created.

SQL> insert into tab3
select * from all_objects
;

36013 rows created.

SQL> commit;

Commit complete.

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       282        133       3420   <====

SQL> explain plan for insert into tab3   
  2  select * from tab3 ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1925526863

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |      | 37070 |  4633K|   129  (14)| 00:00:02 |    <====
|   1 |  TABLE ACCESS FULL| TAB3 | 37070 |  4633K|   129  (14)| 00:00:02 |
--------------------------------------------------------------------------

#######################################
### 2. logging 테이블에 Direct Insert
#######################################

SQL> conn scott
Enter password: 
Connected.

SQL> create table tab1 logging
as select * from all_objects
where 1=2;

Table created.

SQL> insert /*+  append */ into tab1
select * from all_objects
; 

36011 rows created.

SQL> commit;

Commit complete.

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ; 

       SID STATISTIC#      VALUE
---------- ---------- ----------
       240        133        866   <====

SQL> explain plan for insert /*+  append */ into tab1
  2  select * from tab1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1481117511

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |      | 32454 |  4056K|   126  (12)| 00:00:02 |
|   1 |  LOAD AS SELECT    | TAB1 |       |       |            |          |    <====
|   2 |   TABLE ACCESS FULL| TAB1 | 32454 |  4056K|   126  (12)| 00:00:02 |
---------------------------------------------------------------------------

#######################################
### 3. nologging 테이블에 Direct Insert
#######################################

SQL> conn scott
Enter password: 
Connected.

SQL> create table tab2 nologging
as select * from all_objects
where 1=2;

Table created.

SQL> insert /*+  append */ into tab2
select * from all_objects
;

36012 rows created.

SQL> commit;

Commit complete.

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ; 

       SID STATISTIC#      VALUE
---------- ---------- ----------
       118        133        363  <====

SQL> explain plan for insert /*+  append */ into tab2
  2  select * from tab2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4264119061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |      | 36080 |  4510K|   128  (14)| 00:00:02 |
|   1 |  LOAD AS SELECT    | TAB2 |       |       |            |          |    <====
|   2 |   TABLE ACCESS FULL| TAB2 | 36080 |  4510K|   128  (14)| 00:00:02 |
---------------------------------------------------------------------------

** 테스트 결과, logging 속성의 테이블이더라도 APPEND 힌트에 의한 REDO감소 효과는 큰것으로 나타남.

3420  > 866 > 363

이정도의 차이라면 Production환경에서는 recovery를 포기하고 테이블까지 nologging으로 해야 한다는 우를 범하지 않고서라도 충분히 append 힌트의 효용성을 누릴수 있을 것이다.

답글 ->

속도 빠른건 좋죠.. 하지만 스토리지 용량을 더 많이 잡아 먹는다는 단점 정도가 있겠네요~

핵심은 배치작업 수행시 주로 사용되는 append 힌트가 redo로그를 감소시켜서 결국 log switching관련 대기이벤트를 감소시키고, 전체적인 야간배치작업 성능을 향상시킨다는 점입니다. 
HWM 이후에 append시킨다는 점은 물론 delete가 빈번한 테이블일 경우 공간활용의 비효율성으로 연결될수 있으므로 그러한 예외적인 경우에는 table shrink등의 리오그 대책이 뒤따라야 할것입니다.. 
하지만 대부분의 대형사이트라면, 요즘은 (저렴해진)스토리지에 투자하는 비용보다는 성능개선이 우선시되니까요~

[출처] http://blog.naver.com/lalaboy/40051083359

spool 명령으로 테이블의 데이터를 파일로 출력하는 방법이다.

다음의 내용을 sql 확장자 파일로 저장한다.

set echo off  // 실행하는 SQL문을 출력하지 않는다.

set pages 0  // 한페이지로 지정할 라인 수. 0으로 입력시 heading, page break, title을 표시하지 않는다.

set trimspool on  // 라인 뒤 공백을 제거한다.

set colsep ','  // 컬럼 간 데이터 구분자

set lines 30000  // 한 라인에 표시할 글자 수

set termout off  // SQL문으로 실행한 결과를 표시하지 않는다.

set feed off  // SQL문으로 실행한 결과 건수를 표시하지 않는다.

spool /경로/test.csv;

select * from 테이블명;

spool off

quit

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm#i2698970

http://ss64.com/ora/syntax-sqlplus-set.html