Re: Oracle 8i: how to do a full import to the same database?

From: Glen A Stromquist <glen_stromquist_at_no.spam.yahoo.com>
Date: Sun, 23 Feb 2003 18:21:17 GMT
Message-ID: <xi86a.6770$Ho6.699023@news2.telusplanet.net>

JustAnotherDBA wrote:
> "Bombadil" <bombadil_at_fastmail.fm> wrote in message
> news:eedb1e6a.0302230646.4d8a6242_at_posting.google.com...
>

>>Hello,
>>
>>we are running Oracle 8i (8.1.6) on Windows 2000 Server.
>>
>>I have a dump file that I got a while ago using "exp full=y ...".
>>I now want to import it back into the same database replacing the data
>>that is currently there.

>
>
> I assume you do not have hot or cold backups because recovering the entire
> database from a hot or cold backup would be a lot less trouble.
>
>

>>How do you do a full import to the same database?
>>Could someone please give me instructions as to how this can be
>>achieved?
>>
>>If I try to just run "imp full=y ..." on the existing database, I
>>think I will get lots of errors because the tables, records,
>>contraints are already in the db.
>>

>
>
> Yep. If you are really going to do this, first backup what you have.
>
> Make sure you did not create anything while connected as internal ( / as
> sysdba) or SYS. If so, save this off before the next step.
>
> Then, delete the entire database and rerun the create database scripts that
> you saved . If you did not save them, then rerun the dbassistant or
> something to create the scripts and then edit them.
>
> Then, run the import against your new empty database and be prepared for
> something to fail. There is always something you can't imagine will happen
> that will happen. For, example any database links to databases that are down
> while you do the import will not get created and so any Plsql relying on
> these database links will fail to compile or in some cases fail to import.
>
> Good luck.
>
>

>>Any help would be appreciated.
>>
>>Thanks,
>>Bombadil.

>
>
>
>

What I do in this situation is either drop the users whose data I want to import, or use a script I have written that goes and disables all constraints and triggers, then truncates all tables belonging to users other than sys or system, performs the import, then re-enables all triggers and constraints. Once you have the script written (to generate and run the actual disable, enable, and truncate scripts), it's quite easy to use it on any other database if need be.

A sample of the script that generates the sql to disable the triggers is   below, its quite easy to edit it to do the constraints, truncates, and enable scripts as well...

---

column sqlline newline
set pages 0
set lines 132
set feed off

set termout off

spool <<script_name.sql>>

select 'spool <<path>>\disable_trigs.LOG' from dual /

select 'set echo off;' sqlline

       ,'set feed on;' sqlline
from dual;

select ' ' 			sqlline
       ,'prompt ' 		sqlline
       ,'prompt  '||decode(upper('Y'),'N','Enable','Y','Disable','Do 
nothing to')||
                 ' triggers table: '||table_owner||'.'||table_name||' 
trigger name: '||trigger_name||'...'  sqlline
       ,'prompt ' 		sqlline
       ,'alter trigger 

'||OWNER||'.'||trigger_name||decode(upper('Y'),'N',' Enable','Y',' Disable',' **ERROR**')||';' sqlline
from sys.dba_triggers
where owner NOT IN ('SYS','SYSTEM')
order by table_name
/

---

In the end though it is probably easier to to a simple drop user(s) before your import.

Also keep in mind that if you drop the tablespaces, your directory structure has to remain the same if you want your import to work, if you want to move the datafiles before the import, recreate the tablespaces with the same name and the datafiles in their new location before the import.

hth Received on Sun Feb 23 2003 - 12:21:17 CST