Oracle DBA Lesson 84 Part 2 — Choose a Load Path and a Safe Restart

SQL*Loader's load path determines how incoming values reach the table and which database behavior runs along the way. Choose it from the application's integrity, audit, concurrency, and recovery requirements, then measure performance with a representative workload.

Oracle DBA Lesson 84 Part 2 — Choose a Load Path and a Safe Restart

This part assumes the field mapping and record reconciliation from Part 1. It adds two decisions: whether conventional or direct path preserves the required behavior, and how to continue after a load has committed some rows.

Choose the path from required behavior

Suppose every new department needs an audit entry written by an enabled insert trigger.

Requirement or behavior	Conventional SQL*Loader path	SQL*Loader direct path
Insertion mechanism	Uses SQL INSERT processing with bind arrays.	The load engine formats Oracle data blocks and writes them to physical database files on disk.
Table insert triggers	Enabled insert triggers fire.	Insert triggers are disabled for loading and do not fire for the direct-loaded rows.
Check and foreign key constraints	Enabled constraints apply during ordinary insertion.	Disabled by default for the load. EVALUATE CHECK_CONSTRAINTS can request check-constraint evaluation; it does not make foreign key behavior identical to conventional loading.
Not-null constraints	Enforced as part of ordinary insertion.	Remain enabled and are checked during column-array construction.
Unique/primary keys	Ordinary insertion rejects conflicting keys.	Uniqueness is verified during index maintenance. Violating rows can have been loaded, leaving a unique index unusable.
Concurrent access	Subject to normal DML behavior and locking.	Restrictions apply. A nonparallel direct load needs exclusive write access to the table and exclusive read/write access to its indexes.
Recovery	Conventional changes generate redo through normal insertion.	Data redo depends on archive mode, object logging attributes, loader options, and applicable force logging.

For the stated insert-trigger audit requirement, conventional path supplies the required behavior. Direct path needs a separately designed and verified way to produce equivalent audit work. Reenabling a trigger after direct loading affects subsequent inserts; it does not execute the trigger retrospectively for the rows already loaded.

Direct path can improve throughput by avoiding much ordinary SQL insertion work. Its suitability and measured benefit depend on the table, indexes, constraints, workload, and concurrency. This comparison describes regular SQL*Loader in Oracle Database 19c, not SQL*Loader Express mode or every SQL direct-path INSERT variation. See conventional and direct path loads.

Conventional path with an insert trigger

app_user names the account and APP_SERVICE names the service. The account needs a session, CREATE TABLE, CREATE TRIGGER, tablespace quota, and access to these objects. The object names below need to be unused. SQL*Loader and a SQL client must be available, and the loader client needs the local files.

The following objects add a primary key, a name check, and a transactional audit trigger to the simple mapping from Part 1. See CREATE TABLE.

CREATE TABLE load_departments (
  department_id NUMBER CONSTRAINT ld_pk PRIMARY KEY,
  department_name VARCHAR2(80),
  CONSTRAINT ld_name_ck CHECK (department_name IS NOT NULL)
);

