Data Loading and Table Management in Oracle 26ai

What Makes a Database Large?

1. Load Complex Data with SQL*Loader

SQL*Loader transfers data from external source files into Oracle tables. A conventional loading design identifies the input format, target columns, and conversion rules. The control file describes how input fields map to the database. It is a loading specification, distinct from the database control file used for database operation and recovery.

Complex data requires more than matching comma-separated values to scalar columns. A source may describe an object, contain repeating elements, or reference a separate file holding a document. Oracle's SQL*Loader documentation covers objects, collections, and large objects, with syntax and restrictions appropriate to each structure.

For example, a supplier might provide one file describing products and a directory of product documents. Before loading, define how each document is matched to its product identifier. Decide what should happen when the identifier is missing, the document cannot be read, or the source contains duplicates. A loader cannot infer those business rules reliably from arbitrary files.

Plan validation alongside ingestion. Record the expected number of input records and distinguish rejected records from records deliberately excluded by selection rules. Review the loading results and compare a sample of target values with their sources. For a repeatable business process, document how a failed or partially completed run can be investigated without accidentally duplicating accepted data.

SODA Collections in Oracle 26ai

Oracle's 26ai Utilities documentation also describes SQL*Loader support for loading schemaless documents into SODA collections. SODA means Simple Oracle Document Access. A SODA collection groups documents and is backed by an Oracle table or view. It is different from a SQL collection datatype such as a nested table or VARRAY.

The image's document-loading panel introduces this option. It does not mean that loading a file automatically supplies an application-specific document model. Use the documented SODA loading parameters and examples when implementing that workflow. Keep source-file conventions and acceptance checks explicit, especially when documents do not all have the same fields.

2. Move an Eligible Table Online

A DBA may need to relocate a table into a different tablespace to meet a storage-management objective. In a busy application, the important question is whether users can continue changing the table while the move runs. An online move supports concurrent DML for eligible tables, subject to Oracle's restrictions.

The following example uses an ordinary heap-organized table named orders and an existing destination tablespace named app_data:

ALTER TABLE orders MOVE ONLINE
  TABLESPACE app_data
  UPDATE INDEXES;

The table owner must have appropriate quota, the executing account must have the necessary privileges, and the destination must have sufficient capacity. The statement maintains indexes as part of the operation. Maintaining an index does not mean relocating that index to the table's new tablespace.

A move changes physical row locations and therefore ROWIDs. Preserve the distinction between physical location and logical identity: application identifiers should not depend on a ROWID remaining unchanged. The table remains the logical application object, with its grants and constraints retained, rather than being replaced through a separate drop-and-create procedure.

Account for Separate Storage and Restrictions

LOB segments require explicit attention if they must move too. Do not assume that the table's destination clause relocates every associated segment. Likewise, a partitioned table needs guidance for the relevant partition or table operation; the simple example is not a universal recipe for every table organization.

The image's phrase “without losing availability” describes the objective of online movement. It is not a guarantee of zero waits, unrestricted concurrency, or unchanged response times. Check the documented restrictions and consider locking, existing transactions, and competing resource demands before choosing the maintenance window.

Write down the purpose of the move before executing it. For example, “place order data in the designated application tablespace” is a verifiable outcome. “Make the database faster” is too broad without a measured problem and a reason that relocation should address it. Compare the result with that original objective.

Afterward, check the table's placement, index usability, and basic application behavior. Confirm that associated storage is where you intended it to be. Include a representative application transaction in the acceptance checks, because completing the SQL statement successfully does not establish that every consumer of the table behaves as expected.

3. Keep Temporary Working Data Private

Some operations need intermediate results that should not become permanent business records. A report may prepare a working list of identifiers before joining it to several other tables. Temporary tables provide a way to keep that working data in database tables while controlling its visibility and lifetime.

The term global temporary table can be misleading. Global describes the shared table definition, not a shared pool of rows. Different sessions can use the same definition while seeing only their own temporary data. Private temporary tables go further by making the definition private as well.

Transaction-Specific Global Temporary Table

CREATE GLOBAL TEMPORARY TABLE work_ids
  (id NUMBER) ON COMMIT DELETE ROWS;

With ON COMMIT DELETE ROWS, committing clears the transaction's temporary rows. The table definition remains. With ON COMMIT PRESERVE ROWS, rows survive commits but last only until the session ends. These are different lifecycle choices, not interchangeable ways of declaring the same behavior.

