Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ALTER TABLE MOVE encrypted column + CLOB

Solomon YakobsonAug 19 2022

Version: 19.13.0.0.0
Issue:

DROP TABLE TEST_TBL_MOVE PURGE
/
CREATE TABLE TEST_TBL_MOVE(
                           ENCRYPTED_COLUMN VARCHAR2(9 CHAR) ENCRYPT USING 'AES192' 'SHA-1' NO SALT  NOT NULL ENABLE, 
                           CLOB_COLUMN      CLOB
                          )
  TABLESPACE USERS
  LOB(CLOB_COLUMN) STORE AS SECUREFILE
/
INSERT
  INTO TEST_TBL_MOVE
  SELECT  '123456789',
          'X'
    FROM  DUAL
/
COMMIT
/
ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS LOB(CLOB_COLUMN) STORE AS SECUREFILE(TABLESPACE USERS)
/
ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS LOB(CLOB_COLUMN) STORE AS SECUREFILE(TABLESPACE USERS)
*
ERROR at line 1:
ORA-12899: value too large for column ??? (actual: 25, maximum: 9)

SQL>

If we remove CLOB column table move works fine:

DROP TABLE TEST_TBL_MOVE PURGE
/
CREATE TABLE TEST_TBL_MOVE(
                           ENCRYPTED_COLUMN VARCHAR2(9 CHAR) ENCRYPT USING 'AES192' 'SHA-1' NO SALT  NOT NULL ENABLE
                          )
  TABLESPACE USERS
/
INSERT
  INTO TEST_TBL_MOVE
  SELECT  '123456789'
    FROM  DUAL
/
COMMIT
/
ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS
/

Table altered.

SQL>

If we add CLOB back and don't encrypt other column:

DROP TABLE TEST_TBL_MOVE PURGE
/
CREATE TABLE TEST_TBL_MOVE(
                           UNENCRYPTED_COLUMN VARCHAR2(9 CHAR) NOT NULL ENABLE, 
                           CLOB_COLUMN      CLOB
                          )
  TABLESPACE USERS
  LOB(CLOB_COLUMN) STORE AS SECUREFILE
/
INSERT
  INTO TEST_TBL_MOVE
  SELECT  '123456789',
          'X'
    FROM  DUAL
/
COMMIT
/
ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS LOB(CLOB_COLUMN) STORE AS SECUREFILE(TABLESPACE USERS)
/

Table altered.

SQL>

Did anyone ran into this isuue and found solution? I found nothing on Oracle support site.
SY.
This post has been answered by Jonathan Lewis on Aug 20 2022
Jump to Answer

Comments

JohnWatson2

Column encryption does have a fair bit of overhead for narrow columns. I haven't checked since 11.2 and it may have changed since, but back then columns were always padded out to multiples of 16 bytes. Then salt adds 16 bytes, and MAC adds 20. So a varchar2(1) actually used 52 bytes.
I know you specified NO SALT, but 9 + 16 = 25 which is a bit of coincidence. Do you get the same effects with and without NO SALT and NOMAC ? Does disabling inline storage for the LOB change things?
The above are not answers, just possible lines for research.

Jonathan Lewis
Answer

I ran your test on 19.11.0.0 several times and got a similar result, except the "actual" kept changing (varying between 21 and 31). I think the problem is triggered by the CHAR declaration - nothing goes wrong if the declaration is varchar2(9), it only fails on varchar2(9 char).
If it's really important a possible workaround is to declare the column using byte semantics, allowing for the worst-case byte count for a single character and then add a column constraint that limits it to the 9 characters you want; e.g for a 2-byte character set:

        ENCRYPTED_COLUMN VARCHAR2(18)
                ENCRYPT USING 'AES192' 'SHA-1' NO SALT
                NOT NULL
                check (length(encrypted_column) <= 9),

Regards
Jonathan Lewis

P.S. A little warning: the 19c manual says that SHA-1 will be deprecated in 21c.

Marked as Answer by Solomon Yakobson · Aug 20 2022
Solomon Yakobson

Ah, thanks Jonathan, you're right - move fails if encrypted column length semantics is CHAR. So workaround is to change column length semantics to BYTE, move table and change it back to CHAR. Still, I wonder how encrypted column in conjunction to CLOB column makes move to fail. Will see what Oracle says...
SY.

Solomon Yakobson

And the test:

SQL> DROP TABLE TEST_TBL_MOVE PURGE
  2  /


Table dropped.


SQL> CREATE TABLE TEST_TBL_MOVE(
  2                             ENCRYPTED_COLUMN VARCHAR2(9 CHAR) ENCRYPT USING 'AES192' 'SHA-1' NO SALT  NOT NULL ENABLE,
  3                             CLOB_COLUMN      CLOB
  4                            )
  5    TABLESPACE USERS
  6    LOB(CLOB_COLUMN) STORE AS SECUREFILE
  7  /


Table created.


SQL> INSERT
  2    INTO TEST_TBL_MOVE
  3    SELECT  '123456789',
  4            'X'
  5      FROM  DUAL
  6  /


1 row created.


SQL> COMMIT
  2  /


Commit complete.


SQL> ALTER TABLE TEST_TBL_MOVE MODIFY ENCRYPTED_COLUMN VARCHAR2(36 BYTE) -- 9 times 4 (4 is max # of bytes per character in my charset)
  2  /


Table altered.


SQL> ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS LOB(CLOB_COLUMN) STORE AS SECUREFILE(TABLESPACE USERS)
  2  /


Table altered.


SQL> ALTER TABLE TEST_TBL_MOVE MODIFY ENCRYPTED_COLUMN VARCHAR2(9 CHAR) -- back to char length semantics.
  2  /


Table altered.


SQL>

SY.

1 - 4

Post Details

Added on Aug 19 2022
#general-database-discussions
4 comments
544 views