I'm missing a transform....



We've recently copied one of our schemas from a tablespace using TDE (i.e. wallets etc) to another system where we didn't have the ASO licence.

The issue we had is that datapump dump file was created fine with the data unencrypted (it tells you it's doing this with a nice warning at the end)

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

All fine - thats what i wanted

Now when i come to import the table and data in to my other system - i get this problem

Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SCHEMA"."BINARY_BUCKET_ELEMENT" failed to create with error:
ORA-28365: wallet is not open
Failing sql is:
CREATE TABLE "SCHEMA"."XX" ("XX_ID" NUMBER(18,0) NOT NULL ENABLE, "XX_MD5_HASH" VARCHAR2(20 BYTE), "XX_DATA" BLOB ENCRYPT USING 'AES128' 'SHA-1') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "XX -- then rest of DDL truncated


The problem of course being the clause:

"XX_DATA" BLOB ENCRYPT USING 'AES128' 'SHA-1')

Which needs an encryptable tablespace in place - which clearly i haven't got. This is a real pain as there is no transform option i can apply that will strip out this clause (even in 12c) - as it's part of the column definition not the storage clause.

The only option I'm left with is to pre-create the table and load with the truncate/append option and then worry about adding in all indexes/constraints etc manually afterwards.

I think a small enhancement is needed to expdp/impdp to allow this clause to be manipulated - otherwise it's a real pain.

Next version perhaps......?

0 comments:

Post a Comment