Hi,
I’ve never been a big fan of using the System user to perform database exports and today I found another good reason not to use it.
I was investigating some slowness during an export phase.
The default tablespace for the user System is the system tablespace which is not ASSM.
Getting the DDL for the Master Table, we can see it’s using BASICFILE for the LOBs and it’s ok since the System tablespace is not ASSM.
If you try to move the lob columns to SecureFiles, you will get an error:
Let’s create a new user, change the default tablespace for this user to an ASSM tablespace, and set the parameter DB_SECUREFILE to always.
create user EXPDP_AZ identified by *****;
grant exp_full_database to expdp_az;
alter system set DB_SECUREFILE=’ALWAYS’;
alter user EXPDP_AZ default tablespace FUSION_TS_TOOLS;
As you can see now, it’s using SECUREFILE:
Just this change reduced the Insert time by a half.
Unfortunately, we still have one limitation:
The table KU$_DATAPUMP_MASTER_12_2 is a Global Temporary Table and it’s not possible to have SecureFile Lobs on Global Temporary Tables.
ORA-43853 When Creating Temporary Table Contains SECUREFILE LOBs In Temporary Tablespace In 12c (Doc ID 2323564.1)
Just out of curiosity, I created a regular table (Lobs on SecureFiles) and ran the insert manually. The Insert time was only 24 seconds.