Re: Import extent size problems

Comments embedded.
On Aug 23, 2: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.

Apparently this table was exported with compress=y, dropped and imported from that dmp file. At this point having compress=n only keeps Oracle from making that even larger.

> 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?- Hide quoted text -
>
> - Show quoted text -

67 M, or thereabouts, as it will consume as many 64K extents as necessary to achieve that size. Howard has explained this well.

David Fitzjarrell Received on Fri Aug 24 2007 - 08:01:26 CDT