I was analyzing one export taking longer and one big table was not using the Direct Path mode.
I tried to export only this table and set the access method using the expdp parameter “ACCESS_METHOD=DIRECT_PATH”.
This table was created using Lower case and I struggled for a few minutes to find the right syntax 🙂
How to Export or Import Case Sensitive Tables Without Using a Par File (Doc ID 1622134.1)
tables=<USER>.\”TEst\”
After the export trying to force the export mode, I had the error ORA-31696:
I checked this MOS note and the table matches all the conditions for Direct Path export:
Export/Import DataPump Parameter ACCESS_METHOD – How to Enforce a Method of Loading and Unloading Data? (Doc ID 552424.1)
Let’s check the view KU$_UNLOAD_METHOD_VIEW, which can tell the method will be used by this table:
After analyzing the view code to see why it’s not allowing to export this table using the direct path method, I found It’s failing on condition 8:
The table has 3 not null columns added with a default value and it was an optimization from a long time ago to make adding columns quickly, but as you can see, it can cause some downside on the export.
I created a new table to have the default value on the database block and not only at the dictionary:
create table fusion.hz_relationships_new parallel 16 tablespace FUSION_TS_SEED as select * from fusion.”hz_relationships” ;
And the export succeeds:
I decided to add a new column not null with default value to this table, to simulate having the data only at the dictionary level and not at the block level:
And as expected, the export failed:
Alter table move also didn’t fix the issue:
I will try to get this condition updated on the Documentation.
Meanwhile, if you are struggling to find the reason for not being able to use the Direct Path method to export a table, this post should help you troubleshoot the issue 🙂