Fragmented tables

[JasonCooper]

Hi

I need to 'defrag' some tables and wonder if the following method is the best way:

1. export table(s)
2. drop table 'tablename' cascade constraints
3. Import table(s)

It's mainly the drop command I'm unsure about.

Thanks

 

[mrn]

Why do you need to defrag? How do you know they are fragmented. If your talking about chained rows then let me know and show you a way of sorting them out, if you talking extents also let me know as having loads of extents isn't always a bad thing either.

--
| Mike Nixon
| Unix Admin
|
----------------------------

 

[sem]

I suppose, you can not restore constraints that reference this table, as they "belongs" to other objects thus will be missed in export. BTW, why do you need it?

Regards, Dima

 

[JasonCooper]

Mike, Dima, I have tables with many extents, most under 10 extents the rest are between 25 - 100 extents. It's been recommended we defrag when a table reaches >5 extents.

Thanks

 

[sem]

I suppose that it was not a good advice, because such small number can not affect performance. Look here:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:730289259844

http://www.google.com/groups?hl=en&...lli1a%241ppeaj%241%40ID-152732.news.dfncis.de

Regards, Dima

 

[stefanhei]

Use local managed tablespaces and forget about extents.
If you need to bring your tables into 1 extend, better use
alter table move and rebuild your indexes on that table afterwards.

Stefan

 

[JasonCooper]

Thanks very much mrn, sem and stefanhei.

> sem, that makes interesting reading and makes alot of sense, we have no performance issues at the moment so I'm not going to lose any sleep over the extents at the moment.

Many thanks everybody

 

[sem]

Though, defragmenting tablespace is not so useless

Regards, Dima

 

[stefanhei]

@sem: use LMT and you no longer have to defragment ;-)

Stefan

 

[sem]

Stefan: I have NO PROBLEMS, Jason thinks he has

Regards, Dima