-- ONE SINGLE QUERY → generates perfect plain-SQL commands
-- Uses the REAL SYS_C... names from PROD.SALES
SELECT
'ALTER TABLE sales_staging ADD "' || column_name || '" VARCHAR2(1);' || CHR(10) ||
'ALTER TABLE sales_staging SET UNUSED COLUMN "' || column_name || '";'
AS plain_sql_commands
FROM dba_tab_cols
WHERE owner = 'PROD'
AND table_name = 'SALES'
AND column_name LIKE 'SYS_C%'
AND hidden_column = 'YES'
ORDER BY internal_column_id;
==========
STEP 2
-- Compare physical column order (INTERNAL_COLUMN_ID) between source and staging
WITH source_cols AS (
SELECT internal_column_id, column_name, data_type, hidden_column
FROM dba_tab_cols
WHERE owner = 'PROD' AND table_name = 'SALES'
ORDER BY internal_column_id
),
staging_cols AS (
SELECT internal_column_id, column_name, data_type, hidden_column
FROM dba_tab_cols
WHERE owner = 'PROD' AND table_name = 'SALES_STAGING'
ORDER BY internal_column_id
)
SELECT
s.internal_column_id AS source_pos,
s.column_name AS source_col,
t.internal_column_id AS staging_pos,
t.column_name AS staging_col,
CASE WHEN s.internal_column_id = t.internal_column_id THEN 'MATCH' ELSE 'MISMATCH' END AS status
FROM source_cols s
FULL OUTER JOIN staging_cols t
ON s.internal_column_id = t.internal_column_id
AND NVL(s.column_name, '?') = NVL(t.column_name, '?')
ORDER BY NVL(s.internal_column_id, t.internal_column_id);
++++++++++
-- This works 100% identically to the NUMBER version
-- (Oracle officially ignores datatype for UNUSED columns during exchange)
BEGIN
FOR i IN 1..40 LOOP
EXECUTE IMMEDIATE
'ALTER TABLE sales_staging ADD dgh_' || LPAD(i,3,'0') || ' VARCHAR2(1)';
EXECUTE IMMEDIATE
'ALTER TABLE sales_staging SET UNUSED COLUMN dgh_' || LPAD(i,3,'0');
END LOOP;
END;
/
===========
-- CORRECTED VERSION – works in all tools
SELECT source,
internal_column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale
FROM (
-- STAGING minus PARTITIONED
SELECT 'STAGING' AS source,
internal_column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale
FROM dba_tab_cols
WHERE owner = 'PROD' -- <<< change
AND table_name = 'SALES_STAGING' -- <<< change
MINUS
SELECT 'PARTITIONED' AS source,
internal_column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale
FROM dba_tab_cols
WHERE owner = 'PROD' --<<< change
AND table_name = 'SALES' --<<< change
UNION ALL
-- PARTITIONED minus STAGING
SELECT 'PARTITIONED' AS source,
internal_column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale
FROM dba_tab_cols
WHERE owner = 'PROD'
AND table_name = 'SALES'
MINUS
SELECT 'STAGING' AS source,
internal_column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale
FROM dba_tab_cols
WHERE owner = 'PROD'
AND table_name = 'SALES_STAGING'
)
ORDER BY internal_column_id, source; -- only ONE ORDER BY at the very end
-----
-- PERFECT ONE-SHOT QUERY – shows exactly what causes ORA-14097
WITH staging AS (
SELECT
column_id,
column_name,
data_type,
data_type_mod,
data_type_owner,
data_length,
data_precision,
data_scale,
char_length, -- correct column in DBA_TAB_COLUMNS
char_used, -- B or C – correct column
nullable,
data_default -- LONG! but we handle it safely below
FROM dba_tab_columns
WHERE owner = 'YOUR_SCHEMA'
AND table_name = 'SALES_STAGING'
),
partition_def AS (
SELECT
column_id,
column_name,
data_type,
data_type_mod,
data_type_owner,
data_length,
data_precision,
data_scale,
char_col_decl_length AS char_length, -- correct name in DBA_PART_COL_STATISTICS
char_used,
nullable,
NULL AS data_default -- this column does NOT exist here → always NULL
FROM dba_part_col_statistics
WHERE owner = 'YOUR_SCHEMA'
AND table_name = 'SALES'
AND partition_name = 'P_DEC2025'
)
SELECT
src,
column_id,
column_name,
data_type,
char_used,
nullable,
data_precision,
data_scale,
char_length,
CASE WHEN data_default IS NULL THEN 'NULL'
WHEN LENGTH(data_default) > 50 THEN SUBSTR(data_default,1,50)||'...(truncated)'
ELSE TO_CHAR(data_default)
END AS data_default_snippet
FROM (
SELECT 'STAGING ≠ PARTITION' AS src, s.*,
'S' as which
FROM staging s
FULL OUTER JOIN partition_def p
ON s.column_id = p.column_id
AND NVL(s.column_name,'?') = NVL(p.column_name,'?')
WHERE p.column_id IS NULL
OR s.column_id IS NULL
OR s.data_type <> p.data_type
OR NVL(s.data_type_mod,'X') <> NVL(p.data_type_mod,'X')
OR NVL(s.data_type_owner,'X') <> NVL(p.data_type_owner,'X')
OR s.data_length <> p.data_length
OR NVL(s.data_precision,-999) <> NVL(p.data_precision,-999)
OR NVL(s.data_scale,-999) <> NVL(p.data_scale,-999)
OR NVL(s.char_length,-999) <> NVL(p.char_length,-999)
OR NVL(s.char_used,'X') <> NVL(p.char_used,'X')
OR NVL(s.nullable,'X') <> NVL(p.nullable,'X')
OR (s.data_default IS NOT NULL AND p.data_default IS NULL)
OR (s.data_default IS NULL AND p.data_default IS NOT NULL)
UNION ALL
SELECT 'PARTITION ≠ STAGING', p.*,
'P'
FROM staging s
FULL OUTER JOIN partition_def p
ON s.column_id = p.column_id
AND NVL(s.column_name,'?') = NVL(p.column_name,'?')
WHERE p.column_id IS NULL
OR s.column_id IS NULL
OR s.data_type <> p.data_type
OR NVL(s.data_type_mod,'X') <> NVL(p.data_type_mod,'X')
OR NVL(s.data_type_owner,'X') <> NVL(p.data_type_owner,'X')
OR s.data_length <> p.data_length
OR NVL(s.data_precision,-999) <> NVL(p.data_precision,-999)
OR NVL(s.data_scale,-999) <> NVL(p.data_scale,-999)
OR NVL(s.char_length,-999) <> NVL(p.char_length,-999)
OR NVL(s.char_used,'X') <> NVL(p.char_used,'X')
OR NVL(s.nullable,'X') <> NVL(p.nullable,'X')
OR (s.data_default IS NOT NULL AND p.data_default IS NULL)
OR (s.data_default IS NULL AND p.data_default IS NOT NULL)
)
ORDER BY column_id, src;
-- 1. Structure of your staging table
SELECT
column_id,
column_name,
data_type,
data_type_mod,
data_type_owner,
data_length,
data_precision,
data_scale,
char_length,
char_used, -- B or C (this is a VERY common hidden mismatch)
nullable,
data_default
FROM user_tab_columns
WHERE table_name = 'SALES_STAGING'
ORDER BY column_id;
-- 2. Structure of the exact partition you want to exchange with
SELECT
column_id,
column_name,
data_type,
data_type_mod,
data_type_owner,
data_length,
data_precision,
data_scale,
char_length,
char_used, -- B or C
nullable,
data_default
FROM user_part_col_statistics -- this view shows the real partition definition
WHERE table_name = 'SALES' -- your partitioned table
AND partition_name = 'P_DEC2025' -- <<< change to your real partition name
ORDER BY column_id;
--------
-- One-liner that shows you the differences immediately
SELECT * FROM (
SELECT column_id, column_name, data_type, char_used, nullable, data_default, 'STAGING' src
FROM user_tab_columns WHERE table_name = 'SALES_STAGING'
MINUS
SELECT column_id, column_name, data_type, char_used, nullable, data_default, 'PARTITION'
FROM user_part_col_statistics
WHERE table_name = 'SALES' AND partition_name = 'P_DEC2025'
)
UNION ALL
SELECT * FROM (
SELECT column_id, column_name, data_type, char_used, nullable, data_default, 'PARTITION'
FROM user_part_col_statistics
WHERE table_name = 'SALES' AND partition_name = 'P_DEC2025'
MINUS
SELECT column_id, column_name, data_type, char_used, nullable, data_default, 'STAGING'
FROM user_tab_columns WHERE table_name = 'SALES_STAGING'
)
ORDER BY column_id, src;
