Oracle IMP EXP Amazon Relational Database Service (RDS)

Oracle IMP & EXP

Oracle imp exp. Import and export tools imp/exp are sometimes your only options when using Amazons Oracle RDS service. The reason for this is due to the file system on the database server being unavailable to you. The scenario where you would normally use impdp and expdp  to create a dumpfile and move it between servers is no longer a straight forward option. Using Oracle exp and imp has serious limitations such as the inability to remap schemas while importing along with difficult options to exclude certain tables while exporting. If you have administrator access to the Amazon environment where the database service is running and you can open up access to the database from another server then I have written a post here on Network Link Oracle AWS RDS.

However some of the environments that we work with are not owned or managed by our company and access to servers , firewall rules and load balancers is not an option. In this case we just have to get by with whats available. That's where the old school  imp and exp come in to the mix.

Using Oracle IMP EXP To Import and Extract

The first thing that needs to be done is to Allocate Extent on all existing tables in the schema prior to export. The reason for this is that when using the standard oracle imp exp tools any table found to have zero rows of data will be ignored and not added to the dmp file.
This can be done with the below command via sqlplus. The below sql statement will firstly set the pagesize to 0. This is needed to stop headers from being displayed in the output. Secondly it will spool the contents of the query to a file. The query will return the needed statement to run on all tables in order to allocate extent. Finally the spooled file will be called in order to run the allocate extent command on all tables.
set pagesize 0
spool allocate-extent.sql
SELECT 'ALTER TABLE '||table_name||' ALLOCATE EXTENT;' FROM user_tables WHERE segment_created = 'NO';
spool off
@allocate-extent.sql

To do the exp or imp you now need to run the commands on a server that is able to create a connection via tns to the database server.
When using an  ssh terminal session via a  program such as Linux terminal or Putty I recommend using the nohub option as below. The reason for this is to ensure that the command is pushed into the background as a process and is not dependant on the terminal session being open. Network issues or a time-out could kill you export before its finished.
Command to export:
nohup exp scott/password@production file=production.dmp &
Command to import:
nohup imp scott/password@production file=production.dmp &
You will not have any output returned to the screen as the job is now detached from the terminal. To see the output you can view the nohup.out file that is created in the directory from where you invoked the imp/exp command.
$ tail -f nohup.out
Oracle documentation for original Import and Export:
https://docs.oracle.com/cd/E11882_01/server.112/e22490/original_import.htm#SUTIL001
https://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#SUTIL3634