I have a scenario of table refresh with having BLOB column from prod to dev.
Say ex, MASTER_ACCOUNT table in prod having BLOB column (name ATTACH_ID), every night or weekly application team want to refresh in dev region, where having same set of table. i.e.., MASTER_ACCOUNT table in dev having column BLOB (name ATTACH_ID) too.
What are the restrictions on how the test table is updated? Do you need to preserve the existing table structure as is? Do you need to preserve any of the existing data? Do you only want to update the rows that exist in test or do you want to bring all the data in production to test for these tables?
If you can replace the entire table you could use export/import since with an export of the base table the blob is associated with would also export/import the blob segment.
We can truncate data in dev before inserting prod data. Structure in dev is same as that of prod. Not having any specific restriction.
If possible they are looking for option like SQL loader & insert in to dev table via db links.. i.e.., without DBA intervention.
where as expdp/impdp requires DBA.
The sqlldr utility can definitely be used to load blob data. Someone would just need to develop a program to extract the data into a file or series of files (one per blob), copy the files to the test server, and then run sqlldr to insert the data.
You could also look to see if Oracle has removed the restriction that did not support the insertion of BLOB data in a distributed transaction. If this restriction has been removed then a truncate followed by a simple insert/select in turn followed by a commit might be all you need.
You could probably use impdp directly without exporting data actually, by using NETWORK_LINK (a db link), and taking data only (avoiding meta-data). I remember it was not working with LONG datatype, but it should work with LOB.
Find out more :