# Migrating Oracle LOBs from BasicFile to SecureFiles: A Field Guide

*Notes from a real production migration on Oracle Standard Edition 2, ahead of a 12c → 19c upgrade.*

BasicFile is Oracle's original LOB storage mechanism, predating 11g. SecureFiles has been the default since 12c and is where Oracle keeps investing engineering effort — including recent LOB optimizations and the AI Vector Search infrastructure underneath it. If you're planning a major version upgrade anyway, converting beforehand means you don't end up doing the storage-format migration and the version upgrade as two separate projects.

This is a walkthrough of the actual errors hit during one such migration, and how each was resolved — the kind of thing that doesn't show up in the official docs until you're the one staring at the error code.

## Edition constraints worth knowing up front

Several things commonly cited as SecureFiles benefits turn out to be Enterprise Edition-only in practice. Worth checking your edition before you plan around these:

*   **Compression and deduplication** require the Advanced Compression Option — Enterprise Edition only, not purchasable for Standard Edition at any price.
    
*   **LOB-level encryption** requires the Advanced Security Option (and a configured TDE wallet) — also Enterprise Edition only.
    
*   `ALTER TABLE ... MOVE ONLINE` (keeps a table live during a move) is Enterprise Edition only. On Standard Edition, every move runs offline and briefly locks the table.
    

On Standard Edition, the case for converting still holds — better concurrency behavior, and continued access to storage-layer improvements Oracle ships going forward — but the compression/dedup/encryption benefits you'll read about elsewhere simply aren't in play.

## Finding what needs converting

Start with `DBA_LOBS`, joined to `DBA_SEGMENTS` on both owner *and* segment name — not segment name alone, since that can cross-match across schemas. Filter out Oracle-maintained schemas to cut the noise:

```sql
SELECT l.owner, l.table_name, l.column_name, l.securefile,
       ROUND(s.bytes/1024/1024/1024, 3) AS gb
FROM   dba_lobs l
JOIN   dba_segments s
       ON s.owner = l.owner
      AND s.segment_name = l.segment_name
WHERE  l.securefile = 'NO'
AND    l.owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
ORDER  BY s.bytes DESC;
```

Two blind spots this query misses:

*   **Partitioned tables** — `DBA_LOBS.SEGMENT_NAME` is `NULL` at the table level for partitioned tables; the real segments live in `DBA_LOB_PARTITIONS`, which an inner join silently drops. Check it separately.
    
