Importing Database Dump into Oracle - For local Development Box

One of the most important steps to have a healthy and accurate development and unit testing on local machines is to have most accurate data as similar as the data on TEST or Stage environments. Importing the data on to local database installations has be a regular activity preferably once for iteration or a release. 

This section explains the process of importing the oracle data dumps on to the local environment.
Assumptions: Data has been exported into .dmp format with all the tables in it, well yes some of  PII data need not be dumped as is, but should be altered to make all passwords the same, email id to be changed and address information to be changed to read same values. Alternatively have only non PII data dump or use no rows option when dumping PII tables. This article talks about approaches of importing a full or clean dump and a partial dump.

First lets look at the partial dump, this is the most easy of all and single command will be able to fulfill the need.

Step 1: Identify the directory where you want the dump file to reside.               
                      select * from ALL_DIRECTORIES; 
This will give you the list of all directories mapped in Oracle. Identify the directory used by your schema. Copy the .dmp file to the directory_path location on your system.

Step 2: Run the impdp utility.

                        impdp system/<password> directory=<directory name from step1 query> dumpfile=<dump file name> logfile=<log file name> PARALLEL=8 CONTENT=ALL TABLE_EXISTS=REPLACE

Here TABLE_EXISTS=REPLACE, will replace your local table with that one from dump.

This will take some time depending on the size of the data being imported.

Now to do a clean import or to create a new schema, following steps would be required.

Step 1: Collect the required information

• Directory to dump the file. Your dump file should in path returned by the query.

              select * from ALL_DIRECTORIES  -- get the directory name and path

• Get the tablespace name used by your schema. Following queries will help you identify the tablespace used by your schema (schema is same as the user name in oracle)

              Select tablespace_name from dba_tablespaces;  -- This will fetch all table spaces used in Oracle.

               Select tablespace_name from all_table where table_name = '<your table name>'; -- This will fetch the table spaces used by your specific table.

• Get the datafile location on the filesystem (although you won't be able to use the same tablespace file name again, but you can use the same path. Once you have successfully imported the database you can delete the old files)

                  select tablespace_name,file_name from dba_data_files;

 From the above queries get the tablespace name for your data and index, if proper naming convention has been used, it could be something like <schemanameDATA> and <schemanameINDX>.

Step 2: Drop tablespaces

            drop tablespace <schemanameDATA> including contents and datafiles;

            drop tablescpace <schemanameINDX> including contents and datafiles;

Step 3: Create the Tablespaces (Datafile location based on step 1, third query)

          create tablespace <tablespace name for data> datafile '<DATAFILE NAME INCLUDING LOCATION>' size           2g reuse autoextend on;
          create tablespace <tablespace name for index> datafile '<DATAFILE NAME INCLUDING LOCATION>' size 2g reuse autoextend on;


Set the size as you require, 2g is 2 Giga bytes.

Step 4: Drop the user
           drop user <schemaname/username> cascade;

Step 5: Create the user and required permissions
           create user <schemaname> identified by <password> default tablespace <tablespace name for data> temporary tablespace temp quota unlimited on <tablespace name for data> quota unlimited 
 on <tablespace name for index>

           grant connect,resource,create materialized view to <schema name>;

           grant connect,resource,create materialized view to comusr

           grant create view to <schema name>;

           grant create synonym to <schema name>;

Step 6: Now logout of the sqlplus. At the command prompt run the following command

          impdp <oracle_admin_user>/<password> full=Y directory=<directory name> dumpfile=<dumpfilename> logfile=<logfilename> REMAP_SCHEMA=<schema name of the export>:<schema name of import> CONTENT=ALL
             
          Directory Name - is the name identified in Step 1, first query

          REMAP_SCHEMA - is required if the original schema of the export is not the same as the schema into which you are importing.

If you need to create a new schema, you will follow Step 1, Step 3, Step 5, Step 6.

Note: Please ensure that your extracted dump file has entire schema, if any of the tables are excluded during the data dump creation, your database will have those table missing.