Posts Tagged ‘expdp’

A quick sharing….

Last few days bask we were have to migrate a schema from 10g R2 on Solaris 10 64 bit to 11g R2  on windows 32 bit using traditional exp/imp. Exp was successful (as usual), but while importing we were facing below error.

Connected to: Oracle Database 11g Express Edition Release – Production

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
. importing USER1’s objects into USER2

ORA-01435: user does not exist
Import terminated successfully with warnings.

we were using below syntax…..

imp system/oracle@DB_NAME file=’c:\dumpfile.dmp’ log=c:\logfile.log grants=n fromuser=<Schema_from>touser=<Schema_to>;

We were facing this issue from last 3 days, finally found the cause of the problem(A silly mistake):

The above command is working as a cmd command other than sql/plsql command so we should not use “;” at the end of the syntax.


imp system/oracle@***** file=’c:\dumpfile.dmp’ log=c:\logfile.log grants=n fromuser=<Schema_from> touser=<Schema_to>

this will sort out the error…..

Hope this helps..

Thanks & Regards

Nimai Karmakar


Read Full Post »

Schema refresh is an regular job for any DBA specially during migration 
projects, so today I decide to post about a schema refresh
Assuming here schema(SCOTT) is  refreshed  from source(PRODDB) to Target(TEST) 
on oracle 10g server

SQL>  select * from  v$version;

On Source side (PRODDB) ,
Create a directory or use an existing directory (ex: data_pump_dir) 
and grant read and write permissions on this directory to user ‘SYSTEM‘

SQL >   grant  read, write  on  directory  data_pump_dir  to   system;
Grant Succeeded.

Step 1:   Exporting the data from proddb(source) 

$   vi   expdp_refresh_schema.sh

$  expdp  system/****@sourcehostname   dumpfile=expdpschema.dmp  
   Directory=data_pump_dir    logfile=export.log   schemas= scott

$  nohup  sh  expdp_refresh_schema.sh>refresh_schema.out &

Step 2 :  Copying the dumpfiles from source to target

For copying Dumpfiles from one server to another server we can use either 
Winscp(Graphical tool for copying files from windows to linux and  vice versa)

$ scp  expdpschema.dmp   system@TargetHostname:/home/oracle/datapump

Here I am copying dumpfile from source to the target /home/oracle/datapump 

Step 3 :  Importing data from dumpfile into target database

Before importing dunpfile into target(TEST) make sure you delete or backup all 
the objects in that schema, to clear all objects from particular schema run 
the script from here  

$ impdp  system/****@targethostname   dumpfile=expdpschema.dmp   
Directory=data_pump_dir    logfile=import.log   remap_schema= scott:newscott

Step 4 :   Verify target database objects with source

SQL>   select   count(*)  from  dba_objects   where  owner=’NEWSCOTT’ ;
SQL>   select  count(*)  from  dba_tables  where  owner =’NEWSCOTT’;

The above results  should be same as that of source  ‘scott’  schema

Read Full Post »

Bitbach's Blog

Just another Oracle weblog @ WordPress

Daniel Westermann's Blog

simplicity...that's it

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles


Few adventures in my slow running life...


4 out of 5 dentists recommend this WordPress.com site


Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World