Search code examples
oracle-databasedumpplsqldeveloper

how to export schema and import it to another schema in PL\SQL Developer

I am using PL\SQL Developer by AllroundAutomations. My task is to import schema named EN to another schema (it doesn't exist now) called E9.

I managed to make a dump of a scheme using Tools --> Export User Objects, so now I have EN.sql file

So, here are my questions?

  1. How can I make EN.dump file?
  2. To import it to another scheme, I need firstly to create new (E9) scheme from sysdba user?
  3. And is there any opportunity to import scheme from PL\SQL Developer interface? For some reason I can't connect to sqlplus which makes things worse.

Thank you in advance.

update: i just reinstalled my instant client from ver 11_2 to 12_2 with the tools and sql*plus

Solution

  • PL/SQL Developer has tools Export Tables and Import Tables which can import/export dmp files using EXP and IMP utilites. See PL/SQL Developer's help:

    Export Tables:

    The Export Tables tool allows you to export one or more table definitions and their data into a file, so that you can import the tables later. After starting the Export Tables tool, you can select the user and the tables you wish to export, choose an export method (Oracle Export, SQL Inserts, or PL/SQL Developer), and set various options that apply to the export method...

    enter image description here

    Import Tables:

    The Import Tables tool allows you to import table definitions and data from a file that was previously exported with the Export Tables tool described in the previous chapter. Just like with the Export Table tool, there are 3 methods to import tables, each with its own file format...

    enter image description here

    P.S. As you see the schema where you want to import to must already exist.

    But in such a way you can export/import only tables. So if you want export the whole schema use a utility through the command line, see example:

    1. Command to export a schema:

      exp userid=dba/dbapassword OWNER=username DIRECT=Y FILE=filename.dmp

      This will create the export dump file.

    2. To import the dump file into a different user schema, first create the new user in SQLPLUS:

      SQL> create user newuser identified by 'password' quota unlimited users;

    3. Then import the data:

      imp userid=dba/dbapassword FILE=filename.dmp FROMUSER=username TOUSER=newusername