Suppose a report inserts candidate identifiers, commits, and then reads the working table. The transaction-specific version will have cleared those identifiers at the commit. Either the report's transaction boundaries or the table's lifetime choice must match the intended workflow. Making the table temporary does not remove the need to understand when the application commits.

Private Temporary Table

CREATE PRIVATE TEMPORARY TABLE ora$ptt_work_ids
  (id NUMBER) ON COMMIT DROP DEFINITION;

This example drops both the data and definition at commit. The alternative ON COMMIT PRESERVE DEFINITION keeps the private table for the session. The default name prefix is ORA$PTT_, controlled by PRIVATE_TEMP_TABLE_PREFIX. Private temporary tables use the DEFINITION clauses, not the global temporary table's ROWS clauses.

Match Session Lifetime to Application Lifetime

Session isolation needs particular care in applications that reuse database connections. A business request finishing is not necessarily the same event as a database session ending. Include temporary-data cleanup in the application's connection-handling design, and test the behavior across repeated requests rather than only a single interactive SQL session.

Temporary data also needs a capacity plan. “Temporary” describes its intended lifetime, not a promise that unlimited working data can be held entirely in memory. Estimate the working set for concurrent requests and test realistic combinations of reports or batch jobs. Session-private rows help separate workloads logically, but those workloads still compete for system resources.

4. Remove Obsolete Columns Deliberately

Applications evolve, and columns that once held important values can become obsolete. A discontinued legacy code, for example, may no longer be used by the current application. Before removing it, check reports, integration jobs, views, constraints, indexes, and maintenance scripts. A column unused by today's user interface may still be required elsewhere.

There are two approaches in this lesson: remove the column directly, or make it unused first and schedule physical cleanup separately. Choose an approach before running the examples. They are alternatives, not a sequence in which the same column is dropped and then marked unused.

Drop a Column Directly

ALTER TABLE orders DROP COLUMN old_code;

This removes old_code from the table. On a large table, physical removal can involve substantial work. Evaluate its duration and operational impact in an appropriate test environment. If the values might be needed later, preserve them through an approved retention or recovery approach before performing destructive maintenance.

Defer Physical Removal with SET UNUSED

ALTER TABLE orders SET UNUSED (old_code);

This makes the column unavailable while deferring physical removal of its stored data. It does not immediately reclaim that storage. It is also different from making a column invisible: there is no corresponding SET USED command to restore access to the old column.

When the maintenance window permits, remove unused columns physically:

ALTER TABLE orders DROP UNUSED COLUMNS;

The statement applies to all unused columns in the table, not just the most recently marked column. Review the table's maintenance history before scheduling it. Dividing logical removal and physical cleanup into separate steps provides scheduling flexibility, but it does not eliminate the cleanup work or make dependency review optional.

A useful change record explains why the column is obsolete, which applications were checked, who approved removal, and how the result will be verified. After the change, run representative queries and application operations. Keep the evidence of those checks with the change record so that a later problem can be investigated against a clear account of what changed.

Plan the Whole Operation, Not Just the Command

Each technique solves a particular problem, but implementation includes preparation and verification. Treat an operation as a small project with an owner, an expected result, and a completion condition. This prevents a technically successful command from being mistaken for a completed business task.

1. Define the result. Specify the source dataset, destination storage, temporary-data lifetime, or obsolete column involved. Identify which application or report will demonstrate success.
2. Confirm the environment. Connect to the intended application PDB and schema. Check the relevant object definitions and privileges rather than relying on an example's names.
3. Estimate the work. Consider available storage, concurrent activity, and the time needed for validation. Include associated objects and files in the plan.
4. Rehearse the operation. Use representative data and workload where practical. Record what happens when an input is invalid or a prerequisite is missing.
5. Verify the outcome. Check accepted data, object state, application behavior, and the original operational objective before declaring completion.

For example, imagine an order-processing application that receives product documents, maintains a growing order table, builds temporary report lists, and still carries an obsolete external-system code. The four techniques address four different parts of that application. They should not be combined into one large maintenance script merely because they appear in the same lesson. Schedule and validate each change according to its own purpose and dependencies.

This approach also makes results easier to measure. Record load completion time for the import task, user impact for the table move, correct cleanup for temporary work, and successful dependent queries for column removal. Revisit those measurements as the workload grows. A process that was adequate at one scale may need different scheduling or implementation later.

Choose the Feature That Matches the Task

Effective administration of a growing database depends on matching the operation to the workload. Use explicit loading rules, plan online maintenance around actual restrictions, choose temporary-table lifetimes deliberately, and coordinate schema cleanup with application changes. These habits make the four features useful at many database sizes without relying on a universal definition of “large.”