Re: Editing export dump files

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Tue, 11 Aug 1998 14:28:46 GMT
Message-ID: <01bdc544$029af680$a504fa80@mndnet>

Haakon,

This is from one of my previous posts. Although it does not address every step that you need to take, it does address few of them.

I am sure that you know how to create a script for trunacting all the tables.

select 'truncate table '||table_name||';' from all_tables where ........;

Good luck !!!

First export the specific tables to a dump file such as suresh.dmp under dba account such as ops$dba.

exp parfile=export_tables_parfile.txt

The content of this parfile, export_tables_parfile.txt is:

userid  = /
file    =/r1/export/export.dmp
rows    =n
log     =$HOME/log/export.log
tables  =(
	   table1,
	   table2
	 )
____________________________________________________________________________

___

Then import the tables. This import will ceate only the script file, import_script.sql. THE STRUCTURES AND DATA WILL NOT BE IMPORTED. Trust me, I have checked it out personally.

imp parfile=import_tables_parfile1.txt

Contents of the import_tables_parfile1.txt is given below. The file, import_script.sql will contain the SQL script for tables (table1, table2 in this case), their indexes, constraints, grants, triggers etc. with appropriate storage parameters. Alter this script to suit your needs and use it to create the tables/indexes/grants/constraints/triggers or any of the above in the appropriate tablespace for any user with your own storage parameters.

userid  = /
file    =/r1/export/export.dmp
log     =$HOME/log/import_script.sql
tables  =(
	   table1,
	   table2	
            )


----------------------------------------------------------------------------





----------------------------------------------

Drop the tables. Before dropping production tables, you may want to make sure that your export file,export.dmp is good by importing few tables in some account, ops$xyz, as given below.

---

---

Use the above altered script to create the tables in appropriate tablespace and/or user.

---

---

Then do the real import with the following script. You may want to add other import options to this script.
Find out about these option by entering: imp help = y.

imp parfile=import_tables_parfile2.txt

Contents of the import_tables_parfile2.txt is given below.

userid  = /
commit  =y
file    =/r1/export/export.dmp
fromuser=ops$dba
touser	= ops$xyz
ignore  =y
log     =$HOME/log/importing_in_new_tablespace.log
tables  =(
	   table1,
	   table2	
            )

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Haakon T Soenderland <haakonts_at_online.no> wrote in article <35D02264.7486A9FD_at_online.no>...

> For reasons beyond our control, someone did an 
> export/import of data which ended up stripping off
> the eighth bit.  The data contains international
> characters (Norway/Sweden/Denmark/France/Iceland...).
> 
> Thus characters in the 'upper-ascii' realm has been
> stripped of one bit, ending up as 'control characters',
> (ascii 01 through 32).
> 
> The data has been imported and new records have been 
> entered into the database.  The original dump file
> is therefore not of much use to us.
> 
> This is a fairly large system 150+ tables so changing 
> things using SQL or PL/SQL is our last resort.
> 
> I have been involved with a similar situation some years
> ago where we used the following solution:
> 
> 1. Shutdown database.
> 2. Export all data.
> 3. Edit the dump file using UNIX sed/awk or similar
>    exchanging known characters for the correct 8 bit
>    equivalent.  Some characters will be lost.  The
>    most relevant for us will remain (nordic characters).
> 4. Truncate all tables, disable constraints/indexes etc.
> 5. Import edited data
> 6. Enable constraints etc.
> 
> I know that this can be done.  I don't however have the 
> Unix scripts (or C-program) to do it.  So for purposes of
> saving some time, I wonder of someone have such scripts
> available or know where I can get them? (URL.?)
> 
> Database is 7.2.2 on Netware.
> (and, yes I know that his is unsupported, but in desperate 
>  times.. etc.).
> 
> Many thanks,
> Haakon
> 

Received on Tue Aug 11 1998 - 09:28:46 CDT