DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12712 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12712: new character set must be a superset of old character set

ORA-12712 occurs when you attempt to change an Oracle database character set to one that cannot represent all characters supported by the current character set. Oracle enforces this restriction to prevent data corruption — if the new character set is not a superset of the old one, existing data may become unreadable or permanently damaged. This error most commonly appears during the execution of ALTER DATABASE CHARACTER SET.

Top 3 Causes

1. Changing to an Incompatible (Non-Superset) Character Set

The most frequent cause is attempting to switch between character sets that have no superset relationship — for example, moving from KO16MSWIN949 to WE8ISO8859P1. These two character sets serve entirely different language groups, so neither is a superset of the other.

-- This will FAIL with ORA-12712
-- KO16MSWIN949 → WE8ISO8859P1 (not a superset)
ALTER DATABASE CHARACTER SET WE8ISO8859P1;

-- Check current character set first
SELECT PARAMETER, VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
Enter fullscreen mode Exit fullscreen mode

2. Skipping Pre-Migration Validation

Attempting a character set change without running Oracle's CSSCAN utility or the Database Migration Assistant for Unicode (DMU) is a common mistake. These tools identify data that cannot be safely converted, but skipping them leads directly to ORA-12712 — or worse, silent data corruption.

-- After running CSSCAN, review conversion issues:
SELECT TABLE_NAME, COLUMN_NAME, FAILED_COUNT
FROM CSMIG.CSMIG$COLUMN_DATA
WHERE FAILED_COUNT > 0
ORDER BY FAILED_COUNT DESC;

-- Check for unconvertible data
SELECT *
FROM CSMIG.CSMIG$ERRORS
WHERE ROWNUM <= 20;
Enter fullscreen mode Exit fullscreen mode

3. Confusing NCHAR Character Set with Database Character Set

Developers sometimes mix up the database character set (NLS_CHARACTERSET) with the national character set (NLS_NCHAR_CHARACTERSET). Applying the wrong ALTER command to the wrong character set triggers ORA-12712 or its sibling error ORA-12714.

-- Verify both character sets before making changes
SELECT PARAMETER, VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

-- Correct syntax for national character set change
ALTER DATABASE NATIONAL CHARACTER SET AL16UTF16;

-- Correct syntax for database character set change
ALTER DATABASE CHARACTER SET AL32UTF8;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Restart in RESTRICTED mode (required for character set changes):

SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
Enter fullscreen mode Exit fullscreen mode

Step 2 — Change to a valid superset character set:

-- AL32UTF8 is a superset of most single-language character sets
ALTER DATABASE CHARACTER SET AL32UTF8;

-- Confirm the change
SELECT VALUE FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

-- Return to normal mode
SHUTDOWN IMMEDIATE;
STARTUP;
Enter fullscreen mode Exit fullscreen mode

Alternative: Export/Import migration (safest when direct conversion is blocked):

-- On source DB: export full database
-- expdp system/pwd full=y directory=DATA_PUMP_DIR dumpfile=full.dmp

-- Create new DB with correct character set
CREATE DATABASE newdb
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16;

-- On target DB: import data
-- impdp system/pwd full=y directory=DATA_PUMP_DIR dumpfile=full.dmp
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always use AL32UTF8 from day one.
When creating a new database, specify AL32UTF8 as the character set. It is Oracle's recommended standard and a superset of virtually all single-byte character sets, eliminating future migration headaches.

CREATE DATABASE mydb
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16;
Enter fullscreen mode Exit fullscreen mode

2. Always back up and validate before any character set change.
Run a full RMAN backup and Oracle DMU scan before touching the character set. This gives you a recovery point and surfaces conversion issues before they cause irreversible damage.

-- RMAN full backup before migration
-- RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- Validate backup
SELECT STATUS, COUNT(*) FROM V$BACKUP GROUP BY STATUS;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-12713 Data loss during character conversion in CHAR/VARCHAR2 columns
ORA-12714 Invalid national character set — same concept as ORA-12712 for NCHAR types
ORA-12721 Operation not allowed when other sessions are active (not in RESTRICT mode)

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)