FreeLists / oracle-l / RE: export & import FULL =Y

[<Joel.Patterson@xxxxxxxxxxx>]

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@xxxxxxxxxxx 
x72546 
904  727-2546 

________________________________

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