Lesson 9

This module examined Oracle Database 23ai utilities and table-management features that help database administrators load external data, manage large objects, reorganize table storage, work with temporary data, remove obsolete columns, and interpret database limits accurately. Although several of these features originated in earlier Oracle releases, they remain important because they address recurring operational problems: moving large volumes of data, reducing downtime, preserving application availability, reclaiming storage, and making structural changes without rebuilding an entire schema.
The central theme of the module is selection. Oracle normally offers more than one way to complete an administrative task, and the correct method depends on data volume, table design, constraints, indexes, application availability, storage configuration, recovery requirements, and the exact database release. A DBA should understand not only the syntax of a command, but also its physical consequences and the verification steps required after it runs.

After completing this module, you should be able to:

1. Explain the purpose and principal components of SQL*Loader.
2. Distinguish conventional-path loading, direct-path loading, and programmatic loading through the Oracle Direct Path Load API.
3. Describe how SQL*Loader uses control files, datafiles, parameter files, log files, bad files, and discard files.
4. Load BLOB, CLOB, NCLOB, and BFILE-related data through appropriate SQL*Loader workflows.
5. Explain how SQL*Loader maps structured records into object attributes, collections, and other supported Oracle data types.
6. Relocate or reorganize a table with ALTER TABLE ... MOVE.
7. Use tablespace, PCTFREE, online-move, index-maintenance, and LOB-storage clauses appropriately.
8. Recognize when DBMS_REDEFINITION is more appropriate than a simple table move.
9. Create transaction-specific and session-specific global temporary tables.
10. Distinguish global temporary tables from private temporary tables.
11. Mark columns unused, report unused-column counts, and physically remove unused columns later.
12. Drop named columns directly while accounting for constraints and dependent objects.
13. Distinguish physical, logical, configurable, architectural, and service-specific database limits.
14. Query the installed database to verify the limits and parameter values that actually govern an Oracle deployment.

SQL*Loader remains one of Oracle's primary tools for loading data from external files into database tables. It is especially useful when the source data already exists in flat files, delimited records, fixed-length records, stream records, secondary LOB files, or other file-based structures that would be inefficient to insert one row at a time through an application.
The loader control file defines how SQL*Loader interprets the source data. It can identify input files, record formats, field delimiters, target tables, column mappings, transformations, selection conditions, LOBFILE references, and loading modes. Separating the data from the loading instructions makes a load repeatable and auditable. The same control file can be reused with new datafiles as part of a scheduled import or migration process.
The parameter file and the control-file OPTIONS clause provide additional ways to store frequently reused settings. Command-line values can override those defaults when a particular load requires different behavior.

Conventional-path loading processes rows through the normal SQL execution path. It is flexible, integrates with ordinary transaction processing, and is appropriate when triggers, constraints, and concurrent activity must behave in the usual manner.
Direct-path loading reduces portions of normal SQL-processing overhead by formatting database blocks for high-throughput insertion. It can substantially improve performance for large data volumes, but it introduces operational considerations involving table locks, indexes, triggers, constraints, free space, and concurrent access.
A custom application can also call the Oracle Direct Path Load API directly. In that model, the application supplies the data and loading instructions programmatically instead of invoking the SQL*Loader executable with a control file. This is useful when data is generated or transformed in memory, consumed from a stream, or integrated into an application-controlled ingestion pipeline.
The fastest method is not automatically the best method. The loading path must match the integrity rules, availability requirements, recovery strategy, and structure of the target table.

SQL*Loader can load large documents, images, text, and binary content into LOB columns. The LOBFILE method keeps large content in secondary files while the primary datafile supplies row metadata and file references. This avoids embedding large binary or character values directly in the primary record and is generally the better design for bulk loading substantial LOB content.
SQL*Loader can also load object-relational structures, including object attributes, nested collections, VARRAYs, and REF values. Modern Oracle loading workflows also support data types such as JSON and VECTOR where the installed release and target structure permit them.
For lower-volume or application-driven LOB insertion, PL/SQL and the DBMS_LOB package provide a programmatic alternative. SQL*Loader is normally preferable when many external files must be loaded in one controlled batch.

A successful load is not evaluated only by the number of inserted rows. SQL*Loader produces several output files that explain what happened during the operation:

Log file

Records the load configuration, row counts, diagnostics, errors, and the number of rows loaded, rejected, discarded, or skipped.

Bad file

Contains records that SQL*Loader or Oracle Database attempted to process but could not load because of invalid formatting, conversion failures, constraint violations, missing required values, or other insertion errors.

Discard file

Contains valid records that were intentionally excluded because they did not satisfy any applicable record-selection condition in the control file.

A rejected record represents a failure. A discarded record represents a deliberate filtering decision. The log file, bad file, and discard file should be reviewed together before a load is considered complete.

