Datapump through Network & ORA-39083

Hi,
Many times we make use of datapump utilities for loading the data from one schema to other schema.We make use of REMAP_SCHEMA parameter provided by the impdp utility but for this we need the dumpfile which is only available when you do the export by using expdp utility,In the below example I have done the import without using dumpfile by using parameter NETWORK_LINK
provided by impdp utility,this is very useful parameter.

When I was doing this import I came across one error
ORA-39083 this usually occurs when OID already exists used by user defined objects type,We can overcome this error by using parameter TRANSFORM=oid:n in oracle 10g rel2 Database(In 10g rel1 we have to recreate type objects).

The steps involved in doing this import is as given below:


Source user:Source_SCHEMA
Target user:Target_SCHEMA

Step 1:
--------

Tablespace check:
-----------------

SELECT F.TABLESPACE_NAME,TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USEDMB",
TO_CHAR (F.FREE_SPACE, '999,999') "FREEMB",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTALMB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME ;

Note:
Source side total space used =64 mb
Target side free space available=2048 mb
tablespace space available to proceed with import


Step 2:
--------
Create directory for dump file.

create directory IMP_DP_TEST as 'E:\TEST_DIRECTORY';
grant read,write on directory IMP_DP_TEST to system;


Step 3: Create database link from the target schema and connect to the source schema
--------

create public database link LINK_NAME
connect to Source_SCHEMA
identified by PASSWD
using 'CONNECT_STRING'
/
TO verify this database link is working fine:
Target side:Target_SCHEMA

SQL> select *from dual@LINK_NAME;
o/p:
---
x

Step 3:
----------
Use REMAP_SCHEMA to load data from one schema to other schema.
Use TRANSFORM=oid:n to replace old oid with new.
Use NETWORK_LINK to do import through network with the help of Database link you have
created.

impdp Source_SCHEMA/PASSWD REMAP_SCHEMA=Source_SCHEMA:Target_SCHEMA TRANSFORM=oid:n DIRECTORY=IMP_DP_TEST NETWORK_LINK=LINK_NAME LOGFILE=testdata5_29122010.log


Step 4:
--------
Compare the two schemas and verify by using the below queries:

Note The source and target objects(tables,indexes,procedures,functions,packages) should be equal.

SQL>Select count(*) from user_objects;
SQL>Select count(*) from user_tables;

Step5: Once you completed your import it is good practise to compile all your objects
------
For compiling the objects

In Windows: Connect as sysdba
-----------
SQL>@%ORACLE_HOME%/rdbms/admin/utlrp.sql

In Unix:
--------
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Now verify whether there are any invalid objects in the target schema by using the below query:

SQL>select object_name||' '||object_type from user_objects where status='INVALID';

If any invalid objects exists compile them individually

eg:
SQL>alter procedure procedure_name compile;


Hope this help.



Best regards,

Rafi.