*   `LONG`**/**`LONG RAW` **columns** — these predate the LOB architecture entirely and never show up in `DBA_LOBS`, but they'll block `ALTER TABLE MOVE` on the whole table if present (more on this below).
    

In practice, a handful of tables usually account for the overwhelming majority of the footprint. In this case, four tables covered roughly 828 GB of the total scan — the long tail was negligible by comparison.

## Issues encountered, and how they were resolved

### 1\. Tablespace "autoextend" that wasn't actually extending

A `MOVE LOB` failed with `ORA-01652` (unable to extend segment). The tablespace's datafiles all showed `AUTOEXTENSIBLE = YES` — but every datafile also had `MAXSIZE` set to exactly its current size. Autoextend was technically enabled but had zero room to actually extend into. Worth auditing this before starting any large move:

```sql
SELECT file_name, autoextensible,
       ROUND(bytes/1024/1024/1024,2) AS cur_gb,
       ROUND(maxbytes/1024/1024/1024,2) AS max_gb
FROM dba_data_files
WHERE tablespace_name = '<TABLESPACE>';
```

### 2\. The filesystem itself was nearly full

The underlying mount was at 95% utilization, independent of the Oracle-level tablespace issue. A `MOVE LOB` briefly needs room for both the old and new copy of the data — so a 300+ GB LOB needs 300+ GB of real headroom, not just "some" free space.

When local disk can't provide that, a staging tablespace on separate storage is a workable relay:

*   Move the LOB into a temporary tablespace elsewhere, converting to SecureFiles at the same time — this also frees the original tablespace, since the old segment is dropped once the move completes.
    
*   Move it back into the original tablespace afterward if that's operationally required, or leave it in its own dedicated tablespace permanently.
    

### 3\. Datafile size ceiling on the staging tablespace

Creating a large single datafile for the staging tablespace hit `ORA-01144` — standard ("smallfile") tablespaces cap each datafile at 4,194,303 blocks, roughly 32 GB at an 8K block size. Two fixes: use multiple smaller datafiles, or create the tablespace as `BIGFILE`, which removes the per-file cap entirely. `BIGFILE` is the simpler option for scratch/staging space holding one large segment.

### 4\. A `LONG RAW` column blocking an unrelated move

Attempting to move a completely different LOB column on the same table failed with `ORA-00997` ("illegal use of LONG datatype"). Oracle refuses `ALTER TABLE MOVE` on *any* table containing a `LONG`/`LONG RAW` column, even when the move doesn't target that column.

Fix: convert it directly to `BLOB`/`CLOB` — you can land it as SecureFiles in the same statement:

```sql
ALTER TABLE <schema>.<table>
MODIFY (<long_column> BLOB)
LOB(<long_column>) STORE AS SECUREFILE;
```

### 5\. Foreign keys blocking a drop-and-reload approach

An alternative Data Pump-based approach was considered for one particularly space-constrained table: export, drop, reimport with `TRANSFORM=LOB_STORAGE:SECUREFILE`. The drop failed with `ORA-02449` — other tables had foreign keys referencing this one, and Data Pump's table-mode export doesn't capture FK constraints that live on *other* tables.

Before attempting a drop-and-reload on any table, capture dependent FK definitions first:

```sql
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT', constraint_name, owner)
FROM dba_constraints
WHERE r_constraint_name IN (
  SELECT constraint_name FROM dba_constraints
  WHERE owner = '<OWNER>' AND table_name = '<TABLE>'
  AND constraint_type IN ('P','U')
);
```

In this case, the staging-tablespace approach worked without needing to drop the table at all, so this path wasn't ultimately required — but it's worth checking for regardless. A failed drop mid-migration is a bad place to discover you didn't back up the FK definitions.

### 6\. Combining multiple LOB moves in one statement

```sql
ALTER TABLE <table>
MOVE LOB(col_a) STORE AS SECUREFILE,
MOVE LOB(col_b) STORE AS SECUREFILE;
```

This fails with `ORA-14133` — Oracle only permits one `MOVE` operation per `ALTER TABLE` statement. Each column needs its own statement, run one after another.

## The standard operating pattern

Once the blockers above are cleared, the actual conversion per column is simple:

```sql
ALTER TABLE <schema>.<table>
MOVE LOB(<column>) STORE AS SECUREFILE;
```

Followed by, every single time:

```sql
-- rebuild any indexes the move invalidated
SELECT index_name FROM dba_indexes
WHERE table_name = '<TABLE>' AND owner = '<OWNER>' AND status = 'UNUSABLE';
ALTER INDEX <index_name> REBUILD;

-- refresh stats
EXEC DBMS_STATS.GATHER_TABLE_STATS('<OWNER>','<TABLE>');

-- confirm
SELECT securefile FROM dba_lobs WHERE owner = '<OWNER>' AND table_name = '<TABLE>';
```

## Lessons for next time

*   Audit tablespace autoextend settings — actual `MAXSIZE` vs. current size, not just the flag — *before* starting, not after hitting `ORA-01652`.
    
*   Check every target table for `LONG`/`LONG RAW` columns up front. They won't show up in the LOB inventory query, but they'll block the move.
    
*   Capture dependent FK DDL before considering any drop-and-reload approach, even if you don't end up needing it.
    
*   If storage is tight, a `BIGFILE` staging tablespace on separate storage is a clean way to relay a large LOB through a conversion without needing double the space in the original tablespace.
    
*   Confirm your Oracle edition's actual feature availability before promising compression, dedup, encryption, or online moves as migration benefits. On Standard Edition, none of the above are available, no matter how much configuration effort you throw at it.