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

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?

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).

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...

Tanel. Received on Tue Sep 09 2003 - 19:59:24 CDT