CREATE TABLE load_department_audit (
  department_id NUMBER PRIMARY KEY,
  inserted_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE OR REPLACE TRIGGER ld_audit_ins
AFTER INSERT ON load_departments
FOR EACH ROW
BEGIN
  INSERT INTO load_department_audit (department_id)
  VALUES (:NEW.department_id);
END;
/

The slash is a SQL*Plus block-execution command. Use the appropriate execution control in another client. Verify successful trigger compilation and enabled status before loading. This trigger inserts its audit row in the same transaction as the target row; it is not an autonomous transaction. The audit table's primary key permits one audit row per department in this load.

Save the corrected, headerless feed as departments.csv:

40,Support
50,Engineering
60,Finance

Save departments.ctl:

LOAD DATA
INFILE 'departments.csv'
INSERT INTO TABLE load_departments
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(department_id INTEGER EXTERNAL, department_name CHAR)

This control file requires an empty target. Use a known directory or explicit paths. Match the input encoding to the loader settings and check target length semantics for the actual data. See SQL*Loader control file reference.

Run conventional path:

sqlldr userid=app_user@APP_SERVICE \
  control=departments.ctl direct=false \
  log=departments_conventional.log \
  bad=departments_conventional.bad
load_status=$?
printf '%s\n' "$load_status"

Enter the password when prompted. Capture the status immediately; a later command changes $?. Handle nonzero warning statuses deliberately in scripts, including scripts using set -e. Preserve each attempt's input, control file, and output before a retry. See SQL*Loader command-line reference.

With the empty tables and the valid input above, expect three target rows and one audit row for each.

SELECT department_id, department_name
FROM load_departments
ORDER BY department_id;

SELECT department_id, COUNT(*) AS audit_entries
FROM load_department_audit
GROUP BY department_id
ORDER BY department_id;

Expected rows:

DEPARTMENT_ID	DEPARTMENT_NAME	Expected audit entries
40	Support	1
50	Engineering	1
60	Finance	1

A count alone cannot confirm names, key correctness, or the required audit work. Reconcile the complete input, the loaded, rejected, and discarded records, the values, and the application-specific results.

Inspect the resulting object state

Keep the utility log, including constraint and trigger disable/reenable messages and any index-maintenance errors. Query the table in its owning schema:

SELECT trigger_name, status
FROM user_triggers
WHERE table_name = 'LOAD_DEPARTMENTS';

SELECT constraint_name, constraint_type, status, validated
FROM user_constraints
WHERE table_name = 'LOAD_DEPARTMENTS';

SELECT index_name, status
FROM user_indexes
WHERE table_name = 'LOAD_DEPARTMENTS';

Inspection	Required interpretation
Trigger LD_AUDIT_INS	ENABLED, successfully compiled, and its required audit rows actually present.
Constraints LD_PK, LD_NAME_CK	ENABLED and VALIDATED. ENABLED alone does not establish that existing rows were validated.
Required nonpartitioned indexes	VALID; investigate any UNUSABLE result before acceptance.
Target and audit contents	Correct keys, names, and one corresponding audit entry per inserted department.

Partitioned indexes need partition and subpartition checks. The short USER_INDEXES.STATUS example is for these ordinary nonpartitioned indexes.

For direct loading, review the documented REENABLE behavior. Without it, constraints disabled by the loader require manual reenabling. Reenabling and validation can also fail or leave a state that needs investigation. Triggers normally return to enabled after loading, but interruption or locking problems can leave objects disabled. Repair the cause, validate the data, and restore the required states. Do not enable a rule and assume that historical data or audit work is complete. See ALL_CONSTRAINTS, ALL_INDEXES, and ALL_TRIGGERS for the corresponding USER_* state columns.

Logging and recoverability

Direct path and NOLOGGING are separate choices. In archive log mode, a recoverable direct load can generate the data redo needed for media recovery. Applicable NOLOGGING or loader UNRECOVERABLE behavior can omit full loaded-data redo, while metadata and invalidation redo still exist. Database or tablespace force logging overrides the applicable no-logging request.

If an unrecoverable operation omits the necessary data redo, restoring an older backup and applying available redo cannot reconstruct those loaded blocks. Take a suitable backup after the operation and include it in the recovery plan. Returning an object to LOGGING does not recreate redo that was never generated. Coordinate any standby or downstream recovery implications separately before selecting such an operation.

Before a direct-path load, inventory archive mode, database and tablespace force logging, table and index logging attributes, restrictions, required application behavior, and a tested recovery plan. Keep the full-data-redo, backup, and restore acceptance criteria explicit. An output log reporting that loading finished is insufficient evidence of recoverability. See logging clause.

Restart from the batch and actual committed progress

An interrupted SQL*Loader load can retain work already committed or saved. Conventional loading commits completed bind arrays. Direct-path behavior depends on the termination reason, savepoints, and load structure. Read the actual utility log and inspect the target. Do not derive the restart point from a generic row count or an assumed fixed commit interval.

This preserved batch is the restart example:

Source key	Name	Target at interruption
40	Support	Committed, with matching audit entry.
50	Engineering	Committed, with matching audit entry.
60	Finance	Missing.

It is a scale model of interrupted progress. A three-record test is not guaranteed to stop at this boundary.

Keep an immutable copy of the input and control file, a batch identifier, source version or hash, attempt identifier, log, bad and discard evidence, and the connection and target identity. A filename alone cannot establish that two attempts used the same batch. Check business keys, existing values, and rejected or missing records before changing anything.

Replaying the whole file with APPEND attempts already processed records again. With no uniqueness protection this can create duplicates. With a unique key, repeats may instead be rejected; that still requires reconciliation and a deliberate restart procedure. The original INSERT loading method requires an empty target, so it will fail against an already populated target.

Stage the batch, then publish missing keys

This design assumes a single immutable batch, stable unique department identifiers, a single publishing writer, unchanged values for already accepted keys, and the transactional audit trigger shown above. It is an insertion-only feed. Updates, external side effects, several batches, concurrent writers, and conflicting source duplicates need a broader design and tests.

1. Create an unused staging table for this attempt. Include a primary key and the required name validation. Use a distinct control file targeting LOAD_DEPARTMENTS_STAGE, and load the complete preserved batch into that empty table with conventional path.
2. Confirm the stage contains all three valid unique keys and expected values, with no unexplained rejected, discarded, or missing records.
3. Compare matching keys with the target. If an existing target value differs, stop for a defined conflict decision rather than silently overwriting or skipping it.
4. Insert only missing keys through conventional SQL so the required target trigger runs. Verify the inserted row and its audit entry in the transaction, then commit. Reconcile again after commit before recording batch completion.

The staging table is:

CREATE TABLE load_departments_stage (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(80),
  CONSTRAINT lds_name_ck CHECK (department_name IS NOT NULL)
);

After loading and validating that stage, compare values:

SELECT s.department_id,
       s.department_name AS source_name,
       t.department_name AS target_name
FROM load_departments_stage s
JOIN load_departments t
  ON t.department_id = s.department_id
WHERE s.department_name <> t.department_name;

Expect no conflicts for this input. Then, with no competing publishing writer:

INSERT INTO load_departments (department_id, department_name)
SELECT s.department_id, s.department_name
FROM load_departments_stage s
WHERE NOT EXISTS (
  SELECT 1
  FROM load_departments t
  WHERE t.department_id = s.department_id
);

Only key 60 is missing in this snapshot, so expect one inserted target row and one new transactional audit entry. Query the target and audit values, commit only after the intended transaction passes its checks, then recheck the committed results. Roll back the current transaction if those checks fail. A rollback cannot remove older SQL*Loader commits. See INSERT.

The target primary key protects uniqueness. NOT EXISTS alone is not concurrency control. Under these single-writer, stable-value conditions, repeating the publication inserts no additional rows after all keys exist. Test interruption before and after publication commit and completion bookkeeping. A lost completion acknowledgment must not cause a second application of business effects.

SQL*Loader also supports SKIP for documented restart cases. It counts logical input records, not successfully inserted target rows. Use the log's supported resume information and unchanged input and record construction. Rejected, discarded, and header records, and multitable or partitioned loading, can invalidate a naive offset. This design uses fresh staging so the full batch can be reconciled before publication. See interrupted loads in the control file reference and SKIP in the command-line reference.

Quiz

1. A required insert trigger writes an audit entry. What is the straightforward load choice for preserving that behavior?

Conventional path with the trigger enabled and working. Direct path followed only by reenabling the trigger. Direct path with no audit validation. Either path always produces identical trigger results.

2. What does reenabling the trigger after direct loading do?

Replays every earlier insert automatically. Reconstructs every missing audit entry automatically. Makes the trigger available for subsequent triggering operations. Repairs all unusable indexes.

3. Why inspect indexes and constraint validation after direct loading?

Every direct load leaves every index unusable. Unique violations or other load problems can leave index/integrity states needing repair. Enabled always means all historical rows were validated. Trigger status contains all the needed information.

4. Which statement about NOLOGGING is correct?

It guarantees zero redo in every load. It makes older backups sufficient for every newly loaded block. It is always implied by direct=true. It can reduce applicable data redo, force logging can override it, and omitted redo affects media recovery.

5. Can whole-file APPEND after partial commit duplicate accepted rows?

Yes, without a design preventing repeated acceptance; unique keys may instead reject repeated keys. No, all previous loader work is always rolled back. No, APPEND remembers every input batch. No, target row count is always a valid SKIP offset.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily