Re: sqllder hangs at 99-100% CPU on select of fet$

"Tanel Poder" <change_to_my_first_name_at_integrid.info> wrote in message news:3f5e776d$1_1_at_news.estpak.ee...
> Hi!
>
> > dumpfile size limits, for example). Therefore, consider, too, the use of
> > 'create table blahcopy tablespace ANOTHERONE as select * from blah'.
That
> > copies your data elsewhere within the database. Then you can drop table
> > blah, re-create it with good extent sizes, and then 'insert into blah
> select
> > * from blahcopy', followed by a drop of the copy table to tidy up.
That's
> > going to be a lot of disk space, probably, but I suspect will be quicker
> > than the export-import route.
>
> Yep, this can be faster as long as the CTAS operation is done nologging. I
> haven't used Oracle versions under 8.0 much, is CTAS nologging by default
in
> 7.3?

Doubt it. Good catch, Tanel. Forgot to mention it, but yes... you'd need to specify it.

>
> But one big problem with CTAS & dropping tables meanwhile is that your
> triggers, constraints (except not null ones I believe), grants and indexes
> get lost when you drop your table & you have to recreate them. It can be
> easier with well-documented and scripted applications, but with packaged
> apps it can be a pain. One way would be to use import with rows=n after
> recreated those schemas/tables, but I've never tested it whether it
creates
> triggers for example (index & constraint creation works well).

Also true, and I should have thought of that myself, too.

Regards
HJR
>
> I'm about to do a same kind of reorg in a messy db with DMTs, but I'm
lucky
> to be on 8.1, thus can use alter table move command...

[Don't forget all those index rebuilds afterwards!!]

>
> Tanel.
>
>
Received on Tue Sep 09 2003 - 20:06:43 CDT