Re: Help! ORA-01658 on import, and with plenty of space

Norman (fast learner),

Thank you for your attachment. I am currently going through it. I am also going through the defrag whitepaper. As a novice, this is information I have been needing for quite some time now. I appreciate your interest and assistance on my problem. Your explanation makes sense to me, and after checking the stats on the tablespace, I see a lot less fragmentation in the import of the data, even though I paid a small price on speed.

Thanks,
Pedro Mercado

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA7029137C6_at_lnewton.leeds.lfs.co.uk>...
> Hi Pedro,
>
> Please stop calling me 'Mr Dunbar' - I'm Norman. Thanks :o) (Hey, we're
> all friends here remember !)
>
> Next up, the reason for slower responses is probably because you
> followed my suggestions !
> When you removed the storage parameters from the script and pre-created
> the tables, the tablespace defaults will have been used. If these are
> quite small, and some of the tables quite large, then you will end up
> with a lot of 'dynamic extension' going on as new extents are added to
> tables and indexes as the import progresses.
>
> Dynamic extensions are expensive operations and will slow things down -
> especially if the database in question has other schemas and they are
> being used. There is only a single space managemnt lock (I forget the
> exact name !) and every user adding or deleting extents has to queue up
> for it !
>
> So if you are importing a 20 Gb table into a tablespace with
> INITIAL=NEXT=1024 KB, you'll need to dynamically extend the table
> 20,971,519 times.
>
> It's all down to setting correct default storage on your tablespaces and
> having more than 1 tablespace for data. There is a white paper about
> setting up tablespaces for different sized objects at this URL:
>
> http://technet.oracle.com/deploy/availability/pdf/defrag.pdf
>
> Which will/should help enormously !
>
>
> Cheers,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> -----Original Message-----
> From: mercadop_at_expertx.com (Pedro Mercado) [mailto:mercadop_at_expertx.com]
> Posted At: Tuesday, August 27, 2002 5:12 PM
> Posted To: server
> Conversation: Help! ORA-01658 on import, and with plenty of space
> Subject: Re: Help! ORA-01658 on import, and with plenty of space
>
>
> I have employed the suggestions made by Mr. Dunbar, and it appears to
> creating the tables more efficiently. That is, the data is being
> spread evenly across the datafiles. However, the speed has
> dramatically slowed down. Is there a reason for this?
>
> Thanks,
> Pedro Mercado
Received on Wed Aug 28 2002 - 11:54:56 CDT