Monday, September 28, 2020

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. 
 
 

Sunday, September 27, 2020

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- 



Wednesday, July 1, 2020

Data model relation mapping ideas

This post is only for myself. sometimes need to check the theory to finalize. Here it covered almost all kind database table/relation mapping.  I just copied it from my note. didn't even check the writing.



Data model relation mapping idea

1.    Multivalue – like Interest column (one can have multiple interest topics)
2 tables/relations.  a new relation (bridge table) need to create for this . main tables pk will be fk in multi-value table . multi-value will be a composite key

 
2.    One to one relationship
2 tables/relations
 answer: Either way is fine





3.    one to one ( one is full participation)

2 tables/relations but one way, need to avoid null value (emp --Dept : all emp must partcipate so Dept.pk will fk in Emp to avoid null value)



4.    One-to-many
Normal way, 2 tables



5.    many-to-many
3 tables, Separate relation (bridge table concept)

 

6.    Identifing relationship with weak entity type
 Like one-to-many (composite key in many table) (almost same with multi-value)








Super type and subtype

1)    Case-1 :  Mandatory - disjoint
(Mandatory means master must be relation with childs, disjoint means- chlid should separate (child1+child2=master), not overlapping)
Two child table (no master table) with all master table's columns


 

2)    Case-2 : Mandatory - Overlaping
Three table/relation, 1 Master table, 2 child tables (with pk of master table)
Or
1 table including all master and childs with a identifiyng flag column (not recommended, consistency problem)



3)    Case-3 : Non-Mandatory - Overlaping
   Same way as case-2, Overlapping are same way

 

4)    Case-4 : Non-Mandatory - Disjoint
Same way as case-2, 3 tables


 




Union type
In union type, looks like Master (actually not master table here, chlid of two masters table) table is a sub-type of Childs
3 tables:  it is like a normal type (sub-type table) table with artificial identifier/surrogate Id column as PK and referencing (FK) to two masters table.

 
Here is the overall picture:  EER (right  sided one) and How to Relation Mapping (left sided one) 



EER : extended Entity diagram and relation mapping