-----Original Message-----
From: joachim.uppenberg@seinf.mail.abb.com [mailto:joachim.uppenberg@seinf.mail.abb.com]
Sent: Friday, August 07, 1998 8:49 AM
To: akaplan@interaccess.com
Subject: Import tables with smaller initial extent info
Hey, you seems to be a Hero in der Oracle gebit.
Here is a tricky one. Oracle version 7.3.4
How to import tabels without the data and the initial extent information.
I don't want huge tables with no data. Iwant small ones with no data.
I think I have tried every possible option on the import and export
command.
I have managed to get huge tables without data. But how to shrink'em.
If it's not possible with the imp/exp utility do you know some workaround.
Please, help.
regards
//Joachim
----------- Reply ----------------
Joachim,
There are a few things you can do for your imp/exp question. First, you can
export with COMPRESS=N. By default, COMPRESS=Y, which takes all of your tables
add combines all extents into one big extent. However, your imported tables will
still be the size of each INITIAL extent from when it was exported.
So, if your original INITIAL extent sizes are too big for the empty tables, you
will have to go outside of export/import. The easiest way is to use a
third-party tool such as Platinum's TSREORG. You can change the INITIAL/NEXT
pretty much on the fly.
If you do not have a third-party tool for this, then you will have to drop and
recreate each table with smaller extents. This is a pain because you also have
to recreate indexes, triggers, constraints, comments, and grants, as well as
recompiling procedures/packages. You can either reverse-engineer the CREATE
statements with an ER tool. The other way to do it is "IMP username/password
SHOW=Y LOG=create_all.sql", which will generate all CREATE statements into the
"create_all.sql" file. The only problem is that Oracle cuts lines in half and
adds quotation marks, etc. You will then also have to modify the INITIAL extent
of each CREATE statement to the size you desire. The advantage of using IMP to
do this is that it also generates the GRANTs and constraints. I've done it
before and it's work, but it does the job.
Best of luck,
-Ari Kaplan
akaplan@interaccess.com
www.arikaplan.com
Back to Ari Kaplan's Home Page