RE: export & import FULL =Y
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <godwin.ror@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Aug 2007 08:56:35 -0400
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.
Other related posts:
