0

When trying to export import into a table into another schema i am facing the following issue:

ORA-31696: unable to export/import TABLE_DATA:"schemaowner"."tablename":"SYS_P41" using client specified AUTOMATIC method
ORA-31696: unable to export/import TABLE_DATA:"schemaowner"."tablename":"SYS_P42" using client specified AUTOMATIC method
ORA-31696: unable to export/import TABLE_DATA:"schemaowner"."tablename":"SYS_P43" using client specified AUTOMATIC method
ORA-31696: unable to export/import TABLE_DATA:"schemaowner"."tablename":"SYS_P44" using client specified AUTOMATIC method

I want to export all data from a table in one schema and import hem into another schema. The errors occur during import. Below is the command i used for export and the command for import which fails:

expdp schema1/schema1@db1 directory=MYDIR tables=schema1.tbl dumpfile=tbl.dmp logfile=tbl.log content=data_only version=10.2.0.4.0

The above works and damp file is created but when trying:

impdp schema2/schema2@db1 directory=MYDIR tables=tbl dumpfile=tbl.dmp logfile=tblload.log content=data_only version=10.2.0.40 remap_schema=schema1:schema2

The above fails with the error specified in the beginning. Can you please advise me what i am doing wrong. I would truly appreciate it

Also as a reference the commands i run them on a linux os with oracle 10

George Georgiou
  • 455
  • 2
  • 14
  • 27
  • i checked also and my table doesn't contain LONG column based on some answers i found online – George Georgiou Apr 08 '14 at 16:16
  • Are you exporting and importing to the same version and edition - from previous questions I think this was to a different schema in the same DB, is that right? Looks like the automatically assigned partition names are different in the two schemas (which would make sense). If that is the case then you can use `REMAP_TABLE` to rename the partitions, but the data you're putting in each one would have to be valid, so it would need to be done carefully? (I haven't tried that and can't test it...) – Alex Poole Apr 08 '14 at 16:19
  • Yes you are right i am inserting to the same database but different schema. The tables have the same structure. I will try to use the remap_table and see what will happen – George Georgiou Apr 08 '14 at 17:24
  • [`partition_options=merge`](http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL923) *might* also work here, but again I've never tried that. It *might* insert data from all the source partitions into the master table, where it would be re-partitioned automatically. But it might just not let you if the target table is partitioned, and it might not handle it well if it does. Just thinking out loud really... – Alex Poole Apr 08 '14 at 17:39

0 Answers0