ORA-14692: STORE AS clause is not allowed for extended character type column

I was trying to import (oracle's legacy imp ) an oracle (12cR1) imp backup where we we set max_string_size=extended in both destination and target database. but while import we faced below issue. 

  IMP-00017: following statement failed with ORACLE error 14692: 

 IMP-00003: ORACLE error 14692 encountered 

ORA-14692: STORE AS clause is not allowed for extended character type column 

ORA-14692: STORE AS clause is not allowed for extended character type column 14692. 00000 - "STORE AS clause is not allowed for extended character type column" *Cause: An attempt was made to use the STORE AS clause to specify storage attributes for an extended character type column. Action: Correct the statement and reenter.

 

 

Alternative Solution :  Then I used data pump (expdp/impdp) and didn't face this issue. so I think, Oracle's legacy exp/imp  doesn't recognise the extended datatype feature How to increase varchar2 column to 32k character size? which is started from oracle 12c onwards. 

 

 

ORA-01450: maximum key length exceeded - oracle varchar2 data type support 32000 bytes/characters

Oracle Database 12c onwards the varchar2 data type support 32000 bytes/character.  but by default it is not enabled. you have to do some work to make it happen. 

by default it support only 4000 bytes. 

Here are the steps to implement this - 

1. Shut down the database.

2. Restart the database in UPGRADE mode.

3. Change the setting of MAX_STRING_SIZE to EXTENDED.

4. Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.

5. Restart the database in NORMAL mode.

Now practically- 

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\my>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 16 10:36:19 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
         0

PL/SQL Release 12.1.0.2.0 - Production
         0

CORE    12.1.0.2.0      Production
         0


BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
         0

NLSRTL Version 12.1.0.2.0 - Production
         0


SQL>
SQL>
SQL> create table test(comments varchar2(30000)) ;
create table test(comments varchar2(30000))
                                    *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL>
SQL>
SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL>
SQL>
SQL>
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP UPGRADE;
ORACLE instance started.

Total System Global Area 5150605312 bytes
Fixed Size                  3100560 bytes
Variable Size            1094713456 bytes
Database Buffers         4047503360 bytes
Redo Buffers                5287936 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET max_string_size=extended;

System altered.

SQL>
SQL>
SQL> @C:\app12c\oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN\utl32k.sql

Session altered.

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database does not have compatible >= 12.0.0
DOC>
DOC>   Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.


Session altered.


0 rows updated.


Commit complete.


System altered.


PL/SQL procedure successfully completed.


Commit complete.


System altered.


Session altered.


PL/SQL procedure successfully completed.

No errors.

Session altered.


PL/SQL procedure successfully completed.


Commit complete.


Package altered.


Package altered.

SQL>
SQL>
SQL>
SQL>
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup ;
ORACLE instance started.

Total System Global Area 5150605312 bytes
Fixed Size                  3100560 bytes
Variable Size            1094713456 bytes
Database Buffers         4047503360 bytes
Redo Buffers                5287936 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create table test(comments varchar2(30000)) ;

Table created.

SQL>
SQL>
SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL>
SQL>
SQL>
SQL>
SQL> create table halim(name varchar2(32000)) ;

Table created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> show parameter compa

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction              string      ADAPTIVE
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE
SQL>
SQL>
SQL>
SQL>
SQL>

For more info- 

https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321