Re: Reorganise local tablespace

On Oct 14, 7:31 pm, DA Morgan <damor..._at_psoug.org> wrote:
> aman.oracle.dba wrote:
> > Hi all,
>
> > I know that we have to reorganise dictionary tablespace but pls inform
> > does reorganisation of local tablespace also require.
> > If yes then how to check which one.
>
> > Thanks in advanceI've never heard of HAVING to reorganize anything without metrics.
> What makes you think a reorg. is required?
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

The need to reorganize a tablespace to reclaim and consolidate free extents is pretty much associated with dictionary managed tablespaces. If the locally managed tablespace is using uniform extents then a reorganization to reclaim and consolidate free extents is never necessary. With an auto-allocate tablespace free space fragmentation can exist, but it is still unlikely that you would need to reorganize the tablespace to reclaim free extents. In any case you would only perform such a task if you had query results that showed such action would be beneficial.

One thing that might require you to "reorganize the tablespace" might be to reallocate objects toward the front of the datafiles where you needed to shrink a file that has significant free exents in it but because objects are allocated extents near the end of the file you cannot shrink it. This is a different problem and is usaully referred to as a file shrink problem.

HTH -- Mark D Powell -- Received on Sat Oct 14 2006 - 20:57:11 CDT