Re: Import extent size problems

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? Received on Thu Aug 23 2007 - 14:58:54 CDT