This is a complete process commonly used for database backups or local data validation. Typically, I perform these operations through the command prompt (DOS black window). Here’s a simple record of the process—after all, writing it down is better than relying on memory!
Oracle Export and Import Overview
The imp/exp commands in Oracle are equivalent to data restoration and backup. Using these features, we can create two identical databases: one for production and one for testing. Commonly, we export data from the server to test it locally, identify issues, and make corrections.
The imp command allows you to import a local .dmp file into the corresponding database, whether local or remote.
The exp command allows you to export data from a remote server to your local system, saving it as a .dmp file in a designated folder.
How to Export Tables from Oracle and Import Them into Your Local Database?
Here’s the complete process to export tables from a project and import them into your database. This method is useful for database backups or validating data locally.
Step 1: Open cmd;
Step 2: Syntax: exp username/password@service_name tables=table_name file=local_dmp_target_path log=local_log_target_path owner=username_to_export
Export a table:
exp test/test@orcl tables=sys_user file=D:/user.dmp log=D:/user.log
Export a database: (Just remove "tables=table_name")
exp test/test@orcl file=D:/user.dmp log=D:/user.log
Export the database of the specified user:
exp test/test@orcl file=D:/user.dmp log=D:/user.log owner=test
How to import a table into a local Oracle database?
Step 1: Open cmd;
Step 2: Enter:
imp username/password BUFFER=64000 file=path_to_database_table.dmp ignore=y full=y
Explanation:
ignore=y means ignoring creation errors and continuing with the following operations;
full=y means importing all content from the file, which might include content from multiple users. If the default tablespace for the user you're connected with is "system," the content from the import file will be imported into the "system" tablespace.
Example:
imp test/test BUFFER=64000 file=D:/user.dmp ignore=y full=y
