Tablespace reorganisation?

Hi! I've been told that i need to carry out a tablespace reorganisation of one of the instances...
Does this mean recreating the database?
Anyone have any pointers or know of any resouces/articles to help with this?
Many thanks!

Who ever told you that ask him/her what does he/she mean by tablespace reorganization and why?

--Do they want you to move the datafiles from one location to other?? why ??
--Shrink the datafiles ?? why ??
--Rename (name) the tablespaces ?? why ??
--Change tablespaces from data dictionary to LMT?? why ??
--Partition the table(more tablespaces) ?? why ??
--Change storage clauses for Tablespaces?? why ??

Tablespace reorganization can be any thing... ask them to be specific and ask the reason... don't just say yes and the run here and there asking others what to do...

If they have the answer to why then they know what they want, if no, forget it... and have a BEER

They probably mean de-fragmenting the tablespace. Tablespace defragmentation only happens when objects are created and dropped and not because of row-level activity. So, it is something the dba wants to keep an eye on. Run this query to check on tablespace fragmentation:

select tablespace_name, block_id, bytes, blocks, segment_name
from dba_free_space
union all
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_extents;

Run the following to de-fragment the tablespaces that are fragmented:

alter tablespace coalesce;

Good luck.

One more thing to keep in mind:

From William Page's book:

"In object-level fragmentation, the object might look fine and healthy at the tablespace level, having a single extent, but some probing inside those extents can reveal a different story.

A common occurence is that a DBA sees space allocation problems in an often-modified tablespace, and rebuilds the tablespace and all its objects. Subsequently, the database performance can increase. This increase in performance is attributed to the tablespace fragmentation being eliminated, but it could very well be the eliminition of object-level fragmentation that gives you the performance boost."

Mr. Page says that in order to de-fragment the tablespace, do an export and import of the objects within that tablespace. The export/import process "not only coalesces your free space, but Export also coalesces your database objects into one extent." So if you were asked to de-frgment the tablespace because of performance issues, then you might want to consider export/import to solve two problems in one shot.

That stuff about how it is better to have tables/indexes as a single extent is a big pile of BS, and has been debunked for many years now.

Export/import to reduce number of extents is ...

i) not going to improve performance unless you have more than many thousands of extents
ii) the wrong way to do it any way -- do "alter table move" and "alter index rebuild" if you really really do have an extent problem.

Quote:

---

Originally posted by akhorram
They probably mean de-fragmenting the tablespace. Tablespace defragmentation only happens when objects are created and dropped and not because of row-level activity. So, it is something the dba wants to keep an eye on. Run this query to check on tablespace fragmentation:

select tablespace_name, block_id, bytes, blocks, segment_name
from dba_free_space
union all
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_extents;

Run the following to de-fragment the tablespaces that are fragmented:

alter tablespace coalesce;

Good luck.

---

oh my.... here he comes with the old myth... pile of BS as slimdave pointed out

Other than the attitude, I appreciate the fact that you point out I am wrong. Please let me know how to de-fragment tablespaces. What different approaches can be taken? Thank you.

The primary approach is to not let your tablespaces get fragmented. If locally managed tablespaces are not available in your version of Oracle then divide your tables/indexes into three groups, by size -- small, medium, large.

Put each type in it's own tablespace -- suggested extent sizes are generally something like 256k, 8m, 256M -- and use pctincrease of 0 on all segments. do not specifyinitial or next when creating segments, just specify the appropriate tablespace based on size.

If locally managed tablespaces are available, then use them. Specify uniform extent sizes, divide the segments into small medium and large as before and put them in an appropiate TS.

Quote:

---

Originally posted by akhorram
They probably mean de-fragmenting the tablespace. Tablespace defragmentation only happens when objects are created and dropped and not because of row-level activity. So, it is something the dba wants to keep an eye on. Run this query to check on tablespace fragmentation:

select tablespace_name, block_id, bytes, blocks, segment_name
from dba_free_space
union all
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_extents;

Run the following to de-fragment the tablespaces that are fragmented:

alter tablespace coalesce;

Good luck.

---

isnt defragment just to collect the blocks so that they can be contiguous for data usage?

They might be logically contiguous within Oracle, but physicaly (on disk) they are almost certain not to be. That's one of the reasons why the benefits of small number of extents in nonsense

NEVER Export with COMPRESS = Y (Which, incidently IS THE Oracle Default setting *shudder*... Maybe in 10i that'll change... YEAH RIGHT!