Well there's two answers that I'll summarize:
1) How to solve my problem using a UNIX solution:
* Use emacs. It will work. I didn't use it because of the more
conventional and flexible method
available through Oracle export/import.
2) How to solve my problem using an Oracle solution:
* Create an indexfile using Oracle's import utility with the indexfile
option.
Instead of the huge data file (the export file) being sourced, which
contains all of the user's objects
definitions AND all of the associated rows of data, an indexfile is created
which contains only the DDL,
or definitions of the user's objects, without data. THIS indexfile can then
be easily modified, mostly with
global string replacements to change the tablespaces to the desired one(s)
and any other object parameters
that you wish to change, like pctincrease from 50 to 1.
* Import the modified indexfile, creating the user's objects, albeit
void of any data/rows.
* Import the full export file with the full=y and ignore=y options
loading the objects/segments with their rows.
* Repeat the full import as needed due to the import order and
dependencies causing some objects not to be created.
This is the method that I used.
The indexfile option was originally provided by Oracle as a method
to separate segments containing
data (tables) from those containing indexes into different
tablespaces. Ironically, I have used this option,
but it's been about a year, and it slipped my mind.
Among the other viable responses were:
UNIX:
- Use Perl
- Use Hexedit
-
Oracle:
- Creating the tables in the new database as a select from the old
database table,
changing the tablespace option. I would have used this, except it
means I type more.
- Giving the user no quota on the SYSTEM tablespace, performing the
import after changing the user's default tablespace
to the desired one, causing it's objects to be imported into the
desired tablespace. Never actually tried this method, but
it should work. The only drawback is you still retain all of the
other parameters on the objects, such as the pctincrease and
the indexes that incorrectly resided in the same tablespace as
their associated tables don't get moved. Of course, I did add in these
required changes after seeing the indexfile, so to simply change
tablespaces, it should work fine.
Thanks to:
Joseph Thvedt - Who nailed it for me - Thanks for the reminder on
the indexfile option!
John Venier
David DeWolfe
Eric Sisson
Andy Cohen
J.A. Gutierrez
Cy Dewhurst
Jeff Berliner
Bob Vickers - Who even sent me a Perl script for editing binaries -
how nice is that ?!
Scott Yelich
Marie-Claude Vialatte
Dr. Thomas Blinn - The answer man!
Joe Ledesma
-----Original Message-----
From: Sweatt, Wayne [SMTP:sweatt_at_dps.state.nm.us]
Sent: Monday, February 26, 2001 7:51 PM
To: Tru64-UNIX-Managers
Subject: What is the best way to replace strings in binary
files?
Dear DUGurus,
I haven't really tried to do this before, but would like to replace
a
string in a binary file.
What is the best tool to use for this?
Just for the curious, and Oracle DBA types:
I was going to do something unconventional and change the alpha
string in an
Oracle export
file that denotes the tablespace name and change it from "SYSTEM" to
"USERS".
Looks like sed won't do it.
I know of the traditional method of changing tablespaces by dropping
the old
one, recreating
the table in the desired tablespace and then importing the table
with
ignore=y...BUT, this
seems so much simpler to do, since I could actually perform this on
multiple
tables at one whack.
I could do a user export, change all of the 'TABLESPACE "SYSTEM"'
strings to
call out the
desired "USERS" tablespace and be done with it. Should work , right?
Thanks!
Wayne
Received on Thu Mar 01 2001 - 00:53:31 NZDT