Export/import Oracle database objects and Data

In Oracle, we can export data from the remote database server together with the object definition to a local DMP file, or import the DMP file from the local to a distant database server. This feature allows you to build several identical databases for testing. Of course, the condition is that you have installed the Oracle client on the local machine.

We can use the doscommand line or sqlplus to execute the operation. Of course, the import/export work is actually completed by exp.exeand imp.exe in the Oracle installation directory \ bin.

The following statements are used to import/export statements (username, password, servicename, and filename are user names, passwords, and local service names and file names respectively)

• Data Export
  1. Export the database content completely: EXP username/password @ servicename file = filename full = y
  2. Export the specified database content: EXP username/password @ servicename file = filename owner = (user1, user2). The export result is all objects and data of user1 and user2. It should be noted that if username is a common user and the owner = (user1, user2) is removed, the exported result is all objects and data of username.
  3. Export the table structure and table data separately: EXP username/password @ servicename file = filename tables = (Table1, table2). The table structure and data of username users Table1 and Table2 are exported.
  4. Data Compression: Add compress = y at the end of the preceding statement.
• Data Import
  1. Import to users with the same name (and password): IMP username/password @ servicename file = filename
  2. Import to other users: IMP username/password @ servicename file = filename fromuser = username touser = username2

Well, username must have sufficient permissions. Otherwise, Oracle will prompt you.