The table-management portion of the module focused on operations that alter physical storage or table structure without requiring a manual drop-and-create cycle.
Use ALTER TABLE ... MOVE to relocate a table to another tablespace, reorganize its segment, change supported physical attributes, or apply storage options. A move creates a new table segment and rewrites the rows into that segment. Because rowids can change, index maintenance must be planned. Where supported, UPDATE INDEXES or an online move can reduce the need for a separate index rebuild.
A table move does not automatically relocate every LOB segment. LOB storage can reside separately and may require an explicit LOB ... STORE AS clause. The destination tablespace must have sufficient usable capacity, but it does not need to match the total size of the source tablespace.
For complex transformations that exceed the scope of a simple move, DBMS_REDEFINITION provides an online-redefinition framework. It is more appropriate when the table structure, partitioning strategy, or storage design must change while the application continues to use the source table.

Global temporary tables provide persistent table definitions with temporary, session-private rows. Multiple sessions can use the same definition while seeing only their own data.
A table created with ON COMMIT DELETE ROWS holds transaction-specific data. Oracle removes the current transaction's temporary rows at commit. A table created with ON COMMIT PRESERVE ROWS holds session-specific data that remains available after commit and is removed when the session ends or explicitly truncates the table.
Private temporary tables provide another option when both the definition and the rows should remain private to the creating session. They complement global temporary tables rather than replacing them.
Temporary tables are useful for calculations, report preparation, staging, validation, and batch processing, but they require careful transaction management in connection pools. A pooled Oracle session can outlive one application request, so session-specific temporary rows must be cleaned up before the connection is returned to the pool.

Oracle provides both deferred and immediate methods for removing columns.

• SET UNUSED removes one or more columns logically without immediately rewriting every row.
• DROP UNUSED COLUMNS physically removes every previously unused column and reclaims its storage.
• DROP COLUMN immediately removes a specified column and its data.

Marking a column unused is useful when a large production table must stop exposing that column quickly but the physical rewrite should be deferred to a maintenance window. The operation is effectively irreversible through ordinary DDL, so any required data must be preserved before the column is marked unused.
The views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, and DBA_UNUSED_COL_TABS report tables that contain unused columns. During the later physical removal, a CHECKPOINT value can limit accumulated undo for a long-running operation.
Constraints, indexes, views, triggers, materialized views, PL/SQL objects, and application code must be reviewed before a column is removed. Dependency analysis is part of the change, not an optional cleanup step.

The final lesson distinguished several types of Oracle Database limits. Physical limits involve blocks, datafiles, tablespaces, and storage architecture. Logical limits involve columns, identifiers, partitions, constraints, and schema objects. Configurable limits depend on initialization parameters such as DB_FILES, PROCESSES, SESSIONS, and MAX_STRING_SIZE.
Oracle Database 23ai also operates within the multitenant CDB/PDB architecture. Some limits apply to the entire CDB, some apply to an individual PDB, and some are instance-specific. Oracle RAC, operating-system limits, service restrictions, and edition licensing add further context.
Static limit charts are useful references, but the installed database is the source of truth. Dynamic performance views and data dictionary views should be used to inspect the exact release, compatibility level, block size, configured file count, session utilization, string-size mode, and recognized parameters. Version-sensitive capabilities, including extended wide-table support, should never be assumed solely from a documentation page for a newer release.

Across all of the topics in this module, the same production discipline applies:

1. Identify the business and technical reason for the change.
2. Inspect the target table, data types, indexes, constraints, dependencies, LOBs, and storage structures.
3. Verify the exact Oracle release, compatibility settings, and feature restrictions.
4. Estimate storage, undo, redo, temporary-space, and maintenance-window requirements.
5. Test the operation on a representative non-production system.
6. Choose the least disruptive method that still satisfies the requirement.
7. Monitor the operation and retain diagnostic output.
8. Verify object status, data integrity, index usability, optimizer statistics, and application behavior afterward.

Oracle provides powerful administrative features, but no command should be treated as a substitute for diagnosis and planning.

Control file

A SQL*Loader file that defines input sources, record formats, field mappings, target tables, transformations, selection rules, and load behavior.

Bad file

A file containing source records rejected because of parsing errors or Oracle Database insertion failures.

Discard file

A file containing valid records excluded by SQL*Loader record-selection conditions.

LOBFILE

A secondary file containing one or more large object values loaded into a LOB column through SQL*Loader.

Global temporary table

A persistent schema definition whose data is private to each session and lasts for a transaction or session.

Private temporary table

A temporary table whose definition and data are visible only within the creating session.

Unused column

A column removed from normal visibility with SET UNUSED but not yet physically removed from stored rows.

Direct-path loading

A high-throughput loading method that reduces portions of conventional SQL-processing overhead.

Database limit

A physical, logical, configurable, architectural, edition-specific, or service-specific boundary affecting a database deployment.

You should now be able to select and explain the Oracle Database 23ai loading and table-maintenance methods covered in this module. More importantly, you should be able to evaluate the operational consequences of each method before applying it to a production database.
The next module examines table joins and the Oracle SQL features used to retrieve related data through inner joins, outer joins, and other query formats.

Click the quiz link below to take a quiz on Database elements.
Database Elements - Quiz