Re: Tablespace defragmentation

unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER) wrote:

>Hi netters!
 

>I would like to defragment all my tablespaces.
 

>0- About the SYSTEM tablespace, I've posted an
> article monday, but I have received no help
> since now...
 

>1- temporary tablespace
> The temp tablespace doesn't contains data, but I
> have actually a Free Space Fragmentation Index (FSFI)
> with value 23.8094802, so I need to defragment it.
> How to do? Simply drop tablespace temp, create
> tablespace temp? What about the users who have
> this tablespace defined as temporary tablespace?
 

>2- index tablespaces
> At the moment no problem (FSFI = 100, wow!).
> But in case I have to do it, what to do?
> I can try to guess: drop indexes, drop tablespace inx01,
> create tablespace inx01, create index using inx01.
> Right? Others solutions?
 

>3- data tablespaces
> Also have to do it. FSFI = 23.1302211
> Export compress=Y + import?
> Have to drop/create all the tables before to import?

There are a few different ways of doing what you are thinkning about. A lot depends on wether or not you have people using the database 24 hours per day.

Assuming that you can have some down time, the easiest thing to do is a full database export, recreate the database and temporary tablespace using a script, then do a full database import. The full import will create whatever tablespace structure you had when you started out.

If you can't be down for that much time, or you don't want to write the scripts to do it automatically, here are some other ways of dealing with fragmentation.

Dropping the temporary tablespace. If nobody is using it this is no problem. I remember having some problems with the "drop tablespace temp including contents." If your temporary tablespace does not have anything in it, it should be no problem. Drop it and recreate it. As long as nobody is using the database it should be no problem. If somebody tries to create a temporary object while you have the tablespace down, I think it gives some error.

One of the things I used to do was create tablespaces with the size of the "initial" and "next" extents the same size. That way when the object using the extents gets dropped, there is not a bunch of odd sized extents left in the tablespace. I have seen where there is a bunch of free space available, but there is not any extents large enough. That problem does not happen if all extents are the same size.

This way that I used to deal with fragmentation, is by dropping and recreating the database every couple of weeks. I got the idea from someone at the Oracle DBA class. From the way he had explained it, he had it automated using script files.

• ========= ========= ========= ========= Noah Monsey noah_at_indirect.com
• ========= ========= ========= ========= The only dumb question is the one that you don't ask.
• ========= ========= ========= ========= Oracle Master Database Administration April 1992 Oracle Master Application Development April 1992
• ========= ========= ========= =========