DBA Hub

📋Steps in this guide1/5

External Table Enhancements in Oracle Database 12c Release 1 (12.1)

An overview of the enhancements to external tables in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
20 views
1

Setup

The examples in this article require the schema. If you are working in a multitenant environment, edit the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script, amending the connection to the schema as necessary, then run the script when connected to the relevant PDB as a privileged user. Create a CSV file to load later. This should be placed in a suitable NFS mount point. In this case I'm also placing it into the "/tmp" directory so as not to confuse when discussing non-NFS related functionality. Create directory objects to allow the user to load the data file from both locations.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
conn sys@pdb1 as sysdba
@?/rdbms/admin/utlsampl.sql

CONN scott/tiger@pdb1

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

-- Create data file.
SET PAGESIZE 0 LINESIZE 100 TRIMSPOOL ON FEEDBACK OFF
SPOOL /nfs/EMP.dat.tmp

SELECT empno || ',' || ename || ',' || job || ',' || mgr || ',' ||
       hiredate || ',' || sal || ',' || comm || ',' || deptno
FROM   emp;

SPOOL OFF
SET PAGESIZE 14 FEEDBACK ON

-- Clean up the file.
HOST cat /nfs/EMP.dat.tmp | grep '[0-9]\{4\}' > /nfs/EMP.dat
HOST cp /nfs/EMP.dat /tmp/EMP.dat

CONN sys@pdb1 AS SYSDBA

-- Create a directory pointing to an NFS location.
CREATE OR REPLACE DIRECTORY nfs_dir AS '/nfs/';
GRANT READ,WRITE ON DIRECTORY nfs_dir TO scott;

-- Create a directory pointing to an non-NFS location.
CREATE OR REPLACE DIRECTORY tmp_dir AS '/tmp/';
GRANT READ,WRITE ON DIRECTORY tmp_dir TO scott;
2

Direct NFS (DNFS) Support

External tables now support the use of Direct NFS (DNFS) to improve performance when the data being loaded resides on NFS filers. DNFS is used by default for files over 1G in size. Explicit control is possible using the , and parameters, with the parameter defaulting to 4.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
CONN scott/tiger@pdb1

DROP TABLE emp_ext;

CREATE TABLE emp_ext (
 EMPNO     NUMBER(4),
 ENAME     VARCHAR2(10),
 JOB       VARCHAR2(9),
 MGR       NUMBER(4),
 HIREDATE  DATE,
 SAL       NUMBER(7,2),
 COMM      NUMBER(7,2),
 DEPTNO    NUMBER(2)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY NFS_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
DNFS_ENABLE
    DNFS_READBUFFERS 10
FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      empno,
      ename,
      job,
      mgr,
      hiredate   CHAR(21) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS",
      sal,
      comm,
      deptno 
    )
  )
  LOCATION ('EMP.dat')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;

SELECT * FROM emp_ext;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10

14 rows selected.

SQL>
3

Extended Data Type Support

When a database is configured to use Extended Data Types , external table operations against the database will support the extended data types also.
4

ORACLE_DATAPUMP Access Driver Enhancements

The access driver includes the ability to specify the level of compression to use when unloading data to a dump file. This feature requires the Oracle Advanced Compression option and the initialization parameter set to 12.0.0 or higher. The available compression options are listed below. - : All data compressed. - : All data compressed using the original compression algorithm that gives a good compression to CPU utilization ratio. - : Lower level of compression, but requires less CPU. Good for systems that already have intensive CPU usage. - : Similar characteristics to , but uses a different algorithm. Recommended. - : Greater level of compression, but more CPU intensive. - : No data will be compressed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
CONN scott/tiger@pdb1

-- Delete if it already exists.
DROP TABLE emp_ext;
HOST rm /tmp/emp_ext.dmp

-- Unload EMP table into dump file using compression.
CREATE TABLE emp_ext
  ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY tmp_dir
ACCESS PARAMETERS (COMPRESSION ENABLED MEDIUM)
LOCATION ('emp_ext.dmp')
   )
   AS SELECT * FROM emp;

-- Create a new external table using the compressed dump file.
DROP TABLE emp2_ext;

CREATE TABLE emp2_ext (
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10),
  JOB       VARCHAR2(9),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2))
  ORGANIZATION EXTERNAL (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY tmp_dir
     LOCATION ('emp_ext.dmp')
  );

SELECT * FROM emp2_ext;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10

14 rows selected.

SQL>
5

ORACLE_LOADER Access Driver Enhancements

