Oracle, Web, Script, SQLserver, Tips & Trick

December 6, 2011

How to import tabels without the data and the initial extent information

Filed under: oracle — sikathabis @ 9:24 am
-----Original Message-----
From: []
Sent:	Friday, August 07, 1998 8:49 AM
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
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.


----------- Reply ----------------


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 

for example by sikathabis
exp dbadmin/dbadmin@DB10g owner=dbadmin rows=N compress=N file=e:\norows_DB10g.dmp

and then import like this
imp dbadmin/dbadmin@DB10g fromuser=dbadmin touser=dbadmin ignore=y statistics=none file=norows_DB10g.dmp

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at



Learning is not a spectator sport

Connor McDonald's Oracle blog

Teknologi Pendidikan

Just another weblog


Jurnalisme Yang Dapat Dipercaya

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server


"A distance is getting far if your heart is impatient" -ean-


Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --


This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa


Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku


Ceritanya saya, Pikirannya saya, Hatinya saya

%d bloggers like this: