Fixing ORA-00904 on Oracle hidden columns during SSMA data migration

SSMA (SQL Server Migration Assistant) handles the whole Oracle-to-SQL Server move: it reads the source data dictionary, converts the schema, then generates a SELECT per table to pull the rows across. That last step is where this story goes wrong.

The problem

Migrating ~5,600 Oracle tables to SQL Server with SSMA. Most load fine; ~100 tables fail Migrate Data with the same error:

ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904:
"SYS_C00004_21081414:28:22$": invalid identifier

The named column exists in no DDL anyone wrote. The [Ora] prefix says Oracle itself is rejecting the query: SSMA built an extraction SELECT naming a column Oracle refuses to resolve. Tellingly, SELECT * and COUNT(*) run fine against the same table, whatever this column is, Oracle is happy to ignore it, but not to be asked for it by name.

Where the column actually comes from

The name is the giveaway: SYS_C00004_21081414:28:22$ is what Oracle calls a column after ALTER TABLE … SET UNUSED COLUMN.

Oracle offers two ways to get rid of a column: a logical delete and a physical one. The physical delete (ALTER TABLE … DROP COLUMN) is the honest one, but on a large table it is very time- and resource-consuming. That’s why people reach for the logical delete instead:

ALTER TABLE table_name SET UNUSED (column_name);

That statement is metadata-only and instant. The column immediately stops being visible to users, and the physical removal is deferred to whenever there is time for it (see Oracle Documentation):

ALTER TABLE table_name DROP UNUSED COLUMNS;
-- on large tables, cap undo growth by checkpointing every N rows:
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

To free the original name for reuse, Oracle renames the column to SYS_C<internal column number>_<YYMMDDHH24:MI:SS>$, sets USER_GENERATED to NO, HIDDEN_COLUMN to YES and releases its COLUMN_ID. So the timestamp is not when the column was added, it is the second someone ran SET UNUSED. Ours says 14 August 2021, 14:28:22.

That also explains the error pattern. The operation is one-way and the column is unreadable by design, so naming it gets you ORA-00904 «invalid identifier». SELECT * and COUNT(*) keep working because the column no longer has a COLUMN_ID and is simply excluded from the star. SSMA, however, lists it and builds an explicit column list Oracle then refuses.

Not to be confused with SYS_NC…$. Those are a different animal: virtual columns backing a function-based index or extended statistics.

Bottom line: returning NULL costs you nothing. This is a column its owner already decided to delete, holding data Oracle itself will no longer hand out. There is no information left to lose.

What doesn’t work for the migration

• SSMA setting Ignore hidden system columns = Yes was not making any effect on this use case
• Dropping the column on SQL Server resolves nothing because the error is on the source SELECT, unaffected.
• Dropping it on Oracle could not be done in our scenario because the source is frozen; DDL not allowed.
• Custom select, column removed or bare NULL: SSMA still expects the name in its mapping and fails with “key not present” or “does not match up” before the query ever reaches Oracle.

Find them all first

Before editing anything, get the full list. Discovering the affected tables one failed migration at a time is a waste of an afternoon because the data dictionary already knows.

The reason the columns are findable at all is an asymmetry between two views: an unused column is gone from ALL_TAB_COLUMNS, but still listed in ALL_TAB_COLS with HIDDEN_COLUMN = 'YES'. That second view is what you query:

SELECT owner,
table_name,
column_name,
data_type,
internal_column_id,
TO_DATE(REGEXP_SUBSTR(column_name, '\d{8}:\d{2}:\d{2}'),
'YYMMDDHH24:MI:SS') AS set_unused_at
FROM dba_tab_cols
WHERE hidden_column = 'YES'
AND user_generated = 'NO'
AND REGEXP_LIKE(column_name, '^SYS_C\d+_\d{8}:\d{2}:\d{2}\$$')
-- AND owner = '<SCHEMA_NAME>'
ORDER BY owner, table_name, internal_column_id;

The regex is deliberately strict: it matches only the SET UNUSED naming pattern, so virtual columns and other system-generated names stay out of the result.

One more view is worth a look, as a cross-check:

SELECT owner, table_name, count AS unused_columns
FROM   dba_unused_col_tabs
--WHERE owner = '<SCHEMA_NAME>'
ORDER  BY count DESC, table_name;

DBA_UNUSED_COL_TABS gives the number of unused columns per table. Sorting by that number puts the dangerous tables first: those with two or three hidden columns are the ones where you’ll forget a line in the custom select and be back at square one.

The fix

Keep the hidden column’s name as an alias, but return a literal NULL instead of reading it. SSMA’s mapping finds the name (no “key not present”); Oracle never resolves the real column (no ORA-00904).

• Tools → Project Settings → General → Migration → enable Extended data migration options.

• Data Migration Settings tab → tick Use custom select → replace each hidden-column line with:

 SELECT ...   
    TO_CHAR("<COLUMN_NAME>", 'TM', 'NLS_NUMERIC_CHARACTERS = ''.,''') as "<COLUMN_NAME>",
    NULL as "SYS_C00004_21081414:28:22$"
from <OWNER>.<TABLE_NAME> t

• Migrate Data → 100%. Drop the NULL-filled column(s) on SQL Server in post-migration cleanup.

Takeaway

SYS_C…$ is not an exotic Oracle feature, it’s an ordinary column someone deleted years ago, logically. Oracle keeps the name on file; SSMA finds it, insists on naming it, and Oracle refuses to hand it over. Aliasing a NULL satisfies both, then you drop the column on the target. No source DDL, no external tooling, everything inside SSMA, behind a project setting that’s hidden by default.