Re: initial value on create table/index statement

From: DEV <sdpant_at_gmail.com>
Date: Mon, 29 Jun 2009 10:30:54 -0700 (PDT)
Message-ID: <77b98a71-b0ed-4214-b1fa-d9638ce55a38_at_x5g2000yqk.googlegroups.com>



On Jun 29, 6:32 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Jun 26, 5:52 pm, DEV <sdp..._at_gmail.com> wrote:
>
>
>
> > On Jun 26, 2:46 pm, DEV <sdp..._at_gmail.com> wrote:
>
> > > On Jun 26, 2:25 pm, John Hurley <johnbhur..._at_sbcglobal.net> wrote:
>
> > > > On Jun 26, 5:18 pm, DEV <sdp..._at_gmail.com> wrote:
>
> > > > > Is there a way to change the value of "inital" in "create table" or
> > > > > "create index" during imp?  Pre-creating table is not an option here.
> > > > > I am taking a export dump with rows=n from production and importing it
> > > > > into dev/QA and since the production tables/indexes have larger values
> > > > > for "inital", it takes a lot of storage.  Is there a way to create
> > > > > tables/indexes with smaller inital values during import?
> > > > > Thanks,
> > > > > Dev
>
> > > > What oracle versions are you going to/from in the export/import
> > > > process?
>
> > > > How are the relevant tablespaces defined in the to/from process?
>
> > > Both are on same version (10.2.0.3) and defined the same way.
> > > EXTENT MANAGEMENT LOCAL AUTOALLOCATE
> > > BLOCKSIZE 8K
> > > SEGMENT SPACE MANAGEMENT AUTO
>
> > Sorry for the upper case on my last post.  I wasn't shouting.  It was
> > due to copy/paste...- Hide quoted text -
>
> > - Show quoted text -
>
> You might consider just performing alter table move and alter index
> rebuild commands on the empty tables after the import.  You can reset
> the initial extent allocation value on moves and rebuilds.  The
> commands are easy to generate.  As long as you have enough space to
> handle the initial allocation this should work.
>
> Then I would notify the exporter that on all future exports you want
> compress=n set.
>
> HTH -- Mark D Powell --

import has to be automated, so I can't edit the indexfile. Fortunately I have enough space to do the inital import and I have scheduled a daily job to do something like below, which is giving back the space for reuse and which is what I wanted. alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;
Thanks Phil and Mark for your suggestions. Dev Received on Mon Jun 29 2009 - 12:30:54 CDT

Original text of this message