DBA Hub

📋Steps in this guide1/7

SQL*Loader Enhancements in Oracle Database 12c Release 1 (12.1)

An overview of the enhancements to SQL*Loader in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
13 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. We also need a table to load into, so we will create a copy of the EMP table called EMP2 without any data.

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
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;

CONN scott/tiger@pdb1

CREATE TABLE emp2 AS
SELECT *
FROM   emp
WHERE  1=2;
2

Direct NFS (DNFS) Support

SQL*Loader now supports 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. Create a file called "/nfs/EMP.ctl" with the following contents. Run the following SQL*Loader command to load the data into the EMP table, replacing the existing data.

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
OPTIONS (
  BADFILE '/nfs/EMP.bad'
  LOGFILE '/nfs/EMP.log'
DNFS_ENABLE TRUE
  DNFS_READBUFFERS 10
)
LOAD DATA
INFILE '/nfs/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS TERMINATED BY ','
   (empno,
    ename,
    job,
    mgr,
    hiredate   DATE "DD-MON-YYYY HH24:MI:SS" ":hiredate",
    sal,
    comm,
    deptno)

$ cd /nfs
$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl
3

Extended Data Type Support

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

SQL*Loader Express

SQL*Loader Express is really just a simple extension of the command line interface, such that all the default values are targeted at handling basic CSV files that match the structure of a table. For example, if we want to load data into the table, the default settings will look for a dump file called "EMP2.dat" that internally matches the structure of table. As a result, we only need to specify command line arguments if the derived and default settings are not acceptable. The SQL*Loader Express documentation lists all the derived and default settings.

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
$ cd /tmp
$ cp EMP.dat EMP2.dat
$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\"
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Sep 13 22:18:46 2014

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

Express Mode Load, Table: EMP2
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table EMP2:
  14 Rows successfully loaded.

Check the log files:
  EMP2.log
  EMP2_%p.log_xt
for more information about the load.
$
5

SQL*Loader Command Line Changes

SQL*Loader Express is the main change for the command line, but there are some additional changes. The parameter allows you to override the default functionality with one of , , , . The parameter can be set to an integer value, , or , with being the default value. The , , and parameters can be specified using just a directory object name.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\" \
TRIM=LRTRIM

$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\" \
DEGREE_OF_PARALLELISM=4

$ sqlldr scott/tiger@pdb1 TABLE=EMP2 DATE_FORMAT=\"DD-MON-YYYY HH24:MI:SS\" \
BAD=TMP_DIR DISCARD=TMP_DIR LOG=TMP_DIR
6

SQL*Loader Control File Changes

Not surprisingly, most of the SQL*Loader control file changes are similar to those introduced for ORACLE_LOADER access driver for external tables . The clause now accepts wildcards, where "*" matches multiple characters, while a "?" matches a single character. Files using CSV (comma-separated-values) format can use the simplified clause. The default settings for this can be modified as described here . A default datetime format can be specified for all datetime fields using the clause. 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 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 .

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
INFILE '/nfs/EMP*.dat'

INFILE '/nfs/EMP?.dat'

LOAD DATA
INFILE '/tmp/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS CSV WITH EMBEDDED
(empno,
    ename,
    job,
    mgr,
    hiredate    DATE "DD-MON-YYYY HH24:MI:SS" ":hiredate",
    sal,
    comm,
    deptno)

$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl

LOAD DATA
INFILE '/tmp/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS TERMINATED BY ','
DATE FORMAT "DD-MON-YYYY HH24:MI:SS"
(empno,
    ename,
    job,
    mgr,
    hiredate DATE,
    sal,
    comm,
    deptno)

$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl

LOAD DATA
INFILE '/tmp/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS TERMINATED BY ','
NULLIF = "NONE"
DATE FORMAT "DD-MON-YYYY HH24:MI:SS"
   (empno,
    ename,
    job,
    mgr
NO NULLIF
,
    hiredate    DATE "DD-MON-YYYY HH24:MI:SS" ":hiredate",
    sal
NULLIF job="PRESIDENT"
,
    comm,
    deptno)

$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl
7

Audit SQL*Loader Direct Path Loads

Oracle 12c allows SQL*Loader direct path loads to be audited by creating an audit policy. I'm having some trouble getting this auditing to work using the "ALL" option. Thanks to Ronan for pointing out the "LOAD" method was working as expected. When this policy is applied to a user, their SQL*Loader direct path operations will appear in the audit trail. The following policy audits all SQL*Loader operations. The policy is applied to the SCOTT user. Use the following control file to run SQL*Loader. Checking the audit trail shows the SQL*Loader run was audited. For more information see: 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
CREATE AUDIT POLICY policy_name 
  ACTIONS COMPONENT=DIRECT_LOAD [LOAD | ALL];

CONN sys@pdb1 AS SYSDBA
CREATE AUDIT POLICY audit_sl_load_policy ACTIONS COMPONENT=DIRECT_LOAD LOAD;
AUDIT POLICY audit_sl_load_policy BY scott;

OPTIONS (DIRECT=TRUE)
LOAD DATA
INFILE '/tmp/EMP.dat'
INTO TABLE emp2
TRUNCATE
FIELDS TERMINATED BY ','
   (empno,
    ename,
    job,
    mgr,
    hiredate   DATE "DD-MON-YYYY HH24:MI:SS" ":hiredate",
    sal,
    comm,
    deptno)

$ sqlldr userid=scott/tiger@pdb1 control=EMP.ctl

CONN sys@pdb1 AS SYSDBA

-- Flush audit information to disk.
EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

SET LINESIZE 200
COLUMN event_timestamp FORMAT A30
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A30
COLUMN action_name FORMAT A20

SELECT event_timestamp,
       object_schema,
       object_name,
       action_name,
       direct_path_num_columns_loaded
FROM   unified_audit_trail
WHERE  audit_type = 'Direct path API'
ORDER BY 1;

EVENT_TIMESTAMP                OBJECT_SCH OBJECT_NAME                    ACTION_NAME          DIRECT_PATH_NUM_COLUMNS_LOADED
------------------------------ ---------- ------------------------------ -------------------- ------------------------------
19-OCT-2015 07:50:08.335507    SCOTT      EMP2                           LOAD                                              8

2 rows selected.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!