A number of minor usability improvements have been made to the ORACLE_LOADER access driver to make external table creation simpler. The clause now accepts wildcards. An "*" matches multiple characters, while a "?" matches a single character. The , , and clauses can now be specified using only a directory object. Files using CSV (comma-separated-values) format can use the simplified clause. The default settings for this are shown below, but they can be modified as described here . Assuming you are using a typical CSV format, you can ignore the optional clauses and stick to the basic clause. A default datetime format can be specified for all datetime fields using the clause. A separate default mask can be specified for , , , , and fields. A default can be specified that applies to all character fields. A field-specific overrides the default . The clause can be used against a field to prevent the default applying to it. The full syntax is presented here . The clause indicates that all fields are present in the data file and their order matches the external table column order. This means the field list only needs to contain columns that require a specific definition. The clause allows you to specify the order of the fields in the data file, saying they are either in the first line of the file, or in a separate file. I can't really see myself using this feature, but it is discussed here . For more information see: - Changes in This Release for Oracle Database Utilities : Oracle External Tables - The ORACLE_LOADER Access Driver - External Tables : All Articles - External Tables : Querying Data From Flat Files in Oracle - External Tables Containing LOB Data - External Tables (Unloading/Loading Data Using External Tables) Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
LOCATION ('emp_ext*.dmp')

LOCATION ('emp_ext?.dmp')

DROP TABLE emp_ext;

CREATE TABLE emp_ext (
 EMPNO     NUMBER(4),
 ENAME     VARCHAR2(10),
 JOB       VARCHAR2(9),
 MGR       NUMBER(4),
 HIREDATE  DATE,
 SAL       NUMBER(7,2),
 COMM      NUMBER(7,2),
 DEPTNO    NUMBER(2)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY TMP_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
BADFILE TMP_DIR
    LOGFILE TMP_DIR
    DISCARDFILE TMP_DIR
FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      empno,
      ename,
      job,
      mgr,
      hiredate   CHAR(21) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS",
      sal,
      comm,
      deptno 
    )
  )
  LOCATION ('EMP.dat')
);

SELECT * FROM emp_ext;

FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

DROP TABLE emp_ext;

CREATE TABLE emp_ext (
 EMPNO     NUMBER(4),
 ENAME     VARCHAR2(10),
 JOB       VARCHAR2(9),
 MGR       NUMBER(4),
 HIREDATE  DATE,
 SAL       NUMBER(7,2),
 COMM      NUMBER(7,2),
 DEPTNO    NUMBER(2)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY TMP_DIR
  ACCESS PARAMETERS
  (
FIELDS CSV
MISSING FIELD VALUES ARE NULL
    (
      empno,
      ename,
      job,
      mgr,
      hiredate   CHAR(21) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS",
      sal,
      comm,
      deptno 
    )
  )
  LOCATION ('EMP.dat')
);


SELECT * FROM emp_ext;

DROP TABLE emp_ext;

CREATE TABLE emp_ext (
 EMPNO     NUMBER(4),
 ENAME     VARCHAR2(10),
 JOB       VARCHAR2(9),
 MGR       NUMBER(4),
 HIREDATE  DATE,
 SAL       NUMBER(7,2),
 COMM      NUMBER(7,2),
 DEPTNO    NUMBER(2)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY TMP_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS"
MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('EMP.dat')
);

SELECT * FROM emp_ext;

DROP TABLE emp_ext;

CREATE TABLE emp_ext (
 EMPNO     NUMBER(4),
 ENAME     VARCHAR2(10),
 JOB       VARCHAR2(9),
 MGR       NUMBER(4),
 HIREDATE  DATE,
 SAL       NUMBER(7,2),
 COMM      NUMBER(7,2),
 DEPTNO    NUMBER(2)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY TMP_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
NULLIF = "NONE"
(
      empno,
      ename,
      job,
      mgr
NO NULLIF
,
      hiredate   CHAR(21) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS",
      sal
NULLIF job="PRESIDENT"
,
      comm,
      deptno 
    )
  )
  LOCATION ('EMP.dat')
);

SELECT * FROM emp_ext;

DROP TABLE emp_ext;

CREATE TABLE emp_ext (
 EMPNO     NUMBER(4),
 ENAME     VARCHAR2(10),
 JOB       VARCHAR2(9),
 MGR       NUMBER(4),
 HIREDATE  DATE,
 SAL       NUMBER(7,2),
 COMM      NUMBER(7,2),
 DEPTNO    NUMBER(2)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY TMP_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
ALL FIELDS OVERRIDE
MISSING FIELD VALUES ARE NULL
    (
hiredate   CHAR(21) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS"
)
  )
  LOCATION ('EMP.dat')
);

SELECT * FROM emp_ext;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!