Re: Import extent size problems

On Aug 23, 12:58 pm, stephen O'D <stephen.odonn..._at_gmail.com> wrote:
> On Aug 23, 8:43 pm, stephen O'D <stephen.odonn..._at_gmail.com> wrote:
>
>
>
>
>
> > On Aug 23, 5:08 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > On Aug 23, 10:40 am, stephen O'D <stephen.odonn..._at_gmail.com> wrote:
>
> > > > > Reading the documentation is always worthwhile:
>
> > > > >http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_im...
>
> > > > > Notice that compress defaults to y, and you're painfully aware of the
> > > > > consequences. You need to set this parameter to n on the command line
> > > > > or in your parfile:
>
> > > > > exp .../... file=.... compress=n .....
>
> > > > > This will prevent exp from 'resizing' the initial extent.
>
> > > > I know what compress = y/n does - but if my original create table
> > > > statement on the big database said
>
> > > > "create table x .... storage initial 80M .... "
>
> > > > Then will export not generate the DDL to have the same initial extent
> > > > size if compress is N?
>
> > > You might want to run:
>
> > > set long 50000
> > > select dbms_metadata.get_ddl('TABLE','<table name here>')
> > > from dual;
>
> > > to see what Oracle believes the storage parameters to be. You might
> > > be surprised at what you find.
>
> > > David Fitzjarrell
>
> > OK, I ran the sql you suggested and the end of the output is:
>
> > ...
> > STORAGE(INITIAL 67108864 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS
> > 2147483645
>
> > So my storage clause is coming up as ~67Meg. If that size comes up in
> > the export file, when I import it into locally managed tablespace with
> > uniform extents of 64K will that table, when empty consume 67M or 64K
> > on Oracle 10g?
>
> I tested this again - compress is set to N for sure. The table that
> reported 67Meg initial extent size using DBMS_Metadata consumes 67Meg
> when I import it. It seems Oracle just ignores the uniform extent if
> you specify one that is bigger in your DDL.
>
> Is there any way to override this, or am I going to have to manually
> (or via a script) adjust the storage parameters of every table before
> exporting

Look at the exp doc again. If compress=y, it makes a big extent. If compress=n it uses the storage parameters of each table. Either precreate the tables with the proper initial, or adjust the tables before export.

Note that the imp command INDEXFILE allows you to get the create table statements that you can modify to precreate the tables.

Uniform extents are uniform, Oracle doesn't ignore them, but may use a whole lot of them because of the DDL initial being large.

jg

--
@home.com is bogus.
Rich Niemiec's 10gR2 book:  http://www.amazon.com/Oracle-Database-Performance-Tuning-Techniques/dp/0072263059
"Customers viewing this page may be interested in these Sponsored
Links
Advanced Finger Cymbals
www.ansuya.com    Zill Drills with Ansuya Movement Demonstration &
Technique"