Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: export & import FULL =Y

RE: export & import FULL =Y

From: Peter McLarty <>
Date: Thu, 2 Aug 2007 15:18:44 +1000
Message-ID: <>

Hi Godwin
Ok firstly doing a full import will not import SYS, SYSTEM etc.  

How to clean the data there is a couple of other possibilities, you could have a look at what schemas that you need to import have data and then use the fromuser and touser functionality to import those to different schemas as a waypoint and then clean that data.  

If you want to merge the data you have into the dev depending on how much of which database you want to retain you could copy certain tables to backup tables in your development database truncate all the tables that will be imported and then import that data and then copy the prior saved data back and any duplicates will be restored, with teh appropriate process you could even place teh duplicates into a seperate table if you really need to.  

If you data you have from prod has duplicates I would still import with fromuser and touser to move it to interim schemas and then run SQL to remove those duplicates, export that back and reimport it to the correct schemas.  

If you need to find all the schemas then run imp show=y and this runs a dummy import without actually doing it and will provide you with what it will attempt to import  

HTH   Cheers  


From: Godwin vincent [] Sent: Thursday, 2 August 2007 02:10 PM
Subject: export & import FULL =Y

Hi all,

           I want to refresh a production database onto development machine using EXP and IMP. My prod. database is of very small size of approximately 4GB. I can get a full backup of the database using EXP by using " exp system/manager file=exp_full.dmp full=y ignore=y ". My main problem here is that my development database has already some data and i want to clean all the data before doing an import from the production, orelse i would be suffering with the duplication and hell lot of errors with the constraints. I would like to ask you as what are the different ways i could use to clean the database before a fresh import. One option would be to drop all the schemas and start the import. In this scenario, how can i find all the shemas, which does not include the default schemas that are created at the database creation time. For example: SYS, SYSTEM, DBSNMP, OUTLN, DIP, TSMSYS are created by default. I do not want to drop these schemas as it might effect the database. Please advise.  

Any information will be of a great help.  


Received on Thu Aug 02 2007 - 00:18:44 CDT

Original text of this message