- 4 Posts
- 50 Views
How to find Oracle Enhancements in the Enhancement Tool
Is there any way to tie an Oracle Enhancement/Bug number back to the enhancement request here?
I found bug #31207636 in Oracle support and its status is "Approved for User Group Voting". I've tried searching all of the keywords in Oracle's description, but can't find the enhancement request so that I can vote on it.
Thanks for sharing.
External Table Enhancements in Oracle Database 12c Release 1 (12.1)
This article presents an overview of the enhancements to external tables in Oracle Database 12c Release 1 (12.1).
Setup
Direct NFS (DNFS) Support
Extended Data Type Support
ORACLE_DATAPUMP Access Driver Enhancements
ORACLE_LOADER Access Driver Enhancements
Related articles.
External Tables : All Articles
External Tables : Querying Data From Flat Files in Oracle
External Tables : Querying Data From Flat Files in Oracle
External Tables Containing LOB Data
External Tables (Unloading/Loading Data Using External Tables)
Setup
The examples in this article require the SCOTT schema. If you are working in a multitenant environment, edit the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script, amending the connection to the SCOTT schema as necessary, then run the script when connected to the relevant PDB as a privileged user.
conn sys@pdb1 as sysdba
@?/rdbms/admin/utlsampl.sql
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.
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
Create directory objects to allow the SCOTT user to load the data file from both locations.
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;
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 DNFS_ENABLE, DNFS_DISABLE and DNFS_READBUFFERS parameters, with the DNFS_READBUFFERS parameter defaulting to 4.
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>
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.
ORACLE_DATAPUMP Access Driver Enhancements
The ORACLE_DATAPUMP 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 COMPATIBLE initialization parameter set to 12.0.0 or higher.
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>
The available compression options are listed below.
COMPRESSION ENABLED : All data compressed.
COMPRESSION ENABLED BASIC : All data compressed using the original compression algorithm that gives a good compression to CPU utilization ratio.
COMPRESSION ENABLED LOW : Lower level of compression, but requires less CPU. Good for systems that already have intensive CPU usage.
COMPRESSION ENABLED MEDIUM : Similar characteristics to BASIC, but uses a different algorithm. Recommended.
COMPRESSION ENABLED HIGH : Greater level of compression, but more CPU intensive.
COMPRESSION DISBALED : No data will be compressed.
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 LOCATION clause now accepts wildcards. An "*" matches multiple characters, while a "?" matches a single character.
LOCATION ('emp_ext*.dmp')
LOCATION ('emp_ext?.dmp')
The BADFILE, DISCARDFILE, and LOGFILE clauses can now be specified using only a directory object.
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;
Files using CSV (comma-separated-values) format can use the simplified FIELDS CSV clause. The default settings for this are shown below, but they can be modified as described here.
FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
Assuming you are using a typical CSV format, you can ignore the optional clauses and stick to the basic FIELDS CSV clause.
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;
A default datetime format can be specified for all datetime fields using the DATE_FORMAT clause. A separate default mask can be specified for DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE fields.
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;
A default NULLIF can be specified that applies to all character fields. A field-specific NULLIF overrides the default NULLIF. The NO NULLIF clause can be used against a field to prevent the default NULLIF applying to it. The full syntax is presented here.
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;
The ALL FIELDS OVERRIDE 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.
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;
The FIELD NAMES 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 : Querying Data From Flat Files in Oracle
External Tables Containing LOB Data
External Tables (Unloading/Loading Data Using External Tables)
Hope this helps.
Hi @Ellen Deak!
Thanks for taking the time to talk through this one with me. You are correct in the process of ranking them, but for some reason this one isn't showing. As mentioned I am going to follow up with Oracle and the SIG to see where this one belongs. Once we get this solved I will post the direct link for you here and follow up on our email chain.
Sorry for the inconvenience and thank you for your patience.