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: William Wagman <>
Date: Thu, 2 Aug 2007 08:38:45 -0700
Message-ID: <>

Why not just recreate the database? Shut it down, remove all the database files and run the DBCA or stored scripts then run the import. That way you are sure to have a clean new database. If you are dropping all the schemas you aren't saving anything anyway. It doesn't take that long to create a new database, especially a small one.  

Another possibility, if you are using RMAN to backup the primary database just use it to clone a copy. That is quite easy to do also.  

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
(530) 754-6208  

[] On Behalf Of
Sent: Thursday, August 02, 2007 5:57 AM
To:; Subject: RE: export & import FULL =Y

You can also drop all the objects first with drpmyobj2.sql shown below by logging into each account. Of course you could modify it to select from dba_objects and drop everyone you select from a DBA account. Purge the recyclebin first if on oracle10g. (note: script does not bother with materialized views).  


set echo off

set heading off

set pause off

set termout on

set feedback off

set pagesize 1000

spool drpmyobj2.tmp

prompt spool drpmyobj2.lis

prompt prompt ... Starting drpmyobj2.tmp ...  

select 'drop '||object_type||' "'||object_name||'"'||

      decode(object_type,'TABLE',' cascade constraints;',';')

   from user_objects

   where object_type not in ('INDEX','TRIGGER','PACKAGE BODY','LOB')

 order by object_id;  

set term off

prompt prompt

prompt prompt


prompt prompt OUTPUT SPOOLED TO <working directory>\drpmyobj2.lis

prompt prompt


prompt prompt

set term on

prompt spool off

spool off  

set heading on

set feedback on

#PROMPT ***************************************************

#PROMPT * Drop Object script is drpmyobj2.tmp *

#PROMPT * Review/Modify before Executing *

#PROMPT ***************************************************
  Joel Patterson
Database Administrator
904 727-2546

[] On Behalf Of Godwin vincent Sent: Thursday, August 02, 2007 12:10 AM To:
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 - 10:38:45 CDT

Original text of this message