Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: export & import FULL =Y

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 2 Aug 2007 08:56:35 -0400
Message-ID: <02C2FA1C9961934BB6D16DE35707B27B03332642@jax-mbh-01.jax.crowley.com>


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).  

Drpmyobj2.sql  

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
joel.patterson_at_crowley.com
x72546
904 727-2546


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Godwin vincent Sent: Thursday, August 02, 2007 12:10 AM To: oracle-l_at_freelists.org
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.  

Thanks,

Godwin.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 02 2007 - 07:56:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US