Subject: Tools for Defragging Tables
To: Christopher Spence
On Thu, 30 Sep 1999, Christopher Spence wrote:
> What is the best tool to export a table that needs
> de-fragmentation and re-import it?
> Between PK/FK, Triggers, and other issues, you have to do a
> lot more than just export it, drop it, and re import it.
>
> Anyone that does this, please help a fairly new d.b.a. with
> more established method for accomplishing such a feat. Or even recommend a
> tool that is commonly used for such of thing.
>
> 'E-mail returned to sender,
> insufficient voltage.'
>
> Christopher R. Spence
> Senior MIS Engineer -- DBA
> MCP CNA Raptor A+
> cspence@delphi-tech.com
> Phone: (617) 494-8361 x24
> Fax: (707) 885-2275
>
I really like using Platinum's "TS-Reorg" tool. It can reorg tables,
tablespaces, and so on. It is pretty fast as well (I believe that Oracle
shared some source code to write directly to datafiles).
Anyway, if you do not want to purchase the 3rd-party tool, you can always
export a table, drop it (cascade constraints) and import it again. But you
must take into account:
1) All constraints that reference the table (Foreign Keys) would be
dropped ; you must identify and recreate these.
2) All procedures/packages that reference the table become invalid. Then,
secondary procedures/packages that reference the just-invalidated
procedures/packages become invalid. This cascades until there are no more
dependencies. All of these must be identified and recompiled ("alter
package xxx compile;" ).
3) grants are dropped and must be re-granted (both to users and to roles).
Import takes care of this.
4) Constraints (PK, FK, Check) on the table must be recreated. Import
takes care of this.
5) Triggers on the table must be recreated. Import takes care of this.
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 335+ Oracle tips, visit my Web Page: <->
<-> <->
<-> http://www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page