Unable to import table data into a different schema

[Marcel Hoefs]

Version: 13.0.0.1882

I used the export data tool to export the data of one table and I selected the PL/SQL Developer method. During export the only option that was checked was the compress file option.

I want to import this table and data into a different database and different schema. So after opening the import tables tool I selected the ".pde" file and changed the window connection of the import tables tool to the target database and schema. The only option that was checked in this tool was the delete records option. After completing the import the table was not created in the target schema. After some analysis I found out that the import was actually done on the source database and schema. So to make sure this analysis was correct I disconnected all sessions and then in the import tables tool I connected to the correct target database and schema. I retried the import with the same ".pde" file and got an "not logged on" error.

Is it not possible to export data from one database schema using the export tables tool and the PL/SQL Developer method and importing this export into a different database and different schema or is this a bug?

Regards, Marcel

 

[Marco Kalter]

Did you export the table when connected as the owner of the table? When you open the .pde file in the import tool, does it list the tables as .

or just

?

 

[Marcel Hoefs]

I exported the table as object owner and when I open the .pde file in the import tool logged on to the target database and target schema it does not list the schema name. So it lists the table simply as

.

 

[Marco Kalter]

In that case the file will be imported in the current schema and database of the connection of the Import tool, as specified in the status line at the bottom of the Import Tool Window.

 

[Marcel Hoefs]

Yes I expected this too but to my surprise the file was imported into the source database and schema. When I disconnected this user and retried the import I got an "not logged on" error. So you can imagine my surprise!

 

[Marcel Hoefs]

Do you have any idea when this bug will be fixed? Now I am forced to use SQL insert statements, which is slow for large tables, or use SQL Developer instead. Neither are attractive alternatives...

 

[Marco Kalter]

We cannot reproduce the issue. It seems to work just fine. Do you have multiple connections open when you run the import? If so, does it help if you open just 1 connection?

 

[Marcel Hoefs]

Hi Marco,

These are the steps to reproduce the issue:

Assumption
- The target schema in the target database contains an empty table with the same name and structure as the table that will be exported

1. start new PL/SQL Developer instance
2. logon to a database schema (e.g. A@DB1) during startup of PL/SQL Developer
3. in object browser select a table from the connected database schema
4. right click table and choose "export data"
5. choose "PL/SQL Developer" tab and choose a file name (.pde file), all options unchecked except "Compress file"
6. click the export button
7. after export finishes close the "export data" window
8. choose from the Tools menu "Import Tables"
9. choose PL/SQL Developer tab in the "Import Tables" window
10. change window connection in the "Import Tables" window to the target database and target schema (e.g. B@DB2)
11. choose the saved .pde file
12. all options are unchecked except for the "Delete records" option
13. Press the import button
14. after the import finishes check the "Log" tab for errors
15. If all is good, open a SQL window, observe that the window connection is A@DB1 (the connection that was used during the export data) and also observe that the commit and rollback button are active.
16. change the connection of the SQL Window to B@DB2 (the connection used during the import data)
16. query the table that was exported
17. observe that this query returns no rows. This of course is the wrong result.

If you press the rollback button in the A@DB1 session, observe that the source table where you exported the data from is now empty!

If you repeat all steps above and logoff the A@DB1 session BEFORE pressing the import button you get the following error: "Not logged on"

Hope this helps fixing the bug,

Marcel.

 

[Marco Kalter]

Thanks. We'll try to reproduce.

 

[Marcel Hoefs]

Hi Marco,

Were you able to reproduce this problem and do you know when this will be fixed?

Today I accidentally used this option again in version 13.0.6.1911 and almost messed up a table. Data being imported into the source table so effectively duplicating the data instead of importing into the target table as I intended to do.

 

[Marco Kalter]

We have not yet been able to reproduce this.

 

[Dmitriy S.]

I have the same problem with PL/SQL Developer 11.0.6.1776 . I'm getting "Not connected to Oracle". I exported table with clobs, and clobs are greater than 32k in size. Please try to reproduce it with table with clobs greater than 32k in size and about 100,000 records in a table.

 

[Marko Sladoljev]

I have the same problem on version 13.0.6.1911 (64 bit)