What is export and import in Oracle?

Export and Import are two utilities in Oracle database used to extract data from database into a single file, and also extract data from that single file into a database. We normally call that file as dump file. It is widely used during backup and transfer data from one database to another.

1. Use command below to show out the parameters that can be used in Export:
   exp help=y
2. To export everything in database:
   exp [username]/[password]@[database] full=y file=[dump file] log=[log file]
3. To export everything in one single schema:
   exp [username]/[password]@[database] owner=[schema] file=[dump file] log=[log file]
4. To export particular tables in database:
   exp [username]/[password]@[database] tables=([table],[table]) file=[dump file] log=[log file]
5. To show parameters that can be used in Import:
   imp help=y
6. To import everything in a dump file:
   imp [username]/[password]@[database] full=y file=[dump file] log=[log file]
7. To import particular schema in a dump file:
   imp [username]/[password]@[database] fromuser=[schema in dump file] touser=[schema in db] file=[dump file] log=[log file]
8. To import particular tables in dump file:
   imp [username]/[password]@[database] tables=([table],[table]) file=[dump file] log=[log file]
9. To import table structure only, add the following parameter:
   rows=n
10. To import table (or other objects) that already exist in database, add following parameter:
    ignore=y
11. Normally import the whole database cannot be done in one shot, need to import objects of different type one by one:
    imp [username]/[password]@[database] full=y file=[dump file] log=[log file] ignore=y rows=y constraints=n indexes=n grants=n
imp [username]/[password]@[database] full=y file=[dump file] log=[log file] ignore=y rows=n constraints=y indexes=n grants=n
imp [username]/[password]@[database] full=y file=[dump file] log=[log file] ignore=y rows=n constraints=n indexes=y grants=n
imp [username]/[password]@[database] full=y file=[dump file] log=[log file] ignore=y rows=n constraints=n indexes=n grants=y