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?
3
-
Blob column only in one table ?– user153556Commented Feb 11, 2020 at 11:25
-
Check this link– user168186Commented Feb 11, 2020 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– user168186Commented Feb 11, 2020 at 15:36
Add a comment
|
1 Answer
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.
-
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– user168186Commented Feb 12, 2020 at 11:10 -
Have you done a test and proven that that error would happen? Commented Feb 12, 2020 at 13:29
-
Yes of course that error was from test case– user168186Commented Feb 12, 2020 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)))? Commented Feb 12, 2020 at 15:24
-
1Uh yes casting did the trick :-)– user168186Commented Feb 12, 2020 at 16:08