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
Tags:
-----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 

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

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

neinframe

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 --

DRK™

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

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: