ORA-31696: unable to export/import TABLE_DATA:”schema”.”table” using client specified AUTOMATIC method

by in DataPump and tagged

Part of my job is to support a Business Intelligence development team. They often need to have certain test schemas refreshed from production. I’ve been using DataPump and on one schema I always get ORA-31696 for the same three tables. It’s due to the way I’m refreshing tables. I truncate the tables first and then I load them using the “content=data_only” parameter. It works for most tables but these three tables contain a LONG column definition. The fix for this is to run a second import using “table_exists_action=replace” and TABLES=(list of skipped tables).

2 thoughts on “ORA-31696: unable to export/import TABLE_DATA:”schema”.”table” using client specified AUTOMATIC method”

  1. Great post Steve !
    I just saved me a lot of grief as I already did expdp & truncated a table without knowing it had a ‘long’ field in it on Production and had the error while using impdp with truncate & append option, luckily I came across your posting & it worked!
    Thanks a lot.

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s