Month: April 2017

ORA-01758: table must be empty to add mandatory (NOT NULL) column

Table must be empty to add mandatory (NOT NULL) column.

While adding NOT NULL column into a table, the following error occurred:

SQL> Alter table TEST_TBL add NAME  CHAR(12) NOT NULL;
Alter table TEST_TBL add NAME  CHAR(12) NOT NULL
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

The workarounds could be either of the following:

1) Default value is given:

SQL> Alter table TEST_TBL add NAME  CHAR(12) DEFAULT 'EMPTY' NOT NULL;

2) Remove NOT NULL:

SQL> Alter TEST_TBL add NAME CHAR(12);

3) Backup the table to another staging table, truncate the table and then add the NOT NULL column, insert the records back to the table from the staging table.

Advertisement

ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

In Unicode AL32UTF8, special characters, invalid characters might cause errors like “ORA-39346: data loss in character set conversion”.

When importing a schema exported from one 12cR1 AL32UTF8 database to another database  with same AL32UTF8 characterset, this ORA -error was generated :

ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

The error message clearly shows the problem data is from “SCHEMA_EXPORT/TABLE/COMMENT”.

Using MDU tool ( Database  Migration Assistant for Unicode ) to scan database Dictionary Objects, we can see the problem data is from SYS.COM$.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Using MDU tool ( Database  Migration Assistant for Unicode ) to scan database Dictionary Objects again, we can see all green including SYS.COM$.

Do another export and import again, everything works fine.