2

I need to import a 50 GB dump file containing three schemas. Over 90% of the file is taken up by a single BLOB column. How could I import the schemas, including the table containing the blobs, but replacing the values in the column with nulls? I could do a full import, null the column manually, do an export, delete the schemas and do a new import, but is there away to exclude a single column's data with impdp parameters?

  • Blob column only in one table ? – Sam Feb 11 at 11:25
  • Check this link – Kumar Feb 11 at 15:26
  • Above link shows how you can null any columns with expdp but you can do the same with impdp but expdp dump file size(for columns with clob and blob) will be smaller – Kumar Feb 11 at 15:36
0

Another option would be to do a datapump export of a view that has only the columns and data that you want. If you want a column to be null have the view make the column null. Then when you do the import you are only importing the data that you want.

expdp VIEWS_AS_TABLES

| improve this answer | |
  • First option works but second option i.e making some columns null will fail with errors like ORA-01723: zero-length columns are not allowed – Kumar Feb 12 at 11:10
  • Have you done a test and proven that that error would happen? – Gandolf989 Feb 12 at 13:29
  • Yes of course that error was from test case – Kumar Feb 12 at 14:19
  • I was an Oracle DBA, but I am now working with Postgres. Hence, I am not sitting in front of an Oracle database. Have you tried using the CAST function in a view and then use datapump to export that? i.e. (CAST (NULL AS VARCHAR2(20)))? – Gandolf989 Feb 12 at 15:24
  • 1
    Uh yes casting did the trick :-) – Kumar Feb 12 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.